一、动态SQL
1、if 元素
(1)数据准备(MySQL)
drop database if exists chapter04;
create database chapter04;
use chapter04;
create table student(
sid int primary key auto_increment,
sname varchar(20),
age varchar(20),
course varchar(20)
);
insert into student(sname,age,course) values('ZhangSan','20','Java');
insert into student(sname,age,course) values('Lisi','21','Java');
insert into student(sname,age,course) values('Lisi','20','Python');
insert into student(sname,age,course) values('WangWu','19','Java');
(2)创建 POJO 类 Student.java(新建包 com.qf.chapter04.pojo)
package com.qf.chapter04.pojo;
public class Student {
private int sid;
private String sname;
private String age;
private String course;
public Student() {
super();
}
public Student(int sid, String sname, String age, String course) {
super();
this.sid = sid;
this.sname = sname;
this.age = age;
this.course = course;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", " + "age=" + age + ", " + "course=" + course + "]";
}
}
(3)修改配置文件 mybatis-config.xml(resources目录)
注:修改数据库名称,添加映射文件位置
<?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="mysql">
<environment id="mysql">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/chapter04" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 配置映射文件的位置 -->
<mappers>
<mapper resource="chapter04/StudentMapper.xml"/>
</mappers>
</configuration>
(4)创建映射文件 StudentMapper.xml( resources 目录下新建目录 chapter04)
<?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="student">
<select id="findStudentBySnameAndCourse" parameterType="com.qf.chapter04.pojo.Student"
resultType="com.qf.chapter04.pojo.Student">
select * from student where sname=#{sname}
<!--根据条件动态拼装SQL语句 -->
<if test=" null!=course and ''!=course ">
and course =#{course}
</if>
</select>
</mapper>
(5)测试类 TestIf.java (新建包 com.qf.chapter04.test)
package com.qf.chapter04.test;
import com.qf.chapter04.pojo.Student;
import java.io.*;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestIf {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
Student student = new Student();
student.setSname("LiSi");
student.setCourse("Java");
List<Student> selectList = sqlSession.selectList("student.findStudentBySnameAndCourse", student);
for (Student stu : selectList) {
System.out.println(stu.toString());
}
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
2、choose、when、otherwise 元素
(1)新增映射文件 StudentMapper.xml 内容( resources 目录下的 chapter04 目录)
<select id="findStudentByChoose" parameterType="com.qf.chapter04.pojo.Student"
resultType="com.qf.chapter04.pojo.Student">
select * from student where 1=1
<choose>
<!--如果sid不为null或空字符串 -->
<when test=" null!=sid and ''!=sid ">
and sid=#{sid}
</when>
<!--如果sname不为null或空字符串 -->
<when test=" null!=sname and ''!=sname ">
and sname like '%${sname}%'
</when>
<!--如果以上两个条件都不满足,则执行下列内容 -->
<otherwise>
and course='Java'
</otherwise>
</choose>
</select>
(2)测试类 TestChoose.java (新建包 com.qf.chapter04.test)
package com.qf.chapter04.test;
import java.io.*;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
import com.qf.chapter04.pojo.Student;
public class TestChoose {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new
SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
Student student = new Student();
student.setSid(2);
List<Student> selectList =
sqlSession.selectList("student.findStudentByChoose",student);
for (Student stu : selectList) {
System.out.println(stu.toString());
}
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
3、trim 元素
(1)修改映射文件 StudentMapper.xml( resources 目录下的 chapter04 目录)
<select id="findStudentByChoose" parameterType="com.qf.chapter04.pojo.Student"
resultType="com.qf.chapter04.pojo.Student">
select * from student
<trim prefix="where" prefixOverrides="and">
<if test="null!=sid and ''!=sid">
and sid=#{sid}
</if>
<if test="null!=sname and ''!=sname">
and sname like '%${sname}%'
</if>
<if test="null==course">
and course='Java'
</if>
</trim>
</select>
(2)运行 TestChoose.java 进行测试
4、set 元素
(1)新增映射文件 StudentMapper.xml 内容( resources 目录下的 chapter04 目录)
<update id="updateStu" parameterType="com.qf.chapter04.pojo.Student">
update student
<set>
<if test="null!=sname and ''!=sname">
sname=#{sname},
</if>
<if test="null!=age and ''!=age">
age=#{age},
</if>
</set>
where sid=#{sid}
</update>
(2)测试类 TestSet.java (新建包 com.qf.chapter04.test)
package com.qf.chapter04.test;
import java.io.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
import com.qf.chapter04.pojo.Student;
public class TestSet {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
Student student = new Student();
// 为Student对象的成员属性赋值
student.setSid(4);
student.setSname("ZhaoLiu");
student.setAge("20");
// 调用sqlSession的update()方法
int result = sqlSession.update("student.updateStu", student);
if (result > 0) {
System.out.println("成功更新" + result + "条数据");
} else {
System.out.println("更新操作失败");
}
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
二、注解
1、@Select 注解
(1)创建 StudentMapper 接口(新建包 com.qf.chapter04.mapper)
package com.qf.chapter04.mapper;
import com.qf.chapter04.pojo.Student;
import org.apache.ibatis.annotations.Select;
public interface StudentMapper {
@Select("select * from student where sid=#{sid}")
Student selectStudent(int sid);
}
(2)修改配置文件 mybatis-config.xml(resources目录)
注:添加映射文件位置
<mappers>
<mapper class="com.qf.chapter04.mapper.StudentMapper" />
</mappers>
(3)测试类 TestFindBySid.java(com.qf.chapter04.test)
package com.qf.chapter04.test;
import java.io.*;
import com.qf.chapter04.mapper.StudentMapper;
import com.qf.chapter04.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestFindBySid {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
// 获取映射接口
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
// 调用映射接口 selectStudent 方法
Student student = mapper.selectStudent(1);
System.out.println(student.toString());
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
2、@Insert注解
(1)在 StudentMapper 接口新增内容(com.qf.chapter04.mapper)
@Insert("insert into student(sname,age,course) "
+ " values(#{sname},#{age},#{course})")
int insertStudent(Student student);
(2)测试类 TestFindBySid.java(com.qf.chapter04.test)
package com.qf.chapter04.test;
import java.io.*;
import com.qf.chapter04.mapper.StudentMapper;
import com.qf.chapter04.pojo.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestInsert {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
Student student = new Student();
student.setSname("ZhouBa");
student.setAge("21");
student.setCourse("Java");
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
int result = mapper.insertStudent(student);
if (result > 0) {
System.out.println("成功插入" + result + "条数据");
} else {
System.out.println("插入操作失败");
}
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
3、@Update注解
(1)在 StudentMapper 接口新增内容(com.qf.chapter04.mapper)
@Update("update student "
+ "set sname=#{sname}, age=#{age} where sid=#{sid}")
int updateStudent(Student student);
(2)测试类 TestUpdate.java(com.qf.chapter04.test)
package com.qf.chapter04.test;
import java.io.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
import com.qf.chapter04.mapper.StudentMapper;
import com.qf.chapter04.pojo.Student;
public class TestUpdate {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
Student student = new Student();
student.setSid(5);
student.setSname("WuJiu");
student.setCourse("Python");
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
int result = mapper.updateStudent(student);
if (result > 0) {
System.out.println("成功更新" + result + "条数据");
} else {
System.out.println("更新操作失败");
}
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
4、@Delete注解
(1)在 StudentMapper 接口新增内容(com.qf.chapter04.mapper)
@Delete("delete from student where sid=#{sid}")
int deleteStudent(int sid);
(2)测试类 TestDelete.java(com.qf.chapter04.test)
package com.qf.chapter04.test;
import com.qf.chapter04.mapper.StudentMapper;
import java.io.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestDelete {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
int result = mapper.deleteStudent(5);
if (result > 0) {
System.out.println("成功删除" + result + "条数据");
} else {
System.out.println("删除操作失败");
}
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
5、@Param注解
(1)在 StudentMapper 接口新增内容(com.qf.chapter04.mapper)
@Select("select * from student where sname=#{param01} "
+ "and course=#{param02}")
Student selectBySnameAndCourse(@Param("param01") String sname,
@Param("param02") String course);
(2)测试类 TestSelect.java(com.qf.chapter04.test)
package com.qf.chapter04.test;
import com.qf.chapter04.mapper.StudentMapper;
import com.qf.chapter04.pojo.Student;
import java.io.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class TestSelect {
public static void main(String[] args) {
String resource = "mybatis-config.xml";
try {
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = factory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectBySnameAndCourse("LiSi", "Java");
System.out.println(student.toString());
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}