Programming in RDBMS with Jdbc, Dao, PreparedQuery and SQLExecutor Mapper

Here are the CRUD(Create/Read/Update/Delete) samples with PreparedQuery/SQLExecutor/Mapper

CRUD by JdbcUtil.PreparedQuery.

String sql_insert = "INSERT INTO account (gui, email_address, first_name, middle_name, last_name, birth_date, status, last_update_time, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
long id = JdbcUtil.prepareQuery(dataSource, sql_insert, true)
        .setParameters(1, account.getGUI(), account.getEmailAddress(), account.getFirstName(), account.getMiddleName(), account.getLastName())
        .setTimestamp(6, account.getBirthDate())
        .setInt(7, account.getStatus())
        .setTimestamp(8, account.getLastUpdateTime())
        .setTimestamp(9, account.getCreateTime())
        .<Long> insert()
        .orElseThrow();

String sql_get = RE.select("id", "firstName", "lastName").from(Account.class).where("id = ?").sql();
Account dbAccount = JdbcUtil.prepareQuery(dataSource, sql_get).setLong(1, id).get(Account.class).orElseThrow();

String sql_update = RE.update(Account.class).set("firstName").where("id = ?").sql();
JdbcUtil.prepareQuery(dataSource, sql_update).setString(1, "updatedFirstName").setLong(2, id).update();

String sql_delete = RE.deleteFrom(Account.class).where("id = ?").sql();
JdbcUtil.prepareQuery(dataSource, sql_delete).setLong(1, id).update();

CRUD by SQLExecutor.

String sql_insert = NE.insertInto(Account.class, N.asSet("id")).sql();
long id = sqlExecutor.insert(sql_insert, account);

String sql_get = NE.select("id", "firstName", "lastName").from(Account.class).where("id = ?").sql();
Account dbAccount = sqlExecutor.get(Account.class, sql_get, id).orElseThrow();

dbAccount.setFirstName("updatedFirstName");
String sql_update = NE.update(Account.class).set("firstName").where("id = :id").sql();
sqlExecutor.update(sql_update, dbAccount);

String sql_delete = NE.deleteFrom(Account.class).where("id = ?").sql();
sqlExecutor.update(sql_delete, id);

CRUD by SQLExecutor.Mapper.

final Mapper<Account> mapper = sqlExecutor.mapper(Account.class);

mapper.add(account);

Account dbAccount = mapper.gett(account.getId());

dbAccount.setFirstName("updatedFirstName");
mapper.update(dbAccount, N.asList("firstName"));

mapper.delete(dbAccount);

CRUD by Jdbc.Dao.

public interface AccountDao extends JdbcUtil.Dao { // Or DBUtil.Dao for Spring.
    @Insert("INSERT INTO account (gui, email_address, first_name, middle_name, last_name, birth_date, status, last_update_time, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
    long insert(String gui, String emailAddress, String firstName, String middleName, String lastName, Date birthDate, int status, Timestamp lastUpdateTime, Timestamp createTime) throws SQLException;
    
    @Select("select first_name, last_name from account where id = ?")
    Account getAccountById(long userId) throws SQLException;
    
    @Update("update account set first_name = ? where id = ?")
    updateFirstNameById(String newFirstName, long id) throws SQLException;
    
    @Delete("delete from account where id = ?")
    deleteAccountById(long id) throws SQLException;
}

final AccountDao accountDao= JdbcUtil.Dao.newInstance(AccountDao.class, datasource);

long id = accountDao.insert(account);

Account dbAccount = accountDao.getAccountById(id);

accountDao.updateFirstNameById("updatedFirstName", id);

accountDao.deleteAccountById(id);