[Tips] Spring Boot - Access Data with JDBC

Last updated on:

Spring JDBC is intuitive: you issue a query, and you get a dataset; when you extract the data from the ResultSet and save it to your POJO object, you can transform it at that point. If the database is configured to have a good performance on processing SQL statements, you can come up with graceful SQLs to shift more work to the database, as a result, you can simplify the operations in your programs.   

We’ll walk through the following examples to see how Spring Boot supports it.

The source codes are available in the sample projects on GitHub.

Prerequisite

First, get the Spring Tool Suite installed in your Eclipse. You can find it in Eclipse Marketplace. If you use Tanzu’s Spring Tool, you can skip this because the IDE comes with the Tool Suite.

After Spring Tool Suite was installed, you can see Spring Boot showing up when you create a project, and, choose Spring Starter Project for practice.

Moreover, you’ll need to create the following tables in the database. In the examples, Oracle XE is the database being used.

SpringMVC project/schema.sql

CREATE TABLE Departments (
   Department_Id NUMBER(4)
 , Department_Name VARCHAR2(20)
 , Manager_Id NUMBER(6)
)
 NOLOGGING
 PARALLEL;

CREATE TABLE Employees (
   Employee_Id NUMBER(6)
 , First_Name VARCHAR2(20)
 , Last_Name VARCHAR2(20)
 , Salary NUMBER(8)
 , Hire_Date CHAR(10)
 , Manager_Id NUMBER(6)
 , Department_Id NUMBER(4)
)
 NOLOGGING
 PARALLEL;

At application level, data classes are created accordingly. Refer to Departments.java and Employees.java in SpringMVC project, respectively. 

Dependencies

You’ll need to add JDBC and Oracle dependencies to the pom.xml file, as shown in the below example.

SpringMVC project/pom.xml

  	<dependency>
		<groupid>org.springframework.boot</groupid>
		<artifactid>spring-boot-starter-jdbc</artifactid>
	</dependency>		
	<dependency>
		<groupid>com.oracle.database.jdbc</groupid>
		<artifactid>ojdbc11</artifactid>
		<scope>runtime</scope>
	</dependency>

However, when you create your Spring Starter project, if you check Oracle Driver and Spring Data JDBC options on the Project Dependencies page, you will get them in pom.xml automatically.

Project Dependencies Page

Configuration

This is a beautiful part of Spring Boot. As long as we write down the connection fields in the application property file, Spring Boot will auto-configure the data source. 

Note that the names of the keys should start with “spring.datasource”.

SpringMVC project/application.properties

spring.config.activate.on-profile=dev
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xepdb1
spring.datasource.username=user
spring.datasource.password=password

Data Classes

The data classes, in association with the tables, are defined as follows. They are POJOs, neither implementing any super interface, nor extending any super class.

SpringMVC project/DptDto.java

public class DptDto {
	private Long department_id;
	private String department_name;
	private Integer manager_id;
	
	/*
	 * Constructors, Getters, Setters etc.
	 */
	…
}

SpringMVC project/EmpDto.java

public class EmpDto {
	private Integer employee_id;
	private String first_name;
	private String last_name;
	private Double salary;
	private String hire_date;	
	private Integer manager_id;
	private Long department_id;
	/*
	 * Constructors, Getters, Setters etc.
	 */
	…
}

DAO

As Spring Boot takes care of the configuration, we can simply autowire a JdbcTemplate bean in our DAO bean. Below is the example of getting all the records from the Departments table.

SpringMVC project/DptDaoImpl.java

  @Repository
  public class DptDaoImpl implements DptDao {

      @Autowired
      JdbcTemplate jdbcTemplate;

      public List<DptDto> getAllDpts(){
          String sql = "SELECT department_id, department_name, manager_id "
          	+ "FROM departments "
          	+ "ORDER BY department_id";

          List<DptDto>> dptlist = null;
          try {
              dptlist = jdbcTemplate.query(sql,
				(rs, rowNum) -> new DptDto(rs.getLong("department_id"), 
				rs.getString("department_name"), 
				rs.getInt("manager_id"))
              );
          } catch (Exception e) {
              System.out.println(e);
          }

          return dptlist;
      }
      …
  }

Test the app

So, we can run the DAO to retrieve data from the database now. To get a better visual effect, we display the list of departments on a web page.

Add another data source

It is not a rare case to handle multiple data sources in an enterprise application. How to do it? Firstly, add the connection strings into the application property file.

secondary.datasource.driver-class-name=oracle.jdbc.OracleDriver
secondary.datasource.url=jdbc:oracle:thin:@localhost:1521/xepdb1
secondary.datasource.username=user
secondary.datasource.password=password

Secondly, declare a data source bean in a configuration class, in case of Java configuration;  and define a JdbcTemplate bean associated with the preceding data source. 

Don’t forget to add a qualifier to these beans because we need to distinguish them from the default ones. This is important.

SpringMVC project/SecondaryDataSourceConfig.java

@Configuration(proxyBeanMethods = false)
public class SecondaryDataSourceConfig {
			
	/*
	 * Secondary data source
	 */
	@Qualifier("secondary")
	@Bean(defaultCandidate = false)
	public DataSource sndDataSource(
    		@Value("${secondary.datasource.driver-class-name}") String dsDriverClassName,	
    		@Value("${secondary.datasource.url}") String dsURL,	
    		@Value("${secondary.datasource.username}") String dsUsername,	
    		@Value("${secondary.datasource.password}") String dsPassword
    		) {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName(dsDriverClassName);
		dataSource.setUrl(dsURL);
		dataSource.setUsername(dsUsername);
		dataSource.setPassword(dsPassword);
        
		return dataSource;
	}

	/*
	 * JdbcTemplate for the secondary data source
	 */
	@Qualifier("secondary")
	@Bean(defaultCandidate = false)
	public JdbcTemplate sndJdbcTemplate(
			@Qualifier("secondary") DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}
}

This also can be done with less codes, as shown in the following example.

SpringREST project/SecondaryDataSourceConfig.java

@Configuration(proxyBeanMethods = false)
public class SecondaryDataSourceConfig {
	
	@Profile("dev")
	@Qualifier("secondary")
	@Bean(defaultCandidate = false)
	@ConfigurationProperties("secondary.datasource")
	public DataSource sndDataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		
		return dataSource;
	}
	
	@Profile("dev")
	@Qualifier("secondary")
	@Bean(defaultCandidate = false)
	public JdbcTemplate sndJdbcTemplate(
			@Qualifier("secondary") DataSource dataSource) {
		
		return new JdbcTemplate(dataSource);
	}
	…
}

Thirdly, just autowire them in your DAO bean, with the qualifier specified.

SpringMVC project/EmpDaoImpl.java

@Repository
public class EmpDaoImpl implements EmpDao {
	
	@Qualifier("secondary")
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	public List<EmpDto> findAll(){
		String sql = "SELECT employee_id, first_name, last_name, salary, "
        		+ "hire_date, manager_id, department_id "
        		+ "FROM employees "
        		+ "ORDER BY employee_id";
        
		return jdbcTemplate.query(sql, new RowMapper<EmpDto>() {
			@Override
			public EmpDto mapRow(ResultSet rs, int rowNum) throws SQLException {
            			EmpDto empDto = new EmpDto(
            				rs.getInt("employee_id"),
            				rs.getString("first_name"),
            				rs.getString("last_name"),
            				rs.getDouble("salary"),
            				rs.getString("hire_date"),
            				rs.getInt("manager_id"),
            				rs.getLong("department_id"));

            			return empDto;
			}
		});	
	};
}

The list of employees, retrieved from the secondary database, are shown in the following screenshot.

Take over the configuration

Due to some reason, in case of explicitly configuring the primary data source, we can do it by disabling the auto-configuration with this instruction.

@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class })

SpringREST project/PrimaryDataSourceConfig.java

@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class })
public class SpringRestApplication {
	…
	public static void main(String[] args) {
		SpringApplication.run(SpringRestApplication.class, args);
	}
}

Then, create a configuration class where to define data source related beans, in the same way as we did for the secondary data source in Section “Add another data source”.  This time we make them primary. 

SpringREST project/PrimaryDataSourceConfig.java

@Configuration(proxyBeanMethods = false)
public class PrimaryDataSourceConfig {
			
	/*
	 * Primary data source
	 */
	@Primary
	@Bean
	public DataSource dataSource(
    		@Value("${spring.datasource.driver-class-name}") String dsDriverClassName,
    		@Value("${spring.datasource.url}") String dsURL,
    		@Value("${spring.datasource.username}") String dsUsername,
    		@Value("${spring.datasource.password}") String dsPassword
    		) {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName(dsDriverClassName);
		dataSource.setUrl(dsURL);
		dataSource.setUsername(dsUsername);
		dataSource.setPassword(dsPassword);
        
		return dataSource;
	}
	
	/*
	 * JdbcTemplate for primary data source
	 */
	@Primary
	@Bean
	public JdbcTemplate jdbcTemplate(
			DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}
	…
}