**上一篇文章:**Java之MyBatis【IDEA版】(一篇文章精通系列)增删改查【XML开发】 - 所有知识点(大全)
这几年来注解开发越来越流行,Mybatis也可以使用注解开发方式,
这样我们就可以减少编写Mapper映射文件了。
我们先围绕一些基本的CRUD来学习,再学习复杂映射多表操作。
@Insert:实现新增
@Update:实现更新
@Delete:实现删除
@Select:实现查询
@Result:实现结果集封装
@Results:可以与@Result 一起使用,封装多个结果集
@One:实现一对一结果集封装
@Many:实现一对多结果集封装
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `order`
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` int(11) NOT NULL,
`ordertime` bigint(60) NOT NULL,
`total` double(60,0) NOT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('1', '1631783536660', '1', '1');
INSERT INTO `order` VALUES ('2', '1631783536660', '2', '1');
INSERT INTO `order` VALUES ('3', '1631783536660', '1', '2');
-- ----------------------------
-- Table structure for `role`
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`roleName` varchar(50) NOT NULL,
`roleDesc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '老师', '教学');
INSERT INTO `role` VALUES ('2', '学生', '上学');
INSERT INTO `role` VALUES ('3', '班主任', '管理班级事务');
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`birthday` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'ssss', '123', '1631947408458');
INSERT INTO `user` VALUES ('2', 'eeee', 'sasass', '1631783536660');
INSERT INTO `user` VALUES ('3', '3sasas', 'rerere', '1631783536660');
INSERT INTO `user` VALUES ('4', 'gffdfdf', 'ddwdwd', '1631783536660');
INSERT INTO `user` VALUES ('5', 'sasasa', 'sasa', '1631783536660');
INSERT INTO `user` VALUES ('6', 'ceshi', 'abc', '1631783536660');
INSERT INTO `user` VALUES ('7', '测试数据', '123', '1631945928626');
-- ----------------------------
-- Table structure for `user_role`
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES ('1', '1', '1');
INSERT INTO `user_role` VALUES ('2', '1', '2');
INSERT INTO `user_role` VALUES ('3', '2', '2');
INSERT INTO `user_role` VALUES ('4', '3', '3');
<?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>cn.itbluebox</groupId>
<artifactId>mybatis</artifactId>
<version>1.0.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.32</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test2
jdbc.username=root
jdbc.password=root
### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### set log levels - for more verbose logging change 'info' to 'debug' ###
log4j.rootLogger=debug, stdout
<?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文件-->
<properties resource="jdbc.properties"></properties>
<!--自定义别名-->
<typeAliases>
<typeAlias type="cn.itbluebox.domain.User" alias="user"></typeAlias>
</typeAliases>
<!--数据源环境-->
<environments default="developement">
<environment id="developement">
<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="cn.itbluebox.mapper"></package>
</mappers>
</configuration>
package cn.itbluebox.domain;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
public Order() {
}
public Order(int id, Date ordertime, double total) {
this.id = id;
this.ordertime = ordertime;
this.total = total;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
'}';
}
}
package cn.itbluebox.domain;
public class Role {
private int id;
private String roleName;
private String roleDesc;
public Role() {
}
public Role(int id, String roleName, String roleDesc) {
this.id = id;
this.roleName = roleName;
this.roleDesc = roleDesc;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
package cn.itbluebox.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
private List<Role> roleList;
public User() {
}
public User(int id, String username, String password, Date birthday, List<Role> roleList) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
this.roleList = roleList;
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", roleList=" + roleList +
'}';
}
}
package cn.itbluebox.domain;
import java.util.Date;
public class Order {
private int id;
private Date ordertime;
private double total;
//当前订单属于哪一个用户
private User user;
public Order() {
}
public Order(int id, Date ordertime, double total, User user) {
this.id = id;
this.ordertime = ordertime;
this.total = total;
this.user = user;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
", user=" + user +
'}';
}
}
因为数据库当中的时间使用的是long类型的数据,如果您使用的datetime则不需要
package cn.itbluebox.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class MyDateTypeHandler extends BaseTypeHandler<Date> {
//将Java 类型转换为数据库需要的类型
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
long time = date.getTime();
preparedStatement.setLong(i,time);
}
//将数据中的类型 转换为java类型
//String 类型 要转换的字段名称
//ResultSet 查询出的结果集
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
//获得结果集中需要的数据(long)转换为Date
long aLong = resultSet.getLong(s);
Date date = new Date(aLong);
return date;
}
//将数据库当中的类型 转换为Java的类型
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
long aLong = resultSet.getLong(i);
Date date = new Date(aLong);
return date;
}
//将数据库中的类型 转换为Java类型
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
long aLong = callableStatement.getLong(i);
Date date = new Date(aLong);
return date;
}
}
<!--注册类型处理器-->
<typeHandlers>
<typeHandler handler="cn.itbluebox.handler.MyDateTypeHandler"></typeHandler>
</typeHandlers>
在上面的配置文件当中以及配置好了,在这里只做简单的说明
实现添加User的代码
package cn.itbluebox.mapper;
import cn.itbluebox.domain.User;
import org.apache.ibatis.annotations.Insert;
public interface UserMapper {
@Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
public void save(User user);
}
package cn.itbluebox;
import cn.itbluebox.domain.User;
import cn.itbluebox.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.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
public class MyBatisTestUser {
/* @Before方法生成一个对象,然后在@Test里调用。 */
private UserMapper usermapper;
@Before
public void before() throws IOException{
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
usermapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void testAdd(){
User user = new User();
user.setUsername("测试数据");
user.setPassword("123");
user.setBirthday(new Date());
usermapper.save(user);
}
}
运行测试
插入成功
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Test
public void testUpdate() throws IOException{
User user = new User();
user.setId(1);
user.setUsername("张三改");
user.setPassword("321");
usermapper.update(user);
}
运行测试
修改成功
@Delete("delete from user where id = #{id}")
public void delete(int id);
@Test
public void testDelete() throws IOException{
usermapper.delete(1);
}
运行测试
@Test
public void testAdd(){
User user = new User();
user.setId(1);
user.setUsername("莎莎");
user.setPassword("123");
user.setBirthday(new Date());
usermapper.save(user);
}
运行测试
@Select("select * from user where id = #{id}")
public User findById(int id);
@Test
public void testFindById() throws IOException{
User user = usermapper.findById(1);
System.out.println(user);
}
运行测试
@Select("select * from user")
public List<User> findAll();
@Test
public void testFindAll() throws IOException{
List<User> users = usermapper.findAll();
for (User user : users) {
System.out.println(user);
}
}
运行测试
用户表和订单表的关系为,一个用户有多个订单,
一个订单只从属于一个用户
一对一查询的需求:
查询一个订单,与此同时查询出该订单所属的用户
对应的sql语句:
select * from user u,`order` o where u.id = o.uid
在这里使用的是Order当中的uid字段所以我们需要修改Order表添加uid字段
设置一些数据
user_order中间表暂时不用
使用注解配置Mapper,创建findAll方法
package cn.itbluebox.mapper;
import cn.itbluebox.domain.Order;
import cn.itbluebox.domain.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface OrderMapper {
@Select("select * from `order`")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "ordertime", column = "ordertime"),
@Result(property = "total",column = "total"),
@Result(property = "user",
column = "uid" ,
javaType = User.class,
one = @One(select = "cn.itbluebox.mapper.UserMapper.findById"))
})
public List<Order> findAll();
}
package cn.itbluebox;
import cn.itbluebox.domain.Order;
import cn.itbluebox.mapper.OrderMapper;
import cn.itbluebox.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.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTestOrder {
/* @Before方法生成一个对象,然后在@Test里调用。 */
private OrderMapper orderMapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
orderMapper = sqlSession.getMapper(OrderMapper.class);
}
@Test
public void testSelectOrderAndUser(){
List<Order> orders = orderMapper.findAll();
for (Order order : orders) {
System.out.println(order);
}
}
}
运行测试
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单
对应的sql语句:
select /* from user;
select /* from order
where uid=查询出用户的id;
查询的结果如下:
package cn.itbluebox.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
private List<Role> roleList;
public User() {
}
public User(int id, String username, String password, Date birthday, List<Order> orderList, List<Role> roleList) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
this.orderList = orderList;
this.roleList = roleList;
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
}
@Select("select * from `order` where uid= # {uid}")
List<Order> findByUid(int uid);
@Select("select * from user")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "password",column = "password"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "orderList",column = "id" ,
javaType = List.class,
many = @Many(select = "cn.itbluebox.mapper.OrderMapper.findByUid"))
})
List<User> findAllUserAndOrder();
@Test
public void testFindAllUserAndOrder() throws IOException{
List<User> allUserAndOrder = usermapper.findAllUserAndOrder();
for (User user : allUserAndOrder) {
System.out.println(user.getUsername());
List<Order> orderList = user.getOrderList();
for (Order order : orderList) {
System.out.println(order);
}
System.out.println("------------------");
}
}
运行测试
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求:查询用户同时查询出该用户的所有角色
对应的sql语句:
select * from user;
select * from role r,user_role ur
where r.id=ur.role_id and ur.user_id=用户的id
查询的结果如下:
package cn.itbluebox.mapper;
import cn.itbluebox.domain.Role;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface RoleMapper {
@Select("select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=#{uid}")
List<Role> findByUid(int id);
}
@Select("select * from user")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "password",column = "password"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "roleList",column = "id",
javaType = List.class,
many = @Many(select = "cn.itbluebox.mapper.RoleMapper.findByUid")
),
})
List<User> findAllUserAndRole();
@Test
public void testFindAllUserAndRole() throws IOException{
List<User> allUserAndRole = usermapper.findAllUserAndRole();
for (User user : allUserAndRole) {
System.out.println(user.getUsername());
List<Role> roleList = user.getRoleList();
for (Role role : roleList) {
System.out.println(role);
}
System.out.println("-----------------");
}
}
运行测试
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/qq_44757034/article/details/120353728
内容来源于网络,如有侵权,请联系作者删除!