在spring boot中使用mybatis连接MySQL数据库并进行CURD操作（使用maven构建）

 

使用mybatis可以提高你的生产效率，可以有效避免将精力浪费在SQL语句的拼接上，将注意力集中在业务开发上面。

开始使用mybatis吧。

工程目录结构：

pom.xml:（注意点：手工加入mapper.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>mybatisTest</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>mybatistest/demo/mapper/StudentMapper.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>


        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

bean.Student.java（POJO对象）:

package mybatistest.demo.bean;

public class Student {
    Integer sid;
    String sname;
    Integer sage;

    public Integer getSid() {
        return sid;
    }

    public String getSname() {
        return sname;
    }

    public Integer getSage() {
        return sage;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

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

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

handler.CURDTest.java（CURD操作测试类）:

package mybatistest.demo.Handler;


import mybatistest.demo.bean.Student;
import mybatistest.demo.mapper.StudentMapper;
import mybatistest.demo.tools.DBTools;
import org.apache.ibatis.session.SqlSession;

import java.util.List;
/**
*@describe 数据库操作测试类
*@author JiangYiNeng
*@date 2018/4/26
*/
public class CURDTest {

    public static void selectStudentById(int id){
        SqlSession session= DBTools.getSession();
        StudentMapper mapper=session.getMapper(StudentMapper.class);
        Student a = new Student();
        a.setSid(id);
        try {
            List<Student> students = mapper.selectStudentById(a);

            for (Student c : students) {
                System.out.println(c.getSid());
                System.out.println(c.getSname());
                System.out.println(c.getSage());
            }
            if (students.size()==0){
                System.out.println("查询结果为空");
            }
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        }finally{
            session.close();
        }
    }

    public static boolean  updateStudentById(Student a){
        SqlSession session = DBTools.getSession();
        StudentMapper mapper=session.getMapper(StudentMapper.class);
        try {
            int index=mapper.insertStudent(a);
            boolean bool=index>0?true:false;
            session.commit();
            return bool;
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
            return false;
        }finally{
            session.close();
        }
    }

    public static boolean  deleteStudentById(int sid){
        SqlSession session = DBTools.getSession();
        StudentMapper mapper=session.getMapper(StudentMapper.class);
        try {
            int index=mapper.deleteStudentById(sid);
            boolean bool=index>0?true:false;
            session.commit();
            return bool;
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
            return false;
        }finally{
            session.close();
        }
    }

    public static boolean  insertStudent(Student a){
        SqlSession session = DBTools.getSession();
        StudentMapper mapper=session.getMapper(StudentMapper.class);
        try {
            int index=mapper.insertStudent(a);
            boolean bool=index>0?true:false;
            session.commit();
            return bool;
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
            return false;
        }finally{
            session.close();
        }
    }
}

mapper.StudentMapper.java（映射接口类）:

package mybatistest.demo.mapper;

import mybatistest.demo.bean.Student;
import org.springframework.stereotype.Component;

import java.util.List;
/**
 *@describe 这里写接口，每个接口的名字和mapper.xml的id一致
 *@author JiangYiNeng
 *@date 2018/4/26
 */
@Component
public interface StudentMapper {
    /**
     *@describe 插入学生接口
     *@author JiangYiNeng
     *@date 2018/4/26
     */

    int insertStudent(Student a) throws Exception;
    /**
    *@describe 根据id更新学生信息
    *@author JiangYiNeng
    *@date 2018/4/26
    */

    int updateStudentById(Student a) throws Exception;

    /**
     *@describe 根据id选择学生接口
     *@author JiangYiNeng
     *@date 2018/4/26
     */
    List<Student> selectStudentById(Student a) throws Exception;

    /**
    *@describe 根据id删除学生接口
    *@author JiangYiNeng
    *@date 2018/4/26
    */
    int deleteStudentById(int sid) throws Exception;
}

mapper.StudentMapper.xml（映射配置文件）:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--写上映射地图java的位置-->
<mapper namespace="mybatistest.demo.mapper.StudentMapper">


    <insert id="insertStudent" parameterType="Student">
        insert into Student (sname,sage) values (${sname},${sage})
    </insert>

    <update id="updateStudentById" parameterType="Student">
        update Student set sname=${sname},sage=${sage} where sid=${sid}
    </update>

    <select id="selectStudentById" parameterType="Student"  resultType="Student">
        select * from Student where sid=${sid}
    </select>

    <delete id="deleteStudentById" parameterType="int">
        delete from Student where sid=#{sid}
    </delete>


</mapper>

dbtools.DBTools（生成sqlsession的工具类）:

package mybatistest.demo.tools;

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.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;

import java.io.Reader;

public class DBTools {
    public static SqlSessionFactory sessionFactory;

    static{
        try {
            //使用MyBatis提供的Resources类加载mybatis的配置文件
            Reader reader = Resources.getResourceAsReader("mybatis/mybatis.cfg.xml");
            //构建sqlSession的工厂
            sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    //创建能执行映射文件中sql的sqlSession
    public static SqlSession getSession(){
        return sessionFactory.openSession();
    }

}

springboot启动类（注意：修改了默认注释，防止数据库未配置报错（我们使用了mybatis方式连接数据库，没用springboot方式连接数据库））:

我们在这里调用了数据库的测试方法

package mybatistest.demo;

import mybatistest.demo.Handler.CURDTest;
import mybatistest.demo.bean.Student;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);

        //实例化一个学生对象并插入数据库
        Student a = new Student();
        a.setSname("'小明'");//这里要加上单引号，防止拼接SQL语句报错
        a.setSage(22);
        if(CURDTest.insertStudent(a)){
            System.out.println("插入成功");
        }else {
            System.out.println("插入失败");
        }

        //根据id查找学生
        CURDTest.selectStudentById(1);

        //实例化一个学生对象并更新数据库
        Student b = new Student();
        b.setSid(1);
        b.setSname("'小王'");//这里要加上单引号，防止拼接SQL语句报错
        b.setSage(24);
        if (CURDTest.updateStudentById(b)){
            System.out.println("更新成功");
        }else {
            System.out.println("更新失败");
        }

        //根据id删除学生
        if (CURDTest.deleteStudentById(1)){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }

}

mybaitis.cfg.xml（mybatis配置文件，这里设置数据库连接方式）

<?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>

    <!-- 引入外部配置文件 -->


    <!-- 为JAVA Bean起类别名 -->
    <typeAliases >
        <!-- 别名方式1，一个一个的配置 type中放置的是类的全路径，alias中放置的是类别名
        <typeAliase type="com.cy.mybatis.beans.UserBean" alias="UserBean"/> -->
        <!-- 别名方式2，自动扫描，将JAVA类的类名作为类的类别名 -->
        <!--这里放pojo（bean）对象的位置-->
        <package name="mybatistest.demo.bean"/>
    </typeAliases>



    <!-- 配置mybatis运行环境 -->
    <environments default="cybatis">
        <environment id="cybatis">
            <!-- type="JDBC" 代表使用JDBC的提交和回滚来管理事务 -->
            <transactionManager type="JDBC" />

            <!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI -->
            <!-- POOLED 表示支持JDBC数据源连接池 -->
            <!-- UNPOOLED 表示不支持数据源连接池 -->
            <!-- JNDI 表示支持外部数据源连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost/Student" />
                <property name="username" value="root" />
                <property name="password" value="" />
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <!-- 告知映射文件方式1，一个一个的配置
        <mapper resource="com/cy/mybatis/mapper/UserMapper.xml"/>-->
        <!-- 告知映射文件方式2，自动扫描包内的Mapper接口与配置文件 -->
        <package name="mybatistest/demo/mapper"/>

    </mappers>

</configuration>

application.properties（这行配置可以实现控制台自动打印SQL语句，方便观察）：

logging.level.mybatistest.demo.mapper=debug

创建数据库代码：

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` text,
  `sage` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', 'xiaoming', '22');

运行结果：

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.0.1.RELEASE)

2018-04-26 11:28:39.875  INFO 6780 --- [           main] mybatistest.demo.DemoApplication         : Starting DemoApplication on DESKTOP-J6EKINS with PID 6780 (C:\Users\qq415\Desktop\work\mybatisdemo\target\classes started by qq415 in C:\Users\qq415\Desktop\work\mybatisdemo)
2018-04-26 11:28:39.878  INFO 6780 --- [           main] mybatistest.demo.DemoApplication         : No active profile set, falling back to default profiles: default
2018-04-26 11:28:39.914  INFO 6780 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@1761df8: startup date [Thu Apr 26 11:28:39 CST 2018]; root of context hierarchy
2018-04-26 11:28:40.367  INFO 6780 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-04-26 11:28:40.376  INFO 6780 --- [           main] mybatistest.demo.DemoApplication         : Started DemoApplication in 0.75 seconds (JVM running for 1.259)
2018-04-26 11:28:40.634 DEBUG 6780 --- [           main] m.d.mapper.StudentMapper.insertStudent   : ==>  Preparing: insert into Student (sname,sage) values ('小明',22) 
2018-04-26 11:28:40.647 DEBUG 6780 --- [           main] m.d.mapper.StudentMapper.insertStudent   : ==> Parameters: 
2018-04-26 11:28:40.653 DEBUG 6780 --- [           main] m.d.mapper.StudentMapper.insertStudent   : <==    Updates: 1
插入成功
2018-04-26 11:28:40.657 DEBUG 6780 --- [           main] m.d.m.StudentMapper.selectStudentById    : ==>  Preparing: select * from Student where sid=1 
2018-04-26 11:28:40.657 DEBUG 6780 --- [           main] m.d.m.StudentMapper.selectStudentById    : ==> Parameters: 
2018-04-26 11:28:40.665 DEBUG 6780 --- [           main] m.d.m.StudentMapper.selectStudentById    : <==      Total: 1
1
xiaoming
22
2018-04-26 11:28:40.666 DEBUG 6780 --- [           main] m.d.mapper.StudentMapper.insertStudent   : ==>  Preparing: insert into Student (sname,sage) values ('小王',24) 
2018-04-26 11:28:40.666 DEBUG 6780 --- [           main] m.d.mapper.StudentMapper.insertStudent   : ==> Parameters: 
2018-04-26 11:28:40.667 DEBUG 6780 --- [           main] m.d.mapper.StudentMapper.insertStudent   : <==    Updates: 1
更新成功
2018-04-26 11:28:40.668 DEBUG 6780 --- [           main] m.d.m.StudentMapper.deleteStudentById    : ==>  Preparing: delete from Student where sid=? 
2018-04-26 11:28:40.669 DEBUG 6780 --- [           main] m.d.m.StudentMapper.deleteStudentById    : ==> Parameters: 1(Integer)
2018-04-26 11:28:40.669 DEBUG 6780 --- [           main] m.d.m.StudentMapper.deleteStudentById    : <==    Updates: 1
删除成功
2018-04-26 11:28:40.674  INFO 6780 --- [       Thread-4] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@1761df8: startup date [Thu Apr 26 11:28:39 CST 2018]; root of context hierarchy
2018-04-26 11:28:40.675  INFO 6780 --- [       Thread-4] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown