目录
1.注解开发项目整体框架(与xml形式开发 在resource里面 没了xxxmapper.xml)
6.数据库信息(创建并使用数据库 然后先创建user表 在创建其他的)
(1)一对一查询,查询一个订单,与此同时查询出该订单所属的用户
(2)一对多查询,查询一个用户,与此同时查询出该用户具有的订单
(3)多对多查询 ,查询所有用户,同时查询出该用户的所有角色
1.注解开发项目整体框架(与xml形式开发 在resource里面 没了xxxmapper.xml)
* @Insert:实现新增,代替了<insert></insert>
* @Delete:实现删除,代替了<delete></delete>
* @Update:实现更新,代替了<update></update>
* @Select:实现查询,代替了<select></select>
* @Result:实现结果集封装,代替了<result></result>
* @Results:可以与@Result 一起使用,封装多个结果集,代替了<resultMap></resultMap>
* @One:实现一对一结果集封装,代替了<association></association>
* @Many:实现一对多结果集封装,代替了<collection></collection>
2.配置资源
(1)jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&useSSL=true
jdbc.username=root
jdbc.password=root
(2)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>
<settings>
<!-- 控制台 打印sql -->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- 别名配置-->
<typeAliases>
<typeAlias type="com.qiku.pojo.User" alias="user"></typeAlias>
<typeAlias type="com.qiku.pojo.Order" alias="order"></typeAlias>
<typeAlias type="com.qiku.pojo.Role" alias="role"></typeAlias>
</typeAliases>
<!-- 配置数据库环境 default 可以自定义-->
<environments default="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>
<!-- 核心配置文件加载OrderMapper.xml-->
<mappers>
<!-- resource 属性实现-->
<!-- <mapper resource="com/qiku/mapper/UserMapper.xml"></mapper>-->
<!-- <mapper resource="com/qiku/mapper/OrderMapper.xml"></mapper>-->
<!-- <mapper resource="com/qiku/mapper/RoleMapper.xml"></mapper>-->
<!-- //用包名-->
<package name="com.qiku.mapper"/>
</mappers>
</configuration>
3.实体类
(1)User
package com.qiku.pojo;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/**
* 2022/6/16 20:46
*
* @author yrc
* @version MybatisTest
*/
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public User(String username, Date birthday, String sex, String address) {
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
// 一对多
private List<Order> orderList;
private List<Role> roleList;
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
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;
}
public User() {
}
public User(Integer id, String username, Date birthday, String sex, String address, List<Order> orderList) {
this.id = id;
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", orderList=" + orderList +
", roleList=" + roleList +
'}';
}
}
(2)Order
package com.qiku.pojo;
import java.io.Serializable;
import java.util.Date;
/**
* 2022/6/21 15:54
*
* @author yrc
* @version Mybatis
*/
public class Order implements Serializable {
private Integer id;
private Date orderTime;
private double money;
// 表示当前订单属于哪个用户
private User user;
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderTime=" + orderTime +
", money=" + money +
", user=" + user +
'}';
}
public Order() {
}
public Order(Integer id, Date orderTime, double money, User user) {
this.id = id;
this.orderTime = orderTime;
this.money = money;
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Date getOrderTime() {
return orderTime;
}
public void setOrderTime(Date orderTime) {
this.orderTime = orderTime;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
(3)Role
package com.qiku.pojo;
/**
* 2022/6/22 9:38
*
* @author yrc
* @version Mybatis
*/
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
public Role() {
}
public Role(Integer id, String roleName, String roleDesc) {
this.id = id;
this.roleName = roleName;
this.roleDesc = roleDesc;
}
}
4.SqlSessionutils 工具类
12.SqlSession 的工具类 获取sqlsession_羊肉串学爪哇的博客-CSDN博客
5.mapper 接口
(1)UserMapper
package com.qiku.mapper;
import com.qiku.pojo.Order;
import com.qiku.pojo.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import java.util.List;
/**
* 2022/6/22 10:35
*
* @author yrc
* @version Mybatis
*/
@CacheNamespace
public interface UserMapper {
@Insert("insert into user(username,birthday,sex,address) " +
"values(#{username},#{birthday},#{sex},#{address})")
public void addUser(User user);
@Delete("delete from user where id=#{id}")
public void deleteUser(Integer id);
@Update("update user set username=#{username} where id=#{id}")
public void updateUser(User user);
@Select("select * from user")
public List<User> selectUser();
@Select("select * from user where id=#{id}")
public User findUserById(Integer id);
@Select("select * from user")
@Results({
@Result(id=true ,column = "id",property ="id"),
@Result(column = "username" ,property="username"),
@Result(column = "birthday" ,property="birthday"),
@Result(column = "sex" ,property="sex"),
@Result(column = "address" ,property="address"),
@Result(column = "id",property ="orderList",javaType = List.class,
many = @Many(select = "com.qiku.mapper.OrderMapper.findOrderByUid",
fetchType = FetchType.EAGER))
})
//一对多
public List<User> findAllUserWithOrder();
//查询所有用户以及角色信息
@Select("select * from user")
@Results({
@Result(id=true ,column = "id",property ="id"),
@Result(column = "username" ,property="username"),
@Result(column = "birthday" ,property="birthday"),
@Result(column = "sex" ,property="sex"),
@Result(column = "address" ,property="address"),
@Result(column = "id",property ="roleList",javaType = List.class,
many = @Many(select = "com.qiku.mapper.RoleMapper.findRoleByUid",
fetchType = FetchType.EAGER))
})
public List<User> findAllUserWithRole();
}
(2)OrderMapper
package com.qiku.mapper;
import com.qiku.pojo.Order;
import com.qiku.pojo.User;
import net.sf.jsqlparser.schema.Column;
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 org.apache.ibatis.mapping.FetchType;
import java.util.List;
/**
* 2022/6/22 11:10
*
* @author yrc
* @version Mybatis
*/
public interface OrderMapper {
// 查询所有订单以及订单的用户信息
@Select("select * from orders ")
@Results({
// 主键 数据库字段 实体类字段
@Result(id=true ,column = "id" ,property="id"),
// 数据库字段 实体类字段
@Result(column = "ordertime" ,property="orderTime"),
@Result(column = "total" ,property="money"),
@Result(column = "uid",property ="user",javaType = User.class,
one = @One(select = "com.qiku.mapper.UserMapper.findUserById",
fetchType = FetchType.EAGER)
)
})
public List<Order> findAllOrderWithUser();
@Select("select * from orders where id=#{uid}")
@Results({
@Result(id =true,column = "id" ,property = "id"),
@Result(column = "ordertime",property = "orderTime"),
@Result(column = "total",property = "money")
})
public List<Order> findOrderByUid(Integer uid);
}
(3)RoleMapper
package com.qiku.mapper;
import com.qiku.pojo.Role;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* 2022/6/22 15:18
*
* @author yrc
* @version Mybatis
*/
public interface RoleMapper {
@Select("select * from sys_role r LEFT JOIN sys_user_role ur\n" +
"on r.id = ur.roleid WHERE ur.userid = #{userId}")
public List<Role> findRoleByUid(Integer userId);
}
6.数据库信息(创建并使用数据库 然后先创建user表 在创建其他的)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` varchar(255) DEFAULT NULL,
`total` double DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
BEGIN;
INSERT INTO `orders` VALUES (1, '2022-12-12', 3000, 1);
INSERT INTO `orders` VALUES (2, '2022-12-12', 4000, 1);
INSERT INTO `orders` VALUES (3, '2022-12-12', 5000, 2);
COMMIT;
-- ----------------------------
-- Table structure for store
-- ----------------------------
DROP TABLE IF EXISTS `store`;
CREATE TABLE `store` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`shop_owner` varchar(32) DEFAULT NULL COMMENT '店主姓名',
`id_number` varchar(18) DEFAULT NULL COMMENT '身份证号',
`name` varchar(100) DEFAULT NULL COMMENT '店铺名称',
`industry` varchar(100) DEFAULT NULL COMMENT '行业分类',
`area` varchar(200) DEFAULT NULL COMMENT '店铺区域',
`phone` varchar(11) DEFAULT NULL COMMENT '手机号码',
`status` int(11) DEFAULT '0' COMMENT '审核状态。 0:待审核 1:审核通过 2:审核失败 3:重新审核 ',
`audit_time` datetime DEFAULT NULL COMMENT '审核时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of store
-- ----------------------------
BEGIN;
INSERT INTO `store` VALUES (1, '张三丰', '441322199309273014', '张三丰包子铺', '美食', '北京市海淀区', '18933283299', 0, '2017-12-08 12:35:30');
INSERT INTO `store` VALUES (2, '令狐冲', '441322199009102104', '华冲手机维修', '电子维修', '北京市昌平区', '18933283299', 1, '2019-01-20 20:20:00');
INSERT INTO `store` VALUES (3, '赵敏', '441322199610205317', '托尼美容美发', '美容美发', '北京市朝阳区', '18933283299', 2, '2020-08-08 10:00:30');
INSERT INTO `store` VALUES (5, '齐云霄', '1111', '云霄子阁', '阵法专卖', '元武国', '110', 0, '2022-06-20 10:30:29');
INSERT INTO `store` VALUES (8, '齐云霄', '1111', '云霄阁', '阵法专卖', '元武国', '110', 0, '2022-06-21 09:46:42');
COMMIT;
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rolename` varchar(255) DEFAULT NULL,
`roleDesc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
BEGIN;
INSERT INTO `sys_role` VALUES (1, 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES (2, 'CEO', 'CEO');
COMMIT;
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`userid` int(11) NOT NULL,
`roleid` int(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`),
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `sys_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
BEGIN;
INSERT INTO `sys_user_role` VALUES (1, 1);
INSERT INTO `sys_user_role` VALUES (2, 1);
INSERT INTO `sys_user_role` VALUES (1, 2);
INSERT INTO `sys_user_role` VALUES (2, 2);
COMMIT;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (1, '张三', '2020-11-11 00:00:00', '男', '北京海淀');
INSERT INTO `user` VALUES (2, '李四', '2020-12-12 00:00:00', '男', '北京海淀');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
7.测试
(1)一对一查询,查询一个订单,与此同时查询出该订单所属的用户
测试代码
@Test
public void testFindAllOrderWithUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> orderList = mapper.findAllOrderWithUser();
for(Order order:orderList){
System.out.println(order);
}
}
(2)一对多查询,查询一个用户,与此同时查询出该用户具有的订单
测试语句
@Test
public void findAllUserWithOrder(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allUserWithOrder = mapper.findAllUserWithOrder();
for(User user:allUserWithOrder){
System.out.println(user);
}
}
(3)多对多查询 ,查询所有用户,同时查询出该用户的所有角色
sql语句
SELECT * FROM `user`;
SELECT * FROM role r INNER JOIN user_role ur ON r.`id` = ur.`rid`
WHERE ur.`uid` = #{id};
测试语句
@Test
public void findAllUserWithRole(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allUserWithRole = mapper.findAllUserWithRole();
for(User user:allUserWithRole){
System.out.println(user);
}
}