Skip to content

DialectAndPagination

Jump to bottom
Yong Zhu edited this page May 7, 2020 · 9 revisions

Dialect and Pagination

All functions related to cross-database such as paging, DDL generation, entity source code generation, primary key, function transformation, and entity annotation analysis of jSqlBox depend on the jDialects module. jDialects is an independent project, supports more than 80 database dialects, supports more than ten major JPA annotations, it can be used by third-party DAO tools, for a detailed introduction to it, please move to its homepage to study: [jDialects homepage] (https://github.com/drinkjava2/jDialects)

When you use the DbContext ctx = new DbContext (dataSource) method to create a DbContext instance, you can use the ctx.getDialect () method to get the Dialect instance of the current data source. In addition, it is strongly recommended to manually specify the dialect type of DbContext. See the "Configuration of jSqlBox" chapter. During the program startup phase, use the DbContext.setGlobalNextDialect () method to set a global default dialect type, which can speed up the creation of DbContext, because By skipping the process of reading database information and guessing the dialect, the dialect type can be specified more clearly.

The TableModel class in jDialects occupies an important position in jSqlBox. TableModel is a virtual data table model that has nothing to do with the actual database. jSqlBox also depends on this virtual table model to support the dynamic configuration of entities. Please see "Fixed and Dynamic" for details. Configuration "chapter. TableModel contains multiple ColumnModels, corresponding to the data table columns.

jDialects has many functions, please see its homepage for details, here is only a brief introduction:

  1. Cross-database DDL script generation function String [] ddls = ctx.toCreateDDL (HelloWorldTest.class); ctx.toCreateDDL () is equivalent to ctx.getDialect (). toCreateDDL method, which can convert the entity class into a DDL script corresponding to the current database. Similarly, there are toDropDDL method (generate delete script) and toDropAndCreateDDL method (generate delete and create script).

  2. Generate Java entity class source code from the database The following statement will read all tables in the database and generate entity class source code corresponding to all database tables in the specified "c:\temp" directory:

  Map <String, Object> setting = new HashMap <String, Object> ();
  setting.put (TableModelUtils.OPT_PACKAGE_NAME, "somepackage"); // Package name
  setting.put (TableModelUtils.OPT_REMOVE_DEFAULT_IMPORTS, false); // Whether to remove the imports statement
  setting.put (TableModelUtils.OPT_IMPORTS, "import java.util.Map; \ n"); // Add custom imports statement
  setting.put (TableModelUtils.OPT_CLASS_DEFINITION, "public class $ 1 extends ActiveRecord <$ 1>"); // Class definition
  setting.put (TableModelUtils.OPT_LINK_STYLE, true); // Chain setter style
  setting.put (TableModelUtils.OPT_FIELD_FLAGS, true); // Whether to build a global static uppercase field constant for each field
  setting.put (TableModelUtils.OPT_PUBLIC_FIELD, false); // Whether to set the entity field attribute to public
  setting.put (TableModelUtils.OPT_EXCLUDE_TABLES, Arrays.asList ("Dbsample")); // Exclude individual tables

  TableModelUtils.db2JavaSrcFiles (dataSource, Dialect.MySqlDialect, "c: / temp", setting);

The first parameter of the db2JavaSrcFiles method is the data pool, the second parameter is the dialect, the third parameter is the output directory, and the fourth parameter is the detailed setting, which is the setting in the above example.

  1. Paging function Wherever SQL text appears, dialect.pagin (pageNumber, pageSize, sql) method can be used to transform a common SQL into a paging SQL corresponding to the dialect of the current database. getDialect (). pagin () method. Pagination example: List <Map <String, Object >> users = ctx.iQuery (new MapListHandler (), ctx.pagin (2, 5, "select concat (firstName, '', lastName) as UserName, age from users where age>?" ), 50);

jSqlBox can also use paging interceptors for paging, for example: List users = ctx.iQuery (new EntityListHandler (User.class), new PaginHandler (2, 5), "select * from users where age>?", Param (0));

Using the method of statically introducing the DB class, the above example can also be abbreviated as follows: List users = eFindAll (User.class, "where age>?", Param (0), pagin (2, 5)); Parameters such as interceptors can appear anywhere in most SQL methods and CRUD methods. This is a feature of the jSqlBox project (see iXxxx and other methods for details). Unlike other DAO tools, jSqlBox does not have a specifically defined paging query method , But in almost all SQL methods and CRUD methods can be passed into the paging interceptor.

When you are not sure whether there is a paging interceptor, you can also pass in noPagin (), which is an example of a paging interceptor, which is as follows:

public void pageQuery (Object ... conditions) {
 iQueryForLongValue ("select count (1) from sys_user where 1 = 1", conditions, noPagin ());
 iQueryForEntityList (DemoUser.class, "select * from sys_user where 1 = 1", conditions, "order by id");
}

There is also an uncommon and non-intrusive paging method that uses the ThreadLocal technique (see the section on the interceptor of jSqlBox for details). The above example can also be written as:

DbContext.setThreadLocalSqlHandlers (pagin (2, 5));
try {
 ctx.nQueryForMapList ("select * from users");
} finally {
 ctx.getThreadedHandlers (). clear ();
}

  1. Function transformation function The functions supported by different databases are very different. Using jDialects, where SQL text appears, dialect.trans (sql) can be used to transform a SQL containing "universal functions" into native SQL corresponding to the current database dialect , The ctx.trans () method is equivalent to the ctx.getDialect (). trans () method.

  2. Combination of paging and function transformation The ctx.paginAndTrans (pageNumber, pageSize, sql) method can simultaneously perform paging and function transformation on sql.

  3. The exchange of Java type and JDBC SQL parameter type In the TypeUtils class of jDialect, there are some type interchange tool methods, such as: jdbcValue2JavaValue: Convert the value returned by the JDBC query to a Java type, for example, to convert a java.sql.Date instance to a Calendar instance. javaParam2JdbcSqlParam: Convert Java values ​​to JDBC SQL query parameters, for example, convert LocalDate instances to java.sql.Date instances.

jDialects supports the mapping between the following entity attribute types and JDBC types by default:

BigDecimal.class => Type.NUMERIC
BigInteger.class => Type.BIGINT
Boolean.class => Type.BOOLEAN
Byte.class => Type.TINYINT
Character.class => Type.CHAR
Double.class => Type.DOUBLE
Float.class => Type.FLOAT
Integer.class => Type.INTEGER
Long.class => Type.BIGINT
Short.class => Type.SMALLINT
String.class => Type.VARCHAR
java.sql.Clob.class => Type.CLOB
java.sql.Blob.class => Type.BLOB
java.util.Date.class => Type.DATE
java.sql.Date.class => Type.DATE
java.util.Calendar.class => Type.DATE
java.sql.Time.class => Type.TIME
java.sql.Timestamp.class => Type.TIMESTAMP
java.time.LocalDate.class => Type.DATE
java.time.LocalTime.class => Type.TIME
java.time.OffsetTime.class => Type.TIME
java.time.Instant.class => Type.TIMESTAMP
java.time.LocalDateTime.class => Type.TIMESTAMP
java.time.OffsetDateTime.class => Type.TIMESTAMP
java.time.ZonedDateTime.class => Type.TIMESTAMP

When jDialects generates a TableModel model for an entity class, if the attribute of the entity class belongs to one of the types listed above, a corresponding ColumnModel will be created in the TableModel and its TYPE type will be marked (corresponding to the type of the same name in java.sql.Types ), Conversely, if it does not belong to the Java type listed above (unless it is a custom type enumerated or @Convert annotation), jDialects will ignore this entity attribute. In addition, if you want to force an entity attribute to be ignored, you can add @Transient annotation on the entity attribute, which is a standard JPA annotation, indicating that this field does not participate in persistence.

Generally, the default mapping relationship of entity attributes is sufficient, but if you want to change the mapping of default field attributes to database column types, you can use the columnDefinition in the standard @Column annotation to change, for example, the following entity attribute definitions are allowed, of course The premise is that the database itself must support these database column types and can be converted to each other:

  @Column (columnDefinition = "timestamp")
  java.util.Date d5;

  @Column (columnDefinition = "timestamp not null") // JPA allow add extra string
  java.sql.Date d6;

  @Column (columnDefinition = "timestamp")
  java.sql.Time d7;

  @Column (columnDefinition = "date")
  java.sql.Time d11;

  @Column (columnDefinition = "date")
  java.sql.Timestamp d12;

  @Column (columnDefinition = "time")
  java.sql.Timestamp d16;
  1. Primary key generation and JPA annotation support jDialects supports ten primary key generation methods and annotations, a distributed primary key generator (snowflake algorithm), and is compatible with major JPA annotations. Please refer to its homepage and jSqlBox sub-library sub-table section. Commonly used are @ UUID25, @ UUID32, @ UUID36, which respectively represent unique UUIDs of different lengths generated by a certain algorithm. These UUID annotations cannot replace @Id annotations. If it is a primary key column, you must also add @Id annotations.

The development purpose of jDialects is to share it as an independent tool. It has a separate release package on Maven, and it does not have any third-party dependencies. Conversely, jSqlBox relies on the jDialects module and is included in the project in an embedded manner.

Clone this wiki locally