mybatis系列三:使用MyBatis实现持久化操作

这里以学生表为例讲解增删改查操作

情况一   假定实体类的属性和数据库中表的列名一致

=================================mapper文件========================

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "mybatis-3-mapper.dtd">
<mapper namespace="stu">
	<select id="selectById" parameterType="int" resultType="StudentEntity">
		select * from student where stuId=#{stuId}
	</select>
	<!-- resultType只表示数据库里面一行数据所能够映射的类型 -->
	<select id="selectByGender" parameterType="string" resultType="StudentEntity">
		select * from student where gender=#{gender}
	</select>
	<insert id="saveOne" parameterType="StudentEntity"
	    useGeneratedKeys="true" keyProperty="stuId">
		insert into Student(stuName,gender,age,address,deptIdd)
		 values(#{stuName},#{gender},#{age},#{address},#{deptIdd});
	</insert>
	<!-- 如果只有一个参数,那么sql里面的参数#{qqq}是可以随便写的 -->
	<delete id="deleteByGender" parameterType="string" statementType="PREPARED">
		delete from student where gender=#{qqq}
	</delete>
	<!-- 如果sql里面有多个参数,那么可以利用parameterType="实体类"的形式传参 -->
	<!-- 这时候sql里面的参数名要和实体类的属性名一致 -->
	<update id="updateById" parameterType="StudentEntity" statementType="PREPARED">
		update student set stuName=#{stuName},gender=#{gender},
		   age=#{age},address=#{address},deptIdd=#{deptIdd}
		   where stuId=#{stuId}
	</update>
	
	<!-- 模糊查询 -->
	<select id="selectByName" parameterType="string" resultType="StudentEntity">
		select * from student 
		where stuName like CONCAT('%',#{theName},'%')
	</select>
	
	<!-- 多个参数也可以用map键值对的形式传参 -->
	<select id="selectByManyParam" parameterType="map" resultType="StudentEntity">
		select * from student
		where gender=#{theGender} and age>#{theAge}
	</select>
	
</mapper>

属性解释：
   id：与select元素的id一样，是命名空间中唯一的标识符，可以被用来引用这条语句。
   parameterType：与select元素的prameterType -样，是传人参数的类型的完全限定名或别名。别名的含义和用法见select元素中的解释。
  flushCache：将其设置为true，执行语句后会清空缓存。增删改默认值为true。
  timeout：设置驱动程序等待数据库返回结果的超时时间，如超出设置时间则抛出异常。默认不设置（驱动自行处理）。
  statementType: STATEMENT、PREPARED或CALLABLE的一种。这会让Mybatis选择使用Statement、PreparedStatement或CallableStatement。默认值为PREPARED。
  useGeneratedKeys：（仅insert才有这个属性）告诉MyBatis使用JDBC的getGencratedKeys方法来取出由数据库（像Mysql和SQL Server这样的数据库管理系统的自动递增字段）内部生成的主键。默认值为false。
  keyProperty：（仅insert才有这个属性）配置MyBatis获得主键值后通过哪个属性给JavaBean赋值，MyBatis可以通过getGeneratedKeys或者通过insert语句的selectKey子元素设置如何获取主键值。默认不设置。

实体类

==============StudentEntity.java================

package com.obtk.entitys;

public class StudentEntity implements java.io.Serializable {

	private static final long serialVersionUID = 4897498920955479723L;
	private Integer stuId;
	private String stuName;
	private String gender;
	private Integer age;
	private String address;
	private Integer deptIdd;
	
	public StudentEntity() {
	}

	public StudentEntity(String stuName, String gender,
			int age, String address) {
		this.stuName = stuName;
		this.gender = gender;
		this.age = age;
		this.address = address;
	}


	public Integer getStuId() {
		return this.stuId;
	}

	public void setStuId(Integer stuId) {
		this.stuId = stuId;
	}


	public String getStuName() {
		return this.stuName;
	}

	public void setStuName(String stuName) {
		this.stuName = stuName;
	}

	public String getGender() {
		return this.gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public void setAge(Integer age) {
		this.age = age;
	}
	public Integer getAge() {
		return age;
	}

	public String getAddress() {
		return this.address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public void setDeptIdd(Integer deptIdd) {
		this.deptIdd = deptIdd;
	}
	
	public Integer getDeptIdd() {
		return deptIdd;
	}
}

案例1   查询一个对象

sql配置：

<select id="selectById" parameterType="int" resultType="StudentEntity">
		select * from student where stuId=#{stuId}
	</select>

如果只有一个参数，那么#{stuId}可以随便乱写

代码:

package com.obtk.test;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestQueryOne {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			//5.执行语句
			StudentEntity stu=session.selectOne("stu.selectById", 120);
			System.out.println(stu.getStuName()+","+stu.getGender());
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

案例2  查询多个对象

sql语句

<!-- resultType只表示数据库里面一行数据所能够映射的类型 -->
	<select id="selectByGender" parameterType="string" resultType="StudentEntity">
		select * from student where gender=#{gender}
	</select>

代码:

package com.obtk.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestQueryMany {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			//5.执行语句
			List<StudentEntity> stuList=session.selectList("stu.selectByGender", "男");
			for(StudentEntity stu : stuList){
				System.out.println(stu.getStuName()+","+stu.getGender());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

案例3   添加数据

sql语句：

<!-- 如果sql里面有多个参数,那么可以利用parameterType="实体类"的形式传参 -->
	<!-- 这时候sql里面的参数名要和实体类的属性名一致 -->
	<insert id="saveOne" parameterType="StudentEntity"
	    useGeneratedKeys="true" keyProperty="stuId">
		insert into Student(stuName,gender,age,address,deptIdd)
		 values(#{stuName},#{gender},#{age},#{address},#{deptIdd});
	</insert>

代码:

package com.obtk.test;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestAdd {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			StudentEntity theStu=new StudentEntity("小红红", "女", 22, "学生宿舍");
			theStu.setDeptIdd(12);
			//5.执行语句
			int theId=session.insert("stu.saveOne", theStu);
			session.commit();
			//添加完成可以取出启动增长的主键
			System.out.println("添加成功!"+theId+","+theStu.getStuId());
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

案例4 ：删除数据

sql语句:

<!-- 如果只有一个参数,那么sql里面的参数#{qqq}是可以随便写的 -->
	<delete id="deleteByGender" parameterType="string" statementType="PREPARED">
		delete from student where gender=#{qqq}
	</delete>

代码:

package com.obtk.test;

import org.apache.ibatis.session.SqlSession;

import com.obtk.utils.MybatisUtil;

public class TestDelete {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			//5.执行语句
			int theId=session.delete("stu.deleteByGender", "妖");
			session.commit();
			System.out.println("删除成功!"+theId);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

案例5：修改

sql语句

<!-- 如果sql里面有多个参数,那么可以利用parameterType="实体类"的形式传参 -->
	<!-- 这时候sql里面的参数名要和实体类的属性名一致 -->
	<update id="updateById" parameterType="StudentEntity" statementType="PREPARED">
		update student set stuName=#{stuName},gender=#{gender},
		   age=#{age},address=#{address},deptIdd=#{deptIdd}
		   where stuId=#{stuId}
	</update>

代码:

package com.obtk.test;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestModify {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			StudentEntity theStu=new StudentEntity("小红花", "女", 23, "火星");
			theStu.setStuId(129);
			theStu.setDeptIdd(10);
			//5.执行语句,参数比较多，就用实体类的对象传参
			int theId=session.update("stu.updateById", theStu);
			session.commit();
			System.out.println("修改成功!"+theId);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

案例6： 模糊查询

sql语句

<!-- 模糊查询 -->
	<select id="selectByName" parameterType="string" resultType="StudentEntity">
		select * from student 
		where stuName like CONCAT('%',#{theName},'%')
	</select>

代码：

package com.obtk.test2;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestLikeQuery {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			//5.执行语句
			List<StudentEntity> stuList=session.selectList("stu.selectByName", "王");
			for(StudentEntity stu : stuList){
				System.out.println(stu.getStuName()+","+stu.getGender());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

案例7： map传参

sql语句

<!-- 多个参数也可以用map键值对的形式传参 -->
	<select id="selectByManyParam" parameterType="map" resultType="StudentEntity">
		select * from student
		where gender=#{theGender} and age>#{theAge}
	</select>

代码

package com.obtk.test2;

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

import org.apache.ibatis.session.SqlSession;

import com.obtk.entitys.StudentEntity;
import com.obtk.utils.MybatisUtil;

public class TestMapQuery {
	public static void main(String[] args) {
		SqlSession session=null;
		try {
			//4.得到session
			session=MybatisUtil.getSession();
			Map paramMap=new HashMap();
			paramMap.put("theGender", "男");
			paramMap.put("theAge", 22);
			//5.执行语句
			List<StudentEntity> stuList=session.
			         selectList("stu.selectByManyParam",paramMap);
			for(StudentEntity stu : stuList){
				System.out.println(stu.getStuName()+","+stu.getGender()
						+","+stu.getAge());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			MybatisUtil.closeSession();
		}
	}
}

情况二   若实体类的属性和数据库中表的列名不一致

现在更改一下实体类，性别属性和学生名称属性修改一下，改成和数据库表里面的列名不一致

===================StudentEntity.java=================

package com.obtk.entitys;

public class StudentEntity implements java.io.Serializable {

	private static final long serialVersionUID = 4897498920955479723L;
	private Integer stuId;
	private String studentName;  //注意该属性名和表中列名不同
	private String sex;      //注意该属性名和表中列名不同
	private Integer age;
	private String address;
	private Integer deptIdd;
	
	public StudentEntity() {
	}
	
	public StudentEntity(String studentName, String sex,
			int age, String address) {
		this.studentName = studentName;
		this.sex = sex;
		this.age = age;
		this.address = address;
	}

	public Integer getStuId() {
		return this.stuId;
	}

	public void setStuId(Integer stuId) {
		this.stuId = stuId;
	}

	public String getStudentName() {
		return studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

	public String getSex() {
		return sex;
	}


	public void setSex(String sex) {
		this.sex = sex;
	}


	public void setAge(Integer age) {
		this.age = age;
	}
	public Integer getAge() {
		return age;
	}

	public String getAddress() {
		return this.address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public void setDeptIdd(Integer deptIdd) {
		this.deptIdd = deptIdd;
	}
	
	public Integer getDeptIdd() {
		return deptIdd;
	}
}

=======================对应的mapper配置文件=======================

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "mybatis-3-mapper.dtd">
<mapper namespace="stu">
	<resultMap  id="studentMap" type="StudentEntity" autoMapping="true">
		<result property="studentName" column="stuName"/>
		<result property="sex" column="gender"/>
	</resultMap>


    <!-- resultMap表示对结果集进行映射 -->
	<select id="selectByGender" parameterType="string" resultMap="studentMap">
		select * from student where gender=#{gender}
	</select>
	
</mapper>

autoMapping="true" 表示自动映射列名和属性名相同的属性，不相同的就要单独做映射。