MyBatis注解方式增删查改及实现动态SQL的条件查询，批量删除/插入

清疚 2023-03-13 12:25 17阅读 0赞

## MyBatis的注解开发 ##

MyBatis提供了下面注解进行映射文件配置  
@Select 查询数据注解  
@Insert 插入数据注解  
@Delete 删除数据注解  
@Update 修改数据注解  
@Options 选项配置  
@Results 手动映射配置  
@Result ： @results中的具体的某一列的映射信息配置

### 主配置文件 mybatis-config.xml ###

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
      PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-config.dtd">
    
      
      
      
    <configuration>
    
    	
    	<properties resource="db.properties"/>
    	
    	
    	<settings> 
    		
    		 <setting name="mapUnderscoreToCamelCase" value="true"/> 
    		
    	 </settings>
    
    	
    		<typeAliases>
    			
    			<typeAlias type="com.ywq.mybatis.pojo.User" alias="User"/>
    			
    			
    			<package name="com.ywq.mybatis.pojo"/>
    		</typeAliases>
    	
    	
      <environments default="mysql">
      
      	
        <environment id="mysql">
        
        	
          <transactionManager type="JDBC"/>
          
          
          <dataSource type="POOLED">
          
         	 
            <property name="driver" value="${driverClassName}"/>
            
            <property name="url" value="${url}"/>
            
            <property name="username" value="${username}"/>
            
            <property name="password" value="${password}"/>
            
          </dataSource>
        </environment>
      </environments>
      
      
      <mappers>
       
       
       
       <mapper class="com.ywq.mybatis.mapper.UserMapper"/>
      </mappers>
    </configuration>

### 获取Sql工厂工具 ###

package com.ywq.utils;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.jdbc.Null;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    public class MybatisUtils {
        
    public static SqlSessionFactory sessionFactory = null;
    
    static {
        
    try {
        
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    sessionFactory = new SqlSessionFactoryBuilder().build(is);
    } catch (IOException e) {
        
    e.printStackTrace();
    }
    }
    
    public static SqlSession getSqlSession(){
        
    return sessionFactory.openSession();
    }
    public static void colose(SqlSession sqlSession){
        
    if (sqlSession != null) {
        
    sqlSession.close();
    }
    }
    }

### pojo类代码 ###

package com.ywq.mybatis.pojo;
    
    public class User {
        
    	private Integer id ;
    	private String name;
    	private String password;
    	private Integer age;
    	public Integer getId() {
        
    		return id;
    	}
    	public void setId(Integer id) {
        
    		this.id = id;
    	}
    	public String getName() {
        
    		return name;
    	}
    	public void setName(String name) {
        
    		this.name = name;
    	}
    	public String getPassword() {
        
    		return password;
    	}
    	public void setPassword(String password) {
        
    		this.password = password;
    	}
    	public Integer getAge() {
        
    		return age;
    	}
    	public void setAge(Integer age) {
        
    		this.age = age;
    	}
    	public User(Integer id, String name, String password, Integer age) {
        
    		super();
    		this.id = id;
    		this.name = name;
    		this.password = password;
    		this.age = age;
    	}
    	public User() {
        
    		super();
    	}
    	@Override
    	public String toString() {
        
    		return "User [id=" + id + ", name=" + name + ", password=" + password + ", age=" + age + "]";
    	}
    	
    	
    }

### Mapper接口代码 ###

package com.ywq.mybatis.mapper;
    
    import java.util.List;
    
    import javax.annotation.Resource;
    import javax.annotation.Resources;
    
    import org.apache.ibatis.annotations.Delete;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import org.apache.ibatis.annotations.Update;
    
    import com.ywq.mybatis.pojo.User;
    
    
    /*
     *mybatis的映射接口
     * */
    public interface UserMapper {
        
    		
    	// DML ：增删改
    	@Insert("insert into user (name,password,age)values(#{name},#{password},#{age})")
    	int insert(User user);
    	
    	@Delete("delete from user where id = #{id}")
    	int deleteByPrimaryKey(Integer id);
    	
    	@Update("update user set name = #{name},password=#{password},age=#{age} where id =#{id}")
    	int updateByPrimaryKey(User user);
    	
    	//DQL : 查询
    	@Select("select * from user where id = #{id}")
    	User selectByPrimaryKey(Integer id);
    	
    	@Results({
        
    		
    		@Result(id = true,column = "u_id",property = "id"),
    		@Result(column = "u_name",property = "name"),
    		@Result(column = "u_password",property = "password"),
    		@Result(column = "u_age",property = "age")
    		
    	})
    	@Select("select id u_id,name u_name,password u_password, age u_age from user")
    	List<User> selectLits();
    }

### 测试代码 ###

package com.ywq.mybatis.test;
    
    import static org.junit.Assert.*;
    
    import java.util.List;
    
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import com.ywq.mybatis.mapper.UserMapper;
    import com.ywq.mybatis.pojo.User;
    import com.ywq.mybatis.util.MyBatisUtils;
    
    public class MyBatisTest {
        
    	
    	@Test
    	public void testInert() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		//2.创建UserMapper映射接口的动态代理对象
    		UserMapper userMapper = session.getMapper(UserMapper.class);
    		System.out.println(userMapper.getClass().getName());
    		
    		//3.执行insert方法
    		User user = new User(null, "jocn", "2020", 33);
    		
    		int row = userMapper.insert(user);
    		System.out.println("受影响的行数："+row);
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    	}
    	
    	@Test
    	public void testUpdate() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		
    		//2.创建UserMapper映射接口的动态对立对象
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		//3.执行修改方法
    		
    		User user = new User(6, "都邦", "5161", 25);
    		int row = mapper.updateByPrimaryKey(user);
    		
    		System.out.println("受影响行数"+row);
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    		
    	}
    	
    	@Test
    	public void testDelete() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		
    		//2.创建UserMapper映射接口的动态对立对象
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		//3.执行删除方法
    		
    		int row = mapper.deleteByPrimaryKey(11);
    		
    		System.out.println("受影响行数"+row);
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    		
    	}
    	
    	@Test
    	public void testSelectOne() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		
    		//2.创建UserMapper映射接口的动态对立对象
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		//3.执行单行查询方法
    		
    		User user = mapper.selectByPrimaryKey(5);
    		
    		System.out.println(user);
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    		
    	}
    	
    	@Test
    	public void testSelectList() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		
    		//2.创建UserMapper映射接口的动态对立对象
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		//3.执行查询方法
    		
    		List<User> users = mapper.selectLits();
    		for (User user : users) {
        
    			System.out.println(user);
    			
    		}
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    		
    	}
    }

--------------------

### 动态SQL的Mapper接口代码 ###

package com.ywq.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 com.ywq.mybatis.pojo.User;
    
    
    /*
     *mybatis的映射接口
     * */
    public interface UserMapper {
        
    	
    	/*
    	 * 查询动态SQL语句的注解
    	 * @SelectProvider(type = ,method=)
    	 * type=编写动态SQL语句的字节码（专门编写动态Sql语句的场所）
    	 * method="编写动态SQL语句类的方法名称"
    	 * 
    	 * */
    	
    	//根据条件查询结果
    	@SelectProvider(type =UserSqlProvider.class ,method="selectByCondition")
    	List<User> selectByCondition(User user);
    	
    	//根据提交查询总数
    	@SelectProvider(type =UserSqlProvider.class ,method="selectTotalByCondition")
    	Long selectTotalByCondition(User user);
    	
    	
    	//修改用户
    	@UpdateProvider(type =UserSqlProvider.class ,method="updateUserByNotNull")
    	int updateUserByNotNull(User user);
    	
    	
    	//批量删除用户
    	@DeleteProvider(type = UserSqlProvider.class,method = "deleteByIds")
    	int deleteByIds(@Param("ids")Integer[] ids);
    	
    	//批量插入
    	@InsertProvider(type = UserSqlProvider.class,method = "insertByBatch")
    	int insertByBatch(@Param("users")List<User> users);
    }

### 动态SQL实现类代码 ###

package com.ywq.mybatis.mapper;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.jdbc.SQL;
    
    import com.ywq.mybatis.pojo.User;
    
    /*
     *专门编写动态sql语句的类
     *
     * 提供一个返回Strin字符串的方法
     * 
     * 方法内部就是编写动态SQL语句
     * */
    public class UserSqlProvider {
        
    
    	/*
    	 * 此方法就是编写条件查询动态SQL语句方法
    	 * 
    	 * */
    	
    	public  String selectByCondition(User user) {
        
    		/*
    		 * SQL:MyBatis专门封装用于拼接SQL语句的类
    		 * */
    		
    		SQL sql = new SQL();
    		sql.SELECT("*");//select *
    		sql.FROM("user");//from user
    		
    		if (user.getName() != null) {
        
    			sql.WHERE("name like concat('%',#{name},'%')");
    		}
    		//默认多条件之间是AND关系
    		if (user.getAge() !=null) {
        
    			//让条件变为OR关系
    			sql.OR();
    			sql.WHERE("age = #{age}");
    			
    		}
    		
    		return sql.toString();
    		
    	}
    	
    	public String selectTotalByCondition(User user) {
        
    		/*
    		 * SQL:MyBatis专门封装用于拼接SQL语句的类
    		 * */
    		
    		SQL sql = new SQL();
    		sql.SELECT("count(*)");//select *
    		sql.FROM("user");//from user
    		
    		if (user.getName() != null) {
        
    			sql.WHERE("name like concat('%',#{name},'%')");
    		}
    		//默认多条件之间是AND关系
    		if (user.getAge() !=null) {
        
    			//让条件变为OR关系
    			sql.OR();
    			sql.WHERE("age = #{age}");
    			
    		}
    		
    		return sql.toString();
    		
    	}
    	
    	
    	public String updateUserByNotNull(User user) {
        
    		// 创建SQL对象
    		SQL sql = new SQL();
    		sql.UPDATE("user"); //update user
    		
    		if (user.getName() != null) {
        
    			sql.SET("name = #{name}");//set name = #{name}
    		}
    		
    		if (user.getPassword() != null) {
        
    			sql.SET("password = #{password}");// set password = #{password}
    		}
    		
    		if (user.getAge() != null) {
        
    			sql.SET("age = #{age}"); // set age = #{age}
    		}
    		
    		sql.WHERE("id= #{id}");// where id =#{id}
    		return sql.toString();
    	}
    	
    	public String deleteByIds(@Param("ids")Integer[] ids) {
        
    		
    		/*
    		 * SQL对象并不能满足所有的业务需求，只能拼接完成常见比较简单的动态SQL
    		 * 如果是比较复杂业务需求，还需要开发者自己拼接SQL
    		 * 	如：批量删除，批量插入
    		 * 
    		 * */
    		
    		//delete from user where id in(?,?,?)
    		SQL sql = new SQL();
    		sql.DELETE_FROM("user"); // delete from user
    		
    		//拼接条件: (#{ids[0]},#{ids[1]},#{ids[2]})
    		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("id in"+sb.toString());
    		System.out.println("SQL:"+sql.toString());
    		return sql.toString();
    		
    	}
    	
    	public String insertByBatch(@Param("users")List<User> users) {
        
    		/*
    		 * SQL语句：
    		 * insert into user(name,password,age)values
     		 *	('张三','sda',25)
     		 *	('李四','dfga',55)
     		 *	('王五','hda',15)
    		 * 
    		 * */
    		
    		StringBuffer sb = new StringBuffer();
    		
    		sb.append("insert into user(name,password,age)values");
    		
    		for (int i = 0; i < users.size(); i++) {
        
    			sb.append("(#{users["+i+"].name},#{users["+i+"].password},#{users["+i+"].age}),");
    		}
    		
    		//删除最后一个多余的逗号
    		sb.deleteCharAt(sb.length()-1);
    		return sb.toString();
    		
    		
    	}
    	
    }

## 测试代码 ##

package com.ywq.mybatis.test;
    
    import static org.junit.Assert.*;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import com.ywq.mybatis.mapper.UserMapper;
    import com.ywq.mybatis.pojo.User;
    import com.ywq.mybatis.util.MyBatisUtils;
    
    public class MyBatisTest {
        
    	
    	@Test
    	public void testSelectOne() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		//2.创建UserMapper映射接口的动态代理对象
    		UserMapper userMapper = session.getMapper(UserMapper.class);
    		
    		//3.执行条件查询方法
    		User user = new User();
    		user.setName("李");
    		user.setAge(20);
    		
    		List<User> users = userMapper.selectByCondition(user);
    		
    		for (User user2 : users) {
        
    			System.out.println(user2);
    		}
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    	}
    	
    	
    	@Test
    	public void testSelect() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		//2.创建UserMapper映射接口的动态代理对象
    		UserMapper userMapper = session.getMapper(UserMapper.class);
    		
    		//3.执行条件查询总数方法
    		User user = new User();
    		//user.setName("李");
    		user.setAge(25);
    		
    		Long users = userMapper.selectTotalByCondition(user);
    		System.out.println(users);
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    	}
    	
    	
    	@Test
    	public void testUpdate() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		//2.创建UserMapper映射接口的动态代理对象
    		UserMapper userMapper = session.getMapper(UserMapper.class);
    		
    		//3.执行修改方法
    		User user = new User();
    		user.setId(7);
    		user.setName("dd");
    		
    		int row = userMapper.updateUserByNotNull(user);
    		System.out.println(row);
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    	}
    	
    		
    	
    	@Test
    	public void testDelete() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		//2.创建UserMapper映射接口的动态代理对象
    		UserMapper userMapper = session.getMapper(UserMapper.class);
    		
    		//3.执行批量删除方法
    		Integer [] ids = {
        6,7};
    		userMapper.deleteByIds(ids);
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    	}
    	
    	
    	@Test
    	public void testInsert() throws Exception {
        
    		
    		//1.创建SqlSession对象
    		SqlSession session = MyBatisUtils.openSession();
    		//2.创建UserMapper映射接口的动态代理对象
    		UserMapper userMapper = session.getMapper(UserMapper.class);
    		
    		//3.执行批量插入方法
    		List<User> users = new ArrayList<>();
    		
    		for (int i = 1; i <=5; i++) {
        
    			User user = new User(null, "猴子"+i, "houzi"+i, i);
    			users.add(user);
    		}
    		
    		int row = userMapper.insertByBatch(users);
    		System.out.println("受影响行数"+row);
    		
    		//4.MyBatis默认需要手动提交事务
    		session.commit();
    		
    		//5.关闭session（释放资源）
    		session.close();
    	}
    }