Open In App

Spring – Using SQL Scripts with Spring JDBC + JPA + HSQLDB

Improve
Improve
Like Article
Like
Save
Share
Report

We will be running the SQL scripts with Spring JDBC +JPA + HSQLDB. These scripts are used to perform SQL commands at the time of application start. Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. JPA is just a specification that facilitates object-relational mapping to manage relational data in Java applications. We will be using JDBC as a connector and JPA with HSQLDB which is the leading SQL relational database system written in Java. It offers a small, fast multithreaded, and transactional database engine with in-memory and disk-based tables and supports embedded and server modes. It includes a powerful command-line SQL tool and simple GUI query tools. For writing SQL scripts, we need to change the DataSource configuration. DataSource is a name given to the connection set up to a database from a server. 

Steps to be followed

Step 1. Add dependency in the pom file

XML




<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>2.2.2.RELEASE</version>
</dependency>
  
<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.4.0</version>
    <scope>runtime</scope>
</dependency>


Step 2. Create a DataSource config file mentioning the DB details and schema file location

We will create a DataSource instance.

Java




// This function create a datasource. Here we will provide
// the DB details like driver,db name,etc
private DataSource createDataSource() {                                  
    DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();    
    dataSourceBuilder.driverClassName("com.mysql.cj.jdbc.Driver");       
    dataSourceBuilder.url("jdbc:mysql://localhost:3306/geeksforgeeks");  
    dataSourceBuilder.username("root");                                  
    dataSourceBuilder.password("Truthpolls@464");                        
    return dataSourceBuilder.build();                                    
}


We will create a DatabasePopulator that will populate the data into the tables.

Java




// This is database populator which will read the SQL script.
private DatabasePopulator createDatabasePopulator() {                                
    ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();   
    databasePopulator.setContinueOnError(true);                                      
    databasePopulator.addScript(new ClassPathResource("schema.sql"));                
    return databasePopulator;                                                        
}


Then we will execute the populator on the data source we have created using DatabasePopulatorUtils

Java




// This is the bean which connects the application to the database. 
// We will use the datasource and the database populator
// for performing commands on the Database
@Bean(name = "dataSource")                                                  
public DataSource getDataSource(){                                          
    DataSource dataSource = createDataSource();                             
    DatabasePopulatorUtils.execute(createDatabasePopulator(), dataSource);  
    return dataSource;                                                      
}


These config changes we have done for Bean are named “dataSource”

Java




package com.geeksforgeeks.Spring.MVC.Pagination.configs;
  
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.DatabasePopulatorUtils;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
  
import javax.sql.DataSource;
  
// annotated as configuration so application
// knows to run it in the start
@Configuration
public class DataSourceConfig {
    @Bean(name = "dataSource")
    public DataSource getDataSource(){
        DataSource dataSource = createDataSource();
        DatabasePopulatorUtils.execute(createDatabasePopulator(), dataSource);
        return dataSource;
    }
  
    private DatabasePopulator createDatabasePopulator() {
        ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
        databasePopulator.setContinueOnError(true);
        databasePopulator.addScript(new ClassPathResource("schema.sql"));
        return databasePopulator;
    }
  
    private DataSource createDataSource() {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("org.hsqldb.jdbc.JDBCDriver");
        dataSourceBuilder.url("jdbc:hsqldb:hsql://localhost/geeksforgeeks");
        dataSourceBuilder.username("root");
        dataSourceBuilder.password("pass");
        return dataSourceBuilder.build();
    }
}


Step 3. Then create the SQL scripts

insert into car (id, name, price) values (default, ‘Audi’,3000000 )
insert into car (id, name, price) values (default, ‘BMW’,4000000 )
insert into car (id, name, price) values (default, ‘Jaguar’,3500000 )

insert into book (id, name, price) values (default, ‘Book-1’,600 )
insert into book (id, name, price) values (default, ‘Book-2’,500 )
insert into book (id, name, price) values (default, ‘Book-3’,800 )
 

When we will run this application these scripts will run and the database will be populated. The database looked like this now (ran application twice).

Output:

Book table

Book table

Car table

Car table



Last Updated : 24 May, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads