Mybatis多表聯(lián)合查找
-
加入依賴
加入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文件
-
導(dǎo)入db.properties資源文件,如下
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3307/shop_mybatis username=root password=970809 設(shè)置settings,加入自動日志
設(shè)置typeAliases,設(shè)置別名包,此包下的引用可直接寫類名,忽略大小寫
設(shè)置連接池屬性POOLED
在mappers標(biāo)簽下設(shè)置對應(yīng)的映射文件路徑
<?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不需要添加第三方j(luò)ar包--> <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標(biāo)簽進行查詢操作,其他操作分別為update,insert,delete
使用resultMap進行聯(lián)合查詢
resultMap標(biāo)簽下 如果對應(yīng)pojo內(nèi)的屬性名(property)和數(shù)據(jù)庫表中的列名相同,可省略不寫result標(biāo)簽
使用association標(biāo)簽進行對一的表結(jié)構(gòu)聯(lián)合查詢,使用Collection標(biāo)簽進行對多的表結(jié)構(gòu)聯(lián)合查詢
<?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中填寫對應(yīng)的映射文件的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); } }
