1.导入坐标
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.7.5</version>
</dependency>
<dependency>
<!-- 解析器-->
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
2.创建数据表
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
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2018-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2018-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2018-12-12', '5000', '2');
-- ----------------------------
-- 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
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'COO', 'COO');
-- ----------------------------
-- 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 `sys_role` (`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
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');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`birthday` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'lucy', '123', '2018-12-12');
INSERT INTO `user` VALUES ('2', 'haohao', '123', '2019-12-12');
3.创建实体类
public class Order {
private int id;
private Date ordertime;
private double total;
//当前订单属于哪一个用户
private 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 +
'}';
}
}
public class Role {
private int id;
private String roleName;
private String 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 + '\'' +
'}';
}
}
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//描述的是当前用户存在哪些订单
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
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;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", roleList=" + roleList +
'}';
}
}
4.创建UserMapper接口
package com.hao.mapper;
import com.hao.domain.User;
import java.util.List;
public interface UserMapper {
public List<User> findAll();
public List<User> findUserAndRoleAll();
}
5.创建UserMapper.xml接口
public interface OrderMapper {
//查询全部的方法
public List<Order> findAll();
}
6.配置映射文件OrderMapper.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="com.hao.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<!--手动指定字段与实体属性的映射关系
column: 数据表的字段名称
property:实体的属性名称
-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<!--<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>-->
<!--
property: 当前实体(order)中的属性名称(private User user)
javaType: 当前实体(order)中的属性的类型(User)
-->
<association property="user" javaType="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id
</select>
</mapper>
7.配置映射文件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="com.hao.mapper.UserMapper">
<resultMap id="userMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--配置集合信息
property:集合名称
ofType:当前集合中的数据类型
-->
<collection property="orderList" ofType="order">
<!--封装order的数据-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
</select>
<!--#############################################################################-->
<resultMap id="userRoleMap" type="user">
<!--user的信息-->
<id column="userId" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--user内部的roleList信息-->
<collection property="roleList" ofType="role">
<id column="roleId" property="id"></id>
<result column="roleName" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserAndRoleAll" resultMap="userRoleMap">
SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
</select>
</mapper>
8.配置核心配置文件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>
<!-- 加载jdbc.properties配置文件-->
<properties resource="jdbc.properties"/>
<!-- 定义别名-->
<typeAliases>
<typeAlias type="com.hao.domain.User" alias="user"/>
<typeAlias type="com.hao.domain.Order" alias="order"/>
<typeAlias type="com.hao.domain.Role" alias="role"></typeAlias>
</typeAliases>
<!-- 注册类型处理器-->
<!-- <typeHandlers>-->
<!-- <typeHandler handler="com.hao.handler.DateTypeHandler"/>-->
<!-- </typeHandlers>-->
<!-- 配置分页助手插件-->
<!-- <plugins>-->
<!-- <plugin interceptor="com.github.pagehelper.PageHelper">-->
<!-- <property name="dialect" value="mysql"/>-->
<!-- </plugin>-->
<!-- </plugins>-->
<!-- 配置数据源环境-->
<environments default="development"><!-- 表示默认情况下使用id为development的环境-->
<environment id="development">
<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="com/hao/mapper/UserMapper.xml"/>
<mapper resource="com/hao/mapper/OrderMapper.xml"/>
</mappers>
</configuration>
9.测试
public class MapperTest {
@Test
public void test1() throws IOException {
InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSession sqlSession = new SqlSessionFactoryBuilder().build(stream).openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> list = mapper.findAll();
System.out.println(list);
}
}
加粗样式