# 快速开始
## 环境准备
项目演示使用maven工程,新建一个Maven工程,直接引用如下库
```xml
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetlsql</artifactId>
<version>${version}</version>
</dependency>
```
然后再引入数据库驱动,本章使用H2数据库作为例子,作为快速开始,避免安装数据库服务器,你也可以使用任意其他数据库
```xml
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.200</version>
</dependency>
```
BeetlSQL访问数据库不依赖数据库连接池,但有数据库连接池是大多数项目的标配。这里使用HikariCP
```xml
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.3.1</version>
</dependency>
```
最后,需要准备一个sql脚本,以初始化H2数据库。保存如下sql到resources/db/schema.sql
```sql
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;
```
> 这个脚本也适合mysql执行
## 环境搭建
创建一个Java类com.QuickTest,如下
```java
import com.zaxxer.hikari.HikariDataSource;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.H2Style;
import org.beetl.sql.ext.DBInitHelper;
import org.beetl.sql.ext.DebugInterceptor;
import javax.sql.DataSource;
import java.util.Set;
public class QuickTest {
private static DataSource datasource() {
HikariDataSource ds = new HikariDataSource();
//内存数据库
ds.setJdbcUrl("jdbc:h2:mem:dbtest;DB_CLOSE_ON_EXIT=FALSE");
ds.setUsername("sa");
ds.setPassword("");
ds.setDriverClassName("org.h2.Driver");
return ds;
}
private static SQLManager getSQLManager(){
//得到一个数据源
DataSource dataSource = datasource();
//得到一个ConnectionSource, 单数据源
ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
//SQLManagerBuilder 唯一必须的参数就是ConnectionSource
SQLManagerBuilder builder = new SQLManagerBuilder(source);
//命名转化,数据库表和列名下划线风格,转化成Java对应的首字母大写,比如create_time 对应ceateTime
builder.setNc(new UnderlinedNameConversion());
//拦截器,非必须,这里设置一个debug拦截器,可以详细查看执行后的sql和sql参数
builder.setInters(new Interceptor[]{new DebugInterceptor()});
//数据库风格,因为用的是H2,所以使用H2Style,
builder.setDbStyle(new H2Style());
SQLManager sqlManager = builder.build();
return sqlManager;
}
public static void main(String[] args) throws Exception {
SQLManager sqlManager = getSQLManager();
//初始化数据脚本,执行后,内存数据库将有一个sys_user表和模拟数据
DBInitHelper.executeSqlScript(sqlManager,"db/schema.sql");
// 得到数据库的所有表
Set<String> all = sqlManager.getMetaDataManager().allTable();
System.out.println(all);
}
}
```
如上main方法将会得到一个sqlMannger类,这个是操作数据库的的基础类,提供了所有访问数据库的API。可以现在运行main方法,看看` System.out.println(all); `是否输出了sys_user.
新建一个POJO对象 com.UserEntity,与sys_user表对应
```java
@Data
@Table(name="sys_user")
public class UserEntity {
@AutoID
private Integer id;
private String name;
private Integer departmentId;
}
```
这个Pojo对象我们注意到使用了@Table注解,表明了关联的表是sys_user. @Table注解是非必须的,如果符合命名转化(NameConversion).也可以不需要
@AutoID 作用于注解上,表示这是一个自增主键
@Data注解是,来自lombok,自动实现getter和setter方法。BeetlSQL 同其他流行框架一样,需要POJO遵循Java规范,如果你使用lombok,工程还需要依赖
```xml
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
<scope>provided</scope>
</dependency>
```
如果你不是lombok,你需要为id和name属性添加getter和setter方法,如下
```java
@Table(name="sys_user")
public class UserEntity {
@AutoID
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
// 忽略name和departmentId
}
```
> 注意,BeetlSQL并不要求POJO与表完全对应,BeetlSQL只处理POJO和表的交集,因此,MyUser对象只有id和name属性。
## 使用BeetlSQL
有了QuickTest和MyUser后,便可以愉快的使用BeetlSQL,本接简单介绍一下常用API和使用风格
### 按照主键查寻
```
UserEntity user = sqlManager.unique(UserEntity.class,1);
```
会看到日志输出如下
```
┏━━━━━ Debug [userEntity.$selectById] ━━━
┣ SQL: select * from sys_user where 1=1 and ID = ?
┣ 参数: [1]
┣ 位置: org.beetl.sql.test.S1QuickStart.baseSqlManager(S1QuickStart.java:52)
┣ 时间: 7ms
┣ 结果: [UserEntity(id=1, name=lijz, departmentId=1)]
┗━━━━━ Debug [userEntity.$selectById] ━━━
```
> 本章节所有代码例子来自源码中的samples/quickstart 模块
### 更新
属性全部更新
```java
UserEntity user = sqlManager.unique(UserEntity.class,1);
user.setName("ok123");
sqlManager.updateById(user);
```
日志输出如下
```
┏━━━━━ Debug [userEntity.$updateById] ━━━
┣ SQL: update sys_user set NAME =? , DEPARTMENT_ID =? where 1=1 and ID = ?
┣ 参数: [ok123, 1, 1]
┣ 位置: org.beetl.sql.test.S1QuickStart.baseSqlManager(S1QuickStart.java:55)
┣ 时间: 2ms
┣ 更新: [1]
┗━━━━━ Debug [userEntity.$updateById] ━━━
```
### 按照模板查询
查询部门为1得到所有用户
```java
UserEntity template = new UserEntity();
template.setDepartmentId(1);
List<UserEntity> list = sqlManager.template(template);
```
因为只有departmentId有值,因此查询所有符合departmentId为1的数据。日志输出如下
```
┏━━━━━ Debug [userEntity.$selectByTemplate] ━━━
┣ SQL: select * from sys_user where 1=1 and DEPARTMENT_ID = ?
┣ 参数: [1]
┣ 位置: org.beetl.sql.test.S1QuickStart.baseSqlManager(S1QuickStart.java:65)
┣ 时间: 1ms
┣ 结果: [9]
┗━━━━━ Debug [userEntity.$selectByTemplate] ━━━
```
> BeetlSQL 提供了大量内置查询方法以避免写SQL,但不代表BeetlSQL不鼓励写SQL,事实上SQL是不可避免的,BeetlSQL提供了最好的SQL管理
### 执行SQL
```java
String sql = "select * from sys_user where id=?";
Integer id = 1;
SQLReady sqlReady = new SQLReady(sql,new Object[]{id});
List<UserEntity> userEntities = sqlManager.execute(sqlReady,UserEntity.class);
String updateSql = "update department set name=? where id =?";
String name="lijz";
SQLReady updateSqlReady = new SQLReady(updateSql,new Object[]{name,id});
sqlManager.executeUpdate(updateSqlReady);
```
`SQLReady` 表示已经准备好的sql和参数,可以直接交给BeetlSQL执行
输出日志如下
```
┏━━━━━ Debug [sql.select * from sys_user where id=?] ━━━
┣ SQL: select * from sys_user where id=?
┣ 参数: [1]
┣ 位置: org.beetl.sql.test.S1QuickStart.executeSql(S1QuickStart.java:77)
┣ 时间: 14ms
┣ 结果: [1]
┗━━━━━ Debug [sql.select * from sys_user where id=?] ━━━
┏━━━━━ Debug [sql.update department set name=? where id =?] ━━━
┣ SQL: update department set name=? where id =?
┣ 参数: [lijz, 1]
┣ 位置: org.beetl.sql.test.S1QuickStart.executeSql(S1QuickStart.java:82)
┣ 时间: 3ms
┣ 更新: [1]
┗━━━━━ Debug [sql.update department set name=? where id =?] ━━━
```
### 执行模板SQL
像MyBatis那样,BeetlSQL 支持模板SQL。
```java
{
String sql = "select * from sys_user where department_id=#{id} and name=#{name}";
UserEntity paras = new UserEntity();
paras.setDepartmentId(1);
paras.setName("lijz");
List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
}
{
//或者使用Map作为参数
String sql = "select * from sys_user where department_id=#{myDeptId} and name=#{myName}";
Map paras = new HashMap();
paras.put("myDeptId",1);
paras.put("myName","lijz");
List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
}
```
模板占位符默认是#{} BeetlSQL会输出"?",如果使用${} 则原样输出内容,这个同MyBatis一致。
模板使用Beetl语法,因此支持复杂的SQL构建
```java
//使用Beetl模板语句
String sql = "select * from sys_user where 1=1 \n" +
"-- @if(isNotEmpty(myDeptId)){\n" +
" and department_id=#{myDeptId}\n" +
"-- @}\n" +
"and name=#{myName}";
Map paras = new HashMap();
paras.put("myDeptId",1);
paras.put("myName","lijz");
List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras);
```
模板默认使用`-- @` 和 `回车`作为定界符,因为`--` 是sql注释符号,这有助于idea或者其他IDE不会报错
> 通常复杂的模板SQL都是在文件里维护,参考后面例子
### 使用Query
通常业务代码需要根据一定逻辑查询数据库,可以使用Query构造较为复杂的单表条件而避免写SQL
```java
Query<UserEntity> query = sqlManager.query(UserEntity.class);
List<UserEntity> entities = query.andEq("department_id",1)
.andIsNotNull("name").select();
```
日志输出如下
```
┏━━━━━ Debug [sql.SELECT * FROM PUBLIC.SYS_USER WHERE department...] ━━━
┣ SQL: SELECT * FROM PUBLIC.SYS_USER WHERE department_id = ? AND name IS NOT NULL
┣ 参数: [1]
┣ 位置: org.beetl.sql.test.S1QuickStart.query(S1QuickStart.java:131)
┣ 时间: 7ms
┣ 结果: [8]
┗━━━━━ Debug [sql.SELECT * FROM PUBLIC.SYS_USER WHERE department...] ━━━
```
使用LambdaQuery,能很好的支持数据库重构
```java
LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class);
List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1)
.andIsNotNull(UserEntity::getName).select();
```
这段查询同上段代码是一样的,不同的是使用lambda表达式,这样如果列名重构,则自动会重构这段代码,不需要作手工修改还是能正确运行
> BeetlSQL推荐一直使用LambdaQuery,Query是JDK7以前的使用方式
### 使用Mapper
BeetlSQL3更为推荐的使用Mapper,而不是SQLManager,SQLManger是更为底层的API,使用Mapper能更容易的维护业务代码
创建一个接口UserMapper
```java
public interface UserMapper extends BaseMapper<UserEntity> {
}
```
`BaseMapper`是BeetlSQL3提供的一个接口,内置了大量CRUD方法。
```java
//得到一个UserMapper接口的代理
UserMapper mapper = sqlManager.getMapper(UserMapper.class);
UserEntity me = mapper.unique(1);//同SQLManager.unique(UserEntity.class,1)
me.setName("newName");
mapper.updateById(me);//同同SQLManager.updateById(me);
```
其他常用的方法在SQLManager也有,BaseMapper有如下内置方法
```java
/**
* 通用插入,插入一个实体对象到数据库,所以字段将参与操作,除非你使用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();
```
> 如何编写自己的BaseMaper或者扩展BaseMapper,超出了本章的范围,会在后面章节提到
对于UserMapper来说,仅仅继承BaseMapper是不够的,可以在UserMapper上添加更多的数据库访问代码
```java
@Sql("select * from sys_user where id = ?")
@Select
UserEntity queryUserById(Integer id);
@Sql("update sys_user set name=? where id = ?")
@Update
int updateName(String name,Integer id);
@Template("select * from sys_user where id = #{id}")
UserEntity getUserById(Integer id);
@SpringData
List<UserEntity> queryByNameOrderById(String name);
/**
* 可以定义一个default接口
* @return
*/
default List<DepartmentEntity> findAllDepartment(){
Map paras = new HashMap();
paras.put("exlcudeId",1);
List<DepartmentEntity> list = getSQLManager().execute("select * from department where id != #{exlcudeId}",DepartmentEntity.class,paras);
return list;
}
```
* queryUserById 使用@Sql注解提供了Sql语句,@Select是可选的,默认都是查询操作
* updateName, 使用@Sql注解提供了Sql语句,使用@Update,表示这是更新语句,返回更新成功数目
* @Template 表示按照模板查询,这时候要求方法参数名字与模板语句的变量名一致。如果没有启用JDK8的parameter,则需要使用注解@Param来申明参数的名字
```java
@Template("select * from sys_user where id = #{id}")
UserEntity getUserById(@Param("id") Integer myId);
```
* queryByNameOrderById 是使用@SpringData风格来拼接一个SQL,这会在后面详细介绍
* findAllDepartment 是一个接口方法实现,你也可以通过default method来实现简单的逻辑
### 使用模板文件
稍微做个复杂项目的人都知道,把复杂SQL放到专门的SQL里维护是个很好的办法。(BeetlSQL3 提供了专门的插件来维护SQL)
默认情况下,sql文件位于classpath的sql目录下,可以在resources目录下新建一个sql目录,并在sql目录下新建一个user.md文件
内容如下
```markdown
select
===
```sql
select * from sys_user u where 1=1
-- @ if(isNotEmpty(name)){
and name like #{name}
-- @ }
order by u.id desc
```
```
如下代码可以访问并执行这个sql语句
```java
SqlId id = SqlId.of("user","select");
Map map = new HashMap();
map.put("name","n");
List<UserEntity> list = sqlManager.select(id,UserEntity.class,map);
```
sqlManager.select将会查询user.md文件下的select片段,并执行,执行结果映射成UserEntity对象。
一个markdown文件可以包含任意多个sql片段,格式如下
```markdown
文件一些说明,放在头部可有可无,如果有说明,可以是任意文字
SQL标示
===
以*开头的注释,可选
SQL语句
SQL标示2
===
SQL语句 2
```
使用SqlId指明sql文件位置不方便,更常见的是在mapper方法里调用
```java
@SqlResource("user") /*sql文件在user.md里*/
public interface UserMapper extends BaseMapper<UserEntity> {
/**
* 调用sql文件user.md#select,方法名即markdown片段名字
* @param name
* @return
*/
List<UserEntity> select(String name);
}
```
这里的select方法没有任何注解,意思是调用sql文件执行,sql文件通过@SqlResource申明,sql片段名字则同方法名,sql中用的参数则同方法参数,调用起来非常方便