基于mybatis的CRUD

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

1 基于mybatis的CRUD

1.1 StudentDAO

1 public interface StudentDao {
2     public void insertStudent(Student s);
3     public void updateStudent(Student s);
4     public void deleteStudent(String stuid);
5     public Student selectStudentById(String stuid);
6     public List<Student> selectStudent();
7 }

1.1 StudentDaoImpl

 1 package cn.mybatis.dao.impl;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 import org.apache.ibatis.session.SqlSession;
 7 import org.apache.ibatis.session.SqlSessionFactory;
 8 
 9 import cn.mybatis.dao.StudentDao;
10 import cn.mybatis.domain.Student;
11 
12 public class StudentDaoImpl implements StudentDao{
13     private SqlSessionFactory fac;
14     public StudentDaoImpl(SqlSessionFactory fac){
15         this.fac = fac;
16     }
17     @Override
18     public void insertStudent(Student s) {
19         SqlSession sess = null;
20         try{
21             sess = this.fac.openSession();
22             sess.insert("student.insertStudent", s);
23             sess.commit();
24         }catch(Exception e){
25             e.printStackTrace();
26         }finally{
27             sess.close();
28         }
29     }
30     @Override
31     public void updateStudent(Student s) {
32         SqlSession sess = null;
33         try{
34             sess = this.fac.openSession();
35             sess.update("student.updateStudent", s);
36             sess.commit();
37         }catch(Exception e){
38             e.printStackTrace();
39         }finally{
40             sess.close();
41         }
42     }
43     
44     @Override
45     public void deleteStudent(String stuid) {
46         SqlSession sess = null;
47         try{
48             sess = this.fac.openSession();
49             sess.delete("student.deleteStudent", stuid);
50             sess.commit();
51         }catch(Exception e){
52             e.printStackTrace();
53         }finally{
54             sess.close();
55         }
56     }
57     
58     @Override
59     public Student selectStudentById(String stuid) {
60         SqlSession sess = null;
61         Student s = new Student();
62         try{
63             sess = this.fac.openSession();
64             s = sess.selectOne("student.selectStudentById", stuid);
65             sess.commit();
66         }catch(Exception e){
67             e.printStackTrace();
68         }finally{
69             sess.close();
70         }
71         return s;
72     }
73     @Override
74     public List<Student> selectStudent() {
75         SqlSession sess = null;
76         List<Student> sList = new ArrayList<Student>();
77         try{
78             sess = this.fac.openSession();
79             sList = sess.selectList("student.selectStudent");
80             sess.commit();
81         }catch(Exception e){
82             e.printStackTrace();
83         }finally{
84             sess.close();
85         }
86         return sList;
87     }
88 
89 }

1.1 StudentMapper.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <mapper namespace="student">
 5     <resultMap type="student" id="BaseResultMap">
 6         <id column="stu_id" property="stuId" jdbcType="VARCHAR" javaType="java.lang.String" />
 7         <result column="stu_name" property="stuName" jdbcType="VARCHAR" javaType="java.lang.String" />
 8         <result column="stu_birthdate" property="stuBirthdate" jdbcType="DATE" javaType="java.util.Date" />
 9         <result column="stu_phone" property="stuPhone" jdbcType="VARCHAR" javaType="java.lang.String" />
10     </resultMap>
11 
12     <!-- 插入数据 -->
13     <insert id="insertStudent" parameterType="student">
14         insert into student (stu_id,stu_name,stu_birthdate,stu_phone)
15         values(#{stuId},#{stuName},#{stuBirthdate},#{stuPhone})
16     </insert>
17     
18     <!-- 更新数据 -->
19     <update id="updateStudent" parameterType="student">
20         update student set stu_name=#{stuName}, stu_birthdate=#{stuBirthdate},
21         stu_phone=#{stuPhone} where stu_id=#{stuId}
22     </update>
23     <!-- 删除数据 -->
24     <delete id="deleteStudent" parameterType="string">
25         delete from student where stu_id=#{stuId}
26     </delete>
27     
28     <!-- 查询数据，返回的数据会根据resultMap设置封装到实体类对象中 -->
29     <select id="selectStudentById" resultMap="BaseResultMap" parameterType="string" >
30         select * from student where stu_id=#{stuId}
31     </select>
32     
33     <select id="selectStudent" resultMap="BaseResultMap">
34         select * from student
35     </select>
36 </mapper>

1.1 测试demo

 1 package cn.mybatis.demo;
 2 ……
 3 public class Demo_01 {
 4     private static SqlSessionFactory fac;
 5     static{
 6         InputStream is = null;
 7         try{
 8             //处理并根据config配置文件实例化SqlSessionFactory
 9             is = Resources.getResourceAsStream("SqlMapperConfig.xml");
10             //获取session工厂类
11             fac = new SqlSessionFactoryBuilder().build(is);
12         }catch(Exception e){
13             e.printStackTrace();
14             Logger.getLogger(Demo_01.class).debug(e.getMessage());
15         }
16     }
17 
18     public static void main(String[] args) throws Exception {
19         //创建要保存的学生信息
20         Student s = new Student();
21         s.setStuId("1");
22         s.setStuName("zhouyeqin");
23         s.setStuBirthdate(new SimpleDateFormat("yyyy-MM-dd").parse("1990-1-12"));
24         s.setStuPhone("341324123");
25         
26         StudentDao sdao = new StudentDaoImpl(fac);
27         //插入数据
28 //        sdao.insertStudent(s);
29         //更新数据
30 //        sdao.updateStudent(s);
31         //删除数据
32 //        sdao.deleteStudent("1");
33         //根据id查询数据
34 //        Student s1 = sdao.selectStudentById("1");
35 //        System.out.println(s1);
36         //查询所有数据
37 //        List<Student> s2 = sdao.selectStudent();
38 //        System.out.println(s2);
39     }
40 }

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使用情况：

1 <!-- 查询单条 -->
2 <select id="queryById" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
3 select * from student where stu_id=#{stuId}
4 </select>
5 <!-- 查询多条 -->
6 <select id="query" resultMap="BaseResultMap">
7 select * from student
8 </select>

resultType使用情况：

studentMapper.xml

 1 <!-- 返回总记录数 -->
 2     <select id="getCount" resultType="java.lang.Integer">
 3         select count(*) from student
 4     </select>
 5     
 6     <!-- 查询多条，返回类型为pojo类型，前提是列名（别名）必须和pojo类属性名称一致 -->
 7     <select id="query2" resultType="cn.mybatis.domain.Student">
 8         SELECT s.stu_id AS stuId,s.stu_name AS stuName,
 9         s.stu_birthdate AS stuBirthdate,s.stu_phone AS stuPhone
10         FROM student s
11     </select>
12     
13     <!-- 查询多条，返回类型为List<Map>类型 -->
14     <select id="query3" resultType="java.util.HashMap">
15         select * from student
16     </select>

StudentDao.java

1 public void testGetCount();
2     public void testQuery2();
3     public void testQuery3();

StudentDaoImpl.java

 1 @Override
 2     public void testGetCount() {
 3         SqlSession session = fac.openSession();
 4         Integer count = (Integer) session.selectOne("student.getCount");
 5         System.out.println("count=" + count);
 6         session.close();
 7     }
 8 
 9     @Override
10     public void testQuery2() {
11         SqlSession session = fac.openSession();
12         List<Student> list = session.selectList("student.query2");
13         for (Student stu : list) {
14             System.out.println(stu.getStuName());
15         }
16         session.close();
17     }
18 
19     @Override
20     public void testQuery3() {
21         SqlSession session = fac.openSession();
22         List<Map<String, Object>> list = session.selectList("student.query3");
23         for (Map<String, Object> map : list) {
24             System.out.println("---------------------");
25             System.out.println("stu_id=" + map.get("STU_ID"));
26             System.out.println("stu_name=" + map.get("STU_NAME"));
27             System.out.println("stu_birthdate=" + map.get("STU_BIRTHDATE"));
28             System.out.println("stu_phone=" + map.get("STU_PHONE"));
29             System.out.println("---------------------");
30         }
31         session.close();
32     }

测试

1 public static void main(String[] args) throws Exception {
2         StudentDao sdao = new StudentDaoImpl(fac);
3 //        sdao.testGetCount();
4         sdao.testQuery2();
5         sdao.testQuery3();
6     }

3 MyBatis的参数类型

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

单一类型，例如

复合类型，例如

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

Map集合类型，例如

参数案例：

studentMapper.xml

1 <!-- 查询多条，传入类型为Map类型，注意左右尖括号的转义 -->
2 <select id="query4" parameterType="java.util.HashMap" resultMap="BaseResultMap">
3     <![CDATA[        
4     select * from student
5     where stu_birthdate>=#{startDate} and stu_birthdate<=#{endDate}
6     ]]>                
7 </select>

StudentDaoImpl.java

 1 @Override
 2     public void testQuery4() {
 3         SqlSession session = fac.openSession();
 4         Map<String,Object> map = new HashMap<String, Object>();
 5         SimpleDateFormat sdf =   new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); 
 6         try {
 7             map.put("startDate", sdf.parse( "1990-07-10 19:20:00" ));
 8             map.put("endDate", sdf.parse( "1992-07-10 19:20:00" ));
 9         } catch (ParseException e) {
10             e.printStackTrace();
11         }
12         List<Student> list =  session.selectList("student.query4",map);
13         for (Student stu : list) {
14             System.out.println(stu.getStuName());
15             System.out.println(stu.getStuId());
16         }
17         session.close();
18     }

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注入

使用${}案例：

 1 <!-- ${}的用法 -->
 2     <select id="query5" resultType="java.lang.String">
 3         select ${value} from student 
 4     </select>

1 @Override
2 public void testQuery5() {
3     SqlSession session = fac.openSession();
4     String result = (String) session.selectOne("student.query5","max(stu_id)");
5     System.out.println("result=" + result);
6     session.close();
7 }

仿制SQL注入：

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

1 <!-- ${}的用法 -->
2 <select id="query5" resultType="java.lang.String">
3     select stu_name from student where 1=1 and stu_id=${value}
4 </select>

1 @Override
2 public void testQuery5() {
3     SqlSession session = fac.openSession();
4     String result = (String) session.selectOne("student.query5","1 and (select count(*) from student)<>0");
5     System.out.println("result=" + result);
6     session.close();
7 }

总结：

#将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。如：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进行拼接。