复合条件查询(动态SQL)
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
if
choose (when, otherwise)
trim (where, set)
foreach
创建PO类 作为导航
package com.foreknow.bean;
public class UserInfo extends User {
}
UserQueryInfo.java
package com.foreknow.bean;
import java.util.List;
/**
* 根据多个条件查询用户的信息
* @author Administrator
*
*/
public class UserQueryInfo {
//传入多个id
private List<Integer> ids;
//在这里包装所需要的查询条件
//用户查询条件
private UserInfo userInfo;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
public UserInfo getUserInfo() {
return userInfo;
}
public void setUserInfo(UserInfo userInfo) {
this.userInfo = userInfo;
}
public static void main(String[] args) {
}
}
UserMapper.java 接口
// 用户信息综合查询
public List<UserInfo> findUserList(UserQueryInfo userQueryInfo) throws Exception;
// 用户信息综合查询总数
public int findUserCount(UserQueryInfo userQueryInfo) throws Exception;
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">
<!--namespace命名空间 ，它的作用是对SQL进行分类化管理 -->
<mapper namespace="com.foreknow.dao.UserMapper">
<!--
Java代码：
public User findUserById(int id)
id:唯一标识 相当于方法的名称
parameterType：输入参数的类型 相当于方法的参数类型
resultType：方法返回值的类型 注意：全路径（包名+类名）
#{id}:相当于一个占位符
-->
<!-- 所有的select标签都能引用他 -->
<sql id="query_user_where">
<if test="userInfo!=null">
<if test="userInfo.sex!=null and userInfo.sex!=''">
and USER .sex = #{userInfo.sex}
</if>
<if test="userInfo.username!=null and userInfo.username!=''">
and USER.username LIKE '%${userInfo.username}%'
</if>
<if test="ids!=null">
<foreach collection="ids" item="user_id" open="AND (" close=")" separator="or">
<!-- 每个遍历需要拼接的串 -->
id=#{user_id}
</foreach>
</if>
</if>
</sql>
<select id="findUserById" parameterType="int" resultType="com.foreknow.bean.User" >
select * from user where id=#{id}
</select>
<!-- public void insertUser(User user) -->
<insert id="insertUser" parameterType="com.foreknow.bean.User">
insert into User(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})
</insert>
<!--
${value}表示拼接sql字符串
-->
<select id="findUserByName" parameterType="java.lang.String" resultType="com.foreknow.bean.User">
select * from user where username like '%${value}%'
</select>
<delete id="deleteUser" parameterType="java.lang.Integer">
delete from user where id=#{id}
</delete>
<update id="updateUser" parameterType="com.foreknow.bean.User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
<select id="findUserList" parameterType="com.foreknow.bean.UserQueryInfo" resultType="com.foreknow.bean.UserInfo">
select*from user
<!-- 导航语音userInfo -->
<where>
<!-- include是包含 refid是引用 引用sql 的 ID -->
<include refid="query_user_where"></include>
</where>
</select >
<!-- 查询订单关联查询用户信息，使用resultmap -->
<select id="findOrderUser" resultType="com.foreknow.bean.OrderUserInfo">
SELECT
orders.*,
USER.username,
USER.sex,
USER.address
FROM
orders,
USER
WHERE orders.user_id = user.id
</select>
<!-- 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 -->
<!-- 查询订单关联查询用户信息，使用resultmap association （一对一）-->
<resultMap type="com.foreknow.Orders" id="OrdersUserResultMap">
<!-- 配置映射的订单信息 -->
<!-- id：指定查询列中的唯 一标识，订单信息的中的唯 一标识，如果有多个列组成唯一标识，配置多个id
column：订单信息的唯 一标识 列
property：订单信息的唯 一标识 列所映射到Orders中哪个属性
-->
<id column="id" property="id"/>
<!-- user_id是数据库的表的列 property 是对应bean中private的属性名称 正常应该是一一对应的 但是
在Mybatis半自动框架中 可以不一一对应 -->
<result column="user_id" property="userId"/>
<!-- <result column="orderid" property代表属性名="orderid"/> -->
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 配置映射的关联的用户信息 -->
<!-- association：用于映射关联查询单个对象的信息
property：要将关联查询的用户信息映射到Orders中哪个属性
-->
<association property="user" javaType="com.foreknow.User">
<!-- id：关联查询用户的唯 一标识
column：指定唯 一标识用户信息的列
javaType：映射到user的哪个属性
-->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
SELECT
orders.*,
USER.username,
USER.sex,
USER.address
FROM
orders,
USER
WHERE orders.user_id = user.id
</select>
<!-- 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 -->
<!--描述一对多的关联关系 collection -->
<!-- 订单及订单明细的resultMap
使用extends继承，不用在中配置订单信息和用户信息的映射
-->
<!-- 订单信息 -->
<!-- 用户信息 -->
<!-- 使用extends继承，不用在中配置订单信息和用户信息的映射 -->
<!-- 订单明细信息
一个订单关联查询出了多条明细，要使用collection进行映射
collection：对关联查询到多条记录映射到集合对象中
property：将关联查询到多条记录映射到Orders哪个属性
ofType：指定映射到list集合属性中pojo的类型
-->
<resultMap type="com.foreknow.bean.Orders" id="OrdersDetailResultMap" extends="OrdersUserResultMap">
<collection property="orderdetails" ofType="com.foreknow.bean.Orderdetail">
<!-- id：订单明细唯 一标识
property:要将订单明细的唯 一标识 映射到com.foreknow.Orderdetail的哪个属性
-->
<id column="orderdetail_id" property="id"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<result column="orders_id" property="ordersId"/>
</collection>
</resultMap>
<select id="findOrderDetailResultMap" resultMap="OrdersDetailResultMap">
SELECT
orders.*,
USER.username,
USER.sex,
USER.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id
FROM
orders,
USER,
orderdetail
WHERE orders.user_id=user.id and orders.id=orderdetail.orders_id
</select>
<!-- 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 -->
<!-- 查询用户及购买的商品 -->
<resultMap type="com.foreknow.bean.User" id="UserItemsResultMap">
<!-- 用户信息 -->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 订单信息
一个用户对应多个订单，使用collection映射
-->
<collection property="ordersList" ofType="com.foreknow.bean.Orders">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="orderid" property="orderid"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 订单明细
一个订单包括 多个明细
-->
<collection property="orderdetails" ofType="com.foreknow.bean.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<result column="orders_id" property="ordersId"/>
<!-- 商品信息
一个订单明细对应一个商品
-->
<association property="items" javaType="com.foreknow.bean.Items">
<id column="items_id" property="id"/>
<result column="items_name" property="name"/>
<result column="items_detail" property="detail"/>
<result column="items_price" property="price"/>
</association>
</collection>
</collection>
</resultMap>
<select id="findUserItemsResultMap" resultMap="UserItemsResultMap">
SELECT
orders.*,
USER.username,
USER.sex,
USER.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id,
items.id items_id,
items.name items_name,
items.detail items_detail,
items.price items_price
from
orders,
user,
orderdetail,
items
where orders.user_id=user.id and orders.id=orderdetail.orders_id and orderdetail.items_id=items.id
</select>
</mapper>
Junit测试 MybatisUserMapperTest.java
package com.foreknow.mybatis_test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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.Before;
import org.junit.Test;
import com.foreknow.bean.OrderUserInfo;
import com.foreknow.bean.Orders;
import com.foreknow.bean.User;
import com.foreknow.bean.UserInfo;
import com.foreknow.bean.UserQueryInfo;
import com.foreknow.dao.UserDao;
import com.foreknow.dao.UserMapper;
public class MybatisUserMapperTest {
private SqlSessionFactory sqlSessionFactory;
// 此方法是在执行testFindUserById之前执行
@Before
public void setUp() throws Exception {
// 创建sqlSessionFactory
// mybatis配置文件
String resource = "SqlMapConfig.xml";
// 得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建会话工厂，传入mybatis的配置文件信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserById() throws Exception {
SqlSession sqlSession= sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
UserQueryInfo userQueryInfo= new UserQueryInfo();
UserInfo userInfo= new UserInfo();
userInfo.setSex("1");
userInfo.setUsername("张");
List<Integer> ids= new ArrayList<>();
ids.add(1);
ids.add(10);
userQueryInfo.setIds(ids);
userQueryInfo.setUserInfo(userInfo);
List<UserInfo> list=userMapper.findUserList(userQueryInfo);
sqlSession.close();
}
@Test
public void testFindOrderUser() throws Exception {
SqlSession sqlSession= sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
List<OrderUserInfo> list=userMapper.findOrderUser();
System.out.println(list.size());
sqlSession.close();
}
@Test
public void findOrderUserResultMap() throws Exception {
SqlSession sqlSession= sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
List<Orders> list=userMapper.findOrderUserResultMap();
System.out.println(list.size());
sqlSession.close();
}
@Test
public void testFindOrderDetailResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 调用maper的方法
List<Orders> list = userMapper.findOrderDetailResultMap();
System.out.println(list);
sqlSession.close();
}
@Test
public void testFindUserItemsResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 调用maper的方法
List<User> list = userMapper.findUserItemsResultMap();
System.out.println(list);
sqlSession.close();
}
}
Mybatis中的关联查询
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT '下单用户id',
`number` varchar(32) NOT NULL COMMENT '订单号',
`createtime` datetime NOT NULL COMMENT '创建订单时间',
`note` varchar(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);
数据模型分析思路
1、每张表记录的数据内容
分模块对每张表记录的内容进行熟悉，相当 于你学习系统 需求（功能）的过程。
2、每张表重要的字段设置
非空字段、外键字段
3、数据库级别表与表之间的关系
外键关系
4、表与表之间的业务关系
在分析表与表之间的业务关系时一定要建立 在某个业务意义基础上去分析。
数据模型分析
用户表user：
记录了购买商品的用户信息
订单表：orders
记录了用户所创建的订单（购买商品的订单）
订单明细表：orderdetail：
记录了订单的详细信息即购买商品的信息
商品表：items
记录了商品信息
表与表之间的业务关系：
在分析表与表之间的业务关系时需要建立 在某个业务意义基础上去分析。
先分析数据级别之间有关系的表之间的业务关系：
usre和orders：
user---->orders：一个用户可以创建多个订单，一对多
orders--->user：一个订单只由一个用户创建，一对一
orders和orderdetail：
orders--->orderdetail：一个订单可以包括 多个订单明细，因为一个订单可以购买多个商品，每个商品的购买信息在orderdetail记录，一对多关系
orderdetail--> orders：一个订单明细只能包括在一个订单中，一对一
orderdetail和itesm：
orderdetail--->itesms：一个订单明细只对应一个商品信息，一对一
items--> orderdetail:一个商品可以包括在多个订单明细 ，一对多
再分析数据库级别没有关系的表之间是否有业务关系：
orders和items：
orders和items之间可以通过orderdetail表建立 关系。
创建JavaBean模型关系
Orders模型
package com.foreknow.bean;
import java.util.Date;
import java.util.List;
public class Orders {
private Integer id;
private Integer userId;
private String orderid;
private Date createtime;
private String note;
// 用户信息(用于resultMap ) orders:user=1:1
private User user;
//orders:Orderdetail=1: n 所以要新建一个集合
//Orders => Orderdetail 为一对多的关系
//最终会将订单信息映射到orders中，订单所对应的订单明细映射到orders中的orderDetails属性中。
private List<Orderdetail> orderdetails;
public List<Orderdetail> getOrderdetails() {
return orderdetails;
}
public void setOrderdetails(List<Orderdetail> orderdetails) {
this.orderdetails = orderdetails;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getOrderid() {
return orderid;
}
public void setOrderid(String orderid) {
this.orderid = orderid;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
User模型
package com.foreknow.bean;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String sex;
private Date birthday;
private String address;
//一个用户可以有多个订单（建立用户与商品之间的关系）
//用户创建的订单列表
private List<Orders> ordersList;
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
Items模型
package com.foreknow.bean;
import java.util.Date;
public class Items {
private Integer id;
private String name;
private Float price;
private String pic;
private Date createtime;
private String detail;
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 == null ? null : name.trim();
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public String getPic() {
return pic;
}
public void setPic(String pic) {
this.pic = pic == null ? null : pic.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail == null ? null : detail.trim();
}
@Override
public String toString() {
return "Items [id=" + id + ", name=" + name + ", price=" + price
+ ", pic=" + pic + ", createtime=" + createtime + ", detail="
+ detail + "]";
}
}
Orderdetail模型
package com.foreknow.bean;
public class Orderdetail {
private Integer id;
private Integer ordersId;
private Integer itemsId;
private Integer itemsNum;
//明细对应的商品信息
private Items items;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getOrdersId() {
return ordersId;
}
public void setOrdersId(Integer ordersId) {
this.ordersId = ordersId;
}
public Integer getItemsId() {
return itemsId;
}
public void setItemsId(Integer itemsId) {
this.itemsId = itemsId;
}
public Integer getItemsNum() {
return itemsNum;
}
public void setItemsNum(Integer itemsNum) {
this.itemsNum = itemsNum;
}
public Items getItems() {
return items;
}
public void setItems(Items items) {
this.items = items;
}
@Override
public String toString() {
return "Orderdetail [id=" + id + ", ordersId=" + ordersId
+ ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";
}
}
创建UserMapper接口
package com.foreknow.dao;
import java.sql.SQLException;
import java.util.List;
import com.foreknow.bean.OrderUserInfo;
import com.foreknow.bean.Orders;
import com.foreknow.bean.User;
import com.foreknow.bean.UserInfo;
import com.foreknow.bean.UserQueryInfo;
public interface UserMapper {
/**
* 根据id查询用户信息
* @param id
* @return User
* @throws SQLException
* @throws Exception
*/
public User findUserById(int id)throws SQLException, Exception;
/**
* 模糊查询用户信息列表
* @param name
* @return List<User>
* @throws SQLException
* @throws Exception
*/
public List<User> findUserByName(String name)throws SQLException, Exception;
/**
* 添加用户信息
* @param user
* @throws SQLException
* @throws Exception
*/
public void insertUser(User user)throws SQLException, Exception;
/**
* 根据id删除用户信息
* @param id
* @throws SQLException
* @throws Exception
*/
public void deleteUser(int id)throws SQLException, Exception;
/**
* 修改用户信息
* @param user
* @throws SQLException
*/
public void updateUser(User user)throws SQLException;
/*
* 根据(多个)条件查询用户的信息
*/
public List<UserInfo> findUserList(UserQueryInfo userQueryInfo) throws SQLException;
/*
* 查询订单所关联用户的信息
*/
public List<OrderUserInfo> findOrderUser()throws SQLException;
/*
* 使用Mybatis中的关联映射（一对一）resultmap
*/
public List<Orders> findOrderUserResultMap()throws SQLException;
/*
* 查询订单（用户信息）以及明细信息
*/
public List<Orders> findOrderDetailResultMap()throws SQLException;
/*
* 查询订单（用户信息）以及明细信息
*/
public List<User> findUserItemsResultMap()throws SQLException;
}
image.png
在Mybatis中动态sql常用的标签有哪些？
<where> <if> <when><choose> <update><set> <include>
一对一关联
<association property="user" javaType="com.foreknow.bean.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
javaType：映射类型的属性
<collection property="orderdetails" ofType="com.foreknow.bean.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="orders_id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
ofType：指定集合中元素的类型
{value}与${value}的区别
parameterType 与 resultType的区别
resultType与resultMap
resultMap表示返回了一个map映射，需要指定
type="com.foreknow.bean.Orders"以及Orders与表的对应关系
网友评论