集成JDBC
xxxTemplate :springboot已经配置好的模板bean,拿来即用
(1)导入start
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
(2)配置数据库
spring:
datasource:
username: root
password: 159263487qwe
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
注意username不是name
(3)测试增删改查
@RestController
public class JDBCController {
@Autowired
JdbcTemplate jdbcTemplate = new JdbcTemplate();
// 查
@GetMapping("/list")
public List<Map<String,Object>> list(){
String sql = "select * from test.user";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
return maps;
}
// 增
@GetMapping("/insert")
public String add(){
String sql = "insert into test.user (name,password,address,phone) values(?,?,?,?)";
Object[] objs = new Object[]{"calsb","sb123","西安","12345"};
jdbcTemplate.update(sql,objs);
return "add_OK!";
}
// 删
@GetMapping("/delete/{id}")
public String delete(@PathVariable("id")int id){
String sql = "delete from test.user where id = ?";
jdbcTemplate.update(sql,id);
return "delete_OK!";
}
// 改
@GetMapping("/update/{id}")
public String update(@PathVariable("id")int id){
String sql = "update user set name = ?, phone = ? where id ="+id;
Object[] objs = new Object[]{"sssbcal","1212121"};
jdbcTemplate.update(sql,objs);
return "update_OK!";
}
}
Druid
(1)pom中引入start(druid和log4j)
(2)spring.datasource:type: com.alibaba.druid.pool.DruidDataSource
(3)Druid特有的配置(application.properties)
#Spring Boot 默认是不注入这些属性值的,需要自己绑定
#druid 数据源专有配置
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
#配置监控统计拦截的filters,stat:监控统计、log4j:日志记录、wall:防御sql注入
#如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority
#则导入 log4j 依赖即可,Maven 地址:https://mvnrepository.com/artifact/log4j/log4j
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
使配置生效:
因为SpringBoot内置了Servlet容器,所以没有web.xml,替代方法:ServletRegistrationBean
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource (){
return new DruidDataSource();
}
// 后台监控
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
//设置登录名与密码
HashMap<String,String> initParameters = new HashMap<>();
initParameters.put("loginUsername","root");//key是固定的
initParameters.put("loginPassword","12345");
//设置允许谁访问后台
initParameters.put("allow","");//所有人
// 禁止谁访问
// initParameters.put("cal","192.168.11.1123");
bean.setInitParameters(initParameters);//设置初始化参数
return bean;
}
// 拦截器
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
HashMap<String ,String> initParameters = new HashMap<>();
// //哪些东西不进行统计
initParameters.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParameters);
return bean;
}
}