SqlStyles
jSqlBox inherits from the DbUtils kernel and has no session architecture design, which makes it support a variety of SQL writing methods, from the lowest level of manual JDBC operations to the topmost ActiveRecord and object auto-association queries. From the perspective of SQL writing, almost It can be said that it is a full stack, it does not have the fault phenomenon that often occurs in other persistence layer tools, that is, support for ORM, often the underlying SQL support is not good (Hibernate), too close to the underlying (DbUtils/JdbcTemplate), ORM function is not complete.
The following is a quick overview of the various SQL writing methods. For details on each method, please click the menu on the left.
SQL style 1 - with the Connection parameter
Manually get and close the connection, this is the SqlBoxContext inherited from DbUtils QueryRunner method (SqlBoxContext is a subclass of QueryRunner), consistent with the use of QueryRunner. This way you must manually catch a SQLException.
Connection conn = null;
Try {
Conn = ctx.prepareConnection();//equivalent to ds.getConnection();
Ctx.execute(conn,
"insert into users (name,address) values(?,?)", "Tom", "China");
} catch (SQLException e) {
doSomething(e);
} finally {
Try {
Ctx.close(conn);
} catch (SQLException e) {
doSomething(e);
}
}
SQL style 2 - no Connection parameter
These are alose methods inherited from QueryRunner, do not need to care about the connection to get and close, use this method to pay attention to the SqlBoxContext must provide a dataSource constructor. This method must also manually catch a SQLException.
Try {
Ctx.execute("insert into users (name,address) values(?,?)", "Sam", "Canada");
} catch (SQLException e) {
doSomething(e);
}
SQL style 3 - n series methods
New methods added from the jDbPro module, starting with the lowercase letter "n". Instead of manually catching the SqlException, it turns it into a runtime exception throw. This is usually used in conjunction with AOP tools that support declarative transactions such as Spring (see jBooox, jsqlbox-spring, jsqlbox in the demo). Example projects such as in-springboot).
ctx.nExecute("insert into users (name,address) values(?,?)", "Tom", "China");
SQL style 4 - i series methods
Write parameters directly in SQL, SQL is automatically converted to preparedStatement when executed, the advantage of this method is that the assigned field and the actual parameters can be written on the same line, the field is good for maintenance, also It is convenient to dynamically splicing SQL according to uncertain conditions. The SQL parameters in this method must be enclosed by the param() method. If it is a SqlResultHandler, a puller, a Connection, a SqlBoxContext, a SqlItem, etc., you do not need to bracket it. String types are considered part of SQL if they are not enclosed by the param method.
iExecute("insert into users (", //
notNull("age, ", u.getAge()), //
" name ,", param("Tom"), //
" address ", param("China"), //
") ", valuesQuesions());
jSqlBox uses an architecture similar to the Windows message mechanism. All related elements of SQL are classified as a type, separated by commas. Except for SQL text and parameters, the order in which other elements appear is usually irrelevant.
In the above example, iExecute, param, etc. are imported statically using import static com.github.drinkjava2.jsqlbox.JSQLBOX.*; JSQLBOX is not the core class of jSqlBox. It defines a large number of static methods that can be directly introduced statically to simplify programming. After setting a default global instance of SqlBoxContext in the system, you can directly reference a series of static methods such as n, i, p, t, e in the JSQLBOX tool class, for example:
SqlBoxContext.setGlobalSqlBoxContext(new SqlBoxContext(someDataSource)); //When the system starts
Import static com.github.drinkjava2.jsqlbox.JSQLBOX.*; // static introduction tool class in the program
iExecute("delete from users where userId=?",param(1)); //Use static methods directly!
The use of static methods can reduce the invasion of business code by the persistence layer context instance (SqlBoxContext), but is limited to single data source occasions.
SQL style 5 - INLINE uppercase series methods
Write parameters directly in SQL, parameters temporarily stored in ThreadLocal, this method is derived from jSqlBox, but is no longer recommended from jSqlBox2.0 (because ThreadLocal does not support nested calls) It is only used for some other persistence layer tools such as JdbcTemplate. These tools only allow SQL to be a single string. In this case, you can use this method to force the field to be assigned and the actual parameters to be written on the same line. maintain. (That is, this is written for use by other third-party persistence tools, not for the jSqlBox itself.)
jdbcTemplate.execute("insert into users (" //
+ " name " + PARA0("Sam") //
+ " ,address" + PARA("Canada") //
+ ")"//
+ VALUESQUES(), PARAMS());
The difference between this method and the iXxxx series method is that the method names are all uppercase, and are introduced statically from the JSQLBOX tool class, and the first parameter must use the PARA0() method to clear the Threadlocal parameter in the previous memory, otherwise it will cause (business In the sense of memory leaks, affecting the next SQL call, this is a pit to pay attention to.
SQL style 6 - p series methods
Contrary to the iXxxx method, in addition to the first SQL string that appears, all SQL strings must be enclosed in sql () method, all parameters are not enclosed (some special parameters such as Connection Except for SqlBoxContext, etc.) are treated as SQL parameters.
ctx.pExecute("insert into users (name, age) ", "Sam", 10, valuesQuestions());
pExecute("update users set name=?", "Tom", sql(", age=?"), 5);
SQL style 7 - t series methods
Template style, placeholders in SQL, through the template engine to interpret these placeholders as ordinary SQL:
User sam = new User("Sam", "Canada");
User tom = new User("Tom", "China");
paramMap.put("user", sam);
ctx.tExecute("insert into users (name, address) values(#{user.name},:user.address)", paramMap);
tExecute("update users set name=#{user.name}, address=:user.address", bind("user", tom));
jSqlBox comes with a configurable simple template that defaults to use the "#{}" placeholder or the ":" placeholder and can be mixed. The jSqlBox template engine is free to switch. In the jsqlbox-beetlsql directory of the demo, there is an example of a template that demonstrates jSqlBox using a custom template to use BeetlSql.
SQL style 8 - e series methods (Data Mapper style)
Similar to the way session.save () in Hibernate. In terms of ease of use, jSqlBox suggests that entity classes inherit from the ActiveRecord class, but for pure POJO classes, jSqlBox is also supported. And unlike Hibernate, jSqlBox does not turn a POJO into a proxy class. A POJO is always a POJO. Moreover, all of the CRUD methods of jSqlBox (including the CRUD method of ActiveRecord in the next section) allow additional SQL entries to be added to the method for maximum flexibility. The e-series method starts with the letter e, indicating that it performs CRUD operations on a single entity (Entity). See the "e-series methods" section for details on which e-series methods are available.
User user = new User("Sam", "Canada");
eInsert(user);
SQL style 9 - ActiveRecord style
Inherited from the ActiveRecord class entity class, automatically has insert, update, delete, load and other CRUD methods. The ActiveRecord method has no lowercase prefix.
Public class User extends ActiveRecord{ ... }
User user= new User();
user.setName("Sam");
User.insert(ctx); //Context can be passed as a parameter
For special occasions, the entity cannot inherit from the ActiveRecord class. If it is easy to solve under Java8, as long as the ActiveEntity interface is implemented, the CURD method is automatically owned without any method. See the demo\jsqlbox-java8-demo directory for details. Example.
The following is a variant of the ActiveRecord style. The premise of using this method is that the SqlBoxContext.setGlobalSqlBoxContext(ctx) method must be called at program startup to set a global default context for single data source occasions. The advantage of this approach is that the shadow of the persistence layer tool is completely invisible in the business method:
User user = new User("Sam", "Canada").insert();
The CRUD methods of other persistence layer tools are usually parameterless, but in jSqlBox, a series of SQL and CRUD methods such as n, i, p, t, and e allow to insert various SQL entries (SqlItem), such as the following ActiveRecord Update statement:
New User("Sam","Zhu","China").update(ctx2," and age>?", param(5),new PrintSqlHander());
Do these things in the background: switch to ctx2 SqlBoxContet (operate another data source), add "and age>?" fragment and parameters in SQL and execute, print SQL to the console.
SQL style 10 - Tail and hybrid models
It is the two assignment methods of the ActiveRecord class and its word class. Because the writing method is special, it is listed separately. These two methods are not recommended for large-scale use in projects because they do not support refactoring.
- Direct assignment based on entity attributes
/ / One of the chain assignment based on the entity attribute
New User().putField("id","u1").putField("userName","user1").insert();
/ / Based on the attribute assignment of the chain attribute
New Address().putField("id","a1","address","address1","userId","u1").insert();
/ / Based on the entity attribute of the batch assignment
New Email().forFields("id","emailName","userId");
New Email().putValues("e1","email1","u1").insert();
New Email().putValues("e2","email2","u1").insert();
For ActiveRecord entities, it is recommended that setters be also chained. jDialect also supports automatic generation of chain style setter entity class source code. See “Paging, DDL, Source Generation” for details. Assignments based on entity attributes can be replaced with ordinary setters/getters. The only advantage is that you can write assignments as a single line.
- Based on the chain name of the database table field name, it does not need the corresponding field name in the entity class, so it is also called Tail mode. In the Tail mode, in the CRUD method, a tail (table name) parameter must be added. Indicates the name of the database table for the current operation. All tail fields will be mixed with the entity fields to participate in the CRUD operation, otherwise all tail fields will be ignored. jsqlBox's ActiveRecord is a hybrid model that works in two modes: "by entity field name" and "by entity field name + tail field name mixed mode", the difference is whether there is a tail parameter.
/ / One of the chain assignment based on the database table field name (Tail mode, the tail method parameter is empty, indicating the use of the same database table as the entity):
New User().putTail("id","u1").putTail("user_name","user1").insert(JSQLBOX.tail());
/ / Based on the database table field name chain assignment two (Tail mode, tail method static introduction)
New Address().putTail("id","a1","usr_address","address1","user_id","u1").insert(tail());
/ / Based on the batch assignment of the database table field name (Tail mode, batch assignment)
user.forTails("user_name", "age", "birth_Day");
user.putValues("Foo", 30, new Date()).insert(tail("user_tb"));
user.putValues("Bar", 40, new Date()).insert(tail("user_tb"));
//tail mode and normal getter/setter can be mixed: new User().setId("u1").putTail("usr_addr","Beijing").putField("userName","Tom").update() ;
//For ActiveRecord classes or classes that implement the TailType interface, jSqlBox will add a query column that is not mapped to the entity field name to tail, without the tail parameter:
User u = eLoadBySQL(User.class, "select *, 'China' as user_addr from tail_demo");
System.out.println(u.getTail("user_addr")); //user_addr has no corresponding entity field, is a tail column
When you don't want to define an entity class, you can directly use a Tail class to operate the database. The Tail class is a subclass of ActiveRecord, which is equivalent to the exception of the entity class whose field name is empty:
net Tail().putTail("id","id1","u_name","Foo").insert(tail("t1").update(tail("t2").load(tail("t3")).delete(tail("t4"));
The ActiveRecord class is not the core class of jSqlBox. It has less than 300 lines of source code. If the user wants to use a different method name (for example, the method name conflicts with the user's entity method name) or does not want it to implement the TailType interface, you can refer to the existing ActiveRecord source code, write your own ActiveRecord class. Similarly, JSQLBOX is not the core class of jSqlBox, users can also refer to it to write their own static method tool class.
SQL style 11 According to the entity template to query
In DataMapper style (e series method) and ActiveRecord two modes, there are methods based on the entity template to query, given an entity bean as a template, and then the system automatically generates SQL according to this template, Thus loading objects similar to the template from the database, examples are as follows:
User u1=new User();
u1.setId(1);
u1.setName("Tom");
u1.setAddress(null);
List<User> users=ctx.eFindBySample(u1);
The system will automatically generate a SQL similar to "select * from users where id=? and name=?" according to the content of the template u1 and substitute the two parameters "1", load the qualified records from the database, and return a record. List collection. As can be seen from the generated SQL, the conditions it automatically generates are "=", and are connected with "and", and ignore the null value. What should I do if I encounter complicated template conditions? Don't worry, see "Press The entity template query section.
SQL style 12 - SQL-based ORM query
When there are more than two objects need to be assembled into a network structure of related objects, you can add EntityNetHandler interceptor to a series of Sql query methods, this kind of query must be given Each table has an alias. Alias can be used to specify the alias. If not specified, the system defaults to the lowercase of the uppercase letter in the class name (how to say this).
Private static final Object[] targets = new Object[] { new EntityNetHandler(), User.class, UserRole.class,
Role.class, RolePrivilege.class, Privilege.class, giveBoth("r", "u"), giveBoth("p", "u") };
Public void testAutoAlias() {
insertDemoData();
EntityNet net = ctx.iQuery(targets, "select u.**, ur.**, r.**, p.**, rp.** from usertb u ", //
" left join userroletb ur on u.id=ur.userid ", //
" left join roletb r on ur.rid=r.id ", //
" left join roleprivilegetb rp on rp.rid=r.id ", //
" left join privilegetb p on p.id=rp.pid ");
List<User> userList = net.pickEntityList("u");
For (User u : userList) {
System.out.println("User:" + u.getId());
Set<Privilege> privileges = u.getPrivilegeSet();
If (privileges != null)
For (Privilege privilege : privileges)
System.out.println(" Privilege:" + privilege.getId());
}
}
The give("r","u") method assigns the entity corresponding to the alias "r" to the corresponding attribute field of the entity corresponding to "u". The field name must have the same name as the class name (case insensitive), and some follow-up The collection class attribute of the List/Set/Map suffix, in the above example, the role and roleList attribute fields in the User class will be automatically assigned. The giveBoth method is a two-way assignment. When the naming of entity class attributes is irregular, you can't simply use the use of give(a, b), but you must explicitly indicate the field name of the assigned entity class, such as give("r","u","allRoles "). SQL-based object-associated queries, as the name implies, are based on SQL, which means that if the query results are on the same line, the system forcibly considers that the corresponding entities have an association relationship, so this method is closely related to the SQL writing method. Entity objects also support Tail if they are ActiveRecord or TailType. For example,
iQuery("select u.**, u.name as u_newname...");
you can use u.getTail("newname") to get the tail content.
Entity association query is a bright spot in jSqlBox, there are many innovations, such as: Automatic leap-level association, one-word solution to the object association query across 5 tables, it will automatically generate SQL, the same function in the above example:
List<User> users = ctx.entityAutoNet(User.class, UserRole.class, Role.class, RolePrivilege.class, Privilege.class)
.pickEntityList(User.class);
There is no oneToOne, oneToMany configuration in jSqlBox. It borrows the foreign key configuration of jDialects entity class to automatically generate SQL. Please refer to the section “Entity Object Association Query” for details.
Tree structure association query, can use one SQL get any subtree without use recursively query, and automatically assembles into an object association structure:
EntityNet net = ctx.pQuery(targets,
"select t.**, t.pid as p_id from treenodetb t where t.line>=? and t.line< (select min(line) from treenodetb where line>? and lvl<=?) ", d.getLine (), d.getLine(), d.getLvl());
TreeNode node = net.pickOneEntity("t", d.getId());
printTree(node, 0);
}
For the above two examples, please refer to the chapter "Object Correlation Query" and "Tree Structure Query".
SQL style 13 - Support for refactoring SQL writing
SQL is a text format and does not support field refactoring. In order to implement SQL that supports refactoring, other persistence layer tools often use Java methods instead of SQL keywords, resulting in repeated inversion of SQL, such an anti-pattern, poor readability, and unfavorable porting. jSqlBox does not reinvent SQL. In order to allow SQL to support refactoring, in addition to the usual means of replacing field names with constants, jSqlBox gives two new ways of writing:
//traditional way
User u = createAliasProxy(User.class, "u"); //"u" can be omitted
List<Map<String, Object>> list = giQueryForMapList(clean(), //
"select"//
, alias(u.getId())//
, ", ", alias(u.getAddress())//
, ", ", alias(u.getName())//
, " from ", table(u), " where "//
, col(u.getName()), ">=?", param("Foo90") //
, " and ", col(u.getAge()), ">?", param(1) //
);
//Java8 Lambda mode
User u = createAliasProxy(User.class);
List<?> list1 = giQueryForMapList( //
"select"//
, (ALIAS) u::getId//
, (C_ALIAS) u::getAddress //
, (C_ALIAS) u::getName //
, " from ", table(u), " where "//
, (COL) u::getName, ">=?", param("Foo90") //
, " and ", (COL) u::getAge, ">?", param(1) //
);
Its basic principle is to use the proxy class to return the field name itself. The sample source code can be found under demo\jsqlbox-java8-demo. Because of the limitations of the Java language, Lambda cannot be directly passed as a parameter to the pintea series of methods. It must be used as a cast by COL, ALIAS, etc. This is a pity, and it is a bit cumbersome to use. In addition, the user can imitate the C_ALIAS, COL source code to write simplified methods such as A, C, L and other single-letter methods for more convenient use.
The above is just a general introduction, please refer to the left menu link for details of each usage. The methods in jSqlBox are all set. If you can't remember, don't worry, learn jSqlBox is not heavy. Just remember the word "pintea", which represents the six major methods of p, i, n, t, entity, activeRecord, using the IDE's automatic prompting function, in addition to ActiveRecord, the first method of knocking The letters will pop up all the methods of this system, and guess the method to dry up according to the literal meaning. These six methods are similar except for the first letter. Learning a set is equivalent to learning a full set of pintea series methods.