2021-1-9 springboot集成mysql（笔记）

springboot集成mysql

• 创建项目引入依赖

 <!--MySQL连接java的驱动程序-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--支持通过JDBC连接数据库-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

• 添加配置参数

server:
  port: 8080

spring:
  datasource:
    #MySQL连接信息
    url: jdbc:mysql://127.0.0.1:3306/lcm_army_dev?serverTimezone=UTC&useSSL=false
    # 账号
    username: root
    # 密码
    password: x6t5Sd
    # 驱动
    driver-class-name: com.mysql.cj.jdbc.Driver

• 直接在test文件里进行测试

@Value("${spring.datasource.url}")
	private String url;

	@Value("${spring.datasource.username}")
	private String username;

	@Value("${spring.datasource.password}")
	private String password;

	@Value("${spring.datasource.driver-class-name}")
	private String driver;


	/**
	 * 测试数据库是否能连接成功
	 * @throws SQLException
	 */
	@Test
	void contextLoads() throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			System.out.println("加载成功");
		} catch (Exception ex) {
			System.out.println("加载失败");
			// handle the error
		}

		try {
			conn = DriverManager.getConnection(
					url,
					username,
					password);

			System.out.println("连接成功");
			conn.close();
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			System.out.println("连接失败");
		}
	}

然后运行test显示成功！

添加连接池依赖

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

添加阿里druid(阿里)连接池

server:
  port: 8080

spring:
  datasource:
    #MySQL连接信息
    url: jdbc:mysql://127.0.0.1:3306/lcm_army_dev?serverTimezone=UTC&useSSL=false
    # 账号
    username: root
    # 密码
    password: x6t5Sd
    # 驱动
    driver-class-name: com.mysql.cj.jdbc.Driver

    type: com.alibaba.druid.pool.DruidDataSource
      # 下面为连接池的补充设置，应用到上面所有数据源中
      # 初始化大小，最小，最大
    max-idle: 10
    max-wait: 1000
    min-idle: 5
    initial-size: 5
    output.ansi.enabled: always
      # 配置druid
    druid:
        # 配置获取连接等待超时的时间
      maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM t_user
      testWhileIdle: true
      testOnBorrow: true
      testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,log4j
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    #     useGlobalDataSourceStat: true