Spring Boot JDBC + MySQL – How to Create/Configure a DataSource

In this article, I am going to explain you how to create/configure a datasource in Spring boot with an example. We are all aware that the process of creating a traditional Spring JDBC application is little tedious because of its XML configurations and we need to follow few steps to configure any datasource. But believe me with Spring Boot creating a JDBC application is as easy as counting 1,2,3.. 🙂

Steps to Create DataSource in Spring Boot Application

• Add Spring Boot JDBC dependency in POM.xml
• Add datasource information in application.properties
• Get JDBCTemplate object in your DAO with @Autowired annotation and use it

…there is no point 4, that’t it 😉

1. Add Spring Boot JDBC Dependency

In order to work with Spring Boot JDBC, first we need to add the following dependency in your applications POM.xml

<!-- Spring boot jdbc dependency -->
<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<!-- MySql dependency -->
<dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
</dependency>

2. Add Datasource Information in application.properties

In real time applications, we will write the datasources information generally in the XML’s, I hope you all aware of that. In Spring Boot, rather then writing in XML’s just open your application.properties and add your datasource information to the Spring Boot’s predefined keys.

application.properties

# Applicationn context name
server.contextPath=/springbootds

# Here 'test' is the database name
spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=java4s
spring.datasource.password=java4s
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

With this we have configured the datasource in our spring boot application.

3. Get JDBCTemplate object in your DAO with @Autowired annotation

Go to your DAO class and get the object of JdbcTemplate by @Autowired annotation and use it. Spring Boot will automatically get the datasource details from application.propeties and injects to jdbcTemplate object while auto wiring.

@Autowired
private JdbcTemplate jdbcTemplate;

Spring Boot JDBC + MySQL Example – How to Create/Configure a DataSource

Directory Structure

Required files

• pom.xml
• SpringBootApp.java
• SpringJava4sController.java
• SpringJava4sDAO.java
• Customer.java
• application.properties

pom.xml

<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.java4s</groupId>
  <artifactId>SpringBootDataSourceExample</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  	<parent>
	    <groupId>org.springframework.boot</groupId>
	    <artifactId>spring-boot-starter-parent</artifactId>
	    <version>1.5.6.RELEASE</version>
	</parent>
	
	<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
         </dependency>    
	    <dependency>
	        <groupId>org.springframework.boot</groupId>
	        <artifactId>spring-boot-starter-web</artifactId>
	    </dependency>
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		</dependency>
	</dependencies>
	
	<properties>
	 <java.version>1.8</java.version>
	</properties> 
  
</project>

SpringBootApp.java

package com.java4s.app;

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

@SpringBootApplication
public class SpringBootApp {
   public static void main(String[] args) {
     SpringApplication.run(SpringBootApp.class, args);
   }
}

SpringJava4sController.java

package com.java4s.app.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.java4s.app.repository.SpringJava4sDAO;
import com.java4s.model.Customer;

@RestController
public class SpringJava4sController {

    @Autowired
    public SpringJava4sDAO dao;

    @RequestMapping("/getcustInfo")
    public List<Customer> customerInformation() {
        List<Customer> customers = dao.isData(); 
        return customers;
    }
}

SpringJava4sDAO.java

package com.java4s.app.repository;

import java.util.ArrayList;
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.Repository;

import com.java4s.model.Customer;

@Repository
public class SpringJava4sDAO {

     @Autowired
     private JdbcTemplate jdbcTemplate;

     private static final String SQL = "select * from customers";

     public List<Customer> isData() {

          List<Customer> customers = new ArrayList<Customer>();
          List<Map<String, Object>> rows = jdbcTemplate.queryForList(SQL);

          for (Map<String, Object> row : rows) 
          {
               Customer customer = new Customer();
               customer.setCustNo((int)row.get("Cust_id"));
               customer.setCustName((String)row.get("Cust_name"));
               customer.setCountry((String)row.get("Country"));

               customers.add(customer);
           }

         return customers;
     }
}

Customer.java

package com.java4s.model;

public class Customer {

    private int custNo;
    private String custName;
    private String country;

    public Customer() {
    }

    public Customer(int custNumber, String custName, String country) {
        this.custNo = custNumber;
        this.custName = custName;
        this.country = country;
    }

    public int getCustNo() {
       return custNo;
    }

    public void setCustNo(int custNo) {
       this.custNo = custNo;
    }

    public String getCustName() {
       return custName;
    }

    public void setCustName(String custName) {
       this.custName = custName;
    }

    public String getCountry() {
       return country;
    }

    public void setCountry(String country) {
       this.country = country;
    }
}

application.properties

# Applicationn context name
server.contextPath=/springbootds

# Here 'test' is the database name
spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=java4s
spring.datasource.password=java4s
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Output

In the properties file I have mentioned contextPath for the application, so the application URL will be…

http://localhost:8080/springbootds/getcustInfo

You Might Also Like

• Spring Boot Configure DataSource Using JNDI with Example
• Spring Boot JDBC + MySQL – How to Configure Multiple DataSource
• Spring Resourcebundle Example
• Resource Bundle In Spring: Dynamically Loading The Values For Property Placeholders In XML
• Spring JdbcTemplate Update() Insert Query Example

::. About the Author .::
		Sivateja Kandula - Java/J2EE Full Stack Developer Founder of Java4s - Get It Yourself, A popular Java/J2EE Programming Blog, Love Java and UI frameworks. You can sign-up for the Email Newsletter for your daily dose of Java tutorials.