Building a Java Application with LeanXcale
The following sections show the basics of using LeanXcale database from a Java Application.
1. Prerequisites
The application in order to use the LeanXcale driver should meet the following requirements:
-
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 data ingestion, real-time aggregation, and linear horizontal scalability.
LeanXcale provides a JDBC driver to access. The following examples show 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 lines in the <dependency> section of the pom.xml file:
<dependency>
<groupId>com.leanxcale</groupId>
<artifactId>lxjdbcdriver</artifactId>
<version>3.0.2404110827</version>
</dependency>In the <version> section should refer to the version number you want to use.
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.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 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)