一、利用实体类对象封装参数实现对student表中的数据的查询。
1)创建MyBaits1项目,使用mapper.xml绑定接口,实现对数据库记录的查询操作。
1、实体类
package wzu.pojo;
public class student {
private int sno;
private String sname;
private String ssex;
private int sage;
private String sdept;
@Override
public String toString() {
return "student{" +
"sno=" + sno +
", sname='" + sname + '\'' +
", ssex='" + ssex + '\'' +
", sage=" + sage +
", sdept='" + sdept + '\'' +
'}';
}
}
2、实体接口类
package wzu.mapper;
import org.apache.ibatis.annotations.Param;
import wzu.pojo.student;
import java.util.List;
public interface studentMapper {
//查询计算机系年龄为19岁的女学生的信息
List<student> select_trim(@Param("Sdept") String sdept,
@Param("Sage") int sage,
@Param("Ssex") String ssex);
}
3、实体接口的mapper文件(SQL配置)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="wzu.mapper.studentMapper">
<!-- 查询计算机系年龄为19岁的女学生的信息 -->
<select id="select_trim" resultType="wzu.pojo.student">
select * from student
<trim prefix="where" prefixOverrides="and|or">
<if test="Sdept !=null and Sdept !=''">
and sdept = '${Sdept}'
</if>
<if test="Sage !=null and Sage !=''">
and sage = '${Sage}'
</if>
<if test="Ssex !=null and Ssex !=''">
and ssex = '${Ssex}'
</if>
</trim>
</select>
</mapper>
4、核心配置文件(mybatis-config.xml)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 引入database.properties文件-->
<!-- <properties resource="database.properties"/>-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
<!-- useUnicode=true&characterEncoding=utf8&useSSL=false&-->
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 将mapper文件加入配置文件中-->
<mappers>
<mapper resource="wzu/mapper/studentMapper.xml"/>
</mappers>
</configuration>
5、Util 类( 创建 / 关闭 SqlSession )
package wzu;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class Util {
private static SqlSessionFactory factory;
static { //静态 - 只创建一次factory
String resource= "mybatis-config.xml";
try {
InputStream is = Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession createSqlSession(){
return factory.openSession(false); // true - 自动提交事务
}
public static void closeSqlSession(SqlSession sqlSession){
if(null!=sqlSession){
sqlSession.close();
}
}
}
6、测试类
package wzu.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import wzu.Util;
import wzu.mapper.studentMapper;
import wzu.pojo.student;
import java.util.List;
public class studentMapperTest {
//(1)查询计算机系年龄为19岁的女学生的信息
@Test
public void select_trim(){
SqlSession sqlSession = Util.createSqlSession();
studentMapper mapper = sqlSession.getMapper(studentMapper.class);
List<student> sel_student=mapper.select_trim("CS", 19,"女");
for (student teacher:sel_student){
System.out.println(teacher.toString());
}
Util.closeSqlSession(sqlSession);
}
}
二、动态SQL实现组合条件查询
1)使用动态SQL中choose+when+otherwise元素实现对student表中的数据的查询。
2)使用动态SQL中set元素实现对student表中的数据的修改。
3)使用动态SQL中foreach元素实现对student表中的数据的批量查询、修改与删除。
1、实体类
package wzu.pojo;
public class student {
private int sno;
private String sname;
private String ssex;
private int sage;
private String sdept;
public student() {
}
@Override
public String toString() {
return "student{" +
"sno=" + sno +
", sname='" + sname + '\'' +
", ssex='" + ssex + '\'' +
", sage=" + sage +
", sdept='" + sdept + '\'' +
'}';
}
public student(int sno, String sname, String ssex, int sage, String sdept) {
this.sno = sno;
this.sname = sname;
this.ssex = ssex;
this.sage = sage;
this.sdept = sdept;
}
public void setSno(int sno) {
this.sno = sno;
}
}
2、实体映射(接口)类
package wzu.mapper;
import org.apache.ibatis.annotations.Param;
import wzu.pojo.student;
import java.util.List;
import java.util.Map;
public interface studentMapper {
//(1)使用动态SQL中choose+when+otherwise元素实现对数据库记录的各种条件查询
List<student> select_choose(@Param("Sname") String sname,
@Param("Sage") int sage,
@Param("Sdept") String sdept);
//(2)使用动态SQL中set元素实现对student表中的数据的修改。
void set(student stu);
//(3)使用动态SQL中foreach元素实现对student表中的数据的批量查询、修改与删除。
// 3.1 查询学号在:201215121-201215123这个范围内的学生的记录。
List<student> selectByIds_foreach(List<Integer> ids);
// 3.2 将学号在:201215122-201215123这个范围内的学生的性别修改为男。
void updateByIds_foreach(Map<String, Object> updateMap);
// 3.3 删除学号在:201215121-201215123这个范围内的学生的记录。
void delByIds_foreach(List<Integer> ids);
}
3、 实体映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="wzu.mapper.studentMapper">
<!-- (1)使用动态SQL中choose+when+otherwise元素实现对数据库记录的各种条件查询-->
<select id="select_choose" resultType="wzu.pojo.student">
select * from student
<where>
<choose>
<when test="Sname !=null and Sname !=''">
sname like '%${Sname}%'
</when>
<when test="Sage !=null and Sage !='-1' ">
sage = '${Sage}'
</when>
<otherwise>
sdept = '${Sdept}'
</otherwise>
</choose>
</where>
</select>
<!-- (2)使用动态SQL中set元素实现对student表中的数据的修改。-->
<update id="set" parameterType="wzu.pojo.student">
update student
<set>
sage=sage+1,
sdept='MA'
</set>
where sno=#{sno}
</update>
<!--(3)使用动态SQL中foreach元素实现对student表中的数据的批量查询、修改与删除。-->
<!-- // 3.1 查询学号在:201215121-201215123这个范围内的学生的记录。-->
<select id="selectByIds_foreach" resultType="wzu.pojo.student">
select * from student
where sno in
<foreach collection="list" item="ids" open="(" separator="," close=")">
#{ids}
</foreach>
</select>
<!-- // 3.2 将学号在:201215122-201215123这个范围内的学生的性别修改为男。-->
<update id="updateByIds_foreach" parameterType="java.util.HashMap">
update student
<set>
ssex ='${ssex}'
</set>
where sno in
<foreach collection="ids" item="ids" open="(" separator="," close=")">
#{ids}
</foreach>
</update>
<!-- // 3.3 删除学号在:201215121-201215123这个范围内的学生的记录。-->
<delete id="delByIds_foreach">
delete from student
where sno in
<foreach collection="list" item="ids" open="(" separator="," close=")">
#{ids}
</foreach>
</delete>
</mapper>
4、mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 引入database.properties文件-->
<!-- <properties resource="database.properties"/>-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
<!-- useUnicode=true&characterEncoding=utf8&useSSL=false&-->
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 将mapper文件加入配置文件中-->
<mappers>
<mapper resource="wzu/mapper/studentMapper.xml"/>
</mappers>
</configuration>
5、Util 类
package wzu;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class Util {
private static SqlSessionFactory factory;
static { //静态 - 只创建一次factory
String resource= "mybatis-config.xml";
try {
InputStream is = Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession createSqlSession(){
return factory.openSession(false); // true - 自动提交事务
}
public static void closeSqlSession(SqlSession sqlSession){
if(null!=sqlSession){
sqlSession.close();
}
}
}
6、测试类
package wzu.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import wzu.Util;
import wzu.mapper.studentMapper;
import wzu.pojo.student;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class studentMapperTest {
// (1)使用动态SQL中choose+when+otherwise元素实现对数据库记录的各种条件查询
@Test
public void select_choose(){
SqlSession sqlSession = Util.createSqlSession();
studentMapper mapper = sqlSession.getMapper(studentMapper.class);
List<student> sel_student=mapper.select_choose("", -1,"CS");
for (student teacher:sel_student){
System.out.println(teacher.toString());
}
Util.closeSqlSession(sqlSession);
}
// (2)使用动态SQL中set元素实现对student表中的数据的修改。
@Test
public void set(){
SqlSession sqlSession = Util.createSqlSession();
studentMapper mapper = sqlSession.getMapper(studentMapper.class);
student stu=new student();
stu.setSno(201215180);
mapper.set(stu);
sqlSession.commit();
Util.closeSqlSession(sqlSession);
}
//(3)使用动态SQL中foreach元素实现对student表中的数据的批量查询、修改与删除。
// 3.1 查询学号在:201215121-201215123这个范围内的学生的记录。
@Test
public void selectByIds_foreach(){
SqlSession sqlSession = Util.createSqlSession();
studentMapper mapper = sqlSession.getMapper(studentMapper.class);
// 学号在:201215121-201215123这个范围
int ids_begin=201215121;
int ids_over=201215123;
List<Integer> stuidList=new ArrayList<Integer>(ids_over - ids_begin + 1);
for(int i=ids_begin;i<=ids_over;i++){
stuidList.add(i);
}
// 查询记录
List<student> sel_student=mapper.selectByIds_foreach(stuidList);
for (student teacher:sel_student){
System.out.println(teacher.toString());
}
Util.closeSqlSession(sqlSession);
}
// 3.2 将学号在:201215122-201215123这个范围内的学生的性别修改为男。
@Test
public void updateByIds_foreach(){
SqlSession sqlSession = Util.createSqlSession();
studentMapper mapper = sqlSession.getMapper(studentMapper.class);
int ids_begin=201215122;
int ids_over=201215123;
List<Integer> stuidList=new ArrayList<Integer>(ids_over - ids_begin + 1);
for(int i=ids_begin;i<=ids_over;i++){
stuidList.add(i);
}
Map<String, Object> map = new HashMap<>();
map.put("ids",stuidList);
map.put("ssex","男");
mapper.updateByIds_foreach(map);
sqlSession.commit();
Util.closeSqlSession(sqlSession);
}
// 3.3 删除学号在:201215121-201215123这个范围内的学生的记录。
@Test
public void delByIds_foreach(){
SqlSession sqlSession = Util.createSqlSession();
studentMapper mapper = sqlSession.getMapper(studentMapper.class);
int ids_begin=201215121;
int ids_over=201215123;
List<Integer> stuidList=new ArrayList<Integer>(ids_over - ids_begin + 1);
for(int i=ids_begin;i<=ids_over;i++){
stuidList.add(i);
}
mapper.delByIds_foreach(stuidList);
sqlSession.commit();
Util.closeSqlSession(sqlSession);
}
}