区块链技术博客
www.b2bchain.cn

28.mybatis的多表操作

这篇文章主要介绍了28.mybatis的多表操作的讲解,通过具体代码实例进行18004 讲解,并且分析了28.mybatis的多表操作的详细步骤与相关技巧,需要的朋友可以参考下https://www.b2bchain.cn/?p=18004

本文实例讲述了2、树莓派设置连接WiFi,开启VNC等等的讲解。分享给大家供大家参考文章查询地址https://www.b2bchain.cn/7039.html。具体如下:

目录

一、一对一查询

二、一对多查询

三、多对多查询


会用到的表先看一下:

28.mybatis的多表操作

28.mybatis的多表操作

 

 

一、一对一查询

用户订单模型:一个用户会有多个订单,一个订单对应一个用户

实例:

目录:

28.mybatis的多表操作

pom.xml

<?xml version="1.0" encoding="UTF-8"?>  <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">   <modelVersion>4.0.0</modelVersion>    <groupId>com.xupeng</groupId>   <artifactId>spring_multi</artifactId>   <version>1.0-SNAPSHOT</version>   <packaging>war</packaging>    <name>spring_multi Maven Webapp</name>   <!-- FIXME change it to the project's website -->   <url>http://www.example.com</url>    <properties>     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>     <maven.compiler.source>1.7</maven.compiler.source>     <maven.compiler.target>1.7</maven.compiler.target>   </properties>    <dependencies>     <dependency>       <groupId>mysql</groupId>       <artifactId>mysql-connector-java</artifactId>       <version>8.0.16</version>     </dependency>     <dependency>       <groupId>org.mybatis</groupId>       <artifactId>mybatis</artifactId>       <version>3.4.6</version>     </dependency>     <dependency>       <groupId>junit</groupId>       <artifactId>junit</artifactId>       <version>4.12</version>       <scope>test</scope>     </dependency>     <dependency>       <groupId>log4j</groupId>       <artifactId>log4j</artifactId>       <version>1.2.17</version>     </dependency>     <dependency>       <groupId>com.github.pagehelper</groupId>       <artifactId>pagehelper</artifactId>       <version>3.7.5</version>     </dependency>     <dependency>       <groupId>com.github.jsqlparser</groupId>       <artifactId>jsqlparser</artifactId>       <version>0.9.1</version>     </dependency>   </dependencies>    <build>     <finalName>spring_multi</finalName>     <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->       <plugins>         <plugin>           <artifactId>maven-clean-plugin</artifactId>           <version>3.1.0</version>         </plugin>         <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->         <plugin>           <artifactId>maven-resources-plugin</artifactId>           <version>3.0.2</version>         </plugin>         <plugin>           <artifactId>maven-compiler-plugin</artifactId>           <version>3.8.0</version>         </plugin>         <plugin>           <artifactId>maven-surefire-plugin</artifactId>           <version>2.22.1</version>         </plugin>         <plugin>           <artifactId>maven-war-plugin</artifactId>           <version>3.2.2</version>         </plugin>         <plugin>           <artifactId>maven-install-plugin</artifactId>           <version>2.5.2</version>         </plugin>         <plugin>           <artifactId>maven-deploy-plugin</artifactId>           <version>2.8.2</version>         </plugin>       </plugins>     </pluginManagement>   </build> </project> 

User

package com.xupeng.domain;  import java.util.Date;  public class User {     private int id;     private String username;     private String password;     private Date birthday;      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 getPassword() {         return password;     }      public void setPassword(String password) {         this.password = password;     }      public Date getBirthday() {         return birthday;     }      public void setBirthday(Date birthday) {         this.birthday = birthday;     }       @Override     public String toString() {         return "User{" +                 "id=" + id +                 ", username='" + username + ''' +                 ", password='" + password + ''' +                 '}';     } } 

Order

package com.xupeng.domain;  import java.util.Date;  public class Order {     private int id;     private Date ordertime;     private double total;     private User user;      public int getId() {         return id;     }      public void setId(int id) {         this.id = id;     }      public Date getOrdertime() {         return ordertime;     }      public void setOrdertime(Date ordertime) {         this.ordertime = ordertime;     }      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;     } } 

UserMapper

package com.xupeng.mapper;  public interface UserMapper { } 

OrderMapper

package com.xupeng.mapper;  import com.xupeng.domain.Order;  import java.util.List;  public interface OrderMapper {     List<Order> findAll(); } 

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/xupeng?serverTimezone=UTC jdbc.username=root jdbc.password=xp880000

log4j.properties

log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.Target=System.out log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n  log4j.appender.file=org.apache.log4j.FileAppender log4j.appender.file.File=d:/product/mylog.log log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n  log4j.rootLogger=debug,stdout

sqlMapConfig.xml

<?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"> <configuration>      <!--自定义别名-->     <typeAliases>         <typeAlias type="com.xupeng.domain.User" alias="user"></typeAlias>         <typeAlias type="com.xupeng.domain.Order" alias="order"></typeAlias>     </typeAliases>      <!--数据源环境-->     <environments default="development">         <environment id="development">             <transactionManager type="JDBC"></transactionManager>             <dataSource type="POOLED">                 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>                 <property name="url" value="jdbc:mysql://localhost:3306/xupeng?serverTimezone=UTC"/>                 <property name="username" value="root"/>                 <property name="password" value="xp880000"/>             </dataSource>         </environment>     </environments>      <!--加载映射文件-->     <mappers>         <mapper resource="com/xupeng/mapper/UserMapper.xml"></mapper>         <mapper resource="com/xupeng/mapper/OrderMapper.xml"></mapper>     </mappers>  </configuration>

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="com.xupeng.mapper.UserMapper">     <!--插入-->     <insert id="save" parameterType="user">         insert into user values (#{id},#{username},#{password},#{birthday})     </insert>      <select id="findById" parameterType="int" resultType="user">         select * from user where id = #{id}     </select>      <select id="findAll" resultType="user">         select * from user     </select>  </mapper>

OrderMapper.xml:通过<resultMap>标签将对应的属性字段和数据库的查询结果字段对应起来

<?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.xupeng.mapper.OrderMapper">          <resultMap id="orderMap" type="order">         <!--手动指定字段与实体属性的映射关系-->         <id column="oid" property="id"></id>         <result column="ordertime" property="ordertime"></result>         <result column="total" property="total"></result>         <result column="uid" property="user.id"></result>         <result column="username" property="user.username"></result>         <result column="password" property="user.password"></result>         <result column="birthday" property="user.birthday"></result>      </resultMap>          <select id="findAll" resultMap="orderMap">         SELECT * ,o.id as oid FROM orders o left join  user u on  o.uid = u.id     </select> </mapper>

MyBatisTest

package com.xupeng.test;  import com.xupeng.domain.Order; import com.xupeng.domain.User; import com.xupeng.mapper.OrderMapper; import com.xupeng.mapper.UserMapper; 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.junit.Test;  import java.io.IOException; import java.io.InputStream; import java.util.List;  public class MyBatisTest {      @Test     public void test1() throws IOException {         //获取核心配置文件         InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");         //获取session工厂对象         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);         //获取session会话对象         SqlSession sqlSession = sqlSessionFactory.openSession();         //插入操作         OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);          List<Order> list = mapper.findAll();         for (Order order:list){             System.out.println(order);         }           //mybatis执行更新操作,需要提交事务 //        sqlSession.commit();         //释放资源         sqlSession.close();     } } 

结果:

28.mybatis的多表操作

 

二、一对多查询

还是上面的项目,以下做了修改:

UserMapper

package com.xupeng.mapper;  import com.xupeng.domain.User;  import java.util.List;  public interface UserMapper {     List<User> findAll(); } 

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="com.xupeng.mapper.UserMapper">     <resultMap id="userMap" type="user">         <id column="uid" property="id"></id>         <result column="username" property="username"></result>         <result column="password" property="password"></result>         <result column="birthday" property="birthday"></result>         <!--配置集合信息             property:集合名称             ofType:集合中的数据类型         -->         <collection property="orderList" ofType="order">             <!--封装order的数据-->             <id column="oid" property="id"></id>             <result column="ordertime" property="ordertime"></result>             <result column="total" property="total"></result>         </collection>     </resultMap>      <select id="findAll" resultMap="userMap">         SELECT * ,o.id as oid FROM  user u left join orders o on u.id = o.uid     </select>       <!--插入-->     <insert id="save" parameterType="user">         insert into user values (#{id},#{username},#{password},#{birthday})     </insert>      <select id="findById" parameterType="int" resultType="user">         select * from user where id = #{id}     </select>    </mapper>

MyBatisTest

@Test     public void test2() throws IOException {         //获取核心配置文件         InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");         //获取session工厂对象         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);         //获取session会话对象         SqlSession sqlSession = sqlSessionFactory.openSession();         //插入操作         UserMapper mapper = sqlSession.getMapper(UserMapper.class);          List<User> list = mapper.findAll();         for (User user:list){             System.out.println(user);         }           //mybatis执行更新操作,需要提交事务 //        sqlSession.commit();         //释放资源         sqlSession.close();     }

结果:

28.mybatis的多表操作

 

三、多对多查询

(其实和一对多差不多)

用户表和角色表:一个用户可有多个角色,一个角色可包含多个用户

28.mybatis的多表操作

28.mybatis的多表操作

Role

package com.xupeng.domain;  public class Role {     private int id;     private String roleName;      public int getId() {         return id;     }      public void setId(int id) {         this.id = id;     }      public String getRoleName() {         return roleName;     }      public void setRoleName(String roleName) {         this.roleName = roleName;     }       @Override     public String toString() {         return "Role{" +                 "id=" + id +                 ", roleName='" + roleName + ''' +                 '}';     } } 

User

package com.xupeng.domain;  import java.util.Date; import java.util.List;  public class User {     private int id;     private String username;     private String password;     private Date birthday;      //用户拥有的订单     private List<Order> orderList;      //用户是哪些角色     private List<Role> roleList;      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 getPassword() {         return password;     }      public void setPassword(String password) {         this.password = password;     }      public Date getBirthday() {         return birthday;     }      public void setBirthday(Date birthday) {         this.birthday = birthday;     }      public List<Order> getOrderList() {         return orderList;     }      public void setOrderList(List<Order> orderList) {         this.orderList = orderList;     }      public List<Role> getRoleList() {         return roleList;     }      public void setRoleList(List<Role> roleList) {         this.roleList = roleList;     }      @Override     public String toString() {         return "User{" +                 "id=" + id +                 ", username='" + username + ''' +                 ", password='" + password + ''' +                 ", birthday=" + birthday +                 ", orderList=" + orderList +                 ", roleList=" + roleList +                 '}';     } } 

UserMapper

package com.xupeng.mapper;  import com.xupeng.domain.User;  import java.util.List;  public interface UserMapper {     List<User> findAll();      List<User> findUserAndRoleAll(); } 

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="com.xupeng.mapper.UserMapper">     <resultMap id="userMap" type="user">         <id column="uid" property="id"></id>         <result column="username" property="username"></result>         <result column="password" property="password"></result>         <result column="birthday" property="birthday"></result>         <!--配置集合信息             property:集合名称             ofType:集合中的数据类型         -->         <collection property="orderList" ofType="order">             <!--封装order的数据-->             <id column="oid" property="id"></id>             <result column="ordertime" property="ordertime"></result>             <result column="total" property="total"></result>         </collection>     </resultMap>      <select id="findAll" resultMap="userMap">         SELECT * ,o.id as oid FROM  user u left join orders o on u.id = o.uid     </select>      <!--多对多:用户角色-->     <resultMap id="userRoleMap" type="user">         <id column="id" property="id"></id>         <result column="username" property="username"></result>         <result column="password" property="password"></result>         <result column="birthday" property="birthday"></result>         <collection property="roleList" ofType="role">             <id column="rid" property="id"></id>             <result column="rolename" property="roleName"></result>         </collection>     </resultMap>      <select id="findUserAndRoleAll" resultMap="userRoleMap">         SELECT *,r.id as rid FROM user_role ur left join  user u  on ur.user_id = u.id left join role r on ur.role_id = r.id     </select>       <!--插入-->     <insert id="save" parameterType="user">         insert into user values (#{id},#{username},#{password},#{birthday})     </insert>      <select id="findById" parameterType="int" resultType="user">         select * from user where id = #{id}     </select>    </mapper>

UserMapper.xml

<?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"> <configuration>      <!--自定义别名-->     <typeAliases>         <typeAlias type="com.xupeng.domain.User" alias="user"></typeAlias>         <typeAlias type="com.xupeng.domain.Order" alias="order"></typeAlias>         <typeAlias type="com.xupeng.domain.Role" alias="role"></typeAlias>     </typeAliases>      <!--数据源环境-->     <environments default="development">         <environment id="development">             <transactionManager type="JDBC"></transactionManager>             <dataSource type="POOLED">                 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>                 <property name="url" value="jdbc:mysql://localhost:3306/xupeng?serverTimezone=UTC"/>                 <property name="username" value="root"/>                 <property name="password" value="xp880000"/>             </dataSource>         </environment>     </environments>      <!--加载映射文件-->     <mappers>         <mapper resource="com/xupeng/mapper/UserMapper.xml"></mapper>         <mapper resource="com/xupeng/mapper/OrderMapper.xml"></mapper>     </mappers>  </configuration>

MyBatisTest

 @Test     public void test3() throws IOException {         //获取核心配置文件         InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");         //获取session工厂对象         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);         //获取session会话对象         SqlSession sqlSession = sqlSessionFactory.openSession();         //插入操作         UserMapper mapper = sqlSession.getMapper(UserMapper.class);          List<User> list = mapper.findUserAndRoleAll();         for (User user:list){             System.out.println(user);         }           //mybatis执行更新操作,需要提交事务 //        sqlSession.commit();         //释放资源         sqlSession.close();     }

结果:

28.mybatis的多表操作

本文转自互联网,侵权联系删除28.mybatis的多表操作

赞(0) 打赏
部分文章转自网络,侵权联系删除b2bchain区块链学习技术社区 » 28.mybatis的多表操作
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

b2b链

联系我们联系我们