Mybatis多表联合查询和Mybatis动态sql
一对一查询：
建表：
CREATE TABLE `servants` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`cla` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`address` varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE `master` (
`mid` int(10) DEFAULT NULL,
`master` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`desc` varchar(20) COLLATE utf8_bin DEFAULT NULL,
KEY `FK_cla` (`mid`),
CONSTRAINT `FK_cla` FOREIGN KEY (`mid`) REFERENCES `servants` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
实体类：get/set方法
public class Servants {
private Integer id;
private String name;
private String cla;
private String address;
private Master master; //当前servants对应的master
public class Master {
private Integer mid;
private String master;
private String desc;
ServantsMapper接口：
import com.fsn.beans.Servants;
public interface ServantsMapper {
public Servants getByName(String name);
}
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">
<configuration>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/ServantsMapper.xml"/>
<mapper resource="mapper/MasterMapper.xml"/>
</mappers>
</configuration>
ServantsMapper.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.fsn.mapper.ServantsMapper">
<resultMap id="newServants" type="com.fsn.beans.Servants">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="cla" column="cla"></result>
<result property="address" column="address"></result>
<association property="master" javaType="com.fsn.beans.Master">
<id property="mid" column="mid"></id>
<result property="master" column="master"></result>
<result property="desc" column="desc"></result>
</association>
</resultMap>
<select id="getByName" resultMap="newServants">
select s.*,m.* from servants s ,master m where s.id=m.mid and s.name='黑狗'
</select>
</mapper>
<!--
association : 一对一的查询标签 查询的结果就是一个对象
property： 对应的对象在当前类中声明的属性名称
javaType: 声明我们查询结果的类型
id result： 将我们的查询结果构建一个新的结果封装对象
-->
<!--
mybatis 将查询结果封装成对象
1： 使用ResultSetMeData 获取查询列的名称
2: 使用 列名称 充当属性名称 通过java中的反射机制获取属性对象
3: 使用属性对象给当前属性的赋值
-->
测试类：
import com.fsn.beans.Servants;
import com.fsn.mapper.ServantsMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class ServantsSelectTest {
@Test
public void test(){
String filename="sqlMapConfig.xml";
try {
InputStream is=Resources.getResourceAsStream(filename);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession=sqlSessionFactory.openSession();
ServantsMapper servantsMapper=sqlSession.getMapper(ServantsMapper.class);
Servants servants = servantsMapper.getByName("黑狗");
System.out.println(servants.getName()+"的master"+servants.getMaster().getMaster()+"是一个"+servants.getMaster().getDesc());
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
一对多查询：
新建三张表：
实体类：
public class Users {
private Integer uid;
private String uname;
private String uphone;
private Car car;
private List<Orders> ordersList; //一个用户对应多个订单对象
public class Car {
private Integer cid;
private String cdes;
private Double total;
public class Orders {
private Integer oid;
private String odes;
private Double total;
private Integer ofk; //user表中的主键对应的外键字段
UsersMapper接口：
import com.fsn.beans.Users;
public interface UsersMapper {
public Users getInfoByUphone(String uphone);
}
sqlMapConfig.xml:
<mapper resource="mapper/UsersMapper.xml"/>
UsersMapper.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.fsn.mapper.UsersMapper">
<resultMap id="BaseResultMap" type="com.fsn.beans.Users">
<id property="id" column="id"></id>
<result property="uname" column="uname"></result>
<result property="uphone" column="uphone"></result>
</resultMap>
<resultMap id="newUsers" type="com.fsn.beans.Users" extends="BaseResultMap">
<collection property="ordersList" ofType="com.fsn.beans.Orders">
<id property="oid" column="oid"></id>
<result property="odes" column="odes"></result>
<result property="total" column="total"></result>
<result property="ofk" column="ofk"></result>
</collection>
</resultMap
<select id="getInfoByUphone" resultMap="newUsers">
SELECT u.*,o.* FROM USER u,orders o WHERE u.uid=o.ofk AND uphone="12345"
</select>
</mapper>
<!--
collection: 表示的是多方查询的关联标签 list类型集合的
ofType: 将查询结果封转成对应的类型
-->
测试类：
import com.fsn.beans.Users;
import com.fsn.mapper.UsersMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class UserSelectMoreTest {
@Test
public void test(){
String filename="sqlMapConfig.xml";
SqlSession sqlSession = null;
try {
InputStream is=Resources.getResourceAsStream(filename);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
sqlSession = sqlSessionFactory.openSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users infoByUphone = usersMapper.getInfoByUphone("12345");
System.out.println(infoByUphone.getUname()+"\t"+infoByUphone.getOrdersList().size());
} catch (IOException e) {
e.printStackTrace();
}
sqlSession.commit();
sqlSession.close();
}
}
多对多查询：
增加2张表：对应关系：
实体类：
public class Users {
private Integer uid;
private String uname;
private String uphone;
private Car car;
private List<Orders> ordersList; //一个用户对应多个订单对象
public class Orders {
private Integer oid;
private String odes;
private Double total;
private Integer ufk; //user表中的主键对应的外键字段
private List<Orderdetail> orderdetails;
public class Orderdetail {
private Integer odid;
private Double subtotal;
private Integer count;
private Integer ofk;
private Integer gfk;
//一个详情对象 关联一个商品对象
private Goods goods;
public class Goods {
private Integer gid;
private String gname;
private Double gprice;
UsersMapper接口：
import com.fsn.beans.Users;
public interface UsersMapper {
public Users getAllInfoByUphone(String uphone);
}
mapper/UsersMapper.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.fsn.mapper.UsersMapper">
<resultMap id="BaseResultMap" type="com.fsn.beans.Users">
<id property="id" column="id"></id>
<result property="uname" column="uname"></result>
<result property="uphone" column="uphone"></result>
</resultMap>
<resultMap id="newUsers2" type="com.fsn.beans.Users" extends="BaseResultMap">
<collection property="ordersList" ofType="com.fsn.beans.Orders">
<id property="oid" column="oid"></id>
<result property="odes" column="odes"></result>
<result property="total" column="total"></result>
<result property="ufk" column="ufk"></result>
<collection property="orderdetails" ofType="com.fsn.beans.Orderdetail">
<id property="odid" column="odid"></id>
<result property="subtotal" column="subtotal"></result>
<result property="count" column="count"></result>
<result property="gfk" column="gfk"></result>
<result property="ofk" column="ofk"></result>
<association property="goods" javaType="com.fsn.beans.Goods">
<id property="gid" column="gid"></id>
<result property="gname" column="gname"></result>
<result property="gprice" column="gprice"></result>
</association>
</collection>
</collection>
</resultMap>
<select id="getAllInfoByUphone" resultMap="newUsers2">
SELECT u.*,o.*,od.*,g.* FROM USER u,orders o,orderdetail od,goods g WHERE u.uid=o.ufk AND o.oid=od.ofk AND od.gfk=g.gid AND u.uphone=#{uphone}
</select>
</mapper>
测试类：
import com.fsn.beans.Orderdetail;
import com.fsn.beans.Orders;
import com.fsn.beans.Users;
import com.fsn.mapper.UsersMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class UserSelectMoreTest {
@Test
public void test(){
String filename="sqlMapConfig.xml";
SqlSession sqlSession = null;
try {
InputStream is=Resources.getResourceAsStream(filename);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
sqlSession = sqlSessionFactory.openSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users allInfoByUphone = usersMapper.getAllInfoByUphone("12345");
System.out.println(allInfoByUphone.getUname());
for (Orders orders : allInfoByUphone.getOrdersList()) {
System.out.println(orders.getOdes());
for (Orderdetail orderdetail : orders.getOrderdetails()) {
System.out.println(orderdetail.getCount()+"\t"+orderdetail.getGoods().getGname());
}
}
} catch (IOException e) {
e.printStackTrace();
}
sqlSession.commit();
sqlSession.close();
}
}
一对一的嵌套查询：
表user,car不变
实体类 不用改
mapper接口：
UserMapper:
public Users getUserByUname(String uname);
CarMappe:
public interface CarMapper {
public Car getCarByUid(Integer uid);
}
UsersMapper.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.fsn.mapper.UsersMapper">
<resultMap id="BaseResultMap" type="com.fsn.beans.Users">
<id property="id" column="id"></id>
<result property="uname" column="uname"></result>
<result property="uphone" column="uphone"></result>
</resultMap>
<resultMap id="newusers" type="com.fsn.beans.Users" extends="BaseResultMap">
<association property="car" javaType="com.fsn.beans.Car" column="uid" select="com.fsn.mapper.CarMapper.getCarByUid">
</association>
</resultMap>
<select id="getUserByUname" resultMap="newusers" >
select u.* from user u where u.uname=#{uname}
</select>
</mapper>
<!--
column 就是我们执行对应的select语句获取的uid的值
select : 调用其他工作空间下的一个查询(这个查询需要一个参数--用户主键)
-->
CarMapper.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.fsn.mapper.CarMapper">
<resultMap id="BaseResultMap" type="com.fsn.beans.Car">
<id property="cid" column="cid"></id>
<result property="cdes" column="cdes"></result>
<result property="total" column="total"></result>
</resultMap>
<select id="getCarByUid" resultMap="BaseResultMap">
select * from car where cid=#{uid}
</select>
</mapper>
测试类：
@Test
public void test(){
String filename="sqlMapConfig.xml";
SqlSession sqlSession = null;
try {
InputStream is=Resources.getResourceAsStream(filename);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
sqlSession = sqlSessionFactory.openSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users user = usersMapper.getUserByUname("黑狗");
System.out.println(user.getUname()+"\t"+user.getCar().getCdes());
} catch (IOException e) {
e.printStackTrace();
}
sqlSession.commit();
sqlSession.close();
}
一对多的嵌套查询：
建表：user orders
建立数据模型
UserMapper接口：
public Users getInfoByUphone(String uphone);
OrdersMapper接口：
public interface OrdersMapper {
//根据用户uid查询用户订单
public List<Orders> getOrdersByUid(Integer uid);
}
OrdersMapper.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.fsn.mapper.OrdersMapper">
<resultMap id="BaseResultMap" type="com.fsn.beans.Orders">
<id property="oid" column="oid"></id>
<result property="odes" column="odes"></result>
<result property="total" column="total"></result>
<result property="ufk" column="ufk"></result>
</resultMap>
<select id="getOrdersByUid" resultMap="BaseResultMap">
select * from orders where ufk=#{uid}
</select>
</mapper>
UsersMapper.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.fsn.mapper.UsersMapper">
<resultMap id="BaseResultMap" type="com.fsn.beans.Users">
<id property="id" column="id"></id>
<result property="uname" column="uname"></result>
<result property="uphone" column="uphone"></result>
</resultMap>
<resultMap id="newUser" type="com.fsn.beans.Users" extends="BaseResultMap">
<collection property="ordersList" ofType="com.fsn.beans.Orders"
column="uid" select="com.fsn.mapper.OrdersMapper.getOrdersByUid">
</collection>
</resultMap>
<select id="getInfoByUphone" resultMap="newUser">
select u.* from user u where uphone=#{uphone}
</select>
</mapper>
测试类：
@Test
public void test(){
String filename="sqlMapConfig.xml";
SqlSession sqlSession = null;
try {
InputStream is=Resources.getResourceAsStream(filename);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
sqlSession = sqlSessionFactory.openSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users infoByUphone = usersMapper.getInfoByUphone("12345");
System.out.println(infoByUphone.getUname()+"\t"+infoByUphone.getOrdersList().size());
} catch (IOException e) {
e.printStackTrace();
}
sqlSession.commit();
sqlSession.close();
}
}
结果：2条查询结果的拼接
Mybatis动态SQL
表：user
UserMapper接口：
public Integer updateUser(Users users);
UsersMapper.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.fsn.mapper.UsersMapper">
<update id="updateUser" parameterType="com.fsn.beans.Users">
update user
<set>
<if test="uname!=null">
uname=#{uname},
</if>
<if test="uphone!=null">
uphone=#{uphone},
</if>
</set>
<where>
uid=#{uid}
</where>
</update>
</mapper>
测试类：
@Test
public void test(){
String filename="sqlMapConfig.xml";
SqlSession sqlSession = null;
try {
InputStream is=Resources.getResourceAsStream(filename);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
sqlSession = sqlSessionFactory.openSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users=new Users();
users.setUname("法狗");
users.setUid(2);
usersMapper.updateUser(users);
} catch (IOException e) {
e.printStackTrace();
}
sqlSession.commit();
sqlSession.close();
}
}
where 标签:
<!--
where标签最后被mybatis解析成where关键字
where标签会忽略到紧邻着where关键字的sql中的 and or
where标签中没有成立的条件那么where自动失效
-->
if 标签：
<!--
if标签是判断标签 如果test属性值为true那么就执行标签中间的sql
如果test值为false 那么就忽略标签中间的内容
if标签的判断的判断表达式 可以判断null值 可以进行数值判断 对 应属性值可以调用当前类中的方法
if标签的判断内容可以是用 and 或者是 or进行一个多条件判断
test中使用的判断的变量的名称必须是我们传入类型的属性名称
注意：如果传入的是一个普通类型的数据，那么我们在代码中必须使用 @Param("名称") 指定名称
-->
set 标签：
进行数据更新的时候使用的标签
<!--
set标签会被mybatis解析成我们的set关键字
set标签会自动的忽略最后一个成立条件的逗号
-->
浙公网安备 33010602011771号