集成JDBC

本文主要是介绍集成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;
    }
}

这篇关于集成JDBC的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！