Package org.ujorm.tools.sql

Class SqlParamBuilder

java.lang.Object

org.ujorm.tools.sql.SqlParamBuilder

All Implemented Interfaces:

AutoCloseable

public class SqlParamBuilder
extends Object
implements AutoCloseable

Very light class to simplify work with JDBC. Original source: GitHub Licence: Apache License, Version 2.0

Sample of usage

 try (var builder = new SqlParamBuilder(dbConnection)) {
      List<Employee> employees = builder.sql("""
                SELECT t.id, t.name, t.created
                FROM employee t
                WHERE t.id > :id
                  AND t.code IN (:code)
                ORDER BY t.id
                """)
        .bind("id", 10)
        .bind("code", "T", "V")
        .streamMap(rs -> new Employee(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getObject("created", LocalDate.class)))
        .toList();
 }
 

Since:

2.26

Author:

Pavel Ponec, https://github.com/pponec

Field Summary

Fields

Modifier and Type	Field	Description
protected @Nullable String	sqlTemplate

Constructor Summary

Constructors

Constructor	Description
SqlParamBuilder(@NotNull Connection dbConnection)

Method Summary

Modifier and Type	Method	Description
SqlParamBuilder	bind(boolean enabled, @NotNull String key, Boolean... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, Byte... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, Integer... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, Long... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, Short... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, String... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, BigDecimal... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, LocalDate... values)
SqlParamBuilder	bind(boolean enabled, @NotNull String key, LocalDateTime... values)
SqlParamBuilder	bind(@NotNull String key, Boolean... values)
SqlParamBuilder	bind(@NotNull String key, Byte... values)	Bind Bytes
SqlParamBuilder	bind(@NotNull String key, Integer... values)	Bind Ingegers
SqlParamBuilder	bind(@NotNull String key, Long... values)	Bind Longs
SqlParamBuilder	bind(@NotNull String key, Short... values)	Bind Shorts
SqlParamBuilder	bind(@NotNull String key, String... values)	Bind String
SqlParamBuilder	bind(@NotNull String key, BigDecimal... values)	Bind BigDecimal
SqlParamBuilder	bind(@NotNull String key, LocalDate... values)	Bind LocalDates
SqlParamBuilder	bind(@NotNull String key, LocalDateTime... values)	Bind LocalDateTimes
SqlParamBuilder	bindObject(boolean enabled, @NotNull String key, JDBCType jdbcType, Object... values)	Assigns SQL parameter values.
SqlParamBuilder	bindObject(@NotNull String key, Object... values)	Bind Objects
protected @NotNull String	buildSql(List<org.ujorm.tools.sql.SqlParamBuilder.ParamValue> sqlValues, boolean toLog)
void	close()	The method closes a PreparedStatement object with related objects, not the database connection.
int	execute()
int	executeInsert()	For INSERT operations used before calling method #generatedKeysRs.
void	forEach(@NotNull SqlConsumer consumer)	Iterate executed select
<R> @NotNull Stream<R>	generatedKeys(SqlFunction<ResultSet,? extends R> mapper)	Method for retrieving the primary keys of an INSERT statement.
protected @Nullable ResultSet	generatedKeysRs()
@NotNull PreparedStatement	prepareStatement(int autoGeneratedKeys)	Build (or reuse) a PreparedStatement object with SQL arguments
SqlParamBuilder	sql(@NotNull String... sqlLines)	Close an old statement (if any) and assign the new SQL template
protected static SQLException	sqlException(@NotNull String messages, @Nullable Exception ex)
protected static SQLException	sqlException(@Nullable SQLException ex)
@NotNull String	sqlTemplate()
<R> @NotNull Stream<R>	streamMap(SqlFunction<ResultSet,? extends R> mapper)
@NotNull String	toString()
String	toStringLine()

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Details

sqlTemplate

@Nullable
protected @Nullable String sqlTemplate

Constructor Details

SqlParamBuilder

public SqlParamBuilder(@NotNull
 @NotNull Connection dbConnection)

Method Details

sql

public SqlParamBuilder sql(@NotNull
 @NotNull String... sqlLines)

Close an old statement (if any) and assign the new SQL template

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 Boolean... values)

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 Boolean... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 Byte... values)

Bind Bytes

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 Byte... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 Short... values)

Bind Shorts

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 Short... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 Integer... values)

Bind Ingegers

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 Integer... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 Long... values)

Bind Longs

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 Long... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 BigDecimal... values)

Bind BigDecimal

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 BigDecimal... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 String... values)

Bind String

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 String... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 LocalDate... values)

Bind LocalDates

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 LocalDate... values)

bind

public SqlParamBuilder bind(@NotNull
 @NotNull String key,
 LocalDateTime... values)

Bind LocalDateTimes

bind

public SqlParamBuilder bind(boolean enabled,
 @NotNull
 @NotNull String key,
 LocalDateTime... values)

bindObject

public SqlParamBuilder bindObject(@NotNull
 @NotNull String key,
 Object... values)

Bind Objects

bindObject

public SqlParamBuilder bindObject(boolean enabled,
 @NotNull
 @NotNull String key,
 JDBCType jdbcType,
 Object... values)

Assigns SQL parameter values. If reusing a statement, ensure the same number of parameters is set.

execute

public int execute()

executeInsert

public int executeInsert()

For INSERT operations used before calling method #generatedKeysRs.

forEach

public void forEach(@NotNull
 @NotNull SqlConsumer consumer)
             throws SQLException

Iterate executed select

Throws:

SQLException

streamMap

@NotNull
public <R> @NotNull Stream<R> streamMap(SqlFunction<ResultSet,? extends R> mapper)

close

public void close()

The method closes a PreparedStatement object with related objects, not the database connection.

Specified by:

close in interface AutoCloseable

prepareStatement

@NotNull
public @NotNull PreparedStatement prepareStatement(int autoGeneratedKeys)

Build (or reuse) a PreparedStatement object with SQL arguments

Parameters:

autoGeneratedKeys - For example: Statement.RETURN_GENERATED_KEYS

generatedKeysRs

@Nullable
protected @Nullable ResultSet generatedKeysRs()

generatedKeys

@NotNull
public <R> @NotNull Stream<R> generatedKeys(SqlFunction<ResultSet,? extends R> mapper)

Method for retrieving the primary keys of an INSERT statement. Only one call per INSERT is allowed.
Usage: builder.generatedKeys(rs -> rs.getInt(1)).findFirst()

buildSql

@NotNull
protected @NotNull String buildSql(List<org.ujorm.tools.sql.SqlParamBuilder.ParamValue> sqlValues,
 boolean toLog)

sqlTemplate

@NotNull
public @NotNull String sqlTemplate()

sqlException

protected static SQLException sqlException(@Nullable
 @Nullable SQLException ex)

sqlException

protected static SQLException sqlException(@NotNull
 @NotNull String messages,
 @Nullable
 @Nullable Exception ex)

toString

@NotNull
public @NotNull String toString()

Overrides:

toString in class Object

toStringLine

public String toStringLine()