#2 Simple Spring Rest Api with JDBC connection

Before starting this tutorial, I am assuming that you guys have basic idea of Spring. If you don't have basic idea of simple Spring API then no worry :), I have written simple tutorial here to start with Spring Boot.

This tutorial is next version of my first tutorial. In my first tutorial I explained that how can we make a simple REST api with Spring Boot. 

So lets start with JDBC connection in some very simple steps:

1. Create DB schema and table

I prefer MySqlWorkBench for this purpose ( you can use any way to create schema). For this tutorial I have named my db as test_db. This DB contains one Employee table with below schema:

Table contains id, name and email of employee ( This is just for tutorial, You make table as per your requirement).

Once you created table, Insert some dummy data for your REST api. Your api will be fetching this data and will return it as JSON. I have made some of dummy entries as below:

2. Add Maven dependencies

Spring has provided some DB wrapper which made it very easy to access MySql database. To use spring features, add following dependencies in your POM.XML file:

1. JDBC
2. MySQL Connector
3. JPA

You can add all these dependencies while creating project from start.spring.io OR you can add them manually as well.

After adding these just build Maven.

3. Define DB for your application

Now its time to let your application know that which DB need to be accessed for REST api. To define DB for your app, open application.properties file and define DB path as given below:

Please note that I don't have given any password for my DB while creating it,  So I have commented line for username and password to access DB. BY default DB is created at PORT 3306.

4. Its coding time now :)

So lets start coding part to write API for accessing Employee table. Follow steps below for the same:

First create DAO class  named EmployeeDao ( its nothing just an interface ) as given below:

This contains method to list all employees from table. So now what we need to do with this interface??

Lets create another class to implement this interface. Let keep the name as EmployeeDaoImpl. This class should look like below image:

You can see that I have override the interface method in this class.

Wait, I have used something anonymous here, Its JdbcTemplate. What is it? Its a spring framework class, used to access DB data. It has its own predefined callback methods. You can get more details here.

Again, why I have written @Autowired in code. Actually this is a spring annotation, If we are writing this on top of any object, It means that object will be initialized only once at the time of tomcat start. So I have added it for JdbcTemplate setter method.

One more annotation used @Repository on top of class name. We have use this because we are using JdbcTemplate here which is a spring framework class, so for calling this class method we will require @Autowired Object for accessing JdbcTemplete class properties. And we can make @Autowired object of class with the help of @Repository.

Now let come back to code, its simple query which is executed via JdbcTemplate class method, no argument passed. Method fetch all employees, assign then to Employee bean.

Here is template for Employee bean:

Now lets come to controller class and finally write the api:

I just simply call listAllEmployee method of interface and it return me the complete list of employees in List form and I just return same list. This is spring intellengency that it will auto convert Employee object to JSON when fetching from client. 

So now its time to see output, Run your application and call api on browser:

Time to cheers now, we have made our REST api with DB connection.

I will be posting next steps for advance level programming in Spring. So please be connected and enjoy programming :)