Mybatis多表联合查找
-
加入依赖
加入mybatis核心依赖,junit测试,mysql依赖
<dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency>
-
XML配置
resources目录下新建Mybatis.xml文件
-
导入db.properties资源文件,如下
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3307/shop_mybatis username=root password=970809 设置settings,加入自动日志
设置typeAliases,设置别名包,此包下的引用可直接写类名,忽略大小写
设置连接池属性POOLED
在mappers标签下设置对应的映射文件路径
<?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> <properties resource="db.properties"/> <!--添加日志功能，STDOUT_LOGGING不需要添加第三方jar包--> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <typeAliases> <package name="pojo"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/mapper/OrderMapper.xml"/> <mapper resource="com/mapper/UserMapper.xml"/> <mapper resource="com/mapper/DetailMapper.xml"/> <mapper resource="com/mapper/ProductMapper.xml"/> <mapper resource="com/mapper/TypeMapper.xml"/> </mappers> </configuration>
-
OrderMapper.xml配置例举
namespace和id组合唯一,可自定义
使用select标签进行查询操作,其他操作分别为update,insert,delete
使用resultMap进行联合查询
resultMap标签下 如果对应pojo内的属性名(property)和数据库表中的列名相同,可省略不写result标签
使用association标签进行对一的表结构联合查询,使用Collection标签进行对多的表结构联合查询
<?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="pojo.OrderMapper"> <select id="getOrderByOid" resultMap="orderMap"> select * from orders where oid = #{oid} </select> <resultMap id="orderMap" type="Order"> <!-- <id property="oid" column="oid"/>--> <!-- <result column="price" property="price"/>--> <!-- <result column="addr" property="addr"/>--> <!-- <result column="payType" property="payType"/>--> <!--对一,select中填写对应的映射文件的namespace.id--> <association property="user" column="uid" select="pojo.UserMapper.getUserByUid"/> <!--对多--> <collection property="details" column="did" select="pojo.DetailMapper.getDetailByDid" ofType="pojo.Detail"/> </resultMap> </mapper>
-
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="pojo.UserMapper"> <select id="getUserByUid" resultType="User"> select * from users where uid = #{uid} </select> </mapper>
-
DetailMapper.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="pojo.DetailMapper"> <select id="getDetailByDid" resultMap="detailMap"> select * from details where did = #{did} </select> <resultMap id="detailMap" type="Detail"> <!-- <id property="did" column="did"/>--> <!-- <result column="count" property="count"/>--> <association property="product" column="pid" select="pojo.ProductMapper.getProductByPid"/> </resultMap> </mapper>
-
ProductMapper.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="pojo.ProductMapper"> <select id="getProductByPid" resultMap="productMap"> select * from products where pid = #{pid} </select> <resultMap id="productMap" type="Product"> <!-- <id property="pid" column="pid"/>--> <!-- <result property="name" column="name"/>--> <!-- <result property="price" column="price"/>--> <!-- <result property="img" column="img"/>--> <association property="type" column="tid" select="pojo.TypeMapper.getTypeByTid"/> </resultMap> </mapper>
-
TypeMapper.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="pojo.TypeMapper"> <select id="getTypeByTid" resultType="Type"> select * from types where tid = #{tid} </select> </mapper>
-
测试类
使用junit的@Before注解,setUp在所有方法运行前运行
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.After; import org.junit.Before; import org.junit.Test; import pojo.Order; import java.io.IOException; public class TestOrders { private SqlSessionFactory sf = null; private SqlSession session = null; @Before public void setUp() { try { sf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("Mybatis.xml")); session = sf.openSession(); } catch (IOException e) { e.printStackTrace(); } } @After public void tearDown() { if (session != null) { session.close(); session = null; } } @Test public void testGetOrderByOid() { Order order = session.selectOne("pojo.OrderMapper.getOrderByOid",1); System.out.println(order); } }
