Spring Boot JPA Integration with SQLite | DB Browser | GitHub Example

JPA Integration with SQLite | DB Browser | GitHub Example

In the previous blog, we implemented CRUD operations for the employee management service, and we saw how to validate our request parameters using hibernate annotation and custom validation using spring boot annotations.

We have not yet used a database to store employee information.

In today's blog, we will learn how to create and connect to an SQLite database in spring boot. 

Using the DB browser tool, let's create the database first.

We do not need to write any SQL query for table creation or insertion in the DB Browser; just click a few buttons and our database with the table and data is ready.

CREATE TABLE employee (
  "id" INTEGER, 
  "name" TEXT NOT NULL, 
  "jobTitle" TEXT NOT NULL, 
  "mobileNo" INTEGER NOT NULL, 
  "joinedDate" TEXT NOT NULL, 
  "gender" TEXT NOT NULL, 
  "salary" NUMERIC NOT NULL, 
  "departmentid" INTEGER, 
  "projectid" INTEGER, 
  PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE project (
  "projectid" INTEGER, 
  "name" INTEGER NOT NULL, 
  PRIMARY KEY("projectid" AUTOINCREMENT)
);

CREATE TABLE department (
  "departmentid" INTEGER, 
  "name" TEXT, 
  PRIMARY KEY("departmentid" AUTOINCREMENT)
);

Add maven dependencies 

Add below dependencies in the pom.xml file.

		
<!-- JPA -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- SQLITE Database library -->
<dependency>
	<groupId>com.zsoltfabok</groupId>
	<artifactId>sqlite-dialect</artifactId>
	<version>1.0</version>
</dependency>
<dependency>
	<groupId>org.xerial</groupId>
	<artifactId>sqlite-jdbc</artifactId>
	<version>3.25.2</version>
</dependency>

Add Database source URL

We have to add the below properties in the application. properties.

#Database
spring.datasource.url=jdbc:sqlite:employeeDB.db

Spring.datasource.url defines the URL or location of our database here we have created and placed our employeeDB database in the project root.

Create an entity class

We have created three entity classes.

• @Entity annotation indicates that a class can be mapped to a table.
• @Table annotation specifies which database table contains the entity. If not used, Hibernate uses the class name as the table name.
• @Id defines the identifier for this entity which can be a primary key
• @Column specifies the table column for the field if not specified then the field (variable) name will be used by default.

import java.io.Serializable;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

@Entity
@Table(name = "employee")
public class Employee implements Serializable {

	private static final long serialVersionUID = 1 L;

	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "name")
	private String name;

	@Column(name = "jobTitle")
	private String jobTitle;

	@Column(name = "mobileNo")
	private String mobileNo;

	@Column(name = "joinedDate")
	private String joinedDate;

	@Column(name = "gender")
	private String gender;

	@Column(name = "salary")
	private Integer salary;

	@OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	@JoinColumn(name = "departmentid", referencedColumnName = "departmentid", insertable = true, updatable = true)
	private Department department;

	@OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	@JoinColumn(name = "projectid", referencedColumnName = "projectid", insertable = true, updatable = true)
	private Project project;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

	public String getJobTitle() {
		return jobTitle;
	}

	public void setJobTitle(String jobTitle) {
		this.jobTitle = jobTitle;
	}

	public String getMobileNo() {
		return mobileNo;
	}

	public void setMobileNo(String mobileNo) {
		this.mobileNo = mobileNo;
	}

	public String getJoinedDate() {
		return joinedDate;
	}

	public void setJoinedDate(String joinedDate) {
		this.joinedDate = joinedDate;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public Integer getSalary() {
		return salary;
	}

	public void setSalary(Integer salary) {
		this.salary = salary;
	}

	public Department getDepartment() {
		return department;
	}

	public void setDepartment(Department department) {
		this.department = department;
	}

	public Project getProject() {
		return project;
	}

	public void setProject(Project project) {
		this.project = project;
	}
}

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "department")
public class Department implements Serializable {

	private static final long serialVersionUID = 1 L;

	@Id
	@Column(name = "departmentid")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private String id;

	@Column(name = "name")
	private String name;

	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "project")
public class Project implements Serializable {

	private static final long serialVersionUID = 1 L;

	@Id
	@Column(name = "projectid")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private String id;

	@Column(name = "name")
	private String name;

	public String getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

Create Repository class 

Let create a repository class that will interact with the database and handle all operations related to database tables.

Our class should extend the JpaRepository interface which contains pre-defined JPA methods to insert, delete, update, fetch data from the database. 

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.employee.example.model.Employee;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
}

If you face the below exception 

Caused by: org.hibernate.AnnotationException: No identifier specified for entity: 

It mean you have missed to add @id annotation in entity class.

Spring Boot Application

Let test our spring boot application and check whether the database connection is working purposely.

Saving employee into database

Successfully data saved in the database.

Read data from database

View source code is available in Github