SQL Executor

SQLExecutor is a general sql/jdbc utility class. Insert/batchInsert/update/batchUpdate/delete/query and parameterized SQL with "?" or named parameters are supported very well. As one of the core functions provided by abacus-util. SQLExecutor is simple, fast and easy to use with the concise APIs. Here is the CRUD(create/read/update/delete) sample, comparing to the implementations by Jdbc, Spring JdbcTemplate, myBatis(iBatis), Hibernate and JPA:

• CURD by SQLExecutor provided by abacus-util: (See JdbcUtil about how to create DataSource for SQLExecutor)

public void test_crudBySQLExecutor() {
    Account account = createAccount();

    // create
    String sql_insert = NE.insert(GUI, FIRST_NAME, LAST_NAME, LAST_UPDATE_TIME, CREATE_TIME).into(Account.class).sql();
    sqlExecutor.insert(sql_insert, account); // About how to initialize a SQLExecutor instance, refer to SQLExecutorTest.java in Helloabacus-util.

    // read
    String sql_selectByGUI = NE.selectFrom(Account.class, N.asSet(DEVICES)).where(L.eq(GUI, L.QME)).sql();
    Account dbAccount = sqlExecutor.queryForEntity(Account.class, sql_selectByGUI, account);

    // update
    String sql_updateByLastName = NE.update(Account.class).set(FIRST_NAME).where(L.eq(LAST_NAME, L.QME)).sql();
    dbAccount.setFirstName("newFirstName");
    sqlExecutor.update(sql_updateByLastName, dbAccount);

    // delete
    String sql_deleteByFirstName = NE.deleteFrom(Account.class).where(L.eq(FIRST_NAME, L.QME)).sql();
    sqlExecutor.update(sql_deleteByFirstName, dbAccount);

    // check
    dbAccount = sqlExecutor.queryForEntity(Account.class, sql_selectByGUI, dbAccount);
    assertNull(dbAccount);
}

• CURD by Mapper in SQLExecutor:

public void test_crud() throws Exception {
    final ExMapper<Account> mapper = sqlExecutor.mapper(Account.class);
    long id = mapper.add(account);
    Account dbAccount = mapper.get(id, selectPropNames);

    dbAccount.setFirstName("newFirstName");
    mapper.update(dbAccount);
    dbAccount = mapper.queryForEntity(selectPropNames, L.eq("gui", dbAccount.getGUI())).get();
    assertEquals("newFirstName", dbAccount.getFirstName());

    assertEquals(1, mapper.delete(dbAccount));
}

• CURD by Jdbc with JdbcUtil:

public void test_crudByJdbc() {
    Account account = createAccount();

    // create
    String sql_insert = RE.insert(GUI, FIRST_NAME, LAST_NAME, LAST_UPDATE_TIME, CREATE_TIME).into(Account.class).sql();
    Connection conn = dataSource.getConnection();
    try {
        JdbcUtil.executeUpdate(conn, sql_insert, account.getGUI(), account.getFirstName(), account.getLastName(), account.getLastUpdateTime(),
                account.getCreateTime());
    } finally {
        JdbcUtil.closeQuietly(conn);
    }

    // read
    Account dbAccount = null;
    String sql_selectByGUI = RE.selectFrom(Account.class, N.asSet(DEVICES)).where(L.eq(GUI, L.QME)).sql();
    conn = dataSource.getConnection();
    try {
        DataSet rs = JdbcUtil.executeQuery(conn, sql_selectByGUI, account.getGUI());
        dbAccount = rs.size() > 0 ? rs.getRow(Account.class, 0) : null;
    } finally {
        JdbcUtil.closeQuietly(conn);
    }

    // update
    dbAccount.setFirstName("newFirstName");
    String sql_updateByLastName = RE.update(Account.class).set(FIRST_NAME).where(L.eq(LAST_NAME, L.QME)).sql();
    conn = dataSource.getConnection();
    try {
        JdbcUtil.executeUpdate(conn, sql_updateByLastName, dbAccount.getFirstName(), dbAccount.getLastName());
    } finally {
        JdbcUtil.closeQuietly(conn);
    }

    // delete
    String sql_deleteByFirstName = RE.deleteFrom(Account.class).where(L.eq(FIRST_NAME, L.QME)).sql();
    conn = dataSource.getConnection();
    try {
        JdbcUtil.executeUpdate(conn, sql_deleteByFirstName, dbAccount.getFirstName());
    } finally {
        JdbcUtil.closeQuietly(conn);
    }

    // check
    conn = dataSource.getConnection();
    try {
        DataSet rs = JdbcUtil.executeQuery(conn, sql_selectByGUI, dbAccount.getGUI());
        dbAccount = rs.size() > 0 ? rs.getRow(Account.class, 0) : null;
    } finally {
        JdbcUtil.closeQuietly(conn);
    }

    assertNull(dbAccount);
}

• CURD by MyBatis: (It's welcome to improve the code below)

public interface AccountMapper {
    @Insert("INSERT INTO account (gui, first_name, last_name, last_update_time, create_time) VALUES (#{gui}, #{firstName}, #{lastName}, #{lastUpdateTime}, #{createTime})")
    void insertAccount(Account account);

    @Select("SELECT id AS \"id\", gui AS \"gui\", first_name AS \"firstName\", last_name AS \"lastName\", status AS \"status\", last_update_time AS \"lastUpdateTime\", create_time AS \"createTime\" FROM account WHERE gui = #{gui}")
    Account getAccountByGUI(String gui);

    @Update("UPDATE account SET first_name = #{firstName} WHERE last_name = #{lastName}")
    int updateByLastName(Account account);

    @Delete("DELETE FROM account WHERE first_name = #{firstName}")
    int deleteByFirstName(Account account);
}

public void test_crudByMyBatis() {
    Account account = createAccount();

    // create
    SqlSession session = sqlSessionFactory.openSession(true);
    try {
        AccountMapper accountMapper = session.getMapper(AccountMapper.class);
        accountMapper.insertAccount(account);
    } finally {
        session.close();
    }

    // read
    Account dbAccount = null;
    session = sqlSessionFactory.openSession();

    try {
        AccountMapper accountMapper = session.getMapper(AccountMapper.class);
        dbAccount = accountMapper.getAccountByGUI(account.getGUI());
    } finally {
        session.close();
    }

    // update
    dbAccount.setFirstName("newFirstName");
    session = sqlSessionFactory.openSession(true);

    try {
        AccountMapper accountMapper = session.getMapper(AccountMapper.class);
        accountMapper.updateByLastName(dbAccount);
    } finally {
        session.close();
    }

    // delete
    session = sqlSessionFactory.openSession(true);

    try {
        AccountMapper accountMapper = session.getMapper(AccountMapper.class);
        accountMapper.deleteByFirstName(dbAccount);
    } finally {
        session.close();
    }

    // check
    session = sqlSessionFactory.openSession();

    try {
        AccountMapper accountMapper = session.getMapper(AccountMapper.class);
        dbAccount = accountMapper.getAccountByGUI(dbAccount.getGUI());
    } finally {
        session.close();
    }

    assertNull(dbAccount);
}

• CURD by Hibernate: (It's welcome to improve the code below)

public void test_crudByHibernateSession() throws Exception {
    Account account = createAccount();

    // create
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
        session.save(account);
    } finally {
        session.close();
    }

    // read
    Account dbAccount = null;
    session = HibernateUtil.getSessionFactory().openSession();

    try {
        List<?> list = session.createCriteria(Account.class).add(Restrictions.eq("GUI", account.getGUI())).list();
        dbAccount = N.isNullOrEmpty(list) ? null : (Account) list.get(0);
    } finally {
        session.close();
    }

    // update
    dbAccount.setFirstName("newFirstName");
    String sql_updateByLastName = NE.update("Account").set(FIRST_NAME).where(L.eq(LAST_NAME, L.QME)).sql();
    session = HibernateUtil.getSessionFactory().openSession();

    try {
        Query query = session.createSQLQuery(sql_updateByLastName);
        query.setString(FIRST_NAME, dbAccount.getFirstName());
        query.setString(LAST_NAME, dbAccount.getLastName());
        query.executeUpdate();
    } finally {
        session.close();
    }

    // delete
    String sql_deleteByFirstName = NE.deleteFrom("Account").where(L.eq(FIRST_NAME, L.QME)).sql();
    session = HibernateUtil.getSessionFactory().openSession();

    try {
        Query query = session.createSQLQuery(sql_deleteByFirstName);
        query.setString(FIRST_NAME, dbAccount.getFirstName());
        query.executeUpdate();
    } finally {
        session.close();
    }

    // check
    session = HibernateUtil.getSessionFactory().openSession();

    try {
        List<?> list = session.createCriteria(Account.class).add(Restrictions.eq("GUI", dbAccount.getGUI())).list();
        dbAccount = N.isNullOrEmpty(list) ? null : (Account) list.get(0);
    } finally {
        session.close();
    }

    assertNull(dbAccount);
}

• CURD by Spring JdbcTemplate: (It's welcome to improve the code below)

public void test_crudBySpringJdbc() throws Exception {
    final ResultSetExtractor<Account> resultSetExtractor = new ResultSetExtractor<Account>() {
        @Override
        public Account extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                Account act = new Account();
                int columnIndex = 1;
                act.setId(rs.getLong(columnIndex++));
                act.setGUI(rs.getString(columnIndex++));
                act.setFirstName(rs.getString(columnIndex++));
                act.setLastName(rs.getString(columnIndex++));
                act.setStatus(rs.getInt(columnIndex++));
                act.setLastUpdateTime(rs.getTimestamp(columnIndex++));
                act.setCreateTime(rs.getTimestamp(columnIndex++));

                return act;
            } else {
                return null;
            }
        }
    };

    final Account account = createAccount();

    // create
    final String sql_insert = NE.insert(GUI, FIRST_NAME, LAST_NAME, LAST_UPDATE_TIME, CREATE_TIME).into(Account.class).sql();
    Map<String, Object> parameters = N.entity2Map(account);
    namedJdbcTemplate.update(sql_insert, parameters);

    // read
    String sql_selectByGUI = NE.selectFrom(Account.class, N.asSet(DEVICES)).where(L.eq(GUI, L.QME)).sql();
    Account dbAccount = namedJdbcTemplate.query(sql_selectByGUI, N.asProps("gui", account.getGUI()), resultSetExtractor);

    // update
    String sql_updateByLastName = NE.update(Account.class).set(FIRST_NAME).where(L.eq(LAST_NAME, L.QME)).sql();
    dbAccount.setFirstName("newFirstName");
    parameters = N.asProps(FIRST_NAME, dbAccount.getFirstName(), LAST_NAME, dbAccount.getLastName());
    namedJdbcTemplate.update(sql_updateByLastName, parameters);

    // delete
    String sql_deleteByFirstName = NE.deleteFrom(Account.class).where(L.eq(FIRST_NAME, L.QME)).sql();
    namedJdbcTemplate.update(sql_deleteByFirstName, N.asProps(FIRST_NAME, dbAccount.getFirstName()));

    dbAccount = sqlExecutor.queryForEntity(Account.class, sql_selectByGUI, dbAccount);
    assertNull(dbAccount);

    // check
    dbAccount = namedJdbcTemplate.query(sql_selectByGUI, N.asProps(GUI, account.getGUI()), resultSetExtractor);

    assertNull(dbAccount);
}

• CURD by JPA: (It's welcome to improve the code below)

public void test_crudByHibernateJPA() {
    final Account account = createAccount();

    // create;
    EntityManager entityManager = hibernateEntityManagerFactory.createEntityManager();
    entityManager.getTransaction().begin();
    boolean noException = false;

    try {
        entityManager.persist(account);
        noException = true;
    } finally {
        try {
            if (noException) {
                entityManager.getTransaction().commit();
            } else {
                entityManager.getTransaction().rollback();
            }
        } finally {
            entityManager.close();
        }
    }

    // read
    Account dbAccount = null;
    String sql_selectByGUI = NE.selectFrom(Account.class, N.asSet(DEVICES)).where(L.eq(GUI, L.QME)).sql();
    entityManager = hibernateEntityManagerFactory.createEntityManager();

    try {
        javax.persistence.Query query = entityManager.createNativeQuery(sql_selectByGUI, Account.class);
        query.setParameter(GUI, account.getGUI());
        List<Account> resultList = query.getResultList();
        dbAccount = N.isNullOrEmpty(resultList) ? null : resultList.get(0);
    } finally {
        entityManager.close();
    }

    // update
    dbAccount.setFirstName("newFirstName");
    String sql_updateByLastName = NE.update(Account.class).set(FIRST_NAME).where(L.eq(LAST_NAME, L.QME)).sql();
    entityManager = hibernateEntityManagerFactory.createEntityManager();
    entityManager.getTransaction().begin();
    noException = false;

    try {
        javax.persistence.Query query = entityManager.createNativeQuery(sql_updateByLastName);
        query.setParameter(FIRST_NAME, dbAccount.getFirstName());
        query.setParameter(LAST_NAME, dbAccount.getLastName());
        query.executeUpdate();

        noException = true;
    } finally {
        try {
            if (noException) {
                entityManager.getTransaction().commit();
            } else {
                entityManager.getTransaction().rollback();
            }
        } finally {
            entityManager.close();
        }
    }

    // delete
    String sql_deleteByFirstName = NE.deleteFrom("Account").where(L.eq(FIRST_NAME, L.QME)).sql();
    entityManager = hibernateEntityManagerFactory.createEntityManager();
    entityManager.getTransaction().begin();
    noException = false;

    try {
        javax.persistence.Query query = entityManager.createNativeQuery(sql_deleteByFirstName);
        query.setParameter(FIRST_NAME, dbAccount.getFirstName());
        query.executeUpdate();

        noException = true;
    } finally {
        try {
            if (noException) {
                entityManager.getTransaction().commit();
            } else {
                entityManager.getTransaction().rollback();
            }
        } finally {
            entityManager.close();
        }
    }

    // check
    entityManager = hibernateEntityManagerFactory.createEntityManager();

    try {
        javax.persistence.Query query = entityManager.createNativeQuery(sql_selectByGUI, Account.class);
        query.setParameter(GUI, dbAccount.getGUI());
        List<Account> resultList = query.getResultList();
        dbAccount = N.isNullOrEmpty(resultList) ? null : resultList.get(0);
    } finally {
        entityManager.close();
    }

    assertNull(dbAccount);
}

As you see, There is no setting or conversion... in test_crudBySQLExecutor. All are done by SQLBuilder/SQLExecutor automatically. You have the full control of the SQL in hand, and will be able to focus on the logic and write the most efficient SQL.