Skip to main content

Single Query

In this section you will learn how to build and execute queries whose objective is to search for a single record on the database through KSearch as well as how to manipulate the information once it has been extracted from the database.

The single() method and the KRow object​

The way available through KSearch to build and execute queries that are in charge of searching for a single record on the database is with the single() method.

This method returns a KRow object which serves as a Java storage for the record returned by the generated and executed SQL query.

If the generated SQL query returns more than one record or no records from the database, then the KRow that is returned is considered a null KRow (A KRow without data). The only method available on a null KRow is the isNull() method, which returns true or false if the KRow is null or not respectively.

Note: The single() method internally invoke the getSingleResult() method of a native query built through the EntityManager from JPA.

How to manipulate data stored in a KRow object?​

There are different methods for the extraction or manipulation of data in a KRow object and they will be shown below:

1. get methods​

The get methods allow the extraction of a single data from the KRow object. They have two ways of being used:

  1. Receiving as a parameter a int value, which corresponds to the index of the column that you want to extract.
  2. Receiving as a parameter a String value, which corresponds to the name of the column that you want to extract. Notably, if in the generated SQL query, the column has an alias, the String value to be received by parameter must correspond to the name of the alias (The name of the column is overridden by the alias for the use get methods).

In this sense, the get methods are divided according to the type of data to be extracted:

Datatypeget method
Objectget()
StringgetString()
CharactergetCharacter()
UUIDgetUUID()
BigDecimalgetBigDecimal()
BigIntegergetBigInteger()
LonggetLong()
IntegergetInteger()
BooleangetBoolean()
DoublegetDouble()
LocalDateTimegetLocalDateTime()
LocalDategetLocalDate()
DategetDate()
TimestampgetTimestamp()

Additionally, the KRow object has 3 methods that allow verifying the nullity of the entire object or of a specific column:

  1. isNull(): Returns true if the generated SQL query returns more than one record or no record (A KRow without data), in any other case, it returns false.
  2. isNull(int): Receiving as a parameter a int value, it allows verifying the nullity of the column whose index matches the supplied parameter. It returns true if the value of the column according to the supplied index is null, in any other case, it returns false.
  3. isNull(String): Receiving a String value as a parameter, allows verifying the nullity of the column whose name or alias matches the supplied parameter (The column name is overridden by the alias for using this method). It returns true if the value of the column according to the name or alias supplied is null, in any other case, it returns false.

2. toMap() method​

The toMap() method allows the extraction of all the data contained in the KRow object through the java.util.Map data structure. The name of the keys in the map is given by the name of the columns of the SQL query, but if these columns are assigned an alias, then the name of the key will be the alias (The name of the column is overridden by the alias). It is very useful at the moment to use it in conjunction with the DynamicObject object to return it as a response from an API. To learn more about how to use the DynamicObject object, go to the DynamicObject section.

3. buildResponse() method​

The buildResponse() method converts the KRow object into a String with JSON format to later be returned as a response from an API through a javax.ws.rs.core.Response object or an org.springframework.http.ResponseEntity object depending on the framework and server that is being used. The name of the JSON properties is given by the name of the columns in the SQL query, but if these columns are assigned an alias, then the property name will be the alias (The name of the column is overridden by alias).

Examples​

We will illustrate the above with the following practical examples:

Example 1: Given the id of a user, must be searched in the database the name and email of the user that matches that id. If the user does not exist, print the message "User does not exist", if the user exists but does not have an email address, print their name and the message "This user does not have email" and if the user exists and has email, print their name and email.

Note: All users have a name in the database.

Java code:

final Long id = 333; //The id of the user to search

final KRow user =
    K.
    table("app_user").
    select(
        "name", //index 0
        "email" //index 1
    ).
    where("id", id).
    single();

if (user.isNull()) {
    System.out.println("User does not exist");
    return;
}

System.out.println("Username: " + user.getString("name")); //John
System.out.println("Username: " + user.getString(0));      //John

if (user.isNull("email")) {
    System.out.println("This user does not have email");
    return;
}

System.out.println("Email of user: " + user.getString("email")); //john@myemail.com
System.out.println("Email of user: " + user.getString(1));       //john@myemail.com

SQL generated:

SELECT name, email
FROM app_user
WHERE id = ?1

Parameters:

  • ?1 → 333

Example 2: Show the id and the registration date of the first user in the year 2021 who was registered without email. If there are none, show the message "All users of that year have email".

Note: Show the date according to the ISO8601 standard.

Java code:

import ve.zlab.k.helper.sql.SQLHelper;

final KRow user =
    K.
    table("app_user").
    select(
        "id",                                                          //index 0
        SQLHelper.timestampToISO8601("created_at", "registrationDate") //index 1
    ).
    whereNull("email").
    whereYear("created_at", 2021).
    orderByAsc("registrationDate").
    limit(1L).
    single();

if (user.isNull()) {
    System.out.println("All users of that year have email");
    return;
}

System.out.println("User ID: " + user.getLong("id")); //7
System.out.println("User ID: " + user.getLong(0));    //7

System.out.println("Registration Date: " + user.getString("registrationDate")); //2021-04-24T05:03:35.726Z
System.out.println("Registration Date: " + user.getString(1));                  //2021-04-24T05:03:35.726Z

SQL generated:

SELECT id, TO_CHAR(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS registrationDate 
FROM app_user
WHERE email IS NULL
AND EXTRACT( YEAR FROM created_at ) = ?1
ORDER BY registrationDate ASC
LIMIT 1

Parameters:

  • ?1 → 2021

Example 3: Build a REST API that, given the id of a user, returns a JSON including his name, surname and date of birth as personal information.

Note: Show date according to ISO8601 standard.

Java code:

import com.example.services.UserService;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import ve.zlab.k.KException;
import ve.zlab.k.KExecutor;

@Stateless
@Path("/user")
public class UserController {

    @EJB
    public KExecutor K;

    @GET
    @Path("/{id}/personal-information")
    @Produces(MediaType.APPLICATION_JSON)
    public Response personalInformation(
        @PathParam("id") final Long id //Example: 3201
    ) throws KException {
        return UserService.personalInformation(K, id);
    }
}

SQL generated:

SELECT au.name, au.last_name AS lastName, TO_CHAR(au.birthdate, 'YYYY-MM-DD') AS birthdate
FROM app_user au
WHERE au.id = ?

Parameters:

  • ?1 → 201

JSON generated:

{
    "name": "John",
    "birthdate": "1991-05-01"
}