Mybatis 动态sql
一简介
动态 SQL
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。
动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。
目前， 动态 SQL 支持以下几种标签：
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
- bind
二 常用符号介绍
数据库建立：
学生表：
班级表：
代码实例：
1.实体类 StudentInfo
package cn.zjw.entity;
public class StudentInfo {
private String studentid;
private String studentName;
private String studentSex;
private String studentPhone;
private String studentAddress;
private int stuAge;
private int stuclassid;
// 一定要有一个空的无参的构造方法
private ClassInfo classInfo;
public String getStudentid() {
return studentid;
}
public void setStudentid(String studentid) {
this.studentid = studentid;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getStudentSex() {
return studentSex;
}
public void setStudentSex(String studentSex) {
this.studentSex = studentSex;
}
public String getStudentPhone() {
return studentPhone;
}
public void setStudentPhone(String studentPhone) {
this.studentPhone = studentPhone;
}
public String getStudentAddress() {
return studentAddress;
}
public void setStudentAddress(String studentAddress) {
this.studentAddress = studentAddress;
}
public int getStuAge() {
return stuAge;
}
public void setStuAge(int stuAge) {
this.stuAge = stuAge;
}
public int getStuclassid() {
return stuclassid;
}
public void setStuclassid(int stuclassid) {
this.stuclassid = stuclassid;
}
public ClassInfo getClassInfo() {
return classInfo;
}
public void setClassInfo(ClassInfo classInfo) {
this.classInfo = classInfo;
}
public StudentInfo(String studentid, String studentName, String studentSex, String studentPhone, String studentAddress, int stuAge, int stuclassid, ClassInfo classInfo) {
this.studentid = studentid;
this.studentName = studentName;
this.studentSex = studentSex;
this.studentPhone = studentPhone;
this.studentAddress = studentAddress;
this.stuAge = stuAge;
this.stuclassid = stuclassid;
this.classInfo = classInfo;
}
public StudentInfo() {
}
}
2.实体类 ClassInfo
package cn.zjw.entity;
import java.util.List;
public class ClassInfo {
private int classid;
private String classname;
private List<StudentInfo> studentInfoList;
public List<StudentInfo> getStudentInfoList() {
return studentInfoList;
}
public void setStudentInfoList(List<StudentInfo> studentInfoList) {
this.studentInfoList = studentInfoList;
}
public int getClassid() {
return classid;
}
public void setClassid(int classid) {
this.classid = classid;
}
public String getClassname() {
return classname;
}
public void setClassname(String classname) {
this.classname = classname;
}
public ClassInfo(int classid, String classname) {
this.classid = classid;
this.classname = classname;
}
public ClassInfo() {
}
}
3.StudentInfoMapper 接口
package cn.zjw.mapper;
import cn.zjw.entity.ClassInfo;
import cn.zjw.entity.StudentInfo;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface StudentInfoMapper {
// 根据班级编号查询所属学生信息
// id班级标号 返回的是所属班级的学生
public List<StudentInfo> findStuByClassId(int id);
/**
* 根据班级编号查询班级信息
* @param id 班级编号
* @return 班级信息 通过班级信息得到班级所属学生
*/
public ClassInfo findClassByClassId(int id);
/**
* 根据班级编号查询班级所属某个姓名的学生
* @param id 班级编号
* @return
*/
public List<StudentInfo> findStuNameByClassId(@Param("stuclassid") int stuclassid, @Param("studentName") String name);
public int update(StudentInfo si);
public StudentInfo findStudentById(String id);
}
4.StudentInfoMapper.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头部-->
<mapper namespace="cn.zjw.mapper.StudentInfoMapper">
<resultMap id="stulist" type="StudentInfo">
<result property="studentid" column="studentid"/>
<result property="studentName" column="studentName"/>
<result property="studentSex" column="studentSex"/>
<result property="studentPhone" column="studentPhone"/>
<result property="studentAddress" column="studentAddress"/>
<result property="stuAge" column="stuAge"/>
<result property="stuclassid" column="stuclassid"/>
<!--多对一 找到ClassInfo对象中属性classInfo-->
<association property="classInfo" javaType="ClassInfo" resultMap="classlist"></association>
</resultMap>
<select id="findStuByClassId" parameterType="int" resultMap="stulist">
select s.*,c.className from student s,class c where s.stuclassid=c.classid and s.stuclassid=#{stuclassid }
</select>
<!-- --------&#|45;---------------------------------------------------------------------------------->
<!--test2()-->
<resultMap id="classlist" type="ClassInfo">
<result property="classid" column="classid"/>
<result property="classname" column="classname"/>
<!--一对多-->
<collection property="studentInfoList" ofType="StudentInfo">
<result property="studentid" column="studentid"/>
<result property="studentName" column="studentName"/>
<result property="studentSex" column="studentSex"/>
<result property="studentPhone" column="studentPhone"/>
<result property="studentAddress" column="studentAddress"/>
<result property="stuAge" column="stuAge"/>
<result property="stuclassid" column="stuclassid"/>
</collection>
</resultMap>
<select id="findClassByClassId" parameterType="int" resultMap="classlist">
select s.*,c.classname from student s,class c where s.stuclassid=c.classid and c.classid=#{classid}
</select>
<!--test(3)-->
<select id="findStuNameByClassId" parameterType="int" resultType="StudentInfo">
select * from student
<where>
<trim suffix="" suffixOverrides="and">
<if test="stuclassid!=null">
stuclassid=#{stuclassid}
</if>
<if test="studentName!=null">
and studentName like concat(concat('%',#{studentName}),'%')
</if>
</trim>
</where>
</select>
<!--test4()更新-->
<select id="findStudentById" resultType="StudentInfo" parameterType="String">
select * from student where studentid=#{studentid}
</select>
<update id="update" parameterType="StudentInfo">
update student
<trim suffix="where studentid=#{studentid}" suffixOverrides=",">
<set>
<if test="studentid!=null">studentid=#{studentid},</if>
<if test="studentName!=null">studentName=#{studentName},</if>
<if test="studentSex!=null">studentSex=#{studentSex},</if>
<if test="studentPhone!=null">studentPhone=#{studentPhone},</if>
<if test="studentAddress!=null">studentAddress=#{studentAddress},</if>
<if test="stuAge!=null">stuAge=#{stuAge},</if>
<if test="stuclassid!=null">stuclassid=#{stuclassid},</if>
</set>
</trim>
</update>
</mapper>
5.MybatisUtil
package cn.zjw.util;
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 java.io.IOException;
import java.io.InputStream;
public class MybatisUtil {
//加载mybatis配置文件
//创建sqlsessionfactory对象
//创建sqlsession连接对象
//创建关闭sqlsession连接对象
private static SqlSessionFactory sessionFactory;
static {
String resource = "mybatis.xml";
try {
InputStream is = Resources.getResourceAsStream(resource);
sessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession getSession() {
return sessionFactory.openSession();
}
public static void closeSession(SqlSession session) {
if (session != null) {
session.close();
}
}
}
6.Mybatis.xml
<?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>
<typeAliases>
<package name="cn.zjw.entity"></package>
</typeAliases>
<environments default="zjw">
<environment id="zjw">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/school?characterEncoding=utf8&useSSL=false&serverTimezone=UTC"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</dataSource>
</environment>
</environments>
<mappers>
<!--注意：此处路径用/不用点-->
<mapper resource="cn/zjw/mapper/StudentInfoMapper.xml"/>
</mappers>
</configuration>
7.测试类
package cn.zjw.test;
import cn.zjw.entity.ClassInfo;
import cn.zjw.entity.StudentInfo;
import cn.zjw.mapper.StudentInfoMapper;
import cn.zjw.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class Test1 {
public static void main(String[] args) {
test1();
// test2();
// test3();
// test4();
}
private static void test1() {
SqlSession session = MybatisUtil.getSession();
List<StudentInfo> lists = session.getMapper(StudentInfoMapper.class).findStuByClassId(3);
for (StudentInfo l : lists) {
System.out.println(l.getStudentName() + "\t" + l.getClassInfo().getClassname());
}
}
private static void test2() {
SqlSession session = MybatisUtil.getSession();
ClassInfo ci = session.getMapper(StudentInfoMapper.class).findClassByClassId(2);
System.out.println(ci.getClassname());
List<StudentInfo> list = ci.getStudentInfoList();
for (StudentInfo l : list) {
System.out.println(l.getStudentName());
}
}
private static void test3() {
SqlSession session = MybatisUtil.getSession();
List<StudentInfo> lists = session.getMapper(StudentInfoMapper.class).findStuNameByClassId(2, null);
for (StudentInfo l : lists) {
System.out.println(l.getStudentName());
}
}
private static void test4() {
SqlSession session = MybatisUtil.getSession();
StudentInfo si= session.getMapper(StudentInfoMapper.class).findStudentById("1");
if(si!=null){
si.setStudentName("李大小");
int a=session.getMapper(StudentInfoMapper.class).update(si);
if(a>0){
System.err.println("ok");
}
session.commit();
}
}
}
运行结果：
test1
test2
test3
test4
扫一扫
892
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
