MyBatis "Set nested Query" and "set nested result" two methods to implement database one-to-many relationship

The two entity classes are as follows: User class and goods commodity class. One user corresponds to multiple items (a pair of multiple)

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 commodity category

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;}}

Mapper mapping file for user entity class: 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"/><!--when the relationship between tables is a couple of long, collection--><!--here column= "U_ ID "is to pass parameters to nested query select=" ... "--><collection property=" goodslist "oftype=" Goods "column=" u_id "select=" Com.leo.mapper.GoodsDao.selectGoodsForUser "/></resultmap><!--Goodslist is a collection of private properties in the user entity class-->< Select Id= "Getuserinfobyid" parametertype= "int"  resultmap= "UserMap" >    

Goods Mapper mapping file for entity classes: 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" ><!-- This is the nested query map--   <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 Environment configuration file 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><!--I put the contents of the data source in the Db.properties file--><properties resource= "com/leo/resources/ Db.properties "/><!--start-type alias: An alias for Resulttype in Mapper.xml, looking not very redundant--><typealiases> <typealias alias= "User" type= "Com.leo.entity.User"/> <typealias alias= "Goods" type= "Com.leo.entity.Goods"/></          typealiases><!--end-type alias--><!--start-environments configuration-<environments default= "Development" > <environment id= "Development" > <transactionmanager type= "JDBC"/> <dataso                Urce type= "Pooled" > <property name= "Driver" value= "${driverclass}"/><!--data Source Configuration--     <property name= "url" value= "${url}"/> <property name= "username" value= "${username}"/>              <property name= "Password" value= "${password}"/> </dataSource> </environ Ment> </environments> <!--end-environments Configuration-<!--map file resources connected to the entity class-<ma ppers> <mapper resource= "Com/leo/entity/userdao.xml"/> <mapper resource= "Com/leo/entity/Good Sdao.xml "/> </mappers> </configuration>

Test servlet (can also be tested with the main function)

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;prote CTED 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);}}

The above is a collection of nested queries, there is also a way to set nested results, this way only requires an entity class file, it is a cascade query, automatic completion of the

Here's how to nest the results in a collection:

Just need to change userdao.xml, and just use this one mapping file can complete

<?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 "/><!--Two ways of difference is here, you can know the analysis of--></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, <!--nested results this way isA connection query was used, and the nested query used two times-->g.goodsname,g.goodsnumber,g.user_id Fromuser Uinner join goods g on u.id = g.user_id where u.id   =${value};</select></mapper>

Hope can help everyone, what wording is not correct, hope to be corrected, hope progress

MyBatis "Set nested Query" and "set nested result" two methods to implement database one-to-many relationship