Mybatis动态SQL
MybatisCRUD操作
项目结构
配置文件
-
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.xiaoge</groupId> <artifactId>dynamicSQL</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.13</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> </dependencies> </project>
-
jdbcConfig.properties
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/eesy_mybatis jdbc.username=root jdbc.password=123456
-
SqlMapConfig.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--> <properties resource="jdbcConfig.properties"> </properties> <!-- 使用typeAliases配置别名, 它只能配置domain中类的别名 --> <typeAliases> <package name="com.xiaoge.domain"></package> </typeAliases> <!--配置环境--> <environments default="mysql"> <!--配置mysql环境--> <environment id="mysql"> <!--配置事务--> <transactionManager type="JDBC"></transactionManager> <!--配置连接池--> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments> <mappers> <package name="com.xiaoge.dao"></package> </mappers> </configuration>
-
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.xiaoge.dao.UserDao"> <!-- 解决方案二: 查询结果的列名和实体类的属性名的对应关系 --> <!-- id为唯一标识: 可以随便写 --> <!-- type查询的是对应的实体类是哪一个 --> <resultMap id="userMap" type="user"> <!-- 主键字段的对应 property对应实体类的属性 column对应数据库字段 --> <id property="userId" column="id"></id> <!-- 非主键字段的对应 --> <result property="userName" column="username"></result> <result property="userSex" column="sex"></result> <result property="userAddress" column="address"></result> <result property="userBirthday" column="birthday"></result> </resultMap> <!-- 抽取重复的sql语句 --> <sql id="defaultUser"> <!-- 注意这里千万不要写; 因为有可能会拼接动态sql语句 有;就会报错 --> select * from user </sql> <!-- 查询所有用户操作 --> <select id="findAll" resultMap="userMap"> <!-- 引入sql语句, 就不在用写select * from user语句了, 因为它被抽取了, 需要用倒时, 直接这样引入就可以了 --> <include refid="defaultUser"></include> <!-- select * from user --> </select> <!--查询条记录信息--> <select id="findById" parameterType="java.lang.Integer" resultMap="userMap"> select * from user where id = #{userId} </select> <!--根据用户名模糊查询--> <select id="findByUsername" parameterType="java.lang.String" resultMap="userMap"> select * from user where username like #{userName} </select> <!--根据queryVo的条件查询用户--> <select id="findByVo" resultMap="userMap" parameterType="QueryVo"> select * from user where username like #{user.userName} </select> <!-- 根据条件查询( 这是使用if的 需要在sql后写 where 1 = 1) <select id="findUserByCondition" resultMap="userMap" parameterType="User"> select * from user where 1 = 1 注意了sql上的不区分大小写, 不是sql上的按照实体类属性名写 <if test="userName != null"> and username = #{userName} </if> <if test="userSex != null"> and sex = #{userSex} </if> </select> --> <!--根据条件查询( 这是使用where嵌套if的 不需要在sql后写 where 1 = 1)--> <select id="findUserByCondition" resultMap="userMap" parameterType="User"> <!-- select * from user --> <include refid="defaultUser"></include> <!-- 注意了sql上的不区分大小写, 不是sql上的按照实体类属性名写 --> <where><!-- 动态添加 where 条件 --> <if test="userName != null"> and username = #{userName} </if> <if test="userSex != null"> and sex = #{userSex} </if> </where> </select> <!-- 根据QueryVo中的Id集合实现查询用户列表 --> <select id="findUserInIds" resultMap="userMap" parameterType="QueryVo"> select * from user <where> <if test="ids != null and ids.size() > 0"> <!-- <foreach>标签用于遍历集合,它的属性: collection:代表要遍历的集合元素,注意编写时不要写#{} open:代表语句的开始部分 close:代表结束部分 item:代表遍历集合的每个元素,生成的变量名 sperator:代表分隔符 --> <foreach collection="ids" open="and id in (" close=")" item="id" separator=","> #{id} <!-- 这里跟item里的名称对应 --> </foreach> </if> </where> </select> </mapper>
实体类
-
User
package com.xiaoge.domain; import java.util.Date; /** * @Author: 潇哥 * @DateTime: 2020/3/4 下午4:03 * @Description: TODO */ public class User { private Integer userId; private String userName; private String userAddress; private String userSex; private Date userBirthday; public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getUserAddress() { return userAddress; } public void setUserAddress(String userAddress) { this.userAddress = userAddress; } public String getUserSex() { return userSex; } public void setUserSex(String userSex) { this.userSex = userSex; } public Date getUserBirthday() { return userBirthday; } public void setUserBirthday(Date userBirthday) { this.userBirthday = userBirthday; } @Override public String toString() { return "User{" + "userId=" + userId + ", userName='" + userName + '\'' + ", userAddress='" + userAddress + '\'' + ", userSex='" + userSex + '\'' + ", userBirthday=" + userBirthday + '}'; } }
-
QueryVo
package com.xiaoge.domain; import java.util.List; /** * @Author: 潇哥 * @DateTime: 2020/3/6 下午3:49 * @Description: TODO */ public class QueryVo { private User user; private List<Integer> ids; public User getUser() { return user; } public void setUser(User user) { this.user = user; } public List<Integer> getIds() { return ids; } public void setIds(List<Integer> ids) { this.ids = ids; } }
持久层接口
-
UserDao
package com.xiaoge.dao; import com.xiaoge.domain.QueryVo; import com.xiaoge.domain.User; import java.util.List; /** * @Author: 潇哥 * @DateTime: 2020/3/4 下午4:19 * @Description: 用户的持久层接口 */ public interface UserDao { /** * 查询所有用户 * @return */ public List<User> findAll(); /** * 查询该用户信息 * @param userId * @return */ public User findById(Integer userId); /** * 根据用户名模糊查询用户信息 * @param username * @return */ public List<User> findByUsername(String username); /** * 根据QueryVo中的条件查询用户 * @param vo * @return */ public List<User> findByVo(QueryVo vo); /** * 根据传入参数条件 * @param user 查询的条件: 有可能有用户名, 有可能有性别, 也由于可能有地址, 还有可能都有 * @return */ public List<User> findUserByCondition(User user); /** * 根据QueryVo中提供的id集合, 查询用户信息 * @param vo * @return */ public List<User> findUserInIds(QueryVo vo); }
测试持久层接口方法
-
MybatisTest
package com.xiaoge.test; import com.xiaoge.dao.UserDao; import com.xiaoge.domain.QueryVo; import com.xiaoge.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.ArrayList; import java.util.List; /** * @Author: 潇哥 * @DateTime: 2020/3/4 下午4:22 * @Description: 测试mybatis的crud操作 */ public class MybatisTest { private InputStream is; private SqlSession session; private UserDao userDao; /** * 初始化 获取代理对象 */ @Before // 用于在测试方法之前执行 public void init() throws IOException { // 1. 加载配置文件 is = Resources.getResourceAsStream("SqlMapConfig.xml"); // 2. 获取SqlSessionFactory SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(is); // 3. 获取SqlSession对象 /* 注意: factory.openSession() 参数默认是false, 事务必须手动提交 factory.openSession(true)事务就会自动提交, 就不要要使用SqlSession对象的commit方法了 commit: 提交事务 rollback: 回滚事务 */ session = factory.openSession(); // 4. 获取代理对象 userDao = session.getMapper(UserDao.class); } @After // 用与在测试方法之后执行 public void destroy() throws IOException { // 1. 提交事务(提交事务以后才会映射进数据库, 不提交的话, 它会自动回滚) session.commit(); // 2. 释放资源 if (session != null) { session.close(); } if (is != null) { is.close(); } } /** * 测试查询所有用户信息 * @throws IOException */ @Test public void findAllTest() throws IOException { // 1. 遍历获取所有的用户 List<User> users = userDao.findAll(); for (User user : users) { System.out.println(user); } // 运行结果 User{userId=41, userName='老王', userAddress='北京', userSex='男', userBirthday=Wed Feb 28 07:47:08 CST 2018} User{userId=42, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Sat Mar 03 05:09:37 CST 2018} User{userId=43, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Mon Mar 05 01:34:34 CST 2018} User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Thu Mar 08 07:37:26 CST 2018} User{userId=48, userName='小马宝莉', userAddress='北京修正', userSex='女', userBirthday=Fri Mar 09 01:44:00 CST 2018} User{userId=53, userName='啸哥', userAddress='北京四合院', userSex='男', userBirthday=Fri Mar 06 13:10:42 CST 2020} User{userId=54, userName='李四 last insertid', userAddress='北京', userSex='男', userBirthday=Fri Mar 06 14:26:40 CST 2020} User{userId=55, userName='王五 last insertid', userAddress='北京', userSex='男', userBirthday=Fri Mar 06 15:17:43 CST 2020} User{userId=56, userName='李六 last insertid', userAddress='北京', userSex='男', userBirthday=Fri Mar 06 18:47:48 CST 2020} User{userId=57, userName='李六 last insertid', userAddress='北京', userSex='男', userBirthday=Fri Mar 06 19:11:40 CST 2020} User{userId=59, userName='李白 last insertid', userAddress='北京', userSex='男', userBirthday=Wed Mar 11 18:17:20 CST 2020} } /** * 测试查询用户信息 */ @Test public void findOneTest() { // 1. 执行查询单个用户方法 User user = userDao.findById(53); System.out.println(user); // 运行结果 User{userId=53, userName='啸哥', userAddress='北京四合院', userSex='男', userBirthday=Fri Mar 06 13:10:42 CST 2020} } /** * 测试模糊查询操作 ---> 由于mybatis不能用%号 只能在调用的时候给%号 */ @Test public void findByUsernameTest() { // 1. 执行模糊查询方法 List<User> users = userDao.findByUsername("%王%"); for (User user : users) { System.out.println(user); } // 运行结果 User{userId=41, userName='老王', userAddress='北京', userSex='男', userBirthday=Wed Feb 28 07:47:08 CST 2018} User{userId=42, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Sat Mar 03 05:09:37 CST 2018} User{userId=43, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Mon Mar 05 01:34:34 CST 2018} User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Thu Mar 08 07:37:26 CST 2018} User{userId=55, userName='王五 last insertid', userAddress='北京', userSex='男', userBirthday=Fri Mar 06 15:17:43 CST 2020} } /** * 测试使用QueryVo作为查询条件 */ @Test public void findByVoTest() { // 创建对象 User user = new User(); QueryVo vo = new QueryVo(); // 给user的username赋值 user.setUserName("%王%"); // 把user度夏宁封装进QueryVo中 vo.setUser(user); // 调用方法 List<User> users = userDao.findByVo(vo); for (User u : users) { System.out.println(u); } // 运行结果 User{userId=41, userName='老王', userAddress='北京', userSex='男', userBirthday=Wed Feb 28 07:47:08 CST 2018} User{userId=42, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Sat Mar 03 05:09:37 CST 2018} User{userId=43, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Mon Mar 05 01:34:34 CST 2018} User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Thu Mar 08 07:37:26 CST 2018} User{userId=55, userName='王五 last insertid', userAddress='北京', userSex='男', userBirthday=Fri Mar 06 15:17:43 CST 2020} } /** * 测试动态sql条件查询 */ @Test public void findUserByConditionTest() { User user = new User(); user.setUserName("老王"); user.setUserSex("女"); List<User> list = userDao.findUserByCondition(user); for (User u : list) { System.out.println(u); } // 运行结果 User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Thu Mar 08 07:37:26 CST 2018} } /** * 测试foreach标签的使用 * @return */ @Test public void findInIdsTest() { QueryVo vo = new QueryVo(); List<Integer> list = new ArrayList<Integer>(); list.add(41); list.add(42); list.add(43); list.add(46); vo.setIds(list); List<User> users = userDao.findUserInIds(vo); for (User user : users) { System.out.println(user); } // 运行结果 User{userId=41, userName='老王', userAddress='北京', userSex='男', userBirthday=Wed Feb 28 07:47:08 CST 2018} User{userId=42, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Sat Mar 03 05:09:37 CST 2018} User{userId=43, userName='小二王', userAddress='北京金燕龙', userSex='女', userBirthday=Mon Mar 05 01:34:34 CST 2018} User{userId=46, userName='老王', userAddress='北京', userSex='女', userBirthday=Thu Mar 08 07:37:26 CST 2018} } }