JDBC

pom文件加入相关依赖

<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>

配置文件配置如下

spring:
  datasource:
    username: root
    password: password
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver

springboot框架下使用JDBC十分方方便

@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    //查询数据库所有信息
    //没有实体类，可以用Map
    @GetMapping("/userList")
    public List<Map<String,Object>> userList(){
        String sql = "select * from user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    @GetMapping("/adduser")
    public String addUser(){
        String  sql = "insert into user(username,birthday) values ('waston','1998-02-13')";
        jdbcTemplate.update(sql);
        return "add-ok";
    }

    @GetMapping("/updateuser/{id}")
    public String updateUser(@PathVariable("id") int id){
        String sql = "update user set username=?,address=? where id=" + id;

        Object[] objects = new Object[2];
        objects[0] = "小明";
        objects[1] = "xa";
        jdbcTemplate.update(sql, objects);
        return "update-ok";
    }

    @GetMapping("/deleteuser/{id}")
    public String deleteUser(@PathVariable("id") int id){
        String sql = "delete from user where id=?";
        jdbcTemplate.update(sql,id);
        return "update-ok";
    }
}

数据源

SpringBoot默认数据源是HikariDataSource

@Test
void checkDataSource(){
    //查看默认数据源 ： com.zaxxer.hikari.HikariDataSource
    System.out.println(dataSource.getClass());
}

我们可以自己配合数据源，配置文件中添加type属性，如下：

spring:
  datasource:
    username: root
    password: password
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

Druid

使用druid需要在配置文件中加入

spring:
  datasource:
    #SpringBoot默认不注入这些属性，需要自己写
    #durid数据源专有配置
    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注入
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobaDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

之后需要自定义配置类

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
}

这样，文件中的哪些属性才能生效

配置类中加入后台监控

  • SpringBoot内置了servlet容器，所以没有web.xml，替代方法：注入ServletRegistrationBean
@Bean
public ServletRegistrationBean statViewServlet(){
    //配置后台服务，以及后台访问路径
    ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

    //配置后台账号、密码
    HashMap<String,String> initParameters = new HashMap<>();
    initParameters.put("loginUsername","admin");
    initParameters.put("loginPassword","123456");

    //允许谁能访问
    initParameters.put("allow","");//所有人


    bean.setInitParameters(initParameters);//设置初始化参数
    return bean;
}

项目启动后，即可访问Druid的后台

Mybatis

  • pom文件中加入
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
  • 项目包下新建mapper包，并定义有如下注解的类
@Mapper
@Repository
public interface UserMapper {

    List<User> queryUserList();

    User queryUserById(int id);

    int addUser(User user);

    int updateUser(User user);

    int deleteUser(int id);
}
  • resource路径下建立对应的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="cn.waston.springdata.demo.mapper.UserMapper">
    <select id="queryUserList" resultType="User">
        select * from user
    </select>

    <select id="queryUserById" resultType="User">
        select * from user where id = #{id};
    </select>

    <insert id="addUser" parameterType="User">
        insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
    </insert>

    <update id="updateUser" parameterType="User">
        update user set username=#{username},birthday=#{birthday},address=#{adderess} where id = #{id}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id}
    </delete>
</mapper>
  • properties配置文件中配置(映射类路径、xml文件路径)
mybatis.type-aliases-package=cn.waston.springdata.demo.pojo
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
  • Controller层调用
@RestController
public class UserController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("/queryuserlist")
    public List<User> queryUserList(){
        List<User> users = userMapper.queryUserList();
        return users;
    }
}