springboot+mybatis+druid+postgreSql/mysql 示例
简介
最近加入一个新的团队,发现团队开发的底层调用时通过jdbctemplate拼凑sql,不能说这个不好,但是对于开发效率来说,肯定没有orm框架使用来的6,jdbctemplate拼凑造轮子的话,大部分时间会花在轮子上,而且,压测的时候,sql的好坏,对业务代码的嵌入太深,综合考虑不划算。
其实,就现在世面主流构建来说,都是mybatis,这个主要是把sql语句独立出来了,做到了对sql语句的可控。
啥也不说,直接看项目结构:
![项目结构](https://img-blog.csdn.net/20180402175327619?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5qdW4yMDEy/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
结构解析
application.yml:总体springboot的配置参数
spring:
profiles:
active: dev
# 服务端口
server:
port: 9090
servlet:
context-path: /mptest
#数据库配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: org.postgresql.Driver
url: jdbc:postgresql://192.168.100.103:5432/postgres
username: postgres
password: postgres
druid:
#初始化连接大小
initial-size: 8
#最小空闲连接数
min-idle: 5
#最大连接数
max-active: 10
#查询超时时间
query-timeout: 6000
#事务查询超时时间
transaction-query-timeout: 6000
#关闭空闲连接超时时间
remove-abandoned-timeout: 1800
filters: stat,config
#mybatis
mybatis:
mapper-locations: classpath:com/examle/mpdemo/dao/*.xml
logback-mptest.xml: 配置日志(开发、测试、生产)
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<!-- 文件输出格式 -->
<property name="PATTERN" value="%-12(%d{yyyy-MM-dd HH:mm:ss.SSS}) |-%-5level [%thread] %c [%L] -| %msg%n" />
<!-- test文件路径 -->
<property name="TEST_FILE_PATH" value="/logs/tianjun" />
<!-- pro文件路径 -->
<property name="PRO_FILE_PATH" value="/logs/tianjun" />
<!-- 开发环境 -->
<springProfile name="dev">
<appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>${PATTERN}</pattern>
</encoder>
</appender>
<logger name="com.cmcc.tian.canal2kafka.common.ClusterCanal" level="debug"/>
<logger name="com.cmcc.tian.canal2kafka.common.AbstractCanalClient" level="debug"/>
<logger name="com.cmcc.tian.canal2kafka.common.CanalKafkaProducer" level="debug"/>
<root level="info">
<appender-ref ref="CONSOLE" />
</root>
</springProfile>
<!-- 测试环境 -->
<springProfile name="test">
<!-- 每天产生一个文件 -->
<appender name="TEST-FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<!-- 文件路径 -->
<file>${TEST_FILE_PATH}</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<!-- 文件名称 -->
<fileNamePattern>${TEST_FILE_PATH}/info.%d{yyyy-MM-dd}.log</fileNamePattern>
<!-- 文件最大保存历史数量 -->
<MaxHistory>100</MaxHistory>
</rollingPolicy>
<layout class="ch.qos.logback.classic.PatternLayout">
<pattern>${PATTERN}</pattern>
</layout>
</appender>
<root level="info">
<appender-ref ref="TEST-FILE" />
</root>
</springProfile>
<!-- 生产环境 -->
<springProfile name="prod">
<appender name="PROD_FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>${PRO_FILE_PATH}</file>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>${PRO_FILE_PATH}/warn.%d{yyyy-MM-dd}.log</fileNamePattern>
<MaxHistory>100</MaxHistory>
</rollingPolicy>
<layout class="ch.qos.logback.classic.PatternLayout">
<pattern>${PATTERN}</pattern>
</layout>
</appender>
<root level="warn">
<appender-ref ref="PROD_FILE" />
</root>
</springProfile>
</configuration>
druid-bean.xml: 开发发测试使用,监控druid,通过界面可以监控数据库连接池
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 配置_Druid和Spring关联监控配置 -->
<bean id="druid-stat-interceptor"
class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor"></bean>
<!-- 方法名正则匹配拦截配置 -->
<bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut"
scope="prototype">
<property name="patterns">
<list>
<value>com.roncoo.education.mapper.*</value>
</list>
</property>
</bean>
<aop:config proxy-target-class="true">
<aop:advisor advice-ref="druid-stat-interceptor"
pointcut-ref="druid-stat-pointcut" />
</aop:config>
</beans>
m-p-demo\src\main\resources\com\example\mpdemo\dao\UserDao.xml:这是Dao的相关mapper类的映射集合,idea中有个问题,在resources资源文件中,必须更Dao类路径取名一致,以便和Dao类编译在一起(不然会报mapper找不到绑定类的坑)。
<?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.example.mpdemo.dao.UserDao" >
<insert id="userInsert" parameterType="com.example.mpdemo.entity.User">
insert into tianjun_test (id,name,age) values (#{user.id},#{user.name},#{user.age})
</insert>
<select id="queryById" resultType="com.example.mpdemo.entity.User">
select * from tianjun_test where id = #{id}
</select>
</mapper>
代码剖析
还是按照entity-dao-servicej-controller的结构
entity层
package com.example.mpdemo.entity;
/**
* @Description: 用户实体
* @Author: tianjun
* @CreateTime: 2018-04-02 13:22
* @Editor: tianjun
* @UpdateTime: 2018-04-02 13:22
* @Company: netposa
*/
public class User {
private Integer id;
private String name;
private Integer age;
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;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
Dao层
package com.example.mpdemo.dao;
import com.example.mpdemo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
/**
* @Description: 用户相关操作
* @Author: tianjun
* @CreateTime: 2018-04-02 13:25
* @Editor: tianjun
* @UpdateTime: 2018-04-02 13:25
* @Company: netposa
*/
@Mapper
public interface UserDao {
Integer userInsert(@Param("user") User user);
User queryById(@Param("id") Integer id);
}
service层
IUserService
package com.example.mpdemo.service;
import com.example.mpdemo.entity.User;
/**
* @Description: 用户业务接口
* @Author: tianjun
* @CreateTime: 2018-04-02 13:38
* @Editor: tianjun
* @UpdateTime: 2018-04-02 13:38
* @Company: netposa
*/
public interface IUserService {
Integer userInsert(User user);
User queryById(Integer id);
}
UserServcie
package com.example.mpdemo.service.impl;
import com.example.mpdemo.dao.UserDao;
import com.example.mpdemo.entity.User;
import com.example.mpdemo.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @Description: 用户业务类
* @Author: tianjun
* @CreateTime: 2018-04-02 13:37
* @Editor: tianjun
* @UpdateTime: 2018-04-02 13:37
* @Company: netposa
*/
@Service
public class UserService implements IUserService {
@Autowired
private UserDao userDao;
@Override
public Integer userInsert(User user) {
return userDao.userInsert(user);
}
@Override
public User queryById(Integer id) {
return userDao.queryById(id);
}
}
controller层
package com.example.mpdemo.controller;
import com.example.mpdemo.entity.User;
import com.example.mpdemo.service.IUserService;
import org.apache.ibatis.annotations.ResultType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @Description: 用户接口类
* @Author: tianjun
* @CreateTime: 2018-04-02 13:42
* @Editor: tianjun
* @UpdateTime: 2018-04-02 13:42
* @Company: netposa
*/
@RestController
@RequestMapping("/tt")
public class UserController {
@Autowired
private IUserService userService;
@PostMapping("/userInsert")
public Integer userInsert(User user){
return userService.userInsert(user);
}
@PostMapping("/queryById")
public User queryById(Integer id){
return userService.queryById(id);
}
}
协助层
DruidConfiguration: 集成druid
package com.example.mpdemo.utils.configuration;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DatabaseDriver;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
@Configuration
public class DruidConfiguration {
@ConditionalOnClass(DruidDataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.alibaba.druid.pool.DruidDataSource", matchIfMissing = true)
static class Druid extends DruidConfiguration {
@Bean
@ConfigurationProperties("spring.datasource.druid")
public DruidDataSource dataSource(DataSourceProperties properties) {
DruidDataSource druidDataSource = (DruidDataSource) properties.initializeDataSourceBuilder().type(DruidDataSource.class).build();
DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
String validationQuery = databaseDriver.getValidationQuery();
if (validationQuery != null) {
druidDataSource.setValidationQuery(validationQuery);
}
return druidDataSource;
}
}
}
MyBatisConfig: 集成mybatis
package com.example.mpdemo.utils.configuration;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;
import javax.sql.DataSource;
import java.util.Properties;
/**
* @Description: springboot集成mybatis的基本入口 1)创建数据源 2)创建SqlSessionFactory
* @Author: tianjun
* @CreateTime: 2018-04-02 14:56
* @Editor: tianjun
* @UpdateTime: 2018-04-02 14:56
* @Company: netposa
*/
@Configuration // 该注解类似于spring配置文件
@MapperScan(basePackages = "com.example.mpdemo.dao") // 扫描Dao文件
@EnableTransactionManagement //支持事务注解
public class MyBatisConfig implements TransactionManagementConfigurer {
@Autowired
DataSource dataSource;//默认配置文件中的数据源
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
fb.setDataSource(dataSource);// 指定数据源(这个必须有,否则报错)
return fb.getObject();
}
@Override
public PlatformTransactionManager annotationDrivenTransactionManager() {
return new DataSourceTransactionManager(dataSource);
}
}
***DruidWebStatFilter:***druid的接界面配置的过滤器
package com.example.mpdemo.utils.filter;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
import com.alibaba.druid.support.http.WebStatFilter;
/**
* @author
*/
@WebFilter(filterName = "druidWebStatFilter", urlPatterns = "/*", initParams = { @WebInitParam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*") })
public class DruidWebStatFilter extends WebStatFilter {
}
***DruidStatViewServlet:***druid界面配置(路径、用户名和密码等)
package com.example.mpdemo.utils.servlet;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;
import com.alibaba.druid.support.http.StatViewServlet;
/**
*
* @author
*/
@WebServlet(urlPatterns = { "/druid/*" }, initParams = { @WebInitParam(name = "loginUsername", value = "admin"), @WebInitParam(name = "loginPassword", value = "admin") })
public class DruidStatViewServlet extends StatViewServlet {
private static final long serialVersionUID = 1L;
}
启动
MPDemoApplication:放在最外层,以便能扫描到所有的带有注解的包
package com.example.mpdemo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.context.annotation.ImportResource;
@ServletComponentScan
@SpringBootApplication
@ImportResource(locations = {"classpath:druid-bean.xml"})
public class MPDemoApplication {
public static void main(String[] args) {
SpringApplication.run(MPDemoApplication.class, args);
}
}