1、application.properties配置文件:
#数据库设置
spring.datasource.mytest.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.mytest.driverClassName=com.mysql.jdbc.Driver
spring.datasource.mytest.url=jdbc:mysql://127.0.0.1:3306/mytest
spring.datasource.mytest.username=root
spring.datasource.mytest.password=176658
#--------------------------
spring.datasource.mytest1.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.mytest1.driverClassName=com.mysql.jdbc.Driver
spring.datasource.mytest1.url=jdbc:mysql://127.0.0.1:3306/mytest1
spring.datasource.mytest1.username=root
spring.datasource.mytest1.password=176658
# 下面为连接池的补充设置,应用到上面所有数据源中
# 初始化大小,最小,最大
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
# 打开PSCache,并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true
2、工程包结构。
3、controller包
//HelloController
package cn.controller;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import cn.entity.City;
import cn.mapper1.CityMapper;
@RestController
@RequestMapping("/demo")
public class HelloController {
static Logger log = LogManager.getLogger(HelloController.class);
@Resource
private CityMapper cityMapper;
@RequestMapping("/test")
String test1(){
return "hello,test1()";
}
@RequestMapping("/findCity2")
City findCity2(){
//log.info("太牛了");
Map map=new HashMap();
map.put("id", 2);
return cityMapper.findCityById(map);
}
@RequestMapping("/addCity")
void addCity(String id ,String name,String province){
System.out.println("id:"+id+","+"name:"+name+","+"province:"+province);
City city =new City();
city.setId(id);
city.setName(name);
city.setProvince(province);
cityMapper.add(city);
}
@RequestMapping("/findState")
List<Map<String,Object>> find(){
return cityMapper.findState();
}
}
//UserController
package cn.controller;
import javax.annotation.Resource;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import cn.entity.User;
import cn.mapper2.UserMapper;
@RestController
@RequestMapping("/demoUser")
public class UserController {
static Logger log = LogManager.getLogger(UserController.class);
@Resource
private UserMapper userMapper;
@RequestMapping("/test")
String test1(){
return "hello,test1()";
}
@RequestMapping("/findUser")
User findUser(@RequestParam String name){
//log.info("");
return userMapper.findCityByName(name);
}
}
4、entity包,因为案例的数据比较简单,大家可以通过这两个实体类,创建对应的数据库变这里就不添加了。
//City
package cn.entity;
import java.io.Serializable;
public class City implements Serializable{
private static final long serialVersionUID = 1L;
private String id;
private String name;
private String province;
public City(){
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
@Override
public String toString() {
return "City [id=" + id + ", name=" + name + ", province=" + province + "]";
}
}
//User
package cn.entity;
public class User {
private String name;
private Integer age;
public User() {
}
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;
}
@Override
public String toString() {
return "User [name=" + name + ", age=" + age + "]";
}
}
5、mapper1.
package cn.mapper1;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import cn.entity.City;
public interface CityMapper {
City findCityById(Map map);
void add(City city);
List<Map<String,Object>> findState();
}
//mapper2
package cn.mapper2;
import org.apache.ibatis.annotations.Param;
import cn.entity.User;
public interface UserMapper {
User findCityByName(@Param("name") String name);
void add(User user);
}
6、application
package cn;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
//DruidDataSource1Configuration对于mytest指定CityMapper.xml映射文件
package cn;
import javax.sql.DataSource;
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.pool.DruidDataSource;
@Configuration
@MapperScan(basePackages = { "cn.Mapper1" }, sqlSessionTemplateRef = "mytestSqlSessionTemplate")
public class DruidDataSource1Configuration {
@Bean(name = "mytestDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.mytest")
public DataSource druidDataSource() {
DataSource druidDataSource = new DruidDataSource();
return druidDataSource;
}
@Bean(name = "mytestSqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("mytestDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("file:mybatis/mapper/CityMapper.xml"));
return bean.getObject();
}
@Bean(name = "mytestTransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("mytestDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "mytestSqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("mytestSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
//DruidDataSource2Configuration对于mytest1库指定UserMapper.xml映射文件
package cn;
import javax.sql.DataSource;
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.pool.DruidDataSource;
@Configuration
@MapperScan(basePackages={"cn.Mapper2"},sqlSessionTemplateRef = "mytest1SqlSessionTemplate")
public class DruidDataSource2Configuration {
@Bean("mytest1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.mytest1")
public DataSource druidDataSource(){
DataSource druidDataSource = new DruidDataSource();
return druidDataSource;
}
@Bean(name = "mytest1SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("mytest1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("file:mybatis/mapper/UserMapper.xml"));
return bean.getObject();
}
@Bean(name = "mytest1TransactionManager")
public DataSourceTransactionManager testTransactionManager(@Qualifier("mytest1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "mytest1SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("mytest1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
//DruidStatFilter和DruidStatViewServlet只是用于SQL语句的监测在这里链接多源数据库可略。
package cn;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
import com.alibaba.druid.support.http.WebStatFilter;
@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
initParams={
@WebInitParam(name="exclusions",value="\*.js,\*.gif,\*.jpg,\*.bmp,\*.png,\*.css,\*.ico,/druid/*")// 忽略资源
}
)
public class DruidStatFilter extends WebStatFilter{
}
//DruidStatViewServlet
package cn;
import com.alibaba.druid.support.http.StatViewServlet;
import javax.servlet.annotation.WebServlet;
import javax.servlet.annotation.WebInitParam;
@WebServlet(urlPatterns = "/druid/*",
initParams={
@WebInitParam(name="allow",value="192.168.16.110,127.0.0.1"),// IP白名单 (没有配置或者为空,则允许所有访问)
@WebInitParam(name="deny",value="192.168.16.111"),// IP黑名单 (存在共同时,deny优先于allow)
@WebInitParam(name="loginUsername",value="admin"),// 用户名
@WebInitParam(name="loginPassword",value="123"),// 密码
@WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能
})
public class DruidStatViewServlet extends StatViewServlet {
private static final long serialVersionUID = 1L;
}
//mybatise/mapper映射文件
//CityMapper.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="cn.mapper1.CityMapper" >
<select id="findCityById" parameterType="map" resultType="cn.entity.City">
select * from city where id=#{id}
</select>
<select id="findState" resultType="map">
select * from atalarm_send_view
</select>
<insert id="add" parameterType="cn.entity.City" >
insert into city (id,name,province)
values(#{id},#{name},#{province})
</insert>
</mapper>
//UserMapper.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="cn.mapper2.UserMapper" >
<select id="findCityByName" parameterType="String" resultType="cn.entity.User">
select * from user where name = #{name}
</select>
<insert id="add" parameterType="cn.entity.User" >
insert into User (name,age)
values(#{name},#{age})
</insert>
</mapper>
7、案例访问样例:
访问mytest中的数据。
访问mytest1中的数据。