Mybatis 中编写dao实现类的使用 - 了解
Mybatis 支持编写通过自己编写dao的实现类来实现对数据库的操作。由于在实际的开发中,使用这种方法会让程序员手动编写大量的代码,所以在实际开发中,我们一般使用自动生成代理的方式来进行数据库的操作,而不是这个方式。
1. 查询列表
-
创建一个 maven 项目,不使用骨架
-
接着,在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"> <modelVersion>4.0.0</modelVersion> <groupId>com.itheima</groupId> <artifactId>mybatis_dao</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.17</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> <scope>test</scope> </dependency> </dependencies> </project>
-
然后编写dao接口类:
package com.itheima.dao; import com.itheima.domain.User; import java.util.List; /** * 用户持久层 */ public interface IUserDao { /** * 查询所有用户 * @return List<User> */ List<User> findAll(); }
-
接着,在 resources 文件夹下,创建主配置文件,并写入一下配置:
<?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"> <!-- 配置 mysql 的环境--> <environment id="mysql"> <!-- 配置事物--> <transactionManager type="JDBC"></transactionManager> <!-- 配置连接池--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/day23?serverTimezone=Asia/Shanghai"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <!-- 配置映射文件的位置--> <mappers> <mapper resource="com/itheima/dao/IUserDao.xml"></mapper> </mappers> </configuration>
-
然后在 resources 文件夹下,创建一个和 dao 接口类包相同结构的文件夹,在文件夹下创建一个映射文件,并写入以下配置:
<?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.itheima.dao.IUserDao"> <!-- 映射的方法--> <!-- 查询所有操作--> <select id="findAll" resultType="com.itheima.domain.User"> <!--select id as userId, username as userName, address as userAddress, sex as userSex, birthday as userBirthday from user; --> select * from user; </select> </mapper>
-
为了在项目中可以使用 log4j,我们需要在 resources 文件夹下面创建一个 log4j.properies 文件,已提供 log4j 运行时的属性:
# Set root category priority to INFO and its only appender to CONSOLE. #log4j.rootCategory=INFO, CONSOLE debug info warn error fatal log4j.rootCategory=debug, CONSOLE, LOGFILE # Set the enterprise logger category to FATAL and its only appender to CONSOLE. log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE # CONSOLE is set to be a ConsoleAppender using a PatternLayout. log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n # LOGFILE is set to be a File appender using a PatternLayout. log4j.appender.LOGFILE=org.apache.log4j.FileAppender log4j.appender.LOGFILE.File=d:\axis.log log4j.appender.LOGFILE.Append=true log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
-
然后,在dao文件夹下创建一个impl文件夹,以存放 dao 接口的实现类,创建一个 java 文件,这个Java 文件就是 dao 的实现类,里面存放了如何查询列表的方法:
package com.itheima.dao.impl; import com.itheima.dao.IUserDao; import com.itheima.domain.User; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import java.util.List; public class UserDaoImpl implements IUserDao { SqlSessionFactory factory; public UserDaoImpl(SqlSessionFactory factory) { this.factory = factory; } public List<User> findAll() { // 1. 根据 SqlSessionFactory 获取 SqlSession 对象 SqlSession session = factory.openSession(); // 2. 调用 SqlSession 中的方法,来实现查询列表 List<User> users = session.selectList("com.itheima.dao.IUserDao.findAll");// 参数就是获取的配置信息 // 3. 释放资源 session.close(); return users; } }
这里我们创建了一个 SqlSessionFactory 对象,并使用构造函数以获取传入的 SqlSessionFactory 对象。SqlSessionFactory 对象主要是用来生成 SqlSession 对象,我们再通过生成的 SqlSession 对象了,传入我们在映射文件中写入的 Sql 语句来实现对数据库的查询。
-
最后我们在测试类中调用该方法即可:
package com.itheima.test; import com.itheima.dao.IUserDao; import com.itheima.dao.impl.UserDaoImpl; import com.itheima.domain.User; 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.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.Date; import java.util.List; /** * 测试 Mybatis 的 CRUD 方法 */ public class MybatisTest { private InputStream is; private IUserDao userDao; @Before public void init() throws IOException { // 1. 读取配置文件,生成文件字符流 is = Resources.getResourceAsStream("SqlMapConfig.xml"); // 2. 获取 SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is); // 3. 使用工厂创建 dao 对象 userDao = new UserDaoImpl(factory); } @After public void destroy() throws IOException { if (is != null) { is.close(); } } @Test public void testFindAll() { // 5. 执行查询方法 List<User> users = userDao.findAll(); for(User user : users) { System.out.println(user); } } }
在调用该方法时,我们首先需要读取主配置文件中,我们设置的数据库传入的属性值,以获取对数据库的连接,通过使用工厂,创建 dao 对象。最后执行查询。
-
以下是结果:
User{idd=41, username='老王', address='北京', sex='男', birthday=Tue Feb 27 17:47:08 CST 2018} User{idd=42, username='小二王', address='北京金燕龙', sex='女', birthday=Fri Mar 02 15:09:37 CST 2018} User{idd=43, username='小二王', address='北京金燕龙', sex='女', birthday=Sun Mar 04 11:34:34 CST 2018} User{idd=45, username='传智播客', address='北京金燕龙', sex='男', birthday=Sun Mar 04 12:04:06 CST 2018} User{idd=46, username='老王', address='北京', sex='男', birthday=Wed Mar 07 17:37:26 CST 2018} User{idd=48, username='小马宝莉', address='北京修正', sex='女', birthday=Thu Mar 08 11:44:00 CST 2018} User{idd=51, username='mybatis update user1', address='China', sex='男', birthday=Thu Aug 22 20:45:19 CST 2019} User{idd=54, username='mybatis save user and get id', address='China', sex='男', birthday=Thu Aug 22 18:18:44 CST 2019}
2. 保存操作
-
首先,在 dao 接口中创建一个保存方法:
/** * 保存用户 * @param user */ void saveUser(User user);
-
接着,在 dao 的实现类中,我们实现保存方法:
public void saveUser(User user) { // 1. 根据 SqlSessionFactory 对象获取 SqlSession 对象 SqlSession session = factory.openSession(); // 2. 使用 SqlSession 对象中的方法,来实现保存操作 session.insert("com.itheima.dao.IUserDao.saveUser"); // 3. 提交事务 session.commit(); // 4. 释放资源 session.close(); return ; }
这里需要注意,执行之后,我们需要提交事务。
-
然后,编写测试类进行测试:
@Test public void testSave() { User user = new User(); user.setUsername("赵老头"); user.setSex("男"); user.setAddress("China"); user.setBirthday(new Date()); userDao.saveUser(user); }
-
然后,运行,会发现报错:
org.apache.ibatis.exceptions.PersistenceException: ### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'username' cannot be null ### The error may exist in com/itheima/dao/IUserDao.xml ### The error may involve com.itheima.dao.IUserDao.saveUser-Inline ### The error occurred while setting parameters ### SQL: insert into user (username, address, sex, birthday) values (?, ?, ?, ?); ### Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'username' cannot be null
log 文件里显示,username 不能为空。我们分别设置了值。
为什么会报错呢?我们来看实现类中的
session.insert()
方法。通过查看源码,我们发现,insert有两个重构方法:session.insert(String statement); session.insert(String statement, Object parameter);
我们使用的是第一种,读取配置文件的 SQL 配置信息。但是我们在读取映射文件的 sql 配置信息之后,并没有将 user 对象传入,导致我们在存储的时候并没有 user 对象的存入。
所以只需要使用第二个重构方法,再将 user 对象传入即可。
-
修改正确后的执行结果:
数据库中已经有了结果:
55 赵老头 2019-08-23 12:02:44 男 China
3. 更新用户
-
在 dao 接口中写入更新方法:
/** * 更新用户 * @param user */ void updateUser(User user);
-
在 dao 实现类中实现更新方法,其实和之前的保存和查询列表方法类似:
public void updateUser(User user) { // 1. 根据 SqlSessionFactory 对象获取 SqlSession 对象 SqlSession session = factory.openSession(); // 2. 使用 SqlSession 对象中的方法,来实现更新操作: session.update("com.itheima.dao.IUserDao.updateUser", user); // 3. 提交事务 session.commit(); // 4. 关闭连接 session.close(); return ; }
-
然后在映射配置文件中,进行一下配置:
<!-- 更新用户--> <update id="updateUser" parameterType="com.itheima.domain.User"> update user set username=#{username}, address=#{address}, sex=#{sex}, birthday=#{birthday} where id=#{id}; </update>
-
以下是结果:
2019-08-23 13:06:14,478 0 [ main] DEBUG ache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter. 2019-08-23 13:06:14,507 29 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:06:14,507 29 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:06:14,507 29 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:06:14,507 29 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:06:14,575 97 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection 2019-08-23 13:06:15,388 910 [ main] DEBUG source.pooled.PooledDataSource - Created connection 1313916817. 2019-08-23 13:06:15,388 910 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4e50c791] 2019-08-23 13:06:15,392 914 [ main] DEBUG theima.dao.IUserDao.updateUser - ==> Preparing: update user set username=?, address=?, sex=?, birthday=? where id=?; 2019-08-23 13:06:15,426 948 [ main] DEBUG theima.dao.IUserDao.updateUser - ==> Parameters: 赵老太(String), China Hongkong(String), 女(String), 2019-08-23 13:06:14.567(Timestamp), 55(Integer) 2019-08-23 13:06:15,430 952 [ main] DEBUG theima.dao.IUserDao.updateUser - <== Updates: 1 2019-08-23 13:06:15,431 953 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4e50c791] 2019-08-23 13:06:15,435 957 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4e50c791] 2019-08-23 13:06:15,435 957 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4e50c791] 2019-08-23 13:06:15,435 957 [ main] DEBUG source.pooled.PooledDataSource - Returned connection 1313916817 to pool.
我们发现日志中记录成功修改了数据。
-
数据库中的数据也得到了修改:
55 赵老太 2019-08-23 13:06:15 女 China Hongkong
4. 删除操作
-
在 dao 接口中,写入删除方法:
/** * 根据 id 删除用户 * @param id */ void deleteUser(Integer id);
-
在 dao 接口的实现类中,写入实现方法:
public void deleteUser(Integer id) { // 1. 根据 SqlSessionFactory 对象获取 SqlSession 对象 SqlSession session = factory.openSession(); // 2. 使用 SqlSession 对象中的删除方法,来实现删除操作: session.delete("com.itheima.dao.IUserDao.deleteUser", id); // 3. 执行事务 session.commit(); // 4. 关闭连接 session.close(); return ; }
依旧是调用 SqlSession 对象中的方法,这次是删除方法,传入配置文件中的 Sql 语句和需要删除的用户的 ID;
-
在映射文件中配置一下信息:
<!-- 删除用户--> <delete id="deleteUser" parameterType="java.lang.Integer"> delete from user where id = #{id}; </delete>
-
编写测试类,进行测试:
@Test public void testDelet() { Integer id = 55; userDao.deleteUser(id); }
-
查看数据库,发现数据库中已经删除了 id 为 55 的那条数据。
5. 查询一个
-
首先,在 dao 接口中,写下查询一个方法:
/** * 根据用户的 id 查找用户 * @param id * @return */ User findById(Integer id);
-
然后,在实现类中进行实现:
public User findById(Integer id) { // 1. 根据 SqlSessionFactory 对象获取 SqlSession 对象 SqlSession session = factory.openSession(); // 2. 使用 SqlSession 对象中的查询方法来实现查询一个: User user = session.selectOne("com.itheima.dao.IUserDao.findById", id); session.close();s return user; }
由于实现类中是有 selectOne 这个方法,直接调用即可。
-
映射文件中的配置信息:
<!-- 查询一个--> <select id="findById" parameterType="int" resultType="com.itheima.domain.User"> select * from user where id = #{id}; </select>
-
在测试类中使用:
@Test public void testFindById() { Integer id = 56; User user = userDao.findById(id); System.out.println(user); }
-
结果:
2019-08-23 13:45:35,342 0 [ main] DEBUG ache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter. 2019-08-23 13:45:35,367 25 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:45:35,367 25 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:45:35,367 25 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:45:35,368 26 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:45:35,436 94 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection 2019-08-23 13:45:36,286 944 [ main] DEBUG source.pooled.PooledDataSource - Created connection 868815265. 2019-08-23 13:45:36,286 944 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@33c911a1] 2019-08-23 13:45:36,290 948 [ main] DEBUG .itheima.dao.IUserDao.findById - ==> Preparing: select * from user where id = ?; 2019-08-23 13:45:36,320 978 [ main] DEBUG .itheima.dao.IUserDao.findById - ==> Parameters: 56(Integer) 2019-08-23 13:45:36,345 1003 [ main] DEBUG .itheima.dao.IUserDao.findById - <== Total: 1 User{idd=56, username='赵老头', address='China', sex='男', birthday=Fri Aug 23 12:58:19 CST 2019}
6. 模糊查询
-
在 dao 接口中写入模糊查询的方法:
/** * 根据名称,模糊查询用户信息 * @param username * @return */ List<User> findByName(String username);
-
在实现类中实现该方法:
public List<User> findByName(String username) { // 1. 根据 SqlSessionFactory 对象获取 SqlSession 对象 SqlSession session = factory.openSession(); // 2. 使用 SqlSession 对象中的查询方法来实现查询一个: List<User> users = session.selectList("com.itheima.dao.IUserDao.findByName", username); session.close(); return users; }
-
在映射文件中进行一下配置:
<!-- 根据名称模糊查询的语法--> <!-- <select id="findByName" parameterType="String" resultType="com.itheima.domain.User">--> <!-- select * from user where username like #{value};--> <!-- </select>--> <select id="findByName" parameterType="String" resultType="com.itheima.domain.User"> select * from user where username like #{username}; </select>
-
在测试类进行一下测试:
@Test public void findByName() { String username = "%王%"; List<User> users = userDao.findByName(username); for(User user : users) { System.out.println(user); } }
-
结果:
2019-08-23 13:55:59,058 0 [ main] DEBUG ache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter. 2019-08-23 13:55:59,085 27 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:55:59,085 27 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:55:59,085 27 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:55:59,085 27 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 13:55:59,146 88 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection 2019-08-23 13:55:59,945 887 [ main] DEBUG source.pooled.PooledDataSource - Created connection 1525409936. 2019-08-23 13:55:59,945 887 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5aebe890] 2019-08-23 13:55:59,949 891 [ main] DEBUG theima.dao.IUserDao.findByName - ==> Preparing: select * from user where username like ?; 2019-08-23 13:55:59,980 922 [ main] DEBUG theima.dao.IUserDao.findByName - ==> Parameters: %王%(String) 2019-08-23 13:56:00,003 945 [ main] DEBUG theima.dao.IUserDao.findByName - <== Total: 4 User{idd=41, username='老王', address='北京', sex='男', birthday=Tue Feb 27 17:47:08 CST 2018} User{idd=42, username='小二王', address='北京金燕龙', sex='女', birthday=Fri Mar 02 15:09:37 CST 2018} User{idd=43, username='小二王', address='北京金燕龙', sex='女', birthday=Sun Mar 04 11:34:34 CST 2018} User{idd=46, username='老王', address='北京', sex='男', birthday=Wed Mar 07 17:37:26 CST 2018}
7. 查询数据总条数
-
在 dao 接口中定义以下方法:
/** * 查询总用户数 * @return */ int findTotal();
-
在实现类中实现该方法:
public int findTotal() { // 1. 根据 SqlSessionFactory 对象获取 SqlSession 对象 SqlSession session = factory.openSession(); // 2. 使用 SqlSession 对象中的查询方法来实现查询一个: Integer total = session.selectOne("com.itheima.dao.IUserDao.findTotal"); session.close(); return total; }
-
映射文件的配置如下:
<!-- 获取用户的总记录条数--> <select id="findTotal" resultType="int"> select count(id) from user; </select>
-
测试方法如下:
@Test public void findAll() { int total = userDao.findTotal(); System.out.println(total); }
-
结果:
2019-08-23 14:14:07,212 0 [ main] DEBUG ache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter. 2019-08-23 14:14:07,242 30 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 14:14:07,242 30 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 14:14:07,242 30 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 14:14:07,242 30 [ main] DEBUG source.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections. 2019-08-23 14:14:07,304 92 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection 2019-08-23 14:14:08,208 996 [ main] DEBUG source.pooled.PooledDataSource - Created connection 1525409936. 2019-08-23 14:14:08,208 996 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5aebe890] 2019-08-23 14:14:08,212 1000 [ main] DEBUG itheima.dao.IUserDao.findTotal - ==> Preparing: select count(id) from user; 2019-08-23 14:14:08,239 1027 [ main] DEBUG itheima.dao.IUserDao.findTotal - ==> Parameters: 2019-08-23 14:14:08,267 1055 [ main] DEBUG itheima.dao.IUserDao.findTotal - <== Total: 1 2019-08-23 14:14:08,270 1058 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5aebe890] 2019-08-23 14:14:08,270 1058 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5aebe890] 2019-08-23 14:14:08,270 1058 [ main] DEBUG source.pooled.PooledDataSource - Returned connection 1525409936 to pool. 10