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 studentwhere 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.得到sessionsession=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.得到sessionsession=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.得到sessionsession=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.得到sessionsession=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.得到sessionsession=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.得到sessionsession=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 studentwhere 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.得到sessionsession=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" 表示自动映射列名和属性名相同的属性，不相同的就要单独做映射。