Newsletter

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

Spring-Boot-Tutorials » on Jan 14, 2018 { 16 Comments } By Sivateja

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

  ::. About the Author .::

Java4s_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.

Comments

16 Responses to “Spring Boot JDBC + MySQL – How to Create/Configure a DataSource”
  1. Chetan says:

    Hi Sir,
    Thanks a lot! Nice explanation for beginners to start on spring boot.
    Could you please cover some more topics on same.

  2. surya says:

    Hi,

    Thanks for nice Explanation, in the above example we have used JdbcTemplate so how jdbc template read connection properties is internally reads from application.properties with dedicated key names? thanks!

  3. Ratan says:

    Hi,

    Can you please give any example which use ENV for database connection?

    Like in application.properties you put

    spring.datasource.url=jdbc:mysql://localhost/test
    spring.datasource.username=java4s
    spring.datasource.password=java4s

    But I want to override this configuration, so I want my own IP address from system env, export DB_HOST=x.x.x.x:3306.
    So when MySQL connection init at that time it take this DB_HOST on place of localhost.

    Thanks in advance.

  4. Sri Harsha says:

    Thank you so much sir very detailed explanation can you please Better to maintain a youtube channel for spring boot videos You will be the best

  5. Rishikant Sharma says:

    What an excellent explanation by example. Keep up the great work!

  6. Mandar says:

    Hello Sivateja Kandula,
    Excellent Tutorial !
    I have few basic questions:
    1) what is the prerequisite for this example? I mean should database already have table named "customer" in it?
    2) Isn't here any need to create a class to link to the database table "customer" ? @table(named)
    3) How to handle exception failures?

    Thanks in advance for your return.

  7. Gurubasamma says:

    Hello Sivateja Kandula,
    Excellent Tutorial !!!!!!!

  8. RAMAKRISHNA RAO says:

    Hi Sivateja Kandula,

    I made a maven project on eclipse ide using based on these steps but I am facing an error that reads:
    Error:
    Parameter 0 of constructor in com……dao required a bean of type 'org.springframework.jdbc.datasource.DriverManagerDataSource' that could not be found.
    Action:
    Consider defining a bean of type 'org.springframework.jdbc.datasource.DriverManagerDataSource' in your configuration.

    PLEASE HELP !

  9. Annie says:

    Hi,

    I am getting the below error while trying to connect to a datasource. Can you please help with this

    Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

  10. Srinivas says:

    Sir after hitting http://localhost:8080/springbootds/getcustInfo i am getting below error so plz advice

    Whitelabel Error Page
    This application has no explicit mapping for /error, so you are seeing this as a fallback.

    Mon Mar 18 21:10:21 IST 2019
    There was an unexpected error (type=Not Found, status=404).
    No message available

  11. Kn says:

    @Srinivas,
    Remove @ComponentScan(basePackages).. from SpringBootApp and keep all the packages under com.java4s.app.
    Even model should be under com.java4s.app.

  12. Murali says:

    Hi

    I am getting the following error. can any one help
    ***************************
    APPLICATION FAILED TO START
    ***************************

    Description:

    Field jdbcTemplate in com.sispl.msa.application.repository.UserDAO required a bean of type 'org.springframework.jdbc.core.JdbcTemplate' that could not be found.

    The injection point has the following annotations:
    – @org.springframework.beans.factory.annotation.Autowired(required=true)

    Action:

    Consider defining a bean of type 'org.springframework.jdbc.core.JdbcTemplate' in your configuration.

  13. Avijit says:

    Till now it seem very easy. But how the hell are you saying that this jdbc tutorial with spring boot is easy. From which angle are you saying this jdbc tutorial is easy?? Its becoming very complex here.

    You should have elaborated more…

  14. sandeep says:

    Customer customer = new Customer();
    customer.setCustNo((int)row.get("Cust_id"));

    Here i got error like cannot cast from object to int how to solve this error

Name*
Mail*
Website



By posting your answer, you agree to our comments policy.
Most Recent Posts from Top Categories
Spring Boot Hibernate Spring
Contact | About Us | Privacy Policy | Advertise With Us

© 2010 - 2024 Java4s - Get It Yourself.
The content is copyrighted to Sivateja Kandula and may not be reproduced on other websites.