学习mybatis时，不用和spring集成，
不用注解，可以了解得多一些，
在这之上，使用注解和spring集成，
乃至到spring boot，
其间的传递路径才不会迷路。
照旧，上差不多全部代码，
又不是写书，不怕篇幅长，
方便自己日后回忆。
数据库语句：
CREATE TABLE tb_user (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
USERNAME VARCHAR(18),
LOGINNAME VARCHAR(18),
PASSWORD VARCHAR(18),
PHONE VARCHAR(18),
ADDRESS VARCHAR(18)
);
INSERT INTO tb_user(username, loginname, password, phone, address)
VALUES ('jack', 'jack', '123456', '16534231234', 'guangzhou');
INSERT INTO tb_user(username, loginname, password, phone, address)
VALUES ('sky', 'chen', '12345678', '16512231234', 'shanghai');
INSERT INTO tb_user(username, loginname, password, phone, address)
VALUES ('tom', 'gang', '123678', '13512231234', 'beijing');
CREATE TABLE tb_article(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18),
PRICE DOUBLE,
REMARK VARCHAR(18)
);
INSERT INTO tb_article(NAME,PRICE,REMARK)
VALUES('Java', 108.9, 'ligang');
INSERT INTO tb_article(NAME,PRICE,REMARK)
VALUES('Android', 99.9, 'ligang');
INSERT INTO tb_article(NAME,PRICE,REMARK)
VALUES('ios', 89.4, 'ligang');
INSERT INTO tb_article(NAME,PRICE,REMARK)
VALUES('SpringMVC', 108.9, 'xiaowenjie');
INSERT INTO tb_article(NAME,PRICE,REMARK)
VALUES('Python', 68, 'chengang');
CREATE TABLE tb_order(
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(32),
TOTAL DOUBLE,
USER_ID INT,
FOREIGN KEY (USER_ID) REFERENCES tb_user(ID)
);
INSERT INTO tb_order(CODE, TOTAL, USER_ID)
VALUES('62345ASDADFA34SASDF', 388.6, 1);
INSERT INTO tb_order(CODE, TOTAL, USER_ID)
VALUES('2342SD234SASDF', 321.6, 1);
INSERT INTO tb_order(CODE, TOTAL, USER_ID)
VALUES('2W4WEADF4SASDF', 588.6, 2);
INSERT INTO tb_order(CODE, TOTAL, USER_ID)
VALUES('ZADWR234SASDF', 31.6, 2);
CREATE TABLE tb_item(
ORDER_ID INT,
ARTICLE_ID INT,
AMOUNT INT,
PRIMARY KEY(ORDER_ID, ARTICLE_ID),
FOREIGN KEY (ORDER_ID) REFERENCES tb_order(ID),
FOREIGN KEY (ARTICLE_ID) REFERENCES tb_article(ID)
);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(1, 1, 1);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(1, 2, 1);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(1, 3, 2);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(2, 4, 2);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(2, 1, 1);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(3, 3, 2);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(4, 4, 2);
INSERT INTO tb_item(order_id, article_id, amount)
VALUES(4, 1, 1);user.java
package org.fkit.domain;
import java.util.List;
public class User {
private Integer id;
private String username;
private String loginname;
private String password;
private String phone;
private String address;
private List<Order> orders;
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 String getLoginname() {
return loginname;
}
public void setLoginname(String loginname) {
this.loginname = loginname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
}
article.java
package org.fkit.domain;
import java.util.List;
public class Article {
private Integer id;
private String name;
private String price;
private String remark;
private List<Order> orders;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
}
Article.java
package org.fkit.domain;
import java.util.List;
public class Order {
private Integer id;
private String code;
private Double total;
private User user;
private List<Article> articles;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
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;
}
public List<Article> getArticles() {
return articles;
}
public void setArticles(List<Article> articles) {
this.articles = articles;
}
}
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="org.fkit.mapper.UserMapper">
<resultMap type="org.fkit.domain.User" id="userResultMap">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="loginname" column="loginname"/>
<result property="password" column="password"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<collection property="orders" javaType="ArrayList"
column="id" ofType="org.fkit.domain.User"
select="org.fkit.mapper.OrderMapper.selectOrderByUserId"
fetchType="lazy">
<id property="id" column="id" />
<result property="code" column="code" />
<result property="total" column="total" />
</collection>
</resultMap>
<select id="selectUserById" parameterType="int" resultMap="userResultMap">
SELECT * FROM tb_user WHERE id = #{id}
</select>
</mapper>articlemapper.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="org.fkit.mapper.ArticleMapper">
<select id="selectArticleByOrderId" parameterType="int" resultType="org.fkit.domain.Article">
SELECT * FROM tb_article WHERE id IN (
SELECT article_id FROM tb_item WHERE order_id=#{id}
)
</select>
</mapper>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="org.fkit.mapper.OrderMapper">
<resultMap type="org.fkit.domain.Order" id="orderResultMap">
<id property="id" column="oid" />
<result property="code" column="code" />
<result property="total" column="total" />
<association property="user" javaType="org.fkit.domain.User">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="loginname" column="loginname"/>
<result property="password" column="password"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
</association>
<collection property="articles" javaType="ArrayList"
column="oid" ofType="org.fkit.domain.Article"
select="org.fkit.mapper.ArticleMapper.selectArticleByOrderId"
fetchType="lazy">
<id property="id" column="id" />
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="remark" column="remark"/>
</collection>
</resultMap>
<select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
SELECT u.*, o.id AS oid, CODE, TOTAL, USER_ID
FROM tb_user u, tb_order o
WHERE u.id = o.USER_ID
AND o.id=#{id}
</select>
<select id="selectOrderByUserId" parameterType="int" resultType="org.fkit.domain.Order">
SELECT * FROM tb_order WHERE user_id = #{id}
</select>
</mapper>ManyToManyTest.java
package org.fkit.tst;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.fkit.domain.Article;
import org.fkit.domain.Order;
import org.fkit.domain.User;
import org.fkit.factory.FKSqlSessionFactory;
import org.fkit.mapper.OrderMapper;
import org.fkit.mapper.UserMapper;
public class ManyToManyTest {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
SqlSession session = FKSqlSessionFactory.getSqlSession();
ManyToManyTest t = new ManyToManyTest();
t.testSelectUserById(session);
t.testSelectOrderById(session);
session.commit();
session.close();
}
public void testSelectUserById(SqlSession session) {
UserMapper um = session.getMapper(UserMapper.class);
User user = um.selectUserById(1);
System.out.println(user.getId() + " === " + user.getUsername());
List<Order> orders = user.getOrders();
for (Order order : orders) {
System.out.println(order.getCode());
}
}
public void testSelectOrderById(SqlSession session) {
OrderMapper om = session.getMapper(OrderMapper.class);
Order order = om.selectOrderById(2);
System.out.println(order.getId() + " @@@ " + order.getCode() + " @@@ " + order.getTotal());
User user = order.getUser();
System.out.println(user.getUsername());
List<Article> articles = order.getArticles();
for(Article article : articles) {
System.out.println(article.getName());
}
}
}
