基于注解方式实现
动态sql除了支持xml方式以外,还是支持使用纯注解的方式
主要一下四个注解+对应动态sql语句的类文件
1.@SelectProvider 动态查询SQL语句对应注解
2.@InsertProvider 动态插入SQL语句对应注解
3.@UpdateProvider 动态修改SQL语句对应注解
4.@DeleteProvider 动态删除SQL语句对应注解
接口映射文件
基于mybatis提供sql方法
package cn.xc.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import cn.xc.mybatis.pojo.User;
public interface UserMapper {
/**
* @SelectProvider(type = UserSQLProvider.class,method = "selectByCondition")
* 查询动态sql语句的注解
* type :拼接动态sql语句的类的字节码
* method :UserSqlProvider 类中返回动态查询sql语句的方法名
* 注意:USerSQLProvider中对应的方法的参数必须和UserMapper中对应的方法参数一样
* 方法名理论上可以不限,但是建议一般和UserMapper接口中的方法名一致,(阅读更直观清晰)
*/
@SelectProvider(type = UserSQLProvider.class,method = "selectByCondition")
List<User> selectByCondition(User user);
/**
* 根据提交查询总数
* @param user
* @return
*/
@SelectProvider(type = UserSQLProvider.class,method = "selectTotalByCondition")
Long selectTotalByCondition(User user);
/**
* 修改用户
* @param user
* @return
*/
@UpdateProvider(type = UserSQLProvider.class,method="updateUserByNotNull")
int updateUserByNotNull(User user);
/**
* 批量删除用户
* @param ids
* (@Param("ids")Integer[] ids)==> 相当与Map 的 key + 参数 + 实参
* @return
*/
@DeleteProvider(type = UserSQLProvider.class,method="deleteByIds")
int deleteByIds(@Param("ids") Integer[] ids);
/**
* 插入
* @param users
* @return
*/
@InsertProvider(type = UserSQLProvider.class,method="insert")
int insert(User user);
}
动态sql语句文件
UserSQLProvider.java
package cn.xc.mybatis.mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import cn.xc.mybatis.pojo.User;
//拼接动态sql语句的类
public class UserSQLProvider {
/*
* org.apache.ibatis.jdbc.SQL 类 MyBatis框架专门为 注解方式动态SQL用于拼接动态SQL语句的类(对象) 1,new
* 一个SQL 对象
*/
public String selectByCondition(User user) {
//select * from user
SQL sql = new SQL();
sql.SELECT("*");
// sql.SELECT("id","name","password","age");
sql.FROM("user");
if (user.getName() != null) {
sql.WHERE("name like concat('%',#{name},'%')");
}
if (user.getAge() != null) {
sql.OR();
sql.WHERE("age <=#{age}");
}
System.out.println("sql :" + sql.toString());
return sql.toString();
}
public String selectTotalByCondition(User user) {
// select * from user
SQL sql = new SQL();
sql.SELECT("count(*)");
// sql.SELECT("id","name","password","age");
sql.FROM("user");
if (user.getName() != null) {
sql.WHERE("name like concat('%',#{name},'%')");
}
if (user.getAge() != null) {
sql.OR();
sql.WHERE("age <=#{age}");
}
return sql.toString();
}
public String updateUserByNotNull(User user) {
SQL sql = new SQL();
sql.UPDATE("user");
if (user.getName() != null) {
sql.SET("name = #{name}");// name = #{name}
}
if (user.getPassword() != null) {
sql.SET("password = #{password}");
}
if (user.getAge() != null) {
sql.SET("age = #{age}");
}
// where条件
sql.WHERE("id = #{id}");
System.out.println("sql :" + sql.toString());
// UPDATE user SET name = #{name}, password = #{password} WHERE (id = #{id})
return sql.toString();
}
// 批量删除
public String deleteByIds(@Param("ids") Integer[] ids) {
//delete from user where id in (#{ids[0],#{ids[1]}})
SQL sql = new SQL();
sql.DELETE_FROM("user");
//手动拼接 :(#{ids[0],#{ids[1]}})
StringBuilder sb = new StringBuilder();
sb.append("(");
for (int i = 0; i < ids.length; i++) {
sb.append("#{ids["+i+"]},");
}
//删除最后一个多余的逗号
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
System.out.println(sb.toString());
//把上面手动拼接的条件添加到 sql.WHERE 中
sql.WHERE("id in "+sb.toString());
//DELETE FROM user WHERE (id in (#{ids[0]},#{ids[1]},#{ids[2]},#{ids[3]}))
System.out.println("sql :" + sql.toString());
return sql.toString();
}
// 插入
public String insert(User user) {
SQL sql = new SQL();
sql.INSERT_INTO("user");
sql.VALUES("name", "#{name}");
sql.VALUES("password", "#{password}");
sql.VALUES("age", "#{age}");
System.out.println("sql :" + sql.toString());
//INSERT INTO user (name, password, age) VALUES (#{name}, #{password}, #{age})
return sql.toString();
}
}
测试代码
package cn.xc.mybatis.test;
import java.util.Arrays;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import cn.xc.mybatis.mapper.UserMapper;
import cn.xc.mybatis.pojo.User;
import cn.xc.mybatis.utils.MyBatisUtil;
public class UserMapperTest {
// 多行查询
@Test
public void testSelectByCondition() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象(接口不能直接创建对象,底层创建代理对象)
UserMapper userMapper = session.getMapper(UserMapper.class);
//条件对象
User conditionUser = new User();
conditionUser.setName("哥");
//conditionUser.setAge(30);
// 3. 执行多行查询方法
List<User> users = userMapper.selectByCondition(conditionUser);
Long total = userMapper.selectTotalByCondition(conditionUser);
for (User user : users) {
System.out.println(user);
}
System.out.println(total);
session.close();
}
// 修改功能
@Test
public void testUpdateUserByNotNull() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象(接口不能直接创建对象,底层创建代理对象)
UserMapper userMapper = session.getMapper(UserMapper.class);
// 3.执行update方法
User user = new User();
user.setId(7);
user.setName("杰克");
user.setPassword("xjg");
int row = userMapper.updateUserByNotNull(user);
// 4.手动提交事务
session.commit();
// 5.关闭Session
session.close();
}
// 批量删除
@Test
public void testDeleteByBatch() throws Exception {
// 1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
// 2.创建UserMapper 映射接口的代理对象(接口不能直接创建对象,底层创建代理对象)
UserMapper userMapper = session.getMapper(UserMapper.class);
Integer[] ids = {1,2,3,4};
int row = userMapper.deleteByIds(ids);
// 4.手动提交事务
//session.commit();
// 5.关闭Session
session.close();
}
//批量插入
@Test
public void testInsertByBatch() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtil.openSession();
//2.创建UserMapper 映射接口的代理对象(接口不能直接创建对象,底层创建代理对象)
UserMapper userMapper = session.getMapper(UserMapper.class);
User u1 = new User(null, "汤姆", "tom", 50);
User u2 = new User(null, "杰瑞", "jerry", 50);
List<User> users = Arrays.asList(u1,u2);
for (User user : users) {
userMapper.insert(user);
}
//4.手动提交事务
session.commit();
//5.关闭Session
session.close();
}
}