Spring Boot JDBC Example

0

In this article we will show how to use spring boot to create a project dependent on database.

Create Project using Spring Starter

  1. Enter group name com.javarticles.springboot
  2. Enter artifact ID SpringbootDatasource
  3. By default, we will use the group name itself as package name
  4. Select Jar as Packaging
  5. Use Java Version as 1.8.
  6. Use Spring Boot Version as 1.3.1.RELEASE
  7. Add JDBC, JPA and MySQL as dependencies
  8. Generate Project to download the project archive.
Generate database dependent spring boot project

Generate database dependent spring boot project

Dependencies

Since the application we are going to create depends on database, we need the below artifacts.

  1. springbootstarterjdbc – This spring boot provided starter is responsible for support to connect and use JDBC databases, and other JDBC related services like connection pools etc.
  2. springbootstarterdata-jpa – This is again spring boot provided JPA starter that provides the dependent libraries related to Java Persistence API, the JPA provider like Hibernate.
  3. mysql-connector-java – MySQL java driver

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="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>

	<groupId>com.javarticles.springboot</groupId>
	<artifactId>SpringbootDatasource</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>SpringbootDatasource</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.3.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
	

</project>

We need >mysql-connector-java only at runtime so its scope is set to runtime. This is because DataSource object provides abstraction to underlying datasource so we don’t really need the database driver at compile time.

How to initialize database in spring boot

Spring JDBC has a DataSource initializer feature that allows us to initialize the database by feeding in the schema and data sql using <jdbc:script>.

	<jdbc:initialize-database data-source="dataSource"
		enabled="true">
		<jdbc:script location="classpath:db-schema.sql" />
		<jdbc:script location="classpath:db-test-data.sql"/>
	</jdbc:initialize-database>

Spring Boot enables initialization of database by default.

  1. Loads SQL from the standard locations schema.sql and data.sql, if found in the classpath.
  2. Loads the schema-${platform}.sql and data-${platform}.sql files, if found in the classpath. platform is the value of spring.datasource.platform
  3. Set spring.datasource.initialize to false if you don’t want spring boot to automatically initialize
  4. By default if the database initialization scripts fail, application will fail to launch.
  5. To disable the fail-fast you can set spring.datasource.continueOnError=true.

Database Scripts

we will use the spring boot specified file for schema, that is, schema.sql.

schema.sql:

drop table if exists `employee`;
CREATE TABLE `employee` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(100) NOT NULL,
  `AGE` INT(3),
  `JOINING_DATE` DATE,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

For the data, we will use a different file name but for spring boot to identify the file, you need to set the property spring.datasource.data=emp_data.sql

emp_data.sql

insert into employee(id, name) values (1, "Sam");
insert into employee(id, name) values (2, "John");

You can also use the file pattern data-${platform}.sql as the data file. If we set spring.datasource.platform=mysqlthen the file name would be data-mysql.sql. For schema, it would be schema-mysql.sql. If you want the data-mysql.sql to be imported then you need to make sure spring.datasource.data is not set in your application properties.
data-mysql.sql

insert into employee(id, name) values (3, "Kim");

Configure data source in application properties

application.properties:

spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=mnr
spring.datasource.data=emp_data.sql
spring.datasource.password=mnrpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.platform=mysql
#spring.datasource.initialize=false
#spring.datasource.continueOnError=true

Component to execute database operations

Create a new component class for the database operations. You need to simply annotate the class with @Component. In order to access the JdbcTemplate simply auto-wire the field using @Autowired. We have method to insert and query the employees which we will use it in spring boot application to demonstrate the JDBC support.

DatabaseOperations:

package com.javarticles.springboot;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class DatabaseOperations {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void queryEmployees() {
        System.out.println("Query employees");
        String sql = "SELECT ID, NAME, AGE FROM EMPLOYEE";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> row : list) {
            System.out.println(row.get("name"));
        }
    }

    public void insertEmployee(final Employee employee) throws SQLException {
        System.out.println("Insert employee " + employee);
        String sql = "INSERT into EMPLOYEE(ID, NAME, AGE) VALUES (?, ?, ?)";
        jdbcTemplate.update(
                sql,
                new Object[] { employee.getId(), employee.getName(),
                        employee.getAge() });
    }
}

Here is the employee bean.

Employee:

package com.javarticles.springboot;

public class Employee {
    private int id;
    private String name;
    private int age;

    public Employee(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public int getAge() {
        return age;
    }
    
    public String toString() {
        return "Employee (" + id + ", " + name + ", " + age + ")";
    }
}

Spring boot application is pretty simple. We just need to get the DatabaseOperations bean, call queryEmployees(), then call insertEmployee() to insert a new employee and finally call queryEmployees() to query again. This time we should see the new employee in the query list.

SpringBootDatasourceApplication:

package com.javarticles.springboot;

import java.sql.SQLException;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

@SpringBootApplication
public class SpringBootDatasourceApplication {

    public static void main(String[] args) throws SQLException {
        ConfigurableApplicationContext context = SpringApplication.run(
                SpringBootDatasourceApplication.class, args);

        DatabaseOperations databaseOperations = (DatabaseOperations) context
                .getBean("databaseInit");

        databaseOperations.queryEmployees();

        databaseOperations.insertEmployee(new Employee(100, "David", 45));

        System.out.print("After insert, ");
        databaseOperations.queryEmployees();
    }
}

Output:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.3.1.RELEASE)

2016-01-11 21:41:34.848  INFO 10376 --- [           main] c.j.s.SpringBootDatasourceApplication    : Starting SpringBootDatasourceApplication on INMAA1-L1005 with PID 10376 (C:\javarticles_ws\springBootDatasource\target\classes started by mokkara in C:\javarticles_ws\springBootDatasource)
2016-01-11 21:41:34.850  INFO 10376 --- [           main] c.j.s.SpringBootDatasourceApplication    : No active profile set, falling back to default profiles: default
2016-01-11 21:41:34.881  INFO 10376 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.spring[email protected]4d1b0d2a: startup date [Mon Jan 11 21:41:34 IST 2016]; root of context hierarchy
2016-01-11 21:41:35.869  INFO 10376 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/C:/javarticles_ws/springBootDatasource/target/classes/schema.sql]
2016-01-11 21:41:35.938  INFO 10376 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from URL [file:/C:/javarticles_ws/springBootDatasource/target/classes/schema.sql] in 69 ms.
2016-01-11 21:41:35.940  INFO 10376 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [emp_data.sql]
2016-01-11 21:41:35.946  INFO 10376 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [emp_data.sql] in 6 ms.
2016-01-11 21:41:36.007  INFO 10376 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2016-01-11 21:41:36.031  INFO 10376 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
	name: default
	...]
2016-01-11 21:41:36.087  INFO 10376 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {4.3.11.Final}
2016-01-11 21:41:36.089  INFO 10376 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2016-01-11 21:41:36.090  INFO 10376 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2016-01-11 21:41:36.184  INFO 10376 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
2016-01-11 21:41:36.292  INFO 10376 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
2016-01-11 21:41:36.333  INFO 10376 --- [           main] o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory
2016-01-11 21:41:36.730  INFO 10376 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2016-01-11 21:41:36.740  INFO 10376 --- [           main] c.j.s.SpringBootDatasourceApplication    : Started SpringBootDatasourceApplication in 2.074 seconds (JVM running for 2.317)
Query employees
Sam
John
Insert employee Employee (100, David, 45)
After insert, Query employees
Sam
John
David
2016-01-11 21:41:36.770  INFO 10376 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.spring[email protected]4d1b0d2a: startup date [Mon Jan 11 21:41:34 IST 2016]; root of context hierarchy
2016-01-11 21:41:36.771  INFO 10376 --- [       Thread-2] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown
2016-01-11 21:41:36.772  INFO 10376 --- [       Thread-2] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

Download the source code

This was an example about spring boot JDBC support.

You can download the source code here: springBootDatasourceExample.zip
Share.

Comments are closed.