酷应用

如何使用 Mybatis 实现数据库 CURD 操作？

百家作者：CSDN 2020-04-10 15:35:53

作者?|?阿文，责编 | 郭芮

头图 | CSDN 下载自东方IC

出品 | CSDN（ID：CSDNnews）

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

在传统的JDBC 实现中，我们需要把查询过程写在java 类中，这样非常不便于后期维护，而使用Mybatis 则可以将查询语句配置在配置文件中，只需要维护好映射关系即可，下面我们就来一起看雪如何去使用Mybatis吧。

准备工作

首先，你需要准备以下软件：

idea
maven
mysql

创建数据库并插入数据

首先，我们创建一个mybatis_db 的数据库，然后创建一个表 t_user，在这个表中我们插入几条数据，如下所示：

mysql>?create?database?mybatis_db;
Query?OK,?1?row?affected?(0.01?sec)

mysql>?use?mybatis_db;
Database?changed
mysql>?create?table?t_user(
????->?id?int(32)?primary?key?auto_increment,
????->?username?varchar(50),
????->?jobs?varchar(50),
????->?phone?varchar(16));
Query?OK,?0?rows?affected,?1?warning?(0.01?sec)

mysql>?insert?into?t_user?values(1,"zhangsan","teacher","13142767333");
Query?OK,?1?row?affected?(0.01?sec)

mysql>?insert?into?t_user?values(2,"lisi","engineer","13142767334");
Query?OK,?1?row?affected?(0.01?sec)

mysql>?insert?into?t_user?values(3,"wangwu","pilot","12342767334");
Query?OK,?1?row?affected?(0.00?sec)

mysql>

配置pom.xml 下载jar包

我们创建一个maven工程，并配置pom.xml 下载mybatis 和mysql-connect-java 的jar包，目前mybatis 的最新版本是3.5.4。

?<dependency>
??????<groupId>org.mybatis</groupId>
??????<artifactId>mybatis</artifactId>
??????<version>3.5.4</version>
????</dependency>
????<dependency>
??????<groupId>mysql</groupId>
??????<artifactId>mysql-connector-java</artifactId>
??????<version>8.0.19</version>
????</dependency>

整个工程的目录如下：

配置mybatis-config

接下来，我们在 idea 的resource 中创建一个mybatis-config.xml 的配置文件(如果没有你需要在src\main目录点击右键创建一个目录，创建的时候选择resource即可)，内容如下，具体不需要过多解释，该配置文件主要是定义JDBC相关的参数包括使用的驱动、mysql 访问地址、用户名和密码以及通过mappers 定义一个mybatis 的映射文件。

<?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="mysql">
????????<environment?id="mysql">
????????????<transactionManager?type="JDBC"?/>
????????????<dataSource?type="POOLED">
????????????????<property?name="driver"?value="com.mysql.jdbc.Driver"?/>
????????????????<property?name="url"?value="jdbc:mysql://192.168.10.128:3306/mybatis_db"?/>
????????????????<property?name="username"?value="root"?/>
????????????????<property?name="password"?value="123456"?/>
????????????</dataSource>
????????</environment>
????</environments>
????<mappers>
????????<mapper?resource="UserMapper.xml"?/>
????</mappers>
</configuration>

创建user类

接下里我们创建对于的User类，并生成get和set以及toString 方法，在idea 中，我们在IDE中右键选择Generate 然后选择Getter 和Setter 以及toString() 全选即可快速生成对应的get和set方法。

最终结果如下：

package?com.mybatis;

public?class?User?{
????private?Integer?id;
????private?String?username;
????private?String?jobs;
????private?String?phone;

????public?Integer?getId()?{
????????return?id;
????}

????public?String?getUsername()?{
????????return?username;
????}

????public?String?getJobs()?{
????????return?jobs;
????}

????public?String?getPhone()?{
????????return?phone;
????}

????public?void?setId(Integer?id)?{
????????this.id?=?id;
????}

????public?void?setUsername(String?username)?{
????????this.username?=?username;
????}

????public?void?setJobs(String?jobs)?{
????????this.jobs?=?jobs;
????}

????public?void?setPhone(String?phone)?{
????????this.phone?=?phone;
????}

????@Override
????public?String?toString()?{
????????return?"User{"?+
????????????????"id="?+?id?+
????????????????",?username='"?+?username?+?'\''?+
????????????????",?jobs='"?+?jobs?+?'\''?+
????????????????",?phone='"?+?phone?+?'\''?+
????????????????'}';
????}
}

创建测试类

配置UserMapper.xml

接下里，我们创建映射文件UserMapper.xml 然后需要制定一个namespace就是 UserMapper，接着写一个select 语句，定义id和参数类型以及resultType，resultMap是指描述如何从数据库结果集中加载对象，是最复杂也是最强大的元素。

<?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="UserMapper">
????<!--根据用户编号获取用户信息?-->
????<select?id="findUserById"?parameterType="Integer"?resultType="com.mybatis.User">
????????select?*?from?t_user?where?id=#{id}
????</select>
</mapper>

然后我们创建一个测试类，根据id来查询用户：

package?com.mybatis;

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;
import?org.junit.jupiter.api.Test;

/**
?*?@Auth:?xxxx
?*?@E-mail:?xxx
?*?@title:?MybatisTest
?*?@projectName:?mybatis
?*?@description:?TODO?描述信息
?*?@Date?2020/4/7?9:15?下午
?**/
public?class?MybatisTest?{

????@Test
????public?void?findUserByIdTest()?throws?Exception?{
????????String?resource?=?"mybatis-config.xml";
????????InputStream?inputStream?=?Resources.getResourceAsStream(resource);
????????SqlSessionFactory?sqlSessionFactory?=?new?SqlSessionFactoryBuilder().build(inputStream);
????????SqlSession?sqlSession?=?sqlSessionFactory.openSession();
????????User?user?=?sqlSession.selectOne("findUserById",?1);
????????System.out.println(user.toString());
????????sqlSession.close();
????}

}

如果我们希望进行模糊查询，则在UserMapper.xml 中定义：

<select?id="findUserByName"?parameterType="String"?resultType="com.mybatisdemo.User">
????????select?*?from?t_user?where?username?like?concat('%','${value}','%')

</select>

使用like concat('%','${value}','%') 是为了防止sql 注入带来的安全隐患。

然后在测试类中新建一个方法进行测试：

??@Test
????public?void?findUserByNameTest()?throws??Exception?{
????????String?resource?=?"mybatis-config.xml";
????????InputStream?inputStream?=?Resources.getResourceAsStream(resource);
????????SqlSessionFactory?sqlSessionFactory?=?new?SqlSessionFactoryBuilder().build(inputStream);
????????SqlSession?sqlSession?=?sqlSessionFactory.openSession();
????????List<User>?users?=?sqlSession.selectList("findUserByName","g");
????????for?(User?user:users){
????????????System.out.println(user.toString());
????????}
????????sqlSession.close();
????????}

如图所示：

接下来我们测试下添加新用户，首先我们在UserMapper.xml 中定义一个insert元素：

<insert?id="addUser"?parameterType="com.mybatisdemo.User"?>
???????insert?into?t_user(username,jobs,phone)?value?(#{username},#{jobs},#{phone})
</insert>

然后来写测试接口：

????@Test
????public?void?addUser()?throws?Exception?{
????????????String?resource?=?"mybatis-config.xml";
????????????InputStream?inputStream?=?Resources.getResourceAsStream(resource);
????????????SqlSessionFactory?sqlSessionFactory?=?new?SqlSessionFactoryBuilder().build(inputStream);
????????????SqlSession?sqlSession?=?sqlSessionFactory.openSession();
????????????User?user?=?new?User();
????????????user.setUsername("beiluo");
????????????user.setJobs("DevOps");
????????????user.setPhone("1314566666");
????????????int?rows?=?sqlSession.insert("addUser",user);
????????????if?(rows?>0){
????????????????System.out.println("Success?add?"+?rows?+"data！");
????????????}else{
????????????????System.out.println("add?data?fail!");
????????????}
????????????sqlSession.commit();
????????????sqlSession.close();

????????}

如下所示：

接下来测试更新：

?<update?id="updateUserInfo"?parameterType="com.mybatisdemo.User">
????????update?t_user?set?username=#{username},jobs=#{jobs},phone=#{phone}?where?id?=#{id}
</update>

然后写个测试类：

?@Test

????public?void?updateUserinfo()?throws??Exception?{

????????String?resource?=?"mybatis-config.xml";
????????InputStream?inputStream?=?Resources.getResourceAsStream(resource);
????????SqlSessionFactory?sqlSessionFactory?=?new?SqlSessionFactoryBuilder().build(inputStream);
????????SqlSession?sqlSession?=?sqlSessionFactory.openSession();
????????User?user?=?new?User();
????????user.setId(1);
????????user.setUsername("jike");
????????user.setJobs("qa");
????????user.setPhone("13142764432");
????????int?rows?=?sqlSession.update("updateUserInfo",user);
????????if?(rows?>0){
????????????System.out.println("Success?update?"+?rows?+" data！");
????????}else{
????????????System.out.println("update?data?fail!");
????????}
????????sqlSession.commit();
????????sqlSession.close();

????}

执行后如下所示：

最后，我们测试下删除功能：

?<delete?id="deleteUser"?parameterType="com.mybatisdemo.User"?>
????????delete?from?t_user?where?id=#{id}
</delete>

测试类如下：

@Test
????public?void?deleteUser()?throws?Exception?{

????????String?resource?=?"mybatis-config.xml";
????????InputStream?inputStream?=?Resources.getResourceAsStream(resource);
????????SqlSessionFactory?sqlSessionFactory?=?new?SqlSessionFactoryBuilder().build(inputStream);
????????SqlSession?sqlSession?=?sqlSessionFactory.openSession();
????????int?rows?=?sqlSession.delete("deleteUser",1);
????????if?(rows?>0){
????????????System.out.println("Success?delete?"+?rows?+" data！");
????????}else{
????????????System.out.println("delete?data?fail!");
????????}
????????sqlSession.commit();
????????sqlSession.close();


????}