Java working with databases
Table of Contents
Introduction
In this Post I will describe how you can easily learn, test and play around with Java interacting with SQL Databases. This post is a summary of my Java learning course I'm taking on udemy. The post is not intended to be spread around the world, since the contents are not my own ideas, its only just for my own sake of summarizing and writing down what I'm learning.
As working environment we will use the SQuirreL SQL Client and create a local H2 Database with it. This allows us to start very quick and skip the hole SQL Server overhead, which is just to learn Java with Databases not relevant so far. You also need to download the H2 driver to work with this.
SQL Queries
Create Table
First of all we need to create our initial SQL Table to work with later on. You can use this SQL Query:
CREATE TABLE PEOPLE(ID BIGINT AUTO_INCREMENT, FIRST_NAME VARCHAR(255), LAST_NAME VARCHAR(255), DOB TIMESTAMP, SALARY NUMERIC);let's add a test record:
INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB, SALARY) VALUES('Harry', 'Johnson', '1950-03-15 10:45:10', 100000.00);let's verify the record by querying all records in the table people:
SELECT * FROM PEOPLE;output:
ID FIRST_NAME LAST_NAME DOB SALARY
-- ---------- --------- --------------------- ------
1 Harry Johnson 1950-03-15 10:45:10.0 100000great, this looks good so far. Let's add some more records:
INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB, SALARY) VALUES('Jack', 'Johnson', '2000-01-10 8:45:10', 50000.00);
INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB, SALARY) VALUES('Mary', 'Johnson', '2005-05-13 17:30:10', 20000.00);
INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB, SALARY) VALUES('Susan', 'Johnson', '1951-10-31 19:13:43', 200000.00);
INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB, SALARY) VALUES('Jake', 'Smith', '1970-10-31 19:13:43', 75000.00);Let's play around with some SQL statements. This are only a few SQL example queries:
SELECT * FROM PEOPLE WHERE FIRST_NAME LIKE 'J%';gives us back all records with firstname J*.
SELECT * FROM PEOPLE WHERE SALARY > 99000;gives all records where salary is bigger than 99000
SELECT * FROM PEOPLE WHERE DOB < DATE '1980-01-01' AND FIRST_NAME LIKE 'H%';gives all records where dateofbirth is before 1980-01-01 and firstname starts with H*.
SELECT * FROM PEOPLE ORDER BY FIRST_NAME;gives back all records sort by firstname
SELECT * FROM PEOPLE ORDER BY DOB ASC, FIRST_NAME DESC;gives back all records, sort by dateofbirth (ascending) and sort by firstname (descending)
SELECT SUM(SALARY), LAST_NAME FROM PEOPLE GROUP BY LAST_NAME;calculates the summary of all records grouped by lastname.
Function calls
We can go one step further and also use SQL function calls like these:
SELECT LAST_NAME, COUNT(*) FROM PEOPLE GROUP BY LAST_NAME;output:
LAST_NAME COUNT(*)
--------- --------
Johnson 4
Smith 2It's also possible to display combined table rows in the output of a sql query:
SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME) AS NAME, DOB FROM PEOPLE;output:
NAME DOB
-------------- ---------------------
Johnson, Harry 1950-03-15 10:45:10.0
Smith, Jake 1970-10-31 19:13:43.0
Johnson, Jack 2000-01-10 08:45:10.0
Johnson, Mary 2005-05-13 17:30:10.0
Johnson, Susan 1951-10-31 19:13:43.0
Smith, Jake 1970-10-31 19:13:43.0or we can manipulate data, before we display the sql query result, without modifying the real db data:
SELECT CONCAT(UPPER(LAST_NAME), ', ', FIRST_NAME) AS NAME, DATEADD(YEAR, 10, DOB) FROM PEOPLE;output:
NAME DATEADD(YEAR, 10, DOB)
-------------- ----------------------
JOHNSON, Harry 1960-03-15 10:45:10.0
SMITH, Jake 1980-10-31 19:13:43.0
JOHNSON, Jack 2010-01-10 08:45:10.0
JOHNSON, Mary 2015-05-13 17:30:10.0
JOHNSON, Susan 1961-10-31 19:13:43.0
SMITH, Jake 1980-10-31 19:13:43.0Regex
If all of this is not enough, you cold even use regex with sql queries:
SELECT REGEXP_REPLACE('1970-10-31', '\d{4}-', 'aaaa-');output:
'aaaa-10-31'
------------
aaaa-10-31 Modify Data (CRUD)
If we need to change existing data, we can for example use the update query:
UPDATE PEOPLE SET FIRST_NAME='Sabrina' WHERE FIRST_NAME='Susan';To remove a record we can use the delete query:
DELETE FROM PEOPLE WHERE LAST_NAME='Smith';We can also change the structure of a table. Let's say we want to add a Column:
ALTER TABLE PEOPLE ADD COLUMN EMAIL VARCHAR(255);The table looks like this now:
ID FIRST_NAME LAST_NAME DOB SALARY EMAIL
-- ---------- --------- --------------------- ------ ------
12 Harry Jonson 1950-03-15 10:45:10.0 100000 <null>
14 Jack Jonson 2000-01-10 08:45:10.0 50000 <null>
15 Mary Jonson 2005-05-13 17:30:10.0 20000 <null>
16 Sabrina Jonson 1951-10-31 19:13:43.0 200000 <null>Lets fill the <null> EMAIL values with a placeholder address:
UPDATE PEOPLE SET EMAIL='[email protected]';result:
ID FIRST_NAME LAST_NAME DOB SALARY EMAIL
-- ---------- --------- --------------------- ------ -------------------
12 Harry Jonson 1950-03-15 10:45:10.0 100000 [email protected]
14 Jack Jonson 2000-01-10 08:45:10.0 50000 [email protected]
15 Mary Jonson 2005-05-13 17:30:10.0 20000 [email protected]
16 Sabrina Jonson 1951-10-31 19:13:43.0 200000 [email protected]Interact with a database with Java
So far so good, but how can we use Java to Create and manipulate a SQL Database? First we will create a new gradle Project in IntellJ IDEA. To add the H2 dependency you can open the build.gradle file and open the dependencie Manager with the shortcut command + n on mac. Search for h2 and add the H2 Database Engine:
Lets do our development with the TDD method and add another dependency for this. Search for Assertj and add the AssertJ fluent assertions:
Make sure to change the newly added AssertJ dependency line in build.gradle from
implementation 'org.assertj:assertj-core:3.23.1'
to
testImplementation 'org.assertj:assertj-core:3.23.1'
Now Lets add a TDD TestClass as well as a main Class:
PeopleRepositoryTests.java:
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.model.Person;
import org.junit.jupiter.api.Test;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import static org.assertj.core.api.Assertions.assertThat;
public class PeopleRepositoryTests {
@Test
public void canSave() {
PeopleRepository repo = new PeopleRepository();
Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
Person savedPerson = repo.save(john);
assertThat(savedPerson.getId()).isGreaterThan(0);
}
}
model/Person.java:
package ch.finecloud.peopledb.model;
import java.time.ZonedDateTime;
public class Person {
private Long id;
public Person(String firstName, String lastName, ZonedDateTime odb) {
}
public Long getId() {
return 1L;
}
public void setId(Long id) {
this.id = id;
}
}
repository/PeopleRepository.java:
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.model.Person;
public class PeopleRepository {
public Person save(Person person) {
return person;
}
}
The logic of this code is not implemented yet. The goal of TDD is to just make the test pass. So we need to force ourself to create a test where the getId method will fail without the correct logic. We can achieve this by adding another Person and calling one of those two person by it's Id:
@Test
public void canSaveTwoPeople() {
PeopleRepository repo = new PeopleRepository();
Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
Person bobby = new Person("Bobby", "Smith", ZonedDateTime.of(1982,9,13,1,51,54,1, ZoneId.of("+1")));
Person savedPerson1 = repo.save(john);
Person savedPerson2 = repo.save(bobby);
assertThat(savedPerson1.getId()).isNotEqualTo(savedPerson2.getId());
}this thest does now fail as expected:
Expecting actual:
1L
not to be equal to:
1L
java.lang.AssertionError:
Expecting actual:
1L
not to be equal to:
1L
PeopleRepositoryTests > canSaveTwoPeople() FAILED
java.lang.AssertionError at PeopleRepositoryTests.java:28
1 test completed, 1 failed
FAILURE: Build failed with an exception.Now we are at the point where we need to write the connection to the database.
JDBC
PeopleRepositoryTests.java:
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.model.Person;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import static org.assertj.core.api.Assertions.assertThat;
public class PeopleRepositoryTests {
private Connection connection;
@BeforeEach
void setUp() throws SQLException {
connection = DriverManager.getConnection("jdbc:h2:~/peopletest".replace("~", System.getProperty("user.home")));
}
@Test
public void canSaveOnePerson() {
PeopleRepository repo = new PeopleRepository(connection);
Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
Person savedPerson = repo.save(john);
assertThat(savedPerson.getId()).isGreaterThan(0);
}
@Test
public void canSaveTwoPeople() {
PeopleRepository repo = new PeopleRepository(connection);
Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
Person bobby = new Person("Bobby", "Smith", ZonedDateTime.of(1982,9,13,1,51,54,1, ZoneId.of("+1")));
Person savedPerson1 = repo.save(john);
Person savedPerson2 = repo.save(bobby);
assertThat(savedPerson1.getId()).isNotEqualTo(savedPerson2.getId());
}
}
model/Person.java:
package ch.finecloud.peopledb.model;
import java.time.ZonedDateTime;
public class Person {
private Long id;
private String firstName;
private String lastName;
private ZonedDateTime dob;
public Person(String firstName, String lastName, ZonedDateTime odb) {
this.firstName = firstName;
this.lastName = lastName;
this.dob = odb;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public ZonedDateTime getDob() {
return dob;
}
public void setDob(ZonedDateTime dob) {
this.dob = dob;
}
}
repository/PeopleRepository.java:
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.model.Person;
import java.sql.*;
import java.time.ZoneId;
public class PeopleRepository {
private Connection connection;
public PeopleRepository(Connection connection) {
this.connection = connection;
}
public Person save(Person person) {
String sql = String.format("INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB) VALUES(?,?,?)");
try {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, person.getFirstName());
ps.setString(2, person.getLastName());
ps.setTimestamp(3, Timestamp.valueOf(person.getDob().withZoneSameInstant(ZoneId.of("+0")).toLocalDateTime()));
int recordsAffected = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
long id = rs.getLong(1);
person.setId(id);
}
System.out.printf("Records affected: %d%n", recordsAffected);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return person;
}
}
How to handle JUnit test records / testdata
Currently we're dirtying up our database every time we run our tests. But that's not necessarily the most desirable way for these tests to run there. Now that we have confirmed that we generally are able to write data to the database, perhaps we don't want to keep dirtying up our database permanently with all these duplicated records, as we are doing now.
There's actually a great technique that we can use if we want to use the database temporarily while the tests are working, but then have the tests essentially clean up after themselves when they're all done so that we're not permanently impacting the database. Currently, every time we connect to the database and we interact with it, and particularly we update or insert records in to the database, our SQirreL commands or statements are getting committed to the database and that's happening for us automatically.
But we can actually turn off the auto commits. This still allows us to insert data or update the database in any way at all. But we don't commit the changes and then we close the database connection. All of the changes that we did to the database will just go away. They won't be committed. They won't be permanently recorded. They will look as if they all worked for as long as we have a connection to the database. But once the database connection is gone, so will be those changes that we made. So that can give us a cool technique for cleaning up after our tests. The needed change are:
PeopleRepositoryTests.java:
@BeforeEach
void setUp() throws SQLException {
connection = DriverManager.getConnection("jdbc:h2:~/peopletest".replace("~", System.getProperty("user.home")));
connection.setAutoCommit(false);
}
@AfterEach
void tearDown() throws SQLException {
if (connection != null) {
connection.close();
}
}The important line is the connection.setAutoCommit(false);
Save return value
At the moment, if we save a person, we just get back a generic message like this:
Records affected: 1
Let's improve this, so that we see the record which has been saved:
/repository/PeopleRepository.java
while (rs.next()) {
long id = rs.getLong(1);
person.setId(id);
System.out.println(person);
}just add the System.out.... Line. But now we need to overwrite the toString() method on the Person class:
/model/Person.java
@Override
public String toString() {
return "Person{" +
"id=" + id +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
", dob=" + dob +
'}';
}If we now save a Person we get the following return value:
Person{id=47, firstName='John', lastName='Smith', dob=1980-11-15T15:15-06:00}Records affected: 1Person{id=48, firstName='Bobby', lastName='Smith', dob=1982-09-13T01:51:54+01:00}Records affected: 1
Prevent SQL injection
We could have made the SQL statement without using a prepared statement but by using just a regular statement. But if we had used a regular statement, then we would have had to use some string concatenation tricks to get these values in here. In other words, we would have had to hard code these values into the values area of our insert statement and lots of database code in Java has been written in that way.
But if you've got parameters that you need to pass in using a regular statement and using string concatenation is like the worst possible way to get those parameters in there. The reason for this has to do with a major security flaw that exists in lots of code not only just Java, it's called SQL injection.
Let's imagine that this code here is part of a web application where there's a web page and the web page asks people to enter their first name, last name, date of birth.
Now there is a malicious and intelligent hacker who wants to hack into our application and steal all of our data. One thing that he might try to do is to utilize this SQL injection technique and enter this pseudocode in the firstName field:
Max', 'Müller', '1999-01-01'); SELECT * FROM PEOPLE;
You can imagine what happens. These would just be appended via string concatenation. And depending on how badly we wrote our database code, our database code might have allowed this to execute and for the output of this to make its way back to the web browser.
So how can we avoid this? By using a prepared statement, SQL injection like this is not possible because with a prepared statement, every parameter that we're expecting to read in is constrained to just it's one little bit of data. So in other words, this name Max here is the only value that can actually be allowed in here, and all the rest of the text would just cause an exception to be thrown.
The rule to live by when when you're writing JDBC code and you've got parameters that you need to bind to outside data. Always use a prepared statement. That will save you from this particular level of SQL injection.
Find Person by Id
Now let's implement the function to query a Person by ID:
repository/PeopleRepositoryTests.java
@Test
public void canFindPersonById() {
Person savedPerson = repo.save(new Person("Test", "Jackson", ZonedDateTime.now()));
Person foundPerson = repo.findById(savedPerson.getId());
assertThat(foundPerson).isEqualTo(savedPerson);
}this forces us to implement some methods like these:
model/Person.java
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Person)) return false;
Person person = (Person) o;
return Objects.equals(id, person.id) && firstName.equals(person.firstName) && lastName.equals(person.lastName) && dob.withZoneSameInstant(ZoneId.of("+0")).equals(person.dob.withZoneSameInstant(ZoneId.of("+0")));
}
@Override
public int hashCode() {
return Objects.hash(id, firstName, lastName, dob);
}model/PersonTest.java
package ch.finecloud.peopledb.model;
import org.junit.jupiter.api.Test;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import static org.assertj.core.api.Assertions.assertThat;
class PersonTest {
@Test
public void testForEquality() {
Person p1 = new Person("p1", "smith", ZonedDateTime.of(2000,9,1,12,0,0,0, ZoneId.of("-1")));
Person p2 = new Person("p1", "smith", ZonedDateTime.of(2000,9,1,12,0,0,0, ZoneId.of("-1")));
assertThat(p1).isEqualTo(p2);
}
@Test
public void testForInequality() {
Person p1 = new Person("p1", "smith", ZonedDateTime.of(2000,9,1,12,0,0,0, ZoneId.of("-1")));
Person p2 = new Person("p2", "smith", ZonedDateTime.of(2000,9,1,12,0,0,0, ZoneId.of("-1")));
assertThat(p1).isNotEqualTo(p2);
}
}repository/PeopleRepository.java
public Person findById(Long id) {
Person person = null;
try {
PreparedStatement ps = connection.prepareStatement(GET_PERSON_BY_ID);
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
long personID = rs.getLong("ID");
String firstName = rs.getString("FIRST_NAME");
String lastName = rs.getString("LAST_NAME");
ZonedDateTime dob = ZonedDateTime.of(rs.getTimestamp("DOB").toLocalDateTime(), ZoneId.of("+0"));
person = new Person(firstName, lastName, dob);
person.setId(personID);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return person;
}But let's also add a negative test case:
repository/PeopleRepositoryTests.java
@Test
public void testPersonIdNotFound() {
Optional<Person> foundPerson = repo.findById(-1L);
assertThat(foundPerson).isEmpty();
}repository/PeopleRepository.java
public Optional<Person> findById(Long id) {
Person person = null;
try {
PreparedStatement ps = connection.prepareStatement(GET_PERSON_BY_ID);
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
long personID = rs.getLong("ID");
String firstName = rs.getString("FIRST_NAME");
String lastName = rs.getString("LAST_NAME");
ZonedDateTime dob = ZonedDateTime.of(rs.getTimestamp("DOB").toLocalDateTime(), ZoneId.of("+0"));
person = new Person(firstName, lastName, dob);
person.setId(personID);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return Optional.ofNullable(person);
}Delete one or more persons
Let's implement the function to delete one or more persons:
repository/PeopleRepositoryTests.java
@Test
public void canDeleteOnePerson() {
Person savedPerson = repo.save(new Person("Test", "Jackson", ZonedDateTime.now()));
long startCount = repo.count();
repo.delete(savedPerson);
long endCount = repo.count();
assertThat(endCount).isEqualTo(startCount-1);
}
@Test
public void canDeleteMultiplePerson() {
Person p1 = repo.save(new Person("Test1", "Jackson", ZonedDateTime.now()));
Person p2 = repo.save(new Person("Test2", "Jackson", ZonedDateTime.now()));
long startCount = repo.count();
repo.delete(p1, p2);
long endCount = repo.count();
assertThat(endCount).isEqualTo(startCount -2);
}repository/PeopleRepository.java
public long count() {
long count = 0;
try {
PreparedStatement ps = connection.prepareStatement("SELECT COUNT(*) FROM PEOPLE");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
count = rs.getLong(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
public void delete(Person person) {
try {
PreparedStatement ps = connection.prepareStatement("DELETE FROM PEOPLE WHERE ID=?");
ps.setLong(1, person.getId());
int recordsAffected = ps.executeUpdate();
System.out.println(recordsAffected);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void delete(Person...people) {
// This would be the easiest Option to delete people,
// however from a DB view it's not the most efficient:
// for (Person person : people) {
// delete(person);
// }
try {
// - Unfortunately the current version of H2 DB does not support a prepared SQL statement for this.
// - This will make us vulnerable to SQL injection, but for demo purposes we do it anyway
// - This should never be used in production
Statement stmt = connection.createStatement();
String ids = Arrays.stream(people).toList().stream()
.map(person -> person.getId().toString())
.collect(Collectors.joining(","));
int affectedRecordCount = stmt.executeUpdate("DELETE FROM PEOPLE WHERE ID IN (:ids)".replace(":ids", ids));
System.out.println(affectedRecordCount);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}Update a person
We need a method to update existing data records. What if we need to change an existing person record because of a change in the timezone?
repository/PeopleRepositoryTests.java
@Test
public void canUpdate() {
Person savedPerson = repo.save(new Person("Test2", "Jackson", ZonedDateTime.now()));
Person p1 = repo.findById(savedPerson.getId()).get();
savedPerson.setSalary(new BigDecimal("7300.28"));
repo.update(savedPerson);
Person P2 = repo.findById(savedPerson.getId()).get();
assertThat(P2.getSalary()).isNotEqualTo(p1.getSalary());
}repository/PeopleRepository.java
public void update(Person person) {
try {
PreparedStatement ps = connection.prepareStatement("UPDATE PEOPLE SET FIRST_NAME=?, LAST_NAME=?, DOB=?, SALARY=? WHERE ID=?");
ps.setString(1, person.getFirstName());
ps.setString(2, person.getLastName());
ps.setTimestamp(3, convertODBtoTimeStamp(person.getDob()));
ps.setBigDecimal(4, person.getSalary());
ps.setLong(5, person.getId());
ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private Timestamp convertODBtoTimeStamp(ZonedDateTime dob) {
return Timestamp.valueOf(dob.withZoneSameInstant(ZoneId.of("+0")).toLocalDateTime());
}Making it reusable
Let's further improve and optimize our existing classes and even make them reusable. We start by extracting the save method of the PeopleRepository class into a new class called CRUDRepository. Let's also extend the PeopleRepository class with the new CRUDRepository class.
As a starting point we just copy paste the save method to the new class. Now we want to see what parts of this method are the parts that would be unique to a class that might extend this? And what parts would be kind of boilerplate? You know, like the stuff that would be the same any time you had to create a repository class? One more goal of the refactoring is to replace all Person class calls with a capital T to make our new class capable to work with generics. Lets look at the result:
model/Entity.java
package ch.finecloud.peopledb.model;
public interface Entity {
Long getId();
void setId(Long id);
}
model/Person.java
package ch.finecloud.peopledb.model;
import java.math.BigDecimal;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.Objects;
public class Person implements Entity {
private Long id;
private String firstName;
private String lastName;
private ZonedDateTime dob;
private BigDecimal salary = BigDecimal.ZERO;
public Person(String firstName, String lastName, ZonedDateTime odb) {
this.firstName = firstName;
this.lastName = lastName;
this.dob = odb;
}
public Person(Long id, String firstName, String lastName, ZonedDateTime dob) {
this(firstName, lastName, dob);
this.id = id;
}
public Person(long id, String firstName, String lastName, ZonedDateTime dob, BigDecimal salary) {
this(id, firstName, lastName, dob);
this.salary = salary;
}
@Override
public Long getId() {
return id;
}
@Override
public void setId(Long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public ZonedDateTime getDob() {
return dob;
}
public void setDob(ZonedDateTime dob) {
this.dob = dob;
}
public BigDecimal getSalary() {
return salary;
}
public void setSalary(BigDecimal salary) {
this.salary = salary;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
", dob=" + dob +
'}';
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Person)) return false;
Person person = (Person) o;
return Objects.equals(id, person.id) && firstName.equals(person.firstName) && lastName.equals(person.lastName) &&
dob.withZoneSameInstant(ZoneId.of("+0")).equals(person.dob.withZoneSameInstant(ZoneId.of("+0")));
}
@Override
public int hashCode() {
return Objects.hash(id, firstName, lastName, dob);
}
}
repository/CRUDRepository.java
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.model.Entity;
import ch.finecloud.peopledb.model.Person;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
abstract class CRUDRepository<T extends Entity> {
protected Connection connection;
public CRUDRepository(Connection connection) {
this.connection = connection;
}
public T save(T entity) {
try {
PreparedStatement ps = connection.prepareStatement(getSaveSql(), Statement.RETURN_GENERATED_KEYS);
mapForSave(entity, ps);
int recordsAffected = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
long id = rs.getLong(1);
entity.setId(id);
System.out.println(entity);
}
System.out.printf("Records affected: %d%n", recordsAffected);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return entity;
}
public Optional<T> findById(Long id) {
T entity = null;
try {
PreparedStatement ps = connection.prepareStatement(getFindByIdSql());
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
entity = extractEntityFromResultSet(rs);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return Optional.ofNullable(entity);
}
protected abstract String getFindAllSql();
public List<T> findAll() {
List<T> entities = new ArrayList<>();
try {
PreparedStatement ps = connection.prepareStatement(getFindAllSql());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
entities.add(extractEntityFromResultSet(rs));
}
} catch (SQLException e) {
e.printStackTrace();
}
return entities;
}
public long count() {
long count = 0;
try {
PreparedStatement ps = connection.prepareStatement(getCountSql());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
count = rs.getLong(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
public void delete(T entity) {
try {
PreparedStatement ps = connection.prepareStatement(getDeleteSql());
ps.setLong(1, entity.getId());
int recordsAffected = ps.executeUpdate();
System.out.println(recordsAffected);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void delete(T...entities) {
try {
Statement stmt = connection.createStatement();
String ids = Arrays.stream(entities).map(T::getId).map(String::valueOf).collect(Collectors.joining(","));
int affectedRecordCount = stmt.executeUpdate(getDeleteInSql().replace(":ids", ids));
System.out.println(affectedRecordCount);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void update(T entity) {
try {
PreparedStatement ps = connection.prepareStatement(getUpdateSql());
mapForUpdate(entity, ps);
ps.setLong(5, entity.getId());
ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
protected abstract String getUpdateSql();
/**
*
* @return should return a SQL string like:
* "DELETE FROM PEOPLE WHERE ID IN (:ids)"
* Be sure to include the '(:ids)' named parameter & call it 'ids'
*/
protected abstract String getDeleteInSql();
protected abstract String getDeleteSql();
protected abstract String getCountSql();
abstract T extractEntityFromResultSet(ResultSet rs) throws SQLException;
/**
*
* @return Returns a String that represents the SQL needed to retrive one entity.
* The SQL must contain one SQL parameter, i.e. "?", that will bind to the
* entity's ID.
*/
protected abstract String getFindByIdSql();
abstract void mapForSave(T entity, PreparedStatement ps) throws SQLException;
abstract void mapForUpdate(T entity, PreparedStatement ps) throws SQLException;
abstract String getSaveSql();
}
repository/PeopleRepository.java
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.model.Person;
import java.math.BigDecimal;
import java.sql.*;
import java.time.ZoneId;
import java.time.ZonedDateTime;
public class PeopleRepository extends CRUDRepository<Person> {
public static final String SAVE_PERSON_SQL = String.format("INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB) VALUES(?,?,?)");
public static final String FIND_BY_ID_SQL = String.format("SELECT ID, FIRST_NAME, LAST_NAME, DOB, SALARY FROM PEOPLE WHERE ID=?");
public static final String FIND_ALL_SQL = String.format("SELECT ID, FIRST_NAME, LAST_NAME, DOB, SALARY FROM PEOPLE");
public static final String SELECT_COUNT_SQL = "SELECT COUNT(*) FROM PEOPLE";
public static final String DELETE_SQL = "DELETE FROM PEOPLE WHERE ID=?";
public static final String DELETE_IN_SQL = "DELETE FROM PEOPLE WHERE ID IN (:ids)";
public static final String UPDATE_SQL = "UPDATE PEOPLE SET FIRST_NAME=?, LAST_NAME=?, DOB=?, SALARY=? WHERE ID=?";
public PeopleRepository(Connection connection) {
super(connection);
}
@Override
String getSaveSql() {
return SAVE_PERSON_SQL;
}
@Override
void mapForSave(Person entity, PreparedStatement ps) throws SQLException {
ps.setString(1, entity.getFirstName());
ps.setString(2, entity.getLastName());
ps.setTimestamp(3, convertODBtoTimeStamp(entity.getDob()));
}
@Override
Person extractEntityFromResultSet(ResultSet rs) throws SQLException {
long personID = rs.getLong("ID");
String firstName = rs.getString("FIRST_NAME");
String lastName = rs.getString("LAST_NAME");
ZonedDateTime dob = ZonedDateTime.of(rs.getTimestamp("DOB").toLocalDateTime(), ZoneId.of("+0"));
BigDecimal salary = rs.getBigDecimal("SALARY");
return new Person(personID, firstName, lastName, dob, salary);
}
@Override
void mapForUpdate(Person entity, PreparedStatement ps) throws SQLException {
ps.setString(1, entity.getFirstName());
ps.setString(2, entity.getLastName());
ps.setTimestamp(3, convertODBtoTimeStamp(entity.getDob()));
ps.setBigDecimal(4, entity.getSalary());
}
@Override
protected String getFindByIdSql() {
return FIND_BY_ID_SQL;
}
@Override
protected String getFindAllSql() {
return FIND_ALL_SQL;
}
@Override
protected String getCountSql() {
return SELECT_COUNT_SQL;
}
@Override
protected String getDeleteSql() {
return DELETE_SQL;
}
@Override
protected String getDeleteInSql() {
return DELETE_IN_SQL;
}
@Override
protected String getUpdateSql() {
return UPDATE_SQL;
}
private Timestamp convertODBtoTimeStamp(ZonedDateTime dob) {
return Timestamp.valueOf(dob.withZoneSameInstant(ZoneId.of("+0")).toLocalDateTime());
}
}
Improve the SQL statement calls
We had to pepper this class with these extra getter and setter methods to provider the SQL statements. But there are frameworks that enable us to do this kind of thing in a better way. We're going to give our CRUDRepository the ability to get the SQL queries without having to implement these abstract methods.
We can do this by using the Reflection API of Java. The Reflection API is pretty much only used by frameworks, but it just so happens that as we are creating this CRUDRepository functionality, we are basically creating a poor man's framework. So let's go a bit further down this rabbit hole and dig into the Reflection API a little bit.
This is the big picture: Instead of having to override and implement these various scatter methods to return the SQL, a more modern way of doing the equivalent to this with a lot of modern frameworks would be to actually make use of an annotation so we could have an annotation so we could introduce and create our own annotation. In that annotation, we could specify the SQL that we want, and then our CRUDRepository could dynamically, at runtime, learn the SQL that it needs from the annotation and thereby we would no longer need these methods.
The Reflection API allows us to write Java code to that allows us to analyze our Java code. This allows us to analyse our code in runtime and to things with it.
annotation/Id.java
package ch.finecloud.peopledb.annotation;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface Id {
}
annotation/MultiSQL.java
package ch.finecloud.peopledb.annotation;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface MultiSQL{
SQL[] value();
}
annotation/SQL.java
package ch.finecloud.peopledb.annotation;
import ch.finecloud.peopledb.model.CrudOperation;
import java.lang.annotation.Repeatable;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
@Repeatable(MultiSQL.class)
public @interface SQL {
String value();
CrudOperation operationType();
}
model/CrudOperation.java
package ch.finecloud.peopledb.model;
public enum CrudOperation {
SAVE,
UPDATE,
FIND_BY_ID,
FIND_ALL,
DELETE_ONE,
DELETE_MANY,
COUNT
}
model/Person.java
package ch.finecloud.peopledb.model;
import ch.finecloud.peopledb.annotation.Id;
import java.math.BigDecimal;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.Objects;
public class Person {
@Id
private Long id;
private String firstName;
private String lastName;
private ZonedDateTime dob;
private BigDecimal salary = BigDecimal.ZERO;
public Person(String firstName, String lastName, ZonedDateTime odb) {
this.firstName = firstName;
this.lastName = lastName;
this.dob = odb;
}
public Person(Long id, String firstName, String lastName, ZonedDateTime dob) {
this(firstName, lastName, dob);
this.id = id;
}
public Person(long id, String firstName, String lastName, ZonedDateTime dob, BigDecimal salary) {
this(id, firstName, lastName, dob);
this.salary = salary;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public ZonedDateTime getDob() {
return dob;
}
public void setDob(ZonedDateTime dob) {
this.dob = dob;
}
public BigDecimal getSalary() {
return salary;
}
public void setSalary(BigDecimal salary) {
this.salary = salary;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
", dob=" + dob +
'}';
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Person)) return false;
Person person = (Person) o;
return Objects.equals(id, person.id) && firstName.equals(person.firstName) && lastName.equals(person.lastName) &&
dob.withZoneSameInstant(ZoneId.of("+0")).equals(person.dob.withZoneSameInstant(ZoneId.of("+0")));
}
@Override
public int hashCode() {
return Objects.hash(id, firstName, lastName, dob);
}
}
repository/CRUDRepository.java
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.annotation.Id;
import ch.finecloud.peopledb.annotation.MultiSQL;
import ch.finecloud.peopledb.annotation.SQL;
import ch.finecloud.peopledb.model.CrudOperation;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;
import java.util.function.Supplier;
import java.util.stream.Collectors;
import java.util.stream.Stream;
abstract class CRUDRepository<T> {
protected Connection connection;
public CRUDRepository(Connection connection) {
this.connection = connection;
}
public T save(T entity) {
try {
PreparedStatement ps = connection.prepareStatement(getSqlByAnnotation(CrudOperation.SAVE, this::getSaveSql), Statement.RETURN_GENERATED_KEYS);
mapForSave(entity, ps);
int recordsAffected = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
long id = rs.getLong(1);
setIdByAnnotation(id, entity);
System.out.println(entity);
}
System.out.printf("Records affected: %d%n", recordsAffected);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return entity;
}
public Optional<T> findById(Long id) {
T entity = null;
try {
PreparedStatement ps = connection.prepareStatement(getSqlByAnnotation(CrudOperation.FIND_BY_ID, this::getFindByIdSql));
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
entity = extractEntityFromResultSet(rs);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return Optional.ofNullable(entity);
}
public List<T> findAll() {
List<T> entities = new ArrayList<>();
try {
PreparedStatement ps = connection.prepareStatement(getSqlByAnnotation(CrudOperation.FIND_ALL, this::getFindAllSql));
ResultSet rs = ps.executeQuery();
while (rs.next()) {
entities.add(extractEntityFromResultSet(rs));
}
} catch (SQLException e) {
e.printStackTrace();
}
return entities;
}
public long count() {
long count = 0;
try {
PreparedStatement ps = connection.prepareStatement(getSqlByAnnotation(CrudOperation.COUNT, this::getCountSql));
ResultSet rs = ps.executeQuery();
if (rs.next()) {
count = rs.getLong(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
public void delete(T entity) {
try {
PreparedStatement ps = connection.prepareStatement(getSqlByAnnotation(CrudOperation.DELETE_ONE, this::getDeleteSql));
ps.setLong(1, getIdByAnnotation(entity));
int recordsAffected = ps.executeUpdate();
System.out.println(recordsAffected);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private Long getIdByAnnotation(T entity) {
return Arrays.stream(entity.getClass().getDeclaredFields())
.filter(f -> f.isAnnotationPresent(Id.class))
.map(f -> {
f.setAccessible(true);
Long id = null;
try {
id = (long)f.get(entity);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
return id;
})
.findFirst().orElseThrow(() -> new RuntimeException("No ID annotated field found"));
}
private void setIdByAnnotation(Long id, T entity) {
Arrays.stream(entity.getClass().getDeclaredFields())
.filter(f -> f.isAnnotationPresent(Id.class))
.forEach(f -> {
f.setAccessible(true);
try {
f.set(entity, id);
} catch (IllegalAccessException e) {
throw new RuntimeException("Unable to set ID field value.");
}
});
}
public void delete(T...entities) {
try {
Statement stmt = connection.createStatement();
String ids = Arrays.stream(entities).map(e -> getIdByAnnotation(e)).map(String::valueOf).collect(Collectors.joining(","));
int affectedRecordCount = stmt.executeUpdate(getSqlByAnnotation(CrudOperation.DELETE_MANY, this::getDeleteInSql).replace(":ids", ids));
System.out.println(affectedRecordCount);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void update(T entity) {
try {
PreparedStatement ps = connection.prepareStatement(getSqlByAnnotation(CrudOperation.UPDATE, this::getUpdateSql));
mapForUpdate(entity, ps);
ps.setLong(5, getIdByAnnotation(entity));
ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private String getSqlByAnnotation(CrudOperation operationType, Supplier<String> sqlGetter) {
Stream<SQL> multiSqlStream = Arrays.stream(this.getClass().getDeclaredMethods())
.filter(m -> m.isAnnotationPresent(MultiSQL.class))
.map(m -> m.getAnnotation(MultiSQL.class))
.flatMap(mSQL -> Arrays.stream(mSQL.value()));
Stream<SQL> sqlStream = Arrays.stream(this.getClass().getDeclaredMethods())
.filter(m -> m.isAnnotationPresent(SQL.class))
.map(m -> m.getAnnotation(SQL.class));
return Stream.concat(multiSqlStream,sqlStream)
.filter(a -> a.operationType().equals(operationType))
.map(SQL::value)
.findFirst().orElseGet(sqlGetter);
}
protected String getUpdateSql() {throw new RuntimeException("SQL not defined.");}
/**
*
* @return should return a SQL string like:
* "DELETE FROM PEOPLE WHERE ID IN (:ids)"
* Be sure to include the '(:ids)' named parameter & call it 'ids'
*/
protected String getDeleteInSql() {throw new RuntimeException("SQL not defined.");}
protected String getDeleteSql() {throw new RuntimeException("SQL not defined.");}
protected String getCountSql() {throw new RuntimeException("SQL not defined.");}
protected String getFindAllSql() {throw new RuntimeException("SQL not defined.");}
abstract T extractEntityFromResultSet(ResultSet rs) throws SQLException;
/**
*
* @return Returns a String that represents the SQL needed to retrive one entity.
* The SQL must contain one SQL parameter, i.e. "?", that will bind to the
* entity's ID.
*/
protected String getFindByIdSql() {throw new RuntimeException("SQL not defined.");}
String getSaveSql() {throw new RuntimeException("SQL not defined.");}
abstract void mapForSave(T entity, PreparedStatement ps) throws SQLException;
abstract void mapForUpdate(T entity, PreparedStatement ps) throws SQLException;
}
repository/PeopleRepositoryV3.java
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.annotation.SQL;
import ch.finecloud.peopledb.model.CrudOperation;
import ch.finecloud.peopledb.model.Person;
import java.math.BigDecimal;
import java.sql.*;
import java.time.ZoneId;
import java.time.ZonedDateTime;
public class PeopleRepositoryV3 extends CRUDRepository<Person> {
public static final String SAVE_PERSON_SQL = "INSERT INTO PEOPLE (FIRST_NAME, LAST_NAME, DOB) VALUES(?,?,?)";
public static final String FIND_BY_ID_SQL = "SELECT ID, FIRST_NAME, LAST_NAME, DOB, SALARY FROM PEOPLE WHERE ID=?";
public static final String FIND_ALL_SQL = "SELECT ID, FIRST_NAME, LAST_NAME, DOB, SALARY FROM PEOPLE";
public static final String SELECT_COUNT_SQL = "SELECT COUNT(*) FROM PEOPLE";
public static final String DELETE_SQL = "DELETE FROM PEOPLE WHERE ID=?";
public static final String DELETE_IN_SQL = "DELETE FROM PEOPLE WHERE ID IN (:ids)";
public static final String UPDATE_SQL = "UPDATE PEOPLE SET FIRST_NAME=?, LAST_NAME=?, DOB=?, SALARY=? WHERE ID=?";
public PeopleRepositoryV3(Connection connection) {
super(connection);
}
@Override
@SQL(value = SAVE_PERSON_SQL, operationType = CrudOperation.SAVE)
void mapForSave(Person entity, PreparedStatement ps) throws SQLException {
ps.setString(1, entity.getFirstName());
ps.setString(2, entity.getLastName());
ps.setTimestamp(3, convertODBtoTimeStamp(entity.getDob()));
}
@Override
@SQL(value = UPDATE_SQL, operationType = CrudOperation.UPDATE)
void mapForUpdate(Person entity, PreparedStatement ps) throws SQLException {
ps.setString(1, entity.getFirstName());
ps.setString(2, entity.getLastName());
ps.setTimestamp(3, convertODBtoTimeStamp(entity.getDob()));
ps.setBigDecimal(4, entity.getSalary());
}
@Override
@SQL(value = FIND_BY_ID_SQL, operationType = CrudOperation.FIND_BY_ID)
@SQL(value = FIND_ALL_SQL, operationType = CrudOperation.FIND_ALL)
@SQL(value = SELECT_COUNT_SQL, operationType = CrudOperation.COUNT)
@SQL(value = DELETE_IN_SQL, operationType = CrudOperation.DELETE_MANY)
@SQL(value = DELETE_SQL, operationType = CrudOperation.DELETE_ONE)
Person extractEntityFromResultSet(ResultSet rs) throws SQLException {
long personID = rs.getLong("ID");
String firstName = rs.getString("FIRST_NAME");
String lastName = rs.getString("LAST_NAME");
ZonedDateTime dob = ZonedDateTime.of(rs.getTimestamp("DOB").toLocalDateTime(), ZoneId.of("+0"));
BigDecimal salary = rs.getBigDecimal("SALARY");
return new Person(personID, firstName, lastName, dob, salary);
}
private Timestamp convertODBtoTimeStamp(ZonedDateTime dob) {
return Timestamp.valueOf(dob.withZoneSameInstant(ZoneId.of("+0")).toLocalDateTime());
}
}
repository/PeopleRepositoryV3Tests.java
package ch.finecloud.peopledb.repository;
import ch.finecloud.peopledb.model.Person;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.List;
import java.util.Optional;
import static org.assertj.core.api.Assertions.assertThat;
public class PeopleRepositoryV3Tests {
private Connection connection;
private PeopleRepositoryV3 repo;
@BeforeEach
void setUp() throws SQLException {
connection = DriverManager.getConnection("jdbc:h2:~/peopletest".replace("~", System.getProperty("user.home")));
connection.setAutoCommit(false);
repo = new PeopleRepositoryV3(connection);
}
@AfterEach
void tearDown() throws SQLException {
if (connection != null) {
connection.close();
}
}
@Test
public void canSaveOnePerson() {
Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
Person savedPerson = repo.save(john);
assertThat(savedPerson.getId()).isGreaterThan(0);
}
@Test
public void canSaveTwoPeople() {
Person john = new Person("John", "Smith", ZonedDateTime.of(1980,11,15,15,15,0,0, ZoneId.of("-6")));
Person bobby = new Person("Bobby", "Smith", ZonedDateTime.of(1982,9,13,1,51,54,0, ZoneId.of("+1")));
Person savedPerson1 = repo.save(john);
Person savedPerson2 = repo.save(bobby);
assertThat(savedPerson1.getId()).isNotEqualTo(savedPerson2.getId());
}
@Test
public void canFindPersonById() {
Person savedPerson = repo.save(new Person("Test", "Jackson", ZonedDateTime.now()));
Person foundPerson = repo.findById(savedPerson.getId()).get();
assertThat(foundPerson).isEqualTo(savedPerson);
}
@Test
public void testPersonIdNotFound() {
Optional<Person> foundPerson = repo.findById(-1L);
assertThat(foundPerson).isEmpty();
}
@Test
public void canDeleteOnePerson() {
Person savedPerson = repo.save(new Person("Test", "Jackson", ZonedDateTime.now()));
long startCount = repo.count();
repo.delete(savedPerson);
long endCount = repo.count();
assertThat(endCount).isEqualTo(startCount-1);
}
@Test
public void canDeleteMultiplePerson() {
Person p1 = repo.save(new Person("Test1", "Jackson", ZonedDateTime.now()));
Person p2 = repo.save(new Person("Test2", "Jackson", ZonedDateTime.now()));
long startCount = repo.count();
repo.delete(p1, p2);
long endCount = repo.count();
assertThat(endCount).isEqualTo(startCount -2);
}
@Test
public void canGetCount() {
long startCount = repo.count();
repo.save(new Person("John1", "Smoth", ZonedDateTime.of(1880,11,15,4,44,0,0,ZoneId.of("+6"))));
repo.save(new Person("John2", "Smoth", ZonedDateTime.of(1880,11,15,4,44,0,0,ZoneId.of("+6"))));
long endCount = repo.count();
assertThat(endCount).isEqualTo(startCount +2);
}
@Test
public void canUpdate() {
Person savedPerson = repo.save(new Person("Test2", "Jackson", ZonedDateTime.now()));
Person p1 = repo.findById(savedPerson.getId()).get();
savedPerson.setSalary(new BigDecimal("7300.28"));
repo.update(savedPerson);
Person P2 = repo.findById(savedPerson.getId()).get();
assertThat(P2.getSalary()).isNotEqualTo(p1.getSalary());
}
@Test
public void canFindAll() {
repo.save(new Person("John", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John1", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John2", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John3", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John4", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John5", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John6", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John7", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John8", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
repo.save(new Person("John9", "Smith", ZonedDateTime.of(1980,11,14,16,22,11,0,ZoneId.of("+1"))));
List<Person> people = repo.findAll();
assertThat(people.size()).isGreaterThanOrEqualTo(10);
}
}
Speeding up SQL queries
They're one or two really basic things that we can do when working with a database to vastly improve the performance of that database. Let's say we have an example SQL query which takes about 2s to complete. So here's the problem. Two seconds might seem like it's pretty fast considering that we're iterating over a big number of rows. But in actuality, that is really quite slow for a database. If this database with all records were being used in an application or on the web or something like that, and we had lots of users who were all trying to do the equivalent of this more or less simultaneously, those two seconds start to add up pretty quickly. And keep in mind every time the computer has to take two and a half seconds to go find one record, that's time that the processor in that computer isn't available to do other things right. So this is actually pretty bad.
If re needed record is towards the end of the database, the database essentially had to perform what's called a full table scan, meaning just that it had to scan through every single record in the table in order to find that record.
What could we improve? Java allows us to work with sets and hash maps. And one of the nice things about the hash map or even just maps in general, is the fact that when we store a key and a value pair in a map, the hash map implementation of the map interface has the ability to analyze the key that we're inserting into that key value pair entry and generate a hash code for that key. The hash map will then use that hash code to determine where in a table that entry should go. So the next time you try to retrieve a value out of the hash map using that key, the hash map doesn't have to iterate over every single entry in that map. Instead, it takes your key that you provided. It regenerates a hash code for it, and then it uses that hash code to determine an index into a table where your record exists or where your entry exists. And then it can much more efficiently go straight to that entry. Or at least it can get super super close if there happened to be collisions where other entries yield the same index. But it can cut down a lot on the amount of scanning that has to be done to find your entry.
With SQL databases you can actually to the same thing, you can create indexes. Lets add an index for a row called "ID" and "EMAIL":
CREATE INDEX ID_IDX ON PEOPLE(ID);
CREATE INDEX EMAIL_IDX ON PEOPLE(EMAIL);
This allows us to speed up a single SQL query from 4-5s down to a millisecond. So it's a huge speed boost.
Now you may be thinking, well then we should probably just generate indexes on every single column, in every database table every time. No better not. Why? These indexes come at a cost. So we don't necessarily just want to automatically generate indexes on every single column in every single table. The key to figuring this out is to understand how our database applications will be utilized. So we have to ask ourselves questions like in what ways are our users most likely to search for and query data? For example, if we have an application that allows people to enter an ID to look up some kind of data or record, well, then you know, you're certainly going to have to be querying the database by ID for those particular records. And since you know that for certain, you'll definitely want to have an index generated on that particular column and any other columns that users are likely to specifically search by or grouping by.
So any of these key types of columns where you're going to search by or group by or sorting on any of those types of columns, you will likely want to have an index generated on them.
But here's the cost: When you have indexes generated on columns in a table and then you insert records into the table, inserts and updates can take longer now because now every time you do an insert or potentially even an update, the database will certainly have to do its analysis and re indexing, potentially on inserts. And it may have to do it on updates, particularly if you modified a column that has an index on it.