<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.dao.StudentDao"><!--
select:表示查询操作
id:你要执行的sql语法的唯一标识,mybatis会使用这个id的值来找到要执行的sql语句
可以自定义,但是前要求使用接口中的方法名称
resultType:表示结果类型的,是sql语句执行后得到ResultSet,遍历这个ResultSet得到java对象的类型
值写的是类型的全限定名称
--><selectid="selectStudents"resultType="com.domain.Student">
select id,name,email,age from student order by id
</select><insertid="insertStudents">
insert into student values(#{id},#{name},#{email},#{age})
</insert></mapper>
publicclassTestMybatis{@TestpublicvoidselectTest(){StudentDao dao =newStudentDaoImpl();List<Student> students = dao.selectStudents();for(Student stu : students){System.out.println(stu);}}@TestpublicvoidinsertTest(){StudentDao dao =newStudentDaoImpl();Student student =newStudent(1017,"张三","zhangsan@qq.com",40);int nums = dao.insertStudents(student);System.out.println(nums);}}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2.8、主要类介绍
(1)、Resources:mybatis中的一个类,负责读取主配置文件,返回不同类型的IO流对象。
InputStream in = Resources.getResourceAsStream(“mybatis.xml”);
(2)、SqlSessionFactoryBuilder : 创建SqlSessionFactory对象。
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder()
SqlSessionFactory factory = builder.build(in);
<selectid="selectStudentById"parameterType="java.lang.Integer"resultType="com.domain.Student">
select id,name,email,age from student where id = #{studentId}
</select>
1
2
3
1
2
3
3.2.2、一个简单类型的参数
简单类型:mybatis把java的基本数据类型和String都叫简单数据类型。
在mapper文件获取简单类型的一个参数的值,使用#{任意字符}
例如StudentDao接口
public Student selectStudentById(int id)
mapper:
<selectid="selectStudentById"resultType="com.domain.Student">
select id,name,email,age from student where id = #{studentId}
</select>
1
2
3
1
2
3
3.2.3、多个参数,使用@Param命名参数
当Dao接口方法有多个参数,需要通过名称使用参数。在方法形参前面加入@Param(“自定义参数名”),mapper文件使用#{自定义参数名}。
接口方法:
List selectMultiParam(@Param(“myName”) String name,@Param(“myAge”) int age);
mapper:
<selectid="selectMultiParam"resultType="com.domain.Student">
select id,name,email,age from student where name = #{myName} or age = #{myAge}
</select>
1
2
3
1
2
3
3.2.4、多个参数-使用java对象
使用java对象传递参数,java的属性值就是sql需要的参数值。每一个属性就是一个参数。
语法格式:#{属性名,javaType=java中数据类型名称,jdbcType=数据库数据类型名称} 这是最为完整的方式。但是javaType、jdbcType的值mybatis通过反射机制可以获取,一般不需要设置。常用格式:#{属性名}
接口方法:
List selectMultiStudent(Student student);
mapper:
<selectid="selectMultiStudent"resultType="com.domain.Student">
select id,name,email,age from student where
name = #{name} or age = #{age}
</select>
1
2
3
4
1
2
3
4
或者为:
<selectid="selectMultiStudent"resultType="com.domain.Student">
select id,name,email,age from student where name = #{name,javaType=java.lang.String,jdbcType=VARCHAR}
or age = #{age,javaType=java.lang.Integer,jdbcType=INTEGER}
</select>
1
2
3
4
1
2
3
4
3.2.5、多个参数-按位置
参数位置从0开始,引用参数语法==#{arg位置}==,第一个参数是#{arg0},第二个参数是#{arg1}
注意:mybatis-3.3版本和之前的版使用#{0},#{1}方式,从mybatis-3.4开始使用#{arg0}方式。
接口方法:
List selectMultiPosition(String name,int age);
mapper:
<!--多个参数使用位置--><selectid="selectMultiPosition"resultType="com.domain.Student">
select id,name,email,age from student where
name = #{arg0} or age = #{arg1}
</select>
接口方法:
List selectMultiMap(Map<String,Object> map);
mapper文件:
<!--多个参数使用Map,使用语法:#{map的key}--><selectid="selectMultiMap"resultType="com.domain.Student">
select id,name,email,age from student where
name = #{name} or age = #{age}
</select>
1
2
3
4
5
1
2
3
4
5
3.2.7、# 和 $
#:占位符,告诉mybatis使用实际的参数值代替。并使用PrepareStatement对象执行sql语句,#{…}代替sql语句的 “ ?”。这样做更安全、通常也是首选做法。
例如:
select id,name, email,age from student where id=#{studentId}
#的结果:select id,name, email,age from student where id=?
$:字符串替换,告诉mybatis使用 $ 包含的“字符串”替换所在位置。使用Statement把sql语句和${}的内容连接起来,主要用在替换表名、列名,不同列排序等操作。
例如:
select id,name, email,age from student where id=${studentId}
$的结果:select id,name, email,age from student where id=1001
相当于:String sql=“select id,name, email,age from student where id=” + “1001”;
#和$的区别:
1、#使用?在sql语句中做占位的,使用PrepareStatement执行sql,效率高
2、#能够避免sql注入,更安全
3、$不使用占位符,是字符串连接的方法,使用Statement对象执行sql,效率低
4、$有sql注入的风险,缺乏安全性
5、$可以替换表名或者列名
<!--返回Map (用的少)
1、列名是Map的key,列值是Map的value
2、最多只能返回一行记录,多一行是错误的
--><selectid="selectMapById"resultType="java.util.HashMap">
select id,name,email,age from student where id = #{id}
</select>
1
2
3
4
5
6
7
1
2
3
4
5
6
7
测试方法:
@TestpublicvoidtestSelectMapById(){SqlSession sqlSession =MybatisUtil.getSqlSession();StudentDao dao = sqlSession.getMapper(StudentDao.class);Map<Object,Object> map = dao.selectMapById(1017);System.out.println(map);}
<!--
resultType的默认原则是: 同名的列值赋给同名的属性,使用列别名(java属性名)
--><selectid="selectMyStudentDiffProperty"resultType="com.domain.MyStudent">
select id as stuId,name as stuName,email as stuEmali,age as stuAge from student
</select>
<selectid="selectLikeOne"resultType="com.domain.Student">
select id,name,email,age from student where name like #{name}
</select>
1
2
3
1
2
3
测试程序:
@TestpublicvoidtestSelectLikeOne(){SqlSession sqlSession =MybatisUtil.getSqlSession();StudentDao dao = sqlSession.getMapper(StudentDao.class);String name ="%杨%";List<Student> students = dao.selectLikeOne(name);for(Student stu : students){System.out.println("student = "+ stu);}
sqlSession.close();}
1
2
3
4
5
6
7
8
9
10
11
1
2
3
4
5
6
7
8
9
10
11
使用like进行模糊查询的第二种方式:在mapper文件中拼接like的内容。
<selectid="selectLikeTwo"resultType="com.domain.Student">
select id,name,email,age from student where name like "%" #{name} "%"
</select>
1
2
3
1
2
3
测试程序:
@TestpublicvoidtestSelectLikeTwo(){SqlSession sqlSession =MybatisUtil.getSqlSession();StudentDao dao = sqlSession.getMapper(StudentDao.class);String name ="杨";List<Student> students = dao.selectLikeTwo(name);for(Student stu : students){System.out.println("student = "+ stu);}
sqlSession.close();}
<selectid="selectStudentIf"resultType="com.domain.Student">
select id,name,email,age from student where true
<iftest="name != null and name != ''">
name = #{name}
</if><iftest="age > 0">
or age > #{age}
</if></select>
<selectid="selectStudentWhere"resultType="com.domain.Student">
select id,name,email,age from student
<where><iftest="name != null and name != ''">
name = #{name}
</if><iftest="age > 0">
or age = #{age}
</if></where></select>
<selectid="selectStudentForOne"resultType="com.domain.Student">
select id,name,email,age from student where id in
<foreachcollection="list"item="myid"open="("close=")"separator=",">
#{myid}
</foreach></select>
1
2
3
4
5
6
1
2
3
4
5
6
测试程序:
@TestpublicvoidselectStudentForOne(){SqlSession sqlSession =MybatisUtil.getSqlSession();StudentDao dao = sqlSession.getMapper(StudentDao.class);List<Integer> list =newArrayList<>();
list.add(1032);
list.add(1017);
list.add(1001);List<Student> students = dao.selectStudentForOne(list);for(Student stu : students){System.out.println("student= "+ stu);}
sqlSession.close();}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1
2
3
4
5
6
7
8
9
10
11
12
13
14
遍历对象类型:
mapper文件:
<selectid="selectStudentForTwo"resultType="com.domain.Student">
select id,name,email,age from student where id in
<foreachcollection="list"item="student"open="("close=")"separator=",">
#{student.id}
</foreach></select>
1
2
3
4
5
6
1
2
3
4
5
6
测试程序:
@TestpublicvoidselectStudentForTwo(){SqlSession sqlSession =MybatisUtil.getSqlSession();StudentDao dao = sqlSession.getMapper(StudentDao.class);List<Student> stuList =newArrayList<>();Student s1 =newStudent();
s1.setId(1001);
stuList.add(s1);
s1 =newStudent();
s1.setId(1017);
stuList.add(s1);List<Student> students = dao.selectStudentForTwo(stuList);for(Student stu : students){System.out.println("student= "+ stu);}
sqlSession.close();}
<sqlid="studentSql">
select id,name,email,age from student
</sql><selectid="selectStudentForTwo"resultType="com.domain.Student"><includerefid="studentSql"/>
where id in
<foreachcollection="list"item="student"open="("close=")"separator=",">
#{student.id}
</foreach></select>
@TestpublicvoidtestSelectAllByPageHelper(){SqlSession sqlSession =MybatisUtil.getSqlSession();StudentDao dao = sqlSession.getMapper(StudentDao.class);PageHelper.startPage(2,3);List<Student> students = dao.selectAll();for(Student stu : students){System.out.println("student= "+ stu);}
sqlSession.close();}