SpringBoot Mybatis连接MySQL数据库

淩亂°似流年 2022-05-29 08:53 278阅读 0赞

SpringBoot整合Mybatis还是比较简单的，本篇文章讲解使用Mapper.xml来写SQL。

（1）build.gradle文件添加依赖

// https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper
        compile group: 'com.github.pagehelper', name: 'pagehelper', version: '5.1.2'
    
    
        // https://mvnrepository.com/artifact/mysql/mysql-connector-java
        compile group: 'mysql', name: 'mysql-connector-java', version: '6.0.6'
    
        // https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter
        compile group: 'org.mybatis.spring.boot', name: 'mybatis-spring-boot-starter', version: '1.3.2'

（2）数据库表结构

数据库为wzj，数据库表为teacher，创建表语句如下：

create table teacher(
     tec_id varchar(16) not null primary key,
     tec_name varchar(16) not null,
     tec_age tinyint unsigned,
     tec_desc varchar(128)
    );

数据库有一条数据

![20180320232924393][]

（3）工程结构

controller--控制器包

entity--实体类，和数据库表结构映射

mybatis--mybatis配置

resources/mapper-- sql语句

> ![20180320233054724][]
> 
>   
>

（4）添加@MapperScan注解

在SpringBoot启动类上添加@MapperScan("com.wzj.demo.mapper")，com.wzj.demo.mapper为mapper接口的包

package com.wzj.demo;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.builder.SpringApplicationBuilder;
    import org.springframework.boot.web.support.SpringBootServletInitializer;
    
    @SpringBootApplication
    @MapperScan("com.wzj.demo.mapper")
    public class SpringBootWebSocketApplication  extends SpringBootServletInitializer{
    
    	public static void main(String[] args) {
    		SpringApplication.run(SpringBootWebSocketApplication.class, args);
    	}
    	
    
    	/**
    	 * Configure the application. Normally all you would need to do it add sources (e.g.
    	 * config classes) because other settings have sensible defaults. You might choose
    	 * (for instance) to add default command line arguments, or set an active Spring
    	 * profile.
    	 *
    	 * @param builder a builder for the application context
    	 * @return the application builder
    	 * @see SpringApplicationBuilder
    	 */
    	@Override
    	protected SpringApplicationBuilder configure(SpringApplicationBuilder builder)
    	{
    		return super.configure(builder);
    	}
    }

（5）添加MySQL数据库连接配置到application.properties

下面是我的数据库连接参数，将下面几行，添加到application.properties中

spring.datasource.url = jdbc:mysql://192.168.3.45:3306/wzj
    spring.datasource.username=root
    spring.datasource.password=12345
    spring.datasource.driverClassName=com.mysql.jdbc.Driver

（6）新建MyBatisConfiguration.java文件

package com.wzj.demo.mybaties;
    
    import com.github.pagehelper.PageHelper;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import java.util.Properties;
    
    /**
     * Created by wzj on 2018/3/17.
     */
    @Configuration
    @ConfigurationProperties(prefix = "spring.datasource")
    public class MyBatisConfiguration
    {
        /**
         * url
         */
        private String url;
    
        /**
         * 驱动名称
         */
        private String driverClassName;
    
        /**
         * 数据库名
         */
        private String username;
    
        /**
         * 数据库密码
         */
        private String password;
    
        public String getUrl()
        {
            return url;
        }
    
        public void setUrl(String url)
        {
            this.url = url;
        }
    
        public String getDriverClassName()
        {
            return driverClassName;
        }
    
        public void setDriverClassName(String driverClassName)
        {
            this.driverClassName = driverClassName;
        }
    
        public String getUsername()
        {
            return username;
        }
    
        public void setUsername(String username)
        {
            this.username = username;
        }
    
        public String getPassword()
        {
            return password;
        }
    
        public void setPassword(String password)
        {
            this.password = password;
        }
    
        @Bean
        public PageHelper pageHelper() {
            System.out.println("MyBatisConfiguration.pageHelper()");
            PageHelper pageHelper = new PageHelper();
            Properties p = new Properties();
            p.setProperty("offsetAsPageNum", "true");
            p.setProperty("rowBoundsWithCount", "true");
            p.setProperty("reasonable", "true");
            pageHelper.setProperties(p);
            return pageHelper;
        }
    
    //    @Bean(name = "dataSource")
    //    public DriverManagerDataSource dataSource(){
    //        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
    //        driverManagerDataSource.setDriverClassName(driverClassName);
    //        driverManagerDataSource.setUrl(url);
    //        driverManagerDataSource.setUsername(username);
    //        driverManagerDataSource.setPassword(password);
    //        return driverManagerDataSource;
    //    }
    
    
    }

（7）新建TeacherBean.java实体类,和数据库表结构映射

package com.wzj.demo.entity;
    
    /**
     * Created by wzj on 2018/3/17.
     */
    public class TeacherBean
    {
        private String tecId;
    
        private String tecName;
    
        private int tecAge;
    
        private String tecDesc;
    
    
        public String getTecId()
        {
            return tecId;
        }
    
        public void setTecId(String tecId)
        {
            this.tecId = tecId;
        }
    
        public String getTecName()
        {
            return tecName;
        }
    
        public void setTecName(String tecName)
        {
            this.tecName = tecName;
        }
    
        public int getTecAge()
        {
            return tecAge;
        }
    
        public void setTecAge(int tecAge)
        {
            this.tecAge = tecAge;
        }
    
        public String getTecDesc()
        {
            return tecDesc;
        }
    
        public void setTecDesc(String tecDesc)
        {
            this.tecDesc = tecDesc;
        }
    
        @Override
        public String toString()
        {
            return "TeacherBean{" + "tecId='" + tecId + '\'' + ", tecName='" + tecName + '\'' + ", tecAge=" + tecAge + ", tecDesc='" + tecDesc + '\'' + '}';
        }
    }

（8）新建TeacherMapper.java文件

该文件定义数据库查询接口

package com.wzj.demo.mapper;
    
    import com.wzj.demo.entity.TeacherBean;
    import org.apache.ibatis.annotations.Param;
    
    /**
     * Created by wzj on 2018/3/18.
     */
    public interface TeacherMapper
    {
        /**
         * 通过id查询老师信息
         * @param id id
         * @return teacher信息
         */
        TeacherBean getTeacherById(@Param("id") String id);
    }

（9）在/resources/mapper/目录新建TeacherMapper.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.wzj.demo.mapper.TeacherMapper">
        <resultMap id="BaseResultMap" type="com.wzj.demo.entity.TeacherBean">
            <result column="tec_id" property="tecId" jdbcType="VARCHAR"/>
            <result column="tec_name" property="tecName" jdbcType="VARCHAR"/>
            <result column="tec_age" property="tecAge" jdbcType="INTEGER"/>
        </resultMap>
    
        <sql id="Base_Column_List">
            tec_id, tec_name, tec_age
        </sql>
    
        <select id="getTeacherById" resultMap="BaseResultMap" parameterType="java.lang.String">
            select
            <include refid="Base_Column_List" />
            from teacher
            where tec_id = #{id}
        </select>
    </mapper>

（10）新建控制器TeacherController.java和服务TeacherService.java

package com.wzj.demo.controller;
    
    import com.wzj.demo.service.TeacherService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    /**
     * Created by wzj on 2018/3/18.
     */
    @RestController
    public class TeacherController
    {
        /**
         * 注入服务
         */
        @Autowired
        private TeacherService teacherService;
    
        /**
         * 查询老师信息
         * @param id id
         * @return 信息
         */
        @RequestMapping(value = "/teacher")
        public String getTeacherMessage(String id)
        {
            return teacherService.queryTeacher(id).toString();
        }
    }

package com.wzj.demo.service;
    
    import com.wzj.demo.entity.TeacherBean;
    import com.wzj.demo.mapper.TeacherMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    /**
     * Created by wzj on 2018/3/18.
     */
    @Service
    public class TeacherService
    {
        /**
         * 注入服务
         */
        @Autowired
        private TeacherMapper teacherMapper;
    
        public TeacherBean queryTeacher(String id)
        {
            return teacherMapper.getTeacherById(id);
        }
    }

（11）测试

启动SpringBoot，浏览器输入[http://127.0.0.1:8080/teacher?id=1001][http_127.0.0.1_8080_teacher_id_1001]

![20180320234548309][]

Demo Github地址： [https://github.com/HelloKittyNII/SpringBoot/tree/master/SpringBootDemo][https_github.com_HelloKittyNII_SpringBoot_tree_master_SpringBootDemo]

[20180320232924393]: /images/20220529/d941c5e79b44453f95302609190066e8.png
[20180320233054724]: /images/20220529/31a5dbd4f8cb49528501a67429616d69.png
[http_127.0.0.1_8080_teacher_id_1001]: http://127.0.0.1:8080/teacher?id=1001
[20180320234548309]: /images/20220529/eee12ee411f04a4eb533d565c7101b5d.png
[https_github.com_HelloKittyNII_SpringBoot_tree_master_SpringBootDemo]: https://github.com/HelloKittyNII/SpringBoot/tree/master/SpringBootDemo