MyBatis查询功能演示
准备工作
① 创建数据库&数据表
## 创建数据库
CREATE DATABASE `dbtest1`;
## 创建数据表
CREATE TABLE `t_user` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) DEFAULT NULL,
`password` VARCHAR(20) DEFAULT NULL,
`age` INT DEFAULT NULL,
`gender` CHAR(1) DEFAULT NULL,
`email` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
② 创建实体类
public class User {
private int id;
private String username;
private String password;
private int age;
private String gender;
private String email;
public User() {
}
public User(int id, String username, String password, int age, String gender, String email) {
this.id = id;
this.username = username;
this.password = password;
this.age = age;
this.gender = gender;
this.email = email;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
", email='" + email + '\'' +
'}';
}
}
③ 创建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>
<!-- 引入外部properties文件,此后就可以在当前文件的数据源连接信息使用${}的方式访问value -->
<properties resource="jdbc.properties"/>
<!-- 设置类型别名 -->
<typeAliases>
<!--以包为单位，将包下所有的类型设置默认的类型别名，即类名且不区分大小写-->
<package name="com.evan.entity"/>
</typeAliases>
<!--配置多个连接数据库的环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<!-- 引入mybatis的映射文件 -->
<mappers>
<!--
以包为单位引入映射文件
要求：
1、mapper接口所在的包要和映射文件所在的包一致
2、mapper接口要和映射文件的名字一致
-->
<package name="com.evan.mapper"/>
</mappers>
</configuration>
④ 创建jdbc.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/dbtest1?serverTimezone=UTC
jdbc.username=root
jdbc.password=123456
⑤ 添加依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
</dependencies>
⑥ 创建SqlSessionUtil
public class SqlSessionUtil {
private static final Log logger = LogFactory.getLog(SqlSessionUtil.class);
public static SqlSession getSqlSession() {
SqlSession sqlSession = null;
try (InputStream is = Resources.getResourceAsStream("mybatis-config.xml");) {
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
sqlSession = sqlSessionFactory.openSession(true);
} catch (IOException e) {
logger.error(e);
}
return sqlSession;
}
}
⑦ 创建log4j.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS}%m (%F:%L) \n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="info" />
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
查询返回实体类对象的用户信息
查询返回一条数据使用实体类接收。
/**
* 根据id查询用户信息
* @param id
* @return
*/
User getUserById(@Param("id") Integer id);
<select id="getUserById" resultType="com.evan.entity.User">
select * from t_user where id = #{id}
</select>
@Test
public void test1() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
System.out.println(mapper.getUserById(17));
sqlSession.close();
}
查询返回List集合的所有用户信息
使用List集合接收查询返回的多条数据。
/**
* 查询所有用户信息
* @return
*/
List<User> getAllUser();
<select id="getAllUser" resultType="com.evan.entity.User">
select * from t_user
</select>
@Test
public void test2() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<User> users = mapper.getAllUser();
users.forEach(System.out::println);
sqlSession.close();
}
说明:
当查询的数据为多条时，不能使用实体类作为返回值，否则会抛出异常TooManyResultsException；
但是若查询的数据只有一条，可以使用实体类或集合作为返回值。
查询返回Map集合的所有用户信息
Map集合返回单条记录
/**
* 根据id查询用户信息(Map集合接收)
* @param id
* @return
*/
Map<String,Object> getUserByIdMap(@Param("id") Integer id);
<!--Map<String,Object> getUserByIdMap(@Param("id") Integer id);-->
<select id="getUserByIdMap" resultType="map">
select * from t_user where id = #{id}
</select>
@Test
public void test3() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> users = mapper.getUserByIdMap(1002);
System.out.println(users);
sqlSession.close();
}
Map集合返回多条记录
方式1：使用List集合接收Map查询的数据
/**
* 查询所有用户信息(list集合接收Map集合查询的所有数据)
* @return
*/
List<Map<String,Object>> getAllUserMap();
<!-- List<Map<String,Object>> getAllUserMap(); -->
<select id="getAllUserMap" resultType="map">
select * from t_user
</select>
@Test
public void test5() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<Map<String, Object> > userList = mapper.getAllUserMap();
userList.forEach(System.out::println);
sqlSession.close();
}
方式2：@MapKey注解
@MapKey("id")
Map<String,Object> getAllUserMap();
<!-- Map<String,Object> getAllUserMap(); -->
<select id="getAllUserMap" resultType="map">
select * from t_user
</select>
@Test
public void test5() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String, Object> userMap = mapper.getAllUserMap();
System.out.println(userMap);
sqlSession.close();
}
结论:
查询所有的用户信息为map集合时：
若查询的数据有多条时，并且要将每条数据转换为map集合，此时有两种解决方案：
1、将mapper接口方法的返回值设置为泛型是map的list集合
List<Map<String,Object>> getAllUserMap();
显示结果的格式(无序结果集)：
{password=123456,gender=男,id=1001,age=18,[email protected],username=李四}
2、可以将每条数据转换的map集合放到一个大的Map集合中，但是必须要通过@MapKey注解将查询的某个字段的值作为大的Map集合的键。
@MapKey("id")
Map<String,Object> getAllUserMap();
相当于：Map<Object,Map<String,Object>>
显示结果格式：
{
1001={password=123456, gender=男, id=1001, age=15, [email protected], username=root},
1002={password=123456, gender=女, id=1002, age=20, [email protected], username=张三}
}
查询总记录数
/**
* 查询用户总记录数
* @return
*/
Integer getCount();
<!--int getCount();-->
<!-- 在MyBatis中，默认对于Java中常用的类型都设置了类型别名 -->
<select id="getCount" resultType="integer">
select count(1) from t_user
</select>
@Test
public void test4() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Integer count = mapper.getCount();
System.out.println(count);
sqlSession.close();
}
MyBatis对Java常用类型设置的类型别名
热门相关：铁血大明 懒散初唐 唐朝小官人 甜蜜隐婚：老公大人，宠上瘾 唐朝小官人