注:不再写多对多的动态sql,可参考之前的动态sql文章
1.User的javabean
package com.bean;
import java.util.List;
public class User {
private int id;
private String username;
private String loginname;
private String password;
private String phone;
private String address;
private List<Order> orders;
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 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;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", loginname=" + loginname + ", password=" + password
+ ", phone=" + phone + ", address=" + address + "]";
}
}
2.Order的javabean
package com.bean;
import java.util.List;
public class Order {
private int id;
private String code;
private double total;
private User user;
private List<Article> articles;
public int getId() {
return id;
}
public void setId(int 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;
}
@Override
public String toString() {
return "Order [id=" + id + ", code=" + code + ", total=" + total + "]";
}
}
3.Article的javabean
package com.bean;
public class Article {
private int id;
private String name;
private double price;
private String remark;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return "Article [id=" + id + ", name=" + name + ", price=" + price + ", remark=" + remark + "]";
}
}
4.log4j.properties
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.mapper=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
5.jdbc.properties(对一些变量的设置)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis
username=root
password=123456
6.mybatis配置文件
<?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">
<!--1.配置环境 ,默认的环境id为mysql-->
<configuration>
<!-- 导入 jdbc.properties 引用里面的值-->
<properties resource="jdbc.properties"/>
<!-- 1.指定mybatis所用日志的具体实现 -->
<settings>
<setting name="logImpl" value="LOG4j"/>
</settings>
<!--2.配置环境 ,默认的环境id为mysql-->
<environments default="mysql">
<!--2.2.配置id为mysql的数据库环境 -->
<environment id="mysql">
<!-- 2.2.1使用JDBC的事务管理 使用了JDBC的提交和回滚设置-->
<transactionManager type="JDBC"></transactionManager>
<!--2.2.2数据库连接池 POOLED是JDBC连接对象的数据源连接池的实现 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--3.配置Mapper的位置 -->
<mappers>
<mapper class="com.mapper.userMapper"/>
<mapper class="com.mapper.orderMapper"/>
<mapper class="com.mapper.articleMapper"/>
</mappers>
</configuration>
7.数据库操作的类(userMapper)
package com.mapper;
import org.apache.ibatis.annotations.Select;
import com.bean.User;
public interface userMapper {
@Select("select * from tb_user where id=#{id}")
User selectUser(int id);
}
8.数据库操作的类(orderMapper)
package com.mapper;
import static org.junit.Assert.assertTrue;
import org.apache.ibatis.annotations.Many;
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 com.bean.Order;
public interface orderMapper {
@Select("select * from tb_order where id=#{id}")
@Results({
@Result(id=true,property="id",column="id"),
@Result(property="code",column="code"),
@Result(property="total",column="total"),
@Result(column="user_id",property="user",
one=@One(select="com.mapper.userMapper.selectUser",fetchType=FetchType.EAGER)),
@Result(column="id",property="articles",
many=@Many(select="com.mapper.articleMapper.seleceArticle",fetchType=FetchType.LAZY))
})
Order selectByUserId(int i);
}
9.数据库操作的类(articleMapper)
package com.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Select;
import com.bean.Article;
public interface articleMapper {
@Select("select * from tb_article where id in(select article_id from tb_item where order_id=#{id})")
List<Article> seleceArticle(int order_id);
}
10.查询
package com.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.tools.ant.types.CommandlineJava.SysProperties;
import org.junit.Test;
import com.bean.Article;
import com.bean.Order;
import com.bean.User;
import com.mapper.orderMapper;
public class test {
@Test
public void selectByUserId() throws IOException
{
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sessionFactory.openSession();
orderMapper ordermapper=session.getMapper(orderMapper.class);
Order order=ordermapper.selectByUserId(2);
System.out.println(order);
User user=order.getUser();
System.out.println(user);
List<Article> articles=order.getArticles();
articles.forEach(article->System.out.println(article));
}
}