mybatis“集合巢狀查詢”和“集合嵌套結果”兩種方法實現資料庫一對多關聯性,mybatis嵌套
兩個實體類分別如下:User使用者類和Goods商品類。一個使用者對應多個商品(一對多)
package com.leo.entity;import java.util.List;public class User {private Integer id;private String username;private Integer age;private String address;private List<Goods> goodsList;public List<Goods> getGoodsList() {return goodsList;}public void setGoodsList(List<Goods> goodsList) {this.goodsList = goodsList;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public User() {super();// TODO Auto-generated constructor stub}@Overridepublic String toString() {return "User [id=" + id + ", username=" + username + ", age=" + age+ ", address=" + address + ", goodsList=" + goodsList + "]";}}
Goods商品類
package com.leo.entity;public class Goods {private Integer id;private String goodsName;private Integer goodsNumber;private Integer user_id;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getGoodsName() {return goodsName;}public void setGoodsName(String goodsName) {this.goodsName = goodsName;}public Integer getGoodsNumber() {return goodsNumber;}public void setGoodsNumber(Integer goodsNumber) {this.goodsNumber = goodsNumber;}public Integer getUser_id() {return user_id;}public void setUser_id(Integer user_id) {this.user_id = user_id;}}
User實體類的mapper對應檔:UserDao.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.leo.mapper.UserDao"><resultMap type="User" id="userMap"><id column="u_id" property="id" /><result column="username" property="username" /><result column="age" property="age" /><result column="address" property="address" /><!--當表之間的關係是一對多時,用 collection--><!-- 這裡的 column="u_id"是為了傳參數到嵌套的查詢select="....."--><collection property="goodsList" ofType="Goods" column="u_id" select="com.leo.mapper.GoodsDao.selectGoodsForUser" /></resultMap><!--goodsList是User實體類中的 私人屬性集合 --><select id="getUserinfoById" parameterType="int" resultMap="userMap"> select u.id as u_id,u.username,u.age, u.address fromuser u where u.id =${value};</select></mapper>
Goods實體類的mapper對應檔:GoodsDao.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.leo.mapper.GoodsDao"><select id="selectGoodsForUser" parameterType="int" resultType="Goods"> SELECT id,goodsName,goodsNumber,user_id FROM Goods WHERE user_id = #{value}</select></mapper>
mabatis的環境設定檔mabatis-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><!-- 我把資料來源的內容放在db.properties檔案中 --><properties resource="com/leo/resources/db.properties" /><!--start-類型別名 :為mapper.xml中resultType取一個別名,看著不會很冗餘--><typeAliases> <typeAlias alias="User" type="com.leo.entity.User"/> <typeAlias alias="Goods" type="com.leo.entity.Goods"/></typeAliases><!-- end- 類型別名--><!-- start- environments配置 --> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driverClass}"/><!-- 資料來源配置 --> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <!-- end- environments配置 --> <!-- 串連到實體類的對應檔資源--> <mappers> <mapper resource="com/leo/entity/UserDao.xml" /> <mapper resource="com/leo/entity/GoodsDao.xml" /> </mappers> </configuration>
測試的servlet(也可以用main函數測試)
package com.leo.servlet;import java.io.IOException;import java.io.InputStream;import java.util.List;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import com.leo.entity.Goods;import com.leo.entity.User;import com.leo.mapper.GoodsDao;import com.leo.mapper.UserDao;/** * Servlet implementation class MybatisServlet */@WebServlet("/MybatisServlet")public class MybatisServlet extends HttpServlet {private static final long serialVersionUID = 1L;protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {InputStream is = Resources.getResourceAsStream("com/leo/resources/mybatis-config.xml");SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);SqlSession session = factory.openSession();//UserDao ud = session.getMapper(UserDao.class);GoodsDao gd = session.getMapper(GoodsDao.class);List<Goods> goodsList= gd.selectGoodsForUser(1);//User user = ud.getUserinfoById(1);//System.out.println(user);//List<Goods> goodsList = user.getGoodsList();for (Goods goods : goodsList) {System.out.println(goods.getId()+" "+ goods.getGoodsName()+" "+goods.getGoodsNumber()+ " "+ goods.getUser_id());}session.commit();session.close();}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doGet(request, response);}}
以上是集合巢狀查詢,還有一種方式是集合嵌套結果,這種方式只需要一個實體類檔案即可,它是一種級聯查詢,自動完成的
下面用集合嵌套結果這種方式:
只需要改動UserDao.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.leo.mapper.UserDao"><resultMap type="Goods" id="goodsMap"><id column="g_id" property="id"/><result column="goodsName" property="goodsName"/><result column="goodsNumber" property="goodsNumber"/><result column="user_id" property="user_id"/></resultMap><resultMap type="User" id="userMap"><id column="u_id" property="id" /><result column="username" property="username" /><result column="age" property="age" /><result column="address" property="address" /><collection property="goodsList" ofType="Goods" resultMap="goodsMap" /><!--兩種方式的不同之處在這裡,自己分析就可以知道--></resultMap><select id="getUserinfoById" parameterType="int" resultMap="userMap"> select u.id as u_id,u.username,u.age,u.address,g.id as g_id, <!--嵌套結果這種方式是使用了一次串連查詢,而巢狀查詢使用了兩次 -->g.goodsName,g.goodsNumber,g.user_id fromuser uinner join goods g on u.id = g.user_id where u.id =${value};</select></mapper>
希望可以幫到大家,有什麼措辭不正確,希望得到指正,希望進步