mybatis介绍
半自动的ORM框架
ORM:对象关系映射,将java中的一个对象与数据表中的一行记录一一对应。
支持自定义sql,存储过程。
对原有jdbc进行封装,几乎消除所有jdbc代码,让开发者只需关注sql本身。
支持xml和注解配置方式自定义完成ORM操作。
mybatis框架部署
pom文件导入:
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.11</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
创建mybatis-config配置文件:
<?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>
</configuration>
在mybatis-config配文件中配置数据库信息:
<configuration>
<!-- 配置数据库链接信息,可配置多个,default用来指定默认用哪个 -->
<environments default="mysql">
<environment id="mysql">
<!-- transactionManager:配置数据库管理方式 -->
<transactionManager type="JDBC"></transactionManager>
<!-- transactionManager:配置数据库连接信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://121.5.12.105:3306/mysql?characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="n123456"/>
</dataSource>
</environment>
</environments>
</configuration>
mybatis的简单使用
创建表:
CREATE TABLE tb_students(
sid INT PRIMARY KEY AUTO_INCREMENT,
stu_num CHAR(5) NOT NULL UNIQUE,
stu_name VARCHAR(20) NOT NULL,
stu_gender CHAR(2) NOT NULL,
stu_age INT NOT NULL
);
创建实体类:
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
private int stuId;
private String stuNum;
private String stuName;
private String stuGender;
private int stuAge;
}
创建Dao接口:
public interface StudentDao {
public int insertStudent(Student student);
}
创建dao接口的映射文件:
resource目录下,新建名为mappers的文件夹,在mappers中新建名为StudentMapper.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">
<!--namespace对应完整的Dao类路径-->
<mapper namespace="com.kingyal.dao.StudentDao">
<!-- id对应方法名;parameterTpye对应参数的类型,可省略不写 -->
<insert id="insertStudent" parameterType="com.kingyal.pojo.Student">
insert into tb_students(stu_num,stu_name,stu_gender,stu_age)
values(#{stuNum},#{stuName},#{stuGender},#{stuAge});
</insert>
</mapper>
将映射文件添加到mybatis-config配置文件中:
<mappers >
<mapper resource="mappers/StudentMapper.xml"></mapper>
</mappers>
测试:
public class StudentDaoTest {
@Test
public void insertStudent() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 获取Dao接口
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
System.out.println(studentDao);
// 插入操作
int i = studentDao.insertStudent(new Student(0, "0001", "zhangsan", "M", 18));
System.out.println(i);
sqlSession.commit();
}
}
mybatis的CRUD操作
在上述简单使用的基础上,继续补充crud操作,包含添加操作,删除操作,修改操作,查询操作所有,查询一条记录,多参数查询,查询记录数,主键回填等。其中,在多参数查询时,在mybsatis中进行条件查询,如果DAO中只有一个简单类型或者字符串类型的参数,在Mapper配置中可以直接用#{xxx}直接获取;如果有一个对象类型的参数,在Mapper配置中可以直接用#{key}直接获取对象的执行属性,key必须是参数对象的属性,名字不能改变。如果有多个参数,则需要在DAO中可以通过@Param指定或者使用hashmap传参,也可以在map的sql中用arg或者param实现参数的传递。
具体实现如下:
a. 创建接口
public int insertStudent(Student student);
public int insertStudentAndUseGeneratedKey(Student student);
public int deleteStudent(int stuNum);
public int updateStudent(Student student);
public List<Student> selectAllStudents();
public List<Student> selectAllStudentsByResultMap();
public Student selectStudentByNum(int sutNum);
public List<Student> selectAllStudentsByHashMap(HashMap<String,Integer> hashMap);
public List<Student> selectAllStudentsByArgs(int start, int pageSize);
public List<Student> selectAllStudentsByPage(@Param("start") int start,
@Param("pageSize") int page);
public int getCount();
b. 在StudentMapper.xml的接口文件中实现sql语句:
<mapper namespace="com.kingyal.dao.StudentDao">
<!-- id对应方法名;parameterTpye对应参数的类型,可省略不写 -->
<insert id="insertStudent" parameterType="com.kingyal.pojo.Student">
insert into tb_students(stu_num,stu_name,stu_gender,stu_age)
values(#{stuNum},#{stuName},#{stuGender},#{stuAge});
</insert>
<!-- useGeneratedKeys:添加操作是否需要回填生成的主键,keyProperty:指定回填的主键值赋值给实体对象的哪个属性-->
<insert id="insertStudentAndUseGeneratedKey" useGeneratedKeys="true" keyProperty="stuId">
insert into tb_students(stu_num,stu_name,stu_gender,stu_age)
values(#{stuNum},#{stuName},#{stuGender},#{stuAge});
</insert>
<delete id="deleteStudent">
delete from tb_students where stu_num=#{stuNum};
</delete>
<update id="updateStudent">
update tb_students set
stu_name=#{stuName},
stu_gender=#{stuGender},
stu_age=#{stuAge}
where stu_num=#{stuNum};
</update>
<!-- select 方式1 -->
<!--resultType:返回的对象;resultSets:指定当前操作的集合类型,通常省略-->
<select id="selectAllStudent" resultType="com.kingyal.pojo.Student" resultSets="java.util.List">
select
sid as stuId,
stu_num as stuNum,
stu_name as stuName,
stu_gender as stuGender,
stu_age as stuAge
from tb_students;
</select>
<!-- select 方式2,建议用方式2 -->
<!-- resultMap 标签用于定义实体类与数据表的映射关系(ORM) -->
<resultMap id="StudentMap" type="com.kingyal.pojo.Student">
<id column="sid" property="stuId"></id>
<result column="stu_num" property="stuNum"></result>
<result column="stu_name" property="stuName"></result>
<result column="stu_gender" property="stuGender"></result>
<result column="stu_age" property="stuAge"></result>
</resultMap>
<select id="selectAllStudentsByResultMap" resultMap="StudentMap">
select sid,stu_num,stu_name,stu_gender,stu_age from tb_students;
</select>
<select id="selectStudentByNum" resultMap="StudentMap">
select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
where stu_num=#{stuNum};
</select>
<select id="selectAllStudentsByHashMap" resultMap="StudentMap">
select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
limit #{start}, #{pageSize};
</select>
<!-- arg0和arg1也可以替换为param1和param2 -->
<select id="selectAllStudentsByArgs" resultMap="StudentMap">
select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
limit #{arg0}, #{arg1};
</select>
<select id="selectAllStudentsByPage" resultMap="StudentMap">
select sid,stu_num,stu_name,stu_gender,stu_age from tb_students
limit #{start}, #{pageSize};
</select>
<select id="getCount" resultType="int">
select count(1) from tb_students;
</select>
</mapper>
c. 测试
public class StudentDaoTest {
@Test
public void insertStudent() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
Student student = new Student(0, "00013", "wang13", "M", 29);
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
System.out.println(studentDao);
// 插入操作
int i = studentDao.insertStudent(student);
sqlSession.commit();
Assert.assertEquals(1, i);
}
@Test
public void insertStudentAndUseGeneratedKey() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
Student student = new Student(0, "00014", "wang14", "M", 29);
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
System.out.println(studentDao);
// 插入操作
int i = studentDao.insertStudentAndUseGeneratedKey(student);
sqlSession.commit();
System.out.println(student);
System.out.println(i);
Assert.assertEquals(1, i);
}
@Test
public void deleteStudent() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
int i = studentDao.deleteStudent(00001);
sqlSession.commit();
Assert.assertEquals(1, i);
}
@Test
public void updateStudent() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
// 待更新的实体
Student student = new Student(0, "00003", "wang3", "M", 29);
int i = studentDao.updateStudent(student);
sqlSession.commit();
Assert.assertEquals(1, i);
}
@Test
public void selectAllStudent() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
// 待更新的实体
List<Student> studentList = studentDao.selectAllStudents();
sqlSession.commit();
System.out.println(studentList.toString());
Assert.assertNotEquals(0, studentList.size());
}
@Test
public void selectAllStudentsByResultMap() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
// 待更新的实体
List<Student> studentList = studentDao.selectAllStudentsByResultMap();
sqlSession.commit();
for (Student s : studentList) {
System.out.println(s);
}
Assert.assertNotEquals(0, studentList.size());
}
@Test
public void selectStudentByNum() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
// 待更新的实体
Student student = studentDao.selectStudentByNum(00001);
sqlSession.commit();
System.out.println(student);
Assert.assertNotNull(student);
}
@Test
public void selectAllStudentsByHashMap() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
HashMap<String, Integer> hashMap = new HashMap<String, Integer>();
hashMap.put("start", 1);
hashMap.put("pageSize", 5);
// 待更新的实体
List<Student> studentList = studentDao.selectAllStudentsByHashMap(hashMap);
sqlSession.commit();
for (Student s : studentList) {
System.out.println(s);
}
Assert.assertNotEquals(0, studentList.size());
}
@Test
public void selectAllStudentsByArgs() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
// 待更新的实体
List<Student> studentList = studentDao.selectAllStudentsByArgs(1, 5);
sqlSession.commit();
for (Student s : studentList) {
System.out.println(s);
}
Assert.assertNotEquals(0, studentList.size());
}
@Test
public void selectAllStudentsByPage() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
// 待更新的实体
List<Student> studentList = studentDao.selectAllStudentsByPage(1, 5);
sqlSession.commit();
for (Student s : studentList) {
System.out.println(s);
}
Assert.assertNotEquals(0, studentList.size());
}
@Test
public void getCount() throws IOException {
// 获取数据库配置信息
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
// builder
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 会话工厂
SqlSessionFactory factory = builder.build(inputStream);
// 会话链接
SqlSession sqlSession = factory.openSession();
// 实体对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
// 待更新的实体
int count = studentDao.getCount();
System.out.println(count);
Assert.assertNotEquals(0, count);
}
}