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

什么是jdbc

java database connectivity 是一种用于执行sql语句的java api，与数据库建立连接、发送操作数据库的语句并处理结果。

spring boot 使用 jdbc

增加依赖

修改pom.xml：将dependecies 修改为如下两个

				?

									<dependencies>

									    <dependency>

									      <groupid>org.springframework.boot</groupid>

									      <artifactid>spring-boot-starter-jdbc</artifactid>

									    </dependency>

									    <dependency>

									      <groupid>com.h2database</groupid>

									      <artifactid>h2</artifactid>

									    </dependency>

									  </dependencies>

创建 customer.java 类

				?

									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

									}

修改application 类

				?

									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

说明

官网的例子，没有配置jdbc template的datasource，默认使用的是h2 的内存存储的数据库，只能当做测试使用。下面会有介绍更改datasource的方法

介绍下 commandlinerunner

功能

在项目启动后，执行执行功能，我们可以定一个类，去实现commandlinerunner接口，重写run方法，执行一部分操作。需要注意的是，定义类必须标记为spring管理的组件

测试类

				?

									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("-----------------------");

									  }

									}

介绍下jdbctempalte

在jdbc核心包中，jdbctemplate是主要的类，简化了jdbc的使用，避免了一些常规错误。它能够执行jdbc核心流程，在应用代码之上提供sql语句、导出结果。这个类执行sql查询、更新、对结果集重复操作捕获jdbc的异常。并将它翻译成 org.springframework.dao 包中定义的基本的、信息量更大的异常层次结构。

jdbc构造方法

jdbctemplate()

				?

									//为bean创建一个jdbctemplate以供使用

									//再没配置datasource的情况下 springboot提供了 一些嵌入式的数据库支持，上面的例子使用的就是h2数据库，是一个内存的数据库

jdbctemplate(javax.sql.datasource datasource)

				?

									//构造的时候传入一个 datasource，来获取链接

									//jdbctemplate spring boot默认链接的是h2 database，

在spring boot中配置mysql 数据库

数据库配置类 db_config

				?

									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;

									    }

									}

application.properties

				?

									# database

									# mysqljdbc连接驱动

									db.driver:com.mysql.cj.jdbc.driver

									db.url:jdbc:mysql://localhost:3306/test

									db.username:root

									db.password:root

pom.xml

				?

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

application 启动类修改

				?

									@autowired

									  jdbctemplate jdbctemplate;

									  //下面是加载了数据库的配置。只需要增加这个

									  @autowired

									  db_config db_config;

运行程序后会发现数据存储到本地数据库

				?

									select * from customers;

									------------------------

									1  john  woo

									2  jeff  dean

									3  josh  bloch

									4  josh  long

另一个简单的方法配置mysql数据库

直接修改application.properties

				?

									# 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

将properties改成yml文件 application.yml

				?

									spring:

									 datasource:

									  url: jdbc:mysql://localhost:3306/test

									  username: root

									  password: root

									  driver-class-name: com.mysql.cj.jdbc.driver