EntityResultSetConverter internally holds ResultSet in ThreadLocal.
When working with a servlet, ThreadLocal is managed for each thread of a servlet, but because Servlet threads are pooled and used, the ThreadLocal field is not released and the stored ResultSet also remains unreleased It is closed.

If SQL exception occurs in postgresql, you can not execute new SQL without issuing rollback.
When uroborosql specifies a retry and SQL exception to be retried occurs, there is no issuance of rollback, so SQL exception occurs in the second SQL execution

I want to set the SQLID when using the SqlAget#queryWith method, but since it does not exist in the current SqlFluent interface, I have acquired SqlContext once in SqlAgent#contextWith.

It is nice to be able to specify SQLID with SqlFluent#sqlId method.

If you do not need file monitoring with NioSqlManager, you want to enable file monitoring off.

I want to execute batch sql using entity, but it isn't exists.
Please add entity support for executing batch sql.

// For insert
int insertBatch(List<?> entityList);

// For update
int updateBatch(List<?> entityList);

Although SqlAgent#close() internally performs Connection#close(), depending on the DB (postgresql, etc.) if you close without commit / rollback, the resource remains locked.
You should commit / rollback explicitly before close.

Cited document
https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close()
It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined.

Present implementation doesn't handle fields other than String or int.

If LocalTransactionManager#getPreparedStatement() fails to generate preparedStatement,
caller returns NullPointerException if it returns null but does not throw SQLException instead

I Want to use the JDBC interface to insert data into the table that has array type column, retrieve it, and update it in PostgreSQL.
reference pace

I am glad that you can implement SqlFluent#param's value in Lambda.

I would like to implement it as below.

sqlAgent.query("xxxx")
    .param("name", () -> condition ? "Tanaka" : "Yamada")
    .collect(Collectors.toList);

When Query or Update is executed repeatedly for one Sql Agent, the Queue of the PreparedStatement in the LocalTransactionContext is not cleared and the memory is enlarged.

In MapResultSetConverter, Date & Time API support is not available, for example, EntityResultSetConverter can convert to LocalDateTime or ZonedDateTime if the DB is of type timestamp, whereas in MapResultSetConveter it will bejava.sql.Timestamp.

For applications in Java 8 and later, since you think that there are many cases you want with ZonedDateTime, please consider.

SecretColumnFilter doesn't support any block-cipher modes which need initialization vector.
A Cipher object is initialized with SecretColumnFilter#initialize() method without any algorithm parameters.
I want to use CBC mode instead of ECB.

When using Postgresql, ColumnLabel and ColumnName of metadata do not match case when getting Metadata of Table using EntityResultSetConverter, so table information can not be acquired correctly.

The following stored function call fails in Oracle

{/*ret*/ = call func_xxx (/*param1*/}

The cause is that the bind parameter has been replaced
{?/*ret*/ = call func_xxx (?/*param1*/}
However, including a comment between "? =" Causes a grammar error.
{? = call func_xxx (?/*param1*/}
Can be executed if

Shutdown of threads launched inside NioSqlManager is not done. Therefore, there are cases where the process does not end and the ghost process is awaited for waiting for thread termination.

		// The UPDATE statement will updates base on following query results.
		final SqlUpdate update = agent().update("Some update sql");

		// A query gets huge number of records.
		query.stream(new MapResultSetConverter(CaseFormat.CAMEL_CASE))
				.peek(r -> {
					// Query results relayed to UPDATE.
					update.paramMap(r).addBatch();

					//FIXME: I want to Execute 'batch()' every each 1000 parameters.
					if (update.context().batchCount() == 1000) {
						update.batch();
					}
				})
				.count();

		// for surplus results of above query
		update.batch();

For example:

		// specify frame size to 1000
		final SqlUpdate update = agent().update("Some update sql").batchFrame(1000);

		query.stream(new MapResultSetConverter(CaseFormat.CAMEL_CASE))
				.peek(r -> update.paramMap(r).addBatch().batch()) // this 'batch' will make sence only for each 1000 parameters
				.count();

		// remove frame and execute 'batch()'
		update.unframe().batch(); // or ".batchFrame(1)"

Another Solution:

		query.stream(new MapResultSetConverter(CaseFormat.CAMEL_CASE))
				.peek(r -> update.paramMap(r).addBatch().batchEvery(1000)) 
				.count();

		update.batch();

Incidentally: If I can use Stream#onClose

		query.stream(new MapResultSetConverter(CaseFormat.CAMEL_CASE))
				.onClose(() -> update.batch())
				.peek(r -> update.paramMap(r).addBatch().batchEvery(1000)) 
				.count();

		// update.batch(); // This is no longer needed.

When generating a Stream from a query, creating an iterator from a Stream, and iterating, an error occurs at the last hasNext().

The following code will not cause an error,

sqlAgent
    .queryWith("...")
    .stream()
    .iterator()

But, the following code will cause an error.

sqlAgent
    .queryWith("...")
    .stream()
    .map(r -> r)
    .iterator()

概要

where句に「OR」始まりのカラム名が含まれる場合、「OR」を除いたカラム名でSQLが実行される

再現SQL例

SELECT
    ORD_NO
FROM
    ORD_LIST
WHERE
    ORD_NO  =   1

バージョン

0.2.0

改修対象ソース

SqlContextImpl.java

必要と思われる改修

改修前：
protected static final Pattern WHERE_CLAUSE_PATTERN = Pattern
.compile("(?i)(WHERE(\s+(/\.\/|--.)+)*\s+)(AND|OR)");

改修後：
protected static final Pattern WHERE_CLAUSE_PATTERN = Pattern
.compile("(?i)(WHERE(\s+(/\.\/|--.)+)*\s+)(AND|OR)\s");

In entity interface update method, make it possible to exclude a specific column.
Entity code sample

@Transient(insert=false update=true)
private String createdUser;

Since there are cases where processing contents and SQL to be constructed are changed depending on the type of Database, we want to add an API for handling the dialect of Database.

When executing a procedure using MariaDB Connector / J, if FetchSize is specified for CallableStatement, a NullPointerException occurs with close () of CallableStatement.
This is a bug in MariaDB Connector / J.
To avoid this bug, please do not set FetchSize specification in SqlAgentFactory to CallableStatement because FetchSize specification is meaningless in CallableStatement.

I want to handling failure of pecimistic lock especially.

sqlConfig.getSqlAgentFactory().setSqlRetryCodeList(Arrays.asList("54", "30006"));

try {
    agent.update("example/insert_product").param("product_id", 1).retry(5, 20).count();
} catch (PecimistickLockException e) { // explicit exception
    ...
}

or

try {
    agent.update("example/insert_product").param("product_id", 1).retry(5, 20).count();
} catch (UroborosqlSQLException e) {
    if(agent.isRetryCode(e.getErrorCode())) { // some way to determine RetryCodeList
        ...
}

When the result is null, have you assigned 0 to Integer?
If the result is null if possible, please assign null to Integer.

I used SqlAgent.query().param().collect()

The way I am thinking fix, it is as follows.

PropertyMapperManager.class

Before

	if (Integer.class.equals(rawType) || int.class.equals(rawType)) {
		return rs.getInt(columnIndex);
	}

After

        if (Integer.class.equals(rawType)) {
            int result = rs.getInt(columnIndex);
            if (rs.wasNull()) {
                return null;
            }
            return result;
        }
        if (int.class.equals(rawType)) {
            return rs.getInt(columnIndex);
        }

Add extensible points to SecretColumnSqlFilter#doParameter to customize the method used for encryption.

If you select columns that contain CLOBs or BLOBs with agent.query().collect() ,
you can get instances of java.sql.Clob or java.sql.Blob.
At the retrieval source, it is necessary to describe processing to acquire column values from Clob and Blob afterwards,
but since it becomes redundant, please go through ResultSetConverter until obtaining the column value.

The target column type is as follows

• java.sql.Clob -> String
• java.sql.NClob -> String
• java.sql.Blob -> byte []
• java.sql.Array -> (BaseType) []

In SqlQuery#collect(), SqlQuery#find(), SqlQuery#findFirst(), the return value key is generated with CaseFormat#UPPER_SNAKE_CASE.
To generate a key in a format other than UPPER_SNAKE_CASE, you need to specify CaseFormat as a method argument.
However, since to specify CaseFormat whenever you call a method is redundant, I want to be able to specify the CaseFormat which is used as an initial value.

Customizing SqlId results in an error

Example of input format

date=2017-07-10T01:39:40

When DataSourceConnectionSupplier is used, Connection is not closed even if SqlAgent#close () is called, and the connection is exhausted.

SecretColumnSqlFilter now supports only local file system.

I want use URI like https://~.

related with #93
Because transformContext method is called before autoParameterBinder is accepted, the "IF" statement will be evaluated before it is added to the bind parameter.

// コンテキスト変換
transformContext(sqlContext); // before autoParameterBinder is accepted.
StopWatch watch = null;
try (PreparedStatement stmt = getPreparedStatement(sqlContext)) {
	// INパラメータ設定
	sqlContext.bindParams(stmt);
	if (LOG.isDebugEnabled()) {
		LOG.debug("Execute update SQL.");
		watch = new StopWatch();
		watch.start();
	}

Currently, the SqlAgent#delete method is int delete (Object entity) and takes an entity class as an argument, but it seems that there may be an overloaded method which executes by passing only the primary key .

The signature you want to add is below.

<E> int delete(Class<? extends E> entityType, Object... keys)

Since SqlEntityQuery only supports equality of extraction conditions, why not add the following support?

For example, when inserting to a table with a column of auto increment type such as PostgreSQL, it gets an instance of PreparedStatement with Connection#prepareStatement(String, int) and it is generated by Statement#getGeneratedKeys() after issuing INSERT statement You can get the value, uroboroSQL does not have an API to get this, so I would like you to add it.

What you want to do is JDBC and it will be implemented as follows.

long key = -1L;
Statement statement = connection.createStatement();
statement.executeUpdate(YOUR_SQL_HERE, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
if (rs != null && rs.next()) {
    key = rs.getLong(1);
}

I quoted from here.

https://stackoverflow.com/questions/4224228/preparedstatement-with-statement-return-generated-keys

Since SqlQuery#first does not return Optional, I want to add it.

For example, it is the image below.

$ uroborlosql> generate select from users

SELECT
  USER.ID    AS ID
, USER.NAME  AS NAME
, USER.EMAIL AS EMAIL
FROM
  USERS

It is a code like the following.

agent.query("owners-find")
    .param("lastName", lastName)
    .collect(CaseFormat.LowerSnakeCase);

In the case of SpringBoot, when returning JSON with the Controller, there are cases where lowercase letters of the map key are ideal.

The number of returned array matches the execution number of SQL.
However, all contents are -2.

In order to make it easy to use in multi DB, add a mechanism to switch SQL referenced by Dialect.

example)

sql/
  sample/
    sample_query.sql

  oracle/
    sample/
      sample_query.sql

  postgresql/
    sample/
      sample_query.sql

• In Oracle, use sql/oracle/sample/sample_query.sql
• In Postgresql, use sql/postgresql/sample/sample_query.sql
• Other(H2 etc.), use default, sql/sample/sample_query.sql

For example, in SpringBoot, I would like to use SqlConfig using DataSource obtained by DI.
I want DefaultSqlConfig#getConfig() to accept the DataSource class.

Since I'm automatically assigning a primary key in PostgreSQL serial type, I added @Transient to exclude id column from INSERT statement at SqlAgent#insert.

INSERT will now work, but this time I encountered a bug that will be excluded from the result of SqlAgent#find, so I would like to ask you to fix it.

// Entity class
@Table(name = "hoge_tables")
public class HogeTable {
	@Transient
	@NotNull
	private Integer id;
...
}

// My logic
HogeTable hoge = sqlConfig.agent().find(HogeTable.class, 1);
System.out.println(hoge.getId()); // null

I want to log number of rows returned by the query.
If ResultSet closed during processing, that should be the number of fetched at that point.

SqlAgentImpl#query(SqlContext) doesn't close ResultSet when SqlFilter/SqlFilterManager#doQuery throws any Exception.

try {
	return new InnerResultSet(getSqlFilterManager().doQuery(sqlContext, stmt, stmt.executeQuery()), stmt);
} catch (SQLException ex) {

CaseFormat # LOWER_SNAKE_CASE.convert ("UPPER") returns "u_p_p_e_r".
I want you to be "upper" correctly.

in REPL.
Specify the column name for optimistic lock in the property file.
If there is an optimistic lock column name, SQL for optimistic locking is output