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

<dependencies>
  <dependency>
    <groupid>org.springframework.boot</groupid>
    <artifactid>spring-boot-starter-jdbc</artifactid>
  </dependency>
  <dependency>
    <groupid>com.h2database</groupid>
    <artifactid>h2</artifactid>
  </dependency>
  </dependencies>

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<object[]> 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()));
  }
}

2019-03-01 14:19:52.078  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : creating tables
2019-03-01 14:19:52.086  info 7436 --- [  restartedmain] com.zaxxer.hikari.hikaridatasource     : hikaripool-1 - starting...
2019-03-01 14:19:52.392  info 7436 --- [  restartedmain] com.zaxxer.hikari.hikaridatasource     : hikaripool-1 - start completed.
2019-03-01 14:19:52.429  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : inserting customer record for john woo
2019-03-01 14:19:52.430  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : inserting customer record for jeff dean
2019-03-01 14:19:52.430  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : inserting customer record for josh bloch
2019-03-01 14:19:52.430  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : inserting customer record for josh long
2019-03-01 14:19:52.461  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : querying for customer records where first_name = 'josh':
2019-03-01 14:19:52.480  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : customer[id=3, firstname='josh', lastname='bloch']
2019-03-01 14:19:52.480  info 7436 --- [  restartedmain] c.e.kane.restfulwebservice1application   : customer[id=4, firstname='josh', lastname='long']
2019-03-01 14:20:01.122  info 7436 --- [nio-8080-exec-5] o.a.c.c.c.[tomcat].[localhost].[/]     : initializing spring dispatcherservlet 'dispatcherservlet'
2019-03-01 14:20:01.123  info 7436 --- [nio-8080-exec-5] o.s.web.servlet.dispatcherservlet    : initializing servlet 'dispatcherservlet'
2019-03-01 14:20:01.146  info 7436 --- [nio-8080-exec-5] o.s.web.servlet.dispatcherservlet    : completed initialization in 22 ms

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

<dependency>
  <groupid>commons-dbcp</groupid>
  <artifactid>commons-dbcp</artifactid>
  <version>1.4</version>
</dependency>
<dependency>
  <groupid>mysql</groupid>
  <artifactid>mysql-connector-java</artifactid>
  <scope>runtime</scope>
</dependency>
<!-- 需要用到commons-dbcp连接池，以及连接mysql使用的drver-->

@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

• execute方法： 可以用于执行任何sql语句，一般用于执行ddl语句；
  
• update方法及batchupdate方法： update方法用于执行新增、修改、删除等语句；batchupdate方法用于执行批处理相关语句；
  
• query方法及queryforxxx方法： 用于执行查询相关语句；
  
• call方法： 用于执行存储过程、函数相关语句。
  

• dbcp：提供最大空闲连接数，超过连接全部自动断开连接，其他两个没有。
  
• c3p0：提供最大空闲连接时间，这样可以做到自动收回空闲连接的机制
  
• druid：阿里出品的，同样提供最大的空闲连接时间