环境的搭建:
表的创建:
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` INT(11) NOT NULL COMMENT '下单用户id',
`number` VARCHAR(32) NOT NULL COMMENT '订单号',
`createtime` DATETIME NOT NULL COMMENT '创建订单时间',
`note` VARCHAR(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`userId`),
CONSTRAINT `FK_orders_id` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', NULL);
INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', NULL);
INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', NULL);
-- ----------------------------
-- 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` DATE DEFAULT NULL COMMENT '生日',
`sex` CHAR(1) DEFAULT NULL COMMENT '性别',
`address` VARCHAR(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', NULL, '2', NULL);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', NULL, NULL, NULL);
POJO的创建:
User:
public class User {
private int id;
private String username;
private String sex;
private Date birthday;
private String address;
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public User() {
super();
}
public User(String username, String sex, Date date,
String address) {
super();
this.username = username;
this.sex = sex;
this.birthday = date;
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
Orders:
public class Orders implements Serializable{
@Override
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + "]";
}
private static final long serialVersionUID = 1L;
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
}
一对一 |
应用场景:首先根据订单 ID 读取一个订单信息(orders),然后再读取这个订单所对应的用户(user)信息。
方法一:使用resultType
使用resultType的话,有个前提就是查询结果要想映射到pojo中去,pojo中必须包括所有查询出的列名才行。这里不仅查询出order表中的所有字段,而且还查询出了用户表中的部分字段,所以我们要自己新建一个pojo来包含所有的这些查询出来的字段才行。
创建pojo的原则是继承包括查询字段较多的po类。
public class OrdersUser extends Orders {
private static final long serialVersionUID = 1L;
//我只显示user的名字和地址
private String username;
private String address;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "OrdersUser ["+super.toString() +"username=" + username + ", address=" + address
+ "]";
}
}
在UserMapper.xml中写sql:
<select id="queryOrderUserById" parameterType="int" resultType="cn.xpu.hcp.bean.OrdersUser">
SELECT
o.*,
u.username,
u.address
FROM
orders o
LEFT JOIN USER u ON o.userId = u.id
WHERE o.id=#{id}
</select>
测试:
public void TestQueryOrderUserById(){
SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
OrdersUser ordersUser = mapper.queryOrderUserById(3);
System.out.println(ordersUser);
session.commit();
session.close();
}
方法二:使用resultMap
在orders类中加入user对象:
public class Orders implements Serializable{
......
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
......
}
使用resultMap:
<resultMap type="orders" id="orderUserResultMap">
<id column="id" property="id"/>
<result column="userId" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!--
association标签用于配置一对一
property:orders中user对象的属性名
javaType:user的类型
-->
<association property="user" javaType="user">
<!-- id:声明主键,column:所对应的外键字段名称 -->
<!--不能对应自己表里的id,否则得不到id值,因为SQL语句中没有查id-->
<!--如果SQL语句中查询了id则可以使用自己表里的id-->
<id property="id" column="userId"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="sex" column="sex"/>
<result property="birthday" column="birthday"/>
</association>
</resultMap>
<select id="queryOrderUserById" resultMap="orderUserResultMap" parameterType="int">
SELECT
o.*,
u.*
FROM
orders o
LEFT JOIN USER u ON o.userId = u.id
WHERE o.id=#{id}
</select>
注意:与前面输出映射所介绍resultMap不同的是在这里所有的result都要配置上,不配值的则显示为null。
测试:
public void TestQueryOrderUserById(){
SqlSession session = sqlSessionFactory.openSession();
OrdersMapper mapper = session.getMapper(OrdersMapper.class);
Orders orders= mapper.queryOrderUserById(5);
System.out.println(orders);
session.commit();
session.close();
}