mybatis java_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包：

org.mybatis

mybatis

3.4.6

com.alibaba

druid

1.1.21

mysql

mysql-connector-java

8.0.11

在项目的/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

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

注意：

1)上边mybatis-config.xml中配置datasource可以配置多个，在中指定启用配置；

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

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

Parent类：

packagecom.dx.test;importjava.util.List;public classParent {privateLong id;privateString name;private ListchildList;

......

@OverridepublicString toString() {return "Parent{" +

"id=" + id +

", name='" + name + '\'' +

", childList=" + childList +

'}';

}

}

Child类：

packagecom.dx.test;public classChild {privateLong id;privateString name;privateLong parentId;privateParent parent;

......

@OverridepublicString 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：

packagecom.dx.test.dao;importcom.dx.test.Child;importcom.dx.test.Parent;import org.apache.ibatis.annotations.*;importjava.util.List;

@Mapperpublic interfaceParentDao {

@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 selectChildsByParentId(@Param("parentId") Long parentId);

}

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

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

测试代码：

packagecom.dx.test;importcom.dx.test.dao.ChildDao;importcom.dx.test.dao.ParentDao;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importjava.io.IOException;importjava.io.InputStream;public classMybatisTest {public static void main(String[] args) throwsIOException {final String resource = "mybatis-config.xml";

SqlSessionFactory factory= null;

InputStream is=Resources.getResourceAsStream(resource);

factory= newSqlSessionFactoryBuilder().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：

packagecom.dx.test.dao;importcom.dx.test.Child;importcom.dx.test.Parent;import org.apache.ibatis.annotations.*;

@Mapperpublic interfaceChildDao {

@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);

}

测试代码：

packagecom.dx.test;importcom.dx.test.dao.ChildDao;importcom.dx.test.dao.ParentDao;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importjava.io.IOException;importjava.io.InputStream;public classMybatisTest {public static void main(String[] args) throwsIOException {final String resource = "mybatis-config.xml";

SqlSessionFactory factory= null;

InputStream is=Resources.getResourceAsStream(resource);

factory= newSqlSessionFactoryBuilder().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查询其购物车(商家->商品 一对多查询)

*@paramconsumerId 顾客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")

)

)

ListfindCartById(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 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