需要的包
建表
(1) 在实验2结果代码的基础上,修改CustomerMapper.xml,使用元素编写动态SQL语句,完成根据客户职业查找客户信息。
<!-- 动态SQL测试,通过工作查个人 -->
<select id="findCustomerByJob" parameterType="com.xiucai.po.Customer" resultMap="resultMap">
select * from t_customer where 1=1
<if test="jobs!=null and jobs!='' ">
and jobs=#{jobs}
</if>
</select>
(2) 在测试类中编写测试方法findCustomerByJobTest()进行测试
package com.xiucai.test;
import java.util.ArrayList;
import java.util.List;
import javax.websocket.Session;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import com.xiucai.po.Customer;
import com.xiucai.util.SqlSessionUtil;
public class findCustomerByJobTest {
// 测试根据工作动态sql测试顾客
@Test
public void findByJobs() {
SqlSession sqlSession=SqlSessionUtil.getSqlSession();
Customer customer=new Customer();
customer.setJobs("cooker");
List<Customer>customers=new ArrayList<Customer>();
customers=sqlSession.selectList("com.xiucai.Mapper.CustomerMapper.findCustomerByJob", customer);
for (Customer customer2 : customers) {
System.out.println(customer2);
}
}
}
执行结果:
(3) 使用choose>、、元素编写动态SQL语句,完成如下场景操作:当客户名称不为空,则根据客户名称进行客户筛选;当客户名称为空,而客户职业不为空,则只根据客户职业进行客户筛选;当客户名称、客户职业都为空,则要求查询出所以电话不为空的客户信息;
<select id="findCustomerByNameOrJobs" parameterType="com.xiucai.po.Customer" resultMap="resultMap">
select * from t_customer where 1=1
<choose>
<!-- 当客户名称不为空,则根据客户名称进行客户筛选 -->
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<!-- 当客户名称为空,而客户职业不为空,则只根据客户职业进行客户筛选 -->
<when test="jobs!=null and jobs!='' ">
and jobs=#{jobs}
</when>
<!-- 当客户名称、客户职业都为空,则要求查询出所以电话不为空的客户信息; -->
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>
(4) 在测试类中编写测试方法findCustomerByNameOrJobTest()进行测试
当姓名不为空的时候
代码:
@Test
public void findCustomerByNameOrJobTest() {
SqlSession sqlSession=SqlSessionUtil.getSqlSession();
Customer customer=new Customer();
//当姓名不为空的时候
customer.setUsername("huang4");;
//当客户姓名为空 职业不为空的时候
// customer.setJobs("cooker");
List<Customer>customers=new ArrayList<Customer>();
customers=sqlSession.selectList("com.xiucai.Mapper.CustomerMapper.findCustomerByNameOrJobs", customer);
for (Customer customer2 : customers) {
System.out.println(customer2);
}
}
结果:
当姓名为空
代码:
@Test
public void findCustomerByNameOrJobTest() {
SqlSession sqlSession=SqlSessionUtil.getSqlSession();
Customer customer=new Customer();
//当客户姓名为空 职业不为空的时候
customer.setJobs("cooker");
List<Customer>customers=new ArrayList<Customer>();
customers=sqlSession.selectList("com.xiucai.Mapper.CustomerMapper.findCustomerByNameOrJobs", customer);
for (Customer customer2 : customers) {
System.out.println(customer2);
}
}
结果:
姓名优先级大
都为空时
(5) 使用、对(3)代码块进行修改,要求程序功能不变
对于where的使用
使用where
<!-- 使用where跟trim对代码进行修改 -->
<select id="findCustomerByNameOrJobs2"
parameterType="com.xiucai.po.Customer" resultMap="resultMap">
select * from t_customer
<where>
<choose>
<!-- 当客户名称不为空,则根据客户名称进行客户筛选 -->
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<!-- 当客户名称为空,而客户职业不为空,则只根据客户职业进行客户筛选 -->
<when test="jobs!=null and jobs!='' ">
and jobs=#{jobs}
</when>
<!-- 当客户名称、客户职业都为空,则要求查询出所以电话不为空的客户信息; -->
<otherwise>
and phone is not null
</otherwise>
</choose>
</where>
</select>
使用trim
<!-- 使用where跟trim对代码进行修改 -->
<select id="findCustomerByNameOrJobs2"
parameterType="com.xiucai.po.Customer" resultMap="resultMap">
select * from t_customer
<trim prefix="where" prefixOverrides="and">
<choose>
<!-- 当客户名称不为空,则根据客户名称进行客户筛选 -->
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<!-- 当客户名称为空,而客户职业不为空,则只根据客户职业进行客户筛选 -->
<when test="jobs!=null and jobs!='' ">
and jobs=#{jobs}
</when>
<!-- 当客户名称、客户职业都为空,则要求查询出所以电话不为空的客户信息; -->
<otherwise>
and phone is not null
</otherwise>
</choose>
</trim>
</select>
(6) 使用和元素对username和jobs进行更新判断,并动态组装SQL。只传入想要更新的字段,编写测试类完成将“jasmine”的电话改为“18966547895”
<update id="updateCustomerByNameOrJobs" parameterType="com.xiucai.po.Customer" >
update t_customer
<set>
<if test="jobs!=null and jobs!='' ">
jobs=#{jobs},
</if>
<!-- <if test="username!=null and username='' "> </if>-->
<if test="username !=null and username !=''"></if>
username=#{username},
<if test="phone!=null and phone !=''">
phone=#{phone},
</if>
</set>
where id=#{id}
</update>
(7) 使用动态SQL的元素完成对结果集的遍历,编写测试类完成查找id是(1,2,3)中的信息。
<sql id="customerColumns">id,username,jobs,phone</sql>
<!-- 给定条件查大量数据集合 -->
<select id="findListByForeach" parameterType="List"
resultType="com.xiucai.po.Customer">
select * from t_customer where id in
<foreach item="id" index="index" collection="list" open="("
separator="," close=")">
#{id}
</foreach>
</select>
(8) 使用元素完成模糊查询SLQ语句的改进。
<select id="findCustomerByBind" parameterType="com.xiucai.po.Customer"
resultType="com.xiucai.po.Customer">
<bind name="usernameLike" value="'%'+username+'%'"/>
select * from t_customer where
username like #{usernameLike}
</select>
测试:
@Test
public void findByBind() {
SqlSession sqlSession=SqlSessionUtil.getSqlSession();
Customer customer=new Customer();
customer.setUsername("sername");
List<Customer>customers=new ArrayList<Customer>();
customers=sqlSession.selectList("com.xiucai.Mapper.CustomerMapper.findCustomerByBind", customer);
for (Customer customer1 : customers) {
System.out.println(customer1);
}
}