Package com.landawn.abacus.jdbc

Class SQLExecutor

java.lang.Object
com.landawn.abacus.jdbc.SQLExecutor
public final class SQLExecutor extends Object
SQLExecutor is a simple sql/jdbc utility class. SQL is supported with different format: 

 
  •  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)
    • 

 All these kinds of SQLs can be generated by 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: