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,etcprivate DataSource createDataSource() { DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(); dataSourceBuilder.driverClassName("com.mysql.cj.jdbc.Driver"); 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.neveropen.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@Configurationpublic 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.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
Car table
