Spring boot: Database connectivity to MySQL using Hibernate + Web
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
|
1 2 3 4 5 6 7 8 9 |
<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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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.
|
1 2 3 |
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
|
1 |
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
|
1 |
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
|
1 |
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
