已经两个月没有写博客了，暑假偷懒了。。。 踏入大三，开始学习Java框架了，最近的课程教的是Mybatis数据库，课后写个博客实时记录一下知识点，尝试用自己的语句表达出来。
动态SQL几个基本的元素：if、where、when、choose、otherwise、foreach、set、trim、bind等等
这篇博客重点记录的是if、where、when、choose、otherwise和foreach的基本用法。
MySQL数据库设计
数据库设计与先前的JavaWeb没有区别
Spring配置
这里建议下载2019的eclipse，我也是重新下载配置了一下，然后配置了Spring。
① 下载eclipse 2019，spring，以及mybatis的一些jar包
https://pan.baidu.com/s/1GGH-qgoUbH043hnddG1U_Q
提取码：ba41
② eclipse下载好之后，重新配置jdk和tomcat（这里我就不分享了，有需要的可以去其他博客查找安装），点击Help->Install New SoftWare
点击Add
Name可写可不写，点击Archive，找到上面下载的spring压缩包，单击锁定后，就可以点打开，点下面的Add，会出现下面的画面
选中这四个Srping IDE
随后按指示安装即可，更详细的在下面的链接
手把手教你安装Spring+搭建Spring开发环境
工程创建
这里是博客的主题
① 创建动态工程Dynamic Web Project，写一个工程名，点next
可能会出现这种情况，把空白的文件删掉，add一个新的文件，命名为src，
下面的Default output folder写“build\classes”，点next
Content directory写"WebContent"，下面的选项打勾，就创建成功了。
② 把上面下载的mybatis压缩包解压，那些jar包，都复制到WebContent的lib文件夹当中，选中全部，右键，build path一下就好了。
③ 到了操作src的步骤了，把mybatis包当中的其他文件夹的mybatis-config.xml和log4j.properties放到src里面
随后创建四个包，分别如下
包名可以自己取，但最后的名字最好统一（dao，po，test，utils）
src里的东西就如上图。
④ po包
Customer.java
package cn.edu.xit.po;
public class Customer {
private Integer id;
private String username;
private String jobs;
private String phone;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getJobs() {
return jobs;
}
public void setJobs(String jobs) {
this.jobs = jobs;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Customer [id=" + id + ", username=" + username + ", jobs=" + jobs + ", phone=" + phone + "]";
}
}
包装类
⑤ dao包
CustomerDao.java（创建的时候选interface接口）
package cn.edu.xit.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import cn.edu.xit.po.Customer;
public interface CustomerDao {
public Customer findCustomerById(Integer id);
public Customer findCustomerByUsername(String username);
public Customer findAll();
public Customer findCustomerLikeUsername(String username);
public Customer insertCustomer();
// 方法名就是id值,返回值就是resultType的值,参数是parameterType
public List<Customer> findCustomerByMany(Customer customer);
public List<Customer> findCustomerByMany2(Customer customer);
public List<Customer> findCustomerByMany3(Customer customer);
int updateCustomer(Customer customer);
public List<Customer> findCustomerByIds(List id);
public Customer findCustomerByNameandPsd(@Param("name")String username,@Param("psd")String password);
}
CustomerMapper.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="cn.edu.xit.dao.CustomerDao">
<select id = "findCustomerById" parameterType="Integer" resultType="cn.edu.xit.po.Customer">
select * from customer where id = #{id}
</select>
<select id = "findCustomerByUsername" parameterType="String" resultType="cn.edu.xit.po.Customer">
select * from customer where username = #{username}
</select>
<select id = "findAll" resultType="cn.edu.xit.po.Customer">
select * from customer
</select>
<select id = "findCustomerLikeUsername" parameterType="String" resultType="cn.edu.xit.po.Customer">
select * from customer where username like concat('%',#{username},'%')
</select>
<insert id = "insertCustomer" parameterType="cn.edu.xit.po.Customer">
insert into customer (username,jobs,phone) values(#{username},#{jobs},#{phone})
</insert>
<update id = "updateCustomer" parameterType="cn.edu.xit.po.Customer">
update customer set username = #{username} where id = #{id}
</update>
<delete id = "deleteCustomer" parameterType="Integer">
delete from customer where id = #{id}
</delete>
<!-- if元素 -->
<select id="findCustomerByMany" parameterType="cn.edu.xit.po.Customer" resultType="cn.edu.xit.po.Customer">
select * from customer where 1 = 1
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test = "jobs!=null and jobs!=''">
and jobs = #{jobs}
</if>
</select>
<!-- choose、when、otherwise元素 -->
<select id = "findCustomerByMany2" parameterType="cn.edu.xit.po.Customer" resultType="cn.edu.xit.po.Customer">
select * from 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>
<!-- where元素 -->
<select id = "findCustomerByMany3" parameterType="cn.edu.xit.po.Customer" resultType="cn.edu.xit.po.Customer">
select * from customer
<where>
<if test = "username != null and username != ''">
and username like concat('%',#{username},'%')
</if>
<if test = "jobs != null and jobs != ''">
and jobs = #{jobs}
</if>
</where>
</select>
<!-- set元素 -->
<!-- <update id = "updateCustomer" parameterType="cn.edu.xit.po.Customer">
update customer
<set>
<if test = "username != null and username != ''">
username = #{username},
</if>
<if test = "jobs != null and jobs != ''">
jobs = #{jobs}
</if>
</set>
where id = #{id}
</update> -->
<!-- 有错误的标签语句要先注释掉 -->
<!-- foreach元素 -->
<select id = "findCustomerByIds" parameterType="List" resultType="cn.edu.xit.po.Customer">
select * from customer where id in
<foreach item = "id" collection = "list" open = "(" separator = "," close = ")">
#{id}
</foreach>
</select>
<!-- 分页显示数据 -->
<!-- <select id = "findCustomerByPage" parameterType="" resultType="">
mysql数据库分页语句
select * from customer limit #{startRow},#{pagesize}
</select> -->
<!-- 用户名和密码,parameterType = ""只写一个参数,如果有多个参数,就不写 -->
<!-- <select id = "findCustomerByNameandPsd" resultType="">
select * from customer where username = #{name} and password = #{psd}
</select> -->
</mapper>
每个select标签的id，指的就是接口当中的抽象类，两者相对应。
⑥ utils包
MybatisUtils.java
package cn.edu.xit.utils;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory = null;
static {
try {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}catch(Exception e) {
e.printStackTrace();
}
}
public static SqlSession getSession() {
return sqlSessionFactory.openSession();
}
}
写这个工具类的作用就是简化开发过程的复杂度，统一在工具类当中搭建。
现在开始逐一使用上面的东西来写
⑦ if元素的使用
一、CustomerMapper.xml当中写标签方法
<!-- if元素 -->
<select id="findCustomerByMany" parameterType="cn.edu.xit.po.Customer" resultType="cn.edu.xit.po.Customer">
select * from customer where 1 = 1
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<if test = "jobs!=null and jobs!=''">
and jobs = #{jobs}
</if>
</select>
二、CustomerDao接口类中写抽象方法（可以把上面接口类的其他方法先删掉，只保留这个）
public List<Customer> findCustomerByMany(Customer customer);
三、创建测试类test
MybatisTest_if.java
package cn.edu.xit.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import cn.edu.xit.dao.CustomerDao;
import cn.edu.xit.po.Customer;
import cn.edu.xit.utils.MybatisUtils;
public class MybatisTest_if {
@Test
public void findCustomerByManyTest() {
SqlSession session = MybatisUtils.getSession();
CustomerDao customerDao = session.getMapper(CustomerDao.class);
Customer customer = new Customer();
customer.setUsername("su");
customer.setJobs("11");
System.out.println(customerDao.findCustomerByMany(customer));
session.close();
}
}
四、最后右键，Run as，选（2 JUnit test），如果没有的话，测试类开头的@Test一开始会出现错误，鼠标移到那边，alt+/就可以出来解决方法，点击就好，最后测试结果如下即为成功
⑧ foreach元素使用
一、xml写方法
<!-- foreach元素 -->
<select id = "findCustomerByIds" parameterType="List" resultType="cn.edu.xit.po.Customer">
select * from customer where id in
<foreach item = "id" collection = "list" open = "(" separator = "," close = ")">
#{id}
</foreach>
</select>
二、接口类写方法
public List<Customer> findCustomerByIds(List id);
三、测试类写方法
package cn.edu.xit.test;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import cn.edu.xit.dao.CustomerDao;
import cn.edu.xit.po.Customer;
import cn.edu.xit.utils.MybatisUtils;
public class MybatisTest_foreach {
@Test
public void findCustomerByIdsTest() throws IOException{
SqlSession sqlSession = MybatisUtils.getSession();
CustomerDao customerDao = sqlSession.getMapper(CustomerDao.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
System.out.println(customerDao.findCustomerByIds(ids));
sqlSession.close();
}
}
四、测试
其他的元素我就不一一举例了，把对应的测试方法放在下面，感兴趣的可以从上面的CustomerMapper.xml（有注释）当中找对应的方法和CustomerDao中找对应的方法，就可以测试啦。
where标签：
package cn.edu.xit.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import cn.edu.xit.dao.CustomerDao;
import cn.edu.xit.po.Customer;
import cn.edu.xit.utils.MybatisUtils;
public class MybatisTest_where {
@Test
public void findCustomerByMany3Test() {
SqlSession session = MybatisUtils.getSession();
CustomerDao customerDao = session.getMapper(CustomerDao.class);
Customer customer = new Customer();
customer.setUsername("su");
customer.setJobs("1");
System.out.println(customerDao.findCustomerByMany2(customer));
session.close();
}
}
choose标签（包含when和otherwise）：
package cn.edu.xit.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import cn.edu.xit.dao.CustomerDao;
import cn.edu.xit.po.Customer;
import cn.edu.xit.utils.MybatisUtils;
public class MybatisTest_choose {
@Test
public void findCustomerByMany2Test() {
SqlSession session = MybatisUtils.getSession();
CustomerDao customerDao = session.getMapper(CustomerDao.class);
Customer customer = new Customer();
customer.setUsername("su");
customer.setJobs("1");
System.out.println(customerDao.findCustomerByMany3(customer));
session.close();
}
}
扫一扫
2693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
