[大数据]第七个MyBatis练习，（动态sql的实现，使用 ＜if＞ ＜where＞＜foreach＞标签，分页，数据库连接信息分离）

项目结构

1.创建Student实体类

package com.it.domain;

public class Student {
    private int sno;
    private String sname;
    private  String ssex;
    private int sage;
    private String sdept;

    public int getSno() {
        return sno;
    }

    public void setSno(int sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    public int getSage() {
        return sage;
    }

    public void setSage(int sage) {
        this.sage = sage;
    }

    public String getSdept() {
        return sdept;
    }

    public void setSdept(String sdept) {
        this.sdept = sdept;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sno=" + sno +
                ", sname='" + sname + '\'' +
                ", ssex='" + ssex + '\'' +
                ", sage=" + sage +
                ", sdept='" + sdept + '\'' +
                '}';
    }
}

2.创建StudentDao接口类

package com.it.dao;

import com.it.domain.Student;

import java.util.List;

public interface StudentDao {
    //动态sql要使用java对象作为参数

    //if标签的使用
List<Student> selectStudentIf(Student student);
//where标签的使用
List<Student> selectStudentWhere(Student student);
//foreach标签的使用，参数是整形的集合
List<Student> selectForeachOne(List<Integer> idList);
//foreach标签的使用，参数是整形的集合
List<Student> selectForeachTwo(List<Student> studentList);
//测试分页
List<Student> selectAll();

}

3.创建StudentDao接口类的映射文件StudentDao.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="com.it.dao.StudentDao">

<!--     sql代码片段先定义再使用，复用sql语句减少代码的冗余，
id:自定义名称
使用时用include标签的refid属性指定该sql的id即可
-->
    <sql id="selectAll">
        select sno,sname,sage,ssex,sdept from student
    </sql>

<!--    查询姓名和年龄都符合参数要求的数据对象，where语句和if标签之间是and的连接关系-->
<select id="selectStudentIf" resultType="com.it.domain.Student">

<include refid="selectAll"></include>
    where 1=1
    <if test="sname!=null and sname!=''">
        and sname=#{sname}
    </if>
    <if test="sage>0">
        and sage>#{sage}
    </if>
</select>

<!-- <where> 用来包含 多个<if>的， 当多个if有一个成立的， <where>会自动增加一个where关键字，
            并去掉 if中多余的 and ，or等-->
    <select id="selectStudentWhere" resultType="com.it.domain.Student">
        <include refid="selectAll"></include>
        <where>
            <if test="sname!=null and sname!=''">
                sname=#{sname}
            </if>
            <if test="sage>0">
                and sage>#{sage}
            </if>
        </where>
    </select>

<!--<foreach> 循环java中的数组，list集合的。 主要用在sql的in语句中
当前传递过来的参数是整形集合
-->
    <select id="selectForeachOne" resultType="com.it.domain.Student">
        select * from student where sno in
        <foreach collection="list" item="i" open="(" close=")" separator=",">
        #{i}
        </foreach>
    </select>

<!--传递过来的参数是Student集合-->
    <select id="selectForeachTwo" resultType="com.it.domain.Student">
        SELECT * FROM student where sno in
        <foreach collection="list" item="i" open="(" separator="," close=")">
            #{i.sno}
        </foreach>
    </select>

<!--测试分页-->
    <select id="selectAll" resultType="com.it.domain.Student">
        select * from student order by sno
    </select>


</mapper>

4.创建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>

<!--    指定properties文件的位置，从类路径根开始找文件-->
    <properties resource="jdbc.properties"></properties>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
<!--    配置分页插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

    <environments default="mydev">
        <environment id="mydev">
            <transactionManager type="JDBC"/>
            <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>
    <mappers>
<!--        <mapper resource="com/it/dao/StudentDao.xml"/>-->

<!--        当需要映射多个文件时，可以使用包名，会自动映射该包下的所有文件
要求：接口名和mapper名一样，且在同一目录下-->
        <package name="com.it.dao"/>
    </mappers>
</configuration>

5.创建MyBatisUntils工具类

package com.it.untils;

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 MyBatisUntils {
    private static SqlSessionFactory factory = null;
//    写在静态代码块中，可以避免SqlSessionFactory对象重复创建，该对象创建耗时较长，
//    且创建一次可以重复使用，故套写在静态代码块中，当类加载的时候才会运行一次

    static {
        String config="mybatis.xml";
        InputStream resourceAsStream=null;
        try {
            resourceAsStream = Resources.getResourceAsStream(config);
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            factory = builder.build(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //获取SqlSession的方法
    public static SqlSession getSqlSession(){
        SqlSession sqlSession=null;
        if (factory!=null){
            sqlSession = factory.openSession(true);
        }
        return sqlSession;
    }
}

6.创建测试类

package com.it;



import com.github.pagehelper.PageHelper;
import com.it.dao.StudentDao;
import com.it.domain.Student;
import com.it.untils.MyBatisUntils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

/**
 * Unit test for simple App.
 */
public class AppTest 
{
@Test
    public void testSelectStudentIf(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    Student student=new Student();
    student.setSname("小白");
    student.setSage(18);
    List<Student> studentList = mapper.selectStudentIf(student);
    for (Student stu:studentList){
        System.out.println(stu);
    }
}
@Test
    public void testSelectStudentWhere(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    Student student=new Student();
    student.setSname("小明");
    student.setSage(20);
    mapper.selectStudentWhere(student);
}
@Test
    public  void testSelectForech(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    List<Integer> list=new ArrayList<>();
    list.add(1);
    list.add(2);
    list.add(3);
    List<Student> studentList = mapper.selectForeachOne(list);
    for (Student stu:studentList){
        System.out.println(stu);
    }
}
@Test
    public void testSelectForeach2(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    List<Student> list=new ArrayList<>();
    Student s1=new Student();
    s1.setSno(2);
    list.add(s1);
    s1=new Student();
    s1.setSno(5);
    list.add(s1);
    List<Student> studentList = mapper.selectForeachTwo(list);
    for (Student student:studentList){
        System.out.println(student);
    }
}

@Test
    public void testAllPageHelper(){
    SqlSession sqlSession = MyBatisUntils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    //加入PageHelper的方法，分页，第一个参数为分页起始位置，第二个参数为每个页的数据量的大小
    PageHelper.startPage(0,3);
    List<Student> studentList = mapper.selectAll();
    for (Student stu:studentList){
        System.out.println(stu);
    }
}

}

7.依赖

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.it</groupId>
  <artifactId>MyBatis4</artifactId>
  <packaging>pom</packaging>
  <version>1.0-SNAPSHOT</version>
  <modules>
    <module>MyBatis4Model</module>
    <module>MyBatis5</module>

  </modules>
<!--jdk版本-->
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <!-- mybatis依赖-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.3</version>
    </dependency>
    <!--mysql驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.37</version>
    </dependency>
<!--    测试依赖-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

<!-- PageHelper依赖-->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.10</version>
    </dependency>

  </dependencies>

  <build>
<!--    设置编译compile生成traget文件时可以把后缀为xml的文件一起加入进去-->
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.properties</include>
          <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
      </resource>
      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>**/*.properties</include>
          <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
      </resource>
    </resources>
<!--下面是自动生成的插件-->
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

数据库内容

结果展示

1.if标签的使用

2.where标签的使用

3.foreach> 循环java中的数组，list集合的。 主要用在sql的in语句中 ，当前传递过来的参数是整形集合

4.foreach> 循环java中的数组，list集合的。 主要用在sql的in语句中 ，当前传递过来的参数是Student对象集合

5.分页，需要使用mevan加载该插件才可以使用

mybatis主配置文件配置插件

?

?6.?把数据库连接信息放到一个单独的文件中。 和mybatis主配置文件分开。
???? 目的是便于修改，保存，处理多个数据库的信息。

jdbc.properties文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=123456

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系