【SpringBoot】最新版2019Spring Boot之连接数据库——JDBC,MyBATIS,分页，MyBatis­Plus（速成SpringBoot）——学习笔记版解析【5】

SpringBoot

---

十二、SpringBoot数据访问

1.JDBC

1.1 创建一个工程，选择以下模板：Web、MySQL、JDBC

即新建的时候除了要勾选web，另外加上与数据库相关的

1.2 配置数据库连接信息

添加数据源依赖，例如dbcp

<!--数据源dbcp-->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>

创建数据库：

CREATE DATABASE springboottest CHARSET utf8;
USE springboottest;

CREATE TABLE t_user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(200) NOT NULL UNIQUE,
    PASSWORD VARCHAR(200)
)ENGINE=INNODB CHARSET utf8;

INSERT INTO t_user(username,PASSWORD) VALUES('admin','123');
INSERT INTO t_user(username,PASSWORD) VALUES('tom','123');

在application.properties配置信息

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/springboottest?useUnicode=true&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.type=org.apache.commons.dbcp.BasicDataSource

spring.datasource.initialSize=10
spring.datasource.maxActive=100
spring.datasource.minIdle=5
spring.datasource.maxWait=50000

1.3 测试

package cn.cz;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.sql.DataSource;
import java.sql.SQLException;

@RunWith(SpringRunner.class)
@SpringBootTest
public class Springboot06JdbctApplicationTests {
	@Qualifier("dataSource")
	@Autowired
	private DataSource dataSource;
	@Test
	public void contextLoads() throws SQLException {
		System.out.println("============================");
		System.out.println(dataSource.getClass());
		System.out.println(dataSource.getConnection());
		System.out.println("============================");
	}

}

1.4 配置连接池参数

仅仅配置properties无法生效，需要配置@Configuration

#springboot默认不能不支持这样配置参数
spring.datasource.initialSize=10
spring.datasource.maxActive=100
spring.datasource.minIdle=5
spring.datasource.maxWait=50000 

配置方式：

package cn.cz;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * 功能
 *
 * @author czzhao2
 * @created 2019年02月18日 15:30:10
 * @lastModified
 * @history
 */
@Configuration
public class DataSourceConfig {
    //从配置文件中读取spring.datasource属性，并注入给数据源的属性
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource(){
      return new BasicDataSource();
    }
}

1.5 操作数据库

@Controller
@RequestMapping("/user")
public class UserController {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @RequestMapping("/findAll")
    @ResponseBody
    public List<Map<String, Object>> findAll(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from t_user");
        return list;
    }
}

2.MyBatis

目录结构：

2.1创建一个工程，选择以下模块：Web、MySQL、MyBatis

加数据源依赖

<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.10</version>
		</dependency>

2.2 配置application.yml

#配置DataSource
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url:  jdbc:mysql://localhost:3306/springboottest?useUnicode=true&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    username: root
    password: 123456
    initialSize: 5
    maxActive: 100
    minIdle: 3
    maxWait: 50000
#配置Mybatis
mybatis:
  type-aliases-package: cn.cz.pojo
  mapper-locations: classpath:mapper/*.xml

2.3 编写Mapper、Service、Controller

UserMapper.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.cz.mapper.UserMapper">
    <sql id="userColumn">
        id,
        username,
        password
    </sql>
    <select id="selectAll" resultType="User">
      SELECT <include refid="userColumn"/>
      FROM t_user
    </select>
    <select id="selectById" resultType="User">
        SELECT  <include refid="userColumn"/>
        FROM t_user
        WHERE  id=#{id}
    </select>
    
    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        INSERT into t_user
        (username,password)
        VALUES (#{username},#{password})
    </insert>
</mapper>

MyBatisConfig.java

@Configuration
@MapperScan("cn.cz.mapper")
public class MyBatisConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource(){
        return new DruidDataSource();
    }
}

3.PageHelper

添加依赖：

<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.2.10</version>
		</dependency>

配置属性

#配置pagehelper
pagehelper:
  helper-dialect: mysql

4.MyBatis­Plus

官方指南：https://mp.baomidou.com/guide/install.html

添加依赖：

<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.0.7.1</version>
		</dependency>
<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.12</version>
		</dependency>

配置属性：application.yml

#配置DataSource
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url:  jdbc:mysql://localhost:3306/springboottest?useUnicode=true&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    username: root
    password: 123456
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: cn.cz.pojo
  global-config:
    db-config:
      id-type: auto #主键类型
      field-strategy: not_null #字段策略
      db-column-underline: true #驼峰下划线转换
      #全局表前缀
      table-prefix: t_
      refresh-mapper: true #刷新mapper.xml
      #逻辑删除配置（下面3个配置）
      logic-delete-value: 1
      logic-not-delete-value: 0
      sql-injector: com.baomidou.mybatisplus.mapper.LogicSqlInjector
      #配置返回数据库(column下划线命名&&返回java实体是驼峰命名)，自动匹配无需as（没开启这个，SQL需要写as： select user_id as userId）
      map-underscore-to-camel-case: true
      cache-enabled: false

配置config：

package cn.cz.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan("cn.cz.mapper")
public class MybatisPlusConfig {
    /**
     * 分页插件，自动识别数据库类型
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }

    /**
     * 数据源
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource(){
        return new DruidDataSource();
    }
}

继承BaseMapper

public interface UserMapper extends BaseMapper<User>{
}

测试

@Autowired
private UserMapper userMapper;

@Test
public void contextLoads() {
}

@Test
public void add(){
   User user=new User();
   user.setPassword("123");
   user.setUsername("xxx");
   userMapper.insert(user);
   System.out.println("==============>"+user);
}

@Test
public void deleteByid(){
   userMapper.deleteById(4);
}
@Test
public void modifyById(){
   User user=new User();
   user.setId(2);
   user.setPassword("666");
   userMapper.updateById(user);
}

@Test
public void findById(){
   User user=userMapper.selectById(2);
   System.out.println(user);
}
//条件查询
	@Test
	public void findByCondition(){
		//定义条件构造器，用来封装查询条件\
		QueryWrapper<User> queryWrapper=new QueryWrapper<>();
		//queryWrapper.eq("username","tom");
		queryWrapper.like("username","a");
		//queryWrapper.ge("id",4);

		queryWrapper.or().ge("id",4);
		List<User> users=userMapper.selectList(queryWrapper);
		System.out.println("========>"+users);

	}
	@Test
	public void findByPage(){
		IPage<User> iPage = userMapper.selectPage(
				new Page<>(2, 3),
				new QueryWrapper<User>().gt("id", 1)
		);
		System.out.println("------------------>"+iPage.toString());
	}

5.lombok

添加依赖

<dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <version>1.18.4</version>
   <scope>provided</scope>
</dependency>

使用注解配置

package cn.cz.pojo;

import lombok.*;

import java.io.Serializable;

/**
 * lombok使用
 * 常用注解：@Data 相当于四个的组合
 * @author czzhao2
 * @created 2019年02月19日 09:32:55
 * @lastModified
 * @history
 */
//@Getter
//@Setter
//@ToString
//@EqualsAndHashCode
@Data
public class User implements Serializable {
    private Integer id;
    private String username;
    private String password;
}

在IDEA安装lombok插件

因为源代码中并没有getter/setter等的定义，IDEA默认无法识别，会报错，需要安装lombok插件 。

PS：需要完整版笔记，请评论留下邮箱，我看到后回发给你PDF完整版记录~~~

点击查看分版笔记目录：

【SpringBoot】最新版2019Spring Boot快速入门（速成SpringBoot）——学习笔记版解析【1】
【SpringBoot】最新版2019Spring Boot配置解析,源码解析（速成SpringBoot）——学习笔记版【2】
【SpringBoot】最新版2019Spring Boot之WEB开发——Thymeleaf模板引擎速成（速成SpringBoot）——学习笔记版解析【3】
【SpringBoot】最新版2019Spring Boot之MVC功能，异常处理，servlet容器（速成SpringBoot）——学习笔记版解析【4】
【SpringBoot】最新版2019Spring Boot之连接数据库——JDBC,MyBATIS,分页，MyBatis­Plus（速成SpringBoot）——学习笔记版解析【5】