一、需求
根据id 和username 查询 user 表
二、代码演示
1、方式1
-
数据库环境
CREATE DATABASE `mybatis_db`; USE `mybatis_db`; CREATE TABLE `user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `username` VARCHAR(32) NOT NULL COMMENT '用户名称', `birthday` DATETIME DEFAULT NULL COMMENT '生日', `sex` CHAR(1) DEFAULT NULL COMMENT '性别', `address` VARCHAR(256) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -- insert.... INSERT INTO USER(id,username,birthday,sex,address) VALUES (1,'tom','2024-01-01 00:00:00','男','北京'),(2,'jerry','2023-01-01 00:00:00','男','上海');
-
pom依赖
<?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>org.example</groupId> <artifactId>mybatis-dao</artifactId> <version>1.0-SNAPSHOT</version> <!--指定编码及版本--> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.encoding>UTF-8</maven.compiler.encoding> <java.version>1.11</java.version> <maven.compiler.source>1.11</maven.compiler.source> <maven.compiler.target>1.11</maven.compiler.target> </properties> <!--引入相关依赖--> <dependencies> <!--引入mybatis依赖--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.3</version> </dependency> <!--引入mysql驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <!--引入junit--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies> </project>
-
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 resource="jdbc.properties"></properties> <typeAliases> <!--方式一:给单个实体起别名--> <!-- <typeAlias type="domain.User" alias="user"></typeAlias>--> <!--方式二:批量起别名 别名就是类名,且不区分大小写--> <package name="domain"/> </typeAliases> <!--环境配置--> <environments default="mysql"> <!--使用mysql环境--> <environment id="mysql"> <!--使用jdbc事务管理亲--> <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> <mapper resource="mapper/UserMapper.xml"></mapper> </mappers> </configuration>
-
User实体类
package domain; import java.util.Date; public class User { private Integer id; private String username; private Date birthday; private String sex; private String address; @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } }
-
UserMapper.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="mapper.UserMapper">
<!--id : 标签的唯一标识
type: 封装后实体类型-->
<resultMap id="userResultMap" type="domain.User">
<!--手动配置映射关系-->
<!--id: 用来配置主键-->
<id property="id" column="id"></id>
<!-- result: 表中普通字段的封装-->
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</resultMap>
<!--多条件查询:方式1-->
<select id="findByIdAndUsername1" resultMap="userResultMap">
<!--两种方法-->
<!--select * from user where id = #{param1} and username = #{param2}-->
select * from user where id = #{arg0} and username = #{arg1}
</select>
</mapper>
-
UserMapper.java
package mapper; import domain.User; import java.util.List; public interface UserMapper { public User findByIdAndUsername1(int id, String username); }
-
测试类
package test; import domain.User; import mapper.UserMapper; 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.Test; import java.io.InputStream; import java.util.List; public class MybatisTest { @Test public void test1() throws Exception { InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); //获取Mapper代理对象 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername1(1, "tom"); System.out.println(user); sqlSession.close(); } }
-
测试结果
2、方式2-注解方式
注解方式主要关注传参和sql占位符的映射关系,如下图:
注意:resultMap是建立结果返回类型和表字段类型的关系映射,也就是你从数据库查到数据,映射到返回结果类型对象,而@Param是传参的映射,也就是你传入的参数和sql中#{}占位符位置内容的映射关系。
-
User
可以修改User实体类中的属性username为usernameabc,此时 实体类的属性 和 表中字段名不一致,需要重新定义resultMap的映射关系。
package domain; import java.util.Date; public class User { private Integer id; private String usernameabc; private Date birthday; private String sex; private String address; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getUsernameabc() { return usernameabc; } public void setUsernameabc(String usernameabc) { this.usernameabc = usernameabc; } @Override public String toString() { return "User{" + "id=" + id + ", usernameabc='" + usernameabc + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}'; } }
-
UserMapper.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="mapper.UserMapper"> <!--id : 标签的唯一标识 type: 封装后实体类型--> <resultMap id="userResultMap" type="domain.User"> <!--手动配置映射关系--> <!--id: 用来配置主键--> <id property="id" column="id"></id> <!-- result: 表中普通字段的封装--> <result property="usernameabc" column="username"></result> <result property="birthday" column="birthday"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> </resultMap> <!--多条件查询:方式1--> <select id="findByIdAndUsername1" resultMap="userResultMap"> <!--两种方法--> <!--select * from user where id = #{param1} and username = #{param2}--> select * from user where id = #{arg0} and username = #{arg1} </select> <!--多条件查询:方式2--> <select id="findByIdAndUsername2" resultMap="userResultMap"> select * from user where username = #{usernameabc} and id = #{id} </select> </mapper>
-
UserMapper.java
package mapper; import domain.User; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper { public User findByIdAndUsername1(int id, String username); public User findByIdAndUsername2(@Param("id") Integer id, @Param("usernameabc") String username); }
-
测试类
package test; import domain.User; import mapper.UserMapper; 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.Test; import java.io.InputStream; import java.util.List; public class MybatisTest { //多条件查询:方式1 @Test public void test1() throws Exception { InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); //获取Mapper代理对象 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername1(1, "tom"); System.out.println(user); sqlSession.close(); } //多条件查询:方式2 @Test public void test2() throws Exception{ InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername2(1, "tom"); System.out.println(user); sqlSession.close(); } }
-
测试结果
3、方式3-传入实体类 (推荐使用)
只要传入 实体类的成员属性名 和 sql占位符的传入名称 一致即可。
-
UserMapper.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="mapper.UserMapper"> <!--id : 标签的唯一标识 type: 封装后实体类型--> <resultMap id="userResultMap" type="domain.User"> <!--手动配置映射关系--> <!--id: 用来配置主键--> <id property="id" column="id"></id> <!-- result: 表中普通字段的封装--> <result property="usernameabc" column="username"></result> <result property="birthday" column="birthday"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> </resultMap> <!--多条件查询:方式1--> <select id="findByIdAndUsername1" resultMap="userResultMap"> <!--两种方法--> <!--select * from user where id = #{param1} and username = #{param2}--> select * from user where id = #{arg0} and username = #{arg1} </select> <!--多条件查询:方式2--> <select id="findByIdAndUsername2" resultMap="userResultMap"> select * from user where username = #{usernameabc} and id = #{id} </select> <!--多条件查询:方式3--> <select id="findByIdAndUsername3" parameterType="user" resultMap="userResultMap"> select * from user where id = #{id} and username = #{usernameabc} </select> </mapper>
注意:前两种方式都不需要编写parameterType,而这种方式需要。
-
UserMapper.java
package mapper; import domain.User; import org.apache.ibatis.annotations.Param; import java.util.List; public interface UserMapper { public User findByIdAndUsername1(int id, String username); public User findByIdAndUsername2(@Param("id") Integer id, @Param("usernameabc") String username); public User findByIdAndUsername3(User user); }
-
测试类
package test; import domain.User; import mapper.UserMapper; 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.Test; import java.io.InputStream; import java.util.List; public class MybatisTest { //多条件查询:方式1 @Test public void test1() throws Exception { InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); //获取Mapper代理对象 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername1(1, "tom"); System.out.println(user); sqlSession.close(); } //多条件查询:方式2 @Test public void test2() throws Exception{ InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findByIdAndUsername2(1, "tom"); System.out.println(user); sqlSession.close(); } //多条件查询:方式3 @Test public void test3() throws Exception{ InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setId(1); user.setUsernameabc("tom"); User user2 = mapper.findByIdAndUsername3(user); System.out.println(user2); sqlSession.close(); } }
-
测试结果