1 <?xml version="1.0" encoding="UTF-8"?> 2 <project xmlns="http://maven.apache.org/POM/4.0.0" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 5 <modelVersion>4.0.0</modelVersion> 6 7 <groupId>sun</groupId> 8 <artifactId>mybatis05crud</artifactId> 9 <version>1.0-SNAPSHOT</version> 10 <packaging>jar</packaging> 11 12 <dependencies> 13 <dependency> 14 <groupId>org.mybatis</groupId> 15 <artifactId>mybatis</artifactId> 16 <version>3.4.5</version> 17 </dependency> 18 19 <dependency> 20 <groupId>mysql</groupId> 21 <artifactId>mysql-connector-java</artifactId> 22 <version>5.1.6</version> 23 </dependency> 24 25 <dependency> 26 <groupId>junit</groupId> 27 <artifactId>junit</artifactId> 28 <version>4.12</version> 29 </dependency> 30 31 <dependency> 32 <groupId>log4j</groupId> 33 <artifactId>log4j</artifactId> 34 <version>1.2.17</version> 35 </dependency> 36 </dependencies> 37 38 39 </project>
实体类：
1 package sun.domain; 2 3 import java.io.Serializable; 4 import java.util.Date; 5 6 public class User implements Serializable { 7 private Integer id; 8 private String username; 9 private Date birthday; 10 private String sex; 11 private String address; 12 13 public Integer getId() { 14 return id; 15 } 16 17 public void setId(Integer id) { 18 this.id = id; 19 } 20 21 public String getUsername() { 22 return username; 23 } 24 25 public void setUsername(String username) { 26 this.username = username; 27 } 28 29 public Date getBirthday() { 30 return birthday; 31 } 32 33 public void setBirthday(Date birthday) { 34 this.birthday = birthday; 35 } 36 37 public String getSex() { 38 return sex; 39 } 40 41 public void setSex(String sex) { 42 this.sex = sex; 43 } 44 45 public String getAddress() { 46 return address; 47 } 48 49 public void setAddress(String address) { 50 this.address = address; 51 } 52 53 @Override 54 public String toString() { 55 return "User{" + 56 "id=" + id + 57 ", username=‘" + username + ‘\‘‘ + 58 ", birthday=" + birthday + 59 ", sex=‘" + sex + ‘\‘‘ + 60 ", address=‘" + address + ‘\‘‘ + 61 ‘}‘; 62 } 63 }
Dao接口：
1 package sun.dao; 2 3 import sun.domain.QueryObj; 4 import sun.domain.User; 5 6 import java.util.List; 7 8 public interface UserDao { 9 /** 10 * 查询所有用户 11 * @return 12 */ 13 List<User> findAll(); 14 15 /** 16 * 创建用户 17 */ 18 void saveUser(User user); 19 20 /** 21 * 更新用户 22 */ 23 void updateUser(User user); 24 25 /** 26 * 删除用户 27 */ 28 void deleteUser(Integer userId); 29 30 /** 31 * 查询用户（根据ID） 32 */ 33 User findUserById(Integer userId); 34 35 /** 36 * 模糊查询 37 */ 38 List<User> findUserByName(String name); 39 40 /** 41 * 查询总用户数 42 */ 43 int getCount(); 44 45 /** 46 * 模糊查询 47 */ 48 List<User> findUserByQueryObj(QueryObj qobj); 49 }
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 6 7 <!--mybatis主配置文件--> 8 <configuration> 9 <!--配置环境--> 10 <environments default="mysql"> 11 <!--配置mysql环境--> 12 <environment id="mysql"> 13 <!--配置事务类型--> 14 <transactionManager type="JDBC"></transactionManager> 15 <!--配置数据库连接池--> 16 <dataSource type="POOLED"> 17 <!--配置连接数据库的四个基本信息--> 18 <property name="driver" value="com.mysql.jdbc.Driver"></property> 19 <property name="url" value="jdbc:mysql://localhost:3306/javatest"></property> 20 <property name="username" value="root"></property> 21 <property name="password" value="root"></property> 22 </dataSource> 23 24 25 </environment> 26 </environments> 27 28 <!--指定映射配置文件位置--> 29 <mappers> 30 <mapper resource="sun/dao/UserDao.xml"></mapper> 31 </mappers> 32 </configuration>
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 6 <mapper namespace="sun.dao.UserDao"> 7 8 <!--查询所有--> 9 <select id="findAll" resultType="sun.domain.User"> 10 SELECT * from user; 11 </select> 12 <!--创建用户--> 13 <insert id="saveUser" parameterType="sun.domain.User"> 14 <selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER"> 15 SELECT last_insert_id(); 16 </selectKey> 17 insert into user(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}); 18 </insert> 19 <!--更新用户--> 20 <update id="updateUser" parameterType="sun.domain.User"> 21 update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}; 22 </update> 23 <!--删除用户--> 24 <delete id="deleteUser" parameterType="java.lang.Integer"> 25 delete from user where id=#{id}; 26 </delete> 27 <!--根据id查询用户--> 28 <select id="findUserById" parameterType="int" resultType="sun.domain.User"> 29 SELECT * from user where id=#{id}; 30 </select> 31 <!--模糊查询--> 32 <select id="findUserByName" parameterType="String" resultType="sun.domain.User"> 33 SELECT * from user where username like #{username}; 34 </select> 35 <!--获取总记录数--> 36 <select id="getCount" resultType="int"> 37 SELECT count(1) from user; 38 </select> 39 <!--模糊查询--> 40 <select id="findUserByQueryObj" parameterType="sun.domain.QueryObj" resultType="sun.domain.User"> 41 SELECT * from user where username like #{user.username}; 42 </select> 43 </mapper>
1 package sun.test; 2 3 4 import org.apache.ibatis.io.Resources; 5 import org.apache.ibatis.session.SqlSession; 6 import org.apache.ibatis.session.SqlSessionFactory; 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 8 import org.junit.After; 9 import org.junit.Before; 10 import org.junit.Test; 11 import sun.dao.UserDao; 12 import sun.domain.QueryObj; 13 import sun.domain.User; 14 15 import java.io.IOException; 16 import java.io.InputStream; 17 import java.util.Date; 18 import java.util.List; 19 20 public class MybatisTest { 21 22 private InputStream in; 23 private SqlSession sqlSession; 24 private UserDao userDao; 25 26 @Before 27 public void init() throws IOException { 28 // 读取配置文件 29 in = Resources.getResourceAsStream("SqlMapConfig.xml"); 30 // 创建SqlSessionFactory 31 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); 32 SqlSessionFactory factory = builder.build(in); 33 // 使用工厂生产sqlsession对象 34 sqlSession = factory.openSession(); 35 // 使用sqlsession创建UserDao接口代理对象 36 userDao = sqlSession.getMapper(UserDao.class); 37 } 38 39 @After 40 public void destory() throws IOException { 41 sqlSession.commit(); 42 sqlSession.close(); 43 in.close(); 44 } 45 46 @Test 47 public void findAllTest() { 48 // 使用代理对象执行方法 49 List<User> all = userDao.findAll(); 50 for (User user : all) { 51 System.out.println(user); 52 } 53 } 54 55 @Test 56 public void saveUserTest() { 57 User user = new User(); 58 user.setUsername("kelvin"); 59 user.setBirthday(new Date()); 60 user.setSex("男"); 61 user.setAddress("安徽省宿州市"); 62 System.out.println(user); 63 userDao.saveUser(user); 64 System.out.println(user); 65 } 66 67 @Test 68 public void updateUserTest() { 69 User user = new User(); 70 user.setId(50); 71 user.setUsername("sun"); 72 user.setBirthday(new Date()); 73 user.setSex("男"); 74 user.setAddress("安徽省宿州市"); 75 userDao.updateUser(user); 76 } 77 78 @Test 79 public void deleteUserTest(){ 80 userDao.deleteUser(50); 81 } 82 83 @Test 84 public void findUserById(){ 85 User user = userDao.findUserById(48); 86 System.out.println(user); 87 } 88 @Test 89 public void findUserByName(){ 90 List<User> userByName = userDao.findUserByName("%王%"); 91 for (User user : userByName) { 92 System.out.println(user); 93 } 94 } 95 @Test 96 public void findUserByQueryObj(){ 97 QueryObj queryObj = new QueryObj(); 98 User user1 = new User(); 99 user1.setUsername("%王%"); 100 queryObj.setUser(user1); 101 List<User> userByName = userDao.findUserByQueryObj(queryObj); 102 for (User user : userByName) { 103 System.out.println(user); 104 } 105 } 106 107 @Test 108 public void getCountTest(){ 109 int count = userDao.getCount(); 110 System.out.println(count); 111 } 112 }
1、在执行增删改的操作中，测试未报错但数据库中数据未生效？
答：在使用Mybatis执行增删改时，AutoCommit会设置为false，所以如果没有手动添加sqlSession.commit()时增删改操作会触发事务回滚导致操作未生效。
2、如果实现类和表中字段名称不一致，查询后不能正确将查询结果封装为指定对象如何解决？
答：方法一、可以在映射配置文件的sql语句中将查询后的字段重命名为实体类中的字段。该方式是在sql语句上对该问题进行解决，因此效率较高，但是每一个查询语句都要对字段进行修改别名操作比较繁琐。
方法二、可以再映射配置文件mapper内部添加下列内容，在每一个查询操作中将属性resultType="全类名"改为resultMap="resultMap的id属性名"，对于resultMap中的property为实体类中字段，column为数据库表字段。该方式在加载配置文件时需要多加载resultMap项，但是在mapper内的所有查询操作中只需配置属性，不需要对查询sql语句进行修改，操作简便。
1 <resultMap id="userMap" type="sun.domain.User"> 2 <!--主键字段对应--> 3 <id property="user_id" column="id"></id> 4 <!--非主键关系对应--> 5 <result property="user_name" column="username"></result> 6 <result property="user_birthday" column="birthday"></result> 7 <result property="user_address" column="address"></result> 8 <result property="user_sex" column="sex"></result> 9 </resultMap>
3、在映射配置文件中，#{ }和${ }的区别的什么？
答：#{ } 预编译后使用PrepareStatement的？占位，而${ }使用Statement对象直接将参数和sql语句拼接成字符串然后在进行编译。
1、在dao包下创建dao实现类
1 package sun.dao.impl; 2 3 import org.apache.ibatis.session.SqlSession; 4 import org.apache.ibatis.session.SqlSessionFactory; 5 import sun.dao.UserDao; 6 import sun.domain.User; 7 8 import java.util.List; 9 10 /** 11 * @Classname UserDaoImpl 12 * @Description TODO 13 * @Date 2020/9/10 10:15 14 * @Created by Administrator 15 */ 16 public class UserDaoImpl implements UserDao { 17 private SqlSessionFactory sqlSessionFactory; 18 19 public UserDaoImpl(SqlSessionFactory sqlSessionFactory) { 20 this.sqlSessionFactory = sqlSessionFactory; 21 } 22 23 public List<User> findAll() { 24 SqlSession sqlSession = sqlSessionFactory.openSession(); 25 List<User> users = sqlSession.selectList("sun.dao.UserDao.findAll"); 26 sqlSession.commit(); 27 sqlSession.close(); 28 return users; 29 } 30 31 public void saveUser(User user) { 32 SqlSession sqlSession = sqlSessionFactory.openSession(); 33 int insert = sqlSession.insert("sun.dao.UserDao.saveUser", user); 34 sqlSession.commit(); 35 sqlSession.close(); 36 } 37 38 public void updateUser(User user) { 39 SqlSession sqlSession = sqlSessionFactory.openSession(); 40 int update = sqlSession.update("sun.dao.UserDao.updateUser", user); 41 sqlSession.commit(); 42 sqlSession.close(); 43 } 44 45 public void deleteUser(Integer userId) { 46 SqlSession sqlSession = sqlSessionFactory.openSession(); 47 int delete = sqlSession.delete("sun.dao.UserDao.deleteUser", userId); 48 sqlSession.commit(); 49 sqlSession.close(); 50 } 51 52 public User findUserById(Integer userId) { 53 SqlSession sqlSession= sqlSessionFactory.openSession(); 54 User user = sqlSession.selectOne("sun.dao.UserDao.findUserById", userId); 55 sqlSession.commit(); 56 sqlSession.close(); 57 return user; 58 } 59 60 public List<User> findUserByName(String name) { 61 SqlSession sqlSession = sqlSessionFactory.openSession(); 62 List<User> users = sqlSession.selectList("sun.dao.UserDao.findUserByName", name); 63 sqlSession.commit(); 64 sqlSession.close(); 65 return users; 66 } 67 68 public int getCount() { 69 SqlSession sqlSession = sqlSessionFactory.openSession(); 70 int count = sqlSession.selectOne("sun.dao.UserDao.getCount"); 71 return count; 72 } 73 }
2、测试类
1 package sun.test; 2 3 4 import org.apache.ibatis.io.Resources; 5 import org.apache.ibatis.session.SqlSession; 6 import org.apache.ibatis.session.SqlSessionFactory; 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 8 import org.junit.After; 9 import org.junit.Before; 10 import org.junit.Test; 11 import sun.dao.UserDao; 12 import sun.dao.impl.UserDaoImpl; 13 import sun.domain.User; 14 15 import java.io.IOException; 16 import java.io.InputStream; 17 import java.util.Date; 18 import java.util.List; 19 20 public class MybatisTest { 21 22 private InputStream in; 23 private UserDaoImpl userDao; 24 25 @Before 26 public void init() throws IOException { 27 // 读取配置文件 28 in = Resources.getResourceAsStream("SqlMapConfig.xml"); 29 // 创建SqlSessionFactory 30 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); 31 SqlSessionFactory factory = builder.build(in); 32 33 userDao = new UserDaoImpl(factory); 34 } 35 36 @After 37 public void destory() throws IOException { 38 in.close(); 39 } 40 41 @Test 42 public void findAllTest() { 43 // 使用代理对象执行方法 44 List<User> all = userDao.findAll(); 45 for (User user : all) { 46 System.out.println(user); 47 } 48 } 49 50 @Test 51 public void saveUserTest() { 52 User user = new User(); 53 user.setUsername("kelvin"); 54 user.setBirthday(new Date()); 55 user.setSex("男"); 56 user.setAddress("安徽省宿州市"); 57 System.out.println(user); 58 userDao.saveUser(user); 59 System.out.println(user); 60 } 61 62 @Test 63 public void updateUserTest() { 64 User user = new User(); 65 user.setId(54); 66 user.setUsername("sun"); 67 user.setBirthday(new Date()); 68 user.setSex("男"); 69 user.setAddress("安徽省宿州市"); 70 userDao.updateUser(user); 71 } 72 73 @Test 74 public void deleteUserTest(){ 75 userDao.deleteUser(54); 76 } 77 78 @Test 79 public void findUserById(){ 80 User user = userDao.findUserById(48); 81 System.out.println(user); 82 } 83 @Test 84 public void findUserByName(){ 85 List<User> userByName = userDao.findUserByName("%王%"); 86 for (User user : userByName) { 87 System.out.println(user); 88 } 89 } 90 91 @Test 92 public void getCountTest(){ 93 int count = userDao.getCount(); 94 System.out.println(count); 95 } 96 }
原文：https://www.cnblogs.com/sun-10387834/p/13644817.html