前置工作
- 了解Druid、了解Mybatis、了解SpringMVC集成mybatis、了解 dataSource
- 了解 @Configuration标签
- 了解
dataSource、Mybatis配置
创建dbserver工程
- 引入druid、mysql、mybatis
- 因为使用log4j2、thymeleaf引入了其他jar包
- 引入的包中需要排除相冲突的jar
build.gradle
apply plugin: 'org.springframework.boot'
dependencyManagement {
imports {
mavenBom "org.springframework.cloud:spring-cloud-dependencies:" + springCloudVersion
mavenBom "org.springframework.boot:spring-boot-starter:"+ springBootVersion
}
}
repositories {
mavenCentral()
}
dependencies {
compile ('org.springframework.boot:spring-boot-starter-web:'+springBootVersion)
compile('org.springframework.cloud:spring-cloud-starter-eureka')
compile ('mysql:mysql-connector-java:'+mysqlVersion)
compile ('com.alibaba:druid:'+druidVersion)
compile ('org.mybatis:mybatis-spring:'+mybatisSpringBootVersion)
compile ('org.mybatis:mybatis:'+mybatisVersion)
compile('org.springframework.boot:spring-boot-starter-log4j2')
compile ('org.springframework.boot:spring-boot-starter-thymeleaf')
compile ('net.sourceforge.nekohtml:nekohtml:'+nekoHtmlVersion)
compile('org.apache.logging.log4j:log4j-1.2-api:'+ log4jAPIVersion)
compile('org.springframework.boot:spring-boot-starter-jdbc')
compile('org.springframework.boot:spring-boot-starter-aop')
compile ('com.alibaba:fastjson:'+fastjsonVersion)
testCompile ('org.springframework.boot:spring-boot-starter-test')
testCompile group: 'junit', name: 'junit', version: '4.11'
}
configurations {
all*.exclude module: 'spring-boot-starter-logging'
all*.exclude module: 'logback-classic'
all*.exclude module: 'log4j-over-slf4j'
all*.exclude module: 'snappy-java'
}
jar {
baseName = 'db-server-bootcwenao'
}
创建DataConfig,使用@Configuration进行注解
/**
* @author cwenao
* @version $Id DataConfig.java, v 0.1 2017-01-24 17:43 cwenao Exp $$
*/
@Configuration
@EnableTransactionManagement
public class DataConfig {
@Bean(name="dataSource")
@ConfigurationProperties(prefix = "druid.datasource")
public DataSource dataSource() {
return new DruidDataSource();
}
@Bean
public ResourceBundleMessageSource messageSource() {
ResourceBundleMessageSource messageSource = new ResourceBundleMessageSource();
messageSource.setBasename("config.message");
messageSource.setDefaultEncoding("UTF-8");
return messageSource;
}
}
配置dataSource相关信息,这里使用了druid
application.yml
druid:
datasource:
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/kakme?useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: 111111
#初始化连接数量,最大最小连接数
initialSize: 5
maxActive: 10
minIdle: 3
#获取连接等待超时的时间
maxWait: 600000
#超过时间限制是否回收
removeAbandoned: true
#超过时间限制多长
removeAbandonedTimeout: 180
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 600000
#配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
#用来检测连接是否有效的sql,要求是一个查询语句
validationQuery: SELECT 1 FROM DUAL
#申请连接的时候检测
testWhileIdle: true
#申请连接时执行validationQuery检测连接是否有效,配置为true会降低性能
testOnBorrow: false
#归还连接时执行validationQuery检测连接是否有效,配置为true会降低性能
testOnReturn: false
#打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 50
#属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有:
#监控统计用的filter:stat 日志用的filter:log4j 防御SQL注入的filter:wall
filters: stat
创建MybatisConfig,使用注解@Configuration、@EnableTransactionManagement、@AutoConfigureAfter({DataConfig.class})、@MapperScan(basePackages = {“com.bootcwenao.dbserver.mapper”})
- @AutoConfigureAfter : 表示此项在某项配置完成后在进行configure
- @MapperScan : 扫描mapper文件
MybatisConfig
/**
* @author cwenao
* @version $Id MybatisConfig.java, v 0.1 2017-01-24 17:38 cwenao Exp $$
*/
@Configuration
@EnableTransactionManagement
@AutoConfigureAfter({DataConfig.class})
@MapperScan(basePackages = {"com.bootcwenao.dbserver.mapper"})
public class MybatisConfig implements EnvironmentAware {
@Autowired
DataSource dataSource;
private RelaxedPropertyResolver propertyResolver;
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactoryBean() {
try {
ResourcePatternResolver resourcePatternResolver;
resourcePatternResolver = new PathMatchingResourcePatternResolver();
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setTypeAliasesPackage(propertyResolver.getProperty("typeAliasesPackage"));
bean.setMapperLocations(resourcePatternResolver.getResources(propertyResolver.getProperty("mapper-locations")));
bean.setConfigLocation(new DefaultResourceLoader().getResource(propertyResolver.getProperty("configLocation")));
return bean.getObject();
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(dataSource);
}
/**
* Set the {@code Environment} that this object runs in.
*
* @param environment
*/
@Override
public void setEnvironment(Environment environment) {
this.propertyResolver = new RelaxedPropertyResolver(environment,"mybatis.");
}
}
配置mybatis相关注入信息
application.yml
mybatis:
typeAliasesPackage: com.bootcwenao.dbserver.pojo,com.bootcwenao.dbserver.vo
mapper-locations: classpath:/com/bootcwenao/dbserver/mapper/*Mapper.xml
configLocation: classpath:/config/mybatis-spring.xml
创建mybatis-spring.xml 用于额外mybatis的信息配置,比如分页
mybatis-spring.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>
<properties>
<property name="dialect" value="mysql" />
</properties>
<settings>
<!-- 这个配置使全局的映射器启用或禁用缓存。系统默认值是true,设置只是为了展示出来 -->
<setting name="cacheEnabled" value="true" />
<!-- 全局启用或禁用延迟加载。当禁用时,所有关联对象都会即时加载。 系统默认值是true,设置只是为了展示出来 -->
<setting name="lazyLoadingEnabled" value="true" />
<!-- 允许或不允许多种结果集从一个单独的语句中返回(需要适合的驱动)。 系统默认值是true,设置只是为了展示出来 -->
<setting name="multipleResultSetsEnabled" value="true" />
<!--使用列标签代替列名。不同的驱动在这方便表现不同。参考驱动文档或充分测试两种方法来决定所使用的驱动。 系统默认值是true,设置只是为了展示出来 -->
<setting name="useColumnLabel" value="true" />
<!--允许 JDBC 支持生成的键。需要适合的驱动。如果设置为 true 则这个设置强制生成的键被使用,尽管一些驱动拒绝兼容但仍然有效(比如
Derby)。 系统默认值是false,设置只是为了展示出来 -->
<setting name="useGeneratedKeys" value="false" />
<!--配置默认的执行器。SIMPLE 执行器没有什么特别之处。REUSE 执行器重用预处理语句。BATCH 执行器重用语句和批量更新 系统默认值是SIMPLE,设置只是为了展示出来 -->
<setting name="defaultExecutorType" value="SIMPLE" />
<!--设置超时时间,它决定驱动等待一个数据库响应的时间。 系统默认值是null,设置只是为了展示出来 -->
<setting name="defaultStatementTimeout" value="25000" />
</settings>
<!--<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql" />
<property name="offsetAsPageNum" value="true" />
<property name="rowBoundsWithCount" value="true" />
<property name="pageSizeZero" value="true" />
<property name="reasonable" value="true" />
</plugin>
</plugins>-->
</configuration>
引入thymeleaf
spring:
thymeleaf:
cache: false
mode: LEGACYHTML5
prefix: classpath:/web/
suffix: .html
content-type: text/html
表以及实体类等
创建表 account_info
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for account_info
-- ----------------------------
DROP TABLE IF EXISTS `account_info`;
CREATE TABLE `account_info` (
`id` varchar(64) NOT NULL,
`account_name` varchar(64) DEFAULT NULL COMMENT '用户名',
`nick_name` varchar(64) DEFAULT NULL COMMENT '昵称',
`mail` varchar(64) DEFAULT NULL COMMENT '邮箱',
`m_tel` varchar(64) DEFAULT NULL COMMENT '移动电话',
`land_tel` varchar(64) DEFAULT NULL COMMENT '固定电话',
`is_vip` int(1) DEFAULT NULL,
`password` varchar(128) DEFAULT NULL COMMENT '密码',
`salt` varchar(32) DEFAULT NULL COMMENT '盐值',
`head_image` varchar(258) DEFAULT NULL COMMENT '头像',
`back_image` varchar(258) DEFAULT NULL COMMENT '背景图',
`account_type` int(1) DEFAULT NULL COMMENT '',
`source` int(1) DEFAULT NULL COMMENT ,
`audit_status` int(1) DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` int(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
根据account_info表创建生成 entity、mapper、*.Mapper.xml、server 此处略
- entity、mapper放置在java下的package
- *.Mapper.xml放置在resources下同mapper的目录结构下
创建 Controller、html页面
UserInfoController
/**
* @author cwenao
* @version $Id UserInfoController.java, v 0.1 2017-01-25 18:35 cwenao Exp $$
*/
@Controller
public class UserInfoController {
@Autowired
AccountInfoServer accountInfoServerImpl;
@RequestMapping("/accountInfo")
public String accountInfo(String name, ModelMap modelMap) {
List<AccountInfo> accountInfoList = accountInfoServerImpl.selectByName(name);
modelMap.addAttribute("accountList", accountInfoList);
return "userinfo/accountInfo";
}
}
创建 accountInfo.html,放置在userinfo文件夹下
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body >
<table>
<tr th:each="accountInfo:${accountList}">
<td th:text="${accountInfo.accountName}">aabbcc</td>
<td th:text="${accountInfo.salt}">123dds</td>
</tr>
</table>
</body>
</html>
apigateway中加入dbserver访问
bootstrap.yml
zuul:
routes:
dbserver:
path: /dbserver/**
serviceId: DBSERVER
测试访问
- 在account_info中加入一条数据
- 启动discovery、configserver、apigateway、dbserver
访问地址:http://localhost:10002/dbserver/accountInfo?name=cwenao
- http://localhost:10002 为网关地址
- dbserver为网关zuul 配置path
- accountInfo?name=cwenao 为controller访问地址
代码
代码请移步 Github参考地址
如有疑问请加公众号(K171),如果觉得对您有帮助请 github start