Mybatis的动态sql语句

if标签

if类似于java中的if语句，是Mybatis中最常用的判断语句。

if语句常常与test属性联用使用，语法如下:

<if test="判断条件">
    SQL语句
</if>

判断条件为true的时候才会执行sql语句

最常见的场景是在if语句中包含where 子句,例如。

两种格式如下:

  <select id="seach" resultType="com.wang.bean.Emp" parameterType="com.wang.bean.Emp">
        <!--        select * from emp where 1=1-->
        <!--        <if test="ename !=null and ename !=''">-->
        <!--            and ename like concat('%',#{ename},'%')-->
        <!--        </if>-->
        <!--        <if test="address !=null and address !=''">-->
        <!--            and address like concat('%',#{address},'%')-->
        <!--        </if>-->
        select * from emp
        <where>
            <if test="ename !=null and ename !=''">
                and ename like concat('%',#{ename},'%')
            </if>
            <if test="address !=null and address !=''">
                and address like concat('%',#{address},'%')
            </if>
        </where>
    </select>

课后作业

编写数据库

将属性私有化

    private int id;
    private String name;
    private int age;
    private String gender;
    private Double score;

编写dao包

package com.wang.dao;

import com.wang.bean.Student;

import java.util.List;

public interface StudentDao {
    //根据id查询学生信息
    Student selectid(int id);

    //查询所以学生信息，根据成绩从高到低排序
    List<Student> selectScore();

    //查询所有女子信息，并且从小到大排序
    List<Student> selectage(String gender);

    //添加信息
    int add(Student student);

    //更新数据
    int update(Student student);

    //删除学生信息
    int del(int id);
}

mapper映射

<?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>
    <properties resource="jdbc.properties"/>
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>
    <!--将所以bean起别名 -->
    <typeAliases>
        <package name="com.wang.bean"/>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

<!--    配置mapper与dao关联映射-->
    <mappers>
        <package name="com.wang.dao"/>
    </mappers>
</configuration>

编写mybatis的sql语句

<?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="com.wang.dao.StudentDao">
<!--    根据id查询学生信息-->
    <select id="selectAll" resultType="com.wang.bean.Student" parameterType="int">
        select * from student where id=#{id};
    </select>
<!--    查询所以学生信息，根据成绩从高到低排序-->
    <select id="selectScore" resultType="com.wang.bean.Student">
        select * from student order by score  desc;
    </select>
<!--    查询所有女子信息，并且从小到大排序-->
    <select id="selectage" resultType="com.wang.bean.Student" parameterType="com.wang.bean.Student">
        select * from student where gender=#{gender} order by age asc;
    </select>
<!--    添加-->
    <insert id="add" parameterType="com.wang.bean.Student">
        insert into student(name,age,gender,score) values(#{name},#{age},#{gender},#{score});
    </insert>
<!--    更新数据-->
    <update id="update" parameterType="com.wang.bean.Student">
        update student set name=#{name},age=#{age},gender=#{gender},score=#{score} where id=#{id};
    </update>
<!--    删除一个数据-->
    <delete id="del" parameterType="int">
        delete from student where id=#{id};
    </delete>
</mapper>

test测试

package com.wang.test;

import com.wang.bean.Student;
import com.wang.dao.StudentDao;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class mytest {
    InputStream stream=null;
    SqlSessionFactory factory=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSession sqlSession=null;
    StudentDao studentDao=null;
    //初始化数据
    @Before
    public void init() throws IOException {
        stream= Resources.getResourceAsStream("SqlMapConfig.xml");
        builder=new SqlSessionFactoryBuilder();
        factory=builder.build(stream);
        sqlSession=factory.openSession();
        studentDao = sqlSession.getMapper(StudentDao.class);
    }

    //根据id查询学生信息
    @Test
    public void selectAll(){
        Student studentList = studentDao.selectid(1);
        System.out.println(studentList);
    }

    //查询所有女子信息，并且根据年龄从小到大排序
    @Test
    public void selectage(){
        List<Student> selectage = studentDao.selectage("女");
        for (Student student : selectage) {
            System.out.println(student);
        }
    }

    //查询所以学生信息，根据成绩从高到低排序
    @Test
    public void seach(){
        List<Student> students = studentDao.selectScore();
        for (Student student : students) {
            System.out.println(student);
        }
    }

    //添加数据
    @Test
    public void add(){
        Student student=new Student();
        student.setName("小帅");
        student.setAge(29);
        student.setGender("男");
        student.setScore(76.0);

        int i = studentDao.add(student);
        if(i>0){
            System.out.println("添加成功");
        }
    }

    //更新数据
    @Test
    public void update(){
        Student students = studentDao.selectid(6);
        System.out.println("修改前的数据"+students);
        students.setName("猴子");
        students.setAge(50);
        students.setGender("男");
        students.setScore(33.3);
        int i = studentDao.update(students);
        if(i>0){
           Student student=studentDao.selectid(6);
            System.out.println("修改后的数据"+student);
        }
    }

    //删除数据
    @Test
    public void del(){
        int i = studentDao.del(6);
        if(i>0){
            System.out.println("删除成功");
        }
    }

    //关闭资源
    @After
    public void close() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}