Beetlsql Tutorial: mybatis function Beetl Implementation

Beetlsql Features

Beetsql is a full-featured DAO tool with Hibernate benefits & MyBatis Advantages for applications that recognize SQL-centric and require tools to automatically generate a large number of commonly used SQL.

Automatically generate a large number of built-in SQL without annotations, and easily perform the add-and-revise function
The data model supports Pojo, and Teng Yun technology (ty300.com) also supports Map/list, a fast model, and a hybrid model
SQL in a more concise way, diligent novice Tutorial (qkxue.net) Markdown Way centralized management, while facilitating program development and database SQL debugging.
SQL templates are based on BEETL implementations, easier to write and debug, and extended
Simple support for relational mapping without the introduction of complex or mapping concepts and techniques.
Features interceptor, debug, Performance Diagnostic SQL, and extended other features
Built-in support for master-slave database, extended to support more complex sub-library table logic
Supports cross-database platforms, minimizing the effort required by developers
5-minute Example

Preparatory work

In order to quickly try Beetlsql, you need to prepare a MySQL database and execute the following SQL script

DROP TABLE IF EXISTS ' user ';
CREATE TABLE ' user ' (
' id ' int (one) is not NULL,
' Name ' varchar (+) DEFAULT NULL,
' Age ' int (4) DEFAULT NULL,
' UserName ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8;
Write a Pojo class that corresponds to the database table

public class User {
Integer ID;
String name;
Integer age;
Public String GetName () {
return name;
}

public void SetName (String name) {
THIS.name = name;
}

Public Integer getId () {
return ID;
}

public void SetId (Integer id) {
This.id = ID;
}

Public Integer Getage () {
return age;
}

public void Setage (Integer age) {
This.age = age;
}

}
Code examples

Write a Java Main method with the following content

Create a simple connectionsource with only one master
Connectionsource Source = connectionsourcehelper.simple (Driver,url,username,password);
Using MySQL Custom
Dbstyle mysql = new Mysqlstyle ();
SQL statements are placed in the/sql directory of the Classpagth
Sqlloader loader = new Classpathloader ("/sql");
Database naming and Java naming using hump transformation
Nameconversion NC = new Humpnameconversion ();
Finally, create a Sqlmanager
Sqlmanager Sqlmanager = new Sqlmanager (Source,mysql,loader);

New user with built-in generated SQL
User user = new user ();
User.setage (19);
User.setname ("Xiandafu");
Sqlmanager.insert (user);
Querying users with built-in SQL
int id = 1;
user = Sqlmanager.unque (user.class,id);

Using the SELECT statement in the User.md file, refer to the next section
User query = new user ();
Query.setname ("Xiandafu");
list<user> list = Sqlmanager.select ("User.select", User.class,query)
SQL Example

In order to execute the User.select, a user.md file needs to be created in the Classpath, as follows

Select
===
SELECT * from user where 1=1
@if (!isempty (age)) {
and age = #age #
@}
@if (!isempty (name)) {
and name = #name #
@}
There are a few simple explanations for how to write SQL templates, which are explained later in this section.

The @ and carriage return symbols are bounding symbols that can be written in beetl statements.

"#" is the position symbol, when the SQL statement is generated, the output? , if you want to output an expression value, you need to use the text function, or any function that starts with a db, and the engine thinks it is the output text directly.

IsEmpty is a function of the beetl to determine whether a variable is empty or not present.

The SQL template uses Beetl reason because the beetl syntax is similar to JS, and the template rendering is specific optimization, compared to the mybatis, more easy to grasp and powerful.

Beetlsql description

Get Sqlmanager

Sqlmanager is the core of the system, and he provides all the DAO methods. Obtain Sqlmanager, can construct sqlmanager directly. And through a single example to obtain such as:

Connectionsource Source = connectionsourcehelper.simple (Driver,url,username,password);
Using MySQL Custom
Dbstyle mysql = new Mysqlstyle ();
SQL statements are placed in the/sql directory of the Classpagth
Sqlloader loader = new Classpathloader ("/sql");
Database naming and Java naming using hump transformation
Nameconversion NC = new Humpnameconversion ();
Finally, create a Sqlmanager
Sqlmanager Sqlmanager = new Sqlmanager (Source,mysql,loader);
More commonly, there are already datasource, and creating a connectionsource can take the following code

Connectionsource Source = Connectionsourcehelper.single (DataSource);
If it is a master-slave datasource

Connectionsource Source = Connectionsourcehelper.getmasterslave (master,slaves)
Spring integration

<bean id= "Sqlmanager" class= "ORG.BEETL.SQL.EXT.SPRINGBEETLSQL" >
<property name= "CS" >
<bean class= "Org.beetl.sql.ext.SpringConnectionSource" >
<property name= "Master" ref= "DataSource" ></property>
</bean>
</property>
<property name= "Dbstyle" >
<bean class= "Org.beetl.sql.core.db.MySqlStyle" > </bean>
</property>
<property name= "Sqlloader" >
<bean class= "Org.beetl.sql.core.ClasspathLoader" >
<property name= "Sqlroot" value= "/sql" ></property>
</bean>
</property>
<property name= "NC" >
<bean class= "Org.beetl.sql.core.HumpNameConversion" >
</bean>
</property>
<property name= "Interceptors" >
<list>
<bean class= "Org.beetl.sql.ext.DebugInterceptor" ></bean>
</list>
</property>
</bean>
CS: Specify Connectionsource, can use system-provided defaultconnectionsource, support according to CRUD decision master and slave. There's only one master library in the example.

Dbstyle: Database type, currently only supported Org.beetl.sql.core.db.MySqlStyle

Sqlloader:sql Statement Load Source

NC: Named conversions, humpnameconversion with hump, underlinednameconversion with database underline

Interceptors:debuginterceptor used to print SQL statements, parameters, and execution time

Note: Any use of transactional annotations will use the master data source uniformly, with the exception of @transactional (Readonly=true), which will allow Beetsql to choose from the database.

public class Myserviceimpl implements MyService {

@Autowired
Springbeetlsql Beetlsql;

@Override
@Transactional ()
public int total (user user) {

Sqlmanager DAO = Beetlsql.getsqlmananger ();
list<user> list = Dao.all (User.class);
int total = list. Size ();
Dao.deletebyid (User.class, 3);
User u =new user ();
U.id = 3;
U.name= "Hello";
U.age = 12;
Dao.insert (User.class, u);

return total;

}

}
Refer to Demo Https://git.oschina.net/xiandafu/springbeetlsql

Jfinal Integration

Configure Beetlsql in Configplugin

Jfinalbeetlsql.init ();
The default is to use C3P0 as the data source, its configuration from the jfinal configuration, if you provide the data source or master-slave, you can be as follows

Jfinalbeetlsql.init (master,slaves);
Because of the use of beetlsql, you no longer have to configure the database connection pooling plug-in, and Activerecordplugin, you can delete the relevant configuration.

In the controller, the Sqlmanager can be obtained through the Jfinalbeetlsql.dao method.

Sqlmanager DAO = Jfinalbeetlsql.dao ();
Bigblog blog = Getmodel (bigblog.class);
Dao.insert (bigblog.class, blog);
If you want to control things, you also need to register trans

public void Configinterceptor (interceptors me) {
Me.addglobalactioninterceptor (New Trans ());
}
Then the business method uses

@Before (Trans.class)
public void Doxxx () {.... +
This way, the method executes before the thing is committed, and any runtimeexception will be rolled back if you want to manually control the rollback. You can also

Trans.commit ()
Trans.rollback ()
If you are accustomed to jfinal record mode, it is recommended that you create a basebean that encapsulates Sqlmanager CRUD methods. Then other models inherit this Basebean

Refer to Demo Https://git.oschina.net/xiandafu/jfinal_beet_beetsql_btjson

Sqlmanager API

Query API

Template class query (automatically generate SQL)

The public List template (t) queries the template to return all databases that conform to this template
The public List template (T t,rowmapper mapper) Ibid, mapper can provide additional mappings, such as handling one-to-many, one-to-one
Public List Template (T t,int start,int size) ditto, can flip page
Public List Template (T t,rowmapper mapper,int start,int size) page, and add additional mappings
Public long Templatecount (T t) gets the number of qualifying conditions
With sqlid queries, SQL statements are in the MD file

The public List Select (String sqlid, Class clazz, map paras) is queried according to the Sqlid, and the parameter is a MAP
The public List Select (String sqlid, Class Clazz, Object paras) is queried according to Sqlid, and the parameter is a Pojo
Public List Select (String sqlid, Class Clazz, MAP paras, int start, int size), increase page flipping
Public List Select (String sqlid, Class Clazz, Object paras, int start, int size), increase page flipping
Public T Selectsingle (String id,object paras, Class target) maps the corresponding unique values to the specified Taget object according to the Sqlid query, and RowMapper Mapper is also provided with these APIs. It's not out.

Public T Selectsingle (String Id,map paras, Class target) ibid., Parameter is Map

The public integer intvalue (String id,object paras) query results are mapped to integers, and the input is objct

The public integer intvalue (String id,map paras) query results are mapped to integers, the input is map, and the others are Longvalue,bigdecimalvalue

Update API

Automatically generate SQL

public void Insert (Class<?> clazz,object paras) insert paras to paras associated table
public void Insert (class<?> clazz,object paras,keyholder holder), insert paras to paras associated table, if primary key is required, can be obtained by Holder's Getkey
public int Updatebyid (Object obj) is updated according to the primary key, the component is represented by annotation, and if not, the attribute ID is considered a primary key
Public int[] Updatebyidbatch (list<?> List) batch Update
Update via Sqlid

public int update (String sqlid, Object obj) updated according to Sqlid
public int update (String sqlid, map paras) is updated according to Sqlid, and the output parameter is Map
Public int[] UpdateBatch (String sqlid,list<?> List) Bulk Update
Public int[] UpdateBatch (String sqlid,map[] maps) batch update, parameters are arrays, element type is Map
Beetlsql Annotation

For automatically generated SQL, no Annotaton is required by default, the class name corresponds to the table name (through the Nameconverstion Class), and the property name of the Getter method corresponds to the specified (also through the Nameconverstion Class). But some cases still need to be anntation.

@<table> (name= "xxxx") tells Beetlsql, this class corresponds to the XXXX table. For example, the database has a user table, the user class corresponds to the user table, you can also create a UserQuery object, also corresponds to the user table

@<table> (name= "User") public class Queryuser.

@AutoID, for Getter Method, tell Beetlsql, this is the self-increment primary key

@AssignID, for the Getter method, tell Beetlsql that this is the primary key and that the primary key is set by the code

@SeqID (name= "Xx_seq", which is used for getter methods, tells Beetlsql, which is the sequence primary key.

(Note that if you want to get a self-increment primary key or sequence primary key, you need to pass in a keyholder in Sqlmanager.insert)

Beetlsql model

Beetlsql is a full-featured DAO tool that supports a very comprehensive model, including

Pojo, which is object-oriented Java object

Map/list, for some agile development, you can use map/list as input and output parameters directly

Hybrid model, it is recommended to use a hybrid model. Flexibility and better serviceability. Pojo can implement QueryResult, or inherit Queryresultbean, so that the resultset of the query, in addition to the mappings according to Pojo, the values that cannot be mapped will be saved by list/value. The following is a hybrid model:

/* Mixed model */public User extends queryresultbean{private int id; pirvate String name; private int Roleid;/* The following are getter and setter Method */}

For SQL statements:

Selectuser
===
Select U.*,r.name r_name from the user U left join role R on U.roleid=r.id .....
When you execute a query

list<user> list = Sqlmanager.select ("User.selectuser", User.class,paras);
for (User user:list) {
System.out.println (User.getid ());
System.out.println (User.get ("Rname"));

}
The program can get a value that is not mapped to Pojo by the Get method, or it can be displayed directly ${user.rname} in the template (supported for most template engines)

Markdown Mode Management

BEETLSQL Centralized management of SQL statements, SQL can be placed in a file according to business logic, such as the user object in the USER.MD, the file can be placed in a directory according to the logic of the module. The file format discards the XML format and uses Markdown, because

XML format is too complex to write easily
XML format has reserved symbols, when writing SQL is not convenient, such as commonly used < symbols must be escaped
The MD format itself is a document format and is easy to read and maintain through the browser
The current SQL file format is very simple, just the sqlid and SQL statements themselves, as follows

File some instructions, put in the head dispensable, if there is a description, can be any text
SQL flag
===
SQL statements

SQL Flag 2
===
SQL Statement 2
All SQL files are proposed to be put into a SQL directory, the SQL directory has multiple subdirectories, the database type, this is the public SQL statements into the SQL directory, the specific database of SQL statements into their own directory when the program gets the SQL statement, the database will be based on the SQL statement under a specific database , if not found, will look for SQL under. The following code

list<user> list = Sqlmanager.select ("User.select", User.class);
Sqlmanager will find the Sql/mysql/user.md file based on the database currently in use, confirm if there is a SELECT statement, and if not, look for sql/user.md

(Note: The default Classpathloader uses this method, you can implement the Sqlloader to implement your own format and SQL storage, such as database storage)

SQL templates are based on BEETL implementations, easier to write and debug, and extended

SQL statements can be generated dynamically, based on the Beetl language, because

Beetl execution is efficient, so using beetl is a good fit for dynamic SQL statements based on templates

Beetl syntax is simple and easy to use, can be achieved by semi-guessing half-way, to eliminate mybatis so difficult to remember grammar. Beetlsql learning curve almost no

The use of Beetl can be set to define the bounds of the symbol, the SQL template delimiter can be well defined as a database SQL comment symbol, so it is easy to test in the database, the following is also the SQL template (the delimiter is defined as "-" and "null", NULL is a carriage return meaning);

Selectbycond
===
SELECT * Form user where 1=1
--if (Age!=null)
Age= #age #
--}
Beetl error hints are very friendly and reduce write-SQL scripting maintenance time

Beetl can easily interact with native classes (direct access to Java classes), execute specific business logic, or write model constants directly in SQL templates, and even SQL refactoring will parse the error in advance.

Beetl statements are easy to extend and provide various functions, such as table logical functions, common functions across databases, etc.

If you do not know Beetl, you can first try to follow the JS syntax to write SQL template, if you have questions, you can check the official website http://ibeetl.com

Interceptor function

Beetlsql can perform a series of intercetor before and after executing SQL, providing the opportunity to perform various extensions and monitoring, which is easier than the known interceptor through the database connection pool. The following interceptor are possible

Monitoring SQL executes longer statements, prints and collects. Timestatinterceptor class Completion
After each SQL statement is executed, its SQL and parameters are output, and only SQL for a specific SQL collection can be output based on the condition. User-friendly debugging. Debuginterceptor completed
SQL expected parsing, summarizing SQL execution (not completed, need to integrate third-party SQL analysis tools)
You can also extend the Interceptor class yourself to accomplish specific needs. As follows, the execution of befor is performed before the database operation, and the context parameters of execution are obtained through CTX, and after the database executes successfully, the After method is executed

Public interface Interceptor {
public void before (Interceptorcontext ctx);
public void after (Interceptorcontext ctx);
}
Interceptorcontext is as follows, contains sqlid, actual SQL, and actual parameters

public class Interceptorcontext {
Private String Sqlid;
private String SQL;
Private list<object> paras;
Private map<string,object> env = NULL;
}
Built-in support master-slave database

Beetlsql manages the data source, if it is provided with only one data source, it is considered that both read and write operate this data source, and if more than one is provided, the first is the write library and the other is read. When developing the code, the user does not need to worry about which database to manipulate, because when calling Sqlscrip's Select related API, always reads from the library, Add/update/delete, always reads the main library.

Sqlmanager.insert (User.class,user)//Operation Main Library, if only one data source is configured, it doesn't matter if the master-slave
Sqlmanager.unique (Id,user.class)//read from library
The logic of the master-slave library is determined by Connectionsource, the following defaultconnectionsource logic

@Override
Public Connection getconn (String sqlid,boolean isupdate,string sql,list<?> paras) {
if (this.slaves==null| | this.slaves.length==0) return This.getwriteconn (Sqlid,sql,paras);
if (isupdate) return This.getwriteconn (Sqlid,sql,paras);
Boolean onlymaster = Localmaster.get ();
if (Onlymaster) return This.getmaster ();
Return This.getreadconn (Sqlid, SQL, paras);
}
Localmaster can force Sqlmanager to use the primary database. Refer to API Sqlmanager. Usemaster (Masterrunner f)
For different Connectionsource completion logic, for spring,jfinal such a framework, if sqlmanager in a transactional environment, always operate the primary database, if it is a read-only transaction environment then operate from the database. If there is no transactional environment, it is determined based on whether SQL is a query or an update.

The following is the master-slave logic provided by Springconnectionsource

@Override
Public Connection getconn (String sqlid,boolean isupdate,string sql,list paras) {
Only one data source
if (this.slaves==null| | this.slaves.length==0) return This.getwriteconn (Sqlid,sql,paras);
If it's an UPDATE statement, you have to go master.
if (isupdate) return This.getwriteconn (Sqlid,sql,paras);
If the API enforces the use of master
Boolean onlymaster = Localmaster.get ();
if (Onlymaster) return This.getmaster ();
Use master in all things, except readonly things.
Boolean Intrans = Transactionsynchronizationmanager.isactualtransactionactive ();
if (Intrans) {
Boolean isreadonly = Transactionsynchronizationmanager.iscurrenttransactionreadonly ();
if (!isreadonly) {
return This.getmaster ();
}
}

Return This.getreadconn (Sqlid, SQL, paras);
}
Can support more complex sub-database table logic

Developers can also be transparent to the user by completing the table logic in the SQL template, as in the following SQL statement

INSERT INTO
#text ("Log_" + getMonth (Date ()) #
VALUES () ...
Note: The text function outputs an expression directly to the SQL statement instead of the output?.

Log indicates that a table can be sorted according to certain rules, and table is determined by the time it is entered.

SELECT * FROM
#text ("Log" +log.date) #
where
Note: The text function outputs an expression directly to the SQL statement instead of the output?.

Similarly, decide which table to go to according to the input criteria, or query all tables

@ var tables = Getlogtables ();
@ for (table in tables) {
SELECT * from #text (table) #
@ if (!tablelp.islast) print ("union");
@}
where name = #name #
Cross-database platform

As mentioned earlier, Beetlsql can support cross-database development with SQL file management and search, as described earlier, searching for a specific database before looking for common. In addition, Beetlsql provides a number of database solutions

Dbstyle describes database features, injects INSERT statements, and page-turn statements are done through their subclasses, without the user having to worry about
Provides some default function extensions instead of functions for each database, such as time and time manipulation functions, date, etc.
Add a custom method

Using the same beetl, you can add a custom function to the btsql-ext.properties. It is important to note that Beetlsql is always output at the * * character, unless your function name starts with a db, such as Db.ifnull,dblog. Or use the built-in text function. For the following SQL statement

SELECT * from ${dblog ()} WHERE id = ${id} and Status = "${text (@Constants. RUNNING)}"
The following statement is generated

SELECT * from xxxlog where id =? and status = "on".
The value of the question mark corresponds to the variable ID

Beetlsql Tutorial: MyBatis features Beetl implementation