快速搭建Mybatis框架

ゞ浴缸里的玫瑰 2021-06-10 20:42 574阅读 0赞

# 搭建Mybatis框架 #

## 1、创建工程 ##

我创建的是一个web工程，貌似也可以创建为java工程，目录结构如下：

![20170518151404143][]

## 2、创建实体类 ##

创建实体类：TbTeacher（属性名与数据库表中的列名一致）

package com.entity; import java.sql.Date; public class TbTeacher \{ private Integer tid; private String tname; private Date ttime; /\*\* \* 构造函数 \* \* @author lvyingliang \* @param tid \* @param tname \* @param ttime \*/ public TbTeacher(Integer tid, String tname, Date ttime) \{ super(); this.tid = tid; this.tname = tname; this.ttime = ttime; \} public TbTeacher(String tname, Date ttime) \{ super(); this.tname = tname; this.ttime = ttime; \} public TbTeacher() \{ super(); // TODO Auto-generated constructor stub \} /\*\* \* @author lvyingliang \* setter getter方法 \* @return \*/ public Integer getTid() \{ return tid; \} public void setTid(Integer tid) \{ this.tid = tid; \} public String getTname() \{ return tname; \} public void setTname(String tname) \{ this.tname = tname; \} public Date getTtime() \{ return ttime; \} public void setTtime(Date ttime) \{ this.ttime = ttime; \} \}

创建实体类：TbStudent（属性名与数据库表中的列名不一致）

package com.entity; public class TbStudent \{ private int id; private String name; private int salary; private int teacher; private int managerid; public TbStudent() \{ super(); // TODO Auto-generated constructor stub \} public TbStudent(int id, String name, int salary, int teacher, int managerid) \{ super(); this.id = id; this.name = name; this.salary = salary; this.teacher = teacher; this.managerid = managerid; \} public TbStudent( String name, int salary, int teacher, int managerid) \{ super(); this.name = name; this.salary = salary; this.teacher = teacher; this.managerid = managerid; \} public int getId() \{ return id; \} public void setId(int id) \{ this.id = id; \} public String getName() \{ return name; \} public void setName(String name) \{ this.name = name; \} public int getSalary() \{ return salary; \} public void setSalary(int salary) \{ this.salary = salary; \} public int getTeacher() \{ return teacher; \} public void setTeacher(int teacher) \{ this.teacher = teacher; \} public int getManagerid() \{ return managerid; \} public void setManagerid(int managerid) \{ this.managerid = managerid; \} \}

创建工具类：OrderBy（用于sql语句中排序）

package com.entity; /\*\* \* 用在Mybatis的排序中 \* @author lvyingliang \* @date 2017年5月16日 下午1:53:35 \*/ public class OrderBy \{ private String orderByColumn; //排序列名 private String fields; //表名 public OrderBy(String orderByColumn, String fields) \{ super(); this.orderByColumn = orderByColumn; this.fields = fields; \} public String getFields() \{ return fields; \} public void setFields(String fields) \{ this.fields = fields; \} public String getOrderByColumn() \{ return orderByColumn; \} public void setOrderByColumn(String orderByColumn) \{ this.orderByColumn = orderByColumn; \} public OrderBy(String orderByColumn) \{ super(); this.orderByColumn = orderByColumn; \} public OrderBy() \{ super(); // TODO Auto-generated constructor stub \} \}

## 3、创建mapper.xml和mapper.java ##

创建各个实体类的mapper.xml（Sql映射文件）和mapper.java（接口文件）

说明：mapper.xml里面配置了执行各种功能的sql语句，并为每个sql语句起了一个id；

mapper.java接口文件相当于dao接口，里面只有方法；

> 二者的联系：

> 其实mapper.xml映射文件文件相当于mapper.java接口文件的实现类，不过需要满足以下两个条件：

> > 1.mapper.xml映射文件中有一个namespace的标签，该标签需要映射到对应的mapper.java接口文件下，相当于将mapper.xml与mapper.java关联；
> > 
> > 2.mapper.java接口文件中，每个方法的方法名，需要与mapper.xml中对应功能sql的Id一致，这样，Mybatis会自动根据sql对应的id去实现对应的方法；

创建TeacherMapper.java接口文件（因为Teacher类的属性名与列名一直，所以只简单举一个例子）

package com.mapper; import com.entity.TbTeacher; public interface TeacherMapper \{ public TbTeacher selectTeacherById(int id); \}

创建Teachermapper.xml映射文件

<?xml version="1.0" encoding="UTF-8" ?> <mapper namespace="com.mapper.TeacherMapper">    <select id="selectTeacherById" parametertype="int" resulttype="TbTeacher"> select \* from teacher where tid = $\{tid\} </select>  </mapper>

创建StudentMapper.java接口文件

package com.mapper; import java.util.HashMap; import java.util.List; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Param; import com.entity.OrderBy; import com.entity.TbStudent; public interface StudentMapper \{ public TbStudent selectStudentById(int id); public TbStudent selectStudentByName(String name); 多个参数 1 public TbStudent selectStudentByProperty\_list(int teacher, int managerid); //\#\{0\} \#\{1\} 多个参数 2 public List<tbstudent> selectStudentByProperty\_list\_in(List<integer> list); //map--<list in="" string=""> 多个参数 3 public List<tbstudent> selectStudentByProperty\_list\_in2(HashMap<string object=""> map); //map 多个参数 4 public List<tbstudent> selectStudentByProperty\_map(HashMap<string object=""> map); //注解 多个参数 5 public TbStudent selectStudent(@Param("teacher") int teacher,@Param("name") String name); //排序 //新建OrderBy类 public List<tbstudent> orderBy(OrderBy orderBy); //insert void insertStudent(TbStudent stu); //update void updateStudentByName(TbStudent stu); //delete01 void deleteStudentByName(TbStudent stu); \} </tbstudent></string></tbstudent></list></string></tbstudent></integer></tbstudent>

创建StudentMapper.xml映射文件（属性名与列名不一致，所以mapper.xml文件中需要先使用resultMap做属性名与列名的映射，并且每个sql操作的返回值不能使用resultType，需要使用resultMap）

<?xml version="1.0" encoding="UTF-8"?> <mapper namespace="com.mapper.StudentMapper">   <resultmap type="TbStudent" id="TbStudent">  <id property="id" column="tid">  <result property="name" column="sname"> <result property="salary" column="ssalary"> <result property="teacher" column="teacher"> <result property="managerid" column="managerid"> </result></result></result></result></id></resultmap>  <select id="selectStudentById" parametertype="int" resultmap="TbStudent"> select \* from student where sid = \#\{id\} </select> <select id="selectStudentByName" parametertype="string" resultmap="TbStudent"> select \* from student where sname = \#\{name\} </select> <select id="selectStudentByProperty\_list" resultmap="TbStudent"> select \* from student order by $\{0\} </select> <select id="selectStudentByProperty\_list\_in" resultmap="TbStudent"> select \* from student where sid in <foreach collection="list" item="item" index="index" open="(" close=")" separator=","> \#\{item\} </foreach> </select> <select id="selectStudentByProperty\_list\_in2" parametertype="HashMap" resultmap="TbStudent"> select \* from student where teacher = \#\{teacher\} and sid in <foreach collection="testArray" item="item" index="index" open="(" close=")" separator=","> \#\{item\} </foreach> </select> <select id="selectStudentByProperty\_map" resultmap="TbStudent"> select \* from student <if test="teacher != null and managerid != null"> where teacher = $\{teacher\} and managerid = $\{managerid\} </if> </select>  <select id="selectStudent" resultmap="TbStudent"> select \* from student where teacher = \#\{teacher\} and sname = \#\{name\} </select>  <select id="orderBy" resultmap="TbStudent"> select \* from $\{fields\} order by $\{orderByColumn\} </select> <insert id="insertStudent" parametertype="TbStudent" usegeneratedkeys="true" keyproperty="id"> insert into student (sname,ssalary,teacher,managerid) values (\#\{name\},\#\{salary\},\#\{teacher\},\#\{managerid\}) </insert> <update id="updateStudentByName" parametertype="TbStudent"> update student set ssalary = \#\{salary\} where sname = \#\{name\} </update>  <delete id="deleteStudentByName" parametertype="TbStudent"> delete from student where sname = \#\{name\} </delete> </mapper>

强调一下：只有在配置mapper.xml映射文件时，采用的是“/”来表示切换到子文件夹中

## 4、创建Test类 ##

package com.test; import java.io.IOException; import java.io.Reader; 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 com.entity.TbStudent; import com.mapper.StudentMapper; public class Test \{ private static SqlSessionFactory ssf; public static void init()\{ String config = "sqlMapConfig.xml"; Reader read = null; try \{ read = Resources.getResourceAsReader(config); \} catch (IOException e) \{ // TODO Auto-generated catch block e.printStackTrace(); \} ssf = new SqlSessionFactoryBuilder().build(read); \} public static void main(String\[\] args) \{ init(); SqlSession session = ssf.openSession(); //读取mapper.java接口 StudentMapper studentMapper = session.getMapper(StudentMapper.class); //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// //TbStudent student = studentMapper.selectStudentById(1); //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// //TbStudent student = studentMapper.selectStudentByName("aa"); //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// // 多个参数 1 //TbStudent student = studentMapper.selectStudentByProperty\_list(2, 1); // System.out.println("name:"+student.getName()); //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// // \#\{0\} \#\{1\} 多个参数 2 /\*//创建List集合 List<integer> list = new ArrayList<integer>(); list.add(1); list.add(2); List<tbstudent> studentList = studentMapper.selectStudentByProperty\_list\_in(list); System.out.println("size:"+studentList.size()); for(TbStudent li:studentList)\{ System.out.println("name:"+li.getName()); \}\*/ //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// // map--(list in,String) 多个参数 3 /\* //创建容器 int\[\] ids = new int\[\]\{1,2\}; //创建Map HashMap<string object=""> map = new HashMap<string object="">(); //放入数组容器 map.put("testArray", ids); //放入其他参数 map.put("teacher",2); List<tbstudent> studentList = studentMapper.selectStudentByProperty\_list\_in2(map); System.out.println("size:"+studentList.size()); for(TbStudent li:studentList)\{ System.out.println("name:"+li.getName()); \}\*/ //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// // map 多个参数 4 /\* HashMap<string object=""> map = new HashMap<string object="">(); List<tbstudent> studentList = studentMapper.selectStudentByProperty\_map(map); System.out.println("size:"+studentList.size()); for(TbStudent li:studentList)\{ System.out.println("name:"+li.getName()); \}\*/ //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// //注解 多个参数 5 /\* TbStudent student = studentMapper.selectStudent(1,"bb"); System.out.println("name:"+student.getName());\*/ //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// //排序 使用工具类排序 /\*OrderBy orderBy = new OrderBy("managerid Desc,ssalary Desc","student"); List<tbstudent> studentList = studentMapper.orderBy(orderBy); System.out.println("size:"+studentList.size()); for(TbStudent li:studentList)\{ System.out.println("name:"+li.getName()); \}\*/ //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// /\* //新增 TbStudent student = new TbStudent("newTest\_02",650,3,4); studentMapper.insertStudent(student); session.commit(); session.close(); //是否能获取到新增数据的id System.out.println("id:"+student.getId());\*/ //\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*// //更新 /\* TbStudent student = new TbStudent(); student.setName("newTest\_02"); student.setTeacher(1000); studentMapper.updateStudentByName(student); session.commit(); session.close();\*/ /\*//删除01 TbStudent student = new TbStudent(); student.setName("test"); studentMapper.deleteStudentByName(student); session.commit(); //删除02 studentMapper.deleteStudentByStringName("newTest\_02"); session.commit(); session.close();\*/ //注解新增 TbStudent student = new TbStudent("newTest\_03",650,3,4); int result = studentMapper.add(student); System.out.println("result:"+result); session.commit(); session.close(); \} public static SqlSessionFactory getSsf() \{ return ssf; \} public static void setSsf(SqlSessionFactory ssf) \{ Test.ssf = ssf; \} \} </tbstudent></tbstudent></string></string></tbstudent></string></string></tbstudent></integer></integer>

# 详解mapper.xml映射文件中的foreach标签： #

1、item表示集合中每一个元素进行迭代时的别名，

2、index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，

3、open表示该语句以什么开始，

4、separator表示在每次进行迭代之间以什么符号作为分隔符，

5、close表示以什么结束，

6、collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的：

> a、如果传入的是单参数且参数类型是一个List的时候，collection属性值为list .
> 
> b、如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array .

当需要传入一个List，还需要传入一个其他类型的对象时，需要用map,如测试类的 多个参数 3

不难发现，在list\_in2对应的sql语句中的foreach循环中，collection对应的“testArray”是封装在map中集合对应的key值，而\#｛teacher｝中的teacher也是map中的key值。

所以可以得到结论：在传入单个list时，Mybatis是将list集合和array数组封装到了map集合中，并设置了对应的key值

# mapper.xml文件中，\#和$的区别 #

区别：

1、使用\#入参，myBatis会生成PrepareStatement(预编译)并且可以安全地设置参数（=?）的值。因为sql语句已经预编译好了，传入参数的时候，不会重新生产sql语句。安全性高。  
2、用$使用的是非预编译的形式,会有sql注入的问题：  
例如，select \* from emp where ename = '用户名'，如果使用$入参，用户名被传入例如‘smith or 1 = 1’，那无论ename是否匹配都能查到结果。  
3、在特定场景下，例如如果在动态排序时，这时候就可以使用$.

使用$的特定场景:

> 1.**动态获取表名;**
> 
> 2.**动态排序——order by**

详见Test类中的排序例；

# 通过注解形式实现简单的增删改 #

## 1、去除mapper.xml映射文件 ##

## 2、sqlMapperConfig配置文件有改动 ##

因为不需要配置mapper.xml映射文件，所以SqlMapConfig中不用在<mapper>标签中映射各种mapper.xml文件;

但又因为mapper.xml文件的不存在，没有办法像之前那样通过namespace来映射mapper.java文件，所以这个需要在SqlMapConfig中加载mapper.java接口文件：

![20170518163034783][]

## 3、mapper.java文件有改动 ##

![20170518163720715][]

Test时直接调用add方法即可

[20170518151404143]: /images/20210522/204424aef982456d9e4ebf99f45c971b.png
[20170518163034783]: /images/20210522/d11e0c3761c942aeae8fcb145ca7fe59.png
[20170518163720715]: /images/20210522/d4f64551a11a46049740d41cc5b12932.png