MyBatis學習筆記(二)之關聯關係

MyBatis學習筆記(二)之關聯關係

今天主要學習的關聯關係是一對一關係與一對多關係。

一、一對一關係

還是通過例子來解釋說明。(一個妻子對應一個丈夫)。

1)資料庫資訊


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)對應的JavaBean程式碼

雖然在資料庫裡只有一方配置的外來鍵,但是這個一對一是雙向的關係。

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;
}
@Override
public 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;
}
@Override
public String toString() {
return "Wife [id="   id   ", name="   name   ", husband="   husband
"]";
}
} 

3)接下來建立兩個介面,HusbandMapper,WifeMapper.

HusbandMapper


package com.cy.mybatis.mapper;
import com.cy.mybatis.beans.HusbandBean;
public interface HusbandMapper {
/**
* 根據id查詢丈夫資訊
* @param id
* @return
* @throws Exception
*/
public HusbandBean selectHusbandById (int id) throws Exception;
/**
* 根據id查詢丈夫與妻子資訊
* @param id
* @return
* @throws Exception
*/
public HusbandBean selectHusbandAndWife(int id) throws Exception;
} 

4)定義HusbandMapper.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.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 – 一個複雜的型別關聯;許多結果將包成這種型別
嵌入結果對映 – 結果對映自身的關聯,或者參考一個
column="id" 這裡的id指的是在t_wife表來的主鍵id 
這個查詢妻子,所以在妻子mapper裡有個方法 -->
<association property="wife" column="id" javaType="WifeBean" select="com.cy.mybatis.mapper.WifeMapper.selectWifeByHusbandId" ></association>
</resultMap>
<!-- resultType 返回型別 從這條語句中返回的期望型別的類的完全限定名或別名
。-->
<select id="selectHusbandById" resultType="HusbandBean">
select * from t_husband where id=#{id}
</select> 
<!-- resultMap 命名引用外部的 resultMap。返回的是一個集合。-->
<select id="selectHusbandAndWife" resultMap="husbandAndWife">
select * from t_husband where id=#{id}
</select>
</mapper> 

在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)寫個實現


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

注意:那個工具類還是前一章那樣寫的,就相當與在昨天的基礎上建立的。

注意:

mybatis實際是對XML進行操作,我們所有的方法都直接定義在XML中,寫個介面只是為了更好的符合我們3層的思想,如果不寫介面,直接通過session也可以直接操作xml中的方法 ,

XML中只要有方法,就可以使用,而呼叫的方式就是:namespace 方法名;

例外使用resultType時,一定要保證,你屬性名與欄位名相同;

如果不相同,就使用resultMap 。

二、一對多關係

還是通過例子來解釋說明。(一把鎖對應多把鑰匙)。

2.1)資料庫資訊 這裡沒有新增資料了,我們用批量新增資料


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) 實體類

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;
}
@Override
public String toString() {
return "KeyBean [id="   id   ", key="   key   ", lock="   lock   "]";
}
} 
LockBean.java
package 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;
}
@Override
public String toString() {
return "LockBean [id="   id   ", keys="   keys   ", lock="   lock   "]";
}
} 

2.3) 建立介面


KeyMapper.java
package com.cy.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.cy.mybatis.beans.KeyBean;
public interface KeyMapper {
/**
* 批量新增鑰匙
* @return
* 提倡 這樣使用 @Param("keys")
*/
public int batchSaveKeys(@Param("keys")List<KeyBean> keys);
} 
LockMapper.java
package com.cy.mybatis.mapper;
import org.apache.ibatis.annotations.Param;
import com.cy.mybatis.beans.LockBean;
public interface LockMapper {
/**
* 新增鎖
* @param lock
* @return
*/
public int saveLock(@Param("lock")LockBean lock);
/**
* 根據ID查詢鎖的資料
* @param id
* @return
*/
public LockBean findLockById(int id);
/**
* 根據ID查詢鎖與鑰匙的資料
* onemany
* @param id
* @return
*/
public LockBean findLockAndKeys(int id);
} 

2.4) 建立xml檔案

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 為用於遍歷的元素(必選),支援陣列、List、Set -->
<!-- item 表示集合中每一個元素進行迭代時的別名. -->
<!--separator表示在每次進行迭代之間以什麼符號作為分隔 符. -->
<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">
<!--自定義返回型別 -->
<resultMap id="lockMap" type="LockBean">
<id property="id" column="id" javaType="java.lang.Integer"/>
<result property="lock" column="lock_name" javaType="java.lang.String"/>
</resultMap>
<!--自定義返回型別 -->
<resultMap id="lockAndKeysMap" type="LockBean">
<id property="id" column="id" javaType="java.lang.Integer"/>
<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 ) 實現


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, "鑰匙" 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, "鎖", null);
lm.saveLock(lock);
session.commit();
}
} 

結果顯示:

三 、批量操作與分頁

這裡就使用前一章的User.就寫出主要的程式碼。

首先定義分頁物件。


package com.cy.mybatis.beans;
import java.util.List;
/**
* 定義一個分頁物件
* 
* @author
* 
*/
public class Pager {
private int pageNo;// 當前頁碼
private int pageTotal;// 總頁碼
private int rowsTotal;// 總條數
private int pageSize;// 每頁顯示條數
private List<Object> list;// 返回的資料集合
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;
}
@Override
public String toString() {
return "Pager [pageNo="   pageNo   ", pageTotal="   pageTotal
", rowsTotal="   rowsTotal   ", pageSize="   pageSize
", list="   list   "]";
}
} 
UserMapper.java介面。
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 {
/**
* 新增使用者
* @param user
* @return
* @throws Exception
*/
public int insertUser(@Param("user")UserBean user) throws Exception;
/**
* 修改使用者
* @param user
* @param id
* @return
* @throws Exception
*/
public int updateUser (@Param("u")UserBean user,@Param("id")int id) throws Exception;
/**
* 刪除使用者
* @param id
* @return
* @throws Exception
*/
public int deleteUser(int id) throws Exception;
/**
* 根據id查詢使用者資訊
* @param id
* @return
* @throws Exception
*/
public UserBean selectUserById(int id) throws Exception;
/**
* 查詢所有的使用者資訊
* @return
* @throws Exception
*/
public List<UserBean> selectAllUser() throws Exception;
/**
* 批量增加
* @param user
* @return
* @throws Exception
*/
public int batchInsertUser(@Param("users")List<UserBean> user) throws Exception;
/**
* 批量刪除
* @param list
* @return
* @throws Exception
*/
public int batchDeleteUser(@Param("list")List<Integer> list) throws Exception;
/**
* 分頁查詢資料
* @param parma
* @return
* @throws Exception
*/
public List<UserBean> pagerUser(Map<String, Object> parmas) throws Exception;
/**
* 
* 分頁統計資料
* @param parma
* @return
* @throws Exception
*/
public int countUser(Map<String, Object> parmas) throws Exception;
} 
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.UserMapper">
<!-- 自定義返回結果集 -->
<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>
<!-- 在各種標籤中的id屬性必須和介面中的方法名相同 , id屬性值必須是唯一的,不能夠重複使用。parameterType屬性指明查詢時使用的引數型別,resultType屬性指明查詢返回的結果集型別--> 
<!-- useGeneratedKeys:( 僅 對 insert 有 用 ) 這 會 告 訴 MyBatis 使 用 JDBC 的getGeneratedKeys 
方法來取出由資料(比如:像 MySQL 和 SQLServer 這樣的資料庫管理系統的自動遞增欄位)內部生成的主鍵。預設值: false。 --> 
<!--keyProperty: (僅對 insert有用)標記一個屬性, MyBatis 會通過 getGeneratedKeys或者通過 insert 語句的 selectKey 子元素設定它的值。預設:不設定。 -->
<!--#{}中的內容,為佔位符,當引數為某個JavaBean時,表示放置該Bean物件的屬性值 -->
<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>
<!-- 批量操作和foreach標籤 -->
<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 為用於遍歷的元素(必選),支援陣列、List、Set -->
<!-- item 表示集合中每一個元素進行迭代時的別名. -->
<!--separator表示在每次進行迭代之間以什麼符號作為分隔 符. -->
<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> 
#在生成SQL時,對於字元型別引數,會拼裝引號
$在生成SQL時,不會拼裝引號,可用於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);//從第幾頁開始。mysql是從開始的
params.put("pageSize", );//每頁顯示的資料條數
int count;
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", );//從第幾頁開始。mysql是從開始的
params.put("pageSize", );//每頁顯示的資料條數
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();
}
}
/**
* 新增使用者
*/
private static void insertUser() {
SqlSession session = DBTools.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
UserBean user = new UserBean("懿", "", .);
try {
mapper.insertUser(user);
System.out.println(user.toString());
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}
}
/**
* 刪除使用者
*/
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();
}
}
/**
* 修改使用者資料
*/
private static void updateUser(){
SqlSession session=DBTools.getSession();
UserMapper mapper=session.getMapper(UserMapper.class);
UserBean user =new UserBean("小明", "",.);
try {
mapper.updateUser(user, );
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}
}
/**
* 根據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();
}
}
/**
* 查詢所有的使用者
*/
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();
}
} 
} 

看一下專案的整體:

每件事都需要堅持!

您可能感興趣的文章:

Mybatis 一對多和多對一關聯查詢問題Mybatis多表關聯查詢的實現(DEMO)深入淺出MyBatis中對映檔案和實體類的關聯性例項講解Java的MyBatis框架對MySQL中資料的關聯查詢MyBatis學習教程(五)-實現關聯表查詢方法詳解MyBatis實踐之動態SQL及關聯查詢mybatis實現表與物件的關聯關係_動力節點Java學院整理