Mybatis之整合SpringBoot

缺乏、安全感 2022-04-11 04:40 290阅读 0赞

我们新建一个Mybatis的Maven项目，使用MyBatisGenerator插件自动生成相关的代码

pom.xml

<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.yj</groupId>
    	<artifactId>MyBatis</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<packaging>jar</packaging>
    
    	<name>MyBatis</name>
    	<url>http://maven.apache.org</url>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    	</properties>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>1.5.2.RELEASE</version>
    		<relativePath />
    	</parent>
    
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>1.3.0</version>
    		</dependency>
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>1.1.0</version>
    		</dependency>
    	</dependencies>
    	
    	<build>
    		<finalName>${artifactId}</finalName>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    				<configuration>
    					<skip>true</skip>
    				</configuration>
    			</plugin>
    			<plugin>
    				<groupId>org.apache.maven.plugins</groupId>
    				<artifactId>maven-jar-plugin</artifactId>
    				<configuration>
    
    					<archive>
    						<manifest>
    							<mainClass>com.yj.Application</mainClass>
    							<addClasspath>true</addClasspath>
    							<classpathPrefix>lib</classpathPrefix>
    						</manifest>
    						<manifestEntries>
    							<Class-Path>./</Class-Path>
    						</manifestEntries>
    					</archive>
    					<excludes>
    						<exclude>config/**</exclude>
    						<exclude>**.xml</exclude>
    						<exclude>**.properties</exclude>
    					</excludes>
    				</configuration>
    			</plugin>
    			<plugin>
    				<artifactId>maven-assembly-plugin</artifactId>
    				<configuration>
    					<appendAssemblyId>false</appendAssemblyId>
    					<descriptors>
    						<descriptor>src/main/build/package.xml</descriptor>
    					</descriptors>
    				</configuration>
    				<executions>
    					<execution>
    						<id>make-assembly</id>
    						<phase>package</phase>
    						<goals>
    							<goal>single</goal>
    						</goals>
    					</execution>
    				</executions>
    			</plugin>
    		</plugins>
    		<testResources>
    			<testResource>
    				<directory>src/main/resources</directory>
    			</testResource>
    		</testResources>
    	</build>
    </project>

application.properties

server.port=9001
    
    spring.datasource.driverClassName = com.mysql.jdbc.Driver
    spring.datasource.url = jdbc:mysql://192.168.124.129:3306/docker?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true
    spring.datasource.username = root
    spring.datasource.password = 123456
    spring.datasource.type= com.alibaba.druid.pool.DruidDataSource
    spring.datasource.initialSize= 5
    spring.datasource.minIdle= 5
    spring.datasource.maxActive= 20
    spring.datasource.maxWait= 60000
    spring.datasource.timeBetweenEvictionRunsMillis= 60000
    spring.datasource.minEvictableIdleTimeMillis= 300000
    spring.datasource.validationQuery= SELECT 1 FROM DUAL
    spring.datasource.testWhileIdle= true
    spring.datasource.testOnBorrow= false
    spring.datasource.testOnReturn= false
    spring.datasource.poolPreparedStatements= true
    spring.datasource.maxPoolPreparedStatementPerConnectionSize= 20
    # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 
    spring.datasource.filters= stat,wall,log4j
    spring.datasource.connectionProperties= druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
     
    #配置mybatis
    mybatis.config-location=classpath:mybatis-config.xml
    mybatis.mapper-locations=classpath:mapper/*.xml

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>
    	<settings>
    		<setting name="cacheEnabled" value="true"/>
    		<setting name="lazyLoadingEnabled" value="true"/>
    		<setting name="defaultStatementTimeout" value="20000"/>
            <setting name="aggressiveLazyLoading" value="false"/>
    	</settings>
    
        <typeAliases>
            <typeAlias alias="User" type="com.yj.model.User" />
        </typeAliases>
    </configuration>

MybatisConfig

package com.yj.config;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.context.annotation.Configuration;
    
    @Configuration
    @MapperScan("com.yj.dao")
    public class MybatisConfig {
    }

DruidDBConfig

package com.yj.config;
    
    import java.sql.SQLException;
    import javax.sql.DataSource;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import com.alibaba.druid.pool.DruidDataSource;
    
    @Configuration
    public class DruidDBConfig {
    
    	private Logger log = LoggerFactory.getLogger(this.getClass());
    
    	@Value("${spring.datasource.url}")
    	private String dbUrl;
    
    	@Value("${spring.datasource.username}")
    	private String username;
    
    	@Value("${spring.datasource.password}")
    	private String password;
    
    	@Value("${spring.datasource.driverClassName}")
    	private String driverClassName;
    
    	@Value("${spring.datasource.initialSize}")
    	private int initialSize;
    
    	@Value("${spring.datasource.minIdle}")
    	private int minIdle;
    
    	@Value("${spring.datasource.maxActive}")
    	private int maxActive;
    
    	@Value("${spring.datasource.maxWait}")
    	private int maxWait;
    
    	@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    	private int timeBetweenEvictionRunsMillis;
    
    	@Value("${spring.datasource.minEvictableIdleTimeMillis}")
    	private int minEvictableIdleTimeMillis;
    
    	@Value("${spring.datasource.validationQuery}")
    	private String validationQuery;
    
    	@Value("${spring.datasource.testWhileIdle}")
    	private boolean testWhileIdle;
    
    	@Value("${spring.datasource.testOnBorrow}")
    	private boolean testOnBorrow;
    
    	@Value("${spring.datasource.testOnReturn}")
    	private boolean testOnReturn;
    
    	@Value("${spring.datasource.poolPreparedStatements}")
    	private boolean poolPreparedStatements;
    
    	@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    	private int maxPoolPreparedStatementPerConnectionSize;
    
    	@Value("${spring.datasource.filters}")
    	private String filters;
    
    	@Value("${spring.datasource.connectionProperties}")
    	private String connectionProperties;
    
    	@Bean //声明其为Bean实例
    	@Primary //在同样的DataSource中，首先使用被标注的DataSource
    	public DataSource dataSource() {
    		DruidDataSource datasource = new DruidDataSource();
    
    		datasource.setUrl(this.dbUrl);
    		datasource.setUsername(username);
    		datasource.setPassword(password);
    		datasource.setDriverClassName(driverClassName);
    		
    		datasource.setInitialSize(initialSize);
    		datasource.setMinIdle(minIdle);
    		datasource.setMaxActive(maxActive);
    		datasource.setMaxWait(maxWait);
    		datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
    		datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
    		datasource.setValidationQuery(validationQuery);
    		datasource.setTestWhileIdle(testWhileIdle);
    		datasource.setTestOnBorrow(testOnBorrow);
    		datasource.setTestOnReturn(testOnReturn);
    		datasource.setPoolPreparedStatements(poolPreparedStatements);
    		datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
    		try {
    			datasource.setFilters(filters);
    		} catch (SQLException e) {
    			log.error("配置监控统计拦截的filters时发生异常", e);
    		}
    		datasource.setConnectionProperties(connectionProperties);
    		return datasource;
    	}
    }

UserMapper

package com.yj.dao;
    
    import org.springframework.stereotype.Repository;
    import com.yj.model.User;
    
    @Repository
    public interface UserMapper {
        int deleteByPrimaryKey(Integer id);
    
        int insert(User record);
    
        int insertSelective(User record);
    
        User selectByPrimaryKey(Integer id);
    
        int updateByPrimaryKeySelective(User record);
    
        int updateByPrimaryKey(User record);
    }

UserMapper

<?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.yj.dao.UserMapper">
      <resultMap id="BaseResultMap" type="User">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
      </resultMap>
      <sql id="Base_Column_List">
        id, name
      </sql>
      <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select 
        <include refid="Base_Column_List" />
        from user
        where id = #{id,jdbcType=INTEGER}
      </select>
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from user
        where id = #{id,jdbcType=INTEGER}
      </delete>
      <insert id="insert" keyColumn="id" keyProperty="id" parameterType="User" useGeneratedKeys="true">
        insert into user (name)
        values (#{name,jdbcType=VARCHAR})
      </insert>
      <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="User" useGeneratedKeys="true">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="name != null">
            name,
          </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
          <if test="name != null">
            #{name,jdbcType=VARCHAR},
          </if>
        </trim>
      </insert>
      <update id="updateByPrimaryKeySelective" parameterType="User">
        update user
        <set>
          <if test="name != null">
            name = #{name,jdbcType=VARCHAR},
          </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
      </update>
      <update id="updateByPrimaryKey" parameterType="User">
        update user
        set name = #{name,jdbcType=VARCHAR}
        where id = #{id,jdbcType=INTEGER}
      </update>
    </mapper>

UserService

package com.yj.service;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import com.yj.dao.UserMapper;
    import com.yj.model.User;
    
    @Service
    public class UserService {
    
    	@Autowired
    	private UserMapper userMapper;
    	
    	public User selectByPrimaryKey(Integer id) {
    		return userMapper.selectByPrimaryKey(id);
    	}
    }

User

package com.yj.model;
    
    public class User {
        private Integer id;
    
        private String name;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name == null ? null : name.trim();
        }
    }

UserController

package com.yj.controller;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.yj.service.UserService;
    
    @RestController
    public class UserController {
    
    	@Autowired
    	private UserService userService;
    
    	@RequestMapping("/getUser")
    	public void getUser(@RequestBody int id) {
    		userService.selectByPrimaryKey(id);
    	}
    }

进行Junit单元测试，成功的查询出了数据，查看Druid监控地址[http://localhost:9001/druid/index.html][http_localhost_9001_druid_index.html]，也看到了监控数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1_size_16_color_FFFFFF_t_70][]

[http_localhost_9001_druid_index.html]: http://localhost:9001/druid/index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1_size_16_color_FFFFFF_t_70]: /images/20220411/da6a57800ace46a99fa1dfefa9534fa8.png