SpringBootJDBCExamples

• Z时代
• 2024-01-10
• 分类：综合

Technologies used :

• Spring Boot 2.1.2.RELEASE
• Spring JDBC 5.1.4.RELEASE
• HikariCP 3.2.0
• H2 in-memory database 1.4.197
• Maven 3
• Java 8

In Spring Boot JDBC, the database related beans like DataSource, JdbcTemplate and NamedParameterJdbcTemplate will be configured and created during the startup, to use it, just @Autowired the bean you want, for examples:

@Autowired
    JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate jdbcTemplate;

To connect to a database (e.g MySQL), include the JDBC driver in the project classpath:

pom.xml

<!-- MySQL JDBC driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

And define the datasoure properties in application.properties

application.properties

## MySQL
#spring.datasource.url=jdbc:mysql://192.168.1.4:3306/test
#spring.datasource.username=mkyong
#spring.datasource.password=password
# Oracle
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
#spring.datasource.username=system
#spring.datasource.password=Password123

Note

By default, Spring Boot 2 uses

HikariCP as the database connection pool.

1. Project Directory

2. Maven

2.1 spring-boot-starter-jdbc is what we need.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<projectxmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
		 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-jdbc</artifactId>
<packaging>jar</packaging>
<name>Spring Boot JDBC</name>
<version>1.0</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
</parent>
<properties>
<java.version>1.8</java.version>
<downloadSources>true</downloadSources>
<downloadJavadocs>true</downloadJavadocs>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- MySQL JDBC driver
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        -->
<!-- in-memory database -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.0</version>
</plugin>
</plugins>
</build>
</project>

Display the project dependencies.

$ mvn dependency:tree
[INFO] org.springframework.boot:spring-jdbc:jar:1.0
[INFO] +- org.springframework.boot:spring-boot-starter-jdbc:jar:2.1.2.RELEASE:compile
[INFO]|  +- org.springframework.boot:spring-boot-starter:jar:2.1.2.RELEASE:compile
[INFO]||  +- org.springframework.boot:spring-boot:jar:2.1.2.RELEASE:compile
[INFO]|||  - org.springframework:spring-context:jar:5.1.4.RELEASE:compile
[INFO]|||     +- org.springframework:spring-aop:jar:5.1.4.RELEASE:compile
[INFO]|||     - org.springframework:spring-expression:jar:5.1.4.RELEASE:compile
[INFO]||  +- org.springframework.boot:spring-boot-autoconfigure:jar:2.1.2.RELEASE:compile
[INFO]||  +- org.springframework.boot:spring-boot-starter-logging:jar:2.1.2.RELEASE:compile
[INFO]|||  +- ch.qos.logback:logback-classic:jar:1.2.3:compile
[INFO]||||  - ch.qos.logback:logback-core:jar:1.2.3:compile
[INFO]|||  +- org.apache.logging.log4j:log4j-to-slf4j:jar:2.11.1:compile
[INFO]||||  - org.apache.logging.log4j:log4j-api:jar:2.11.1:compile
[INFO]|||  - org.slf4j:jul-to-slf4j:jar:1.7.25:compile
[INFO]||  +- javax.annotation:javax.annotation-api:jar:1.3.2:compile
[INFO]||  +- org.springframework:spring-core:jar:5.1.4.RELEASE:compile
[INFO]|||  - org.springframework:spring-jcl:jar:5.1.4.RELEASE:compile
[INFO]||  - org.yaml:snakeyaml:jar:1.23:runtime
[INFO]|  +- com.zaxxer:HikariCP:jar:3.2.0:compile
[INFO]||  - org.slf4j:slf4j-api:jar:1.7.25:compile
[INFO]|  - org.springframework:spring-jdbc:jar:5.1.4.RELEASE:compile
[INFO]|     +- org.springframework:spring-beans:jar:5.1.4.RELEASE:compile
[INFO]|     - org.springframework:spring-tx:jar:5.1.4.RELEASE:compile
[INFO] +- com.h2database:h2:jar:1.4.197:compile

3. BookRepository

A pure Java interface for the repository, later implement with JdbcTemplate and NamedParameterJdbcTemplate

BookRepository.java

package com.mkyong.repository;
import com.mkyong.Book;
import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
publicinterfaceBookRepository{
intcount();
intsave(Book book);
intupdate(Book book);
intdeleteById(Long id);
    List<Book>findAll();
    List<Book>findByNameAndPrice(String name, BigDecimal price);
    Optional<Book>findById(Long id);
    String getNameById(Long id);
}

Book.java

package com.mkyong;
import java.math.BigDecimal;
publicclassBook{
private Long id;
private String name;
private BigDecimal price;
//... setters getters constructors...
}

4. JdbcTemplate

4.1 Some CRUD examples:

JdbcBookRepository.java

package com.mkyong.repository;
import com.mkyong.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
@Repository
publicclassJdbcBookRepositoryimplementsBookRepository{
// Spring Boot will create and configure DataSource and JdbcTemplate
// To use it, just @Autowired
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
publicintcount(){
return jdbcTemplate
.queryForObject("select count(*) from books", Integer.class);
}
@Override
publicintsave(Book book){
return jdbcTemplate.update(
"insert into books (name, price) values(?,?)",
                book.getName(), book.getPrice());
}
@Override
publicintupdate(Book book){
return jdbcTemplate.update(
"update books set price = ? where id = ?",
                book.getPrice(), book.getId());
}
@Override
publicintdeleteById(Long id){
return jdbcTemplate.update(
"delete books where id = ?",
                id);
}
@Override
public List<Book>findAll(){
return jdbcTemplate.query(
"select * from books",
(rs, rowNum)->
newBook(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getBigDecimal("price")
)
);
}
// jdbcTemplate.queryForObject, populates a single object
@Override
public Optional<Book>findById(Long id){
return jdbcTemplate.queryForObject(
"select * from books where id = ?",
newObject[]{id},
(rs, rowNum)->
                        Optional.of(newBook(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getBigDecimal("price")
))
);
}
@Override
public List<Book>findByNameAndPrice(String name, BigDecimal price){
return jdbcTemplate.query(
"select * from books where name like ? and price <= ?",
newObject[]{"%"+ name +"%", price},
(rs, rowNum)->
newBook(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getBigDecimal("price")
)
);
}
@Override
public String getNameById(Long id){
return jdbcTemplate.queryForObject(
"select name from books where id = ?",
newObject[]{id},
                String.class
);
}
}

5. NamedParameterJdbcTemplate

5.1 The NamedParameterJdbcTemplate adds support for named parameters in steads of classic placeholder ? argument.

NamedParameterJdbcBookRepository.java

package com.mkyong.repository;
import com.mkyong.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
@Repository
publicclassNamedParameterJdbcBookRepositoryextendsJdbcBookRepository{
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Override
publicintupdate(Book book){
return namedParameterJdbcTemplate.update(
"update books set price = :price where id = :id",
newBeanPropertySqlParameterSource(book));
}
@Override
public Optional<Book>findById(Long id){
return namedParameterJdbcTemplate.queryForObject(
"select * from books where id = :id",
newMapSqlParameterSource("id", id),
(rs, rowNum)->
                        Optional.of(newBook(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getBigDecimal("price")
))
);
}
@Override
public List<Book>findByNameAndPrice(String name, BigDecimal price){
        MapSqlParameterSource mapSqlParameterSource =newMapSqlParameterSource();
        mapSqlParameterSource.addValue("name","%"+ name +"%");
        mapSqlParameterSource.addValue("price", price);
return namedParameterJdbcTemplate.query(
"select * from books where name like :name and price <= :price",
                mapSqlParameterSource,
(rs, rowNum)->
newBook(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getBigDecimal("price")
)
);
}
}

6. application.properties

6.1 For in-memory database, nothing to configure, if we want to connect to a real database, define a datasource.url property:

application.properties

logging.level.org.springframework=info
#logging.level.org.springframework.jdbc=DEBUG
logging.level.com.mkyong=INFO
logging.level.com.zaxxer=DEBUG
logging.level.root=ERROR
spring.datasource.hikari.connectionTimeout=20000
spring.datasource.hikari.maximumPoolSize=5
logging.pattern.console=%-5level %logger{36} - %msg%n
## MySQL
#spring.datasource.url=jdbc:mysql://192.168.1.4:3306/test
#spring.datasource.username=mkyong
#spring.datasource.password=password
# Oracle
#spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
#spring.datasource.username=system
#spring.datasource.password=Password123

7. Start Spring Boot

7.1 Start Spring Boot application, test CRUD.

StartApplication.java

package com.mkyong;
import com.mkyong.repository.BookRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
publicclassStartApplicationimplementsCommandLineRunner{
privatestaticfinal Logger log = LoggerFactory.getLogger(StartApplication.class);
@Autowired
    JdbcTemplate jdbcTemplate;
@Autowired
//@Qualifier("jdbcBookRepository")              // Test JdbcTemplate
@Qualifier("namedParameterJdbcBookRepository")// Test NamedParameterJdbcTemplate
private BookRepository bookRepository;
publicstaticvoidmain(String[] args){
        SpringApplication.run(StartApplication.class, args);
}
@Override
publicvoidrun(String... args){
        log.info("StartApplication...");
runJDBC();
}
voidrunJDBC(){
        log.info("Creating tables for testing...");
        jdbcTemplate.execute("DROP TABLE books IF EXISTS");
        jdbcTemplate.execute("CREATE TABLE books("+
"id SERIAL, name VARCHAR(255), price NUMERIC(15, 2))");
        List<Book> books = Arrays.asList(
newBook("Thinking in Java",newBigDecimal("46.32")),
newBook("Mkyong in Java",newBigDecimal("1.99")),
newBook("Getting Clojure",newBigDecimal("37.3")),
newBook("Head First Android Development",newBigDecimal("41.19"))
);
        log.info("[SAVE]");
        books.forEach(book ->{
            log.info("Saving...{}", book.getName());
            bookRepository.save(book);
});
// count
        log.info("[COUNT] Total books: {}", bookRepository.count());
// find all
        log.info("[FIND_ALL] {}", bookRepository.findAll());
// find by id
        log.info("[FIND_BY_ID] :2L");
        Book book = bookRepository.findById(2L).orElseThrow(IllegalArgumentException::new);
        log.info("{}", book);
// find by name (like) and price
        log.info("[FIND_BY_NAME_AND_PRICE] : like "%Java%" and price <= 10");
        log.info("{}", bookRepository.findByNameAndPrice("Java",newBigDecimal(10)));
// get name (string) by id
        log.info("[GET_NAME_BY_ID] :1L = {}", bookRepository.getNameById(1L));
// update
        log.info("[UPDATE] :2L :99.99");
        book.setPrice(newBigDecimal("99.99"));
        log.info("rows affected: {}", bookRepository.update(book));
// delete
        log.info("[DELETE] :3L");
        log.info("rows affected: {}", bookRepository.deleteById(3L));
// find all
        log.info("[FIND_ALL] {}", bookRepository.findAll());
}
}

8. Demo

$ mvn spring-boot:run
INFO  com.mkyong.StartApplication - Started StartApplication in 1.051 seconds (JVM running for 1.3)
INFO  com.mkyong.StartApplication - StartApplication...
INFO  com.mkyong.StartApplication - Creating tables for testing...
DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............20000
DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................."org.h2.Driver"
DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................5
DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................5
DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
DEBUG com.zaxxer.hikari.HikariConfig - username........................"sa"
DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:testdb user=SA
INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
INFO  com.mkyong.StartApplication - [SAVE]
INFO  com.mkyong.StartApplication - Saving...Thinking in Java
INFO  com.mkyong.StartApplication - Saving...Mkyong in Java
INFO  com.mkyong.StartApplication - Saving...Getting Clojure
INFO  com.mkyong.StartApplication - Saving...Head First Android Development
INFO  com.mkyong.StartApplication - [COUNT] Total books: 4
INFO  com.mkyong.StartApplication - [FIND_ALL]
[Book{id=1, name="Thinking in Java", price=46.32}, 
Book{id=2, name="Mkyong in Java", price=1.99}, 
Book{id=3, name="Getting Clojure", price=37.30}, 
Book{id=4, name="Head First Android Development", price=41.19}]
INFO  com.mkyong.StartApplication - [FIND_BY_ID] :2L
INFO  com.mkyong.StartApplication - Book{id=2, name="Mkyong in Java", price=1.99}
INFO  com.mkyong.StartApplication - [FIND_BY_NAME_AND_PRICE]: like "%Java%" and price <= 10
INFO  com.mkyong.StartApplication - [Book{id=2, name="Mkyong in Java", price=1.99}]
INFO  com.mkyong.StartApplication - [GET_NAME_BY_ID] :1L = Thinking in Java
INFO  com.mkyong.StartApplication - [UPDATE] :2L :99.99
INFO  com.mkyong.StartApplication - rows affected: 1
INFO  com.mkyong.StartApplication - [DELETE] :3L
INFO  com.mkyong.StartApplication - rows affected: 1
INFO  com.mkyong.StartApplication - [FIND_ALL]
[Book{id=1, name="Thinking in Java", price=46.32},
Book{id=2, name="Mkyong in Java", price=99.99}, 
Book{id=4, name="Head First Android Development", price=41.19}]
INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...
DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Before shutdown stats (total=1, active=0, idle=1, waiting=0)
DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection conn0: url=jdbc:h2:mem:testdb user=SA: (connection evicted)
DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After shutdown stats (total=0, active=0, idle=0, waiting=0)
INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.

以上是 SpringBootJDBCExamples 的全部内容， 来源链接： utcz.com/z/512864.html