SpringBoot + MyBatis(注解版)，常用的SQL方法
一、新建项目及配置
1.1 新建一个SpringBoot项目，并在pom.xml下加入以下代码
<dependency>
<groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.1</version> </dependency>
application.properties文件下配置（使用的是MySql数据库）
# 注：我的SpringBoot 是2.0以上版本，数据库驱动如下
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://127.0.0.1:3306/database?characterEncoding=utf8&serverTimezone=UTC spring.datasource.username=your_username spring.datasource.password=your_password
# 可将 com.dao包下的dao接口的SQL语句打印到控制台，学习MyBatis时可以开启
logging.level.com.dao=debug
SpringBoot启动类Application.java 加入@SpringBootApplication 注解即可（一般使用该注解即可，它是一个组合注解）
@SpringBootApplication public class Application { public static void main(String[] args) { SpringApplication.run(Application.class, args); } }
之后dao层的接口文件放在Application.java 能扫描到的位置即可，dao层文件使用@Mapper注解
@Mapper public interface UserDao { /** * 测试连接 */ @Select("select 1 from dual") int testSqlConnent(); }
测试接口能返回数据即表明连接成功
二、简单的增删查改sql语句
2.1 传入参数
(1) 可以传入一个JavaBean
(2) 可以传入一个Map
(3) 可以传入多个参数，需使用@Param("ParamName") 修饰参数
2.2 Insert，Update，Delete 返回值
接口方法返回值可以使用 void 或 int，int返回值代表影响行数
2.3 Select 中使用@Results 处理映射
查询语句中，如何名称不一致，如何处理数据库字段映射到Java中的Bean呢？
(1) 可以使用sql 中的as 对查询字段改名，以下可以映射到User 的 name字段
@Select("select "1" as name from dual")
User testSqlConnent();
(2) 使用 @Results，有 @Result(property="Java Bean name", column="DB column name"), 例如：
@Select("select t_id, t_age, t_name "
+ "from sys_user "
+ "where t_id = #{id} ")
@Results(id="userResults", value={
@Result(property="id", column="t_id"),
@Result(property="age", column="t_age"),
@Result(property="name", column="t_name"),
})
User selectUserById(@Param("id") String id);
对于resultMap 可以给与一个id，其他方法可以根据该id 来重复使用这个resultMap。例如：
@Select("select t_id, t_age, t_name "
+ "from sys_user "
+ "where t_name = #{name} ")
@ResultMap("userResults")
User selectUserByName(@Param("name") String name);
2.4 注意一点，关于JavaBean 的构造器问题
我在测试的时候，为了方便，给JavaBean 添加了一个带参数的构造器。后面在测试resultMap 的映射时，发现把映射关系@Results 注释掉，返回的bean 还是有数据的；更改查询字段顺序时，出现 java.lang.NumberFormatException: For input string: "hello"的异常。经过测试，发现是bean 的构造器问题。并有以下整理：
(1) bean 只有一个有参的构造方法，MyBatis 调用该构造器(参数按顺序)，此时@results 注解无效。并有查询结果个数跟构造器不一致时，报异常。
(2) bean 有多个构造方法，且没有 无参构造器，MyBatis 调用跟查询字段数量相同的构造器；若没有数量相同的构造器，则报异常。
(3) bean 有多个构造方法，且有 无参构造器， MyBatis 调用无参数造器。
(4) 综上，一般情况下，bean 不要定义有参的构造器；若需要，请再定义一个无参的构造器。
2.5 简单查询例子
/** * 测试连接 */ @Select("select 1 from dual") int testSqlConnent(); /** * 新增，参数是一个bean */ @Insert("insert into sys_user " + "(t_id, t_name, t_age) " + "values " + "(#{id}, #{name}, ${age}) ") int insertUser(User bean); /** * 新增，参数是一个Map */ @Insert("insert into sys_user " + "(t_id, t_name, t_age) " + "values " + "(#{id}, #{name}, ${age}) ") int insertUserByMap(Map<String, Object> map); /** * 新增，参数是多个值，需要使用@Param来修饰 * MyBatis 的参数使用的@Param的字符串，一般@Param的字符串与参数相同 */ @Insert("insert into sys_user " + "(t_id, t_name, t_age) " + "values " + "(#{id}, #{name}, ${age}) ") int insertUserByParam(@Param("id") String id, @Param("name") String name, @Param("age") int age); /** * 修改 */ @Update("update sys_user set " + "t_name = #{name}, " + "t_age = #{age} " + "where t_id = #{id} ") int updateUser(User bean); /** * 删除 */ @Delete("delete from sys_user " + "where t_id = #{id} ") int deleteUserById(@Param("id") String id); /** * 删除 */ @Delete("delete from sys_user ") int deleteUserAll(); /** * truncate 返回值为0 */ @Delete("truncate table sys_user ") void truncateUser(); /** * 查询bean * 映射关系@Results * @Result(property="java Bean name", column="DB column name"), */ @Select("select t_id, t_age, t_name " + "from sys_user " + "where t_id = #{id} ") @Results(id="userResults", value={ @Result(property="id", column="t_id"), @Result(property="age", column="t_age"), @Result(property="name", column="t_name", javaType = String.class), }) User selectUserById(@Param("id") String id); /** * 查询List */ @ResultMap("userResults") @Select("select t_id, t_name, t_age " + "from sys_user ") List<User> selectUser(); @Select("select count(*) from sys_user ") int selectCountUser();
三、MyBatis动态SQL
注解版下，使用动态SQL需要将sql语句包含在script标签里
<script></script>
3.1 if
通过判断动态拼接sql语句，一般用于判断查询条件
<if test=''>...</if>
3.2 choose
根据条件选择
<choose>
<when test=''> ...
</when>
<when test=''> ...
</when>
<otherwise> ...
</otherwise>
</choose>
3.3 where，set
一般跟if 或choose 联合使用，这些标签或去掉多余的 关键字 或 符号。如
<where>
<if test="id != null ">
and t_id = #{id}
</if>
</where>
若id为null，则没有条件语句；若id不为 null，则条件语句为 where t_id = ?
<where> ... </where> <set> ... </set>
3.4 bind
绑定一个值，可应用到查询语句中
<bind name="" value="" />
3.5 foreach
循环，可对传入和集合进行遍历。一般用于批量更新和查询语句的 in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
(1) item：集合的元素，访问元素的Filed 使用 #{item.Filed}
(2) index: 下标，从0开始计数
(3) collection：传入的集合参数
(4) open：以什么开始
(5) separator：以什么作为分隔符
(6) close：以什么结束
例如 传入的list 是一个 List<String>: ["a","b","c"]，则上面的 foreach 结果是: ("a", "b", "c")
3.6 动态SQL例子
/** * if 对内容进行判断 * 在注解方法中，若要使用MyBatis的动态SQL，需要编写在<script></script>标签内 * 在 <script></script>内使用特殊符号，则使用java的转义字符，如 双引号 "" 使用"" 代替 * concat函数：mysql拼接字符串的函数 */ @Select("<script>" + "select t_id, t_name, t_age " + "from sys_user " + "<where> " + " <if test='id != null and id != ""'> " + " and t_id = #{id} " + " </if> " + " <if test='name != null and name != ""'> " + " and t_name like CONCAT('%', #{name}, '%') " + " </if> " + "</where> " + "</script> ") @Results(id="userResults", value={ @Result(property="id", column="t_id"), @Result(property="name", column="t_name"), @Result(property="age", column="t_age"), }) List<User> selectUserWithIf(User user); /** * choose when otherwise 类似Java的Switch，选择某一项 * when...when...otherwise... == if... if...else... */ @Select("<script>" + "select t_id, t_name, t_age " + "from sys_user " + "<where> " + " <choose> " + " <when test='id != null and id != ""'> " + " and t_id = #{id} " + " </when> " + " <otherwise test='name != null and name != ""'> " + " and t_name like CONCAT('%', #{name}, '%') " + " </otherwise> " + " </choose> " + "</where> " + "</script> ") @ResultMap("userResults") List<User> selectUserWithChoose(User user); /** * set 动态更新语句，类似<where> */ @Update("<script> " + "update sys_user " + "<set> " + " <if test='name != null'> t_name=#{name}, </if> " + " <if test='age != null'> t_age=#{age}, </if> " + "</set> " + "where t_id = #{id} " + "</script> ") int updateUserWithSet(User user); /** * foreach 遍历一个集合，常用于批量更新和条件语句中的 IN * foreach 批量更新 */ @Insert("<script> " + "insert into sys_user " + "(t_id, t_name, t_age) " + "values " + "<foreach collection='list' item='item' " + " index='index' separator=','> " + "(#{item.id}, #{item.name}, #{item.age}) " + "</foreach> " + "</script> ") int insertUserListWithForeach(List<User> list); /** * foreach 条件语句中的 IN */ @Select("<script>" + "select t_id, t_name, t_age " + "from sys_user " + "where t_name in " + " <foreach collection='list' item='item' index='index' " + " open='(' separator=',' close=')' > " + " #{item} " + " </foreach> " + "</script> ") @ResultMap("userResults") List<User> selectUserByINName(List<String> list); /** * bind 创建一个变量，绑定到上下文中 */ @Select("<script> " + "<bind name=\"lname\" value=\"'%' + name + '%'\" /> " + "select t_id, t_name, t_age " + "from sys_user " + "where t_name like #{lname} " + "</script> ") @ResultMap("userResults") List<User> selectUserWithBind(@Param("name") String name);
四、MyBatis 对一，对多查询
首先看@Result注解源码
public @interface Result { boolean id() default false; String column() default ""; String property() default ""; Class<?> javaType() default void.class; JdbcType jdbcType() default JdbcType.UNDEFINED; Class<? extends TypeHandler> typeHandler() default UnknownTypeHandler.class; One one() default @One; Many many() default @Many; }
可以看到有两个注解 @One 和 @Many，MyBatis就是使用这两个注解进行对一查询和对多查询
@One 和 @Many写在@Results 下的 @Result 注解中，并需要指定查询方法名称。具体实现看以下代码
// User Bean的属性 private String id; private String name; private int age; private Login login; // 每个用户对应一套登录账户密码 private List<Identity> identityList; // 每个用户有多个证件类型和证件号码 // Login Bean的属性 private String username; private String password; // Identity Bean的属性 private String idType; private String idNo;
/** * 对@Result的解释 * property： java bean 的成员变量 * column： 对应查询的字段，也是传递到对应子查询的参数，传递多参数使用Map column = "{param1=SQL_COLUMN1,param2=SQL_COLUMN2}" * one=@One： 对一查询 * many=@Many： 对多查询 * select： 需要查询的方法，全称或当前接口的一个方法名
* fetchType.EAGER: 急加载 */ @Select("select t_id, t_name, t_age " + "from sys_user " + "where t_id = #{id} ") @Results({ @Result(property="id", column="t_id"), @Result(property="name", column="t_name"), @Result(property="age", column="t_age"), @Result(property="login", column="t_id", one=@One(select="com.github.mybatisTest.dao.OneManySqlDao.selectLoginById", fetchType=FetchType.EAGER)), @Result(property="identityList", column="t_id", many=@Many(select="selectIdentityById", fetchType=FetchType.EAGER)), }) User2 selectUser2(@Param("id") String id); /** * 对一 子查询 */ @Select("select t_username, t_password " + "from sys_login " + "where t_id = #{id} ") @Results({ @Result(property="username", column="t_username"), @Result(property="password", column="t_password"), }) Login selectLoginById(@Param("id") String id); /** * 对多 子查询 */ @Select("select t_id_type, t_id_no " + "from sys_identity " + "where t_id = #{id} ") @Results({ @Result(property="idType", column="t_id_type"), @Result(property="idNo", column="t_id_no"), }) List<Identity> selectIdentityById(@Param("id") String id);
测试结果，可以看到只调用一个方法查询，相关对一查询和对多查询也会一并查询出来
// 测试类代码 @Test public void testSqlIf() { User2 user = dao.selectUser2("00000005"); System.out.println(user); System.out.println(user.getLogin()); System.out.println(user.getIdentityList()); } // 查询结果 User2 [id=00000005, name=name_00000005, age=5] Login [username=the_name, password=the_password] [Identity [idType=01, idNo=12345678], Identity [idType=02, idNo=987654321]]
五、MyBatis开启事务
5.1 使用 @Transactional 注解开启事务
@Transactional标记在方法上，捕获异常就rollback，否则就commit。自动提交事务。
/** * @Transactional 的参数 * value |String | 可选的限定描述符，指定使用的事务管理器 * propagation |Enum: Propagation | 可选的事务传播行为设置 * isolation |Enum: Isolation | 可选的事务隔离级别设置 * readOnly |boolean | 读写或只读事务，默认读写 * timeout |int (seconds) | 事务超时时间设置 * rollbackFor |Class<? extends Throwable>[] | 导致事务回滚的异常类数组 * rollbackForClassName |String[] | 导致事务回滚的异常类名字数组 * noRollbackFor |Class<? extends Throwable>[] | 不会导致事务回滚的异常类数组 * noRollbackForClassName |String[] | 不会导致事务回滚的异常类名字数组 */ @Transactional(timeout=4) public void testTransactional() { // dosomething..
}
5.2 使用Spring的事务管理
如果想使用手动提交事务，可以使用该方法。需要注入两个Bean，最后记得提交事务。
@Autowired private DataSourceTransactionManager dataSourceTransactionManager; @Autowired private TransactionDefinition transactionDefinition; public void testHandleCommitTS(boolean exceptionFlag) { // DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition(); // transactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // 开启事务 TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition); try { // dosomething
// 提交事务 dataSourceTransactionManager.commit(transactionStatus); } catch (Exception e) { e.printStackTrace(); // 回滚事务 dataSourceTransactionManager.rollback(transactionStatus); } }
六、使用SQL语句构建器
MyBatis提供了一个SQL语句构建器，可以让编写sql语句更方便。缺点是比原生sql语句，一些功能可能无法实现。
有两种写法，SQL语句构建器看个人喜好，我个人比较熟悉原生sql语句，所有挺少使用SQL语句构建器的。
(1) 创建一个对象循环调用方法
new SQL().DELETE_FROM("user_table").WHERE("t_id = #{id}").toString()
(2) 内部类实现
new SQL() {{ DELETE_FROM("user_table");
WHERE("t_id = #{id}"); }}.toString();
需要实现ProviderMethodResolver接口，ProviderMethodResolver接口属于比较新的api，如果找不到这个接口，更新你的mybatis的版本，或者Mapper的@SelectProvider注解需要加一个 method注解， @SelectProvider(type = UserBuilder.class, method = "selectUserById")
继承ProviderMethodResolver接口，Mapper中可以直接指定该类即可，但对应的Mapper的方法名要更Builder的方法名相同 。
Mapper: @SelectProvider(type = UserBuilder.class) public User selectUserById(String id); UserBuilder: public static String selectUserById(final String id)...
dao层代码：(建议在dao层中的方法加上@see的注解，并指示对应的方法，方便后期的维护)
/** * @see UserBuilder#selectUserById() */ @Results(id ="userResults", value={ @Result(property="id", column="t_id"), @Result(property="name", column="t_name"), @Result(property="age", column="t_age"), }) @SelectProvider(type = UserBuilder.class) User selectUserById(String id); /** * @see UserBuilder#selectUser(String) */ @ResultMap("userResults") @SelectProvider(type = UserBuilder.class) List<User> selectUser(String name); /** * @see UserBuilder#insertUser() */ @InsertProvider(type = UserBuilder.class) int insertUser(User user); /** * @see UserBuilder#insertUserList(List) */ @InsertProvider(type = UserBuilder.class) int insertUserList(List<User> list); /** * @see UserBuilder#updateUser() */ @UpdateProvider(type = UserBuilder.class) int updateUser(User user); /** * @see UserBuilder#deleteUser() */ @DeleteProvider(type = UserBuilder.class) int deleteUser(String id);
Builder代码：
public class UserBuilder implements ProviderMethodResolver { private final static String TABLE_NAME = "sys_user"; public static String selectUserById() { return new SQL() .SELECT("t_id, t_name, t_age") .FROM(TABLE_NAME) .WHERE("t_id = #{id}") .toString(); } public static String selectUser(String name) { SQL sql = new SQL() .SELECT("t_id, t_name, t_age") .FROM(TABLE_NAME); if (name != null && name != "") { sql.WHERE("t_name like CONCAT('%', #{name}, '%')"); } return sql.toString(); } public static String insertUser() { return new SQL() .INSERT_INTO(TABLE_NAME) .INTO_COLUMNS("t_id, t_name, t_age") .INTO_VALUES("#{id}, #{name}, #{age}") .toString(); } /** * 使用SQL Builder进行批量插入 * 关键是sql语句中的values格式书写和访问参数 * values格式：values (?, ?, ?) , (?, ?, ?) , (?, ?, ?) ... * 访问参数：MyBatis只能读取到list参数，所以使用list[i].Filed访问变量，如 #{list[0].id} */ public static String insertUserList(List<User> list) { SQL sql = new SQL() .INSERT_INTO(TABLE_NAME) .INTO_COLUMNS("t_id, t_name, t_age"); StringBuilder sb = new StringBuilder(); for (int i = 0; i < list.size(); i++) { if (i > 0) { sb.append(") , ("); } sb.append("#{list["); sb.append(i); sb.append("].id}, "); sb.append("#{list["); sb.append(i); sb.append("].name}, "); sb.append("#{list["); sb.append(i); sb.append("].age}"); } sql.INTO_VALUES(sb.toString()); return sql.toString(); } public static String updateUser() { return new SQL() .UPDATE(TABLE_NAME) .SET("t_name = #{name}", "t_age = #{age}") .WHERE("t_id = #{id}") .toString(); } public static String deleteUser() { return new SQL() .DELETE_FROM(TABLE_NAME) .WHERE("t_id = #{id}") .toString();
或者 Builder代码：
public class UserBuilder2 implements ProviderMethodResolver { private final static String TABLE_NAME = "sys_user"; public static String selectUserById() { return new SQL() {{ SELECT("t_id, t_name, t_age"); FROM(TABLE_NAME); WHERE("t_id = #{id}"); }}.toString(); } public static String selectUser(String name) { return new SQL() {{ SELECT("t_id, t_name, t_age"); FROM(TABLE_NAME); if (name != null && name != "") { WHERE("t_name like CONCAT('%', #{name}, '%')"); } }}.toString(); } public static String insertUser() { return new SQL() {{ INSERT_INTO(TABLE_NAME); INTO_COLUMNS("t_id, t_name, t_age"); INTO_VALUES("#{id}, #{name}, #{age}"); }}.toString(); } public static String updateUser(User user) { return new SQL() {{ UPDATE(TABLE_NAME); SET("t_name = #{name}", "t_age = #{age}"); WHERE("t_id = #{id}"); }}.toString(); } public static String deleteUser(final String id) { return new SQL() {{ DELETE_FROM(TABLE_NAME); WHERE("t_id = #{id}"); }}.toString(); } }
七、附属链接
7.1 MyBatis官方源码
https://github.com/mybatis/mybatis-3 ，源码的测试包跟全面，更多的使用方法可以参考官方源码的测试包
7.2 MyBatis官方中文文档
http://www.mybatis.org/mybatis-3/zh/index.html ，官方中文文档，建议多阅读官方的文档
7.3 我的测试项目地址，可做参考
https://github.com/caizhaokai/spring-boot-demo ，SpringBoot+MyBatis+Redis的demo，有兴趣的可以看一看
