BeetlSQL Framework Learning (1)-- first acquaintance with BeetlSQL, features, use of built-in sql, Pojo code generation, etc. 02/25 Update SLTechnology News&Howtos

BeetlSQL Framework Learning (1)-- first acquaintance with BeetlSQL, features, use of built-in sql, Pojo code generation, etc.

2025-02-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

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

Learning BeetlSQL Summary (1)

1. BeetlSQL features:

1. Development efficiency:

(1) No annotations are needed, and a large number of built-in sql can be used automatically to quickly complete the functions of adding, deleting, changing and querying.

(2) the data model supports Pojo, fast model such as Map/List, and hybrid model.

(3) SQL template is implemented based on B eetl, which is easier to write and debug, as well as extension.

(4) Pojo classes and sql models can be generated for a single table (or view), or even the whole database, which can effectively reduce the amount of code writing.

two。 Maintainability:

(1) SQL is managed centrally in a more concise and Markdown way, and it is convenient for program development and database sql debugging.

(2) sql files can be automatically mapped to dao interface classes.

(3) flexibly and intuitively support one-to-one, one-to-many and many-to-many relational mapping without introducing complex OR Maping concepts and technologies.

(4) have Interceptor function, debug, performance diagnose SQL, and extend other functions.

3. Other:

(1) built-in open source tools that support master-slave databases

(2) performance is several times higher than that of JPA,Mybatis

(3) support cross-database platforms to minimize developers' workload. Currently, cross-database platforms are supported by mysql,postgres,oracle,sqlserver,h3,sqllite,DB2.

Second, take the mevan project as an example (understand BeetlSQL)

Since I have finished all the code shown this time, I will show the code structure in advance.

1. Create a mevan project (BeetlSQL)

I have created the mevan project with the following structure:

two。 Add BeetlSQL framework (configure pom.xml)

4.0.0 cn.com.dhcc.beetl BeetlSQL war 0.0.1-SNAPSHOT BeetlSQL Maven Webapp http://maven.apache.org junit junit 3.8.1 test ch.qos.logback logback-classic 1.2. 3 com.ibeetl beetl 2.8.6 com.ibeetl beetlsql 2.10.31 mysql mysql-connector-java 8.0.11 BeetlSQL

3. Because we are going to operate on the database, the second step is to create the database and database tables.

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 'username', roleId INT (11) DEFAULT NULL COMMENT 'user role', create_date DATETIME NULL DEFAULT NULL, PRIMARY KEY (id)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

4. Next, write the entity entity class corresponding to the datasheet user (automatic code generation will be shown 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 + ", name=" + name + ", userName=" + userName + ", createDate=" + createDate + "]";}}

5. Let's write a main method to test and understand the function of BeetlSQL.

(1) establish a connection to the database. Here we do not write a configuration file, and the parameters required for each database connection 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 statement is placed in classpath's sql directory SQLLoader loader=new ClasspathLoader ("/ sql") / / Database naming is the same as java naming, so it uses DefaultNameConversion, and there is also a UnderlinedNameConversion underscore style UnderlinedNameConversion nc=new UnderlinedNameConversion (); / / finally, create a SQLManager,DebugInterceptor, which is not necessary, but you can view the execution of SQL through it SQLManager sqlManager=new SQLManager (mysql, loader,source,nc,new Interceptor [] {new DebugInterceptor ()})

(2) next, we will first use the built-in sql statement to operate on the database table, as follows

/ / 1. Add new users using the built-in generated sql. If you need to get the primary key, you can pass KeyHolder System.out.println ("start using built-in sql for user addition..."); User user=new User (); user.setAge (19); user.setName ("Manstein"); int num=sqlManager.insert (user) If (num > 0) {System.out.println ("added successfully using built-in sql user!");} / / 2. Use built-in sql to query user int id=1; System.out.println ("start using built-in sql for user query..."); user=sqlManager.unique (User.class, id); System.out.println ("use built-in sql for user query finished!") / / 3. Update (modify) data, only update column System.out.println whose value is not null based on id ("start updating user information using built-in sql..."); 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 ("Manstein"); System.out.println ("start template query..."); List userList=sqlManager.template (query); System.out.println ("print query results:"); for (User u:userList) {System.out.println (u) } System.out.println ("query using template succeeded...")

The implementation results are as follows:

Start using the built-in sql to add users.

┏━ Debug [user._gen_insert] ━━━

┣ SQL: insert into user (name,age,create_date) VALUES

┣ parameter: [Manstein, 19, null]

┣ location: main.TestBeetlSQL.main (TestBeetlSQL.java:52)

┣ time: 319ms

┣ update: [1]

┗━ Debug [user._gen_insert] ━━━

Use built-in sql user to add successfully!

Start using built-in sql for user queries.

┏━ Debug [user._gen_selectById] ━━━

┣ SQL: select * from user where id =?

┣ parameter: [1]

┣ location: main.TestBeetlSQL.main (TestBeetlSQL.java:60)

┣ time: 37ms

┣ result: [User [id=1, age=19, roleId=null, name= Zhukov, userName=null, createDate=null]]

┗━ Debug [user._gen_selectById] ━━━

Use built-in sql for user query. Over!

Start updating user information using the built-in sql.

┏━ Debug [user._gen_updateTemplateById] ━━━

┣ SQL: update user set name=? Where id =?

┣ parameter: [Zhukov, 1]

┣ location: 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: [Manstein]

┣ location: main.TestBeetlSQL.main (TestBeetlSQL.java:77)

┣ time: 91ms

┣ result: [10]

┗━ Debug [user._gen_selectByTemplate] ━━━

Print the 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]

Query using template succeeded.

Start querying using the sql statement in the sql file.

┏━ Debug [user.select] ━━━

┣ SQL: select * from user where 1

┣ parameter: []

┣ location: 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]