Package com.landawn.abacus.jdbc
Class SQLExecutor
java.lang.Object
com.landawn.abacus.jdbc.SQLExecutor
SQLExecutor is a simple sql/jdbc utility class. SQL is supported with different format:
All these kinds of SQLs can be generated by
========================================================================
If
Transaction can be started:
INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES (?, ?, ?, ?, ?)
INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES (#{firstName}, #{lastName}, #{gui}, #{lastUpdateTime}, #{createTime})
INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES (:firstName, :lastName, :gui, :lastUpdateTime, :createTime)
SQLBuilder
conveniently. Parameters with format of Object[]/List parameters are supported for parameterized SQL(id = ?
).
Parameters with format of Object[]/List/Map/Entity are supported for named parameterized SQL(id = :id
).
DO NOT use primitive array boolean[]/char[]/byte[]/short[]/int[]/long[]/float[]/double[]
for passing multiple parameters.
Here is sample of CRUD(create/read/update/delete):
========================================================================
static final DataSource dataSource = JdbcUtil.createDataSource(...);
static final SQLExecutor sqlExecutor = new SQLExecutor(dataSource);
...
Account account = createAccount();
// create
String sql_insert = NE.insert(GUI, FIRST_NAME, LAST_NAME, LAST_UPDATE_TIME, CREATE_TIME).into(Account.class).sql();
N.println(sql_insert);
sqlExecutor.insert(sql_insert, account);
// read
String sql_selectByGUI = NE.selectFrom(Account.class, N.asSet(DEVICES)).where(L.eq(GUI, L.QME)).sql();
N.println(sql_selectByGUI);
Account dbAccount = sqlExecutor.findFirst(Account.class, sql_selectByGUI, account);
assertEquals(account.getFirstName(), dbAccount.getFirstName());
// update
String sql_updateByLastName = NE.update(Account.class).set(FIRST_NAME).where(L.eq(LAST_NAME, L.QME)).sql();
N.println(sql_updateByLastName);
dbAccount.setFirstName("newFirstName");
sqlExecutor.update(sql_updateByLastName, dbAccount);
// delete
String sql_deleteByFirstName = NE.deleteFrom(Account.class).where(L.eq(FIRST_NAME, L.QME)).sql();
N.println(sql_deleteByFirstName);
sqlExecutor.update(sql_deleteByFirstName, dbAccount);
dbAccount = sqlExecutor.findFirst(Account.class, sql_selectByGUI, account);
assertNull(dbAccount);
========================================================================
If
conn
argument is null or not specified, SQLExecutor
is responsible to get the connection from the
internal DataSource
, start and commit/roll back transaction for batch operations if needed, and close the
connection finally. otherwise it's user's responsibility to do such jobs if conn
is specified and not null. Transaction can be started:
final SQLTransaction tran = sqlExecutor.beginTransaction(IsolationLevel.READ_COMMITTED);
try {
// sqlExecutor.insert(...);
// sqlExecutor.update(...);
// sqlExecutor.query(...);
tran.commit();
} finally {
// The connection will be automatically closed after the transaction is committed or rolled back.
tran.rollbackIfNotCommitted();
}
Spring Transaction is also supported and Integrated.
If a method of this class is called where a Spring transaction is started with the DataSource
inside this SQLExecutor
, without Connection
parameter specified,
the Connection
started the Spring Transaction will be used. Otherwise a Connection
directly from the inside DataSource
(Connection pool) will be borrowed and used.
SQLExecutor is tread-safe.- Since:
- 0.8
- Author:
- Haiyang Li
- See Also:
-
- JdbcUtil
ReadOnly
ReadOnlyId
NonUpdatable
Transient
Table
Column
ConditionFactory
ConditionFactory.CF
- http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html
- http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html
- http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html
- http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html
-
Method Summary