SqlUtil

• Use this method when exactly one matching record is expected to exist.
• Throws an exception if the result is zero records.
• Throws an exception if multiple records are returned.

• Use this method when exactly one matching record is expected to exist.
• Returns null if the result is zero records.
• Throws an exception if multiple records are returned.
• Physical field names are in lowercase. (key rule of AbstractIoTypeMap)

• Returns null if the result is zero records.
• Returns the first record without error even if multiple records are retrieved.
• Physical field names are in lowercase. (key rule of AbstractIoTypeMap)

• Returns a SqlResultSet.
• Physical field names of row maps obtained from the SqlResultSet iterator are in lowercase. (key rule of AbstractIoTypeMap)
• Use in a try statement (try-with-resources).
• The default fetch size in this class is 500. Use SqlUtil#selectFetchAll(Connection, SqlBean) to fetch all records.
• Notes on fetch size per DBMS:
  • Oracle defaults to 10, which is small, so specify the fetch size.
  • PostgreSQL defaults to fetching all records, which may cause OutOfMemory, so specify the fetch size.
  • With PostgreSQL, specifying a fetch size (without fetching all), updating retrieved data, and performing intermediate commits causes a cursor invalidation error (SQLSTATE 34000). In that case, stop intermediate commits or fetch all records.
    Alternatively, though more complex, splitting data retrieval using a SQL LIMIT clause also resolves the issue.
  • With MS SQL Server, specifying a fetch size may not work as expected, so use a SQL LIMIT clause to split data retrieval if OutOfMemory is a concern.

[Example]
try (final SqlResultSet rSet = SqlUtil.select(getDbConn(), sqlBuilder);) {
  for (final IoItems row : rSet) {
    : (omitted)
  }
  if (rSet.getReadedCount() <= 0) {
    // Zero records.
  }
}

• Do not use this method in general.
• Use this method only when issues occur without fetching all records.
• Retrieving a large number of records with this method may cause an OutOfMemory error.

• Returns a list of multiple rows.
• Returns an empty list if the result is zero records.
• Physical field names of each row map are in lowercase. (key rule of AbstractIoTypeMap)
• Use #select(Connection, SqlBean) for loop processing since this method consumes memory.
• Retrieving a large number of records with this method may cause an OutOfMemory error.

• Returns a list of multiple rows.
• Returns an empty list if the result is zero records.
• Physical field names of each row map are in lowercase. (key rule of AbstractIoTypeMap)
• Use #select(Connection, SqlBean) for loop processing since this method consumes memory.
• Retrieving a large number of records with this method may cause an OutOfMemory error.

• Inserts one record into the specified table.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be inserted into the new column without modifying the implementation.
• Throws an exception if the number of affected rows is zero for reasons other than a unique constraint violation.

• Inserts one record into the specified table.
• Sets the timestamp for optimistic locking.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be inserted into the new column without modifying the implementation.
• Throws an exception if the number of affected rows is zero for reasons other than a unique constraint violation.

• Updates one record in the specified table.
• Throws an exception if multiple records are updated.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be updated in the new column without modifying the implementation.
• A WHERE clause is built using the key columns.
• Key columns MUST be included in the parameter values.
• Key column names MUST be specified in lowercase. (key rule of AbstractIoTypeMap)

• Updates one record in the specified table.
• Throws an exception if multiple records are updated.
• Performs optimistic locking using a timestamp.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be updated in the new column without modifying the implementation.
• A WHERE clause is built using the key columns and the timestamp column (for locking).
• Key columns and the timestamp column MUST be included in the parameter values.
• Key column names and the timestamp column name MUST be specified in lowercase. (key rule of AbstractIoTypeMap)
• The timestamp column is updated to the current date and time.
• Use #updateOne(Connection, String, AbstractIoTypeMap, String[]) if timestamp optimistic locking is not required.

• Updates one record in the specified table.
• Throws an exception if multiple records are updated.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be updated in the new column without modifying the implementation.
• A WHERE clause is built using the primary key columns of the table.
• Throws an exception if the table has no primary key.
• Primary key columns MUST be included in the parameter values.

• Updates one record in the specified table.
• Throws an exception if multiple records are updated.
• Performs optimistic locking using a timestamp.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be updated in the new column without modifying the implementation.
• A WHERE clause is built using the primary key columns and the timestamp column (for locking).
• Throws an exception if the table has no primary key.
• Primary key columns and the timestamp column MUST be included in the parameter values.
• The timestamp column name MUST be specified in lowercase. (key rule of AbstractIoTypeMap)
• The timestamp column is updated to the current date and time.
• Use #updateOneByPkey(Connection, String, AbstractIoTypeMap) if timestamp optimistic locking is not required.

• Updates multiple records in the specified table.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be updated in the new column without modifying the implementation.
• A WHERE clause is built using the search condition columns.
• Search condition columns MUST be included in the parameter values.
• Search condition column names MUST be specified in lowercase. (key rule of AbstractIoTypeMap)

• Updates all records in the specified table.
• Parameters not present in the table are ignored.
• Note: if a column is added to the table after implementation and that column name already exists in the parameters, values will be updated in the new column without modifying the implementation.

• Deletes one record from the specified table.
• Throws an exception if multiple records are deleted.
• A WHERE clause is built using the key columns.
• Key columns MUST be included in the parameter values.
• Parameters that are not key columns are ignored.
• Key column names MUST be specified in lowercase. (key rule of AbstractIoTypeMap)

• Deletes one record from the specified table.
• Throws an exception if multiple records are deleted.
• Performs optimistic locking using a timestamp.
• A WHERE clause is built using the key columns and the timestamp column (for locking).
• Key columns and the timestamp column MUST be included in the parameter values.
• Parameters that are neither key columns nor the timestamp column are ignored.
• Key column names and the timestamp column name MUST be specified in lowercase. (key rule of AbstractIoTypeMap)
• Use #deleteOne(Connection, String, AbstractIoTypeMap, String[]) if timestamp optimistic locking is not required.

• Deletes one record from the specified table.
• Throws an exception if multiple records are deleted.
• A WHERE clause is built using the primary key columns of the table.
• Throws an exception if the table has no primary key.
• Primary key columns MUST be included in the parameter values.
• Parameters that are not primary key columns are ignored.

• Deletes one record from the specified table.
• Throws an exception if multiple records are deleted.
• Performs optimistic locking using a timestamp.
• A WHERE clause is built using the primary key columns and the timestamp column (for locking).
• Throws an exception if the table has no primary key.
• Primary key columns and the timestamp column MUST be included in the parameter values.
• Parameters that are neither primary key columns nor the timestamp column are ignored.
• The timestamp column name MUST be specified in lowercase. (key rule of AbstractIoTypeMap)
• Use #deleteOneByPkey(Connection, String, AbstractIoTypeMap) if timestamp optimistic locking is not required.

• Deletes multiple records from the specified table.
• A WHERE clause is built using the search condition columns.
• Search condition columns MUST be included in the parameter values.
• Search condition column names MUST be specified in lowercase. (key rule of AbstractIoTypeMap)

• Deletes all records from the specified table.

• 反映件数が複数件の場合は例外エラーとする。