使用spring整合mybatis连接mysql数据库的一个完整实例

项目完整结构:

pom.xml:

<?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.jnshu</groupId>
<artifactId>wq_task1_spring-mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<!--//定义spring包统一版本号-->
<spring.version>4.3.14.RELEASE</spring.version>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>

<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>

<!--Spring.jar start-->
<!--包含Spring框架基本的核心工具类，Spring其它组件要都要使用到这个包里的类，是其它组件的基本核心-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<!--jar文件是所有应用都要用到的，它包含访问配置文件、创建和管理bean
以及进行Inversion of Control / Dependency Injection（IoC/DI）操作相关的所有类-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<!--为Spring核心提供了大量扩展。可以找到使用Spring ApplicationContext特性时所需的全部类，JDNI所需的全部类，
UI方面的用来与模板（Templating）引擎如Velocity、FreeMarker、JasperReports集成的类，以及校验Validation方面的相关类-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<!--这个jar文件包含对Spring对JDBC数据访问进行封装的所有类。-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!--Spring表达式语言-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${spring.version}</version>
</dependency>
<!--是spring-context.RELEASE.jar依赖包.是spring事务处理的jar-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<!--commons-logging.jar包是使用spring的必备包,用来记录程序运行时的活动的日志记录.如果缺少就会报错-->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.1</version>
</dependency>
<!--这个jar文件包含Web应用开发时，用到Spring框架时所需的核心类，包括自动载入WebApplicationContext特性的类、Struts与JSF集成类、
文件上传的支持类、Filter类和大量工具辅助类。 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.3.14.RELEASE</version>
</dependency>
<!--　这个jar文件包含Spring MVC框架相关的所有类。包含国际化、标签、Theme、
视图展现的FreeMarker、JasperReports、Tiles、Velocity、XSLT相关类。-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.14.RELEASE</version>
</dependency>
<!--spring End-->

<!--日志包 Start-->
<!--log4j2依赖包-->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.8.2</version>
</dependency>

<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.8.2</version>
</dependency>
<!--slf4j依赖-->
<!--slf4j-api作为日志接入的接口,不负责具体的日志实现，只在编译时负责寻找合适的日志系统进行绑定-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.22</version>
</dependency>

<!--log4j2和slf4j桥接依赖，桥接作用-->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
<version>2.8.2</version>
<exclusions>
<exclusion>
<artifactId>slf4j-api</artifactId>
<groupId>org.slf4j</groupId>
</exclusion>
</exclusions>
</dependency>
<!--日志包 End-->

<!--Mybatis Start-->
<!--Mybatis的核心包-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
<!--集合spring与mybatis框架的工具包-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
<!--Mybatis END-->

<!--Mysql数据库连接-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>

<!--dbcp数据库连接池的jar包，用来在applicationContext.xml中配置数据库 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
</dependencies>
</project>

Student实体类:

package com.jnshu.pojo;

public class Student {
    //实体类.属性与数据表一一对应.然后添加set/get方法,toString方法
    private int id;
    private String name;
    private String major;
    private long admission_time;
    private String graduate_school;
    private long online_id;
    private String diary_link;
    private String wish;
    private String guide;
    private long create_time;
    private long update_time;

    public Student() {
    }

    public Student(String name, String major, long admission_time, String graduate_school, long online_id, String diary_link, String wish, String guide,long create_time,long update_time) {
        this.name = name;
        this.major = major;
        this.admission_time = admission_time;
        this.graduate_school = graduate_school;
        this.online_id = online_id;
        this.diary_link = diary_link;
        this.wish = wish;
        this.guide = guide;
        this.create_time = create_time;
        this.update_time=update_time;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public long getAdmission_time() {
        return admission_time;
    }

    public void setAdmission_time(long admission_time) {
        this.admission_time = admission_time;
    }

    public String getGraduate_school() {
        return graduate_school;
    }

    public void setGraduate_school(String graduate_school) {
        this.graduate_school = graduate_school;
    }

    public long getOnline_id() {
        return online_id;
    }

    public void setOnline_id(long online_id) {
        this.online_id = online_id;
    }

    public String getDiary_link() {
        return diary_link;
    }

    public void setDiary_link(String diary_link) {
        this.diary_link = diary_link;
    }

    public String getWish() {
        return wish;
    }

    public void setWish(String wish) {
        this.wish = wish;
    }

    public String getGuide() {
        return guide;
    }

    public void setGuide(String guide) {
        this.guide = guide;
    }

    public long getCreate_time() {
        return create_time;
    }

    public void setCreate_time(long create_time) {
        this.create_time = create_time;
    }

    public long getUpdate_time() {
        return update_time;
    }

    public void setUpdate_time(long update_time) {
        this.update_time = update_time;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", major='" + major + '\'' +
                ", admission_time='" + admission_time + '\'' +
                ", graduate_school='" + graduate_school + '\'' +
                ", online_id=" + online_id +
                ", diary_link='" + diary_link + '\'' +
                ", wish='" + wish + '\'' +
                ", guide='" + guide + '\'' +
                ", create_time='" + create_time + '\'' +
                ", update_time='" + update_time + '\'' +
                '}';
    }
}

StudentMapper:

package com.jnshu.mapper;

import com.jnshu.pojo.Student;

public interface StudentMapper {
//    Dao层接口.定义基础增删改查操作,但不实现
//    具体的实现在studentMapper.xml中
    int insertStudent(Student student);
    int deleteStudent(String name);
    int updateStudent(Student student);

    Student queryStudentById(int id);
    Student queryStudentByName(String name);
    Student queryStudentByOnline_Id(long online_id);
}

StudentMapper.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">
<!--命名空间.与StudentMapper绑定-->
<mapper namespace="com.jnshu.mapper.StudentMapper">
    <!--定义结果集-->
    <resultMap id="studentResultMap" type="com.jnshu.pojo.Student">
        <id property="id" column="id" javaType="java.lang.Long" />
        <result property="name" column="name" javaType="java.lang.String" jdbcType="VARCHAR"/>
        <result property="major"  column="major" javaType="java.lang.String" jdbcType="VARCHAR"/>
        <result property="admission_time" column="admission_time" javaType="java.lang.Long" jdbcType="BIGINT"/>
        <result property="graduate_school" column="graduate_school" javaType="java.lang.String" jdbcType="VARCHAR"/>
        <result property="online_id" column="online_id" javaType="java.lang.Long" jdbcType="BIGINT"/>
        <result property="diary_link" column="diary_link" javaType="java.lang.String" jdbcType="VARCHAR" />
        <result property="wish" column="wish" javaType="java.lang.String" jdbcType="VARCHAR" />
        <result property="guide" column="guide" javaType="java.lang.String" jdbcType="VARCHAR"/>
        <result property="create_time" column="create_time" javaType="java.lang.Long" jdbcType="BIGINT"/>
        <result property="update_time" column="update_time" javaType="java.lang.Long" jdbcType="BIGINT"/>
    </resultMap>

    <insert id="insertStudent" parameterType="student"
            keyProperty="id" useGeneratedKeys="true">
        insert into student (name,major,admission_time,graduate_school,
        online_id,diary_link,wish,guide,create_time,update_time)
                values(#{name},#{major},#{admission_time},
        #{graduate_school},#{online_id},#{diary_link},#{wish},
        #{guide},#{create_time},#{update_time})
    </insert>
    <update id="updateStudent" parameterType="student">
        update student set wish=#{wish} where name=#{name}
    </update>
    <delete id="deleteStudent" parameterType="java.lang.String">
        delete from student where name =#{name}
    </delete>
    <select id="queryStudentById" parameterType="java.lang.Integer" resultType="student">
        select * from student where id=#{id}
    </select>
    <select id="queryStudentByName" parameterType="java.lang.String" resultType="student">
        select * from student where name=#{name}
    </select>
    <select id="queryStudentByOnline_Id" parameterType="java.lang.Long" resultType="student">
        select * from student where online_id=#{online_id}
    </select>
</mapper>

log4j2:

<?xml version="1.0" encoding="UTF-8" ?>
<!--status：打印日志级别-->
<configuration status="error">

    <!--定义所有的appender-->
    <appenders>
        <!--输出到控制台的配置-->
        <Console name="Console" target="SYSTEM_OUT">
            <!--控制台只输出level及以上的信息-->
            <ThresholdFilter level="info" onMatch="ACCEPT"
                             onMismatch="DENY"/>
            <!-- 日志输入格式-->
            <PatternLayout pattern="%d %-5level %class(36) %L %M - %msg%xEx%n"/>
        </Console>
        <!--指定log打印位置-->
        <File name="log" fileName="f:/log4jlog/wq_task1_sm.log" append="false">
            <PatternLayout pattern="%d %-5level %class(36) %L %M - %msg%xEx%n"/>
        </File>
        <!--指定error级以上打印位置-->
        <File name="ERROR" fileName="f:/log4jlog/wq_task1_sm.log">
            <ThresholdFilter level="error" onMatch="ACCEPT" onMismatch="DENY"/>
            <PatternLayout pattern="%d %-5level %class(36) %L %M - %msg%xEx%n"/>
        </File>
        <!--打印所有信息，但是超过设置的日志大小限制就会滚动保存，新的日志会覆盖老的日志-->
        <RollingFile name="RollingFileError" fileName="${sys:user.home}/logs/error.log"
                     filePattern="${sys:user.home}/logs/$${date:yyyy-MM}/error-%d{yyyy-MM-dd}-%i.log">
            <ThresholdFilter level="error" onMatch="ACCEPT" onMismatch="DENY"/>
            <PatternLayout pattern="[%d{HH:mm:ss:SSS}] [%p] - %l - %m%n"/>
            <Policies>
                <TimeBasedTriggeringPolicy/>
                <SizeBasedTriggeringPolicy size="100 MB"/>
            </Policies>
        </RollingFile>



    </appenders>
    <!--定义logger，只有定义logger并引入appender，appender才会生效-->
    <loggers>
        <logger name="org.springframework" level="Error"/>
        <logger name="org.mybatis" level="Error"/>
        <logger name="org.eclipse.jetty" level="Error"/>
        <logger name="org.apache.tomcat" level="Error"/>

        <root level="info">
            <!--ref值与上面的name一一对应-->
            <appender-ref ref="RollingFileError"/>
            <appender-ref ref="Console"/>
            <appender-ref ref="ERROR"/>
            <appender-ref ref="log"/>
        </root>
    </loggers>
</configuration>

db.properties:

#数据库信息
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/wang?characterEncoding=UTF-8&allowMultiQueries=true
jdbc.username=root
jdbc.password=123456wq

Mybatis-config.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>
        <typeAlias type="com.jnshu.pojo.Student" alias="student"/>
    </typeAliases>
</configuration>

applicationContext.xml:

<?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 http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 加载数据库配置文件-->
    <context:property-placeholder location="db.properties"/>
    <!-- 开启后使用注解-->
    <context:annotation-config/>
    <!-- 在spring中注入注解-->
    <context:component-scan base-package="com.jnshu.*"/>

    <!-- 配置数据源-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="maxActive" value="20"/>
        <property name="maxWait" value="180"/>
        <property name="maxIdle" value="5"/>
    </bean>

    <!--创建sqlSessionFactoryBean
    绑定Mybatis_congif.xml,绑定datasource,绑定studentMapper.xml-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
          <property name="configLocation" value="Mybatis-config.xml"/>
          <property name="dataSource" ref="dataSource"/>
          <property name="mapperLocations" value="studentMapper.xml"/>
    </bean>

    <!--将映射器注册到sping中-->
    <bean id="studentMapper" class=
            "org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value=
                "com.jnshu.mapper.StudentMapper"/>
        <property name="sqlSessionFactory" ref=
                "sqlSessionFactory"/>
    </bean>

</beans>

StudentService:

package com.jnshu.service;

import com.jnshu.pojo.Student;

public interface StudentService {
    //Service层的接口.具体的实现在StudentServiceImpl
    //在实际应用中不会直接调用Dao层,而是在Service层按需求组合一系列Dao层中的crud操作,再调用StudentServiceImpl.
    public int insertStudent(Student student);
    public int insertStudentReturnId(Student student);
    public int deleteStudent(String name);
    public int updateStudent(Student student);
    /*public Student queryStudentByName(String name);*/
    public Student queryStudentByName(String name);
    public Student queryStudentByID(int id);


}

StudentServiceImpl:

package com.jnshu.serviceimpl;

import com.jnshu.client.Client;
import com.jnshu.mapper.StudentMapper;
import com.jnshu.pojo.Student;
import com.jnshu.service.StudentService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

//StudentService的实现类.
@Service(value = "studentService")
//打上@Service注解
public class StudentServiceImpl implements StudentService {
    @Autowired
    //自动装配
    StudentMapper studentMapper;
    private static final Logger log= LogManager.getLogger(Client.class);
    @Autowired
    //构造注入
    public StudentServiceImpl(StudentMapper studentMapper)
    {this.studentMapper=studentMapper;}


    @Override
    public int insertStudent(Student student){
        studentMapper.insertStudent(student);
        return student.getId();
    }
    @Override
    //插入一条数据,返回他的id
    public int insertStudentReturnId(Student student){
        studentMapper.insertStudent(student);
        studentMapper.queryStudentByOnline_Id(student.getOnline_id());
        log.info(student.getId());
        return student.getId();
    }
    @Override
    public int deleteStudent(String name) {
        int result = studentMapper.deleteStudent(name);
        boolean flag = result>0?true:false;
        log.info(flag);
        return result;
    }

    @Override
    public int updateStudent(Student student) {
        int result = studentMapper.updateStudent(student);
        boolean flag = result>0?true:false;
        log.info(flag);
        return result;
    }

    @Override
    public Student queryStudentByName(String name) {
        Student student = studentMapper.queryStudentByName(name);
        log.info(student);
        return student;
    }

    @Override
    public Student queryStudentByID(int id) {
        Student student = studentMapper.queryStudentById(id);
        log.info(student);
        return student;
    }

}

测试类TestSpringMybatis:

import com.jnshu.client.Client;
import com.jnshu.pojo.Student;
import com.jnshu.service.StudentService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestSpringMybatis {
    private static final Logger log= LogManager.getLogger(Client.class);
    ApplicationContext context=new
            ClassPathXmlApplicationContext("applicationContext.xml");
    StudentService studentService= (StudentService) context.getBean("studentService");
    @Test
    //测试插入一条数据,但不打印信息
    public void insert(){
        Student student =new Student();
        student.setOnline_id(1181);
        student.setWish("6666");
        student.setName("wangquan");
        studentService.insertStudent(student);
    }
    @Test
    //测试插入一条数据后返回它的id
    public void insertReturnId(){
        Student student =new Student();
        student.setOnline_id(11814);
        student.setWish("6666");
        student.setName("wangquan");
        studentService.insertStudentReturnId(student);
    }
    @Test
    //测试根据名字查询
    public void queryByName() {
        studentService.queryStudentByName("wangquan");
    }
    @Test
    //测试根据id查询
    public void queryById(){studentService.queryStudentByID(56);}
    @Test
    //测试修改立愿
    public void update(){
        Student student =new Student();
        student.setName("wangquajhghn");
        student.setWish("写代码");
        studentService.updateStudent(student);
    }
    @Test
    //测试根据姓名删除某些数据
    public void delete(){
        studentService.deleteStudent("wangquan");
    }
}