Sql4j

This is a lightweight java library to simplify building and executing sql queries in java, without actually hard-coding any sql query in java code.

This library is published to Maven Central Repository where you can find Snippets for adding a dependency on this library in Maven, Gradle and other types of projects.

Usage

Suppose, in a database, we have a table TABLE_1 with two columns: COL_1 of type String (VARCHAR) and COL_2 of type Integer (INT).

static final Table TABLE_1 = Table.forName("TABLE_1");
static final Column<String> COL_1 = Column.forName("COL_1");
static final Column<Integer> COL_2 = Column.forName("COL_2");

We can run queries like the following:

List<String> results =
        SqlQuery.select(COL_1)
                .from(TABLE_1)
                .execute(connection, resultSet -> resultSet.getString(COL_1.getName()));

List<Row> results =
        SqlQuery.select(COL_1, COL_2)
                .from(TABLE_1)
                .where(COL_1.equalTo("test1")
                        .and(COL_2.lessThan(2)))
                .execute(connection, resultSet -> new Row(
                        resultSet.getString(COL_1.getName()),
                        resultSet.getInt(COL_2.getName())));
// Row is defined as:
record Row(String col1, int col2) {}

int numberOfDeletedRows =
        SqlQuery.delete()
                .from(TABLE_1)
                .where(COL_1.equalTo("test1"))
                .execute(connection);

int numberOfInsertedRows =
        SqlQuery.insert()
                .into(TABLE_1)
                .values(COL_1.value("test1"), COL_2.value(1))
                .execute(connection);

int numberOfUpdatedRows =
        SqlQuery.update(TABLE_1)
                .set(COL_2.value(2))
                .where(COL_1.equalTo("test1"))
                .execute(connection);

Where connection is a java.sql.Connection object that is created via java.sql.DriverManager.getConnection(<database url>, <database user>, <user's password>).

Checkout more examples in

• SelectQueryExecuteTest.java
• DeleteQueryExecuteTest.java
• InsertQueryExecuteTest.java
• UpdateQueryExecuteTest.java