- 添加 pom.xml坐标
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- 整合 mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<!-- 整合数据源,连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!-- 由于druid 依赖 log4j,故需要导入 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
2.1 添加 application.yaml 配置
spring:
datasource:
db1: # 数据源1
url: jdbc:mysql://127.0.0.1:3306/zmkm?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
username: root
password: ""
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource # 配置阿里巴巴的 druid 数据源
initialSize: 5 # 初始化5个链接
minIdle: 5
maxWait: 6000
maxActive: 30
name: db1
timeBetweenEvictionRunsMillis: 6000 # 时间应少于 数据库配置文件 wait_timeout选项
minEvictableIdleTimeMillis: 30000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true # 空闲时检测
testOnBorrow: false # 获取连接时检测
testOnReturn: false # 连接放回连接池时检测
poolPreparedStatements: true
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
# 配置 mybatis 实体类所在包
mybatis:
type-aliases-package: cn.waimaolang.demo.pojo
db2: # 数据源2配置，同数据源1的配置
2.2 . 添加 log4j.properties 配置
log4j.rootLogger=ERROR, stdout
# 应用于控制台
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
# 应用于文件
log4j.appender.FILE = org.apache.log4j.FileAppender
log4j.appender.FILE.File = file.log
log4j.appender.FILE.Append = false
log4j.appender.FILE.layout = org.apache.log4j.PatternLayout
log4j.appender.FILE.layout.ConversionPattern = [framework] % d - % c -%- 4r [ % t] %- 5p % c % x - % m % n
3 . 多数据源之 db1 的配置，可创建另外一个 configuration类配置数据源2
package cn.waimaolang.demo.configura;
import com.alibaba.druid.pool.DruidDataSource;
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.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
// 扫描的 mapper包 接口类
@MapperScan(basePackages = "cn.waimaolang.demo.mapper", sqlSessionTemplateRef = "db1SqlSessionTemplate")
public class DataSource1Config {
/**
* @Primary 注解声明为默认数据源
* 使用 druid 数据源
*/
@Bean(name = "db1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db1")
@Primary
public DataSource testDataSource() {
return new DruidDataSource();
}
/**
* 创建 SqlSessionFactory
*/
@Bean(name = "db1SqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/db1/*.xml"));
return bean.getObject();
}
/**
* 配置事务管理
*/
@Bean(name = "db1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* 配置对外操作的 bean
* @param sqlSessionFactory
* @return
* @throws Exception
*/
@Bean(name = "db1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
// 配置 druid 监控 的web面板
@Bean
public ServletRegistrationBean staViewServlet(){
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
Map<String,String> initParams = new HashMap<>();
//设置servlet初始化参数
initParams.put("loginUsername","admin");// druid登陆名
initParams.put("loginPassword","123456");// druid密码
initParams.put("allow","");//默认就是允许所有访问
initParams.put("deny","192.168.15.21");//拒绝相对应的id访问
bean.setInitParameters(initParams);
//加载到容器中
return bean;
}
//2.配置一个web监控的filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
bean.setFilter(new WebStatFilter());
Map<String,String> initParams = new HashMap<>();
//设置filter初始化参数、
initParams.put("exclusions","*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");//排除静态资源和请求
bean.setInitParameters(initParams);
//拦截所有请求
bean.setUrlPatterns(Arrays.asList("/*"));
//加载到容器中
return bean;
}
}
-
创建测试数据库
image.png
创建语句
CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`student_sno` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '',
`student_sname` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`student_ssex` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`student_sage` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`student_saddress` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
5.创建 数据表 student 对应的实体类 pojo
package cn.waimaolang.demo.pojo;
public class Students {
private int id;
private String studentSno;
private String studentSname;
private String studentSsex;
private String studentSage;
private String studentSaddress;
public Students() {
super();
}
public Students(String studentSno, String studentSname, String studentSsex, String studentSage,
String studentSaddress) {
super();
this.studentSno = studentSno;
this.studentSname = studentSname;
this.studentSsex = studentSsex;
this.studentSage = studentSage;
this.studentSaddress = studentSaddress;
}
public Students(String studentSname, String studentSsex, String studentSage, String studentSaddress) {
super();
this.studentSname = studentSname;
this.studentSsex = studentSsex;
this.studentSage = studentSage;
this.studentSaddress = studentSaddress;
}
public String getStudentSno() {
return studentSno;
}
public void setId(int id1){
this.id=id1;
}
public int getId(){
return id;
}
public void setStudentSno(String studentSno) {
this.studentSno = studentSno;
}
public String getStudentSname() {
return studentSname;
}
public void setStudentSname(String studentSname) {
this.studentSname = studentSname;
}
public String getStudentSsex() {
return studentSsex;
}
public void setStudentSsex(String studentSsex) {
this.studentSsex = studentSsex;
}
public String getStudentSage() {
return studentSage;
}
public void setStudentSage(String studentSage) {
this.studentSage = studentSage;
}
public String getStudentSaddress() {
return studentSaddress;
}
public void setStudentSaddress(String studentSaddress) {
this.studentSaddress = studentSaddress;
}
@Override
public String toString() {
return "Students [studentSno=" + studentSno + ", studentSname=" + studentSname + ", studentSsex=" + studentSsex
+ ", studentSage=" + studentSage + ", studentSaddress=" + studentSaddress + "]";
}
}
- 创建表对应的 mapper 操作接口类
package cn.waimaolang.demo.mapper;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;
import cn.waimaolang.demo.pojo.Students;
import java.util.List;
@Repository
@Qualifier("db1SqlSessionTemplate") // 声明使用数据源1的数据库
public interface StudentMapper
{
String table = "student";
@Select("SELECT * FROM "+table)
@Results({
@Result(property = "studentSno", column = "student_sno"),
@Result(property = "studentSname", column = "student_sname"),
@Result(property = "studentSsex", column = "student_ssex"),
@Result(property = "studentSage", column = "student_sage"),
@Result(property = "studentSaddress", column = "student_saddress")
})
List<Students> getAll();
@Select("SELECT * FROM "+table+" where student_sno=#{id} ")
@Results({
@Result(property = "studentSno", column = "student_sno"),
@Result(property = "studentSname", column = "student_sname"),
@Result(property = "studentSsex", column = "student_ssex"),
@Result(property = "studentSage", column = "student_sage"),
@Result(property = "studentSaddress", column = "student_saddress")
})
Students findOneById(Integer id);
@Insert("insert into "+table+"(student_sno,student_sname,student_ssex,student_sage,student_saddress) values(#{studentSno},#{studentSname},#{studentSsex},#{studentSage},#{studentSaddress})")
@Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id") // 将自增主键返给 实体类的 id 属性
int storeStudents(Students students);
@Delete("delete from "+table+" where student_sno=#{id} ")
int deleteStudentsById(Integer id);
}
- 使用， 向数据库 插入一条数据 并得到主键id
@Autowired
StudentMapper studentMapper;
@PostMapping("/addName")
public int validate1(){
Students students= new Students("0001","wangjun","男","500","广东 东莞");
int r= studentMapper.storeStudents(students);
return students.getId(); // 返回得到 数据表的自增主键id
}
结果如下图
image.png
启动 项目后在 浏览器 输入 localhost:8080/druid/ 即可打开 druid 监控数据库登录界面
用户名 在 配置的 Configuration 中
Loginusername : admin
Loginpassword : 123456
网友评论