-
Notifications
You must be signed in to change notification settings - Fork 15
Handlers
The Handler class in jSqlBox is divided into two categories: ResultSetHandler result set processing class and SqlHandler interceptor class, although they are called Handler class, usage is similar, but their role is not the same, can not be confused.
ResultSetHandler is an interface class supported from DbUtils. In many SQL methods of jSqlBox (mainly inherited from QueryRunner), you can pass the ResultSetHandler class instance as a parameter, for example:
List<Map<String, Object>> result =
Ctx.query("select u.* from DemoUser u where u.age>?", new MapListHandler(),0);
The ResultSetHandler class implementation supported in DbUtils:
ArrayHandler converts the first row of data in the result set into an array of objects
ArrayListHandler converts each row of data in the result set into an array and stores it in the List.
The BeanHandler encapsulates the first row of data in the result set into a Bean instance
The BeanListHandler wraps each row of data in the result set into a Bean instance and stores it in a List.
BeanMapHandler encapsulates each row of data in the result set into a Bean instance and stores it in the Map in the specified column.
ColumnListHandler stores the data of a column in the result set into the List
KeyedHandler assembles the result into a Map<Object, Object> class by the specified primary key
MapHandler encapsulates the first row of data in the result set into a Map
The MapListHandler wraps each row of data in the result set into a Map and then stores it in a List.
ScalarHandler returns a single value type, which is the value of the first column of the first row of the query result.
For details of each ResultSetHandler class, please refer to the DbUtils manual. In addition, a TitleArrayListHandler is added to the jDbPro module, and the result is returned to a List<Object[]> type. The difference between it and the ArrayListHandler is that the first List element is an array, and the content is the title of each column.
(The puller class should be Interceptor in English, but because the naming of Interceptor has been overused by many projects, in order to avoid unnecessary duplicate names, name it SqlHandler in jSqlBox)
The SqlHandler interceptor class is an interface, its instance can only be used for p, i, n, t, e, ActiveRecord series methods (referred to as pintea system method), it is not like the ResultSetHandler can only be used for individual classes, but The pintea method mentioned above can be used, and a method can pass multiple SqlHandler interceptors to form an interceptor chain. It should be noted that the SqlHandler interceptor does not work for SQL methods directly inherited from the QueryRuner series, that is, those that need to manually capture SqlException, because these methods are already very low-level, they are the underlying functions of jSqlBox, rarely directly Use, that is, no need, it is difficult to add a stoper.
The SqlHandler interceptor class implementation in jSqlBox mainly includes:
PrintSqlHandler: This is a debug interceptor that prints the output SQL to the console.
SimpleCacheHandler: This is a caching tool that implements a simple LRU query cache using the memory HashMap
PaginHandler: This is a pagination interceptor that converts plain SQL into paged SQL, which internally calls the paging function of jDialects.
EntityListHandler: used to assemble the List<Map<String,Object>> result set of the SQL query into an entity List
SSHandler: used to convert "select u.** ..." such non-standard SQL (referred to as double-star SQL, see below) to normal SQL
SSMapListHandler: used to process binary SQL and return List<Map<String,Object>>
SSTitleArrayListHandler: is used to process binary SQL and returns a List<Object[]> type. The first List object is the title array of each column.
EntityNetHandler: Used to process binary SQL, and assemble the query results into an EntityNet. For details, see the section on Entity Query.
Explain "Double Star SQL" here, examples are as follows:
EntityNet net = ctx.iQuery(new EntityNetHandler(), User.class, UserRole.class, "select u.**, ur.** from usertb u left join userroletb ur on u.id=ur.userid");
"u.**" This method is translated at runtime into u.name as u_name, u.age as u_age..., because when the jSqlBox entity is associated with a query, when more than two entities are involved, in order to accurately distinguish The database corresponding fields of different entity attributes must be distinguished by the individual name of the field when querying. It is very cumbersome to use manual typing (it is also possible), and the double-star writing method can greatly simplify the typing. Similarly, u.## is also handled specially in jSqlBox, which means that only all primary and foreign key fields are loaded and other fields are ignored.
In any pintea method, all the interceptors corresponding to this class can be disabled by passing a new SqlItem (SqlOption.DISABLE_HANDLERS, xxxHandler.class) as a parameter to the last parameter. An example of its application 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);
}
In the above example, noPagin is a static method introduced in JSQLBOX, which is equivalent to new SqlItem (SqlOption.DISABLE_HANDLERS, PaginHandler.class), which can disable the paging class in the current interceptor. In this example, the user passed a set of parameters, which may or may not have a paging interceptor. The first line in this example, regardless of the three seven twenty-one, disables the paging interceptor first, so the total number is counted, otherwise SQL Will report an error.
Usage 1: Configure as a global interceptor In the SqlBoxContext constructor, you can set a set of interceptors, this set of interceptors will be called when all SQL is executed:
DbContext ctx=new DbContext(ds);
ctx.setSqlHandlers(new PrintSqlHandler(), new SimpleCacheHandler());
Then all the pintea methods will print the SQL output and open the query cache. The scope of the global interceptor is relatively large, so if there is no special reason, it is generally not recommended to configure the global interceptor.
Usage 2: Thread Local Variable Interceptor Using thread-local variables, you can force a set of SqlHandler interceptors to any arbitrary pintea method at any time without having to pass in the parameters of the method body. The following example can force a normal query to join the paging and print SQL functions:
ctx.setThreadLocalSqlHandlers(new PaginHandler(1, 5), new PrintSqlHandler() );
Try{
ctx.nQueryForMapList("select * from users");
} finally{
ctx.getThreadedHandlers().clear();
}
Note: Although jSqlBox automatically clears the thread local variable interceptor after each SQL execution, for security reasons (such as when an exception occurs), this usage must use try...finally to ensure that thread local variables are cleared to avoid A thread-local variable leak caused a serious logic error in the following program.
The custom interceptor class is very simple in jSqlBox. It only needs to implement the SqlHandler interface or inherit from the DefaultOrderSqlHandler class. The former must implement the getOrder method, and the latter is used for occasions where the execution order is not required. When there are multiple interceptors at the same time, if the order value is set smaller, the execution order is higher. The order value of the DefaultOrderSqlHandler class defaults to 100.
For example, customize an interceptor class, print a "Hello" before SQL execution, and print "Bye" after the end, as shown in the following example:
Public static class MyDemoAroundSqlHandler extends DefaultOrderSqlHandler {
@Override
Public Object handle(ImprovedQueryRunner runner, PreparedSQL ps) {
System.out.println("Hello");
Object result = runner.runPreparedSQL(ps);
System.out.println("Bye");
Return result;
}
}
The parameter ImprovedQueryRunner in the above example is defined in the jDbPro project. It is the parent class of DbContext. In the jSqlBox environment, the parameter ImprovedQueryRunner can be cast to the DbContext type.
Finally use a custom interceptor in your program:
List<Map<String, Object>> result2 = pQuery(new MapListHandler(), new MyDemoAroundSqlHandler(), "select u.* from DemoUser u where u.age>?", 50);