Spring boot + Spring JDBC (English version)


In this post we will explain how to access to a database by using Spring boot + Spring JDBC step by step. For this we will use the project Spring Boot + REST Jersey Part 1 as base.

Step 1 : Configure the required dependencies

The first step will be add the required dependencies to the project, in this case we will use 2 spring-boot-starter-jdbc and mysql-connector-java.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
  • spring-boot-starter-jdbc : Contains all the necessary classes that we will use to enable spring jdbc.
  • mysql-connector-java: Contains the mysql driver to connect via JDBC.

Step 2: Create the tables we will use in the database

For this example we will use MySQL as database engine and we will create a database named jdbc_example with the following table:

CREATE TABLE USER(
USER_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
USERNAME VARCHAR(100) NOT NULL,
PASSWORD VARCHAR(100) NOT NULL
);

And with the following data:

INSERT INTO USER (USERNAME,PASSWORD)VALUES('raidentrance','superSecret');
INSERT INTO USER (USERNAME,PASSWORD)VALUES('john','smith');
INSERT INTO USER (USERNAME,PASSWORD)VALUES('juan','hola123');

Step 3: Create the class to represent a User

Now we will create a POJO to represent the information stored in the User table.

/**
 *
 */
package com.raidentrance.model;

/**
 * @author raidentrance
 *
 */
public class User {
    private Integer id;
    private String user;
    private String password;

    public User() {
    }

    public User(Integer id, String user, String password) {
        super();
        this.id = id;
        this.user = user;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

Step 4: Add the database configuration to the project

The next step is to include the database configuration to the project, in order to do it we will edit the file application.properties with the following information:

spring.datasource.url=jdbc:mysql://localhost:3306/jdbc_example
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Step 5 : Create a DAO (Data access object)

Once Spring has the information to connect to the database the next step is create a data access object, it will be used to execute operations over the User table in the database:

/**
 *
 */
package com.raidentrance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.ws.rs.core.Response.Status;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.raidentrance.model.ServiceException;
import com.raidentrance.model.User;

/**
 * @author raidentrance
 *
 */
@Component
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<User> findAll() {
        List<User> users = jdbcTemplate.query("select * from user", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int arg1) throws SQLException {
                User user = new User(rs.getInt("USER_ID"), rs.getString("USERNAME"), rs.getString("PASSWORD"));
                return user;
            }
        });
        return users;
    }

    public User findByUsername(String username) throws ServiceException {
        User user = jdbcTemplate.query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement("select * from user where username=?");
                ps.setString(1, username);
                return ps;
            }
        }, new ResultSetExtractor<User>() {
            @Override
            public User extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    User user = new User(rs.getInt("USER_ID"), rs.getString("USERNAME"), rs.getString("PASSWORD"));
                    return user;
                } else {
                    return null;
                }
            }
        });
        if (user != null) {
            return user;
        } else {
            throw new ServiceException(Status.NOT_FOUND.getStatusCode(), "User not found ", 4004);
        }
    }
}

In the previous code we can see the following points :

  • the @Componen annotation: It means that the object will be living in the spring context and we can access to the instance by using the @Autowired annotation.
  • @Autowired JdbcTemplate jdbcTemplate : The JdbcTemplate will use the configuration that we establish in the application.properties file to connect to our database. We use @Autowired to get a reference to the object that is living in the Spring context.
  • public List findAll() : This method will be used to get all the users in the table. As you can see this method receives the sql query that we want to execute and an object that implements the RowMapper interface, this object will be used to transform from a ResultSet to a Java list.
  • public User findByUsername(String username): The method findByUsername will be used to get a user by username. In this example we are using a PreparedStatement to prevent SQL Injection because this sql query receives a parameter. Other important difference is that this method is receiving a ResultSetExtractor instead a RowMapper and the reason to do it is because this method will return a single object in the response.
  • The last point is that we can see that in case that we cant find a user by its username we will throw a ServiceException with a message, code and http status.

Step 6: Using the DAO in our web service

Once we have a DAO created we have to use it in our endpoint, in future posts we will see that is a good practice to separate this logic in a separated service, but for now we will inject the DAO directly in the endpoint UserResource as follow:

/**
 *
 */
package com.raidentrance.resource;

import javax.ws.rs.Consumes;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.raidentrance.dao.UserDao;
import com.raidentrance.model.ServiceException;

/**
 * @author raidentrance
 *
 */

@Component
@Path("/users")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public class UserResource {

    @Autowired
    private UserDao userDao;

    private static final Logger log = LoggerFactory.getLogger(UserResource.class);

    @GET
    public Response getUsers() {
        log.info("Getting users");
        return Response.ok(userDao.findAll()).build();
    }

    @GET
    @Path("/user/{username}")
    public Response getUser(@PathParam("username")String username) throws ServiceException {
        log.info("Getting users");
        return Response.ok(userDao.findByUsername(username)).build();
    }

}

As you can see, to inject the DAO we just need to use the annotation @Autowired because the object lives in the spring context.

Step 7: Testing all together

To execute the application we have to execute the main class as in all the Spring boot applications and access to the following url http://localhost:8080/users, we can see the following output:

Captura de pantalla 2017-09-18 a las 2.20.01 p.m.

If we want to get a single user by using the username we will use the url http://localhost:8080/users/user/raidentrance and it will show the following output:

Captura de pantalla 2017-09-18 a las 2.21.40 p.m.

You can find the complete code in the url https://github.com/raidentrance/spring-boot-example/tree/part6-spring-jdbc .

And if you want to learn more about Spring boot and web services we recommend the following books:

Also you can find version in Spanish of this post here.

Autor: Alejandro Agapito Bautista

Twitter: @raidentrance

Contacto:raidentrance@gmail.com

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s