Mybatis 学习
1. 什么是 MyBatis?
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
2. Mybatis的优点是什么?
- MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架。
- MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。
- MyBatis可以使用简单的XML或注解用于配置和原始映射,将接口和Java的POJO(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录。
- mybatis还给我们提供了很多第三方插件(分页插件 / 逆向工程)。
3. 搭建MyBatis环境使用的jar包
- mybatis-3.5.2.jar
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
- mysql-connector-java-5.1.16.jar 数据库驱动
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
- log4j-1.2.17.jar 日志包
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
- junit-4.12.jar 单元测试包
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
4. mybatis 需要的配置文件
4.1. mybatis-config.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>
<!--引入配置文件-->
<properties resource="db.properties"></properties>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--给数据库中的类设置别名-->
<typeAliases>
<package name="com.hspedu.pojo"></package>
</typeAliases>
<!--配置数据库的信息 driver驱动 url地址 username用户名 password密码-->
<!--type:设置mybatis是否采用连接池技术,POOLED:表示mybatis采用连接池技术-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--将Mapper的xml配置文件单独放置到resources中,和Mapper类分开配置-->
<!-- <mapper resource="com/hspedu/dao/TeacherDao.xml"></mapper>-->
<!--使用类来注册-->
<!--<mapper class="com.hspedu.dao.StudentDao"></mapper>-->
<!--<mapper class="com.hspedu.dao.TeacherDao"></mapper>-->
<!--Mapper类所在的package包路径配置Mapper-->
<package name="com.hspedu.dao"></package>
</mappers>
</configuration>
解释:typealias是指类型别名,将一个已存在的类型别名一个其他名字
- name=“com.hspedu.pojo” 表示以类名的小写作为别名
- type=“com.hspedu.pojo.Student” alias=“Student” 直接指定类型别名
<typeAliases>
<typeAlias type="com.hspedu.pojo.Student" alias="Student"></typeAlias>
<package name="com.hspedu.pojo"></package>
</typeAliases>
properties 导入数据库信息文件
<properties resource="db.properties"></properties>
- db.properties 数据库配置信息文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&;useUnicode=true&;characterEncoding=utf8
username=root
password=123456
配置log4j为日志的实现
<setting name="logImpl" value="LOG4J"/>
4.2 pom.xml 配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>Mybatis</artifactId>
<groupId>com.hspedu</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>mybatis03</artifactId>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
</dependencies>
<!--在build中配置resources,来防止资源导出失败的问题-->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resourcesxini>
</build>
</project>
5. mybatis的应用
5.1 单表查询
程序框架图如下
使用Mybatis 对数据库进行增删查改操作
- UserDao.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="com.hspedu.dao.UserDao">
<select id="getUserList" resultType="com.hspedu.pojo.User">
select * from mybatis.user
</select>
<!--使用结果集映射的方式-->
<resultMap id="UserMap" type="User">
<result column="id" property="id"></result>
<result column="name" property="name"></result>
<result column="pwd" property="pwd"></result>
</resultMap>
<select id="getUserById" resultMap="UserMap">
select * from mybatis.user where id=#{id}
</select>
<!--插入数据-->
<insert id="addUser" parameterType="com.hspedu.pojo.User">
insert into mybatis.user values(#{id}, #{name}, MD5(#{pwd}))
</insert>
<!--增加一个用户-->
<insert id="addUser2" parameterType="map">
insert into mybatis.user values(#{userId}, #{userName}, MD5(#{password}))
</insert>
<!--更新数据-->
<update id="updateUser" parameterType="com.hspedu.pojo.User">
update mybatis.user set name = #{name} , pwd=MD5(#{pwd}) where id = #{id};
</update>
<!--删除用户-->
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id = #{id}
</delete>
<!--模糊查询-->
<select id="getUserLike" resultType="com.hspedu.pojo.User">
select * from mybatis.user where name like #{value}
</select>
<!--分页测试-->
<select id="getUserByLimit" resultType="User" parameterType="map">
select * from mybatis.user limit #{startIndex}, #{numIndex}
</select>
</mapper>
- UseDao.java 文件
public interface UserDao {
List<User> getUserList();
User getUserById(int id);
int addUser(User user);
int updateUser(User user);
int deleteUser(int id);
List<User> getUserByLimit(Map<String, Integer> map);
}
- MybatisUtils.java 工具类
package com.hspedu.utils;
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 java.io.IOException;
import java.io.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
//第一步获取sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
}
- 对数据库进行操作
package com.hspedu.dao;
import com.hspedu.pojo.User;
import com.hspedu.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
public class UserDaoTest {
@Test
public void test() {
//第一步 获取Session对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
//分页测试
@Test
public void getUserByLimit() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
HashMap<String, Integer> map = new HashMap<String, Integer>();
map.put("startIndex", 0);
map.put("numIndex", 2);
List<User> list = userDao.getUserByLimit(map);
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}
//通过id 获取用户
@Test
public void testGetUserById () {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.getUserById(1);
System.out.println(user);
sqlSession.close();
}
//增加用户
@Test
public void testAddUser () {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
int des = userDao.addUser(new User(4, "曹操", "123456"));
if (des > 0) {
System.out.println("插入成功");
}
//提交事务
sqlSession.commit();
sqlSession.close();
}
//更新数据
@Test
public void testUpdateUser () {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
int des = userDao.updateUser(new User(1, "赵云", "12345"));
if(des > 0) {
System.out.println("更新成功");
}
sqlSession.commit();
sqlSession.close();
}
//删除用户
@Test
public void deleteUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
int des = userDao.deleteUser(1);
if(des > 0) {
System.out.println("删除成功");
}
sqlSession.commit();
sqlSession.close();
}
}
提示:更新(update), 删除(delete), 增加(insert) 都需要设置提交事务 sqlSession.commit()。
实验结果:
5.2 使用注解进行简单查询
package com.hspedu.dao;
import com.hspedu.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface UserDao {
@Select("select * from user")
List<User> getUsers();
//方法存在多个参数,所有参数前面必须加上@Param("xxx")注解
@Select("select * from user where id = #{id}")
User getUserById(@Param("id") int id, @Param("name")String name);
@Insert("insert into user values(#{id}, #{name}, md5(#{pwd}))")
int addUser(User user);
}
测试类
package com.hspedu.dao;
import com.hspedu.pojo.User;
import com.hspedu.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class UserDaoTest {
@Test
public void test() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.getUserById(1, "牛逼");
System.out.println(user);
sqlSession.close();
}
//增加用户
@Test
public void testAddUser() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
int index = userDao.addUser(new User(6, "马超", "123456"));
if (index > 0) {
System.out.println("插入成功");
}
sqlSession.close();
}
}
实验结果:
5.3 多对一查询
以上的案例只是对数据表进行简单查询,当涉及多表查询的时候,便不能查询到结果了,所以需要使用多表查询的方式。多对一和老师与学生的关系差不多,一个老师对应多个学生。因此可以使用学生与老师的关系测试多对一查询。
项目目录如下
这里直接对关键步骤进行介绍
StudentDao.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hspedu.dao.StudentDao">
<select id="getStudent" resultMap="StudentTeacher">
select * from mybatis.student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"></result>
<result property="name" column="name"></result>
<!--复杂的属性需要单独处理, 对象:association 集合-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher where id = #{id}
</select>
<!--按照结果集嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid, s.name sname, t.name tname
from student s, teacher t
where s.tid = t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
StudentDao.java
package com.hspedu.dao;
import com.hspedu.pojo.Student;
import java.util.List;
public interface StudentDao {
List<Student> getStudent();
List<Student> getStudent2();
}
Student.java
package com.hspedu.pojo;
import lombok.Data;
import lombok.ToString;
@Data
@ToString
public class Student {
private Integer id;
private String name;
private Teacher teacher;
}
Teacher.java
package com.hspedu.pojo;
import lombok.Data;
@Data
public class Teacher {
private Integer id;
private String name;
}
对数据进行测试类
package com.hspedu.dao;
import com.hspedu.pojo.Student;
import com.hspedu.pojo.Teacher;
import com.hspedu.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class Test {
//根据老师id获取老师信息
@Test
public void test() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
Teacher teacher = teacherDao.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
//获取所有学生以及学生对应的老师信息
@Test
public void testGetStudent() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
List<Student> studentList = studentDao.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
@Test
public void testGetStudent2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
List<Student> studentList = studentDao.getStudent2();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
}
实验结果
使用 两种查询方式的结果如下
方式一:
方式二:
5.4 一对多查询
多对一 与 一对多差不多,只是mapper文件有些许不同,下面显示TeacherDao.xml的文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hspedu.dao.TeacherDao">
<!--第一种查询方式-->
<select id="getTeacher1" resultMap="TeacherStudent">
SELECT s.id sid, s.name sname, t.name tname, t.id tid
FROM student s, teacher t
WHERE s.tid = t.id AND t.id = #{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--connection 集合 collection javaType 指定属性的信息-->
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
<!--第二种查询方式-->
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from mybatis.teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"></collection>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from mybatis.student where tid = #{tid}
</select>
</mapper>
方法一的实验结果
方法二的实验结果
解释:
- association: 处理一对一、多对一
- collection: 处理一对多