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.

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

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

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

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

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

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.