<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>sql-springboot-starter</artifactId>
<version>3.12.0-RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot</artifactId>
<version>2.6.0</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot</artifactId>
</dependency>
</dependencies>
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`department_id` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
BEGIN;
INSERT INTO `sys_user` VALUES (1, 'lijz', 1, NULL);
INSERT INTO `sys_user` VALUES (2, 'lucy', 1, NULL);
INSERT INTO `sys_user` VALUES (3, 'bear', 2, NULL);
INSERT INTO `sys_user` VALUES (4, 'mike', 1, NULL);
INSERT INTO `sys_user` VALUES (5, 'lisan', 1, NULL);
INSERT INTO `sys_user` VALUES (6, 'xb', 1, NULL);
INSERT INTO `sys_user` VALUES (7, 'duanwu', 2, NULL);
INSERT INTO `sys_user` VALUES (8, 'fenh', 1, NULL);
INSERT INTO `sys_user` VALUES (9, 'lj', 2, NULL);
INSERT INTO `sys_user` VALUES (10, 'gshen', 1, NULL);
INSERT INTO `sys_user` VALUES (11, 'lihui', 1, NULL);
COMMIT;
package com.it.beet_demo;
import com.zaxxer.hikari.HikariDataSource;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DBInitHelper;
import org.beetl.sql.ext.DebugInterceptor;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.util.Set;
@SpringBootTest
class BeetDemoApplicationTests {
//连接数据库获取ds
private static DataSource dataSource(){
HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl("jdbc:mysql://localhost:3306/springboot");
ds.setUsername("root");
ds.setPassword("root");
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
return ds;
}
private static SQLManager getSQLManager(){
//得到一个数据源
DataSource dataSource = dataSource();
//得到一个connectionSource,单数据源
ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
SQLManagerBuilder builder = new SQLManagerBuilder(source);
//命名转化,数据库表和列名下划线风格,转化成Java对应的首字母大写,比如create_time 对应ceateTime
builder.setNc(new UnderlinedNameConversion());
//拦截器,非必须,这里设置一个debug拦截器,可以详细查看执行后的sql和sql参数
builder.setInters(new Interceptor[]{new DebugInterceptor()});
//数据库风格,因为用的是mysql,所以使用MySqlStyle
builder.setDbStyle(new MySqlStyle());
SQLManager sqlManager = builder.build();
return sqlManager;
}
@Test
void beetTest() {
SQLManager sqlManager = getSQLManager();
//初始化数据脚本,执行后,内存数据库将有一个sys_user表和模拟数据
DBInitHelper.executeSqlScript(sqlManager,"db/schema.sql");
Set<String> all = sqlManager.getMetaDataManager().allTable();
System.out.println(all);
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9d9TKx6N-1637744290460)(C:\Users\大鹏\AppData\Roaming\Typora\typora-user-images\image-20211123152725826.png)]
@Data
@Table(name="sys_user")
public class User {
@AutoID
private Integer id;
private String name;
private Integer departmentId;
}
/***
* 按主键查找
*
*
*/
@Test
public void unique(){
SQLManager sqlManager = getSQLManager();
User unique = sqlManager.unique(User.class, 1);
System.out.println(unique);
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Npqqzla3-1637744290464)(C:\Users\大鹏\AppData\Roaming\Typora\typora-user-images\image-20211123153741056.png)]
1.unique:按照主键查寻
例子见上
1.updateXXX:跟新
update有多种方法,见下图
本次以updateById为例。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eR7P9OyB-1637744290466)(C:\Users\大鹏\AppData\Roaming\Typora\typora-user-images\image-20211123154346871.png)]
@Test
public void update(){
SQLManager sqlManager = getSQLManager();
User user = sqlManager.unique(User.class, 1);
user.setName("已修改");
sqlManager.updateById(user);
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KIegPoqP-1637744290468)(C:\Users\大鹏\AppData\Roaming\Typora\typora-user-images\image-20211123154452575.png)]
1.template:按照模板查询
@Test
public void template(){
User user = new User();
user.setDepartmentId(1);
SQLManager sqlManager = getSQLManager();
List<User> users = sqlManager.template(user);
for (User user1 : users) {
System.out.println(user1);
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RpcxZ39v-1637744290470)(C:\Users\大鹏\AppData\Roaming\Typora\typora-user-images\image-20211123155030989.png)]
BeetlSQL 提供了大量内置查询方法以避免写SQL,但不代表BeetlSQL不鼓励写SQL,事实上SQL是不可避免的,BeetlSQL提供了最好的SQL管理
SQLReady 表示已经准备好的sql和参数,可以直接交给BeetlSQL执行
@Test
public void sqlReadyTest(){
String sql = "SELECT * FROM sys_user where id=?";
Integer id =1;
SQLReady sqlReady = new SQLReady(sql,new Object[]{id});
SQLManager sqlManager = getSQLManager();
List<User> userList = sqlManager.execute(sqlReady, User.class);
for (User user : userList) {
System.out.println(user);
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WndLdvMH-1637744290472)(C:\Users\大鹏\AppData\Roaming\Typora\typora-user-images\image-20211123155906015.png)]
beet也可像MyBatis那样,BeetlSQL 支持模板SQL。
@Test
public void executeSQLTest(){
String sql = "select * from sys_user where id=#{id} and name=#{name}";
SQLManager sqlManager = getSQLManager();
User paras = new User();
paras.setId(1);
paras.setName("已修改");
List<User> userList = sqlManager.execute(sql, User.class, paras);
for (User user : userList) {
System.out.println(user);
}
}
或
@Test
public void executeSQLTest2(){
String sql = "select * from sys_user where id=#{myId} and name=#{myName}";
Map map = new HashMap();
map.put("myId",1);
map.put("myName","已修改");
SQLManager sqlManager = getSQLManager();
List<User> userList = sqlManager.execute(sql, User.class, map);
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void queryTest(){
SQLManager sqlManager = getSQLManager();
Query<User> query = sqlManager.query(User.class);
//注意andEq()中是数据库的字段,不是实体类的字段,否则会报错
List<User> userList = query.andEq("department_id", 1).andEq("name", "已修改")
.andIsNotNull("Id").select();
for (User user : userList) {
System.out.println(user);
}
}
使用LambdaQuery,能很好的支持数据库重构
BeetlSQL推荐一直使用LambdaQuery,Query是JDK7以前的使用方式
@Test
public void lambdaQuery(){
SQLManager sqlManager = getSQLManager();
LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
List<User> userList = query.andEq("department_id",1).andIsNotNull("name").select();
for (User user : userList) {
System.out.println(user);
}
}
BeetlSQL3更为推荐的使用Mapper,而不是SQLManager,SQLManger是更为底层的API,使用Mapper能更容易的维护业务代码
BaseMapper是BeetlSQL3提供的一个接口,内置了大量CRUD方法
public interface UserMapper extends BaseMapper<User>{
}
@Test
public void mapperMethod(){
SQLManager sqlManager = getSQLManager();
UserMapper mapper = sqlManager.getMapper(UserMapper.class);
User user = mapper.unique(1);
System.out.println(user);
}
@Test
public void updareMapperMethod(){
SQLManager sqlManager = getSQLManager();
UserMapper mapper = sqlManager.getMapper(UserMapper.class);
User unique = mapper.unique(1);
unique.setName("再次修改");
mapper.updateById(unique);
}
其他常用的方法在SQLManager也有,BaseMapper有如下内置方法
1.创建mapper接口,继承BaseMapper
2.通过sqlManager.getMapper(mapper),获取mapper
3.调用mapper中的方法
public interface UserMapper extends BaseMapper<User>{
}
@Test
public void insert(){
User user = new User();
user.setName("大鹏");
user.setDepartmentId(1);
SQLManager sqlManager = getSQLManager();
//调用getMapper().获取mapper
UserMapper mapper = sqlManager.getMapper(UserMapper.class);
//调用mapper中的方法
mapper.insert(user);
}
其他常用的方法在SQLManager也有,BaseMapper有如下内置方法
/**
* 通用插入,插入一个实体对象到数据库,所以字段将参与操作,除非你使用ColumnIgnore注解
*SqlResource
* @param entity
*/
@AutoMapper(InsertAMI.class)
void insert(T entity);
/**
* 插入实体到数据库,对于null值不做处理
*
* @param entity
*/
@AutoMapper(InsertTemplateAMI.class)
void insertTemplate(T entity);
/**
* 批量插入实体。此方法不会获取自增主键的值,如果需要,建议不适用批量插入,适用
* <pre>
* insert(T entity,true);
* </pre>
*
* @param list
*/
@AutoMapper(InsertBatchAMI.class)
void insertBatch(List<T> list);
/**
* 根据主键更新对象,所以属性都参与更新。也可以使用主键ColumnIgnore来控制更新的时候忽略此字段
* @param entity
* @return
*/
@AutoMapper(UpdateByIdAMI.class)
int updateById(T entity);
/**
* 根据主键更新对象,只有不为null的属性参与更新
*
* @param entity
* @return
*/
@AutoMapper(UpdateTemplateByIdAMI.class)
int updateTemplateById(T entity);
/**
* 按照主键更新更新或插入,自增或者序列id自动赋值给entity
* @param entity 待更新/插入的实体对象
* @return 如果是插入操作,返回true,如果是更新,返回false
*/
@AutoMapper(UpsertAMI.class)
boolean upsert(T entity);
/**按照主键更新或插入,更新失败,会调用插入,属性为空的字段将不更新或者插入。自增或者序列id自动赋值给entity
* @param entity 待更新/插入的实体对象
* @return
*/
@AutoMapper(UpsertByTemplateAMI.class)
int upsertByTemplate(T entity);
/**
* 根据主键删除对象,如果对象是复合主键,传入对象本生即可
*
* @param key
* @return
*/
@AutoMapper(DeleteByIdAMI.class)
int deleteById(Object key);
/**
* 根据主键获取对象,如果对象不存在,则会抛出一个Runtime异常
*
* @param key
* @return
*/
@AutoMapper(UniqueAMI.class)
T unique(Object key);
/**
* 根据主键获取对象,如果对象不存在,返回null
*
* @param key
* @return
*/
@AutoMapper(SingleAMI.class)
T single(Object key);
/**
* 根据一批主键查询
* @param key
* @return
*/
@AutoMapper(SelectByIdsAMI.class)
List<T> selectByIds(List<?> key);
default boolean exist(Object key){
return this.getSQLManager().exist(this.getTargetEntity(),key);
}
/**
* 根据主键获取对象,如果在事物中执行会添加数据库行级锁(select * from table where id = ? for update),如果对象不存在,返回null
*
* @param key
* @return
*/
@AutoMapper(LockAMI.class)
T lock(Object key);
/**
* 返回实体对应的所有数据库记录
*
* @return
*/
@AutoMapper(AllAMI.class)
List<T> all();
/**
* 返回实体在数据库里的总数
*
* @return
*/
@AutoMapper(AllCountAMI.class)
long allCount();
/**
* 模板查询,返回符合模板得所有结果。beetlsql将取出非null值(日期类型排除在外),从数据库找出完全匹配的结果集
*
* @param entity
* @return
*/
@AutoMapper(TemplateAMI.class)
List<T> template(T entity);
/**
* 模板查询,返回一条结果,如果没有,返回null
*
* @param entity
* @return
*/
@AutoMapper(TemplateOneAMI.class)
<T> T templateOne(T entity);
/**
* 符合模板得个数
*
* @param entity
* @return
*/
@AutoMapper(TemplateCountAMI.class)
long templateCount(T entity);
/**
* 执行一个jdbc sql模板查询
*
* @param sql
* @param args
* @return
*/
@AutoMapper(ExecuteAMI.class)
List<T> execute(String sql, Object... args);
/**
* 执行一个更新的jdbc sql
*
* @param sql
* @param args
* @return
*/
@AutoMapper(ExecuteUpdateAMI.class)
int executeUpdate(String sql, Object... args);
@AutoMapper(GetSQLManagerAMI.class)
SQLManager getSQLManager();
/**
* 返回一个Query对象
*
* @return
*/
@AutoMapper(QueryAMI.class)
Query<T> createQuery();
/**
* 返回一个LambdaQuery对象
*
* @return
*/
@AutoMapper(LambdaQueryAMI.class)
LambdaQuery<T> createLambdaQuery();
/**
* 得到mapper的范型类
* @return
*/
@AutoMapper(GetTargetEntityAMI.class)
Class getTargetEntity();
public interface UserMapper extends BaseMapper<User>{
@Sql("select * from sys_user where id=?")
@Select
User queryUserById(Integer id);
@Sql("update sys_user set name=? where id=?")
@Update
Integer updateName(Integer id,String name);
@Template("select * from sys_user where id=#{id}")
User getUserById(@Param("id") Integer id);
}
@Test
public void queryUserById(){
SQLManager sqlManager = getSQLManager();
UserMapper mapper = sqlManager.getMapper(UserMapper.class);
User user = mapper.queryUserById(1);
System.out.println(user);
}
@Test
public void updateName(){
SQLManager sqlManager = getSQLManager();
UserMapper mapper = sqlManager.getMapper(UserMapper.class);
Integer i = mapper.updateName(2, "修改name");
System.out.println(i);
}
@Test
public void getUserById(){
SQLManager sqlManager = getSQLManager();
UserMapper mapper = sqlManager.getMapper(UserMapper.class);
User user = mapper.getUserById(2);
System.out.println(user);
}
1.默认情况下,sql文件位于classpath的sql目录下,可以在resources目录下新建一个sql目录,并在sql目录下新建一个user.md文件
2.读取文件并执行
select
===
```sql
select * from sys_user u where 1=1
-- @ if(isNotEmpty(name)){ *注意--与@之间有空格
AND name like #{name}
-- @ }
order BY u.id desc
```
@Test
public void queryByMD(){
SqlId id = SqlId.of("user", "select");
SQLManager sqlManager = getSQLManager();
HashMap map = new HashMap();
map.put("name","%11%");
List<User> users = sqlManager.select(id, User.class, map);
for (User user : users) {
System.out.println(user);
}
}