【SpringBoot】整合数据库

SpringBoot如何整合数据库，实现CURD操作

一、整合原生JDBC

1.1 导入相关依赖

<!--SpringBoot JDBC启动器依赖-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<!--MySQL驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

1.2 配置文件连接数据库

spring:
  datasource: #基础JDBC连接
    username: root
    password: 123
    url: jdbc:mysql://localhost:3306/jdbctest?serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver

1.3 CURD测试

在Controller层实现相关业务，数据库操作方法全部封装在jdbcTemplate类中

@Controller
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    //查询
    @RequestMapping("/select")
    @ResponseBody
    public List<Map<String, Object>> selectPerson(){
        String sql = "select * from person";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    //增加
    @RequestMapping("/update")
    @ResponseBody
    public String addPerson(){
        String sql = "insert into person value('盐取', 80, 45787)";
//        jdbcTemplate.execute(sql); 都可以
        jdbcTemplate.update(sql);
        return "add-ok";
    }
    
    //删除
    @RequestMapping("/delete/{name}")
    @ResponseBody
    public String deletePerson(@PathVariable("name") String name){
        //预编译SQL（占位符）
        String sql = "delete from person where name = ?";
        jdbcTemplate.update(sql, name);
        return "delete OK!";
    }
    
    //修改
    @RequestMapping("/add/{name}/{age}/{passowrd}")
    @ResponseBody
    public String addPerson(@PathVariable("name") String name, @PathVariable("age") Integer age, @PathVariable("password") String password){
        String sql = "update `person` value(?, ?, ?)";
        jdbcTemplate.update(sql, name, age, password);
        return "update-ok";
    }

    //使用方式是原生JDBC的方法封装好了，直接使用
}

---

二、整合Mybatis

2.1 导入相关依赖

<!--Mybatis和SpringBoot整合包(非springboot官方)-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>
		
<!--MySQL驱动-->		
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

2.2 配置文件连接数据库

spring:
  datasource:
    username: root
    password: 123
    url: jdbc:mysql://localhost:3306/jdbctest?serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver


#整合Mybatis全局配置文件，直接在此设置
mybatis:
  configuration:
    map-underscore-to-camel-case: true #开启驼峰命名
  mapper-locations: classpath:mybatis/mapper/*.xml #Mapper文件路径

2.3 Mybatis基础操作

• 其他操作和使用Mybtias差异不大，只是Mapper.xml文件，放进resources/mapper目录下

JavaBean
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Person {
    private String name;
    private Integer age;
    private String password;
}

Mapper接口
//此注解标识：此类为Mybatis的Mapper类
@Mapper
@Repository
public interface PersonMapper {

    List<Person>  queryPersons();

    Person queryPersonByName(String name);

    int addPerson(Person person);

    int deletePersonByName(String name);

    int updatePerson(Person person);
}

Mapper.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="com.letere.mapper.PersonMapper"> <!--绑定接口文件-->
    <select id="queryPersons" resultType="com.letere.bean.Person">
        select *
        from `person`
    </select>

    <select id="queryPersonByName" resultType="com.letere.bean.Person">
        select *
        from `person`
        where name like #{name}
    </select>

    <insert id="addPerson">
        insert into `person`
        value (#{name}, #{age}, #{password})
    </insert>

    <delete id="deletePersonByName">
        delet from `person`
        where name = #{name}
    </delete>

    <update id="updatePerson">
        update `person`
        set age = #{age}, password = #{password}
        where name = #{name}
    </update>
</mapper>

Service层实现类
@Service
public class PersonService implements PersonMapper {

    @Autowired
    private PersonMapper mapper;

    @Override
    public List<Person> queryPersons() {
        return mapper.queryPersons();
    }

    @Override
    public Person queryPersonByName(String name) {
        name = '%' + name + '%';
        return mapper.queryPersonByName(name);
    }

    @Override
    public int addPerson(Person person) {
        return mapper.addPerson(person);
    }

    @Override
    public int deletePersonByName(String name) {
        return mapper.deletePersonByName(name);
    }

    @Override
    public int updatePerson(Person person) {
        return mapper.updatePerson(person);
    }
}

Controller层调用Service层
@RestController
public class PersonController {

    @Autowired
    private PersonService server;

    @RequestMapping("/person/queryAll")
    public List<Person> queryAll(Model model){
        List<Person> persons = server.queryPersons();
        return persons;
    }

    @RequestMapping("/person/queryByName/{name}")
    public Person queryByName(@PathVariable("name") String name){
        Person person = server.queryPersonByName(name);
        return person;
    }
}

---

三、整合druid数据连接池

3.1 导入相关依赖

<!--druid数据源-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.1</version>
</dependency>
		
<!--log4j日志记录-->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

3.2 配置文件设置

spring:
  datasource:
    username: root
    password: 123
    url: jdbc:mysql://localhost:3306/jdbctest?serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource #切换druid数据源

    druid:
      #druid数据源特有设置
      #配置监控统计拦截filters：stat(监控统计)、log4j(日志记录)、wall(防止sql注入)
      filters: stat,wall,log4j
      max-pool-prepared-statement-per-connection-size: 20
      use-global-data-source-stat: true
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillus=500

      #基础设置
      #初始化连接数大小
      initial-size: 3
      #最小连接数
      min-idle: 3
      #最大连接数
      max-active: 10
      #连接超时时间
      max-wait: 60000

3.3 配置druid后台监控

• 创建一个druid的配置类

//druid配置类
@Configuration
public class DruidConfig {

    //绑定配置文件的数据源
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }

    //后台监控功能 注册Servlet 类似web.xml
    //因为SpringBoot内置了servlet容器，所有没有web.xml，替代方法ServletRegistrationBean
    @Bean
    public ServletRegistrationBean servletRegistrationBean(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");//后台监控地址

        //后台需要有人登陆，账号密码配置
        HashMap<String, String> initParameters = new HashMap<>();
		
        //-----------------后台监控设置---------------------
        //增加配置（参数名为固定的，不能修改）
        initParameters.put("loginUsername", "莱特雷");
        initParameters.put("loginPassword", "123");

        //允许谁可以访问
        initParameters.put("allow", ""); //值为空，所有人都可以访问

        //禁止谁可以访问
        initParameters.put("用户名", "ip地址"); //里面参数自定义
        //----------------------------------------------------------
		
        bean.setInitParameters(initParameters);//设置初始化参数

        return bean;
    }

    //注册过滤器
    @Bean
    public FilterRegistrationBean filterRegistrationBean(){
        FilterRegistrationBean<Filter> filterBean = new FilterRegistrationBean<>();

        //配置过滤器
        filterBean.setFilter(new WebStatFilter());//druid过滤器

        //过滤请求
        HashMap<String, String> initParameters = new HashMap<>();

        //不过滤的内容
        initParameters.put("exclusions", "*.js, *.css, /druid/*");

        filterBean.setInitParameters(initParameters);

        return filterBean;
    }
}

• 网页输入：”http://localhost:8080/druid"
  • 会进入登录页面，填写配置的账号，密码进入后台