MyBatis

第一个MyBatis案例

项目开始之前需要写一个表：

/*创建数据库使用默认编码*/
CREATE DATABASE mybatis DEFAULT CHARACTER SET utf8;
USE mybatis;
/*创建表并且使用备注
约束：保证录入数据的完整性
*/
CREATE TABLE flower(
id INT(10) PRIMARY KEY auto_increment COMMENT '花序号',
name VARCHAR(30) NOT NULL COMMENT '花名',
price FLOAT NOT NULL COMMENT '价格',
production VARCHAR(30) NOT NULL COMMENT '原产地');

/*查询语句*/
DESC flower;

/*插入数据*/
INSERT INTO flower VALUES(DEFAULT,'牵牛花',2.5,'非洲');
INSERT INTO flower VALUES(DEFAULT,'喇叭花',5.5,'中国');
INSERT INTO flower VALUES(DEFAULT,'樱花',10.5,'日本');
INSERT INTO flower VALUES(DEFAULT,'枫树花',4.5,'加拿大');

/*删除表*/
/*删除数据库*/
DROP TABLE flower;
DROP DATABASE mybatis;

创建一个动态web项目

注意刚开始在WebConten下面没有index.jsp，并且在WEB-INF下面也没有web.xml文件，我们要手动补足：
web.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
    http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
</web-app>

导入两个jar包

1.mysql-connector-java-5.1.30.jar
2.mybatis-3.2.7.jar

在src下创建mybatis.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <environments default="dft">
    <environment id="dft">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="cn/jxb/mapper/insert-mapper.xml"/>
  </mappers>
</configuration>

然后创建两个包：

在cn.jxb.mapper包下创建insert-mapper.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="cn.jxb.mapper.InsertMapper">
  <insert id="insertFlower">
  insert into flower values(default,"普罗旺斯",99.5,"法国")
  </insert>
</mapper>

编写测试类

package cn.jxb.mapper;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class InsertMapper {
    public static void main(String[] args) throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = factory.openSession();
        int flag =  sqlSession.insert("cn.jxb.mapper.InsertMapper.insertFlower");
        sqlSession.commit();
        System.out.println(flag);
    }
}

带参数插入

首先给insert-mapper.xml新增一个插入方法

<insert id="insertOne" parameterType="map">
insert into flower values(default,#{name},#{price},#{production})
</insert>

再写一个带参数的插入方法

InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = factory.openSession();
Map<String, Object> map = new HashMap<String, Object>();
map.put("name", "郁金香");
map.put("price", 75.4);
map.put("production", "加拿大");
int flag =  sqlSession.insert("cn.jxb.mapper.InsertMapper.insertOne",map);
sqlSession.commit();
System.out.println(flag);

修改数据

首先添加更新操作

<update id="updateFlower" parameterType="map">
update flower set name=#{name} where id=#{id}
</update>

然后传入参数

InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = factory.openSession();
Map<String, Object> map = new HashMap<String, Object>();
map.put("name", "思乐库");
map.put("id", 8);
int flag =  sqlSession.insert("cn.jxb.mapper.InsertMapper.updateFlower",map);
sqlSession.commit();
System.out.println(flag);

删除操作

首先配置文件

<delete id="deleteFlower" parameterType="map">
delete from flower where id=#{id}
</delete>

传入参数

InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = factory.openSession();
Map<String, Object> map = new HashMap<String, Object>();
map.put("id", 8);
int flag =  sqlSession.insert("cn.jxb.mapper.InsertMapper.deleteFlower",map);
sqlSession.commit();
System.out.println(flag);

查询一行数据

配置文件

<select id="selectFlower" parameterType="map" resultType="map">
select * from flower where id=#{id}
</select>

查询操作

InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = factory.openSession();
Map<String, Object> map = new HashMap<String, Object>();
map.put("id", 3);
HashMap<String, Object> result =  sqlSession.selectOne("cn.jxb.mapper.InsertMapper.selectFlower",map);
sqlSession.commit();
System.out.println(result);

不仅可以使用map当作参数或者结果，而且也可以自己写一个JavaBean实体类接收

多条查询

配置文件

<select id="selectFlower" resultType="map">
select * from flower
</select>

查询操作

InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = factory.openSession();

List<HashMap<String, Object>> result =  sqlSession.selectList("cn.jxb.mapper.InsertMapper.selectFlower");
sqlSession.commit();
System.out.println(result);

如果使用JavaBean类型

<select id="selectFlower" resultType="cn.jxb.pojo.Flower">
select * from flower
</select>

List<Flower> result =  sqlSession.selectList("cn.jxb.mapper.InsertMapper.selectFlower");
sqlSession.commit();

ResultMap单表映射写法

假如有一张教师表，只包含两个列：id和name

1.首先我们在mapper里写一个查询全部数据的select语句

<mapper namespace="a.b">
<select id="selAll" resultMap="">
    select * from teacher
</select>
</mapper>

2.resultMap其实需要对应一个resultMap的标签，例如：

<mapper namespace="a.b">

<resultMap type="teacher" id="ttt">

</resultMap>

<select id="selAll" resultMap="ttt">
    select * from teacher
</select>


</mapper>

3.接下来需要映射关系：主键(或者联合主键)必须是id标签，其他的列都是result(多个列都是result)，其中column是表中的属性，property是指实体类中的名称

<resultMap type="cn.jxb.pojo.Teacher" id="ttt">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
</resultMap>

4.完整代码如下：

<?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="a.b">

<resultMap type="cn.jxb.pojo.Teacher" id="ttt">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
</resultMap>

<select id="selAll" resultMap="ttt">
    select * from teacher
</select>


</mapper>

5.最后不要忘记去mybatis-config.xml当中映射该文：

 <mappers>
   <mapper resource="cn/jxb/mapper/teacher.xml"/>
 </mappers>

6.主要查询代码：

InputStream iStream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(iStream);
SqlSession session = factory.openSession();
System.out.println("当前获取到session啦");

List<Teacher> teachers =  session.selectList("a.b.selAll");

ResultMap加载对象

先看一下两张表的pojo：

Student：
private int id;
private String name;
private int age;
private int tid;
private Teacher teacher;

Teacher：
private int id;
private String name;

当我们需要在Student的查询中携带一位老师信息的时候，下面的查询是无法满足条件的：

<mapper namespace="selectStudent">

<select id="selAll" resultType="cn.jxb.pojo.Student">
select * from stu
</select>

</mapper>

然后我们可以这样关联一个resultMap，其返回值类型依然是Student

<resultMap type="cn.jxb.pojo.Student" id="sss">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <result column="tid" property="tid"/>
    <!-- 如果关联一个对象就使用这个 -->
    <association property=""></association>
    <!-- 如果关联一个集合对象就使用这个 -->
    <collection property=""></collection>
</resultMap>


 <select id="selAll" resultMap="sss">
 select * from stu
 </select>
 

association需要查询一个老师的对象所以：先去给teacherMapper配置一个通过tid查询到老师对象的select标签

<mapper namespace="selectTeacher">
    <select id="selId" resultType="cn.jxb.pojo.Teacher" parameterType="int">
        SELECT * FROM   teacher WHERE id=#{0}
    </select>
</mapper>

然后继续完成association里面的内容，property是在Student中的属性名称,select指向TeacherMapper当中查询Teacher对象的报名加方法名称，最后一个参数是上面已经配置好的参数

<association property="teacher" select="selectTeacher.selId" column="tid"></association>

看一下完整的代码

<mapper namespace="selectStudent">

<resultMap type="cn.jxb.pojo.Student" id="sss">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <result column="tid" property="tid"/>
    <association property="teacher" select="selectTeacher.selId" column="tid"></association>
</resultMap>

 <select id="selAll" resultMap="sss">
 select * from student
 </select>
 
</mapper>

可以将上面的代码简化一下：

<mapper namespace="selectStudent">

<resultMap type="cn.jxb.pojo.Student" id="sss">
    <id column="id" property="id"/>
    <result column="tid" property="tid"/>
    <association property="teacher" select="selectTeacher.selId" column="tid"></association>
</resultMap>

 <select id="selAll" resultMap="sss">
 select * from student
 </select>
 
</mapper>

ResultMap加载集合

和上一个项目一样，只不过这一次需要加载集合：每一个老师都教一堆学生，那么在Teacher中必定一个List< Student>，所以需要查询加载集合

Teacher
private int id;
private String name;
private List<Student>list;

cn/jxb/mapper/StudentMapper.xml

<mapper namespace="selectStudent">

 <select id="selId" resultType="cn.jxb.pojo.Student" parameterType="int">
    select * from student where id=#{0}
 </select>
 
</mapper>

/cn/jxb/mapper/TeacherMapper.xml

<mapper namespace="selectTeacher">

    <resultMap type="cn.jxb.pojo.Teacher" id="tttt">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="list" select="selectStudent.selId" column="id"></collection>
    </resultMap>
    <select id="selAll" resultMap="tttt">
    SELECT * FROM teacher
    </select>
</mapper>

联合查询方式

<mapper namespace="selectTeacher">

    <resultMap type="cn.jxb.pojo.Teacher" id="tttt">
        <id column="tid" property="id"/>
        <result column="tname" property="name"/>
        <collection property="list" ofType="cn.jxb.pojo.Student">
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="age" property="age"/>
        <result column="tid" property="tid"/>
        </collection>
    </resultMap>
    <select id="selAll" resultMap="tttt">
    SELECT t.id tid,t.name tname,s.id sid,s.name sname,age,tid FROM teacher t LEFT JOIN student s ON t.id=s.tid;
    </select>

</mapper>

AutoMapping

使用AutoMapping加别名的方式查询集合时，只能使用联合查询的方式

首先组织一下sql语句

SELECT 
t.id `teacher.id`,
t.name `teacher.name`,
s.id id,
s.name name,
age,
tid
FROM student s LEFT JOIN teacher t ON t.id=s.tid;

查询结果如下：

teacher.id	teacher.name	id	name	age	tid
1	教师1	1	学生1	19	1
2	教师2	2	学生2	18	2
1	教师1	3	学生3	16	1
2	教师2	4	学生4	19	2
1	教师1	5	学生5	14	1
2	教师2	6	学生6	20	2
1	教师1	7	学生7	21	1
2	教师2	8	学生8	19	2
1	教师1	9	学生9	19	1
2	教师2	10	学生10	29	2
1	教师1	11	学生11	19	1
2	教师2	12	学生12	39	2
1	教师1	13	学生13	22	1
1	教师1	14	学生14	19	1

然后放入mapping中：

<select id="selAll" resultType="cn.jxb.pojo.Student">
SELECT 
t.id `teacher.id`,
t.name `teacher.name`,
s.id id,
s.name name,
age,
tid
FROM student s LEFT JOIN teacher t ON t.id=s.tid;
</select>

代码实现：

InputStream inputStream =  Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession= factory.openSession();
List<Student> list =  sqlSession.selectList("selectStudent.selAll");

MyBatis注解

• 可以简化mybatis-config.xml
• 注解可以和xml配置共同存在
• 使用mybatis.xml中< mappers>时可以使用< package>或者< mapper>中的calss属性

首先编写一个TeacherMapper的接口，因为注解写在方法上面所以直到方法名称、参数、返回值，我们只需要提供一个sql语句即可

package cn.jxb.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Select;
import cn.jxb.pojo.Teacher;
public interface TeacherMapper {
    @Select("select * from teacher")
    List<Teacher> selAll();
}

首先在mybais.xml当中配置

<mapper class="cn.jxb.mapper.TeacherMapper"/>
</mappers>

接下来测试一次：

package cn.jxb.servlet;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 cn.jxb.mapper.TeacherMapper;
import cn.jxb.pojo.Teacher;

public class Demo {
    public static void main(String[] args) throws IOException {
        InputStream inputStream =  Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession= factory.openSession();
        TeacherMapper teacherMapper = sqlSession.getMapper(cn.jxb.mapper.TeacherMapper.class);
        List<Teacher> list =  teacherMapper.selAll();
        for (Teacher teacher : list) {
            System.out.println(teacher);
        }
    }
}

特殊查询集合

使用注解实现< resultMap>功能,在StudentMapper 接口添加查询

@Select("select * from student where tid=#{0}")
List<Student> selByTid(int tid);

在 TeacherMapper 接口添加
@Results() 相当于< resultMap>
@Result() 相当于< id/>或< result/>
@Result(id=true) 相当于< id/>
@Many() 相当于< collection/>
@One() 相当于< association/>

@Results(value={
    @Result(id=true,property="id",column="id"), 
    @Result(property="name",column="name"),
    @Result(property="list",column="id",many=@Many(select="com.bjsxt.mapper.StudentMapper.selByTid"))
})
@Select("select * from teacher") 
List<Teacher> selTeacher();