mybatis学习

#实体类

package com.neu.entity;

public class Dept {
	private Integer deptno;
	private String dname;
	private String loc;
	public Dept() {
		super();
		// TODO 自动生成的构造函数存根
	}
	public Dept(Integer deptno, String dname, String loc) {
		super();
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}
	public Integer getDeptno() {
		return deptno;
	}
	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getLoc() {
		return loc;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((deptno == null) ? 0 : deptno.hashCode());
		result = prime * result + ((dname == null) ? 0 : dname.hashCode());
		result = prime * result + ((loc == null) ? 0 : loc.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Dept other = (Dept) obj;
		if (deptno == null) {
			if (other.deptno != null)
				return false;
		} else if (!deptno.equals(other.deptno))
			return false;
		if (dname == null) {
			if (other.dname != null)
				return false;
		} else if (!dname.equals(other.dname))
			return false;
		if (loc == null) {
			if (other.loc != null)
				return false;
		} else if (!loc.equals(other.loc))
			return false;
		return true;
	}
	@Override
	public String toString() {
		return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
	}
}

#DeptMapper.java

package com.neu.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;

import com.neu.entity.Dept;

public interface DeptMapper {
	//全部查
	List<Dept> getAll();
	//根据deptno查
	Dept getId(int deptno);
	//增
	int Insert(Dept dept);
	//删
	int Delect(int deptno);
	//改
	int Update(Dept dept);
	//传递一个不确定的表单列名的情况和模糊查询的情况
	List<Dept> getOrder(String ord);
	//传递一个模糊查询的参数的情况
	List<Dept> getByName(String dname);
	//传递多个参数的方法,第一种 给传递的参数加标签，DeptMapper.xml中使用参数标签名  如：#{dname}、#{loc}
	List<Dept> find(@Param("dname") String dname,@Param("loc") String loc);
	//传递多个参数的方法,第二种 不给传递的参数加标签，DeptMapper.xml中使用参数索引号 如：#{0}、#{1}
	//List<Dept> find(String dname,String loc);
	//用map作为参数，传递不定个数参数的方法，DeptMapper.xml中使用参数map的键(key) 如：#{dname}、#{loc}
	List<Dept> find2(Map<String, String>params);
	//动态的sql
	List<Dept> findByExample(Dept dept);
	//动态的更新数据（可能全部更新，可能部分更新）
	int updateByExample(Dept dept);
	//动态的插入数据（可能全部更新，可能部分更新）
	int insertByExample(Dept dept);
	//批量删除(集合形式)
	int batchDelete(List<Integer> deptnos);
	//批量删除(数组形式)
	int batchDelete2(int[] deptnos);
	//批量添加
	int batchInsert(List<Dept> depts);
}

DeptMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.neu.mapper.DeptMapper">

	<!-- 全部查 -->
	<select id="getAll" resultType="com.neu.entity.Dept">
		select * from dept
	</select>
	
	<!-- 根据deptno查 -->
	<select id="getId" resultType="com.neu.entity.Dept">
		select * from dept where deptno = #{deptno}
	</select>
	
	<!-- 增 -->
	<insert id="Insert">
		insert into dept values (#{deptno},#{dname},#{loc});
	</insert>
	
	<!-- 改 -->
	<update id="Update">
		update dept set dname = #{dname},loc = #{loc} where deptno = #{deptno}
	</update>
	
	<!-- 删 -->
	<delete id="Delect">
	delete from dept where deptno = #{deptno}
	</delete>
	
	
	<select id="getOrder" resultType="com.neu.entity.Dept">
		<!-- ${value} 因为传递的是一个列名 所以需要拼字符串 不能用#{} 需要用${}里面需要放value是固定的,value代表传递的字符串，而且只能是一个参数的时候使用-->
		select * from dept order by ${value}
		
	</select>
	
	<select id="getByName" resultType="com.neu.entity.Dept">
		<!-- ${value} 因为传递的是一个模糊数据，需要拼字符串 不能用#{} 需要用${}里面需要放value是固定的,value代表传递的字符串，而且只能是一个参数的时候使用-->
		
		select * from dept where dname like '%${value}%'
	</select>
	
	<select id="find" resultType="com.neu.entity.Dept">
	<!-- #{dname} #{loc}这里的dname、loc是参数标签的名-->
		select * from dept where dname =#{dname}  and loc =#{loc} 
		<!-- #{0} #{1}这里的0、1是参数的索引号，索引号是从0开始的 -->
		select * from dept where dname =#{0}  and loc =#{1}
	</select>
	
	<select id="find2" resultType="com.neu.entity.Dept">
	<!-- #{dname} #{loc}这里的dname、loc是map集合中的key-->
		select * from dept where dname =#{dname}  and loc =#{loc} 
	</select>
	
	<!-- 动态的拼sql语句 -->
	<select id="findByExample" resultType="com.neu.entity.Dept">
		select * from dept 
		<!-- prefix="where"是前缀，表示如果trim中有执行的语句就在之前加上where -->
		<!-- prefixOverrides="and "是前缀覆盖，表示如果trim中有执行的语句就将第一个执行的语句中的and 用前缀where覆盖掉 -->
		<!-- <trim prefix="where" prefixOverrides="and "> -->
		
		<!-- <where>标签是对<trim prefix="where" prefixOverrides="and ">标签的一个封装，两者类似起到相同的作用 -->
		<where>
			<if test="deptno != null ">
				and deptno = #{deptno}
			</if>
			<if test="dname != '' and dname !=null ">
				and dname = #{dname}
			</if>
			<if test="loc != '' and loc !=null ">
				and loc = #{loc}
			</if>
		</where>
			
		<!-- </trim> -->	
	</select>
	<!-- 不定参数的更新 -->
	<update id="updateByExample">
		update dept
		<!-- suffixOverrides=","是后缀覆盖 表示如果trim中的语句执行那么最后的一个,被覆盖掉 -->
		<!-- <trim prefix="set" suffixOverrides=","> -->
		
		<!-- <set>标签是对<trim prefix="set" suffixOverrides=",">标签的一个封装，两者类似起到相同的作用 -->
		<set>
			<if test="dname != null and dname != ''">
				dname = #{dname},
			</if>
			<if test="loc != null and loc != ''">
				loc = #{loc},
			</if>
		</set>	
		<!-- </trim> -->
		where deptno = #{deptno}
	</update>
	<!-- 不定参数的插入 -->
	<insert id="insertByExample">
		insert into dept
		(
			<trim suffixOverrides=",">
				<if test="deptno !=null">
					deptno,
				</if>
				<if test="dname !=null and dname != ''">
					dname,
				</if>
				<if test="loc !=null and loc != ''">
					loc,
				</if>
			</trim>	
		) 
		values 
		(
			<trim suffixOverrides=",">
				<if test="deptno !=null">
					#{deptno},
				</if>
				<if test="dname !=null and dname != ''">
					#{dname},
				</if>
				<if test="loc !=null and loc != ''">
					#{loc},
				</if>
			</trim>
		)
	</insert>
	
	<!-- 批量添加(集合形式) -->
	<insert id="batchInsert">
		insert into dept values
		<foreach collection="list" item="dept" separator=",">
			(#{dept.deptno},#{dept.dname},#{dept.loc})
		</foreach>
	</insert>
	
	<!-- 批量删除(集合形式) -->
	<delete id="batchDelete">
		delete from dept where deptno in
		<!-- <foreach collection="list" item="deptno" open="(" close=")" separator=",">遍历输出 list集合成以(开头，)结尾，并且用,分割 -->
		<foreach collection="list" item="deptno" open="(" close=")" separator=",">
			#{deptno}
		</foreach>
	</delete>
	
	<!-- 批量删除(数组形式) -->
	<delete id="batchDelete2">
		delete from dept where deptno in
		<!-- <foreach collection="list" item="deptno" open="(" close=")" separator=",">遍历输出 list集合成以(开头，)结尾，并且用,分割 -->
		<foreach collection="array" item="deptno" open="(" close=")" separator=",">
			#{deptno}
		</foreach>
	</delete>
</mapper>

#对这些方法进行测试

package com.neu.mapper;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.neu.entity.Dept;

public class DeptMapperTest {
	private DeptMapper deptMapper;
	private SqlSession session;
	
	@Before
	public void before() throws IOException {
		//读取配置文件，连接数据库
		InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
		//创建一个SqlSessionFactory
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
		//得到与数据库进行连接的会话SqlSession
		session = factory.openSession();
		
		//得到DeptMapper接口的实现类
		deptMapper = session.getMapper(DeptMapper.class);
	}
	
	@After
	public void after() {
		//关闭
		session.close();
	}
	
	//全部查
	@Test
	public void testGetAll() throws IOException{
		
		
		List<Dept> list = deptMapper.getAll();
		
		System.out.println(list);
		
	}
	
	//根据条件查
	@Test
	public void testGetId() throws IOException{
		
		Dept dept = deptMapper.getId(10);
		
		System.out.println(dept);
		
	}
	
	//增
	@Test
	public void testInsert() {
		Dept dept = new Dept(91, "hr", "beijing");
		int n = deptMapper.Insert(dept);
		
		System.out.println(n);
		//提交事务
		session.commit();
	}
	
	//改
	@Test
	public void testUpdate() {
		Dept dept = new Dept(91, "java", "shenyang");
		int n = deptMapper.Update(dept);
		
		System.out.println(n);
		session.commit();
	}
	
	//删
	@Test
	public void testDelete() {
		int n = deptMapper.Delect(91);

		System.out.println(n);
		session.commit();
	}
	
	//传递的字符串，是一个不确定的列名
	@Test
	public void testGetOrder() throws IOException{
		
		
		List<Dept> list = deptMapper.getOrder("loc");
		
		System.out.println(list);
		
	}
	
	//传递的字符串，是模糊查询的内容
	@Test
	public void testGetByName() throws IOException{
		
		
		List<Dept> list = deptMapper.getByName("a");
		
		System.out.println(list);
		
	}
	
	//传递多个参数，在xml中使用给参数加标签名或使用参数索引号的方式
	@Test
	public void find() throws IOException{
		
		
		List<Dept> list = deptMapper.find("SALES", "CHICAGO");
		
		System.out.println(list);
		
	}
	
	//传递多个值，将参数名和参数的值以map集合的方式进行传递
	@Test
	public void find2() throws IOException{
		Map<String, String> map = new HashMap<>();
		map.put("dname", "ACCOUNTING");
		map.put("loc", "NEW YORK");
		
		List<Dept> list = deptMapper.find2(map);
		
		System.out.println(list);
		
	}
	
	//动态的拼sql语句
	@Test
	public void testFindByExample() {
		Dept dept = new Dept(10, "ACCOUNTING", null);
		List<Dept> list = deptMapper.findByExample(dept);
		System.out.println(list);
	}
	
	//不定参数个数的更新
	@Test
	public void updateByExample() {
		Dept dept = new Dept(91, "java", "shenyang");
		int n = deptMapper.updateByExample(dept);
		
		System.out.println(n);
		session.commit();
	}
	
	//不定参数个数的插入
	@Test
	public void insertByExample() {
		Dept dept = new Dept(92, "hr", "");
		int n = deptMapper.insertByExample(dept);
		
		System.out.println(n);
		//提交事务
		session.commit();
	}
	
	//批量的插入
	@Test
	public void testBatchInsert() {
		List<Dept> list = new ArrayList<>();
		
		Dept dept = new Dept(91, "java", "shenyang");
		list.add(dept);
		
		dept = new Dept(92, "hr", "beijing");
		list.add(dept);
		
		int n = deptMapper.batchInsert(list);
		System.out.println(n);
		//提交事务
		session.commit();
	}
	
	
	//批量删除(集合形式)
	@Test
	public void testBatchDelet() {
		List<Integer> list = new ArrayList<>();
		list.add(91);
		list.add(92);
		int n = deptMapper.batchDelete(list);
		System.out.println(n);
		//提交事务
		session.commit();
	}
	
	//批量删除(数组形式)
		@Test
		public void testBatchDelet2() {
			int[] deptnos= {91,92};
			int n = deptMapper.batchDelete2(deptnos);
			System.out.println(n);
			//提交事务
			session.commit();
		}
}