Spring+Mybatis+数据库连接池摸索过程

Spring+Mybatis+数据库连接池

1.依赖项

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.16</version>
    <scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.3.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.3.6</version>
    <scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.3.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.3</version>
</dependency>

mybatis注意项：

添加以下标签，在进行编译的过程中对.properties和xml配置文件不进行过滤
<build>
    <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>
</build>

2.填写数据库基础连接信息

application.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatistest?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
jdbc.user=root
jdbc.password=root

3.利用Spring的IOC创建所需的实体

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       https://www.springframework.org/schema/context/spring-context.xsd">

    <!--开启包扫描-->
    <context:component-scan base-package="com.hzu.*"/>
    <!--使用@AutoWired,@Resource注解-->
    <context:annotation-config/>

    <!--读取.properties文件，location指定其路径-->
    <context:property-placeholder location="config/setting/application.properties"/>

    <!--C3P0连接池的基础配置，读取application.properties文件的内容-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!--Druid连接池的基础配置，读取application.properties文件的内容-->
    <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!--创建mybatis会话工厂-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="druidDataSource"/>
        <property name="mapperLocations" value="classpath:com/hzu/dao/*Mapper.xml"/>
    </bean>
    <!--创建mybatis会话template-->
    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="sqlSessionFactory"/>
    </bean>
    <!--注册接口类的bean，使得程序中可以用注解方式获取-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.hzu.dao"/>
    </bean>
</beans>

4.查询实体

Student.class

package com.hzu.po;

import lombok.*;

/**
 * @Author Changqing
 * @Description: Student实现类
 * @Date 2021/3/28 16:07
 * @Version 1.0.0
 */
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    Integer id;
    String name;
    String email;
    Integer age;
}

5.DAO

StudentDao.class

package com.hzu.dao;

import com.hzu.po.Student;
import com.hzu.vo.QueryParam;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @Author FangChangqing
 * @Description: student表的Dao接口
 * @Date 2021/3/28 15:51
 * @Version 1.0.0
 */
public interface StudentDao {

    /**
     * @Description 查询student表的所有数据
     * @Param [NULL]
     * @Return [List<Student>] student表的所有内容
     */
    public List<Student> selectStudents();

    /**
     * @Description 向数据库中插入数据
     * @Param [student] 表示要插入到数据库的数据
     * @Return [int] 表示执行insert操作后影响数据库的行数
     */
    public int insertStudent(Student student);

    /**
     * @Description 查询指定ID的学生的所有信息
     * @Param [id] 需要查询学生的id
     * @Return [Student] 该id学生的所有信息
     */
    public Student selectStudentById(Integer id);

    /**
     * @Description 查询指定ID和AGE的学生的所有信息(存在同名同年龄)
     * @Param [name] 学生的名字
     * @Param [age] 学生的年龄
     * @Return [List<Student>] 指定ID和AGE的学生的所有信息
     */
    public List<Student> selectStudentsByNameAndAge(@Param("Name") String name, @Param("Age") Integer age);

    /**
     * @Description 同上
     * @Param [queryParam.paramName] 学生的姓名
     * @Param [queryParam.paramAge] 学生的年龄
     * @Return [List<Student>] 指定ID和AGE的学生的所有信息
     */
    public List<Student> selectStudentsByObject(QueryParam queryParam);

    /**
     * @Description 通过姓名模糊查询查询学生信息
     * @Param [target] 模糊查询的字段
     * @Return [List<Student>] 模糊查询到的学生的所有信息
     */
    public List<Student> selectStudentsNameLike(@Param("target") String target);

    /**
     * @Description 条件查询
     * @Param [Student] name和age其中一个参数
     * @Return [List<Student>] 条件查询结果
     */
    public List<Student> selectStudentsIf(Student student);

    /**
     * @Description
     * @Param [Student]
     * @Return [List<Student>]
     */
    public List<Student> selectStudentWhere(Student student);

    /**
     * @Description
     * @Param [null]
     * @Return [List<Student>]
     */
    public List<Student> selectStudentFor();
}

其中QueryParam.class文件内容

package com.hzu.vo;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@AllArgsConstructor
public class QueryParam {
    private String paramName;
    private Integer paramAge;
}

6.XML映射文件

StudentDaoMapper.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.hzu.dao.StudentDao">

    <select id="selectStudents" resultType="com.hzu.po.Student">
        select id, name, email, age
        from student
        order by id
    </select>

    <select id="insertStudent">
        insert into student values(#{id},#{name},#{email},#{age})
    </select>

    <select id="selectStudentById" resultType="com.hzu.po.Student" parameterType="java.lang.Integer">
        select id, name, email, age
        from student
        where id=#{id}
    </select>

    <!-- 多个参数使用@Param注解 -->
    <select id="selectStudentsByNameAndAge" resultType="com.hzu.po.Student">
        select id, name, email, age
        from student
        where name=#{Name} or age=#{Age}
    </select>

    <!-- 多个参数，使用java对象的属性值，作为参数的实际值
        完整的语法格式： #{属性名,javaType=类型名称,jdbcType=数据类型} 很少使用
        javaType: 指java中的属性的数据类型
        jdbcType: 在数据库中的数据类型
        例如: #{paramName,javaType=java.lang.String,jdbcType=VARCHAR}

        简化方式: #{属性名}
     -->
    <select id="selectStudentsByObject" resultType="com.hzu.po.Student">
        select id, name, email, age
        from student
        where name=#{paramName} or age=#{paramAge}
    </select>

    <select id="selectStudentsNameLike" resultType="com.hzu.po.Student">
        select id, name, email, age
        from student
        where name like #{target}
    </select>

    <!-- if:test="使用参数java对象的属性值作为判断条件， 语法：属性=XXX值" -->
    <select id="selectStudentsIf" resultType="com.hzu.po.Student">
        select id,name,email,age from student
        where 1=1
        <if test="name != null and name !=''">
            name = #{name}
        </if>
        <if test="age > 0">
            and age > #{age}
        </if>
    </select>

    <select id="selectStudentWhere" resultType="com.hzu.po.Student">
        select id,name,email,age from student
        <where>
            <if test="name != null and name !=''">
                name = #{name}
            </if>
            <if test="age > 0">
                and age > #{age}
            </if>
        </where>
    </select>

    <select id="selectStudentFor" resultType="com.hzu.po.Student">
        select id,name,email,age from student
    </select>
</mapper>

        <!--
         sql映射文件：专门写sql语句，mybatis会执行这些sql
         1.指定约束文件：
             <!DOCTYPE mapper
              PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
              "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
            mybatis-3-mapper.dtd是约束文件的名称，扩展名是dtd的

         2.约束文件的作用： 限制、检查在当前文件中出现的标签，属性必须符合mybatis的要求

         3.mapper 是当前文件的根标签，必须的
           namespace：叫做命名空间，唯一值，可以是自定义的字符串。要求使用dao接口的全限定名称

         4.在当前文件中，可以使用特定的标签，表示数据的特定操作。
          <select>: 表示执行查询，select语句
          <update>: 表示更新数据的操作，就是在<update>标签中写的update sql语句
          <insert>: 表示插入，放的是insert语句
          <delete>: 表示删除，放的是delete语句
         -->

        <!--
          1.select表示查询操作。
            id->要执行的sql语句的唯一标识，mybatis会使用这个id的值来找到要执行的sql语句。可以自定义，但是要求使用接口中方法名称
            resultType->表示结果的类型，是sql语句执行后得到ResultSet,遍历这个ResultSet得到的java对象的类型。值为类型的全限定名称
         -->

7.测试

package com.hzu;

import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:bean.xml")
public class BaseTest {
}

package com.hzu;

import com.hzu.dao.StudentDao;
import com.hzu.po.Student;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

public class TestStudentDao extends BaseTest {
    @Autowired
    StudentDao studentDao;

    @Test
    public void testSelectStudent() {
        List<Student> students = studentDao.selectStudents();
        for (Student student : students) {
            System.out.println("student = " + student);
        }
    }
}

数据库内容：

测试结果：