MyBatis(六)：Mybatis Java API编程实现一对多、一对一

最近工作中用到了mybatis的Java API方式进行开发，顺便也整理下该功能的用法，接下来会针对基本部分进行学习：

Mybatis官网给了具体的文档，但是并没有对以上用法具体介绍，因此在这里整理下，以便以后工作用到时，可以参考。

本章主要对一对多、一对一查询的用法进行学习，下边文章分为以下几个步骤：

1）新建maven，并引入mybatis/mysql/druid包；

2）在mysql中新建mydb，并创建parent,child两张主子表；

3）一对多的用法；

4）多对一的用法。

1）新建maven，并引入mybatis/mysql/druid包；

在idea中新建一个maven module（mybatis-test）:

修改pom.xml中jdk版本为1.8。

在pom.xml中引入mybaits/mysql/druid/log相关jar包：

        <!--MyBatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <!--MySql数据库驱动 -->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

在项目的/src/main/resources下引入新建一下配置文件：

1）db.properties

#oracle.jdbc.driver.OracleDriver | com.mysql.jdbc.Driver
driver=com.mysql.cj.jdbc.Driver
#jdbc:oracle:thin:@localhost:1521:orcl | jdbc:mysql://localhost:3306/mybatis
url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
name=root
password=123456

注意：

driver配置的com.mysql.cj.jdbc.Driver，在mysql新版本中需要这么配置

2）log.properties

# #define DEBUG priority, R
# log4j.rootLogger = INFO, R
# # configure log output type as file
# log4j.appender.R = org.apache.log4j.DailyRollingFileAppender
# #log filename
# log4j.appender.R.file = ./logRecord.log
# # append
# log4j.appender.R.Append = true
# log4j.appender.R.layout = org.apache.log4j.PatternLayout
# log4j.appender.R.layout.ConversionPattern = %n%d%p[%l]%m%n
#
# log4j.appender.R.DatePattern='.' yyyy-MM-dd


log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

3）mybatis-config.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>
    <!-- 引用db.properties配置文件 -->
    <properties resource="db.properties"/>
    <typeAliases>
        <package name="com.dx.test.model"/>
    </typeAliases>
    <!-- 对事务的管理和连接池的配置 -->
    <environments default="mysql_jdbc">
        <environment id="oracle_jdbc">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

        <environment id="mysql_jdbc">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${name}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--
    <mappers>
        <mapper resource="resources/mapper/TaskAutoExecutePlanMapper.xml"/>
    </mappers>
    -->
    <mappers>
        <mapper class="com.dx.test.dao.ParentDao"></mapper>
        <mapper class="com.dx.test.dao.ChildDao"></mapper>
    </mappers>
</configuration>

注意：

1）上边mybatis-config.xml中配置datasource可以配置多个，在<environments default="mysql_jdbc">中指定启用配置；

2）因我们这里使用的是mybatis Java API技术，因此mappers下的mapper不需要配置*Mapper.xml，直接指定Mapper类即可，上边配置的ParentDao和ChildDao都是Mapper接口。

在com.dx.test.model下创建model类Parent/Child：

Parent类：

package com.dx.test;

import java.util.List;

public class Parent {
    private Long id;
    private String name;
    private List<Child> childList;

    ......

    @Override
    public String toString() {
        return "Parent{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", childList=" + childList +
                '}';
    }
}

Child类：

package com.dx.test;

public class Child {
    private Long id;
    private String name;
    private Long parentId;
    private Parent parent;

    ......

    @Override
    public String toString() {
        return "Child{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", parentId=" + parentId +
                ", parent=" + parent +
                '}';
    }
}

2）在mysql中新建mydb，并创建parent,child两张主子表；

创建和插叙数据语句如下：

CREATE DATABASE  IF NOT EXISTS `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mydb`;
-- MySQL dump 10.13  Distrib 8.0.18, for macos10.14 (x86_64)
--
-- Host: localhost    Database: mydb
-- ------------------------------------------------------
-- Server version       8.0.17

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `child`
--

LOCK TABLES `child` WRITE;
/*!40000 ALTER TABLE `child` DISABLE KEYS */;
INSERT INTO `child` VALUES (1,'child1',1),(2,'child2',1),(3,'child2-1',2),(4,'child2-2',2);
/*!40000 ALTER TABLE `child` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `parent`
--

LOCK TABLES `parent` WRITE;
/*!40000 ALTER TABLE `parent` DISABLE KEYS */;
INSERT INTO `parent` VALUES (1,'test1'),(2,'test2');
/*!40000 ALTER TABLE `parent` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-11-12 21:09:47

3）一对多的用法；

定义Mapper类ParentDao：

package com.dx.test.dao;

import com.dx.test.Child;
import com.dx.test.Parent;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ParentDao {
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id = "parentResult", value = {
            @Result(property = "id", column = "id", id = true),
            @Result(property = "name", column = "name"),
            @Result(property = "childList", javaType = List.class, many = @Many(select = "selectChildsByParentId"), column = "id")
    })
    @Select("select * from parent where id = #{id}")
    Parent queryParentById(Long id);

    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id="childResult",value={
            @Result(property = "id",column = "id",id=true),
            @Result(property = "name",column = "name"),
            @Result(property = "parentId",column = "parent_id")
    })
    @Select({"select t10.* from child t10 where t10.parent_id=#{parentId}"})
    List<Child> selectChildsByParentId(@Param("parentId") Long parentId);
}

1）queryParentById（Long id）：根据parent.id查询parent实体，并在@Reusults中定义了chilList的装配方法；

2）selectChildsByParentId(Long parentId)：根据parentId，查询子类实体列表。

测试代码：

package com.dx.test;

import com.dx.test.dao.ChildDao;
import com.dx.test.dao.ParentDao;
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 java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    public static void main(String[] args) throws IOException {
        final String resource = "mybatis-config.xml";
        SqlSessionFactory factory = null;

        InputStream is = Resources.getResourceAsStream(resource);
        factory = new SqlSessionFactoryBuilder().build(is);

        SqlSession sqlSession = factory.openSession();

        ParentDao parentDao = sqlSession.getMapper(ParentDao.class);
        Parent parent = parentDao.queryParentById(1L);
        System.out.println(parent);
    }
}

输出结果：

Parent{id=1, name='test1', childList=[Child{id=1, name='child1', parentId=1, parent=null}, Child{id=2, name='child2', parentId=1, parent=null}]}

4）多对一的用法。

定义mapper类ChildDao：

package com.dx.test.dao;

import com.dx.test.Child;
import com.dx.test.Parent;
import org.apache.ibatis.annotations.*;

@Mapper
public interface ChildDao {
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id = "childResult", value = {
            @Result(property = "id", column = "id", id = true),
            @Result(property = "name", column = "name"),
            @Result(property = "parentId", column = "parent_id"),
            @Result(property = "parent", javaType = Parent.class, one = @One(select = "selectParentById"), column = "parent_id")
    })
    @Select({"select * from child where id=#{id}"})
    Child selectById(@Param("id") Long id);


    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE, timeout = 60000)
    @Results(id = "parentResult", value = {
            @Result(property = "id", column = "id", id = true),
            @Result(property = "name", column = "name")
    })
    @Select("select * from parent where id = #{id}")
    Parent selectParentById(@Param("parentId") Long id);

}

测试代码：

package com.dx.test;

import com.dx.test.dao.ChildDao;
import com.dx.test.dao.ParentDao;
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 java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    public static void main(String[] args) throws IOException {
        final String resource = "mybatis-config.xml";
        SqlSessionFactory factory = null;

        InputStream is = Resources.getResourceAsStream(resource);
        factory = new SqlSessionFactoryBuilder().build(is);

        SqlSession sqlSession = factory.openSession();

        ChildDao childDao = sqlSession.getMapper(ChildDao.class);
        Child child = childDao.selectById(1L);
        System.out.println(child);
    }
}

输出结果：

Child{id=1, name='child1', parentId=1, parent=Parent{id=1, name='test1', childList=null}}

5)一对多在查询子表时传递多个参数

/**
     * 按顾客id查询其购物车（商家->商品 一对多查询）
     * @param consumerId 顾客id
     * @return 购物车商品列表
     */
    @Select("select distinct saler.id,saler.shopname,#{consumerId} as consumerId from shoppingcart \n" +
            "join saler on saler.id = shoppingcart.salerId \n" +
            "where consumerId = #{consumerId}")
    @Results(
            @Result(
                    property = "goods",
                    column = "{salerId = id,consumerId = consumerId}",
                    many = @Many(select = "cn.datacharm.springbootvuecli.dao.CartMapper.findGoodsBySalerId")
            )
    )
    List<Shop> findCartById(Integer consumerId);

    @Select("select \n" +
            "sid,consumerId,productName,price,photo,\n" +
            "shoppingcart.salerId,\n" +
            "shoppingcart.productId,\n" +
            "shoppingcart.amount\n" +
            "from shoppingcart\n" +
            "join saler_inventory on shoppingcart.salerId = saler_inventory.salerId\n" +
            "and shoppingcart.productId = saler_inventory.productId\n" +
            "where shoppingcart.salerId = #{salerId}\n"+
            "and consumerId = #{consumerId}" )
    List<Goods> findGoodsBySalerId(Integer salerId,Integer consumerId);

@Result中column = "{salerId = id,consumerId = consumerId}"
表示把id列和consumerId列取出
id列值使用salerId,consumerId列使用consumerId 表示（类似别名，对应子查询参数）
然后以这两个参数进行子查询

参考：https://blog.csdn.net/weixin_43297055/article/details/93635950

posted @ 2019-11-12 21:17 cctext 阅读(949) 评论(0) 编辑收藏举报

刷新页面返回顶部

yy

基础才是编程人员应该深入研究的问题，警告自己问题解决不了时，多从运行原理底层研究后再考虑方案。

MyBatis(六)：Mybatis Java API编程实现一对多、一对一

1）新建maven，并引入mybatis/mysql/druid包；

2）在mysql中新建mydb，并创建parent,child两张主子表；

3）一对多的用法；

4）多对一的用法。

5)一对多在查询子表时传递多个参数

公告