最近工作中用到了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