目录结构
maven
src-main-java-com-entity/mapper/test/utils
resources-jdbc.properties/mybatis-config.xml
pom.xml
java
entity
Category.java
package com.itqf.entity;
import java.util.List;
public class Category {
private int cId;
private String cName;
private int iId;
private Info info;
private List<Prodoct> prodocts;
public int getcId() {
return cId;
}
public void setcId(int cId) {
this.cId = cId;
}
public String getcName() {
return cName;
}
public void setcName(String cName) {
this.cName = cName;
}
public int getiId() {
return iId;
}
public void setiId(int iId) {
this.iId = iId;
}
public Info getInfo() {
return info;
}
public void setInfo(Info info) {
this.info = info;
}
public List<Prodoct> getProdocts() {
return prodocts;
}
public void setProdocts(List<Prodoct> prodocts) {
this.prodocts = prodocts;
}
@Override
public String toString() {
return "Category{" +
"cId=" + cId +
", cName='" + cName + '\'' +
", iId=" + iId +
", info=" + info +
", prodocts=" + prodocts +
'}';
}
}
Info.java
package com.itqf.entity;
import java.util.List;
public class Info {
private int iId;
private String iInfo;
private int aId;
private List<Category> categories;
public int getiId() {
return iId;
}
public void setiId(int iId) {
this.iId = iId;
}
public String getiInfo() {
return iInfo;
}
public void setiInfo(String iInfo) {
this.iInfo = iInfo;
}
public int getaId() {
return aId;
}
public void setaId(int aId) {
this.aId = aId;
}
public List<Category> getCategories() {
return categories;
}
public void setCategories(List<Category> categories) {
this.categories = categories;
}
@Override
public String toString() {
return "Info{" +
"iId=" + iId +
", iInfo='" + iInfo + '\'' +
", aId=" + aId +
", categories=" + categories +
'}';
}
}
Prodoct.java
package com.itqf.entity;
public class Prodoct {
private int pId;
private String pName;
private int cId;
private Category category;
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public int getpId() {
return pId;
}
public void setpId(int pId) {
this.pId = pId;
}
public String getpName() {
return pName;
}
public void setpName(String pName) {
this.pName = pName;
}
public int getcId() {
return cId;
}
public void setcId(int cId) {
this.cId = cId;
}
@Override
public String toString() {
return "Prodoct{" +
"pId=" + pId +
", pName='" + pName + '\'' +
", cId=" + cId +
", category=" + category +
'}';
}
}
mapper
CategoryMapper.java
package com.itqf.mapper;
import com.itqf.entity.Category;
import java.util.List;
public interface CategoryMapper {
Category selectCategoryByCid(int cid);
Category selectCategoryAndProductByCid(int cid);
Category selectCategoryAndProdoctsByCIDQ(int cid);
List<Category> selectCategoryAndProdoctsByIIDQ(int iid);
Category selectCategoryAndInfoByPIDQ(int cid);
}
CategoryMapper.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.itqf.mapper.CategoryMapper">
<!--Category selectCategoryByCid(int cid);-->
<select id="selectCategoryByCid" resultType="category">
select * from category where c_id = #{cid}
</select>
<!--Category selectCategoryAndProductByCid(int cid);-->
<resultMap id="categorymap" type="category">
<id column="c_id" property="cId"/>
<collection property="prodocts" ofType="prodoct">
<id column="p_id" property="pId"/>
</collection>
</resultMap>
<select id="selectCategoryAndProductByCid" resultMap="categorymap">
select c.c_id, c.c_name, c.i_id, p.p_id, p.p_name
from category c join prodoct p on c.c_id = p.c_id
where c.c_id = #{cid}
</select>
<!--Category selectCategoryAndProdoctsByCIDQ(int cid);-->
<resultMap id="ctq" type="category">
<id column="c_id" property="cId"/>
<collection property="prodocts" ofType="prodoct" column="c_id" select="com.itqf.mapper.ProdoctMapper.selectProdoctsByCID"/>
</resultMap>
<select id="selectCategoryAndProdoctsByCIDQ" resultMap="ctq">
select * from category where c_id = #{cid}
</select>
<!--List<Category> selectCategoryAndProdoctsByIIDQ(int iid);-->
<select id="selectCategoryAndProdoctsByIIDQ" resultMap="ctq">
select * from category where i_id = #{iid}
</select>
<!--Prodoct selectCategoryAndInfoByPIDQ(int pid);-->
<resultMap id="piq" type="category">
<id column="c_id" property="cId"/>
<result column="i_id" property="iId"/>
<association property="info" javaType="info" column="i_id" select="com.itqf.mapper.InfoMapper.selectInfoByIID"/>
</resultMap>
<select id="selectCategoryAndInfoByPIDQ" resultMap="piq">
select * from category where c_id = #{cId}
</select>
</mapper>
InfoMapper.java
package com.itqf.mapper;
import com.itqf.entity.Info;
public interface InfoMapper {
Info selectInfoByIID(int iid);
Info selectInfoAndCategoryAndProdoctsByIIDQ(int iid);
}
InfoMapper.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.itqf.mapper.InfoMapper">
<!--Info selectInfoAndCategoryAndProdoctsByIIDQ(int iid);-->
<resultMap id="infoMapQ" type="info">
<id column="i_id" property="iId"/>
<collection property="categories" ofType="category" column="i_id" select="com.itqf.mapper.CategoryMapper.selectCategoryAndProdoctsByIIDQ"/>
</resultMap>
<select id="selectInfoAndCategoryAndProdoctsByIIDQ" resultMap="infoMapQ">
select * from info where i_id = #{iid};
</select>
<!--Info selectInfoByIID(int iid);-->
<select id="selectInfoByIID" resultType="info">
select * from info where i_id = #{iId};
</select>
</mapper>
ProdoctMapper.java
package com.itqf.mapper;
import com.itqf.entity.Prodoct;
import java.util.List;
public interface ProdoctMapper {
List<Prodoct> selectProdoctsByCID(int cid);
Prodoct selectProdoctByPID(int pid);
Prodoct selectProdoctAndCategoryByPID(int pid);
Prodoct selectProdoctPIDQ(int pid);
Prodoct selectProdoctAndCategoryAndInfoByPIDQ(int pid);
}
ProdoctMapper.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.itqf.mapper.ProdoctMapper">
<!--单表的商品查询 -->
<select id="selectProdoctByPID" resultType="prodoct">
select * from prodoct where p_id = #{pId}
</select>
<!--<sql id="ps">-->
<!-- p.*,c.c_name,c.i_id-->
<!--</sql>-->
<resultMap id="prodoctmap" type="prodoct">
<id column="p_id" property="pId"/>
<result column="p_name" property="pName" />
<result column="c_id" property="cId" />
<!-- 给对象赋值 property对象属性 javatype对象的类型 -->
<association property="category" javaType="category">
<id column="c_id" property="cId" />
<result column="c_name" property="cName" />
<result column="i_id" property="iId" />
</association>
</resultMap>
<!--多表查询 嵌套结果集 (连接查询) -->
<select id="selectProdoctAndCategoryByPID" resultMap="prodoctmap">
select p_id, p_name, c.c_id, c_name, i_id
from prodoct p join category c
on p.c_id = c.c_id
where p.p_id = #{pid}
</select>
<!--Prodoct selectProdoctPIDQ(int pid);-->
<resultMap id="qprodoctMap" type="prodoct">
<id column="p_id" property="pId"/>
<result column="c_id" property="cId"/>
<association property="category" javaType="category" column="c_id" select="com.itqf.mapper.CategoryMapper.selectCategoryByCid"/>
</resultMap>
<select id="selectProdoctPIDQ" resultMap="qprodoctMap">
select * from prodoct where p_id = #{pid}
</select>
<!--List<Prodoct> selectProdoctsByCID(int cid);-->
<select id="selectProdoctsByCID" resultType="prodoct">
select * from prodoct where c_id = #{cid}
</select>
<!--Prodoct selectProdoctAndCategoryAndInfoByPIDQ(int pid);-->
<resultMap id="pci" type="prodoct">
<id column="p_id" property="pId"/>
<result column="c_id" property="cId"/>
<association property="category" javaType="category" column="c_id" select="com.itqf.mapper.CategoryMapper.selectCategoryAndInfoByPIDQ"/>
</resultMap>
<select id="selectProdoctAndCategoryAndInfoByPIDQ" resultMap="pci">
select * from prodoct where p_id = #{pid};
</select>
</mapper>
test
MybatisTest.java
package com.itqf.test;
import com.itqf.entity.Category;
import com.itqf.entity.Info;
import com.itqf.entity.Prodoct;
import com.itqf.mapper.CategoryMapper;
import com.itqf.mapper.InfoMapper;
import com.itqf.mapper.ProdoctMapper;
import com.itqf.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
public class MybatisTest {
@Test
public void test1() throws IOException {
//单表商品查询
SqlSession sqlSession = MybatisUtils.openSqlsession();
ProdoctMapper mapper = sqlSession.getMapper(ProdoctMapper.class);
Prodoct product = mapper.selectProdoctByPID(1);
System.out.println("product = " + product);
MybatisUtils.commitAndClose(sqlSession);
}
@Test
public void test2() throws IOException {
//多表商品查询
SqlSession sqlSession = MybatisUtils.openSqlsession();
ProdoctMapper mapper = sqlSession.getMapper(ProdoctMapper.class);
Prodoct product = mapper.selectProdoctAndCategoryByPID(1);
System.out.println("product = " + product);
System.out.println("product.getCategory() = " + product.getCategory());
MybatisUtils.commitAndClose(sqlSession);
}
@Test
public void test3() throws IOException {
//单表查询类别
SqlSession sqlSession = MybatisUtils.openSqlsession();
CategoryMapper mapper = sqlSession.getMapper(CategoryMapper.class);
Category category = mapper.selectCategoryByCid(1);
System.out.println("category = " + category);
MybatisUtils.commitAndClose(sqlSession);
}
@Test
public void test4() throws IOException {
//多表查询类别
SqlSession sqlSession = MybatisUtils.openSqlsession();
CategoryMapper mapper = sqlSession.getMapper(CategoryMapper.class);
Category category = mapper.selectCategoryAndProductByCid(1);
System.out.println("category = " + category);
MybatisUtils.commitAndClose(sqlSession);
}
@Test
public void test5() throws IOException {
//多表查询类别
SqlSession sqlSession = MybatisUtils.openSqlsession();
ProdoctMapper mapper = sqlSession.getMapper(ProdoctMapper.class);
Prodoct prodoct = mapper.selectProdoctPIDQ(1);
System.out.println("prodoct = " + prodoct);
MybatisUtils.commitAndClose(sqlSession);
}
@Test
public void test6() throws IOException {
//多表查询类别
SqlSession sqlSession = MybatisUtils.openSqlsession();
CategoryMapper mapper = sqlSession.getMapper(CategoryMapper.class);
Category category = mapper.selectCategoryAndProdoctsByCIDQ(1);
System.out.println("category = " + category);
MybatisUtils.commitAndClose(sqlSession);
}
@Test
public void test7() throws IOException {
//多表查询类别
SqlSession sqlSession = MybatisUtils.openSqlsession();
InfoMapper mapper = sqlSession.getMapper(InfoMapper.class);
Info info = mapper.selectInfoAndCategoryAndProdoctsByIIDQ(1);
System.out.println("info = " + info);
MybatisUtils.commitAndClose(sqlSession);
}
@Test
public void test8() throws IOException {
//多表查询类别
SqlSession sqlSession = MybatisUtils.openSqlsession();
ProdoctMapper mapper = sqlSession.getMapper(ProdoctMapper.class);
Prodoct prodoct = mapper.selectProdoctAndCategoryAndInfoByPIDQ(1);
System.out.println("prodoct = " + prodoct);
MybatisUtils.commitAndClose(sqlSession);
}
}
utils
MybatisUtils.java
package com.itqf.utils;
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 MybatisUtils {
private static SqlSessionFactory factory = null;
static {
//使用ibatis的方案添加二狗子
InputStream resourceAsStream = null;
try {
resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
e.printStackTrace();
}
//2.创建sqlsesssionfbuiler
SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
//3.创建sqlsessionfactory
factory = factoryBuilder.build(resourceAsStream);
}
public static SqlSession openSqlsession() throws IOException {
//4.创建sqlsesssion
SqlSession sqlSession = factory.openSession();
return sqlSession;
}
public static void commitAndClose(SqlSession sqlSession){
sqlSession.commit();
sqlSession.close();
}
}
resources
jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///store
user=root
password=dong1995
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>
<!-- 配置mybati的核心属性-->
<!--读取外部的配置文件,或者直接声明外部配置文件 -->
<properties resource="jdbc.properties">
<!-- ${key} 可以引入配置文件的内容
可以在内部使用property标签进行声明属性!
后面也是正常引用!
注意:如果name和外部配置文件的命名相同!
内部新声明会覆盖外部!
ctrl+shit + a
registry
ide.suppess.double.clik 勾选上!
-->
<!-- <property name="user" value="hehe"/>-->
</properties>
<settings>
<!-- 告诉mybatis开启后台的sql语句输出!
-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<setting name="returnInstanceForEmptyRow" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- FULL多层查询也会映射 PRE单表查询 NONE 根本不会映射 都需要手动配置-->
<setting name="autoMappingBehavior" value="FULL"/>
</settings>
<typeAliases>
<!-- 指定具体的类起别名 -->
<!-- <typeAlias type="com.itqf.entity.Account" alias="account" />-->
<!-- 给包下的所有类起别名 默认:类的首字母小写 XxXx xxXx -->
<!-- 不想用默认命名 @Alias 一定要加载package - name对应的包的类中-->
<package name="com.itqf.entity"/>
</typeAliases>
<!-- 配置具体的数据库连接!-->
<!-- 可以选择当前使用的环境 数据库 -->
<environments default="dev">
<!-- 一个数据库-->
<environment id="dev">
<!-- 是否需要mybatis进行事务管理 -->
<!-- MyBatis 中有两种类型的事务管理器(也就是 type=”[JDBC|MANAGED]”):
jdbc:mybatis进行事务管理
managed:mybatis放弃事务管理!
spring+mybatis spring事务管理模块! tx!会覆盖mybatis的是事务管理模块
mybatis后期没有必要配置事务管理!
-->
<transactionManager type="JDBC">
</transactionManager>
<!--连接的数据库的信息 driver url user password -->
<!--
type: UNPOOLED POOLED
unpooled代表每次请求都创建一个连接和使用完毕销毁一个连接!(没有使用连接池!)
driver – 这是 JDBC 驱动的 Java 类的完全限定名(并不是 JDBC 驱动中可能包含的数据源类)。
url – 这是数据库的 JDBC URL 地址。
username – 登录数据库的用户名。
password – 登录数据库的密码。
defaultTransactionIsolationLevel – 默认的连接事务隔离级别。
POOLED:使用连接池的概念!mybatis内部封装了连接池!
每次获取,使用完放入连接池!这样效率更高!
pooled代表要使用连接池!所以他包含unpooled属性设置(数据库的基本连接信息)
同时也支持连接池的一些属性设置!
除了上述提到 UNPOOLED 下的属性外,还有更多属性用来配置 POOLED 的数据源:
poolMaximumActiveConnections – 在任意时间可以存在的活动(也就是正在使用)连接数量,默认值:10
poolMaximumIdleConnections – 任意时间可能存在的空闲连接数。
poolMaximumCheckoutTime – 在被强制返回之前,池中连接被检出(checked out)时间,默认值:20000 毫秒(即 20 秒)
poolTimeToWait – 这是一个底层设置,如果获取连接花费了相当长的时间,连接池会打印状态日志并重新尝试获取一个连接(避免在误配置的情况下一直安静的失败),默认值:20000 毫秒(即 20 秒)。
poolMaximumLocalBadConnectionTolerance – 这是一个关于坏连接容忍度的底层设置, 作用于每一个尝试从缓存池获取连接的线程。 如果这个线程获取到的是一个坏的连接,那么这个数据源允许这个线程尝试重新获取一个新的连接,但是这个重新尝试的次数不应该超过 poolMaximumIdleConnections 与 poolMaximumLocalBadConnectionTolerance 之和。 默认值:3 (新增于 3.4.5)
poolPingQuery – 发送到数据库的侦测查询,用来检验连接是否正常工作并准备接受请求。默认是“NO PING QUERY SET”,这会导致多数数据库驱动失败时带有一个恰当的错误消息。
poolPingEnabled – 是否启用侦测查询。若开启,需要设置 poolPingQuery 属性为一个可执行的 SQL 语句(最好是一个速度非常快的 SQL 语句),默认值:false。
poolPingConnectionsNotUsedFor – 配置 poolPingQuery 的频率。可以被设置为和数据库连接超时时间一样,来避免不必要的侦测,默认值:0(即所有连接每一时刻都被侦测 — 当然仅当 poolPingEnabled 为 true 时适用)。
-->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 将mapper中sql语句和mybatis的核心类连接到!-->
<mappers>
<!--每次只找到一个mapper.xml -->
<!-- <mapper resource="com/itqf/mapper/AccountMapper.xml" />-->
<!-- 如果使用扫描!要求接口和mapper.xml命名必须一致-->
<package name="com.itqf.mapper"/>
</mappers>
</configuration>
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.itqf</groupId>
<artifactId>mybatis1031</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>3.2.4</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>