Spring boot: Database connectivity to MySQL using Hibernate + Web

· by · in · Leave a response

Hello friends,
In this post we will see how to connect database using boot, for this demo will use MySQL as a database and Hibernate as an ORM.

For this example we need “Web” and “JPA” dependency in our project.

Add below dependency in your pom.xml

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>

Note: artifactId spring-boot-starter-data-jpa will internally provides support and download dependency for spring-orm, hibernate-entity-manager and spring-data-jpa

or download fully configured project using url below.

http://start.spring.io/starter.zip
?name=db-mysql-jpa
&groupId=com.kode12
&artifactId=db-mysql-jpa
&version=0.0.1-SNAPSHOT
&description=Project+to+show+database+connectivity.
&packageName=com.kode12.dbmysqljpa
&type=maven-project
&packaging=war
&javaVersion=1.7
&language=java
&bootVersion=1.3.3.RELEASE
&dependencies=data-jpa
&dependencies=web

After downloading and importing project in to your editor you will find one java file DbMysqlJpaApplication.java with main method. We are using this class to start server.

DbMysqlJpaApplication.java

Code
package com.kode12.dbmysqljpa;

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

@SpringBootApplication
public class DbMysqlJpaApplication {

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

After that i created one controller in controllers package, one model class in models package and one service interface in services package.

Find project structure in below screenshot.

Now lets see all files with code.

application.properties

Code
server.port=8081

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

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

This file contains some new properties comparing to previous posts.
spring.datasource.url is used to provide database connection url.
spring.datasource.username is used to provide database user name.
spring.datasource.password is used to provide database password.

spring.jpa.hibernate.ddl-auto is used to provide what hibernate needs to do on ddl change, update meand it will update database schema if model changed.
spring.jpa.show-sql is used to toggle SQL output inn console, true means hibernate will log all queries executed by in console.

Employee.java

Code
package com.kode12.dbmysqljpa.models;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Employee implements Serializable {

	@Id
	@GeneratedValue
	private Long id;

	private String name;

	public Employee() {

	}

	public Employee(String name) {
		super();
		this.name = name;
	}

	public Long getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "Employee [id=" + getId() + ", name=" + getName() + "]";
	}

}

This is a Pojo class is used as hibernate entity as we annotated by @Entity.

EmployeeService.java

Code
package com.kode12.dbmysqljpa.services;

import org.springframework.data.repository.CrudRepository;

import com.kode12.dbmysqljpa.models.Employee;

public interface EmployeeService extends CrudRepository<Employee, Long> {

}

Oops !, nothing is written in this file.
You are right reader, here we just create one interface and extended another interface called CrudRepository with Parameter Employee as an type of entity and Long as an type of ID field.

Technically CrudRepository extends Repository interface.

public interface CrudRepository<T,ID extends Serializable>
extends Repository<T,ID>

Boot will allow to do some basic CRUD (Create, Read, Update and Delete) operation without any implementations using CrudRepository interface.

Basic operation like save, delete, count, findAll, findById, exist, etc…

Employeecontroller.java

Code
package com.kode12.dbmysqljpa.controllers;

import java.util.List;

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

import com.kode12.dbmysqljpa.models.Employee;
import com.kode12.dbmysqljpa.services.EmployeeService;

@RestController
@RequestMapping("/employee")
public class EmployeeController {

	@Autowired
	EmployeeService employeeService;

	@RequestMapping("/getAll")
	public List<Employee> getAll() {
		return (List<Employee>) employeeService.findAll();
	}

	@RequestMapping("/getById/{id}")
	public Employee getbyId(@PathVariable("id") Long id) {
		return employeeService.findOne(id);
	}

	@RequestMapping("/count")
	public Long count() {
		return employeeService.count();
	}
}

In this controller we used three methods from CrudRepository.
Here, we just Autowired EmployeeService interface and used findAll(), findOne(Long id) and count() method to perform operations.

Run Project and Output

To run project execute main method from class DbMysqlJpaApplication.java.

Insert sample data

Use below script to insert sample data in Employee table.

INSERT INTO `employee` (`name`) VALUES ('Yogesh');
INSERT INTO `employee` (`name`) VALUES ('Vishal');
INSERT INTO `employee` (`name`) VALUES ('Aarush');
Invoke getAll endpoint

Use http://localhost:8081/employee/getAll to invoke getAll endpoint.

Output

Query by hibernate
Hibernate: select employee0_.id as id1_0_, employee0_.name as name2_0_ from employee employee0_

It will return 3 records in JSON format.

Invoke getById endpoint

Use http://localhost:8081/employee/getById/3 to invoke getById endpoint.

Output

Query by hibernate
Hibernate: select employee0_.id as id1_0_0_, employee0_.name as name2_0_0_ from employee employee0_ where employee0_.id=?

It will return 3rd record where name=Aarush available as we passed value 3 in path.

Invoke count endpoint

Use http://localhost:8081/employee/count to invoke count endpoint.

Output

Query by hibernate
Hibernate: select count(*) as col_0_0_ from employee employee0_

It will return total no of records available in database.

At Last, i would like to hear from you guys for some mistakes, new blogs, demo, ideas or anything which will help us to make our blog better.

Share current post by copy: http://goo.gl/R7cMbS

:)

Tags: , , , , , ,