# 快速开始 ## 环境准备 项目演示使用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中用的参数则同方法参数,调用起来非常方便