Beetlsql Framework Learning (I.)--initial knowledge of Beetlsql, features, use of built-in SQL, Pojo code generation, etc.

Learning Beetlsql Summary (1)
A Beetlsql Features:
1. Development efficiency:
(1) Without annotations, can automatically use a large number of built-in SQL, quickly complete the increase, delete, change, check the function
(2) The data model supports Pojo, and also supports Map/list, a fast model, and a hybrid model
(3) SQL template based on B-eetl implementation, easier to write and debug, and extended
(4) Pojo classes and SQL models can be generated for a single table (or view), and even the entire database can effectively reduce the amount of code written
2. Serviceability:
(1) SQL in a more concise way, markdown centralized management, while facilitating the development of the program and database SQL debugging
(2) Automatic mapping of SQL files to DAO interface classes
(3) Flexible and intuitive support one-to-one, one-to-many, many-to-many relational mappings without the introduction of complex or maping concepts and techniques
(4) With Interceptor function, can debug, performance diagnose SQL, and extend other functions
3. Other:
(1) Built-in open source tools supporting master-slave database support
(2) performance several times Jpa,mybatis
(3) Support cross-database platform, reduce the workload of developers to a minimum, currently supported cross-database has MYSQL,POSTGRES,ORACLE,SQLSERVER,H2,SQLLITE,DB2
Second, take Mevan project as an example (understanding Beetlsql)
Because I've finished all the code I've shown, I'll show you the code structure ahead of time.

1. Create Mevan project (BEETLSQL)
I have created the Mevan project with the following structure:

2. Add beetlsql Frame (config pom.xml)

<project xmlns= "http://maven.apache.org/POM/4.0.0" xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" xsi: schemalocation= "http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd" > <modelversion >4.0.0</modelVersion> <groupId>cn.com.dhcc.beetl</groupId> <artifactid>beetlsql</ artifactid> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>b  Eetlsql Maven webapp</name> <url>http://maven.apache.org</url> <dependencies> <!-- Unit Tests-<dependency> <groupId>junit</groupId> <artifactid>junit& lt;/artifactid> <version>3.8.1</version> <scope>test</scope> </  Dependency> <!--2. Logs--<!--Https://mvnrepository.com/artifact/ch.qos.logback/logback-classic       -<dependency>     <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency> <!--beetl SQL framework-related packages--<!--HT Tps://mvnrepository.com/artifact/com.ibeetl/beetl-<dependency> <groupid>com.ibeetl&lt        ;/groupid> <artifactId>beetl</artifactId> <version>2.8.6</version> </dependency> <!--https://mvnrepository.com/artifact/com.ibeetl/beetlsql--<dependency&gt            ; <groupId>com.ibeetl</groupId> <artifactId>beetlsql</artifactId> <version& Gt;2.10.31</version> </dependency> <!--Https://mvnrepository.com/artifact/mysql/mysql-connec Tor-java-<dependency> <groupId>mysql</groupId> <artifactid>mys Ql-connector-java</artifactid> <version>8.0.11</version> </dependency> </dependencies > <build> <finalName>BeetlSQL</finalName> </build></project>

3. Because we are working on the database, the second step is to create a database and a database table

CREATE DATABASE beetlsql;USE beetlsql;CREATE TABLE USER( id INT(11) NOT NULL AUTO_INCREMENT, NAME VARCHAR(64) DEFAULT NULL, age INT(4) DEFAULT NULL, username VARCHAR(64) DEFAULT NULL COMMENT ‘用户名‘, roleId INT(11) DEFAULT NULL COMMENT ‘用户角色‘, create_date DATETIME NULL DEFAULT NULL, PRIMARY KEY(id))ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

4. Next write the data table user corresponding Entity entities Class (will show code auto-generated later)

Package Cn.com.dhcc.beetlsql.entity;import java.util.date;//User entity class public class User {private Integer ID;    Private Integer age;    User role private Integer Roleid;    private String name;    User name private String userName;    Private Date CreateDate; Public user () {} public user (integer ID, integer age, Integer roleid, string name, String userName, Date createdate        ) {super ();        This.id = ID;        This.age = age;        This.roleid = Roleid;        THIS.name = name;        This.username = UserName;    This.createdate = CreateDate;    } 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;    } public Integer Getroleid () {return roleid;    } public void Setroleid (Integer roleid) {This.roleid = Roleid;    } public String GetName () {return name;    }public void SetName (String name) {this.name = name;    } public String GetUserName () {return userName;    } public void Setusername (String userName) {this.username = UserName;    Public Date Getcreatedate () {return createdate;    } public void Setcreatedate (Date createdate) {this.createdate = CreateDate; } @Override Public String toString () {return ' User [id= + ID + ', age= "+ Age +", roleid= "+ Roleid +", n    Ame= "+ name +", username= "+ UserName +", createdate= "+ CreateDate +"] "; }}

5. Down we write a main method to perform functional testing on Beetlsql and understand
(1) To establish a database connection, where we do not write the configuration file, the database connection required parameters are set in the Main method

String mysqlDriver="com.mysql.jdbc.Driver";        String url="jdbc:mysql://localhost:3306/beetlsql?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false";        String userName="root";        String password="qitao1996";        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()});

(2) Next we will use the built-in SQL statement to operate the database table, the code is as follows

1. Using built-in generated SQL new users, if you need to get the primary key, you can pass in Keyholder System.out.println ("Start using built-in SQL for user add ...");        User User=new user ();        User.setage (19);        User.setname ("Turkmenistan");        int Num=sqlmanager.insert (user);        if (num>0) {System.out.println ("add successful!!!! with built-in SQL User");        }//2. Use built-in SQL query user int id=1;        System.out.println ("Start using built-in SQL for user queries ...");        User=sqlmanager.unique (User.class, id); SYSTEM.OUT.PRINTLN ("User query completed using built-in SQL!!        ");        3. Update (Modify) The data, only update the value by the ID of the column System.out.println ("Start using built-in SQL update user information ...");        User Newuser=new user ();        Newuser.setid (1);        Newuser.setname ("Zhukov");        int Num1=sqlmanager.updatetemplatebyid (newuser);        if (num1>0) {System.out.println ("Use built-in SQL to update user information successfully!!!!");        }//4. Template query User query=new user ();        Query.setname ("Turkmenistan");        System.out.println ("Start template query ...");        list<user> userlist=sqlmanager.template (query); System.out.prINTLN ("Print query result:");        for (User u:userlist) {System.out.println (U); } System.out.println ("Use template query succeeded ...");

The results of the implementation are as follows:
Start using built-in SQL for user additions ...
┏━━━━━debug [User._gen_insert]━━━
┣sql:insert into user ( name , age , create_date ) VALUES (?,?,?)
┣ parameter: [Manstein, +, NULL]
┣ Position: Main. Testbeetlsql.main (testbeetlsql.java:52)
┣ Time: 319ms
┣ update: [1]
┗━━━━━debug [User._gen_insert]━━━

Add a successful!!!! using the built-in SQL user
Start using built-in SQL for user queries ...
┏━━━━━debug [User._gen_selectbyid]━━━
┣sql:select * from user where id =?
┣ parameter: [1]
┣ Position: Main. Testbeetlsql.main (TESTBEETLSQL.JAVA:60)
┣ Time: 37ms
┣ results: [User [Id=1, age=19, Roleid=null, Name= Zhukov, Username=null, Createdate=null]
┗━━━━━debug [User._gen_selectbyid]━━━

User query completed with built-in SQL!!
Start updating user information with built-in SQL ...
┏━━━━━debug [User._gen_updatetemplatebyid]━━━
┣sql:update user Set name =? where id =?
┣ parameter: [Zhukov, 1]
┣ Position: Main. Testbeetlsql.main (testbeetlsql.java:68)
┣ Time: 168ms
┣ update: [1]
┗━━━━━debug [User._gen_updatetemplatebyid]━━━

Use built-in SQL to update user information successfully!!!!
Start a template query ...
┏━━━━━debug [User._gen_selectbytemplate]━━━
┣sql:select * from user where 1=1 and name =?
┣ parameter: [Turkmenistan]
┣ Position: Main. Testbeetlsql.main (testbeetlsql.java:77)
┣ Time: 91ms
┣ result: [10]
┗━━━━━debug [User._gen_selectbytemplate]━━━

Print Query results:
User [id=2, age=19, Roleid=null, Name= Manstein, username=mansitanying, Createdate=null]
User [Id=3, age=19, Roleid=null, Name= Manstein, Username=deguoyuanshuai, Createdate=null]
User [Id=4, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=5, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=6, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=7, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=8, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=9, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=10, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=11, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
Using template Query Success ...
Start querying with SQL statements in SQL file ....
┏━━━━━debug [User.select]━━━
┣sql:select * from user where 1=1
┣ parameter: []
┣ Position: Main. Testbeetlsql.main (testbeetlsql.java:88)
┣ Time: 78ms
┣ result: [11]
┗━━━━━debug [User.select]━━━

User [Id=1, age=19, Roleid=null, Name= Zhukov, Username=null, Createdate=null]
User [id=2, age=19, Roleid=null, Name= Manstein, username=mansitanying, Createdate=null]
User [Id=3, age=19, Roleid=null, Name= Manstein, Username=deguoyuanshuai, Createdate=null]
User [Id=4, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=5, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=6, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=7, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=8, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [Id=9, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=10, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=11, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]

(3) Show Code generation:
# #1. We first create a person table in the database to generate the Pojo class and the SQL

CREATE TABLE person( id INT(11) NOT NULL AUTO_INCREMENT, NAME VARCHAR(64) DEFAULT NULL, age INT(4) DEFAULT NULL, pername VARCHAR(64) DEFAULT NULL COMMENT ‘人名‘, roleId INT(11) DEFAULT NULL COMMENT ‘个人角色‘, create_date DATETIME NULL DEFAULT NULL, PRIMARY KEY(id))ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# #2. The code used to generate the code:

//利用genPojoCodeToConsole 生成代码        System.out.println("开始生成代码：");        try {            sqlManager.genPojoCodeToConsole("person");            sqlManager.genSQLTemplateToConsole("person");            sqlManager.genSQLTemplateToConsole("user");        } catch (Exception e) {            e.printStackTrace();        }        System.out.println("代码生成结束....");

Execution Result:
To start generating code:
Package com.test;
Import java.math.*;
Import Java.util.Date;
Import Java.sql.Timestamp;
Import org.beetl.sql.core.annotatoin.Table;

/*

• Gen by Beetlsql 2018-08-17br/>*/
  @Table (name= "Beetlsql.person")
  < p="">

  Private Integer ID;
  Private Integer age;
  /
  Personal role
  /
  Private Integer Roleid;
  private String name;
  /
  Names
  /
  Private String Pername;
  Private Date CreateDate;

  Public person () {
  }

  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;
  }

  /**

  • Personal role
    @return
    /
    Public Integer Getroleid () {
    return Roleid;
    }
    /**
  • Personal role
    @param Roleid
    /
    public void Setroleid (Integer roleid) {
    This.roleid = Roleid;
    }

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

  /**

  • Names
    @return
    /
    Public String Getpername () {
    return pername;
    }
    /**
  • Names
    @param pername
    /
    public void Setpername (String pername) {
    This.pername = Pername;
    }

  Public Date getcreatedate () {
  return createdate;
  }
  public void Setcreatedate (Date createdate) {
  This.createdate = CreateDate;
  }

}

Sample

• Comments

  Select #use ("Cols") # from the person where #use ("condition") #

Cols

id,NAME,age,pername,roleId,create_date

Updatesample

id=#id#,NAME=#name#,age=#age#,pername=#pername#,roleId=#roleid#,create_date=#createDate#

Condition

1 = 1  @if(!isEmpty(id)){ and id=#id#@}@if(!isEmpty(name)){ and NAME=#name#@}@if(!isEmpty(age)){ and age=#age#@}@if(!isEmpty(pername)){ and pername=#pername#@}@if(!isEmpty(roleid)){ and roleId=#roleid#@}@if(!isEmpty(createDate)){ and create_date=#createDate#@}sample

===

• Comments

  Select #use ("Cols") # from user where #use ("condition") #

Cols

id,name,age,username,roleId,create_date

Updatesample

id=#id#,name=#name#,age=#age#,username=#username#,roleId=#roleid#,create_date=#createDate#

Condition

1 = 1  @if(!isEmpty(id)){ and id=#id#@}@if(!isEmpty(name)){ and name=#name#@}@if(!isEmpty(age)){ and age=#age#@}@if(!isEmpty(username)){ and username=#username#@}@if(!isEmpty(roleid)){ and roleId=#roleid#@}@if(!isEmpty(createDate)){ and create_date=#createDate#@}（4）利用外部sql文件进行数据库表的操作

# #1. Write the SQL file structure in MD format and the contents as follows (for example, query)

select====    select * from user where 1=1    @if(!isEmpty(age)){    and age=#age#    @}    @if(!isEmpty(name)){    and name=#name#    @}

# #2. Main method Code

    //5.利用sql文件中sql语句进行查询    User query2 = new User();    query.setName("xiandafu");    System.out.println("开始使用sql文件中sql语句进行查询....");    List<User> list2 = sqlManager.select("user.select",User.class,query2);    for(User u:list2) {        System.out.println(u);    }    System.out.println("sql文件中sql语句进行查询成功...");     

Execution Result:
start query with SQL statement in SQL file ....
┏━━━━━debug [User.select]━━━
┣sql:select * from user where 1=1
┣ parameter: []
┣ position: Main. Testbeetlsql.main (testbeetlsql.java:88)
┣ time: 78ms
┣ Result: [one]
┗━━━━━debug [User.select]━━━

User [Id=1, age=19, Roleid=null, Name= Zhukov, Username=null, Createdate=null]
User [id=2, age=19, Roleid=null, name= Manstein, Username=mansitanying, Createdate=null]
User [id=3, age=19, Roleid=null, Name= Manstein, Username=deguoyuanshuai, Createdate=null]
User [id=4, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=5, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=6, age=19, Roleid=null, Name= Manstein, UserName=null, Createdate=null]
User [id=7, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=8, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=9, age=19, Roleid=null, Name= Manstein, UserName=null, Createdate=null]
User [id=10, age=19, Roleid=null, Name= Manstein, Username=null, Createdate=null]
User [id=11, age= Roleid=null, Name= Manstein, Username=null, createdate=null]
SQL statements are successfully queried ...

Here we see the SQL file, this chapter does not do a detailed explanation, we first simple to understand the SQL ask price possible to write br/> (5) Simple understanding of SQL file Writing
# #1. In MD format, = = = Above is the only identifier of the SQL statement, the following is the SQL statement
# #[email protected] and carriage return are bound symbols, you can write BEETL statements inside

# #4. IsEmpty is a function of beetll to determine if a variable is empty or not present
# #5. File name Convention is the class name, first letter lowercase
(6) Mapping of Sqlid to SQL files
The mapping of Sqlid to SQL files is done through class sqlidnameconversion, which provides the defaultsqlidnameconversion implementation by default, that is, the last part of the "." Distinction is the SQL fragment name, preceded by the file-relative path, If Sqlid is User.select, then select is the SQL fragment name, user is the file name, Beetlsql will look for/user.sql,/user.md in the root directory, but also look for the database dialect directory, such as if the use of MySQL database, The first to find/mysql/user.md,/mysql/user.sql and then look for/user.md,/user.sql.
If SQL is Test.user.select, the "select" Fragment is found under/TEST/USER.MD (SQL) or/MYSQL/TEST/USER.MD (SQL)
"This summary is complete"

Beetlsql Framework Learning (I.)--initial knowledge of Beetlsql, features, use of built-in SQL, Pojo code generation, etc.