SpringBoot使用JDBC获取相关的数据方法

    
      org.springframework.boot
      spring-boot-starter-jdbc
    
    
      com.h3database
      h3
    
  

package com.example.kane.Model;

public class Customer {
  private long id;
  private String firstName, lastName;

  public Customer(long id, String firstName, String lastName) {
    this.id = id;
    this.firstName = firstName;
    this.lastName = lastName;
  }

  @Override
  public String toString() {
    return String.format(
        "Customer[id=%d, firstName='%s', lastName='%s']",
        id, firstName, lastName);
  }

  // getters & setters omitted for brevity
}

package com.example.kane;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.client.RestTemplateBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.EnableScheduling;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.client.RestTemplate;

import com.example.kane.Model.Customer;

@SpringBootApplication
//@EnableScheduling
public class RestfulWebService1Application implements CommandLineRunner{
  
  private static final Logger log = LoggerFactory.getLogger(RestfulWebService1Application.class);

  public static void main(String args[]) {
    SpringApplication.run(RestfulWebService1Application.class, args);
  }

  @Autowired
  JdbcTemplate jdbcTemplate;

  @Override
  public void run(String... strings) throws Exception {

    log.info("Creating tables");

    jdbcTemplate.execute("DROP TABLE customers IF EXISTS");
    jdbcTemplate.execute("CREATE TABLE customers(" +
        "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");

    // Split up the array of whole names into an array of first/last names
    List splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream()
        .map(name -> name.split(" "))
        .collect(Collectors.toList());

    // Use a Java 8 stream to print out each tuple of the list
    splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1])));

    // Uses JdbcTemplate's batchUpdate operation to bulk load data
    jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);

    log.info("Querying for customer records where first_name = 'Josh':");
    jdbcTemplate.query(
        "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[] { "Josh" },
        (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
    ).forEach(customer -> log.info(customer.toString()));
  }
}

package com.example.kane.Model;

import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Component
@Order(value=1) //因为可能有许多事情要做，Order 可以根据大小，判读执行的顺序
public class run_after_application implements CommandLineRunner{

  @Override
  public void run(String... args) throws Exception {
    // TODO Auto-generated method stub
    System.out.println("-----------------------");
  }
  
}

//为Bean创建一个JdbcTemplate以供使用
//再没配置DataSource的情况下 springboot提供了 一些嵌入式的数据库支持，上面的例子使用的就是H2数据库，是一个内存的数据库

//构造的时候传入一个 DataSource，来获取链接
//JdbcTemplate Spring boot默认链接的是H2 database，

package com.example.kane.config;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class db_config {
  //这个类是一个Config类
    @Value("${db.driver}")
    private String DRIVER;

    @Value("${db.password}")
    private String PASSWORD;

    @Value("${db.url}")
    private String URL;

    @Value("${db.username}")
    private String USERNAME;
    @Bean
    public DataSource dataSource1() {
      BasicDataSource dataSource = new BasicDataSource();
      dataSource.setDriverClassName(DRIVER);
      dataSource.setUrl(URL);
      dataSource.setUsername(USERNAME);
      dataSource.setPassword(PASSWORD);
      return dataSource;
    }
}

# Database
# mysqljdbc连接驱动
db.driver:com.mysql.cj.jdbc.Driver
db.url:jdbc:mysql://localhost:3306/test
db.username:root
db.password:root

  commons-dbcp
  commons-dbcp
  1.4


  mysql
  mysql-connector-java
  runtime

@Autowired
  JdbcTemplate jdbcTemplate;
  //下面是加载了数据库的配置。只需要增加这个
  @Autowired
  db_config db_config;

SELECT * from customers;
------------------------
1  John  Woo
2  Jeff  Dean
3  Josh  Bloch
4  Josh  Long

# database
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring:
 datasource:
  url: jdbc:mysql://localhost:3306/test
  username: root
  password: root
  driver-class-name: com.mysql.cj.jdbc.Driver