准备工作
① 创建数据库&数据表
| ## 创建数据库 |
| 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 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> |
| |
| <mappers> |
| |
| |
| |
| |
| |
| |
| <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> |
| |
| <dependency> |
| <groupId>org.mybatis</groupId> |
| <artifactId>mybatis</artifactId> |
| <version>3.5.7</version> |
| </dependency> |
| |
| <dependency> |
| <groupId>junit</groupId> |
| <artifactId>junit</artifactId> |
| <version>4.13.2</version> |
| <scope>test</scope> |
| </dependency> |
| |
| <dependency> |
| <groupId>mysql</groupId> |
| <artifactId>mysql-connector-java</artifactId> |
| <version>8.0.32</version> |
| </dependency> |
| |
| <dependency> |
| <groupId>log4j</groupId> |
| <artifactId>log4j</artifactId> |
| <version>1.2.17</version> |
| </dependency> |
| |
| <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> |
查询返回实体类对象的用户信息
查询返回一条数据使用实体类接收。
| |
| |
| |
| |
| |
| 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集合接收查询返回的多条数据。
| <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集合返回单条记录
| |
| |
| |
| |
| |
| 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<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(); |
| |
| <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=lisi@qq.com,username=李四}
2、可以将每条数据转换的map集合放到一个大的Map集合中,但是必须要通过@MapKey
注解将查询的某个字段的值作为大的Map集合的键。
@MapKey("id")
Map<String,Object> getAllUserMap();
相当于:Map<String,Map<String,Object>>
显示结果格式:
{
1001={password=123456, gender=男, id=1001, age=15, email=root@qq.com, username=root},
1002={password=123456, gender=女, id=1002, age=20, email=zh3@sina.cn, username=张三}
}
查询总记录数
| |
| |
| <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常用类型设置的类型别名


【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性