Building a Java Application with LeanXcale

The following sections show the basics of using LeanXcale database from a Java Application.

1. Prerequisites

The application using LeanXcale driver should meet the following prerequisites:

Java Development Kit (JDK) 11 or later is installed.

Samples use Apache Maven 3.3 or later.

2. How to Install the Driver and Write a CRUD

2.1. Introduction

LeanXcale is a 2003 ANSI SQL database. LeanXcale provides fast inserting/updating, real-time aggregation, and linear and transactional horizontal scalability.

LeanXcale provides a JDBC driver to access. The following examples show us a quick start for JAVA developers.

2.2. Driver Deploy

To use the JDBC driver, it is necessary to add the LeanXcale Maven repository into the Maven configuration. You can achieve this by inserting the following code lines within the <repositories> section of the pom.xml file:

<repositories>
    <repository>
        <id>maven-releases</id>
        <url>https://artifactory.leanxcale.com/artifactory/lx-public/</url>
    </repository>
</repositories>

Then add the maven dependency under the <dependencies> tag in the same file:

<dependencies>
  <dependency>
    <groupId>com.leanxcale</groupId>
    <artifactId>kivi-api</artifactId>
    <version>{minor-version}</version>
  </dependency>
</dependencies>

In an alternative approach, you have the option to download the LeanXcale Java JDBC driver from the official download page and then manually integrate it into your project as a library. Should you decide to follow this method, you can utilize the following command to add it within your local Maven repository:

<repositories>
  <repository>
    <id>lx-public</id>
    <url>https://artifactory.leanxcale.com/artifactory/lx-public/</url>
  </repository>
</repositories>

This approach is equivalent to the use of the Maven dependency defined previously.

Finally, JDBC driver can be used directly deployed on the JAVA_CLASSPATH.

2.3. Connect

When establishing a connection to the LeanXcale SQL interface through JDBC, you’ll need to provide several parameters in your JDBC URL. Here’s a breakdown of these parameters:

IP/hostname (e.g., 123.45.67.89)
Port (default: 14420)
Database name (DB)
Username
Password

When utilizing LeanXcale DBaaS, the IP address and hostname information can be found at LeanXcale Cloud Console.

By default, the connection is set to 'auto-commit,' which means that each individual SQL command is an individual transaction. To modify this default behavior, you can take the following steps:

Connection conn = DriverManager.getConnection("jdbc:leanxcale://123.45.67.89:14420/dbtest", "user1", "pass4user1");
conn.setAutoCommit(false);

2.4. Create, Alter and Drop a Table

For the creation, modification, or deletion of a database object, such as a table or sequence, use the execute() method.

2.4.1. Create

Here’s how you can create a table:

String create = "CREATE TABLE person (" +
	"id BIGINT NOT NULL, " +
	"name VARCHAR, " +
	"lastName VARCHAR, " +
	"birth_date DATE, " +
	"passport VARCHAR, " +
	"phone VARCHAR,, " +
	"PRIMARY KEY (id))";
try (Statement st = conn.createStatement()) {
	st.execute(create);
}

2.4.2. Alter

Here is a demonstration of how to modify a table:

String alter = "ALTER TABLE person ADD PARTITION(id) FOR VALUES(100)";
try (Statement st = conn.createStatement()) {
	st.execute(alter);
}

2.4.3. Drop

And now, this is how you drop the table previously created:

String drop = "DROP TABLE person";
try (Statement st = conn.createStatement()) {
	st.execute(drop);
}

2.5. Insert, Select, Update, and Delete Rows

For data manipulation tasks, encompassing operations like inserting, updating, or deleting records, the tool at your disposal is the executeUpdate() method. This method serves the purpose of executing these SQL statements, and in turn, it provides valuable feedback - specifically, the count of rows influenced by your insert, update, or delete action.

2.5.1. Insert

Now, let’s narrow our focus to how to insert one new record:

String insert = "INSERT INTO person VALUES (100, 'John', 'Doe', '1978-12-18', '666668888', '555333695')";
try (Statement st = conn.createStatement()) {
	st.executeUpdate(insert);
}

2.5.2. Upsert

LeanXcale provides upserts. Unlike traditional inserts, upserts do not perform a prior check for record existence. Instead, they directly proceed to either insert or update the description, resulting in faster performance.

String insert = "UPSERT INTO person VALUES (100, 'John', 'Doe', '1978-12-18', '666668888', '555333695')";
try (Statement st = conn.createStatement()) {
	st.executeUpdate(insert);
}

2.5.3. Select

When interacting with the database using SQL statements, leverage either a Statement or PreparedStatement instance. Once you have prepared the Statement or PreparedStatement, execute the query, which will return a ResultSet instance. This ResultSet instance allows you to retrieve the complete result of your query.

try (Statement st = conn.createStatement()) {
	int count = 0;
	try (ResultSet rs = st.executeQuery("SELECT * FROM person WHERE lastName = 'Doe'")) {
		while (rs.next()) {
			System.out.println("[" + count + "] " + rs.getString(1));
		}
	}
}

Here’s the same example, but with the use of a PreparedStatement, which involves the need to bind values within the query:

try (PreparedStatement pst = conn.prepareStatement("SELECT * FROM person WHERE lastName = ?")) {
	pst.setString(1, "Doe");
	try (ResultSet rs = pst.executeQuery()) {
		while (rs.next()) {
			System.out.print("Column 1 returned");
			System.out.println(rs.getString(1));
		}
	}
}

2.5.4. Join

In this instance, it retrieves information from multiple tables through the execution of a single query:

try(Statement stmt = conn.createStatement()) {
	String query = "SELECT title, name, isbn FROM author INNER JOIN book ON author.id = book.authorid";
	try (ResultSet rs = stmt.executeQuery(query)) {
		System.out.println("Title   Author  ISBN");
		while (rs.next()) {
			String title = rs.getString("title");
			String author = rs.getString("name");
			String isbn = rs.getString("isbn");
			System.out.printf("title: %s author: %s isbn: %s", title, author, isbn);
		}
	}
}

2.5.5. Update

Here is how to update existing records:

String update = "UPDATE person SET passport = '666668888' WHERE id in (100, 101)";
try (Statement st = conn.createStatement()) {
	st.executeUpdate(update);
}

2.5.6. Delete

This is the procedure for deleting records:

int id = 100;
try (PreparedStatement pst = conn.prepareStatement("DELETE FROM person WHERE id = ?")) {
	pst.setInt(1, id);
	int rowsDeleted = pst.executeUpdate();
	System.out.println("rows deleted " + rowsDeleted);
	conn.commit();
}

These are the fundamental operations you can perform with the LeanXcale JDBC driver. For more advanced functionality and the optimization of your database interactions according to your specific requirements, we encourage you to delve deeper into LeanXcale’s capabilities:

On "How to Ingest Effectively" you will find a guide on effective data ingestion using LeanXcale.

Refer to the Advance Deployment section for insights into specific details related to dealing with arrays in Java.
You can access the comprehensive SQL reference documentation SQL Reference.

3. Advanced Deployment

3.1. Array Support

LeanXcale enhances the standard JDBC functionality by seamlessly integrating array support, providing advanced data manipulation capabilities. This powerful feature simplifies data modeling and is particularly useful when dealing with structured data where you have to aggregate in relationship 1:n. By incorporating array columns into your database schema, you can manage all the array complexity by accessing any of the fields of the array individually, retrieving all the array values, or aggregating in relationships 1:n while maintaining compatibility with standard JDBC practices.

To know more about aggregations, read this.

3.1.1. Create a Table with Array Columns

With LeanXcale, creating a table with array columns is straightforward. You can define an array column in your create table statement using the array data type, allowing you to store collections of values in a single column.

String createTable = "CREATE TABLE tarry (" +
	"id INT, " +
	"longs BIGINT ARRAY, " +
	"doubles DOUBLE ARRAY, " +
	"strings VARCHAR ARRAY, " +
	"PRIMARY KEY(id))";
try (Statement st = conn.createStatement()) {
	st.execute(createTable);
}

3.1.2. Insert in a Table with Array Columns

This code snippet will illustrate how to harness array support to efficiently manage collections of values within your database.

String sql = "INSERT INTO tarry VALUES (" +
	"1, " +
	"array[-4367937811447089352, 7, 5575876413993699465], " +
	"array[0.023533916958532797, 5.476721734666199, 1.0], " +
	"array['MADRID','MURCIA','JEREZ'])";
 try (Statement st = conn.createStatement()) {
	 st.executeUpdate(sql); 
	conn.commit();
}

3.1.3. Select from a Table with Array Columns

The following code snippets will showcase how you can leverage the array support to retrieve and manipulate data.

Querying Arrays Values

Query Arrays Using Statement

You can get the array with a JDBC statement:

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT * FROM tarry WHERE ROWNUM = 1")) {
		if (rs.next()) {
			int id = rs.getInt("id");
			String typeLong = rs.getMetaData().getColumnTypeName(2);
			System.out.println("columnType :" + typeLong);      //BIGINT ARRAY
			Object[] longs = (Object[]) ((ArrayImpl) rs.getObject(2)).getArray();
			for (int i = 0; i < longs.length; i++) {
				long item = (long) longs[i];
				System.out.println("long[" + i + "]" + item);
			}
			String typeDouble = rs.getMetaData().getColumnTypeName(3);
			System.out.println("columnType :" + typeDouble);    //DOUBLE ARRAY
			Object[] doubles = (Object[]) ((ArrayImpl) rs.getObject(3)).getArray();
			for (int i = 0; i < doubles.length; i++) {
				double item = (double) doubles[i];
				System.out.println("double[" + i + "]" + item);
			}
			String typeString = rs.getMetaData().getColumnTypeName(4);
			System.out.println("columnType :" + typeString);    //VARCHAR ARRAY
			Object[] strings = (Object[]) ((ArrayImpl) rs.getObject(4)).getArray();
			for (int i = 0; i < strings.length; i++) {
				String item = (String) strings[i];
				System.out.println("string[" + i + "]" + item);
			}
		}
	}
}

Query Arrays Using PreparedStatement

This example shows how you would bind an array value using a Prepared Statement, making it easier to parameterize your queries.

try (PreparedStatement pst = conn.prepareStatement("SELECT id FROM tarry WHERE doubles = ?")) {
	Double[] values = {0.023533916958532797, 5.476721734666199, 1.0};
	Array sqlArray = conn.createArrayOf("DOUBLE", values);
	pst.setArray(1, sqlArray);
	try (ResultSet rs = pst.executeQuery()) {
		while (rs.next()) {
			int id = rs.getInt("id");
			System.out.println("id " + id);
		}
	}
}

Recovery of the Array Values as String

You can retrieve the values of arrays as strings, which can be particularly useful if you need to process or display them in a human-readable format.

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT id, TO_string(longs), TO_string(doubles), TO_string(strings) FROM tarry")) {
		while (rs.next()) {
			int id = rs.getInt("id");
			String longs = rs.getString(2);
			String doubles = rs.getString(3);
			String strings = rs.getString(4);
		}
	}
}

Filter Using the Array Column

Filter Using the Entire Column

You can filter all the records with an array column containing a specific array by constructing SQL queries that match the desired array values.

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT * FROM tarry WHERE strings = array['CUENCA', 'BILBAO']")) {
		while (rs.next()) {
			int id = rs.getInt(1);
			Array longs = rs.getArray(2);
			Array doubles = rs.getArray(3);
			Array strings = rs.getArray(4);
		}
	}
}

Filter all the Records with an Array Column Containing a Specific Array

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT id FROM tarry, UNNEST(tarry.strings) AS t(flattened_string) where flattened_string='MADRID]")) {
		while (rs.next()) {
			int id = rs.getInt(1);
			System.out.println("id " + id);
		}
	}
}

Filter by the Array Column Position

To filter records based on the value of a position of an array within a column, you can utilize LeanXcale’s features to retrieve them.

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT id FROM tarry WHERE longs[3] = 5575876413993699465")) {
		while (rs.next()) {
			int id = rs.getInt(1);
			System.out.println("id " + id);
		}
	}
}

These capabilities empower you to work effectively with array columns in LeanXcale, enabling you to handle complex data structures and perform operations straightforwardly.

4. LeanXcale NoSQL Database Integration with Hibernate ORM

Hibernate ORM is a robust Object-Relational Mapping (ORM) framework tailored for Java applications. Its core strength lies in ensuring efficient data persistence for relational databases.

The LeanXcale database seamlessly integrates with Hibernate ORM for Java applications, offering robust data persistence capabilities. This guide will walk you through getting started with Hibernate ORM for LeanXcale.

4.1. Prerequisites

The application using Hibernate ORM on LeanXcale should meet the following prerequisites:

Java Development Kit (JDK) 11 or later is installed.
Use Apache Maven Apache Maven 3.3 or later.
Use Hibernate 6.0 or later.

4.2. Create a CRUD with Hibernate and LeanXcale

Discover how to connect to your LeanXcale database and initiate fundamental CRUD operations by following the instructions on the Java ORM example application page. Subsequent sections will illustrate the essential procedures for Java application development employing Hibernate ORM while integrating seamlessly with LeanXcale.

4.2.1. Configure the Hibernate ORM Dependency

To use the Hibernate connector/dialect, include the dependency in your Maven configuration:

REVISAR

<dependency>
  <groupId>com.leanxcale.connectors</groupId>
  <artifactId>hibernate-connector</artifactId>
  <version>2.0.0</version>
</dependency>

Be sure to include the LeanXcale public Maven repository in your Maven configuration:

<repositories>
  <repository>
    <id>lx-public</id>
    <url>https://artifactory.leanxcale.com/artifactory/lx-public/</url>
  </repository>
</repositories>

If you don’t use Maven or prefer to include the libraries in your project manually, download the Hibernate Connector from the download page.

4.2.2. Map the Hibernate ORM

Create a class named Payment and use the following code. Please note that we will use Lombok @Data notation to simplify the definition

package com.leanxcale.cm.payment;


import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Data
@Entity
@Table(name = "payment")
public class Payment {

  @Id
  private Integer id;
  private String customer;
  private double amount;

}

4.2.3. Create a DAO Object for the Company Object

Establish a Data Access Object (DAO) named PaymentDAO.java. This DAO is the implementation hub for fundamental CRUD operations involving the Payment.java domain object. You can incorporate the provided sample code into your project for seamless integration with LeanXcale.

package com.leanxcale.cm.payment;

import java.util.Optional;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class PaymentDao { //FIXME delete!
  Session hibernateSession;

  public PaymentDao (Session session) {
    hibernateSession = session;
  }

  public void save(final Payment entity) {
    Transaction transaction = hibernateSession.beginTransaction();
    try {
      hibernateSession.save(entity);
      transaction.commit();
    } catch(RuntimeException rte) {
      transaction.rollback();
    }
  }

  public Optional<Payment> findById(final Integer id) {
    return Optional.ofNullable(hibernateSession.get(Payment.class, id));
  }

  public void delete(final Payment entity) {
    Transaction transaction = hibernateSession.beginTransaction();
    try {
      hibernateSession.remove(entity);
      transaction.commit();
    } catch(RuntimeException rte) {
      transaction.rollback();
    }
  }

  public void update(final Payment entity) {
    Transaction transaction = hibernateSession.beginTransaction();
    try {
      hibernateSession.merge(entity);
      transaction.commit();
    } catch(RuntimeException rte) {
      transaction.rollback();
    }
  }

  public void close(){
    if (hibernateSession.isOpen()){
      hibernateSession.close();
    }
  }
}

4.2.4. Set Hibernate Properties

Add the Hibernate configurations file hibernate.cfg.xml to the resources directory, and set up the following options.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

  <session-factory>
    <property name="hibernate.connection.driver_class">com.leanxcale.client.Driver</property>
    <property name="hibernate.connection.url">jdbc:leanxcale://localhost:14440/db;distribute=no</property>
    <property name="hibernate.connection.username">APP</property>
    <property name="hibernate.connection.password">APP</property>
    <property name="hibernate.dialect">com.leanxcale.connector.hibernate.LxDialect</property>
    <property name="show_sql">true</property>
    <property name="hbm2ddl.auto">update</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="hibernate.globally_quoted_identifiers">true</property>

    <mapping class="com.leanxcale.cm.payment.Payment"></mapping>

  </session-factory>
</hibernate-configuration>

HIBERNATE PARAMETER	DESCRIPTION	DEFAULT
hibernate.dialect	Dialect to use to generate SQL optimized for a particular database	com.leanxcale.connector.hibernate.LxDialect
hibernate.connection.driver_class	JDBC Driver name	com.leanxcale.client.Driver
hibernate.connection.url	JDBC Connection URL	jdbc:leanxcale://localhost:1530/db;distribute=no
hibernate.connection.username	Username	APP
hibernate.connection.password	Password	APP

HIBERNATE PARAMETER

DESCRIPTION

DEFAULT

hibernate.dialect

Dialect to use to generate SQL optimized for a particular database

com.leanxcale.connector.hibernate.LxDialect

hibernate.connection.driver_class

JDBC Driver name

com.leanxcale.client.Driver

hibernate.connection.url

JDBC Connection URL

jdbc:leanxcale://localhost:1530/db;distribute=no

hibernate.connection.username

Username

APP

hibernate.connection.password

Password

APP

Hibernate offers a comprehensive list of configuration properties that allows you to fine-tune various aspects and functionalities supported by the ORM. Please check the official Hibernate documentation.for more in-depth information and specific configurations.

4.2.5. Add the Object-Relational Mapping

Add a mapping for the Payment class in the hibernate.cfg.xml configuration file.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        ...
        <mapping class="com.leanxcale.model.Payment"/>
    </session-factory>
</hibernate-configuration>

4.2.6. To Query LeanXcale Database

Create a new class named QuickStartWithHibernate.java. Paste the provided sample code to execute queries on the table contents from your Java client using Hibernate ORM while connecting to your LeanXcale instance.

package com.leanxcale.cm.payment;

import java.sql.SQLException;
import java.util.Optional;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;

public class QuickStartWithHibernate {

  public static void main(String[] args) {
    SessionFactory sessionFactory = getSessionFactory();
    Session session = sessionFactory.openSession();

    System.out.println("Connected to the Leanxcale successfully.");
    PaymentDao paymentDao = new PaymentDao(session);
    // Save an payment
    Payment payment = new Payment();
    payment.setId(1);
    payment.setCustomer("Acme");
    payment.setAmount(123.45);
    paymentDao.save(payment);

    // Find the Payment
    Optional<Payment> byId = paymentDao.findById(1);
    if (byId.isPresent()) {
      System.out.println("Got Payment:" + payment.toString());
    }else {
      System.out.println("Payment not found:" + 1);
    }

    session.close();
}

  private static SessionFactory getSessionFactory() {
    StandardServiceRegistry standardRegistry = new StandardServiceRegistryBuilder().configure().build();
    Metadata metaData = new MetadataSources(standardRegistry).getMetadataBuilder().build();
    return metaData.getSessionFactoryBuilder().build();
  }
}

When you execute it, the application should output this:

2023-09-05 19:41:48,258 [main] INFO org.hibernate.orm.connections.access: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@5d2e6f62] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Hibernate: create table "payment" ("id" integer not null, "amount" double not null, "customer" varchar(255), primary key ("id"))
Connected to the Leanxcale successfully.
Hibernate: insert into "payment" ("amount","customer","id") values (?,?,?)
Got Payment:Payment(id=1, customer=Acme, amount=123.45)