SQL Database Recon

Tests

If something of what was said below was not clear to you, then you can refer to the tests, which were the source of information and code: Click to redirect

---

How to create SQL connection?

The SQLDatabaseRecon module generally contains three kinds of connections:

• ClickHouse (ru.yandex.clickhouse:clickhouse-jdbc)
• Hikari (com.zaxxer:HikariCP)
• MySQL (mysql:mysql-connector-java)
• SQLite (org.xerial:sqlite-jdbc)

(This list will be updated in the future)

Based on this, at the moment it is possible to implement only one of the above types of connection.

---

First, let's implement a data class to initialize credentials:

public static final ReconSqlCredentials DATABASE_CREDENTIALS = ReconSql.getInstance().createCredentials(
        3306, "localhost", "root", "", "test"
);

After that, we have the opportunity to call any of the predefined types of connection:

ClickHouseDatabaseConnection connection = ReconSql.getInstance().createClickHouseConnection(DATABASE_CREDENTIALS);

HikariDatabaseConnection connection = ReconSql.getInstance().createHikariConnection(DATABASE_CREDENTIALS);

MysqlDatabaseConnection connection = ReconSql.getInstance().createMysqlConnection(DATABASE_CREDENTIALS);

The SQLite connection type only needs a file to store for initialization:

SqliteDatabaseConnection connection = ReconSql.getInstance().createSqliteConnection(new File("sqlite.db"));

After this procedure, the connect() method is called from the connection implementation:

connection.connect();

---

Listen an events of connection

Listening to any events is available from the implementation of ReconSqlEventListener

For example, let's create a class through the adapter of the interface given earlier:

public class SQLEventHandler extends ReconSqlEventListenerAdapter {

    @Override
    public void onConnected(ReconSqlConnection connection) {
        connection.getLogger().info("Success connected to " + connection.getCredentials());
    }

    @Override
    public void onReconnect(ReconSqlConnection connection) {
        connection.getLogger().info("Try reconnect to " + connection.getCredentials());
    }

    @Override
    public void onDisconnect(ReconSqlConnection connection) {
        connection.getLogger().info(connection.getCredentials() + " was disconnected");
    }

    @Override
    public void onExecute(ReconSqlConnection connection, String sql) {
        connection.getLogger().info("Request sent: " + sql);
    }
}

The listener is initialized into the connection via a setter:

connection.setEventHandler(new SQLEventHandler());

---

Details

---

Tables Control

To begin with, we need to get or create a table in the database, in which various kinds of requests for changing, deleting, obtaining data will already be transmitted.

An example of creat and get a table:

ReconSqlTable usersTable = connection.createOrGetTable("users", create -> {

    create.push(IndexedField.createPrimaryNotNull(ReconSqlFieldType.INT, "Id")
            .index(IndexedField.IndexType.AUTO_INCREMENT));

    create.push(IndexedField.createNotNull(ReconSqlFieldType.VAR_CHAR, "FirstName"));
    create.push(IndexedField.createNotNull(ReconSqlFieldType.VAR_CHAR, "LastName"));

    create.push(IndexedField.create(ReconSqlFieldType.TIMESTAMP, "Birthday"));
});

An example of getting a table:

ReconSqlTable usersTable = connection.getTable("users");

In this case, if the table does not exist, then the getTable(String) method will return null.

For simple requests example:

usersTable.clear(); // -> Clear all table values. 

usersTable.drop(); // -> Delete the table from database.

usersTable.requestCount(); // -> Get values count of the table.

usersTable.selectAll(); // -> Get all values of the table.

usersTable.selectWhere(); // -> Get all values of the table with filters use.

and more...

---

Requests execution

After we created the table, it became possible to execute any queries on it.
There are two ways to do this:

---

1. Manual query execution. This comes directly from the originally created connection (inherited from ReconSqlConnection) by the method ReconSqlConnection.getExecution()...

For example:

boolean sync = true;

connection.getExecution().update(sync, 
        
        // update request string.
        "INSERT INTO `users` (`FirstName`, `LastName`, `Birthday`) VALUES (?,?,?)",
        
        // update request parameters.
        "Misha", "Leyn", new Timestamp(System.currentTimeMillis()));

boolean sync = true;

connection.getExecution().getResponse(sync,

        // update request string.
        "SELECT * FROM `users` WHERE `Id` > ? LIMIT 1000",

        // update request parameters.
        5).thenAccept(response -> {

            if (!response.next()) {
                connection.getLogger().warning("Response data is not found!");
                return;
            }
            
            response.doForEachOrdered(value -> {
                
                int userID = value.getInt("Id");
                long birthdayAsMillis = value.getTimestamp("Birthday").getTime();

                String username = (value.getString("FirstName") + " " + value.getString("LastName"));

                connection.getLogger().info("ID: " + userID + " | Name: " + username + " | Birthday: " + birthdayAsMillis);
            });
        });

boolean sync = true;

int count = connection.getExecution().getResponse(sync,

        // update request string.
        "SELECT COUNT(*) AS `len` FROM `users` WHERE `Id` > 5 LIMIT 1000")
        
        // response handling.
        .thenApply(response -> !response.next() ? 0 : response.getInt("len"))
        .join();

---

2. Execute the request through available adapters in org.itzstonlex.recon.sql.request.impl. Examples will be given below, or in the link directing to the tests, which is located at the very beginning of this page.

For example:

usersTable.createRequest()
        .insert()
        .push(ValuedField.create("FirstName", "Misha"))
        .push(ValuedField.create("LastName", "Leyn"))
        .push(ValuedField.create("Birthday", new Timestamp(System.currentTimeMillis())))
        .updateSync(usersTable.getConnection());

usersTable.createRequest()
        .select()
        .limit(1000) // no requirement.
        .pushMoreThen(ValuedField.create("Id", 5))
        .getResponseSync(usersTable.getConnection())

        .thenAccept(response -> {
            if (!response.next()) {
                connection.getLogger().warning("Response data is not found!");
                return;
            }

            response.doForEachOrdered(value -> {

                int userID = value.getInt("Id");
                long birthdayAsMillis = value.getTimestamp("Birthday").getTime();

                String username = (value.getString("FirstName") + " " + value.getString("LastName"));

                connection.getLogger().info("ID: " + userID + " | Name: " + username + " | Birthday: " + birthdayAsMillis);
            });
        });

int limit = 1000; // no requirement.
int count = usersTable.requestCount(limit, ValuedField.create("Id", 5));

---

Complex Requests

This functionality combines several possible queries into one complex one.

This will help optimize the number of requests

For example:

ReconSqlComplexRequest.newComplex()
        .pushComplex(usersTable.createRequest().insert()...)
        .pushComplex(usersTable.createRequest().update()...)
        .pushComplex(usersTable.createRequest().delete()...)
                
        .updateAsync(connection);

---

Request objects

First, let's create a test user that will store both primitive data and some kind of object.

For example:

@InjectionSql(table = "users")
public static class User {

    @FieldSql // For objects storage tests.
    private final User currentUser;

    @FieldSql(name = "name", indexes = IndexedField.IndexType.NOT_NULL)
    private final String username;

    @FieldSql(indexes = IndexedField.IndexType.NOT_NULL)
    private int age;

    private User(String name, int age) {
        this.username = name;
        this.age = age;

        this.currentUser = this;
    }

    public String getName() {
        return username;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

Great, now we can fully function with it as a target for querying the database.

But for this we need a manager that will handle the User object we just created

// Create object to request's parsing.
User user = new User("Misha Leyn", 18);

// Create sql-object services.
SqlObjectWorker worker = ReconSql.getInstance().newObjectWorker(connection);

Then, using the newly created SqlObjectWorker, we inject the object, creating its description: SqlObjectDescription.

For object inject example:

SqlObjectDescription<User> userSqlDescription = worker.injectObject(user);

Insert object to database:

// Insert user object to database and getting a user id.
if (userSqlDescription != null) {
    
    if (!worker.contains(userSqlDescription)) {
        worker.insert(userSqlDescription);
    }

    int userID = worker.getID(userSqlDescription);
    System.out.println("User ID: " + userID);
}
else {
    System.out.println("Cannot a find annotation @InjectionSql in User class.");
}

Update object data:

// Try to change user-object a fields values.
user.setAge(25);

if (userSqlDescription != null) {
    userSqlDescription.reinject(worker);

    // Execute UPDATE request to database-server.
    worker.update(userSqlDescription);
}

Getting object data from database:

// Check `users` table content.
userSqlDescription.getTable()
        .selectWhere(ValuedField.create("name", user.username))
        .thenAccept(response -> {

    if (!response.next()) {
        return;
    }

    int userID = response.getInt("id");
    int userAge = response.getInt("age");

    String username = response.getString("name");

    System.out.println("-------------------------------------------");
    System.out.println("ID: " + userID + " | Name: " + username + " | Age: " + userAge);
    System.out.println("User: " + response.getJsonObject("currentUser", User.class).getName());
});

and more...