基于mybatis的CRUD

u 基于Mybatis的CRUD
u 掌握MyBatis的结果类型-resultMap和resultType
u 掌握MyBatis的参数类型
u 掌握#和$两种语法

1 基于mybatis的CRUD

1.1 StudentDAO

 public interface StudentDao {

     public void insertStudent(Student s);

     public void updateStudent(Student s);

     public void deleteStudent(String stuid);

     public Student selectStudentById(String stuid);

     public List<Student> selectStudent();

 }

1.1 StudentDaoImpl

 package cn.mybatis.dao.impl;

 import java.util.ArrayList;

 import java.util.List;

 import org.apache.ibatis.session.SqlSession;

 import org.apache.ibatis.session.SqlSessionFactory;

 import cn.mybatis.dao.StudentDao;

 import cn.mybatis.domain.Student;

 public class StudentDaoImpl implements StudentDao{

     private SqlSessionFactory fac;

     public StudentDaoImpl(SqlSessionFactory fac){

         this.fac = fac;

     }

     @Override

     public void insertStudent(Student s) {

         SqlSession sess = null;

         try{

             sess = this.fac.openSession();

             sess.insert("student.insertStudent", s);

             sess.commit();

         }catch(Exception e){

             e.printStackTrace();

         }finally{

             sess.close();

         }

     }

     @Override

     public void updateStudent(Student s) {

         SqlSession sess = null;

         try{

             sess = this.fac.openSession();

             sess.update("student.updateStudent", s);

             sess.commit();

         }catch(Exception e){

             e.printStackTrace();

         }finally{

             sess.close();

         }

     }

     @Override

     public void deleteStudent(String stuid) {

         SqlSession sess = null;

         try{

             sess = this.fac.openSession();

             sess.delete("student.deleteStudent", stuid);

             sess.commit();

         }catch(Exception e){

             e.printStackTrace();

         }finally{

             sess.close();

         }

     }

     @Override

     public Student selectStudentById(String stuid) {

         SqlSession sess = null;

         Student s = new Student();

         try{

             sess = this.fac.openSession();

             s = sess.selectOne("student.selectStudentById", stuid);

             sess.commit();

         }catch(Exception e){

             e.printStackTrace();

         }finally{

             sess.close();

         }

         return s;

     }

     @Override

     public List<Student> selectStudent() {

         SqlSession sess = null;

         List<Student> sList = new ArrayList<Student>();

         try{

             sess = this.fac.openSession();

             sList = sess.selectList("student.selectStudent");

             sess.commit();

         }catch(Exception e){

             e.printStackTrace();

         }finally{

             sess.close();

         }

         return sList;

     }

 }

1.1 StudentMapper.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="student">

     <resultMap type="student" id="BaseResultMap">

         <id column="stu_id" property="stuId" jdbcType="VARCHAR" javaType="java.lang.String" />

         <result column="stu_name" property="stuName" jdbcType="VARCHAR" javaType="java.lang.String" />

         <result column="stu_birthdate" property="stuBirthdate" jdbcType="DATE" javaType="java.util.Date" />

         <result column="stu_phone" property="stuPhone" jdbcType="VARCHAR" javaType="java.lang.String" />

     </resultMap>

     <!-- 插入数据 -->

     <insert id="insertStudent" parameterType="student">

         insert into student (stu_id,stu_name,stu_birthdate,stu_phone)

         values(#{stuId},#{stuName},#{stuBirthdate},#{stuPhone})

     </insert>

     <!-- 更新数据 -->

     <update id="updateStudent" parameterType="student">

         update student set stu_name=#{stuName}, stu_birthdate=#{stuBirthdate},

         stu_phone=#{stuPhone} where stu_id=#{stuId}

     </update>

     <!-- 删除数据 -->

     <delete id="deleteStudent" parameterType="string">

         delete from student where stu_id=#{stuId}

     </delete>

     <!-- 查询数据，返回的数据会根据resultMap设置封装到实体类对象中 -->

     <select id="selectStudentById" resultMap="BaseResultMap" parameterType="string" >

         select * from student where stu_id=#{stuId}

     </select>

     <select id="selectStudent" resultMap="BaseResultMap">

         select * from student

     </select>

 </mapper>

1.1 测试demo

 package cn.mybatis.demo;

 ……

 public class Demo_01 {

     private static SqlSessionFactory fac;

     static{

         InputStream is = null;

         try{

             //处理并根据config配置文件实例化SqlSessionFactory

             is = Resources.getResourceAsStream("SqlMapperConfig.xml");

             //获取session工厂类

             fac = new SqlSessionFactoryBuilder().build(is);

         }catch(Exception e){

             e.printStackTrace();

             Logger.getLogger(Demo_01.class).debug(e.getMessage());

         }

     }

     public static void main(String[] args) throws Exception {

         //创建要保存的学生信息

         Student s = new Student();

         s.setStuId("1");

         s.setStuName("zhouyeqin");

         s.setStuBirthdate(new SimpleDateFormat("yyyy-MM-dd").parse("1990-1-12"));

         s.setStuPhone("341324123");

         StudentDao sdao = new StudentDaoImpl(fac);

         //插入数据

 //        sdao.insertStudent(s);

         //更新数据

 //        sdao.updateStudent(s);

         //删除数据

 //        sdao.deleteStudent("1");

         //根据id查询数据

 //        Student s1 = sdao.selectStudentById("1");

 //        System.out.println(s1);

         //查询所有数据

 //        List<Student> s2 = sdao.selectStudent();

 //        System.out.println(s2);

     }

 }

2 MyBatis的结果类型-resultMap和resultType

<select>元素的返回值设置有两个常用属性：

resultMap

属性值是已经定义好的<resultMap>元素的id

示例：

<select id="query" resultMap="BaseResultMap">

resultType

属性值有以下情况：

单一类型，例如<select id="getCount" resultType="java.lang.Integer">
组合类型，一般都是pojo类，需要注意查询语句的列名或别名必须要和pojo类的属性名称一致，否则无法映射，例如<select id="query2" resultType="com.icss.MyBatis.pojo.Student">
Map类型，列值会自动封装为键值对Map集合，键为列名，值为列值，例如<select id="query3" resultType="java.util.HashMap">

resultMap使用情况：

 <!-- 查询单条 -->

 <select id="queryById" resultMap="BaseResultMap" parameterType="java.lang.Integer" >

 select * from student where stu_id=#{stuId}

 </select>

 <!-- 查询多条 -->

 <select id="query" resultMap="BaseResultMap">

 select * from student

 </select>

resultType使用情况：

studentMapper.xml

 <!-- 返回总记录数 -->

     <select id="getCount" resultType="java.lang.Integer">

         select count(*) from student

     </select>

     <!-- 查询多条，返回类型为pojo类型，前提是列名（别名）必须和pojo类属性名称一致 -->

     <select id="query2" resultType="cn.mybatis.domain.Student">

         SELECT s.stu_id AS stuId,s.stu_name AS stuName,

         s.stu_birthdate AS stuBirthdate,s.stu_phone AS stuPhone

         FROM student s

     </select>

     <!-- 查询多条，返回类型为List<Map>类型 -->

     <select id="query3" resultType="java.util.HashMap">

         select * from student

     </select>

StudentDao.java

 public void testGetCount();

     public void testQuery2();

     public void testQuery3();

StudentDaoImpl.java

 @Override

     public void testGetCount() {

         SqlSession session = fac.openSession();

         Integer count = (Integer) session.selectOne("student.getCount");

         System.out.println("count=" + count);

         session.close();

     }

     @Override

     public void testQuery2() {

         SqlSession session = fac.openSession();

         List<Student> list = session.selectList("student.query2");

         for (Student stu : list) {

             System.out.println(stu.getStuName());

         }

         session.close();

     }

     @Override

     public void testQuery3() {

         SqlSession session = fac.openSession();

         List<Map<String, Object>> list = session.selectList("student.query3");

         for (Map<String, Object> map : list) {

             System.out.println("---------------------");

             System.out.println("stu_id=" + map.get("STU_ID"));

             System.out.println("stu_name=" + map.get("STU_NAME"));

             System.out.println("stu_birthdate=" + map.get("STU_BIRTHDATE"));

             System.out.println("stu_phone=" + map.get("STU_PHONE"));

             System.out.println("---------------------");

         }

         session.close();

     }

测试

 public static void main(String[] args) throws Exception {

         StudentDao sdao = new StudentDaoImpl(fac);

 //        sdao.testGetCount();

         sdao.testQuery2();

         sdao.testQuery3();

     }

3 MyBatis的参数类型

CRUD操作都有可能传入参数，参数类型的设置需要用到parameterType属性，属性值有以下几种情况：

单一类型，例如

复合类型，例如

<update id="update" parameterType="com.icss.MyBatis.pojo.Student">

Map集合类型，例如

参数案例：

studentMapper.xml

 <!-- 查询多条，传入类型为Map类型，注意左右尖括号的转义 -->

 <select id="query4" parameterType="java.util.HashMap" resultMap="BaseResultMap">

     <![CDATA[

     select * from student

     where stu_birthdate>=#{startDate} and stu_birthdate<=#{endDate}

     ]]>

 </select>

StudentDaoImpl.java

 @Override

     public void testQuery4() {

         SqlSession session = fac.openSession();

         Map<String,Object> map = new HashMap<String, Object>();

         SimpleDateFormat sdf =   new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );

         try {

             map.put("startDate", sdf.parse( "1990-07-10 19:20:00" ));

             map.put("endDate", sdf.parse( "1992-07-10 19:20:00" ));

         } catch (ParseException e) {

             e.printStackTrace();

         }

         List<Student> list =  session.selectList("student.query4",map);

         for (Student stu : list) {

             System.out.println(stu.getStuName());

             System.out.println(stu.getStuId());

         }

         session.close();

     }

4 #和$两种语法

#可以进行预编译，进行类型匹配，#{变量名}会转化为jdbc的类型
$不进行数据类型匹配，${变量名}就直接把${name}替换为name的内容

例如：

select * from tablename where id = #{id}，假设id的值为12,其中如果数据库字段id为字符型，那么#{id}表示的就是'12'，如果id为整型，那么#{id}就是 12 会转化为jdbc的 select * from tablename where id=？，把？参数设置为id的值。

select * from tablename where id = ${id} ，如果字段id为整型，Sql语句就不会出错，但是如果字段id为字符型，那么Sql语句应该写成 select * from table where id = '${id}' 。

事实上在MyBatis中使用${id}这种标识符会直接抛异常，允许直接使用的标识符只能是${value}
如果不需要动态指定SQL语句，应该尽量使用#而不是$，因为$语法容易被SQL注入

使用${}案例：

 <!-- ${}的用法 -->

     <select id="query5" resultType="java.lang.String">

         select ${value} from student

     </select>

 @Override

 public void testQuery5() {

     SqlSession session = fac.openSession();

     String result = (String) session.selectOne("student.query5","max(stu_id)");

     System.out.println("result=" + result);

     session.close();

 }

仿制SQL注入：

通过输入查询条件，猜出表名。

 <!-- ${}的用法 -->

 <select id="query5" resultType="java.lang.String">

     select stu_name from student where 1=1 and stu_id=${value}

 </select>

 @Override

 public void testQuery5() {

     SqlSession session = fac.openSession();

     String result = (String) session.selectOne("student.query5","1 and (select count(*) from student)<>0");

     System.out.println("result=" + result);

     session.close();

 }

总结：

#将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id，则解析成的sql为order by "id".
$将传入的数据直接显示生成在sql中。如：order by $user_id$，如果传入的值是111,那么解析成sql时的值为order by user_id, 如果传入的值是id，则解析成的sql为order by id.
#方式能够很大程度防止sql注入。　
$方式无法防止Sql注入。
$方式一般用于传入数据库对象，例如传入表名.　
一般能用#的就别用$.

举个例子。写一句SQL-例如：

select * from user_role where user_code = "100";

这句话而言，需要写成：

select * from ${tableName} where user_code = #{userCode}

$符是直接拼成sql的，#符则会以字符串的形式与sql进行拼接。

巴特西