#问题: 1(100分)有一个在线交易的电商平台,主要包括三张数据库业务表:
现在需要基于MyBatis数据库ORM框架,实现读取商品信息和用户信息两个功能。
商品对象和用户对象定义如下:
package com.hava.eb.entity;
/**
* Created by yanfa on 2016/10/9.
*/
public class Product {
public int id;
public String productName;
public String catalog;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public String getCatalog() {
return catalog;
}
public void setCatalog(String catalog) {
this.catalog = catalog;
}
}
User
package com.hava.eb.entity;
import java.util.List;
/**
* Created by yanfa on 2016/10/9.
*/
public class User {
public int id;
public String userName;
public String tel;
public List<Integer> products;
// Getter and Setter
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 getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public List<Integer> getProducts() {
return products;
}
public void setProducts(List<Integer> products) {
this.products = products;
}
}
每个用户都关联了一个Product的List,用于表示该用户所购买的所有商品,可以通过查询transaction表的交易记录获得。
操作接口定义如下:
package com.hava.eb.repository;
import com.hava.eb.entity.Product;
import com.hava.eb.entity.User;
/**
* Created by yanfa on 2016/10/9.
*/
public interface Op {
public User getUser(int id);
public Product getProduct(int id);
}
请分别实现getProduct和getUser两个函数方法与SQL语句的映射配置文件。
模板如下:
<?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="MyBatisTest.Op">
<select >
</select>
</mapper>
#回答: 由于User并没有构造函数,则在映射对象时并不使用构造函数,而是使用result,通过属性的getset方法进行注入 ##Op的映射配置
<?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.hava.eb.repository.Op">
<select id="getUser" parameterType="int" resultMap="opMap">
select User.ID,User.UserName,User.Tel,transaction.ProductId
from User
left join transaction on User.ID=transaction.UserId
where User.ID = #{id}
</select>
<select id="getProduct" parameterType="int" resultType="com.hava.eb.entity.Product">
select ID,ProductName,Catalog from Product where id = #{id}
</select>
<resultMap id="opMap" type="com.hava.eb.entity.User">
<!--<constructor>-->
<!--<idArg column="id" javaType="int" />-->
<!--<arg column="userName" javaType="String" />-->
<!--<arg column="tel" javaType="String" />-->
<!--</constructor>-->
<result property="id" column="ID" />
<result property="userName" column="UserName" />
<result property="tel" column="Tel" />
<collection property="products" ofType="java.lang.Integer">
<id property="id" column="productId" />
</collection>
</resultMap>
</mapper>
##主函数查询
package com.hava.eb.service;
import com.hava.eb.entity.Product;
import com.hava.eb.entity.User;
import com.hava.eb.repository.Op;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
/**
* Created by yanfa on 2016/10/9.
*/
public class SelectMyBatis {
public static void main(String[] args) {
// 1. 声明配置⽂件的⺫录渎职
String resource = "mybatis-conf.xml";
// 2. 加载应⽤配置⽂件
InputStream is = SelectMyBatis.class.getClassLoader()
.getResourceAsStream(resource);
// 3. 创建SqlSessonFactory
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder()
.build(is);
// 4. 获取Session
SqlSession session = sessionFactory.openSession();
try {
// 5. 获取操作类
Op op = session.getMapper(Op.class);
// 6. 完成查询操作
// get product
Product product = op.getProduct(1);
System.out.println("[product.id]:" + product.getId() + " [product.productName]:" + product.getProductName() + " [product.catalog]:" + product.getCatalog());
// get user
User user = op.getUser(1);
System.out.println("[user.id]:" + user.getId() + " [user.userName]:" + user.getUserName() + " [user.tel]:" + user.getTel());
for(int product_id : user.getProducts())
System.out.println(" [product_id]:" + product_id);
} finally {
// 7.关闭Session
session.close();
}
}
}
##查询结果输出
[product.id]:1 [product.productName]:iPhone [product.catalog]:Phone
[user.id]:1 [user.userName]:ZhanPeng [user.tel]:12345678
[product_id]:1
[product_id]:3
[product_id]:2
[product_id]:4
##数据库初始化SQL
/*
Navicat MySQL Data Transfer
Source Server : 192.168.1.200_3306
Source Server Version : 50715
Source Host : 192.168.1.200:3306
Source Database : eb
Target Server Type : MYSQL
Target Server Version : 50715
File Encoding : 65001
Date: 2016-10-09 21:18:45
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for Product
-- ----------------------------
DROP TABLE IF EXISTS `Product`;
CREATE TABLE `Product` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ProductName` varchar(100) NOT NULL,
`Catalog` varchar(100) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of Product
-- ----------------------------
INSERT INTO `Product` VALUES ('1', 'iPhone', 'Phone');
INSERT INTO `Product` VALUES ('2', 'iMac', 'PC');
INSERT INTO `Product` VALUES ('3', 'MacBook', 'PC');
INSERT INTO `Product` VALUES ('4', 'iPad', 'Pad');
-- ----------------------------
-- Table structure for transaction
-- ----------------------------
DROP TABLE IF EXISTS `transaction`;
CREATE TABLE `transaction` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UserId` int(11) DEFAULT NULL,
`ProductId` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of transaction
-- ----------------------------
INSERT INTO `transaction` VALUES ('1', '1', '1');
INSERT INTO `transaction` VALUES ('2', '1', '3');
INSERT INTO `transaction` VALUES ('3', '1', '2');
INSERT INTO `transaction` VALUES ('4', '1', '4');
-- ----------------------------
-- Table structure for User
-- ----------------------------
DROP TABLE IF EXISTS `User`;
CREATE TABLE `User` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UserName` varchar(100) DEFAULT NULL,
`Tel` varchar(100) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of User
-- ----------------------------
INSERT INTO `User` VALUES ('1', 'ZhanPeng', '12345678');
##最终运行状态结果