Exposing Subset/View of the database with a JPA Repository over Rest
In this short article we are going to look at how we can expose a subset of data as a refined business model rather than a direct mapping of the database table.
A Quick HR Example
In our HR database we have 3 tables which each contain a plethora of information about employees, their salaries, departments etc.
We have a requirement to expose a subset of this data for a report. In the request we have been asked to expose the following:
- Employee ID
- Name
- Salary
- Job Title
- Department Name
Of course our JPA models contain the whole suite of fields and we only want to expose a subset.
@Subselect and Database Views to the rescue
To facilitate this we are going to expose only a subset of the data. This in traditional database terms is a View. Views are like tables but they are a presentation of data obtained through a query, normally across multiple tables.
If you are not familiar with the concept of a View and why one might be used I would suggest you immediately look into it.
Spring Data JPA Entities have a very handy Annotation which can be added, it allows us to embed “views” into our code.
This should not be considered good practice, database design is not a subjecet for this post. However it will have its time and place and it can be much cleaner than data mutating classes, factories or marshallers.
Query
JPA Entity
import org.hibernate.annotations.Immutable
import org.hibernate.annotations.Subselectimport javax.persistence.Column
import javax.persistence.Entity
import javax.persistence.Id@Entity
@Immutable
@Subselect("" +
"SELECT e.employee_id,\n" +
" concat(e.first_name, ' ', e.last_name) as name,\n" +
" e.salary,\n" +
" j.job_title,\n" +
" d.department_name\n" +
"FROM employees e\n" +
" JOIN departments d on e.department_id = d.department_id\n" +
" JOIN jobs j on e.job_id = j.job_id")
class EmployeeSalary { @Id
Integer employeeId @Column
String name @Column
BigDecimal salary @Column
String jobTitle @Column
String departmentName}
Now that we have our JPA entity mapped to a view we can expose the data over a REST API by creating a standard JpaRepsoitory interface and annotating it with @RestResource.
import net.fxqlabs.examples.hr.rows.EmployeeSalary
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.rest.core.annotation.RestResource@RestResource
interface EmployeeSalaryRepository extends JpaRepository<EmployeeSalary, Integer>{
}
Visiting the root of our rest api we can see the new repository exposes the data:
http://localhost:8080/
{
"_links" : {
"employees" : {
"href" : "http://localhost:8080/employees"
},
"departments" : {
"href" : "http://localhost:8080/departments{?page,size,sort}",
"templated" : true
},
"employeeSalaries" : {
"href" : "http://localhost:8080/employeeSalaries{?page,size,sort}",
"templated" : true
},
"jobs" : {
"href" : "http://localhost:8080/jobs{?page,size,sort}",
"templated" : true
},
"profile" : {
"href" : "http://localhost:8080/profile"
}
}
}Visiting the endpoint we can see we have a fully HATEOAS compliant rest api exposing the exact data we need.
http://localhost:8080/employeeSalaries?size=5{
"_embedded" : {
"employeeSalaries" : [ {
"name" : "Steven King",
"salary" : 24000.00,
"jobTitle" : "President",
"departmentName" : "Executive",
"_links" : {
"self" : {
"href" : "http://localhost:8080/employeeSalaries/100"
},
"employeeSalary" : {
"href" : "http://localhost:8080/employeeSalaries/100"
}
}
}, {
"name" : "Neena Kochhar",
"salary" : 17000.00,
"jobTitle" : "Administration Vice President",
"departmentName" : "Executive",
"_links" : {
"self" : {
"href" : "http://localhost:8080/employeeSalaries/101"
},
"employeeSalary" : {
"href" : "http://localhost:8080/employeeSalaries/101"
}
}
}, {
"name" : "Lex De Haan",
"salary" : 17000.00,
"jobTitle" : "Administration Vice President",
"departmentName" : "Executive",
"_links" : {
"self" : {
"href" : "http://localhost:8080/employeeSalaries/102"
},
"employeeSalary" : {
"href" : "http://localhost:8080/employeeSalaries/102"
}
}
}, {
"name" : "Alexander Hunold",
"salary" : 9000.00,
"jobTitle" : "Programmer",
"departmentName" : "IT",
"_links" : {
"self" : {
"href" : "http://localhost:8080/employeeSalaries/103"
},
"employeeSalary" : {
"href" : "http://localhost:8080/employeeSalaries/103"
}
}
}, {
"name" : "Bruce Ernst",
"salary" : 6000.00,
"jobTitle" : "Programmer",
"departmentName" : "IT",
"_links" : {
"self" : {
"href" : "http://localhost:8080/employeeSalaries/104"
},
"employeeSalary" : {
"href" : "http://localhost:8080/employeeSalaries/104"
}
}
} ]
},
"_links" : {
"first" : {
"href" : "http://localhost:8080/employeeSalaries?page=0&size=5"
},
"self" : {
"href" : "http://localhost:8080/employeeSalaries{&sort}",
"templated" : true
},
"next" : {
"href" : "http://localhost:8080/employeeSalaries?page=1&size=5"
},
"last" : {
"href" : "http://localhost:8080/employeeSalaries?page=7&size=5"
},
"profile" : {
"href" : "http://localhost:8080/profile/employeeSalaries"
}
},
"page" : {
"size" : 5,
"totalElements" : 40,
"totalPages" : 8,
"number" : 0
}
}
We can even visit specific ID’s using the usual conventions, all this without a single line of implementation code!
http://localhost:8080/employeeSalaries/101?size=5{
"name" : "Neena Kochhar",
"salary" : 17000.00,
"jobTitle" : "Administration Vice President",
"departmentName" : "Executive",
"_links" : {
"self" : {
"href" : "http://localhost:8080/employeeSalaries/101"
},
"employeeSalary" : {
"href" : "http://localhost:8080/employeeSalaries/101"
}
}
}
Should you have full control of the database rather than just an application specific requirement creating an actual view is much better than embedded SQL. This view for instance could live in the HR Portal schema to keep it away from our database domain, or it might exist in the HR schema but have limited select access so the application can only read these specific fields thus preventing unauthorised access to more sensitive information.
Create a view representing our needs:
CREATE OR REPLACE VIEW employee_salaries as
SELECT e.employee_id,
concat(e.first_name, ' ', e.last_name) as name,
e.salary,
j.job_title,
d.department_name
FROM employees e
JOIN departments d on e.department_id = d.department_id
JOIN jobs j on e.job_id = j.job_id;Change our JPA entity annotations to @Table rather than @Subselect and reference the new view as the table name and your all done.
import org.hibernate.annotations.Immutableimport javax.persistence.Column
import javax.persistence.Entity
import javax.persistence.Id
import javax.persistence.Table@Entity
@Immutable
@Table(name = "employee_salaries")
class EmployeeSalary { @Id
Integer employeeId @Column
String name @Column
BigDecimal salary @Column
String jobTitle @Column
String departmentName}
In this short article we discussed the power and simplicity of JPA entities and how they don’t always need to be managed tables, we saw how we can expose a fully HATEOAS compliant JpaRepository with nothing more than a simple SQL query, a POJO and an interface.
We saw how we can use this technique to expose information over an API so that our data consumers can see just the right amount of information to meet their requirements.
Don’t just expose your database as a 1 to 1, despite just how tempting this can be with ORM frameworks that make beautiful rest API’s, someone somewhere is going to have to fold that data into a usable format and leaving until you code your frontend can be a painful mistake.
