DbAssist JPA Commons overview

Concept

The generic AbstractRepository class provides implementation of methods used to get whole entity, single attributes or a group of attributes. It allows us to add multiple conditions and fetch callbacks on the query.

Let's see the setup with the example of the entity class User.java

@Entity
@Table(name = "users", schema = "jpa")
public class User {

    @Id
    @Column(name = "id")
    private int id;

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

    @Column(name = "created_at")
    private Date createdAt;
    
    //Setters and getters
    ...
}

Then we create a UserRepository extending AbstractRepository<User>:

@Repository
public class UserRepo extends AbstractRepository<User> {

    public UserRepo() {
        super(User.class);
    }

    @PersistenceContext
    protected EntityManager entityManager;
    
    //methods using AbstractRepository methods to retrieve data from DB
    ...
}

It gives us the possibility to read data from DB using already implemented methods.

find

The method takes three arguments:

• ConditionsBuilder

• FetchesBuilder

• OrderBy

It returns the list of entities which meet the conditions specified in ConditionsBuilder (look up Building conditions) with the order specified by the third argument (look on Grouping and specifying order page). Also, it fetches the specified data using JOIN, so that we read the data (including joined entities) using one SQL query. We specify what data to fetch by using FetchesBuilder. The example is provided in the unit test project, DbAssist-jpa-tester.

Example

//prepare and apply conditions
ConditionsBuilder cb = new ConditionsBuilder();
Condition c1 = cb.greaterThan("id", 3);
Condition c2 = cb.lessThan("id", 10);
cb.apply(and(c1, c2));

//specify the order of the result list
OrderBy orderBy = new OrderBy();
orderBy
        .asc(cb, "name")
        .desc(cb, "createdAt");

//read data from DB
List<User> results = uRepo.find(cb, userOrderBy);

Result

SELECT * FROM users 
WHERE users.id > 3 AND users.id < 10 
ORDER BY users.name ASC, users.created_at DESC

findAttribute

The method prepares the SQL query to read a specific attribute of the entity, applies fetch callbacks, conditions and sets specified order on the generated query. Then it runs the query and returns the list of the values read from the corresponding column in the DB. It returns a list of entities meeting the conditions from the DB.

Parameters

• attributeName - the name of the entity attribute to read from the database
• selectDistinct - specify whether duplicate query results will be eliminated
• conditionsBuilder - class containing conditions to apply on the query
• fetchesBuilder - class containing fetch callbacks to apply on the query
• orderBy - list of lambdas specifying order of the returned list

Example

//prepare and apply conditions
ConditionsBuilder cb = new ConditionsBuilder();
Condition hc = cb.equal("createdAt", date);
cb.apply(hc);

//read only names of the entity
List<String> names = uRepo.findAttribute("name", cb);

Result

SELECT users.name FROM users
WHERE users.created_at = ?

findAttributes

The method prepares the SQL query to read a few specific attributes of the entity, applies fetch callbacks, conditions and sets the specified order on the generated query. Then it runs the query and returns the list of tuples (one tuple per found entity in the DB) containing the values read from the specified columns in the DB.

Parameters

• selectionList - specifies which entity attributes to read or which aggregate methods to use
• conditionsBuilder - class containing conditions to apply on the query
• fetchesBuilder - class containing fetch callbacks to apply on the query
• orderBy - list of lambdas specifying order of the returned list
• groupBy - list of lambdas specifying grouping

Example

//prepare and apply conditions
ConditionsBuilder cb = new ConditionsBuilder();
Condition hc = cb.inRangeCondition("id", 1, 5);
cb.apply(hc);

//prepare attributes to select
SelectionList selectionList = new SelectionList();
selectionList
        .select(cb, "id")
        .select(cb, "name");

//get data from the DB
List<Tuple> tuples = uRepo.findAttributes(selectionList, cb);

//seperate data from tuples
List<Integer> ids = new ArrayList<>();
List<String> names = new ArrayList<>();
tuples.forEach((tuple -> {
    ids.add((Integer) tuple.get(0));
    names.add((String) tuple.get(1));
}));

Result

SELECT users.id, users.name FROM users
WHERE users.id >= 1 AND users.id <= 5