forked from bwajtr/java-persistence-frameworks-comparison
-
Notifications
You must be signed in to change notification settings - Fork 0
/
EBeanDataRepositoryImpl.java
309 lines (267 loc) · 12.4 KB
/
EBeanDataRepositoryImpl.java
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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
package com.clevergang.dbtests.repository.impl.ebean;
import com.avaje.ebean.Ebean;
import com.avaje.ebean.EbeanServer;
import com.avaje.ebean.SqlRow;
import com.clevergang.dbtests.repository.api.DataRepository;
import com.clevergang.dbtests.repository.api.data.*;
import com.clevergang.dbtests.repository.impl.ebean.entities.CompanyEntity;
import com.clevergang.dbtests.repository.impl.ebean.entities.DepartmentEntity;
import com.clevergang.dbtests.repository.impl.ebean.entities.EmployeeEntity;
import com.clevergang.dbtests.repository.impl.ebean.entities.ProjectEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.stream.Collectors;
/**
* EBean implementation of DataRepository interface...
*
* @author Bretislav Wajtr
*/
@Repository
public class EBeanDataRepositoryImpl implements DataRepository {
private final EbeanServer ebean;
@Autowired
public EBeanDataRepositoryImpl(EbeanServer ebean) {
this.ebean = ebean;
}
@Override
public Company findCompany(Integer pid) {
CompanyEntity companyEntity = ebean.find(CompanyEntity.class, pid);
Company result = new Company();
result.setPid(companyEntity.getPid());
result.setName(companyEntity.getName());
result.setAddress(companyEntity.getAddress());
return result;
}
@Override
public Company findCompanyUsingSimpleStaticStatement(Integer pid) {
// I didn't find a way how to setup EBean so the query is executed statically (using JDBC regular Statement
// not PreparedStatement). However, EBean is polite and provides way how to access the underlying JDBC connection
// -> therefore we can actually fall back to JDBC way of doing things (which is not such a big deal since we need simple
// Statements only rarely):
try {
String query = "SELECT pid, address, name " +
"FROM company " +
"WHERE pid = " + pid;
Connection connection = ebean.currentTransaction().getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
Company result = null;
if (resultSet.next()) {
result = new Company();
result.setPid(resultSet.getInt("pid"));
result.setAddress(resultSet.getString("address"));
result.setName(resultSet.getString("name"));
}
return result;
} catch (SQLException e) {
// just wrap checked exceptions as e
throw new RuntimeException(e);
}
}
@Override
public void removeProject(Integer pid) {
ebean.delete(ProjectEntity.class, pid);
}
@Override
public Department findDepartment(Integer pid) {
DepartmentEntity departmentEntity = ebean.find(DepartmentEntity.class, pid);
Department result = new Department();
result.setPid(departmentEntity.getPid());
result.setName(departmentEntity.getName());
result.setCompanyPid(departmentEntity.getCompanyPid());
return result;
}
@Override
public List<Department> findDepartmentsOfCompany(Company company) {
List<DepartmentEntity> departments = ebean
.find(DepartmentEntity.class)
.where()
.eq("companyPid", company.getPid())
.orderBy("pid")
.findList();
return departments.stream()
.map(entity -> {
Department department = new Department();
department.setPid(entity.getPid());
department.setName(entity.getName());
department.setCompanyPid(entity.getCompanyPid());
return department;
}).collect(Collectors.toList());
}
@Override
public void deleteDepartments(List<Department> departmentsToDelete) {
List<Integer> ids = departmentsToDelete.stream().map(Department::getPid).collect(Collectors.toList());
ebean.deleteAll(DepartmentEntity.class, ids);
}
@Override
public void updateDepartments(List<Department> departmentsToUpdate) {
List<DepartmentEntity> entities = departmentsToUpdate.stream()
.map(department -> {
DepartmentEntity entity = new DepartmentEntity();
entity.setPid(department.getPid());
entity.setName(department.getName());
entity.setCompanyPid(department.getCompanyPid());
return entity;
}).collect(Collectors.toList());
ebean.updateAll(entities);
}
@Override
public void insertDepartments(List<Department> departmentsToInsert) {
List<DepartmentEntity> entities = departmentsToInsert.stream()
.map(department -> {
DepartmentEntity entity = new DepartmentEntity();
entity.setName(department.getName());
entity.setCompanyPid(department.getCompanyPid());
return entity;
}).collect(Collectors.toList());
// Please see comment about batching in insertProjects() method
ebean.saveAll(entities);
}
@Override
public Project findProject(Integer pid) {
ProjectEntity projectEntity = ebean.find(ProjectEntity.class, pid);
Project result = new Project();
result.setPid(projectEntity.getPid());
result.setName(projectEntity.getName());
result.setDate(projectEntity.getDateStarted());
return result;
}
@Override
public Integer insertProject(Project project) {
ProjectEntity entity = new ProjectEntity();
entity.setName(project.getName());
entity.setDateStarted(project.getDate());
ebean.save(entity);
return entity.getPid();
}
@Override
public List<Integer> insertProjects(List<Project> projects) {
List<ProjectEntity> entities = projects.stream()
.map(project -> {
ProjectEntity entity = new ProjectEntity();
entity.setName(project.getName());
entity.setDateStarted(project.getDate());
return entity;
}).collect(Collectors.toList());
// Actually EBean handles batch inserts/updates in a very clever way. The default setting for batching is
// following:
// ebean.currentTransaction().getBatch() == PersistBatch.NONE
// ebean.currentTransaction().getBatchOnCascade() == PersistBatch.ALL
// so basically when you do single insert (ebean.save()) then batching is not used. But when
// use use ebean.saveAll, then value for BatchOnCascade is used for storing the value and in
// our case that's PersistBatch.ALL -> batching is used.
// That means, for default settings:
// ebean.save() --> no batching is used
// ebean.saveAll() --> batching is automatically used
// WHICH IS AWESOME!
ebean.saveAll(entities);
return entities.stream().map(ProjectEntity::getPid).collect(Collectors.toList());
}
@Override
public List<ProjectsWithCostsGreaterThanOutput> getProjectsWithCostsGreaterThan(int totalCostBoundary) {
String query;
query = "WITH project_info AS (\n" +
" SELECT project.pid project_pid, project.name project_name, salary monthly_cost, company.name company_name\n" +
" FROM project\n" +
" JOIN projectemployee ON project.pid = projectemployee.project_pid\n" +
" JOIN employee ON projectemployee.employee_pid = employee.pid\n" +
" LEFT JOIN department ON employee.department_pid = department.pid\n" +
" LEFT JOIN company ON department.company_pid = company.pid\n" +
"),\n" +
"project_cost AS (\n" +
" SELECT project_pid, sum(monthly_cost) total_cost\n" +
" FROM project_info GROUP BY project_pid\n" +
")\n" +
"SELECT project_name, total_cost, company_name, sum(monthly_cost) company_cost FROM project_info\n" +
" JOIN project_cost USING (project_pid)\n" +
"WHERE total_cost > :totalCostBoundary\n" +
"GROUP BY project_name, total_cost, company_name\n" +
"ORDER BY company_name";
return ebean.createSqlQuery(query)
.setParameter("totalCostBoundary", totalCostBoundary)
.findList()
.stream()
.map(row -> {
ProjectsWithCostsGreaterThanOutput output = new ProjectsWithCostsGreaterThanOutput();
output.setProjectName(row.getString("project_name"));
output.setTotalCost(row.getBigDecimal("total_cost"));
output.setCompanyName(row.getString("company_name"));
output.setCompanyCost(row.getBigDecimal("company_cost"));
return output;
})
.collect(Collectors.toList());
}
@Override
public Employee findEmployee(Integer pid) {
EmployeeEntity entity = ebean.find(EmployeeEntity.class, pid);
Employee result = new Employee();
result.setPid(entity.getPid());
result.setName(entity.getName());
result.setDepartmentPid(entity.getDepartmentPid());
result.setEmail(entity.getEmail());
result.setSalary(entity.getSalary());
result.setSurname(entity.getSurname());
return result;
}
@Override
public List<Employee> employeesWithSalaryGreaterThan(Integer minSalary) {
return ebean
.find(EmployeeEntity.class)
.where()
.gt("salary", minSalary)
.findList() // query executed here
.stream()
.map(entity -> {
Employee emp = new Employee();
emp.setPid(entity.getPid());
emp.setSurname(entity.getSurname());
emp.setName(entity.getName());
emp.setSalary(entity.getSalary());
emp.setEmail(entity.getEmail());
emp.setDepartmentPid(entity.getDepartmentPid());
return emp;
}).collect(Collectors.toList());
}
@Override
public void updateEmployee(Employee employeeToUpdate) {
EmployeeEntity entity = new EmployeeEntity();
entity.setPid(employeeToUpdate.getPid());
entity.setEmail(employeeToUpdate.getEmail());
entity.setName(employeeToUpdate.getName());
entity.setSurname(employeeToUpdate.getSurname());
entity.setSalary(employeeToUpdate.getSalary());
entity.setDepartmentPid(employeeToUpdate.getDepartmentPid());
ebean.update(entity);
}
@Override
public RegisterEmployeeOutput callRegisterEmployee(String name, String surname, String email, BigDecimal salary, String departmentName, String companyName) {
String query = "SELECT employee_id, department_id, company_id FROM register_employee(:name, :surname, :email, :salary, :departmentName, :companyName)";
SqlRow row = ebean.createSqlQuery(query)
.setParameter("name", name)
.setParameter("surname", surname)
.setParameter("email", email)
.setParameter("salary", salary)
.setParameter("departmentName", departmentName)
.setParameter("companyName", companyName)
.findUnique();
RegisterEmployeeOutput result = new RegisterEmployeeOutput();
result.setEmployeePid(row.getInteger("employee_id"));
result.setDepartmentPid(row.getInteger("department_id"));
result.setCompanyPid(row.getInteger("company_id"));
// this is required so the EBean caches are invalidated
Ebean.externalModification("department", true, false, false);
Ebean.externalModification("company", true, false, false);
Ebean.externalModification("employee", true, false, false);
return result;
}
@Override
public Integer getProjectsCount() {
return ebean.find(ProjectEntity.class).findCount();
}
}