org.ujorm.tools.jdbc

Class JdbcBuilder

java.lang.Object

org.ujorm.tools.jdbc.JdbcBuilder

All Implemented Interfaces:

Serializable

Direct Known Subclasses:

SqlBuilder

public class JdbcBuilder
extends Object
implements Serializable

PrepareStatement builder support

How to use a SELECT

 JdbcBuilder sql = new JdbcBuilder()
     .write("SELECT")
     .column("t.id")
     .column("t.name")
     .write("FROM testTable t WHERE")
     .andCondition("t.name", "=", "Test")
     .andCondition("t.created", ">=", someDate);
 for (ResultSet rs : sql.executeSelect(dbConnection)) {
      int id = rs.getInt(1);
      String name = rs.getString(2);
 }
 

How to use a INSERT

 JdbcBuilder sql = new JdbcBuilder()
     .write("INSERT INTO testTable (")
     .columnInsert("id", 10)
     .columnInsert("name", "Test")
     .columnInsert("date", someDate)
     .write(")");
 sql.executeUpdate(dbConnection);
 

How to use a UPDATE

 JdbcBuilder sql = new JdbcBuilder()
     .write("UPDATE testTable SET")
     .columnUpdate("name", "Test")
     .columnUpdate("date", SOME_DATE)
     .write("WHERE")
     .andCondition("id", "IN", 10, 20, 30)
     .andCondition("created BETWEEN ? AND ?", null, someDate, someDate.plusMonths(1))
     .andCondition("name", "IS NOT NULL")
 sql.executeUpdate(dbConnection);
 

For more information see a jUnit test.

See Also:

Serialized Form

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
protected static class	JdbcBuilder.MarkerEnvelope A value marker envelope
protected static class	JdbcBuilder.SqlEnvelope A SQL fragment

Field Summary

Fields

Modifier and Type	Field and Description
protected List<Object>	arguments Argument list
protected int	columnCounter Column counter
protected int	conditionCounter Condition counter
protected boolean	insertMode An insert sign for different rendering
static JdbcBuilder.SqlEnvelope	ITEM_SEPARATOR Separator of database columns
protected static char	SPACE A value marker for SQL
protected List<CharSequence>	sql SQL string fragments
protected static String	VALUE_MARKER A value marker for SQL

Constructor Summary

Constructors

Constructor and Description
JdbcBuilder() Default constructor
JdbcBuilder(List<CharSequence> sql, List<Object> arguments) Default constructor

Method Summary

Modifier and Type	Method and Description
JdbcBuilder	addArguments(Object... values) Add argument values with no SAPARATOR and no MARKER (for a common use)
protected JdbcBuilder	addValue(Object value) Add a value to SQL (inlucing MARKER)
JdbcBuilder	andCondition(CharSequence sqlCondition, String operator, Object... values) Add a condition for a multivalue argument joined by AND operator
JdbcBuilder	andCondition(CharSequence sqlCondition, String operator, Object value) Add a condition for a valid argument joined by AND operator
JdbcBuilder	column(CharSequence column) Add new column
JdbcBuilder	columnInsert(CharSequence column, Object value) Set new value to column by template {@code name = ?
JdbcBuilder	columnUpdate(CharSequence column, Object value) Set new value to column by template {@code name = ?
JdbcBuilder	condition(CharSequence sqlCondition, String operator, Object value) Add a condition for an argument with length
protected static ValuePrinter	createValuePrinter(StringBuilder result) Create a value printer
LoopingIterator<ResultSet>	executeSelect(Connection connection) Create an iterator ready to a loop statement for ( ; ; ) Supported SQL statements are: INSERT, UPDATE, DELETE .
<T> List<T>	executeSelect(Connection connection, JdbcFunction<T> function) Create a new result list
int	executeUpdate(Connection connection) Create statement and call PreparedStatement.executeUpdate() .
Object[]	getArguments() Returns an array of all JDBC arguments
protected int	getBufferSizeEstimation(boolean preview) Estimate a buffer size in characters
String	getSql() Returns a SQL statement
String	getSql(boolean preview) Returns a SQL text
JdbcBuilder	orCondition(CharSequence sqlCondition, String operator, Object... values) Add a condition for a multivalue argument joined by OR operator
JdbcBuilder	orCondition(CharSequence sqlCondition, String operator, Object value) Add a condition for a valid argument joined by OR operator
PreparedStatement	prepareStatement(Connection connection) Build the PreparedStatement with arguments
String	toString() Returns a SQL preview including values
<T> T	uniqueValue(Class<T> resultType, Connection connection) Return the first column value of a unique resultset, else returns null value
<T> Optional<T>	uniqueValueOptional(Class<T> resultType, Connection connection) Return the first column value of a unique resultset
JdbcBuilder	value(Object value) Add an argument value (including a SEPARATOR and a MARKER) for buidling a SQL INSERT statement
JdbcBuilder	write(CharSequence sqlFragment) Write a sql fragment including a space before
JdbcBuilder	write(JdbcBuilder builder) Add a another statement to the end of this statement.
JdbcBuilder	writeMany(CharSequence... sqlFragments) Write many sql fragments including a space before
JdbcBuilder	writeManyNoSpace(CharSequence... sqlFragments) Write many sql fragments with no space before
JdbcBuilder	writeNoSpace(CharSequence sqlFragment) Write a sql fragment with no space before
protected void	writeOperator(boolean andOperator, boolean enabled) Write an opetaror AND / OR

Methods inherited from class java.lang.Object

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

Field Detail

ITEM_SEPARATOR

public static final JdbcBuilder.SqlEnvelope ITEM_SEPARATOR

Separator of database columns

VALUE_MARKER

protected static final String VALUE_MARKER

A value marker for SQL

See Also:

Constant Field Values

SPACE

protected static final char SPACE

A value marker for SQL

See Also:

Constant Field Values

sql

@Nonnull
protected final List<CharSequence> sql

SQL string fragments

arguments

@Nonnull
protected final List<Object> arguments

Argument list

conditionCounter

protected int conditionCounter

Condition counter

columnCounter

protected int columnCounter

Column counter

insertMode

protected boolean insertMode

An insert sign for different rendering

Constructor Detail

JdbcBuilder

public JdbcBuilder()

Default constructor

JdbcBuilder

public JdbcBuilder(@Nonnull
                   List<CharSequence> sql,
                   @Nonnull
                   List<Object> arguments)

Default constructor

Method Detail

write

@Nonnull
public JdbcBuilder write(@Nonnull
                                  JdbcBuilder builder)

Add a another statement to the end of this statement.

write

@Nonnull
public JdbcBuilder write(@Nullable
                                  CharSequence sqlFragment)

Write a sql fragment including a space before

Parameters:

sqlFragment - An empty or null value is ignored.

writeNoSpace

@Nonnull
public JdbcBuilder writeNoSpace(@Nonnull
                                         CharSequence sqlFragment)

Write a sql fragment with no space before

Parameters:

sqlFragment - An empty or null fragment is ignored.

writeMany

@Nonnull
public JdbcBuilder writeMany(@Nonnull
                                      CharSequence... sqlFragments)

Write many sql fragments including a space before

writeManyNoSpace

@Nonnull
public JdbcBuilder writeManyNoSpace(@Nonnull
                                             CharSequence... sqlFragments)

Write many sql fragments with no space before

column

@Nonnull
public JdbcBuilder column(@Nonnull
                                   CharSequence column)

Add new column

columnUpdate

@Nonnull
public JdbcBuilder columnUpdate(@Nonnull
                                         CharSequence column,
                                         @Nonnull
                                         Object value)

Set new value to column by template {@code name = ?

columnInsert

@Nonnull
public JdbcBuilder columnInsert(@Nonnull
                                         CharSequence column,
                                         @Nonnull
                                         Object value)

Set new value to column by template {@code name = ?

andCondition

@Nonnull
public JdbcBuilder andCondition(@Nonnull
                                         CharSequence sqlCondition,
                                         @Nullable
                                         String operator,
                                         @Nullable
                                         Object value)

Add a condition for a valid argument joined by AND operator

Parameters:

sqlCondition - A condition in the SQL format like the next: "table.id = ?"

operator - An optional operator is followed by the VALUE_MARKER automatically

value - Add the value to arguments including a markup to the SQL statement. To ignore the value, send a null.

andCondition

@Nonnull
public JdbcBuilder andCondition(@Nonnull
                                         CharSequence sqlCondition,
                                         @Nonnull
                                         String operator,
                                         @Nullable
                                         Object... values)

Add a condition for a multivalue argument joined by AND operator

Parameters:

sqlCondition - A condition in the SQL format like the next: "table.id = ?"

operator - An optional operator is followed by the VALUE_MARKER automatically

values - The value of the condition (a replacement for the question character)

orCondition

@Nonnull
public JdbcBuilder orCondition(@Nonnull
                                        CharSequence sqlCondition,
                                        @Nullable
                                        String operator,
                                        @Nullable
                                        Object value)

Add a condition for a valid argument joined by OR operator

Parameters:

sqlCondition - A condition in the SQL format like the next: "table.id = ?"

operator - An optional operator is followed by the VALUE_MARKER automatically

value - Add the value to arguments including a markup to the SQL statement. To ignore the value, send a null.

orCondition

@Nonnull
public JdbcBuilder orCondition(@Nonnull
                                        CharSequence sqlCondition,
                                        @Nonnull
                                        String operator,
                                        @Nullable
                                        Object... values)

Add a condition for a multivalue argument joined by OR operator

Parameters:

sqlCondition - A condition in the SQL format like the next: "table.id = ?"

operator - An optional operator is followed by the VALUE_MARKER automatically

values - The value of the condition (a replacement for the question character)

condition

@Nonnull
public JdbcBuilder condition(@Nullable
                                      CharSequence sqlCondition,
                                      @Nullable
                                      String operator,
                                      @Nonnull
                                      Object value)

Add a condition for an argument with length

Parameters:

sqlCondition - A condition in the SQL format like the next: "table.id = ?". Send a null value to ignore the method.

operator - An optional operator is followed by the VALUE_MARKER automatically

value - Add a value to arguments including a markup to the SQL statement. To ignore the value, send a null. An array is supported

writeOperator

protected void writeOperator(@Nullable
                             boolean andOperator,
                             boolean enabled)

Write an opetaror AND / OR

Parameters:

andOperator -

enabled -

value

@Nonnull
public JdbcBuilder value(@Nonnull
                                  Object value)

Add an argument value (including a SEPARATOR and a MARKER) for buidling a SQL INSERT statement

See Also:

addArguments(java.lang.Object...)

addValue

@Nonnull
protected JdbcBuilder addValue(@Nullable
                                        Object value)

Add a value to SQL (inlucing MARKER)

Parameters:

value - A null value is ignored

See Also:

addArguments(java.lang.Object...)

addArguments

@Nonnull
public JdbcBuilder addArguments(@Nonnull
                                         Object... values)

Add argument values with no SAPARATOR and no MARKER (for a common use)

See Also:

value(java.lang.Object), writeNoSpace(java.lang.CharSequence)

getArguments

@Nonnull
public Object[] getArguments()

Returns an array of all JDBC arguments

Returns:

Array of arguments

prepareStatement

@Nonnull
public PreparedStatement prepareStatement(@Nonnull
                                                   Connection connection)
                                            throws SQLException

Build the PreparedStatement with arguments

Throws:

SQLException

executeSelect

@Nonnull
public LoopingIterator<ResultSet> executeSelect(@Nonnull
                                                         Connection connection)
                                                  throws IllegalStateException,
                                                         SQLException

Create an iterator ready to a loop statement for ( ; ; ) Supported SQL statements are: INSERT, UPDATE, DELETE .

Throws:

IllegalStateException

SQLException

executeSelect

@Nonnull
public <T> List<T> executeSelect(@Nonnull
                                          Connection connection,
                                          JdbcFunction<T> function)
                                   throws SQLException

Create a new result list

Throws:

SQLException

executeUpdate

public int executeUpdate(@Nonnull
                         Connection connection)
                  throws IllegalStateException

Create statement and call PreparedStatement.executeUpdate() . Supported SQL statements are: INSERT, UPDATE, DELETE .

Throws:

IllegalStateException

uniqueValue

@Nullable
public <T> T uniqueValue(@Nonnull
                                   Class<T> resultType,
                                   @Nonnull
                                   Connection connection)

Return the first column value of a unique resultset, else returns null value

uniqueValueOptional

@Nonnull
public <T> Optional<T> uniqueValueOptional(@Nonnull
                                                    Class<T> resultType,
                                                    @Nonnull
                                                    Connection connection)

Return the first column value of a unique resultset

getSql

@Nonnull
public String getSql(boolean preview)

Returns a SQL text

createValuePrinter

@Nonnull
protected static ValuePrinter createValuePrinter(@Nonnull
                                                          StringBuilder result)

Create a value printer

getBufferSizeEstimation

protected int getBufferSizeEstimation(boolean preview)

Estimate a buffer size in characters

getSql

@Nonnull
public String getSql()

Returns a SQL statement

toString

@Nonnull
public String toString()

Returns a SQL preview including values

Overrides:

toString in class Object