黑马Mybatis day3 多表查询 1.xml配置方式 2.注解方式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.mozq; 2 3 import com.itheima.domain.Order; 4 import com.itheima.mapper.OrderMapper; 5 import org.apache.ibatis.io.Resources; 6 import org.apache.ibatis.session.SqlSession; 7 import org.apache.ibatis.session.SqlSessionFactory; 8 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 9 import org.junit.Before; 10 import org.junit.Test; 11 12 import java.io.IOException; 13 import java.io.InputStream; 14 import java.util.List; 15 16 17 public class OneToOneTest { 18 OrderMapper mapper; 19 20 @Before 21 public void before() throws IOException { 22 //加载配置 23 InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml"); 24 //创建SSF 25 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); 26 //创建SS 27 SqlSession sqlSession = sqlSessionFactory.openSession(true); 28 mapper = sqlSession.getMapper(OrderMapper.class); 29 } 30 31 @Test 32 public void testFindAll(){ 33 List<Order> orderList = mapper.findAll(); 34 for (Order order : orderList) { 35 System.out.println(order); 36 } 37 } 38 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.mozq; 2 3 import com.itheima.domain.User; 4 import com.itheima.mapper.UserMapper; 5 import org.apache.ibatis.io.Resources; 6 import org.apache.ibatis.session.SqlSession; 7 import org.apache.ibatis.session.SqlSessionFactory; 8 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 9 import org.junit.Before; 10 import org.junit.Test; 11 12 import java.io.IOException; 13 import java.io.InputStream; 14 import java.util.List; 15 16 public class OneToManyTest { 17 UserMapper mapper; 18 19 @Before 20 public void before() throws IOException { 21 //加载配置 22 InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml"); 23 //创建SSF 24 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); 25 //创建SS 26 SqlSession sqlSession = sqlSessionFactory.openSession(true); 27 mapper = sqlSession.getMapper(UserMapper.class); 28 } 29 30 @Test 31 public void testFindWithOrder(){ 32 List<User> userList = mapper.findWithOrder(); 33 for (User user : userList) { 34 System.out.println(user); 35 } 36 } 37 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.mozq; 2 3 import com.itheima.domain.Order; 4 import com.itheima.domain.User; 5 import com.itheima.mapper.OrderMapper; 6 import com.itheima.mapper.UserMapper; 7 import org.apache.ibatis.io.Resources; 8 import org.apache.ibatis.session.SqlSession; 9 import org.apache.ibatis.session.SqlSessionFactory; 10 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 11 import org.junit.Before; 12 import org.junit.Test; 13 14 import java.io.IOException; 15 import java.io.InputStream; 16 import java.util.List; 17 18 public class ManyToManyTest { 19 UserMapper mapper; 20 21 @Before 22 public void before() throws IOException { 23 //加载配置 24 InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml"); 25 //创建SSF 26 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); 27 //创建SS 28 SqlSession sqlSession = sqlSessionFactory.openSession(true); 29 mapper = sqlSession.getMapper(UserMapper.class); 30 } 31 32 @Test 33 public void testFindWithRole(){ 34 List<User> userList = mapper.findWithRole(); 35 for (User user : userList) { 36 System.out.println(user); 37 } 38 } 39 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 5 <!--通过properties标签加载外部properties文件--> 6 <properties resource="jdbc.properties"></properties> 7 8 <!--自定义别名--> 9 <typeAliases> 10 <typeAlias type="com.itheima.domain.User" alias="User"></typeAlias> 11 <typeAlias type="com.itheima.domain.Role" alias="Role"></typeAlias> 12 <typeAlias type="com.itheima.domain.Order" alias="Order"></typeAlias> 13 </typeAliases> 14 15 <!--数据源环境--> 16 <environments default="developement"> 17 <environment id="developement"> 18 <transactionManager type="JDBC"></transactionManager> 19 <dataSource type="POOLED"> 20 <property name="driver" value="${jdbc.driver}"/> 21 <property name="url" value="${jdbc.url}"/> 22 <property name="username" value="${jdbc.username}"/> 23 <property name="password" value="${jdbc.password}"/> 24 </dataSource> 25 </environment> 26 </environments> 27 28 29 <!--设置映射关系--> 30 <mappers> 31 <!--指定接口所在的包--> 32 <package name="com.itheima.mapper"></package> 33 </mappers> 34 35 36 </configuration>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 ### direct log messages to stdout ### 2 log4j.appender.stdout=org.apache.log4j.ConsoleAppender 3 log4j.appender.stdout.Target=System.err 4 log4j.appender.stdout.layout=org.apache.log4j.PatternLayout 5 log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n 6 7 ### direct messages to file mylog.log ### 8 log4j.appender.file=org.apache.log4j.FileAppender 9 log4j.appender.file.File=c:/mylog.log 10 log4j.appender.file.layout=org.apache.log4j.PatternLayout 11 log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n 12 13 ### set log levels - for more verbose logging change 'info' to 'debug' ### 14 15 log4j.rootLogger=debug, stdout
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://localhost:3307/test 3 jdbc.username=root 4 jdbc.password=root
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.mapper; 2 3 import com.itheima.domain.Order; 4 import com.itheima.domain.User; 5 import org.apache.ibatis.annotations.One; 6 import org.apache.ibatis.annotations.Result; 7 import org.apache.ibatis.annotations.Results; 8 import org.apache.ibatis.annotations.Select; 9 10 import java.util.List; 11 12 public interface OrderMapper { 13 14 @Select("select * from orders") 15 @Results({ 16 @Result(column = "id", property = "id"), 17 @Result(column = "ordertime", property = "ordertime"), 18 @Result(column = "total", property = "total"), 19 @Result( 20 column = "uid", 21 property = "user", 22 javaType = User.class, 23 one = @One(select = "com.itheima.mapper.UserMapper.findById")) 24 25 }) 26 List<Order> findAll(); 27 28 @Select("select * from orders where uid=#{uid}") 29 List<Order> findByUid(int uid); 30 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.mapper; 2 3 import com.itheima.domain.Role; 4 import org.apache.ibatis.annotations.Select; 5 6 import java.util.List; 7 8 public interface RoleMapper { 9 10 @Select("select * from (select ur.roleId from sys_user_role ur where ur.userId = #{uid}) as t1 left join sys_role t2 on t1.roleId=t2.id;") 11 List<Role> findByUid(int uid); 12 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.mapper; 2 3 import com.itheima.domain.User; 4 import org.apache.ibatis.annotations.*; 5 6 import java.util.List; 7 8 public interface UserMapper { 9 10 @Insert("insert into user(username, password, birthday) values(#{username}, #{password}, #{birthday})") 11 void save(User user); 12 13 @Delete("delete from user where id=#{id}") 14 void delete(int id); 15 16 @Update("update user set username=#{username}, password=#{password}, birthday=#{birthday} where id=#{id}") 17 void update(User user); 18 19 @Select("select * from user") 20 List<User> findAll(); 21 22 @Select("select * from user where id=#{id}") 23 List<User> findById(int id); 24 25 @Select("select * from user") 26 @Results({ 27 @Result(id=true ,column = "id", property = "id"), 28 @Result(column = "username", property = "username"), 29 @Result(column = "password", property = "password"), 30 @Result(column = "birthday", property = "birthday"), 31 @Result( 32 column = "id", 33 property = "orderList", 34 javaType = List.class, 35 many = @Many(select = "com.itheima.mapper.OrderMapper.findByUid") 36 ) 37 }) 38 List<User> findWithOrder(); 39 40 @Select("select * from user") 41 @Results({ 42 @Result(id=true ,column = "id", property = "id"), 43 @Result(column = "username", property = "username"), 44 @Result(column = "password", property = "password"), 45 @Result(column = "birthday", property = "birthday"), 46 @Result( 47 column = "id", 48 property = "roleList", 49 javaType = List.class, 50 many = @Many(select = "com.itheima.mapper.RoleMapper.findByUid") 51 ) 52 }) 53 List<User> findWithRole(); 54 55 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.domain; 2 3 import java.util.Date; 4 5 public class Order { 6 7 private int id; 8 private Date ordertime; 9 private double total; 10 11 //当前订单属于哪一个用户 12 private User user; 13 14 public int getId() { 15 return id; 16 } 17 18 public void setId(int id) { 19 this.id = id; 20 } 21 22 public Date getOrdertime() { 23 return ordertime; 24 } 25 26 public void setOrdertime(Date ordertime) { 27 this.ordertime = ordertime; 28 } 29 30 public double getTotal() { 31 return total; 32 } 33 34 public void setTotal(double total) { 35 this.total = total; 36 } 37 38 public User getUser() { 39 return user; 40 } 41 42 public void setUser(User user) { 43 this.user = user; 44 } 45 46 @Override 47 public String toString() { 48 return "Order{" + 49 "id=" + id + 50 ", ordertime=" + ordertime + 51 ", total=" + total + 52 ", user=" + user + 53 '}'; 54 } 55 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.domain; 2 3 public class Role { 4 5 private int id; 6 private String roleName; 7 private String roleDesc; 8 9 public int getId() { 10 return id; 11 } 12 13 public void setId(int id) { 14 this.id = id; 15 } 16 17 public String getRoleName() { 18 return roleName; 19 } 20 21 public void setRoleName(String roleName) { 22 this.roleName = roleName; 23 } 24 25 public String getRoleDesc() { 26 return roleDesc; 27 } 28 29 public void setRoleDesc(String roleDesc) { 30 this.roleDesc = roleDesc; 31 } 32 33 @Override 34 public String toString() { 35 return "Role{" + 36 "id=" + id + 37 ", roleName='" + roleName + '\'' + 38 ", roleDesc='" + roleDesc + '\'' + 39 '}'; 40 } 41 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.itheima.domain; 2 3 import java.util.Date; 4 import java.util.List; 5 6 public class User { 7 8 private int id; 9 private String username; 10 private String password; 11 private Date birthday; 12 13 //当前用户具备哪些角色 14 private List<Role> roleList; 15 16 public List<Role> getRoleList() { 17 return roleList; 18 } 19 20 public void setRoleList(List<Role> roleList) { 21 this.roleList = roleList; 22 } 23 24 //描述的是当前用户具有的订单 25 private List<Order> orderList; 26 public List<Order> getOrderList() { 27 return orderList; 28 } 29 public void setOrderList(List<Order> orderList) { 30 this.orderList = orderList; 31 } 32 33 public Date getBirthday() { 34 return birthday; 35 } 36 37 public void setBirthday(Date birthday) { 38 this.birthday = birthday; 39 } 40 41 public int getId() { 42 return id; 43 } 44 45 public void setId(int id) { 46 this.id = id; 47 } 48 49 public String getUsername() { 50 return username; 51 } 52 53 public void setUsername(String username) { 54 this.username = username; 55 } 56 57 public String getPassword() { 58 return password; 59 } 60 61 public void setPassword(String password) { 62 this.password = password; 63 } 64 65 @Override 66 public String toString() { 67 return "User{" + 68 "id=" + id + 69 ", username='" + username + '\'' + 70 ", password='" + password + '\'' + 71 ", birthday=" + birthday + 72 ", roleList=" + roleList + 73 ", orderList=" + orderList + 74 '}'; 75 } 76 }