1. How to Install the Driver and Write a CRUD

1.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.

1.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.

1.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);

1.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.

1.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);
}

1.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);
}

1.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);
}

1.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.

1.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);
}

1.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);
}

1.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));
		}
	}
}

1.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);
		}
	}
}

1.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);
}

1.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:

  • 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.