Learning Beetlsql Summary (2)--query API, update API

Learning Beetlsql Summary (2)
After the last section of the study, we have learned the basis of beetlsql, and then we learn in depth beetlsql
A Beetlsql description
1. Acquiring Sqlmanager is the core of the system, which provides all the DAO methods, obtains the Sqlmanager, can construct the Sqlmanager directly, and obtains through the singleton

ConnectionSource source = ConnectionSourceHelper.getSimple(mysqlDriver, url, userName, password);        DBStyle mysql = new MySqlStyle();        // SQL语句放于classpath的sql目录下        SQLLoader loader = new ClasspathLoader("/sql");        // 数据库命名和java命名一样，所以采用DefaultNameConversion,还有一个UnderlinedNameConversion下划线风格的        UnderlinedNameConversion nc = new UnderlinedNameConversion();        // 最后，创建一个SQLManager,DebugInterceptor,不是必须的，但可以通过它查看SQL的执行情况        SQLManager sqlManager = new SQLManager(mysql, loader, source, nc, new Interceptor[] { new DebugInterceptor() });

If you already have datasource, creating a Connectionsource can take the following code:

ConnectionSource source = ConnectionSourceHelper.getSingle(datasource);

If it is a master-slave datasource:

ConnectionSource source = ConnectionSourceHelper.getMasterSlave(master,slaves)

2. Query API
(1) Simple query (automatically generate SQL)
public T Unique (Class clazz,object PK) query based on primary key, throw exception if not found
Public T single (Class clazz,object PK) query based on primary key, return NULL if not found
public List All (Class clazz) queries all result sets
public List All (Class clazz,int start,int size) page
public int Allcount (class<?> clazz) Total
(2) Single-table query
Sqlmanager provides a query class that enables single-table querying operations

 //7.单表查询         System.out.println("开始执行单表查询");         List<User> list =         sqlManager.query(User.class).andEq("name","曼斯坦因").orderBy("create_date").select();         System.out.println("打印查询结果：");         for(User user1:list) {         System.out.println(user1);         }

where Sql.query (user.class) returned the query class for a single-table query
Execution Result:

If it is java8, you can use LAMDBA to represent the column name

List<User> list1  = sql.lambdaQuery(User.class).andEq(User::getName, "hi").orderBy(User::getCreateDate).select();

(3) Template query
public T template query returns all databases conforming to this template, as above, Mapper can provide additional mappings, such as handling
public T Templateone (T T) based on the template query, returns a result, if not found, returns null
Public List Template (T t,int start,int size) ditto, can page
public long Templatecount (T t) get the number of eligible
Public List template query (Class target,object paras,long start,long size), parameter is paras, can be a map or a normal object
Public Long Templatecount (Class Target,object paras) Gets the number of eligible
Start of the page, the system by default from the beginning, in order to compatible with the database of the various systems, will be automatically translated into the database custom, such as Start 1, will be considered mysql,postgres starting from 0 (starting from start-1), Oracle,sqlserver, DB2 starting from 1 (start-0)
However, if you only use a specific database, you can follow a specific database custom, for example, you only use MYSQL,SATRT to 0 for the start record, need to configure

OFFSET_START_ZERO = true

This allows the page turn parameter to pass in 0
Note: The template Query method does not include a time period query or a sort based on the templates query, however, it can be customized by using the Getter method on the Pojo class using @table template () or the Date field, As follows:

User Login Verification:

 //8.使用template方法查询         User template=new User();         template.setUserName("zhukefu");         template.setPassword("123456");         template.setStatus(1);执行结果：         User user2=sqlManager.templateOne(template);         System.out.println("打印template查询结果："+user2);

Execution Result:

(4) query via Sqlid, SQL statement in MD file
public List Select (String Sqlid,class clazz,map<string,object> paras) is queried according to the Sqlid parameter is a MAP
public List Select (String Sqlid,class clazz,object paras) is queried according to Sqlid, the parameter is a Pojo
public List Select (String sqlid,class clazz) query based on sqlid, no parameters
public T selectdsingle (String sqlid,object paras,class target), based on the Sqlid query, enters Pojo, maps the corresponding unique value to the target object, and returns null if it is not found. It is important to note that sometimes the result set itself is empty and it is recommended to use the unique
public T selectdsingle (String sqlid,map<string,object> paras,class target) based on the Sqlid query, the input is Map, Map the corresponding unique value to the target object, and if not found, return NULL, it is important to note that sometimes the result set itself is empty, it is recommended to use the unique
public T selectunipue (String sqlid,object paras,class target), based on a sqlid query, enters a map or Pojo, maps the corresponding unique value to the target object, and throws an exception if it is not found
public T selectunipue (String sqlid,map<string,object> paras,class target) According to the Sqlid query, the input is Map or Pojo, Maps the corresponding unique value to the target object and throws an exception if it is not found
· Public Interger Intvalue (String id,object paras) query results map to Interger, if not found, return null, enter Object
public Interger Intvalue (String id,map paras) query results map to Interger, if not found, return null, enter Map, other Longvalue,bigdecimalvalue
Note: For map, there is a special key called _root, which represents the query root object, and the variables that could not be found in the SQL statement will try to find it from the _root.
(5) Specify scope query
public List Select (String sqlid,class clazz,map<string,object> paras,int start,int size) query specified range
public List Select (String sqlid,class clazz,object paras,int start,int size) query specified range
Attention:
Beetlsql default starting from 1, automatically translates to the starting line of the target database, such as MySQL 0,oracle 1, if you want to start from 0, you need to configure Beetlsql
Example: (SQL uses 3. The SQL statement in a page-turn query)

// 9.使用sqlid查询        List<User> list1 = sqlManager.select("user.queryUser", User.class);        for (User user3 : list1) {            System.out.println(user3);        }

Query Result:

3. Page Turn Query API

public <T> void pageQuery(String sqlId,Class<T> clazz,PageQuery query)

Beetlsql provides a Pagequery object for the Web app's paging query, Beetsql assumes Sqlid and Sqlid$count, two sqlid, and uses this to page and query the total number of results:
SQL code:

queryUser====select * from user order by id descqueryCountUser====select count(1) from user

Query code:

// 10.翻页查询        PageQuery<User> query1 = new PageQuery<User>();        sqlManager.pageQuery("user.queryCountUser", User.class, query1);        System.out.println(query1.getTotalPage());        System.out.println(query1.getTotalRow());        System.out.println(query1.getPageNumber());

Results:

4. Update API
(1) Automatically generate SQL
public void Insert (Object paras) insert paras to paras associated table
· public void Insert (object Paras,boolean Autoassignkey) inserts the Paras object into the table associated with the Paras object, and specifies whether the database primary key is automatically assigned to paras. For primary keys generated for self-increment or sequence class databases
public void Inserttemplate (Object paras) inserts paras to paras associated tables, ignoring null values or null-valued properties
public void Inserttemplate (Object paras,boolean Autoassignkey) inserts a paras to the paras associated table, and specifies whether the database primary key is automatically assigned to paras. Properties that omit null values or null values for primary keys that are generated for a database that is growing from a primary key
public void Insert (Class<?> clazz,object paras) insert paras to clazz associated table
public void Insert (class<?> clazz,object paras,keyholder holder) insert paras to clazz associated table
, if a primary key is required, the Keyholder method is called to get the primary key, and the primary key for calling this method must be self-increment
public int Insert ((class<?> clazz,object Paras,boolean Autoassignkey) inserts the Paras to the clazz associated table, and specifies whether to automatically assign the database primary key to Paras, calling this method the primary key must be self-increment
public int Updatebyid (Object obj) is updated according to the primary key, all values participate in the update
public int updatetemplate (Object obj) is updated based on primary key, property null is not updated
public int Updatebatchtemplatebyid (Class clazz,list<?> List) is updated in bulk based on primary key, property null is not updated
public int Updatetemplatebyid (Class<?> clazz,map paras) is updated according to the primary key, the component is represented by Clazz's annottion, and if not, the attribute ID is considered a primary key, property is null and will not be updated
public int[] Updatebyidbatch (list<?> List) batch Update
public void Insertbatch (Class clazz,list<?> List) BULK INSERT Data
(2) Update according to Sqlid (delete)
The public int insert (String sqlid,object paras,keyholder holder) is inserted according to the Sqlid and returns the primary key, the primary key ID is specified by the object, and the corresponding database table must be self-augmented with the primary key
public int Insert (String sqlid,object paras,keyholder holder,string keyName), primary key specified by KeyName
public int Insert (strint sqlid,map paras,keyholder holder,string keyName), parameters are provided via Map
public int update (String sqlid,object obj) updated according to Sqlid
public int update (String SQL Id,map<string,object> paras) based on Sqlid update, the output parameter is Map
public int[] UpdateBatch (stirng sqlid,list<?> List) batch Update
public int UpdateBatch (String sqlid,map<string,object>[] maps) batch update, parameters are arrays, element type is Map
5. Execute SQL templates directly
(1) Execute SQL template statements directly
public List Execute (String SQL,CLASSS clazz,object paras)
public List Execute (String Sql,class clazz,map paras)
public int executeupdate (STRING sql,object paras) returns the number of successfully executed bars
public int executeupdate (STRING Sql,map paras) returns the number of successfully executed bars
(2) direct execution of JDBC SQL statements
• Query public List execute (sqlready p,classs clazz) sqlready contains the SQL statements and parameters that need to be executed, clazz is the result of the query, such as:

//11. 直接执行sql语句        List<User> list4 = sqlManager.execute(                new SQLReady("select * from user where username=? and password=?","zhukefu", "123456"),                User.class);        System.out.println("打印list："+list4);

Execution Result:

public Pagequery Execute (sqlready p,class clazz,pagequery pagequery)
Cases:

//12.直接执行sql语句分页查询        PageQuery query3=new PageQuery(1,8);        String jdbcSql="select * from user order by id";        sqlManager.execute(new SQLReady(jdbcSql), User.class, query3);        List<User> list3=query3.getList();        for(User user3:list) {            System.out.println(user3);        }

Execution Result:

Note: parameters are passed through Sqlready instead of Pagequery
• Update public int executeupdate (Sqlready p) sqlready contains the SQL statements and arguments that need to be executed to return the new results
• Direct use of the connection public T executeonconnection (OnConnection calls) consumer needs to implement the call method of the OnConnection method, such as calling a stored procedure
"This summary is complete"

Learning Beetlsql Summary (2)--query API, update API