Shulou(Shulou.com)06/03 Report--

Learning BeetlSQL Summary (2)

After the study in the previous section, we have learned the basics of BeetlSQL, and then we will learn more about BeetlSQL.

I. BeetlSQL description

1. Getting SQLManager is the core of the system, it provides all the dao methods, obtains SQLManager, you can construct SQLManager directly, and get it through a single example.

ConnectionSource source = ConnectionSourceHelper.getSimple (mysqlDriver, url, userName, password); DBStyle mysql = new MySqlStyle (); / / SQL statements are placed in classpath's sql directory SQLLoader loader = new ClasspathLoader ("/ sql"); / / Database naming is the same as java naming, so DefaultNameConversion is used, and there is a UnderlinedNameConversion underscore style UnderlinedNameConversion nc = new UnderlinedNameConversion () / / finally, create a SQLManager,DebugInterceptor, which is not required, but you can check the execution of SQL SQLManager sqlManager = new SQLManager (mysql, loader, source, nc, new Interceptor [] {new DebugInterceptor ()})

If you already have a Datasource, you can create the ConnectionSource with the following code:

ConnectionSource source = ConnectionSourceHelper.getSingle (datasource)

If it is a master-slave Datasource:

ConnectionSource source = ConnectionSourceHelper.getMasterSlave (master,slaves)

two。 Query API

(1) simple query (automatically generate sql)

Public T unique (Class clazz,Object contention) queries according to the primary key and throws an exception if it is not found

Public T single (Class clazz,Object competition) query according to the primary key, if not found, return null

Public List all (Class clazz) queries all result sets

Public List all (Class clazz,int start,int size) turn the page

Total public int allCount (Class clazz)

(2) single table query

SQLManager provides Query class to implement single table query operation.

/ / 7. Single table query System.out.println ("start executing single table query"); List list = sqlManager.query (User.class) .andEq ("name", "Manstein"). OrderBy ("create_date"). Select (); System.out.println ("print query results:"); for (User user1:list) {System.out.println (user1);}

Where sql.query (User.class) returns the Query class for single table query

Execution result:

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

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

(3) template query

Public T template (T t) returns all databases that match this template according to the template query. As above, mapper can provide additional mappings, such as dealing with one-to-one, one-to-many

Public T templateOne (T t) returns a result according to the template query, or null if it is not found

Public List template (T tpenint start,int size) ditto, you can turn the page.

Public long templateCount (T t) gets the number that meets the condition.

Public List template (Class target,Object paras,long start,long size) template query. The parameter is paras, which can be Map or ordinary object.

Public long templateCount (Class target,Object paras) gets the number that meets the criteria

For the start that turns pages, by default, from the beginning, in order to be compatible with the databases of each system, it will be automatically translated into database customs. For example, if start is 1, you will think that mysql,postgres starts with 0 (starting with start-1) and oracle,sqlserver,db2 starts with 1 (start-0).

However, if you only use a specific database, you can follow a specific database custom, for example, if you only use a mysql,satrt of 0 to represent the starting record, you need to configure

OFFSET_START_ZERO = true

In this way, the paging parameter can be passed in 0.

Note: the template query method does not include a time period query or sorting according to the template query. However, you can customize it by using @ DataTemplate (for customization) on the getter method using @ Table Template () or date field on pojo class, as follows:

User login authentication:

/ / 8. Use the template method to query User template=new User (); template.setUserName ("zhukefu"); template.setPassword ("123456"); template.setStatus (1); execution result: User user2=sqlManager.templateOne (template); System.out.println ("print template query results:" + user2)

Execution result:

(4) through sqlid query, sql statement is in md file.

Public List select (String sqlId,Class clazz,Map paras) queries according to sqlId, and the parameter is a map

Public List select (String sqlId,Class clazz,Object paras) queries according to sqlId, and the parameter is a pojo

Public List select (String sqlId,Class clazz) queries according to sqlId and has no parameters

Public T selectDSingle (String sqlId,Object paras,Class target) according to the sqlid query, enter pojo, map the corresponding unique value to the target object, and return empty if it is not found. It should be noted that sometimes the result set itself is empty, so it is recommended to use unique.

Public T selectDSingle (String sqlId,Map paras,Class target) according to the sqlid query, enter map, map the corresponding unique value to the target object, and return empty if it is not found. It should be noted that sometimes the result set itself is empty, so it is recommended to use unique.

Public T selectUnipue (String sqlId,Object paras,Class target) according to the sqlid query, enter map or pojo, map the corresponding unique value to a target object, and throw an exception if it is not found

Public T selectUnipue (String sqlId,Map paras,Class target) according to the sqlid query, enter map or pojo, map the corresponding unique value to a target object, and throw an exception if it is not found

Public Interger intValue (String id,Object paras) query result is mapped to Interger. If not found, null is returned and object is entered.

Public Interger intValue (String id,Map paras) query results are mapped to Interger, if not found, return null, enter map, and other longValue,bigDecimalValue

Note: for Map, there is a special key called _ root, which represents the root object of the query. Variables that cannot be found in the sql statement will try to find them in _ root.

(5) query with specified range

Public List select (String sqlId,Class clazz,Map paras,int start,int size) query specified range

Public List select (String sqlId,Class clazz,Object paras,int start,int size) query specified range

Note:

By default, Beetlsql starts from 1 and automatically translates to the starting line of the target database, such as 0 of mysql and 1 of oracle. If you want to start from 0, you need to configure beetlsql

Example: (sql uses 3. Sql statement in a paged query)

/ / 9. Use sqlid to query List list1 = sqlManager.select ("user.queryUser", User.class); for (User user3: list1) {System.out.println (user3);}

Query results:

3. Turn the page to query API

Public void pageQuery (String sqlId,Class clazz,PageQuery query)

BeetlSQL provides a pageQuery object for paging queries in web applications. BeetSQL assumes that there are sqlId and sqlId$count, two sqlid, and use this to turn pages and the total number of query results: such as

Sql Code:

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

Query code:

/ / 10. Paging query PageQuery query1 = new PageQuery (); 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) inserts paras into the table associated with paras

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 the paras, which is suitable for self-incrementing or sequential databases.

Public void insertTemplate (Object paras) inserts paras into the table associated with paras, ignoring null or null attributes

Public void insertTemplate (Object paras,boolean autoAssignKey) inserts paras into the table associated with paras and specifies whether to automatically assign the database primary key to paras, ignoring null or null attributes, which are suitable for primary keys generated by databases with self-increasing primary keys

Public void insert (Class clazz,Object paras) inserts paras into the table associated with clazz

Public void insert (Class clazz,Object paras,KeyHolder holder) inserts paras into the table associated with clazz

If a primary key is required, call the KeyHolder method to get the primary key, which must be self-incremented

Public int insert ((Class clazz,Object paras,boolean autoAssignKey) inserts paras into the table associated with clazz and specifies whether the database primary key is automatically assigned to paras. To call this method, the primary key must be self-incremented.

Public int updateById (Object obj) updates according to the primary key, and all values participate in the update

Public int updateTemplate (Object obj) is updated according to the primary key, and the property null will not be updated.

Public int updateBatchTemplateById (Class clazz,List list) updates in batches according to the primary key, and those with null attribute will not be updated.

Public int updateTemplateById (Class clazz,Map paras) updates according to the primary key, and the component is represented by the annottion of clazz. If not, the attribute Id is considered to be the primary key, and the attribute null will not be updated.

Public int [] updateByIdBatch (List list) batch updates

Public void insertBatch (Class clazz,List list) bulk insert data

(2) Update (delete) according to sqlId

Public int insert (String sqlId,Object paras,KeyHolder holder) inserts according to sqlId and returns the primary key. The primary key id is specified by the object. If this method is called, the corresponding database table must augment the primary key.

Public int insert (String sqlId,Object paras,KeyHolder holder,String keyName) same as above, the primary key is specified by keyName

Public int insert (Strint sqlId,Map paras,KeyHolder holder,String keyName) is the same as above, and the parameters are provided through map

Public int update (String sqlId,Object obj) updates according to sqlid

Public int update (String sqlId, Map paras) is updated according to sqlId. The output parameter is map.

Public int [] updateBatch (Stirng sqlId,List list) batch updates

Public int updateBatch (String sqlId,Map [] maps) batch updates. The parameter is an array and the element type is map.

5. Execute the SQl template directly

(1) execute the sql template statement 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 messages

Public int executeUpdate (String sql,Map paras) returns the number of successfully executed messages

(2) execute the sql statement of JDBC directly

The query public List execute (SQLReady pcent Classs clazz) SQLReady contains the sql statements and parameters to be executed. Clazz is the result of the query, such as:

/ / 11. Directly execute the sql statement List list4 = sqlManager.execute (new SQLReady ("select * from user where username=? and password=?", "zhukefu", "123456"), User.class); System.out.println ("print list:" + list4)

Execution result:

Public PageQuery execute (SQLReady pjam Class clazz,PageQuery pageQuery)

Example:

/ / 12. Directly execute sql statement paging query PageQuery query3=new PageQuery (1Power8); String jdbcSql= "select * from user order by id"; sqlManager.execute (new SQLReady (jdbcSql), User.class, query3); List list3=query3.getList (); for (User user3:list) {System.out.println (user3);}

Execution result:

Note: parameters are passed through SQLReady, not pageQuery

Update public int executeUpdate (SQLReady p) SQLReady contains sql statements and parameters that need to be executed, and returns new results

To use Connection public T executeOnConnection (OnConnection call) directly, consumers need to implement call methods of onConnection methods, such as calling stored procedures.

[this summary is over]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.