溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下MyBatis多表操作查詢功能怎么用，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
用戶表和訂單表的關(guān)系為，一個(gè)用戶多個(gè)訂單，一個(gè)訂單只從屬一個(gè)用戶
一對(duì)一查詢的需求：查詢一個(gè)訂單，與此同時(shí)查詢出該訂單所屬的用戶
在只查詢order表的時(shí)候，也要查詢user表，所以需要將所有數(shù)據(jù)全部查出進(jìn)行封裝SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id
創(chuàng)建Order和User實(shí)體
order
public class Order {
private int id;
private Date ordertime;
private double total;
//表示當(dāng)前訂單屬于哪一個(gè)用戶
private User user;user
public class User {
private int id;
private String username;
private String password;
private Date birthday;創(chuàng)建OrderMapper接口
public interface UserMapper {
//查詢?nèi)康姆椒?
public List<Order> findAll();
}配置OrderMapper.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.zg.mapper.OrderMapper" > <resultMap id="orderMap" type="order"> <!--手動(dòng)指定字段與實(shí)體的映射關(guān)系--> <!--comlumn:數(shù)據(jù)表的字段名稱 property：實(shí)體的屬性名稱--> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> <result column="uid" property="user.id"></result> <result column="username" property="user.username"></result> <result column="password" property="user.password"></result> <result column="birthday" property="user.birthday"></result> </resultMap> <!--這里不能使用resultType=“order”，因?yàn)閛rder中沒(méi)有user中的字段，只有一個(gè)user對(duì)象--> <select id="findAll" resultMap="orderMap"> SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id </select> </mapper>
sqlMapConfig.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">
<!--主要配置mybatis的核心配置-->
<configuration>
<!--通過(guò)properties標(biāo)簽加載外部properties文件-->
<properties resource="jdbc.properties"></properties>
<!--自定義別名-->
<typeAliases>
<typeAlias type="com.zg.domain.User" alias="user"></typeAlias>
<typeAlias type="com.zg.domain.Order" alias="order"></typeAlias>
</typeAliases>
<!--配置當(dāng)前數(shù)據(jù)源的環(huán)境-->
<environments default="developement">
<environment id="developement">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--加載映射文件-->
<mappers>
<mapper resource="com.zg.mapper/UserMapper.xml"></mapper>
<mapper resource="com.zg.mapper/OrderMapper.xml"></mapper>
</mappers>
</configuration>
在一對(duì)一配置的時(shí)候，在order實(shí)體中創(chuàng)建了一個(gè)user，所以property屬性都使用user.** 的方式進(jìn)行編寫(xiě)，但是這里還可以使用association
<?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.zg.mapper.OrderMapper" > <resultMap id="orderMap" type="order"> <!--手動(dòng)指定字段與實(shí)體的映射關(guān)系--> <!--comlumn:數(shù)據(jù)表的字段名稱 property：實(shí)體的屬性名稱--> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> <!--<result column="uid" property="user.id"></result> <result column="username" property="user.username"></result> <result column="password" property="user.password"></result> <result column="birthday" property="user.birthday"></result>--> <!--以上被封裝到user內(nèi)的還可以使用association進(jìn)行配置--> <!--association匹配的意思，在order中有個(gè)屬性叫user--> <!-- property="user"當(dāng)前order實(shí)體中的屬性名稱，javaType="user"當(dāng)前實(shí)體order中的屬性類型user--> <association property="user" javaType="user"> <id column="uid" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> </association> </resultMap> <!--這里不能使用resultType=“order”，因?yàn)閛rder中沒(méi)有user中的字段，只有一個(gè)user對(duì)象--> <select id="findAll" resultMap="orderMap"> SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id </select> </mapper>
用戶表和訂單表的關(guān)系為，一個(gè)用戶有多個(gè)訂單，一個(gè)當(dāng)?shù)粡膶僖粋€(gè)用戶
一對(duì)多查詢需求：查詢一個(gè)用戶，與此同時(shí)查詢出該用戶具有的訂單
package com.zg.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//描述當(dāng)前用戶存在哪些訂單
private List<Order> orderList;
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
修改User實(shí)體
package com.zg.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//描述當(dāng)前用戶存在哪些訂單
private List<Order> orderList;
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}創(chuàng)建UserMapper接口
package com.zg.mapper;
import com.zg.domain.User;
import java.util.List;
public interface UserMapper {
public List<User> findAll();
}配置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="com.zg.mapper.UserMapper" > <resultMap id="userMap" type="user"> <id column="uid" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> <!--配置集合信息--> <!--property：集合名稱 ofType：當(dāng)前集合中的數(shù)據(jù)類型--> <collection property="orderList" ofType="order"> <!--封裝order的數(shù)據(jù)--> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> </collection> </resultMap> <select id="findAll" resultMap="userMap"> select *,o.id oid from user u,orders o where u.id=o.uid </select> </mapper>
測(cè)試
@Test//測(cè)試一對(duì)多
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
用戶表和角色表的關(guān)系為，一個(gè)用戶有多個(gè)角色，一個(gè)角色被多個(gè)用戶使用
多對(duì)多查詢的需求：查詢用戶同時(shí)查詢?cè)撚脩舻乃薪巧?/p>
select * from user u,sys_user_role ur ,sys_role r where u.id=ur.userId and ur.roleId=r.id
創(chuàng)建Role實(shí)體，修改User實(shí)體
添加UserMapper接口
package com.zg.mapper;
import com.zg.domain.User;
import java.util.List;
public interface UserMapper {
public List<User> findAll();
public List<User> findUserAndRoleAll();
}配置UserMapper.xml
<resultMap id="userRoleMap" type="user"> <!--user信息的封裝--> <id column="userid" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> <!--user內(nèi)部的rolelist信息--> <collection property="roleList" ofType="role"> <id column="roleid" property="id"></id> <result column="roleName" property="roleName"></result> <result column="roleDesc" property="roleDesc"></result> </collection> </resultMap> <select id="findUserAndRoleAll" resultMap="userRoleMap"> select * from user u,sys_user_role ur ,sys_role r where u.id=ur.userId and ur.roleId=r.id </select>
測(cè)試代碼
@Test//測(cè)試多對(duì)多
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userAndRoleAll = mapper.findUserAndRoleAll();
for (User user : userAndRoleAll) {
System.out.println(user);
}
sqlSession.close();
}
看完了這篇文章，相信你對(duì)“MyBatis多表操作查詢功能怎么用”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼