The relationships we mainly learn today are one-to-one relationships and one-to-many relationships.
1. One-to-one relationship
Let’s explain it through examples. (A wife corresponds to a husband).
1) Database information
create table t_wife(id int primary key auto_increment,wife_name varchar(),fk_husband_id int);create table t_husband(id int primary key auto_increment,husband_name varchar());insert into t_husband values (null,'hello');insert into t_wife values(null,'kitty',)
2) Corresponding JavaBean code
Although there are only one foreign keys configured by one party in the database, this one-to-one relationship is a two-way relationship.
HusbandBean.java
package com.cy.mybatis.beans;import java.io.Serializable;/*** one to one* @author acer**/public class HusbandBean implements Serializable{private static final long serialVersionUID = L;private Integer id;private String name;private WifeBean wife;public HusbandBean() {super();}public HusbandBean(Integer id, String name, WifeBean wife) {super();this.id = id;this.name = name;this.wife = wife;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public WifeBean getWife() {return wife;}public void setWife(WifeBean wife) {this.wife = wife;}@Overridepublic String toString() {return "Husband [id=" + id + ", name=" + name + ", wife=" + wife + "]";}} WifeBean.java
package com.cy.mybatis.beans;import java.io.Serializable;/*** one to one* @author acer**/public class WifeBean implements Serializable{private static final long serialVersionUID = L;private Integer id;private String name;private HusbandBean husband;public WifeBean() {super();}public WifeBean(Integer id, String name, HusbandBean husband) {super();this.id = id;this.name = name;this.husband = husband;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public HusbandBean getHusband() {return husband;}public void setHusband(HusbandBean husband) {this.husband = husband;}@Overridepublic String toString() {return "Wife [id=" + id + ", name=" + name + ", husband=" + husband+ "]";}} 3) Next, establish two interfaces, HusbandMapper and WifeMapper.
HusbandMapper
package com.cy.mybatis.mapper;import com.cy.mybatis.beans.HusbandBean;public interface HusbandMapper {/***Query husband information based on id* @param id* @return* @throws Exception*/public HusbandBean selectHusbandById (int id) throws Exception;/***Query husband and wife information based on id* @param id* @return* @throws Exception*/public HusbandBean selectHusbandAndWife(int id) throws Exception;} 4) Define the HusbandMapper.xml file
<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.HusbandMapper"><resultMap type="HusbandBean" id="husbandAndWife"><id property="id" column="id" javaType="java.lang.Integer"/><result property="name" column="name" javaType="java.lang.String"/><!-- association A complex type association; many results will be packaged into this type embedded result map's association, or refer to a column="id" The id here refers to the primary key id from the t_wife table. This query wife, so there is a method in the wife mapper--><association property="wife" column="id" javaType="WifeBean" select="com.cy.mybatis.mapper.WifeMapper.selectWifeByHusbandId" ></association></resultMap><!-- resultType Returns the fully qualified name or alias of the class of the expected type returned from this statement. --><select id="selectHusbandById" resultType="HusbandBean">select * from t_husband where id=#{id}</select> <!-- resultMap Name references external resultMap. Returns a collection. --><select id="selectHusbandAndWife" resultMap="husbandAndWife">select * from t_husband where id=#{id}</select></mapper>There is a method in WifeMapper.xml
<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.WifeMapper"><select id="selectWifeByHusbandId" resultType="WifeBean">select * from t_wife where fk_husband_id = #{id}</select> </mapper> 5) Write an implementation
package com.cy.mybatis.service;import org.apache.ibatis.session.SqlSession;import com.cy.mybatis.beans.HusbandBean;import com.cy.mybatis.mapper.HusbandMapper;import com.cy.mybatis.tools.DBTools;public class OneToOneService {public static void main(String[] args) {selectHusbandAndWife();}private static void selectHusbandAndWife() {SqlSession session = DBTools.getSession();HusbandMapper hm = session.getMapper(HusbandMapper.class);try {HusbandBean husband = hm.selectHusbandAndWife();System.out.println(husband);session.commit();} catch (Exception e) {e.printStackTrace();}}} Note: The tool class was written in the previous chapter, which is quite similar to that established on yesterday's basis.
Notice:
Mybatis is actually operating on XML. All our methods are directly defined in XML. Writing an interface is just to better conform to our three-layer thinking. If you don’t write an interface, you can directly operate the methods in XML through session.
As long as there are methods in XML, it can be used, and the method of calling is: namespace+ method name;
When using resultType for exception, be sure to ensure that your attribute name is the same as the field name;
If it is not the same, use resultMap.
2. One-to-many relationship
Let’s explain it through examples. (One lock corresponds to multiple keys).
2.1) There is no data added here in the database information, we use batch to add data
create table t_key(id int primary key auto_increment,key_name varchar(),fk_lock_id int ); create table t_lock(id int primary key auto_increment,lock_name varchar());
2.2) Entity Class
KeyBean.java
package com.cy.mybatis.beans;import java.io.Serializable;/*** manyTOone* **/public class KeyBean implements Serializable {private static final long serialVersionUID = L;private Integer id;private String key;private LockBean lock;public KeyBean() {super();}public KeyBean(Integer id, String key, LockBean lock) {super();this.id = id;this.key = key;this.lock = lock;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getKey() {return key;}public void setKey(String key) {this.key = key;}public LockBean getLock() {return lock;}public void setLock(LockBean lock) {this.lock = lock;}@Overridepublic String toString() {return "KeyBean [id=" + id + ", key=" + key + ", lock=" + lock + "]";}} LockBean.javapackage com.cy.mybatis.beans;import java.io.Serializable;import java.util.List;/*** oneTOmany* **/public class LockBean implements Serializable{private static final long serialVersionUID = L;private Integer id;private String lock;private List<KeyBean> keys;public LockBean() {super();}public LockBean(Integer id, String lock, List<KeyBean> keys) {super();this.id = id;this.lock = lock;this.keys = keys;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getLock() {return lock;}public void setLock(String lock) {this.lock = lock;}public List<KeyBean> getKeys() {return keys;}public void setKeys(List<KeyBean> keys) {this.keys = keys;}@Overridepublic String toString() {return "LockBean [id=" + id + ", keys=" + keys + ", lock=" + lock + "]";}} 2.3) Establish an interface
KeyMapper.javapackage com.cy.mybatis.mapper;import java.util.List;import org.apache.ibatis.annotations.Param;import com.cy.mybatis.beans.KeyBean;public interface KeyMapper {/*** Add keys in batch* @return* It is recommended to use @Param("keys")*/public int batchSaveKeys(@Param("keys")List<KeyBean> keys);} LockMapper.javapackage com.cy.mybatis.mapper;import org.apache.ibatis.annotations.Param;import com.cy.mybatis.beans.LockBean;public interface LockMapper {/*** Add lock* @param lock* @return*/public int saveLock(@Param("lock")LockBean lock);/*** Query the information of the lock based on the ID* @param id* @return*/public LockBean findLockById(int id);/*** Query the information of the lock and key based on the ID* onemany* @param id* @return*/public LockBean findLockAndKeys(int id);} 2.4) Create an xml file
KeyMapper.xml
<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.KeyMapper"><resultMap id="keyMap" type="KeyBean"><id property="id" column="id" javaType="java.lang.Integer"/><result property="key" column="key_name" javaType="java.lang.String"/></resultMap><!--collection is an element used for traversal (required), supporting arrays, List, Set --><!-- item represents the alias when each element in the collection is iterated. --><!--separator indicates what symbol is used as the separator between each iteration. --><insert id="batchSaveKeys">insert into t_key values <foreach collection="keys" item="key" separator=",">(null,#{key.key},#{key.lock.id})</foreach></insert><select id="findKeysByLockId" resultMap="keyMap">select * from t_key where fk_lock_id = #{id}</select></mapper> LockMapper.xml<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.LockMapper"><!--Custom return type--><resultMap id="lockMap" type="LockBean"><id property="id" column="id" javaType="java.lang.Integer"/><result property="lock" column="lock_name" javaType="java.lang.String"/><result property="lock" column="lock_name" javaType="java.lang.String"/><collection property="keys" column="id" select="com.cy.mybatis.mapper.KeyMapper.findKeysByLockId"></collection></resultMap><insert id="saveLock">insert into t_lock values (null,#{lock.lock}) </insert><select id="findLockById" resultMap="lockMap">select * from t_lock where id= #{id}</select><select id="findLockAndKeys" resultMap="lockAndKeysMap">select * from t_lock where id= #{id}</select></mapper> 2.5) Implementation
package com.cy.mybatis.service;import java.util.ArrayList;import java.util.List;import org.apache.ibatis.session.SqlSession;import com.cy.mybatis.beans.KeyBean;import com.cy.mybatis.beans.LockBean;import com.cy.mybatis.mapper.KeyMapper;import com.cy.mybatis.mapper.LockMapper;import com.cy.mybatis.tools.DBTools;public class OneToManyService {public static void main(String[] args) {// saveLock();// batchSaveKeys(); findLockAndKeys();}private static void findLockAndKeys() {SqlSession session = DBTools.getSession();LockMapper lm = session.getMapper(LockMapper.class);LockBean lock = lm.findLockAndKeys();System.out.println(lock);}private static void batchSaveKeys() {SqlSession session = DBTools.getSession();LockMapper lm = session.getMapper(LockMapper.class);KeyMapper km = session.getMapper(KeyMapper.class);LockBean lock = lm.findLockById();List<KeyBean> keys = new ArrayList<KeyBean>();for(int i = ; i < ; i++){KeyBean key = new KeyBean(null, "key"+i, lock);keys.add(key);}km.batchSaveKeys(keys);session.commit();}private static void saveLock() {SqlSession session = DBTools.getSession();LockMapper lm = session.getMapper(LockMapper.class);LockBean lock = new LockBean(null, "lock", null);lm.saveLock(lock);session.commit();}} The results show:
3. Batch operation and pagination
Here I will use the User from the previous chapter and write the main code.
First define the paging object.
package com.cy.mybatis.beans;import java.util.List;/*** Define a pagination object* * @author* */public class Pager {private int pageNo;// Current page number private int pageTotal pageTotal;// Total page number private int rowsTotal;// Total number of entries private int pageSize;// Number of displayed entries per page private List<Object> list;// Returned data set public int getPageNo() {return pageNo;}public void setPageNo(int pageNo) {this.pageNo = pageNo;}public int getPageTotal() {return pageTotal;}public void setPageTotal(int pageTotal) {this.pageTotal = pageTotal;}public int getRowsTotal() {return rowsTotal;}public void setRowsTotal(int rowsTotal) {this.rowsTotal = rowsTotal;pageTotal = rowsTotal % pageSize == ? rowsTotal / pageSize : rowsTotal / pageSize + ;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public List<?> getList() {return list;}public void setList(List<Object> list) {this.list = list;}@Overridepublic String toString() {return "Pager [pageNo=" + pageNo + ", pageTotal=" + pageTotal+ ", rowsTotal=" + rowsTotal + ", pageSize=" + pageSize+ ", list=" + list + "]";}} UserMapper.java interface. package com.cy.mybatis.mapper;import java.util.List;import java.util.Map;import org.apache.ibatis.annotations.Param;import com.cy.mybatis.beans.UserBean;public interface UserMapper {/*** Add * @param user* @return* @throws Exception*/public int insertUser(@Param("user")UserBean user) throws Exception;/*** Use * @param user* @param id* @return* @throws Exception*/public int updateUser (@Param("u")UserBean user,@Param("id")int id) throws Exception;/*** Exception* @param id* @return* @throws Exception*/public int deleteUser(int id) throws Exception;/*** Query user information based on id* @param id* @return* @throws Exception*/public UserBean selectUserById(int id) throws Exception;/*** Query all user information* @return* @throws Exception*/public List<UserBean> selectAllUser() throws Exception;/*** Batch Add* @param user* @return* @throws Exception*/public int batchInsertUser(@Param("users")List<UserBean> user) throws Exception;/*** Batch Delete* @param list* @return* @throws Exception*/public int batchDeleteUser(@Param("list")List<Integer> list) throws Exception;/*** Paging query data* @param parma* @return* @throws Exception*/public List<UserBean> pagerUser(Map<String, Object> parmas) throws Exception;/*** * Pagination Statistics* @param parma* @return* @throws Exception*/public int countUser(Map<String, Object> parmas) throws Exception;} xml file<?xml version="." encoding="UTF-"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd"><mapper namespace="com.cy.mybatis.mapper.UserMapper"><!-- Custom return result set--><resultMap id="userMap" type="UserBean"><id property="id" column="id" javaType="java.lang.Integer"></id><result property="username" column="username" javaType="java.lang.String"></result><result property="password" column="password" javaType="java.lang.String"></result><result property="account" column="account" javaType="java.lang.Double"></result></resultMap><!-- The id attribute in various tags must be the same as the method name in the interface, and the id attribute value must be unique and cannot be reused. The parameterType property specifies the parameter type used when querying, and the resultType property specifies the result set type returned by the query --> <!-- useGeneratedKeys: (It is only useful for insert) This tells MyBatis to use the JDBC's getGeneratedKeys method to retrieve the primary key generated internally by data (for example, the auto-increment field of database management systems like MySQL and SQLServer). Default value: false. --> <!--keyProperty: (useful only for insert) Mark a property. MyBatis will set its value through getGeneratedKeys or through the selectKey child element of the insert statement. Default: Not set. --><!--#{} content is a placeholder. When the parameter is a JavaBean, it indicates the property value of the bean object that is placed--><insert id="insertUser" useGeneratedKeys="true" keyProperty="user.id">insert into t_user (username,password,account) values (#{user.username},#{user.password},#{user.account})</insert><update id="updateUser">update t_user set username=#{u.username},password=#{u.password},account=#{u.account} where id=#{id}</update><delete id="deleteUser" parameterType="int">delete from t_user where id=#{id} </delete><select id="selectUserById" parameterType="int" resultMap="userMap">select * from t_user where id=#{id}</select><select id="selectAllUser" resultMap="userMap">select * from t_user</select><!-- Batch Operations and Foreach Tags--><insert id="batchInsertUser" parameterType="java.util.List">insert into t_user values <foreach collection="users" item="users" separator=",">(null,#{users.username},#{users.password},#{users.account})</foreach></insert><delete id="batchDeleteUser">delete from t_user where id in (<foreach collection="list" item="list" separator=",">#{id}</foreach>)</delete><!--collection is an element used for traversal (required), supporting arrays, List, Set --><!-- item represents the alias when each element in the set is iterated. --><!--separator represents what symbol is used as the separator between each iteration. --><select id="pagerUser" parameterType="java.util.Map" resultMap="userMap">select * from t_user where =<if test="username!=null">and username like '%${username}%'</if>limit ${index},${pageSize} </select><select id="countUser" parameterType="java.util.Map" resultType="int">select count(*) from t_user where = <if test="username != null">and username like '%${username}%' </if></select></mapper> #When generating SQL, quotes will be assembled for character type parameters. $When generating SQL, quotes will not be assembled. It can be used for parameter assembly test classes such as order by package com.cy.mybatis.service;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.ibatis.session.SqlSession;import com.cy.mybatis.beans.UserBean;import com.cy.mybatis.tools.DBTools;import com.cy.mybatis.mapper.UserMapper;public class UserService {/*** @param args*/public static void main(String[] args) {// insertUser();// deleteUser();// updateUser();// selectUserById();// selectAllUser();// batchInsertUser();// batchDeleteUser();// countUser();pagerUser();}private static void countUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);Map<String,Object> params = new HashMap<String,Object>();params.put("username", "kitty");int index = ;params.put("index", index);//From which page to start. mysql starts with params.put("pageSize", );//The number of data strips displayed per page int county;try {count = mapper.countUser(params);System.out.println(count);} catch (Exception e) {e.printStackTrace();}}private static void pagerUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);Map<String,Object> params = new HashMap<String,Object>();params.put("username", "kitty");params.put("index", );//From which page. mysql starts with params.put("pageSize", );//The number of data strips displayed per page try {List<UserBean> u = mapper.pagerUser(params); for (UserBean userBean : u) {System.out.println("----------"+userBean);}} catch (Exception e) {e.printStackTrace();}} private static void batchDeleteUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);List<Integer> ids = new ArrayList<Integer>();for(int i = ; i < ; i ++){ids.add(i);}try {mapper.batchDeleteUser(ids);session.commit();} catch (Exception e) {e.printStackTrace();}}private static void batchInsertUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);List<UserBean> users = new ArrayList<UserBean>();for(int i = ; i < ; i ++){UserBean user = new UserBean("kitty"+i, "", .);users.add(user);}try {mapper.batchInsertUser(users);session.commit();} catch (Exception e) {e.printStackTrace();}}/*** New user*/private static void insertUser() {SqlSession session = DBTools.getSession();UserMapper mapper = session.getMapper(UserMapper.class);UserBean user = new UserBean("Yi", "", .);try {mapper.insertUser(user);System.out.println(user.toString());session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** Delete user*/private static void deleteUser(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);try {mapper.deleteUser();session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** Modify user data*/private static void updateUser(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);UserBean user =new UserBean("Xiao Ming", "",.);try {mapper.updateUser(user, );session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** Query user by id*/private static void selectUserById(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);try {UserBean user= mapper.selectUserById();System.out.println(user.toString());session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}}/*** Query all users*/private static void selectAllUser(){SqlSession session=DBTools.getSession();UserMapper mapper=session.getMapper(UserMapper.class);try {List<UserBean> user=mapper.selectAllUser();System.out.println(user.toString());session.commit();} catch (Exception e) {e.printStackTrace();session.rollback();}} } Take a look at the overall project:
Everything needs to be persisted in!