CN110851142A - Method for converting Transact-SQL program into Java program - Google Patents
Method for converting Transact-SQL program into Java programDownload PDFInfo
- Publication number
- CN110851142A CN110851142ACN201910994111.1ACN201910994111ACN110851142ACN 110851142 ACN110851142 ACN 110851142ACN 201910994111 ACN201910994111 ACN 201910994111ACN 110851142 ACN110851142 ACN 110851142A
- Authority
- CN
- China
- Prior art keywords
- sql
- java
- statement
- code
- type
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Withdrawn
Links
Images
Classifications
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/51—Source to source
Landscapes
- Engineering & Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Devices For Executing Special Programs (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Description
Translated fromChinese技术领域technical field
本发明属于计算机软件中的程序转换领域，具体涉及一种将Transact-SQL程序转换为Java程序的方法。The invention belongs to the field of program conversion in computer software, in particular to a method for converting a Transact-SQL program into a Java program.
背景技术Background technique
随着程序语言的发展，新型的程序语言被不断设计出来。相比于传统程序语言，新型程序语言往往支持更多的高级语言特性，表达能力更强，开发起来更快捷，更适应技术发展潮流。因此，企业中的很多历史悠久的业务系统面临着从传统编程语言向新型编程语言转换的需求。With the development of programming languages, new programming languages are constantly being designed. Compared with traditional programming languages, new programming languages often support more high-level language features, have stronger expressive ability, are faster to develop, and are more adaptable to the trend of technological development. As a result, many long-established business systems in the enterprise face the need to transition from traditional programming languages to newer programming languages.
SQL(Structured Query Language)，即结构化查询语言，是关系型数据库的标准语言，已被众多数据库管理系统所采用。Transact-SQL(简称T-SQL)是Microsoft SQLSERVER对SQL的扩展，提供了类似C、Basic和Pascal中的过程式语句，如变量、控制流、子程序等，使得SQL不再局限于标准的数据库操作，成为一种功能强大的结构化语言，可以用来完成业务逻辑编写。SQL (Structured Query Language), that is, structured query language, is the standard language of relational databases and has been adopted by many database management systems. Transact-SQL (T-SQL for short) is an extension of Microsoft SQLSERVER to SQL, providing procedural statements similar to C, Basic and Pascal, such as variables, control flow, subroutines, etc., so that SQL is no longer limited to standard databases Operation has become a powerful structured language that can be used to write business logic.
Java是一门面向对象编程语言，不仅吸收了C++的各种优点，还摒弃了C++里难以理解的多继承、指针等概念，因此Java语言具有功能强大和简单易用两个特征。Java允许程序员以优雅的思维方式进行复杂的编程，是目前主流的编程语言之一，并且是编写企业应用程序的首选。Java is an object-oriented programming language. It not only absorbs the advantages of C++, but also abandons the concepts of multiple inheritance and pointers that are difficult to understand in C++. Therefore, the Java language has two features: powerful and easy to use. Java allows programmers to do complex programming in an elegant way of thinking. It is one of the mainstream programming languages and the first choice for writing enterprise applications.
与一般的程序转换场景不同，T-SQL是一种特殊的编程语言，它包括SQL语句和过程式语句两部分，而其中的SQL语句必须由数据库来执行，因此，转换后的Java程序中既有Java代码(通常由T-SQL中的过程式语句转换而来)，也有SQL语句(包含在Java的JDBC API调用语句中，以Java中的字符串形式存在)。这两部分代码运行在不同的系统软件中，Java代码运行在Java虚拟机中，SQL语句运行在数据库中(Java程序在执行JDBC API调用语句时会通过系统驱动将SQL语句传递给数据库来执行)。这样的运行方式带来两个主要问题：(1)两种代码在运行时如何进行数据传递；(2)SQL语句如何调用Java语句。产生第二个问题的主要原因是SQL语句中会嵌套一些子程序调用，但是T-SQL中的子程序需要整体转换为Java中的子程序，而SQL语句在数据库中执行时是无法调用Java中的子程序的。Different from general program conversion scenarios, T-SQL is a special programming language that includes SQL statements and procedural statements, and the SQL statements must be executed by the database. Therefore, in the converted Java program, both There is Java code (usually converted from procedural statements in T-SQL), and there are SQL statements (contained in Java's JDBC API call statements, which exist in the form of strings in Java). The two parts of the code run in different system software, the Java code runs in the Java virtual machine, and the SQL statement runs in the database (the Java program will pass the SQL statement to the database through the system driver when executing the JDBC API call statement for execution) . Such an operation mode brings two main problems: (1) how the two codes perform data transfer at runtime; (2) how the SQL statement invokes the Java statement. The main reason for the second problem is that some subroutine calls are nested in SQL statements, but the subroutines in T-SQL need to be converted into subroutines in Java as a whole, and Java cannot be called when SQL statements are executed in the database. subroutine in .
另外，也存在一些特殊情况：In addition, there are some special cases:
■不需要将SQL语句转换为JDBC API调用语句，而是转换为Java中的原生实现，这是因为T-SQL本身的语法限制，导致一些在Java中很容易就能完成的功能，在T-SQL中就需要用一些复杂的SQL语句。比如Java中的容器在T-SQL中并没有类似的实现，所以只能用数据库表来替代。There is no need to convert SQL statements into JDBC API call statements, but into native implementations in Java. This is because of the grammatical limitations of T-SQL itself, resulting in some functions that can be easily completed in Java. SQL needs to use some complex SQL statements. For example, the container in Java does not have a similar implementation in T-SQL, so it can only be replaced by a database table.
■有些过程语句必须由数据库来执行，所以需要将过程式语句转换为JDBC API调用。例如T-SQL中的SET语句通常可以转换为Java中的赋值语句，但如果是通过SET语句改变数据库系统状态，就只能转换为JDBC API调用。■Some procedural statements must be executed by the database, so it is necessary to convert procedural statements into JDBC API calls. For example, the SET statement in T-SQL can usually be converted into an assignment statement in Java, but if the database system state is changed through the SET statement, it can only be converted into a JDBC API call.
综上所述，将T-SQL转换为Java是一项十分复杂的任务。而且，现实中的业务系统往往积累了大量的T-SQL代码，开发人员进行手动转换需要耗费大量的时间和人力。To sum up, converting T-SQL to Java is a very complex task. Moreover, real business systems often accumulate a large amount of T-SQL code, and it takes a lot of time and manpower for developers to perform manual conversion.
发明内容SUMMARY OF THE INVENTION
针对上述现有技术中存在的缺陷，本发明的目的在于提供一种将Transact-SQL程序转换为Java程序的方法。In view of the above-mentioned defects in the prior art, the purpose of the present invention is to provide a method for converting a Transact-SQL program into a Java program.
为达到上述目的，本发明釆用如下技术方案：To achieve the above object, the present invention adopts the following technical solutions:
一种将Transact-SQL程序转换为Java程序的方法，包括如下步骤：A method for converting a Transact-SQL program into a Java program, comprising the following steps:
获取Transact-SQL代码；Get Transact-SQL code;
遍历类型定义代码，获取全局类型信息；Traverse the type definition code to obtain global type information;
遍历业务逻辑代码，获取签名信息以及依赖关系；Traverse the business logic code to obtain signature information and dependencies;
确定业务逻辑代码的转换顺序；Determine the conversion sequence of business logic code;
依次遍历业务逻辑代码，按照转换规则，执行转换过程。Traverse the business logic code in turn, and execute the conversion process according to the conversion rules.
上述技术方案中，进一步地，所获取的Transact-SQL代码包括数据库表定义代码、用户定义类型代码、函数定义代码、存储过程定义代码。其中，数据库表定义代码和用户定义类型代码统称为类型定义代码，这些代码所定义的类型是所有业务逻辑代码都可以使用的全局类型；函数定义代码和存储过程定义代码统称为业务逻辑代码，执行业务代码就是对函数/存储过程的调用。In the above technical solution, further, the acquired Transact-SQL code includes database table definition code, user-defined type code, function definition code, and stored procedure definition code. Among them, database table definition code and user-defined type code are collectively called type definition code, and the types defined by these codes are global types that can be used by all business logic codes; function definition code and stored procedure definition code are collectively called business logic code. Business code is just a call to a function/stored procedure.
进一步地，所述的遍历类型定义代码，获取全局类型信息，用于后续对业务逻辑代码中的数据类型进行转换，包括如下步骤：首先，构建T-SQL类型定义代码的抽象语法树AST；然后遍历抽象语法树，访问特定节点类型，提取全局类型信息，主要包括类型名、子类型构成等。Further, the described traversal type definition code obtains global type information for subsequent conversion of the data type in the business logic code, including the following steps: first, construct the abstract syntax tree AST of the T-SQL type definition code; then Traverse the abstract syntax tree, access specific node types, and extract global type information, mainly including type name, subtype composition, etc.
进一步地，所述的遍历业务逻辑代码，获取签名信息以及依赖关系，用于后续对业务逻辑代码中的子过程调用进行转换，包括如下步骤：首先，构建T-SQL业务逻辑代码的抽象语法树；然后，遍历抽象语法树，访问特定节点类型，提取签名信息以及依赖信息；Further, the described traversal of the business logic code to obtain signature information and dependencies for subsequent conversion of sub-procedure calls in the business logic code, including the following steps: first, constructing an abstract syntax tree of the T-SQL business logic code ; Then, traverse the abstract syntax tree, access specific node types, extract signature information and dependency information;
其中，签名信息包括名称(函数名/存储过程名)、输入参数的信息和返回值的信息，输入参数的数量和返回值的数量都是不限的；输入参数的信息包括名称、类型、顺序、默认值；每个输入参数或返回值的类型是内置类型、数据库表和自定义类型中的任意一种具体类型。返回值的信息包括名称、类型、顺序等；Among them, the signature information includes name (function name/stored procedure name), input parameter information and return value information, the number of input parameters and the number of return values are not limited; the input parameter information includes name, type, sequence , default value; the type of each input parameter or return value is any one of the built-in types, database tables, and custom types. The information of the return value includes name, type, order, etc.;
依赖信息主要体现在业务逻辑代码之间的调用关系，如果函数/存储过程A调用了函数/存储过程B，就认为函数/存储过程A依赖于函数/存储过程B。Dependency information is mainly reflected in the calling relationship between business logic codes. If function/stored procedure A calls function/stored procedure B, it is considered that function/stored procedure A depends on function/stored procedure B.
更进一步地，所述的业务逻辑代码的转换顺序主要考虑代码之间的依赖关系，被依赖方要在依赖方之前进行转换，所以需要基于依赖关系，通过拓扑排序确定转换顺序。Furthermore, the transformation sequence of the business logic code mainly considers the dependency relationship between the codes, and the relying party needs to perform the transformation before the relying party, so the transformation sequence needs to be determined by topological sorting based on the dependency relationship.
更进一步地，完成转换后，每个T-SQL自定义函数/存储过程都转换为独立的Java类，自定义函数/存储过程中的可执行代转换为Java类中的一个静态方法的方法体。Further, after the conversion is completed, each T-SQL custom function/stored procedure is converted into an independent Java class, and the executable code in the custom function/stored procedure is converted into the method body of a static method in the Java class. .
进一步地，实际转换过程包括方法签名生成和方法体生成两部分；方法签名基于前述步骤提取的T-SQL签名信息来生成，主要包括方法名、输入参数名称、输入参数类型和返回值类型；方法体的生成主要包括如下步骤：基于T-SQL业务逻辑代码构建抽象语法树；遍历T-SQL抽象语法树，根据节点类型及其相邻节点类型，按照T-SQL语法的转换规则转换为Java抽象语法树；根据Java抽象语法树生成Java业务逻辑代码。Further, the actual conversion process includes two parts: method signature generation and method body generation; method signature is generated based on the T-SQL signature information extracted in the preceding steps, mainly including method name, input parameter name, input parameter type and return value type; method The generation of the body mainly includes the following steps: constructing an abstract syntax tree based on the T-SQL business logic code; traversing the T-SQL abstract syntax tree, according to the node type and its adjacent node type, and converting it to Java abstraction according to the conversion rules of T-SQL syntax Syntax tree; generate Java business logic code according to Java abstract syntax tree.
进一步地，在生成方法体的过程中，需要对包含子程序调用的标准SQL语句进行特殊处理，以解决Java代码和SQL代码之间的数据传递问题，因为SQL语句需要在数据库中执行，而子程序调用需要在Java运行环境中执行；包含子程序调用的SQL语句的转换过程，主要分成三种情况，一是系统内置子程序调用，二是不依赖于SQL语句的自定义子程序调用，三是依赖于SQL语句的自定义子程序调用，如果子程序调用的输入参数引用了数据库表的数据列，那么子程序的执行结果就依赖于所在的SQL语句；所述的特殊处理主要包括如下步骤：Further, in the process of generating the method body, it is necessary to perform special processing on the standard SQL statements including subroutine calls to solve the problem of data transfer between Java code and SQL code, because the SQL statement needs to be executed in the database, and the subroutine The program call needs to be executed in the Java runtime environment; the conversion process of the SQL statement including the subprogram call is mainly divided into three cases, one is the system built-in subprogram call, the second is the custom subprogram call that does not depend on the SQL statement, and the third is the system built-in subprogram call. It is a custom subprogram call that depends on SQL statements. If the input parameters of the subprogram call refer to the data columns of the database table, then the execution result of the subprogram depends on the SQL statement where it is located; the special processing mainly includes the following steps :
判断子程序类型，如果是系统内置子程序，不需对子程序进行转换，直接生成JDBCAPI调用语句，因为SQL语句中通常会大量调用这些内置子程序，对其进行转换会造成Java代码的极大膨胀，而且这些内置子程序相对简单，相较而言，转换带来的收益较少；如果是自定义子程序，再判断子程序的执行结果是否依赖于所在的SQL语句，如果不存在依赖，先将自定义子程序的调用提前，转换为Java中的子程序调用语句，并使用局部变量存储返回值，再将SQL语句中的子程序调用改为对返回值的引用，如果存在依赖，则不存在统一的转换方案，需要开发人员根据具体代码人工设计转换方案。Judging the type of subroutine, if it is a built-in subroutine in the system, it is not necessary to convert the subroutine, and directly generate the JDBCAPI call statement, because a large number of these built-in subroutines are usually called in the SQL statement. Inflation, and these built-in subroutines are relatively simple, in comparison, the conversion brings less benefits; if it is a custom subroutine, then determine whether the execution result of the subroutine depends on the SQL statement where it is located, and if there is no dependency, First advance the call of the custom subroutine, convert it into a subroutine call statement in Java, and use a local variable to store the return value, and then change the subroutine call in the SQL statement to a reference to the return value. If there is a dependency, then There is no unified conversion scheme, and developers need to manually design the conversion scheme according to specific codes.
更进一步地，所述的JDBC API主要分成四类，分别是JDBC DECLARE API、JDBC SETAPI、JDBC EXECUTE API和JDBC GET API。JDBC DECLARE API用于声明SQL语句，JDBC SETAPI用于初始化SQL语句中的数值，JDBC EXECUTE API用于执行SQL语句，JDBC GET API用于获取SQL语句的执行结果；所述的生成JDBC API调用语句，包括如下步骤：Further, the JDBC API is mainly divided into four categories, namely JDBC DECLARE API, JDBC SETAPI, JDBC EXECUTE API and JDBC GET API. The JDBC DECLARE API is used to declare SQL statements, the JDBC SETAPI is used to initialize the values in the SQL statements, the JDBC EXECUTE API is used to execute the SQL statements, and the JDBC GET API is used to obtain the execution results of the SQL statements; the generated JDBC API call statement, It includes the following steps:
处理SQL语句得到SQL字符串，并生成JDBC DECLARE API调用语句；判断SQL语句是否引用了变量，如果引用了变量，则获取对应的Java变量信息，并生成JDBC SET API调用语句；生成JDBC EXECUTE API调用语句；判断SQL语句是否是查询语句，如果不是查询语句，则完成生成；如果是查询语句，则生成JDBC GET API调用语句并生成赋值语句；Process the SQL statement to obtain the SQL string, and generate the JDBC DECLARE API call statement; determine whether the SQL statement refers to a variable, and if so, obtain the corresponding Java variable information, and generate the JDBC SET API call statement; generate the JDBC EXECUTE API call Statement; judge whether the SQL statement is a query statement, if it is not a query statement, complete the generation; if it is a query statement, generate a JDBC GET API call statement and generate an assignment statement;
所述的转换为Java中的子程序调用语句的方法，包括如下步骤：如果调用时使用了可选参数，根据函数定义时声明的参数默认值补全可选参数；如果调用时使用了参数名来指定实际参数与形式参数的对应关系，根据函数定义时声明的形式参数列表顺序生成实际参数列表顺序；生成Java子程序调用语句。The described method of converting into a subprogram calling statement in Java includes the following steps: if optional parameters are used when calling, the optional parameters are completed according to the default values of the parameters declared when the function is defined; if the parameter names are used when calling To specify the correspondence between actual parameters and formal parameters, generate the actual parameter list order according to the formal parameter list order declared when the function is defined; generate Java subprogram call statements.
更进一步地，所述的生成JDBC GET API调用语句并生成赋值语句的方法为，针对JDBC EXECUTE API的每个返回值，执行如下步骤：Further, the method for generating the JDBC GET API calling statement and generating the assignment statement is to perform the following steps for each return value of the JDBC EXECUTE API:
首先获取类型信息，生成变量声明语句以定义临时变量，然后生成赋值语句，调用对应的JDBC GET API获取返回值，并存储到临时变量；First obtain the type information, generate a variable declaration statement to define a temporary variable, then generate an assignment statement, call the corresponding JDBC GET API to obtain the return value, and store it in the temporary variable;
其中，返回值类型主要分为两种，数值类型和数据库表类型；对于返回值为数值类型的子程序调用，转化后的Java代码中，临时变量存储的是真实数值；对于返回值为数据库表类型的子程序调用，转化后的Java代码中，临时变量存储的是表名。对于返回值为数据库表类型的子程序调用，转换后的Java代码中，临时变量存储的是表名，并且在转换这些子程序时，将其中用于存储返回值的临时表声明为全局唯一的正式表，并返回正式表的表名。因为，对于返回值为数据库表类型的子程序，它们通常先要在函数体内部创建临时表，再在临时表上进行SQL操作，最后返回临时表。而且调用方在拿到返回值之后，也是进行SQL操作。由此可见，临时表上的操作都需要在数据库中完成。为了减少数据传递，本发明将这些用于存储返回值的临时表声明为全局唯一的正式表。Among them, the return value type is mainly divided into two types, numerical type and database table type; for the subroutine call whose return value is a numerical type, in the converted Java code, the temporary variable stores the real value; for the return value of the database table Type of subroutine call, in the converted Java code, the temporary variable stores the table name. For subroutine calls whose return value is database table type, in the converted Java code, the temporary variable stores the table name, and when these subroutines are converted, the temporary table used to store the return value is declared as globally unique Formal table, and returns the table name of the formal table. Because, for subroutines whose return value is database table type, they usually first create a temporary table inside the function body, then perform SQL operations on the temporary table, and finally return the temporary table. And the caller also performs SQL operations after getting the return value. It can be seen that the operations on the temporary table need to be completed in the database. In order to reduce data transfer, the present invention declares these temporary tables for storing return values as globally unique formal tables.
本发明的有益效果为：The beneficial effects of the present invention are:
一方面，在转换过程中考虑了T-SQL和Java各自的优势，既保留了T-SQL对数据库的高效操作，又获得了Java的灵活性和可扩展性；另一方面，通过引入自动化转换技术，减少了人力成本，提高了转换效率。On the one hand, the respective advantages of T-SQL and Java are considered in the conversion process, which not only retains the efficient operation of T-SQL on the database, but also obtains the flexibility and scalability of Java; on the other hand, by introducing automatic conversion technology, reducing labor costs and improving conversion efficiency.
附图说明Description of drawings
为了更清楚地说明本发明实施例，下面将对实施例中所需要使用的附图作简单地介绍。显而易见地，下面描述中的附图仅仅是本发明的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其他的附图。In order to describe the embodiments of the present invention more clearly, the following briefly introduces the accompanying drawings that are used in the embodiments. Obviously, the drawings in the following description are only some embodiments of the present invention, and for those of ordinary skill in the art, other drawings can also be obtained from these drawings without creative effort.
图1为本发明实施例提供的程序转换方法整体流程图；1 is an overall flowchart of a program conversion method provided by an embodiment of the present invention;
图2为本发明实施例提供的单独子程序调用的转换案例；Fig. 2 is the conversion case of the independent subroutine call that the embodiment of the present invention provides;
图3为本发明实施例提供的单独SQL语句的转换案例。FIG. 3 is a conversion case of a single SQL statement provided by an embodiment of the present invention.
具体实施方式Detailed ways
本部分将依据本发明中所述的程序转换方法，通过一些具体案例对本发明做进一步的详细说明，但不构成对本发明的限制。This part will further describe the present invention in detail through some specific cases according to the program conversion method described in the present invention, but does not constitute a limitation of the present invention.
如图1所示为本发明的程序转换方法整体流程图。FIG. 1 is an overall flow chart of the program conversion method of the present invention.
本发明中获取T-SQL源码，可以借助于已有的工具，如微软官方的数据库管理工具SQL Server Management Studio，可以导出数据库实例中的所有T-SQL对象。其中，每个T-SQL对象都保存在单独的源码文件中，并且可以通过文件名的后缀判断此源码文件中SQL语句的类型。例如自定义函数的文件名都是以UserDefinedFunction.sql结尾。In the present invention, the source code of T-SQL can be obtained, and all T-SQL objects in the database instance can be exported with the help of existing tools, such as SQL Server Management Studio, an official database management tool of Microsoft. Among them, each T-SQL object is stored in a separate source file, and the type of SQL statement in the source file can be judged by the suffix of the file name. For example, the file names of custom functions all end with UserDefinedFunction.sql.
本发明主要获取数据库表定义代码、类型定义代码、函数定义代码和存储过程定义代码。其中，数据库表定义代码和类型定义代码统称为类型定义代码，这些代码所定义的类型是所有业务逻辑代码都可以使用的全局类型；函数定义代码和存储过程定义代码统称为业务逻辑代码，执行业务代码就是对函数/存储过程的调用。The present invention mainly acquires database table definition code, type definition code, function definition code and stored procedure definition code. Among them, database table definition code and type definition code are collectively called type definition code, and the types defined by these codes are global types that can be used by all business logic codes; function definition code and stored procedure definition code are collectively called business logic code, which executes business logic. Code is just a call to a function/stored procedure.
本发明中对T-SQL程序的分析，都是基于通用的抽象语法树AST，所以涉及到建立和操纵AST。在本发明的实施例中，T-SQL的AST是使用ANTLR4来创建的。ANTLR4会根据给定的语法描述文件生成词法解析器和语法解析器，这样就可以解析源码得到抽象语法树AST。The analysis of the T-SQL program in the present invention is all based on the general abstract syntax tree AST, so it involves building and manipulating the AST. In an embodiment of the present invention, the AST for T-SQL is created using ANTLR4. ANTLR4 will generate a lexical parser and a grammar parser according to the given grammar description file, so that the source code can be parsed to obtain the abstract syntax tree AST.
在进入实际的转换过程之前，需要先获取一些全局信息，用于辅助后续转换过程。Before entering the actual conversion process, it is necessary to obtain some global information to assist the subsequent conversion process.
首先遍历类型定义代码，获取全局类型信息，用于后续对业务逻辑代码中的数据类型进行转换。该过程可以进一步分成两个子过程，首先基于ANTLR4生成的语法解析器，然后构建T-SQL类型定义代码的抽象语法树AST。遍历抽象语法树，访问特定节点类型，提取全局类型信息，主要包括类型名、子类型构成等。例如数据库表的名称可以访问Table_nameContext类型的节点获取，数据库表的构成可以访问Column_definitionContext类型的节点获取。First, traverse the type definition code to obtain the global type information, which is used to convert the data type in the business logic code later. The process can be further divided into two sub-processes, first based on the grammar parser generated by ANTLR4, and then building the abstract syntax tree AST of the T-SQL type definition code. Traverse the abstract syntax tree, access specific node types, and extract global type information, mainly including type name, subtype composition, etc. For example, the name of the database table can be obtained by visiting the node of type Table_nameContext, and the composition of the database table can be obtained by visiting the node of type Column_definitionContext.
以下是部分常见T-SQL类型、Java类型以及JDBC SET/GET API的对应关系：The following is the correspondence between some common T-SQL types, Java types, and JDBC SET/GET APIs:
然后遍历业务逻辑代码，获取签名信息以及依赖关系，用于后续对业务逻辑代码中的子过程调用进行转换。该过程可以进一步分成两个子过程，首先构建T-SQL业务逻辑代码的抽象语法树；然后遍历抽象语法树，访问特定节点类型，提取签名信息以及依赖信息。Then, the business logic code is traversed to obtain signature information and dependencies, which are used for subsequent conversion of sub-procedure calls in the business logic code. The process can be further divided into two sub-processes, firstly constructing the abstract syntax tree of T-SQL business logic code; then traversing the abstract syntax tree, accessing specific node types, extracting signature information and dependency information.
其中，签名信息包括名称(函数名/存储过程名)、输入参数的信息和返回值的信息。输入参数的数量和返回值的数量都是不限的。输入参数的信息包括名称、类型、顺序、默认值等。每个输入参数或返回值的类型是内置类型、数据库表和自定义类型中的任意一种具体类型。返回值的信息包括名称、类型、顺序等。依赖关系主要体现在业务逻辑代码之间的调用关系，如果函数/存储过程A调用了函数/存储过程B，就认为函数/存储过程A依赖于函数/存储过程B。The signature information includes name (function name/stored procedure name), input parameter information and return value information. The number of input parameters and the number of return values are unlimited. The information of the input parameters includes name, type, order, default value, etc. The type of each input parameter or return value is any one of the built-in types, database tables, and custom types. The information of the return value includes name, type, order, etc. The dependency relationship is mainly reflected in the calling relationship between business logic codes. If function/stored procedure A calls function/stored procedure B, it is considered that function/stored procedure A depends on function/stored procedure B.
自定义函数的签名信息可以通过访问Create_or_alter_functionContext类型的节点获得。自定义存储过程的签名信息可以访问Create_or_alter_procedureContext类型的节点获得。依赖关系可以通过访问子过程调用节点获取，T-SQL中子过程调用节点包含了很多类型，这里就不再一一列举。The signature information of the custom function can be obtained by accessing the node of type Create_or_alter_functionContext. The signature information of the custom stored procedure can be obtained by visiting the node of type Create_or_alter_procedureContext. Dependencies can be obtained by visiting the sub-procedure call node. There are many types of sub-procedure call nodes in T-SQL, so I won't list them all here.
在实际系统中，源码通常分散在很多不同的文件里，需要确定文件的转换顺序。转换顺序主要考虑代码之间的依赖关系，原则上，被依赖方要在依赖方之前进行转换。所以需要基于依赖关系，通过拓扑排序确定转换顺序。In the actual system, the source code is usually scattered in many different files, and the conversion order of the files needs to be determined. The conversion order mainly considers the dependencies between codes. In principle, the relying party should convert before the relying party. Therefore, it is necessary to determine the transformation order through topological sorting based on dependencies.
接下来，进入实际的转换过程。按照前述步骤获取的转换顺序，依次遍历业务逻辑代码，按照转换规则，执行转换过程。完成转换后，每个T-SQL自定义函数/存储过程都转换为独立的Java类，自定义函数/存储过程中的可执行代转换为Java类中的一个静态方法。如图2所示，T-SQL中的ReverseStr自定义函数转化为Java代码后，ReverseStr成为一个Java类，可执行代码转化为这个类中execute方法的方法体。Next, enter the actual conversion process. According to the conversion sequence obtained in the preceding steps, the business logic codes are traversed in turn, and the conversion process is executed according to the conversion rules. After the conversion is completed, each T-SQL custom function/stored procedure is converted into an independent Java class, and the executable code in the custom function/stored procedure is converted into a static method in the Java class. As shown in Figure 2, after the ReverseStr custom function in T-SQL is converted into Java code, ReverseStr becomes a Java class, and the executable code is converted into the method body of the execute method in this class.
以下是部分常见T-SQL语法的转换规则：The following are conversion rules for some common T-SQL syntaxes:
本发明中对Java程序的生成，都是基于通用的抽象语法树AST，所以涉及到建立和操纵AST。在本发明的实施例中，Java的AST是使用Eclipse JDT来创建的。Eclipse JDT可以从零开始创建抽象语法树AST，并且可以将AST转换成源码输出。The generation of the Java program in the present invention is all based on the general abstract syntax tree AST, so it involves the establishment and manipulation of the AST. In an embodiment of the present invention, Java's AST is created using Eclipse JDT. Eclipse JDT can create an abstract syntax tree AST from scratch, and can convert the AST into source output.
实际转换过程主要包括方法签名生成和方法体生成两部分。方法签名基于前述步骤提取的签名信息来生成，主要包括方法名、输入参数名称、输入参数类型和返回值类型。方法体的生成主要包括如下步骤：基于T-SQL业务逻辑代码构建抽象语法树；遍历T-SQL抽象语法树，根据节点类型及其相邻节点类型，按照T-SQL语法的转换规则转换为Java抽象语法树；根据Java抽象语法树生成Java业务逻辑代码。The actual conversion process mainly includes two parts: method signature generation and method body generation. The method signature is generated based on the signature information extracted in the preceding steps, and mainly includes method name, input parameter name, input parameter type and return value type. The generation of the method body mainly includes the following steps: constructing an abstract syntax tree based on T-SQL business logic code; traversing the T-SQL abstract syntax tree, according to the node type and its adjacent node type, and converting it to Java according to the conversion rules of T-SQL syntax Abstract syntax tree; generate Java business logic code according to Java abstract syntax tree.
在生成方法体的过程中，需要对包含子程序调用的SQL语句进行特殊处理，以解决Java代码和SQL代码之间的数据传递问题。这里首先对独立的SQL语句的转换过程进行说明，然后对独立的子程序调用的转换过程进行说明，最后再对包含子程序调用的SQL语句的转换过程进行说明。In the process of generating the method body, special processing needs to be performed on the SQL statement including the subroutine call to solve the problem of data transfer between the Java code and the SQL code. Here, the conversion process of the independent SQL statement is described first, then the conversion process of the independent subprogram call is described, and finally the conversion process of the SQL statement including the subprogram call is described.
不包含子程序调用的SQL语句的转换过程，主要包括如下步骤：处理SQL语句得到SQL字符串，并生成JDBC DECLARE API调用语句；判断SQL语句是否引用了变量，如果引用了变量，则获取对应的Java变量信息，并生成JDBC SET API调用语句；生成JDBC EXECUTE API调用语句；判断SQL语句是否是查询语句，如果不是查询语句，则完成生成；如果是查询语句，则生成JDBC GET API调用语句并生成赋值语句。具体来说，针对JDBC EXECUTE API的每个返回值，首先生成变量声明语句以定义临时变量，然后生成赋值语句，调用对应的JDBCGET API获取返回值，并存储到临时变量。返回值类型主要分为两种，数值类型和数据库表类型。对于返回值为数值类型的子程序调用，转化后的Java代码中，临时变量存储的是真实数值；对于返回值为数据库表类型的子程序调用，转换后的Java代码中，临时变量存储的是表名，并且在转换这些子程序时，将其中用于存储返回值的临时表声明为全局唯一的正式表，并返回正式表的表名。因为，对于返回值为数据库表类型的子程序，它们通常先要在函数体内部创建临时表，再在临时表上进行SQL操作，最后返回临时表。而且调用方在拿到返回值之后，也是进行SQL操作。由此可见，临时表上的操作都需要在数据库中完成。为了减少数据传递，本发明将这些用于存储返回值的临时表声明为全局唯一的正式表。The conversion process of SQL statements that do not include subroutine calls mainly includes the following steps: processing SQL statements to obtain SQL strings, and generating JDBC DECLARE API calling statements; judging whether the SQL statement references variables, and if so, obtain the corresponding Java variable information, and generate a JDBC SET API call statement; generate a JDBC EXECUTE API call statement; determine whether the SQL statement is a query statement, if not, complete the generation; if it is a query statement, generate a JDBC GET API call statement and generate assignment statement. Specifically, for each return value of the JDBC EXECUTE API, first generate a variable declaration statement to define a temporary variable, then generate an assignment statement, call the corresponding JDBCGET API to obtain the return value, and store it in the temporary variable. The return value type is mainly divided into two types, numeric type and database table type. For a subroutine call whose return value is a numeric type, in the converted Java code, the temporary variable stores the real value; for a subroutine call whose return value is a database table type, in the converted Java code, the temporary variable stores the table name, and when converting these subroutines, declare the temporary table in which the return value is stored as a globally unique formal table, and return the table name of the formal table. Because, for subroutines whose return value is database table type, they usually first create a temporary table inside the function body, then perform SQL operations on the temporary table, and finally return the temporary table. And the caller also performs SQL operations after getting the return value. It can be seen that the operations on the temporary table need to be completed in the database. In order to reduce data transfer, the present invention declares these temporary tables for storing return values as globally unique formal tables.
如图3所示，数据表Person包含了name和gender两个数据列，SELECT语句根据name取出对应的gender。首先所有的JDBC API都需要放置在try-catch代码块中，用来捕获数据库执行过程中出现的异常。SELECT语句引用了外部变量name，所以需要生成JDBC SET API。SELECT语句会返回gender，所以需要生成JDBC GET API。As shown in Figure 3, the data table Person contains two data columns, name and gender, and the SELECT statement retrieves the corresponding gender according to the name. First of all, all JDBC APIs need to be placed in try-catch code blocks to catch exceptions that occur during database execution. The SELECT statement refers to the external variable name, so the JDBC SET API needs to be generated. The SELECT statement will return gender, so the JDBC GET API needs to be generated.
独立的子程序调用的转换过程进行说明，主要包括如下步骤：如果调用时使用了可选参数，根据函数定义时声明的参数默认值补全可选参数；如果调用时使用了参数名来指定实际参数与形式参数的对应关系，根据函数定义时声明的形式参数列表顺序生成实际参数列表顺序；生成Java子程序调用语句。此外，很多子程序调用都是对内置子程序的调用，而且这些内置子程序的逻辑都相对简单，可以将它们的转换作为转换规则固定下来。以下是部分常见T-SQL内置子程序的转换规则：The conversion process of the independent subroutine call is described, mainly including the following steps: if optional parameters are used in the call, the optional parameters are completed according to the default values of the parameters declared when the function is defined; if the parameter name is used to specify the actual The corresponding relationship between parameters and formal parameters, the actual parameter list order is generated according to the formal parameter list order declared when the function is defined; the Java subprogram call statement is generated. In addition, many subprogram calls are calls to built-in subprograms, and the logic of these built-in subprograms is relatively simple, and their conversions can be fixed as conversion rules. The following are the conversion rules for some common T-SQL built-in subroutines:
如图2所示，T-SQL中的ReverseStr自定义函数调用了reverse内置函数，本发明将reverse内置函数替换为Java中的StringBuilder.reverse()语法。As shown in Figure 2, the ReverseStr custom function in T-SQL calls the reverse built-in function, and the present invention replaces the reverse built-in function with the StringBuilder.reverse() syntax in Java.
包含子程序调用的SQL语句的转换过程，主要分成三种情况，一是系统内置子程序调用，二是不依赖于SQL语句的自定义子程序调用，三是依赖于SQL语句的自定义子程序调用。其中，如果子程序数调用的输入参数引用了一些数据库表的一些数据列，那么子程序的执行结果就依赖于所在的SQL语句。转换过程主要包括如下步骤：判断子程序类型，如果是系统内置子程序，不需对子程序进行转换，直接生成JDBC API调用语句，因为SQL语句中通常会大量调用这些内置子程序，对其进行转换会造成Java代码的极大膨胀，而且这些内置子程序相对简单，相较而言，转换带来的收益较少；如果是自定义子程序，再判断子程序的执行结果是否依赖于所在的SQL语句，如果不存在依赖，先将自定义子程序的调用提前，转换为Java中的子程序调用语句，并使用局部变量存储返回值，再将SQL语句中的子程序调用改为对返回值的引用，如果存在依赖，则不存在统一的转换方案，需要开发人员根据具体代码人工设计转换方案。The conversion process of SQL statements including subprogram calls is mainly divided into three cases, one is the system built-in subprogram call, the other is the custom subprogram call that does not depend on the SQL statement, and the third is the self-defined subprogram that depends on the SQL statement. transfer. Among them, if the input parameters of the subroutine number call refer to some data columns of some database tables, then the execution result of the subroutine depends on the SQL statement where it is located. The conversion process mainly includes the following steps: judging the type of the subprogram, if it is a built-in subprogram in the system, no need to convert the subprogram, and directly generate the JDBC API call statement, because these built-in subprograms are usually called in large numbers in the SQL statement, and they are processed. Conversion will cause great expansion of Java code, and these built-in subroutines are relatively simple, and in comparison, conversion brings less benefits; if it is a custom subroutine, then determine whether the execution result of the subroutine depends on the SQL statement, if there is no dependency, first advance the call of the custom subprogram, convert it into a subprogram call statement in Java, and use a local variable to store the return value, and then change the subprogram call in the SQL statement to the return value. If there are dependencies, there is no unified conversion scheme, and developers need to manually design the conversion scheme according to the specific code.
以某大型互联网公司为例，其核心ERP系统最初使用T-SQL语言开发，如表1所示，该公司采用本发明的自动化转换技术可大大减少了人力成本，提高了转换效率。由于近年来系统规模不断扩大，T-SQL语言不易重用和扩展的缺点越来越凸显，因此管理人员计划迁移将系统整体迁移到Java语言。使用本发明所述的转换方法后效果如下表所示，可以大幅减少人力投入。Taking a large Internet company as an example, its core ERP system was initially developed using T-SQL language. As shown in Table 1, the company can greatly reduce labor costs and improve conversion efficiency by using the automatic conversion technology of the present invention. Due to the continuous expansion of the system scale in recent years, the shortcomings of the T-SQL language that is not easy to reuse and expand have become more and more prominent, so the administrator plans to migrate the entire system to the Java language. The effect of using the conversion method of the present invention is shown in the following table, which can greatly reduce the manpower input.
表1某公司采用本发明方法进行代码转换情况统计Table 1 A company adopts the method of the present invention to carry out code conversion statistics
Claims (10)
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910994111.1ACN110851142A (en) | 2019-10-18 | 2019-10-18 | Method for converting Transact-SQL program into Java program |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910994111.1ACN110851142A (en) | 2019-10-18 | 2019-10-18 | Method for converting Transact-SQL program into Java program |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110851142Atrue CN110851142A (en) | 2020-02-28 |
Family
ID=69597562
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910994111.1AWithdrawnCN110851142A (en) | 2019-10-18 | 2019-10-18 | Method for converting Transact-SQL program into Java program |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110851142A (en) |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111694846A (en)* | 2020-06-04 | 2020-09-22 | 贵州易鲸捷信息技术有限公司 | Separate mode distributed storage process implementation method based on Type2 JDBC drive |
| CN112346730A (en)* | 2020-11-04 | 2021-02-09 | 星环信息科技(上海)股份有限公司 | Intermediate representation generation method, computer equipment and storage medium |
| CN112346737A (en)* | 2021-01-08 | 2021-02-09 | 深圳壹账通智能科技有限公司 | Method, device and equipment for training programming language translation model and storage medium |
| CN112363727A (en)* | 2020-11-10 | 2021-02-12 | 中国平安人寿保险股份有限公司 | JAVA conversion method and device of SQL (structured query language) code, computer equipment and storage medium |
| CN112711628A (en)* | 2020-12-30 | 2021-04-27 | 北京楚梵基业科技有限公司 | Data interaction method, device and central station |
| CN112765209A (en)* | 2021-02-07 | 2021-05-07 | 浪潮云信息技术股份公司 | SQL statement syntax migration method and system between databases |
| CN114386373A (en)* | 2022-01-13 | 2022-04-22 | 未鲲(上海)科技服务有限公司 | Code statement conversion method and device, computer equipment and storage medium |
| CN116204196A (en)* | 2022-12-30 | 2023-06-02 | 武汉天喻信息产业股份有限公司 | Programming language conversion method, device, electronic equipment and storage medium |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030055820A1 (en)* | 2001-07-30 | 2003-03-20 | Aigen Michael L. | Process for generating enterprise java bean components from an SQL database |
| CN110008448A (en)* | 2019-04-02 | 2019-07-12 | 中国工商银行股份有限公司 | The method and apparatus that SQL code is automatically converted to Java code |
- 2019
- 2019-10-18CNCN201910994111.1Apatent/CN110851142A/ennot_activeWithdrawn
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030055820A1 (en)* | 2001-07-30 | 2003-03-20 | Aigen Michael L. | Process for generating enterprise java bean components from an SQL database |
| CN110008448A (en)* | 2019-04-02 | 2019-07-12 | 中国工商银行股份有限公司 | The method and apparatus that SQL code is automatically converted to Java code |
Non-Patent Citations (1)
| Title |
|---|
| 10KM: "sql2java:一个古老但稳定的轻量级的ORM工具的使用说明", 《HTTPS://BLOG.CSDN.NET/10KM/ARTICLE/DETAILS/74906545》* |
Cited By (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111694846A (en)* | 2020-06-04 | 2020-09-22 | 贵州易鲸捷信息技术有限公司 | Separate mode distributed storage process implementation method based on Type2 JDBC drive |
| CN111694846B (en)* | 2020-06-04 | 2023-08-29 | 贵州易鲸捷信息技术有限公司 | Separation mode distributed storage process implementation method based on Type 2JDBC driver |
| CN112346730A (en)* | 2020-11-04 | 2021-02-09 | 星环信息科技(上海)股份有限公司 | Intermediate representation generation method, computer equipment and storage medium |
| CN112363727A (en)* | 2020-11-10 | 2021-02-12 | 中国平安人寿保险股份有限公司 | JAVA conversion method and device of SQL (structured query language) code, computer equipment and storage medium |
| CN112711628A (en)* | 2020-12-30 | 2021-04-27 | 北京楚梵基业科技有限公司 | Data interaction method, device and central station |
| CN112346737A (en)* | 2021-01-08 | 2021-02-09 | 深圳壹账通智能科技有限公司 | Method, device and equipment for training programming language translation model and storage medium |
| CN112346737B (en)* | 2021-01-08 | 2021-04-13 | 深圳壹账通智能科技有限公司 | Method, device and equipment for training programming language translation model and storage medium |
| CN112765209A (en)* | 2021-02-07 | 2021-05-07 | 浪潮云信息技术股份公司 | SQL statement syntax migration method and system between databases |
| CN114386373A (en)* | 2022-01-13 | 2022-04-22 | 未鲲(上海)科技服务有限公司 | Code statement conversion method and device, computer equipment and storage medium |
| CN116204196A (en)* | 2022-12-30 | 2023-06-02 | 武汉天喻信息产业股份有限公司 | Programming language conversion method, device, electronic equipment and storage medium |
| CN116204196B (en)* | 2022-12-30 | 2025-08-19 | 武汉天喻信息产业股份有限公司 | Programming language conversion method, device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110851142A (en) | Method for converting Transact-SQL program into Java program | |
| Agrawal et al. | TensorFlow Eager: A multi-stage, Python-embedded DSL for machine learning | |
| JP7090778B2 (en) | Impact analysis | |
| CN110187885B (en) | Intermediate code generation method and device for quantum program compiling | |
| Tahboub et al. | How to architect a query compiler, revisited | |
| Klonatos et al. | Building efficient query engines in a high-level language | |
| CN110555032A (en) | Data blood relationship analysis method and system based on metadata | |
| CN110149800B (en) | An apparatus for processing an abstract syntax tree associated with source code of a source program | |
| Rompf et al. | Functional pearl: a SQL to C compiler in 500 lines of code | |
| CA2980333A1 (en) | Field specialization systems and methods for improving program performance | |
| Yang et al. | Powerstation: Automatically detecting and fixing inefficiencies of database-backed web applications in ide | |
| CN115809063B (en) | Storage process compiling method, system, electronic equipment and storage medium | |
| CN117377949A (en) | Methods and systems for providing a context-sensitive, non-intrusive data processing optimization framework | |
| CN111771186B (en) | Compiler-generated asynchronous enumerable objects | |
| Rompf et al. | A SQL to C compiler in 500 lines of code | |
| CN118132086A (en) | A method and system for converting CUDA code into OpenCL code | |
| US8495595B2 (en) | Method of selecting an expression evaluation technique for domain-specific language compilation | |
| CN113641361A (en) | Clang-based code hiding method and device | |
| EP4208779B1 (en) | Transforming operations of a computer program for execution at a database | |
| CN110096274A (en) | The generation code reuse matching management method of distributed memory columnar database | |
| Ceccato et al. | Migrating legacy data structures based on variable overlay to Java | |
| Salucci et al. | Efficient Embedding of Dynamic Languages in Big-Data Analytics | |
| Setiawan et al. | Enhancing Python Object-Relational Mapping Performance using Just-in-Time Compiler | |
| HK40088651A (en) | Transforming operations of a computer program for execution at a database | |
| HK40088651B (en) | Transforming operations of a computer program for execution at a database |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WW01 | Invention patent application withdrawn after publication | ||
| WW01 | Invention patent application withdrawn after publication | Application publication date:20200228 |