Skip to content

SQL Executor

Jump to bottom
landawn edited this page May 2, 2018 · 5 revisions

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 AbacusUtil. 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 AbacusUtil: (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 HelloAbacusUtil.

    // 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.

Clone this wiki locally