JDialects: A Native SQL paging tool that extracts from Hibernate and supports more than 70 database dialects, jdialectshib.pdf

JDialects: A Native SQL paging tool that extracts from Hibernate and supports more than 70 database dialects, jdialectshib.pdf

JDialects (https://git.oschina.net/drinkjava2/jdialects) is a small Java project that collects the majority of known database dialects and is usually used to create paging SQL and table creation DDL statements that generate different SQL statements based on different database dialects. Currently, jDialects supports 75 database dialects, including SQLLite and Access, which are not available in Hibernate. JDialects must be supported by Java 7 or later.

JDialects was initially developed for the jSqlBox project, but it is an independent project (the release package is only 90 kb in size and has no other third-party dependencies), as long as SQL is used, you can use it to create paging SQL and DDL for the corresponding database. For example, the persistent layer tools such as pure JDBC, JdbcTemplate, and DbUtils Based on operating native SQL are used, this tool can be used to achieve cross-database paging when cross-database requirements exist (for example, unit tests must run on both the H2 memory database and the actual database Oracle. Or, like me, you are developing a persistent layer tool that supports multiple databases. You can also consider using jDialects.

The main part of the jDialects project is automatically generated by the Code Generation Tool extracted from Hibernate5.2.9, which guarantees its code quality to a certain extent. For code generation tools, see the jDiagen (https://github.com/drinkjava2/jDiagen) project.

How to introduce jDialects to a project?
Download the "jdialects-1.0.1.jar" and place it in the project library directory, or add the following to the project's pom. xml file:

Java code

1. <Dependency>
2. <GroupId> com. github. drinkjava2 </groupId>
3. <ArtifactId> jdialects </artifactId>
4. <Version> 1.0.1 </version>
5. </Dependency>

Use in programs
1) create a cross-database page:
Dialect d = Dialect. guessDialect (dataSource); // determine the Dialect type based on the data source,
// Dialect d = Dialect. guessDialect (connection); // determine the Dialect type based on the connection.
// Dialect d = Dialect. MySQL5Dialect; // or manually specify the database Dialect type
String result = d. paginate (3, 10, "select * from users where id =? "); // Create a paging SQL statement

When the dialect is MySQL5Dialect, the result is: "select * from users where id =? Limit 20, 10"
When the dialect is Oracle8iDialect, the result is: "select * from (select row _. *, rownum _ from (select * from users where id =? ) Row _) where rownum _ <= 30 and rownum _> 20"
When the dialect is Oracle12cDialect, the result is: "select * from users where id =? Offset 20 rows fetch next 10 rows only"
When the dialect is Sybase11Dialect, A DialectExcepiton exception is thrown and the message "Sybase11Dialect" does not support physical pagination
...

2) create a cross-database DDL:

Java code

1. Private static String ddlSQL (Dialect d ){
2. Return "create table" + d. check ("BufferPool") + "("//
3. + D. BIGINT ("f1 ")//
4. + "," + D. BIT ("f2", 5 )//
5. + "," + D. BLOB ("f3 ")//
6. + "," + D. BOOLEAN ("f4 ")//
7. + "," + D. INTEGER ("f5 ")//
8. + "," + D. VARCHAR ("f6", 8000 )//
9. + "," + D. NUMERIC ("ACCESS_LOCK", 8, 2 )//
10. + ")" + D. engine ("default charset = utf8 ");
11. }
12. Public static void main (String [] args) {// run on different dialects
13. System. out. println (ddlSQL (Dialect. MySQL57InnoDBDialect ));
14. System. out. println (ddlSQL (Dialect. SQLServer2012Dialect ));
15. System. out. println (ddlSQL (Dialect. Oracle10gDialect ));
16. }

The running result of this example is:
Create table BufferPool (f1 bigint, f2 bit, f3 longblob, f4 bit, f5 integer, f6 varchar (8000), ACCESS_LOCK decimal () engine = innoDB default charset = utf8
Create table BufferPool (f1 bigint, f2 bit, f3 varbinary (MAX), f4 bit, f5 int, f6 varchar (MAX), ACCESS_LOCK numeric (8, 2 ))
Create table BufferPool (f1 number (), f2 number (), f3 blob, f4 number (), f5 number (), f6 long, ACCESS_LOCK number ))

In this example, a log warning output is displayed: "BufferPool" and "ACCESS_LOCK" are reserved words for DB2 and Teradata respectively. This means that DialectException exceptions will be thrown if the database runs on DB2Dialect or TeradataDialect. To ensure portability, it is best to change the field with a warning prompt to another name.
To skip the reserved word check (not recommended), you can also write DDL in the following format:

Java code

1. Ddl = "create table BufferPool ("//
2. + "F1" + d. BIGINT ()//
3. + ", F2" + d. BIT (5 )//
4. + ", F3" + d. BLOB ()//
5. + ", F4" + d. BOOLEAN ()//
6. + ", F5" + d. INTEGER ()//
7. + ", F6" + d. VARCHAR (8000 )//
8. + ", ACCESS_LOCK" + d. NUMERIC (8, 2 )//
9. + ")" + D. engine ();

All types supported by jDialects are defined as follows:
BOOLEAN
DOUBLE
FLOAT
INTEGER
LONG (= BIGINT)
SHORT (= SMALLINT)
BIGDECIMAL (= NUMERIC)
STRING (= VARCHAR)
DATE
TIME
TIMESTAMP
BIGINT
BINARY
BIT
BLOB
CHAR
CLOB
DECIMAL
LONGNVARCHAR
LONGVARBINARY
LONGVARCHAR
NCHAR
NCLOB
NUMERIC
NVARCHAR
REAL
SMALLINT
TINYINT
VARBINARY
VARCHAR

3) SQL Functions
Currently, jDialects does not support cross-Database SQL functions, mainly because SQL functions are usually in two situations in different databases. One is that the names and parameters are identical, and the other is that the changes are very large, many are proprietary functions. both the former and the latter are not suitable for general SQL functions. In this regard, the jdialectscode generation tool writes the 75-plus words in the comparative databasedialects.xls file (which also contains the paging and Type Definitions of various dialects). It can be used as a quick reference manual for database porting.

The above are all jDialects documents. If you have any questions, you can view the project source code and unit test. At last, it is emphasized that jDialects is only a text conversion tool. It performs different transformations on SQL statements based on different dialects. It is not a complete persistence tool, must be used with other persistence tools, such as JDBC/DbUtils.

Appendix-below are 75 database dialects currently supported by jDialects:
AccessDialect
Cache71Dialect
CobolDialect
CUBRIDDialect
DataDirectOracle9Dialect
DB2390Dialect
DB2400Dialect
DB2Dialect
DbfDialect
DerbyDialect
DerbyTenFiveDialect
DerbyTenSevenDialect
DerbyTenSixDialect
ExcelDialect
FirebirdDialect
FrontBaseDialect
H2Dialect
HANAColumnStoreDialect
HANARowStoreDialect
HSQLDialect
Informix10Dialect
InformixDialect
Ingres10Dialect
Ingres9Dialect
IngresDialect
InterbaseDialect
JDataStoreDialect
MariaDB53Dialect
MariaDBDialect
MckoiDialect
MimerSQLDialect
MySQL55Dialect
MySQL57Dialect
MySQL57InnoDBDialect
MySQL5Dialect
MySQL5InnoDBDialect
MySQLDialect
MySQLInnoDBDialect
MySQLMyISAMDialect
Oracle10gDialect
Oracle12cDialect
Oracle8iDialect
Oracle9Dialect
Oracle9iDialect
OracleDialect
ParadoxDialect
PointbaseDialect
PostgresPlusDialect
PostgreSQL81Dialect
PostgreSQL82Dialect
PostgreSQL91Dialect
PostgreSQL92Dialect
PostgreSQL93Dialect
PostgreSQL94Dialect
PostgreSQL95Dialect
PostgreSQL9Dialect
PostgreSQLDialect
ProgressDialect
RDMSOS2200Dialect
SAPDBDialect
SQLiteDialect
SQLServer2005Dialect
SQLServer2008Dialect
SQLServer2012Dialect
SQLServerDialect
Sybase11Dialect
SybaseAnywhereDialect
SybaseASE157Dialect
SybaseASE15Dialect
SybaseDialect
Teradata14Dialect
TeradataDialect
TextDialect
TimesTenDialect
XMLDialect