AbacusUtil Docs | Download | Javadoc | FAQ | Community

SQLExecutor

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)
  • @Test
    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 ExMapper 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:
  • @Test
    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);
    }
    
    @Test
    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)
  • @Test
    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)
  • @Test
    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)
  • @Test
    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 to write the most effient SQL with the talent you have.

    Finally, let's take a look at the performance test running on a laptop with: CPU: Intel i7-3520M 2.9GHz, Meory: 8G, OS: Windows 7 64-bit, JDK: 1.7.0_80, Database: h2-1.3.176:

    Test Result: (unit: milliseconds)

    ========================================================================================================================
    threadNum=6; loops=10000
    totalElapsedTime: 9670

    maxMethodTime(test_crudBySQLExecutor): 129
    minMethodTime(test_crudBySQLExecutor): 0
    method name avg time min time max time 0.01% >= 0.1% >= 1% >= 10% >= 20% >= 50% >= 80% >= 90% >= 99% >= 99.9% >= 99.99% >=
    ... 0.9212 0 129 79 4 2 1 1 1 1 0 0 0 0
    ======================================================================================================================== ========================================================================================================================
    threadNum=6; loops=10000
    totalElapsedTime: 8467

    maxMethodTime(test_crudByJdbc): 141
    minMethodTime(test_crudByJdbc): 0
    method name avg time min time max time 0.01% >= 0.1% >= 1% >= 10% >= 20% >= 50% >= 80% >= 90% >= 99% >= 99.9% >= 99.99% >=
    ... 0.7889 0 141 108 4 2 1 1 1 0 0 0 0 0
    ======================================================================================================================== ========================================================================================================================
    threadNum=6; loops=10000
    totalElapsedTime: 10015

    maxMethodTime(test_crudBySpringJdbc): 74
    minMethodTime(test_crudBySpringJdbc): 0
    method name avg time min time max time 0.01% >= 0.1% >= 1% >= 10% >= 20% >= 50% >= 80% >= 90% >= 99% >= 99.9% >= 99.99% >=
    ... 0.9495 0 74 52 4 2 1 1 1 1 0 0 0 0
    ======================================================================================================================== ========================================================================================================================
    threadNum=6; loops=10000
    totalElapsedTime: 18455

    maxMethodTime(test_crudByMyBatis): 101
    minMethodTime(test_crudByMyBatis): 0
    method name avg time min time max time 0.01% >= 0.1% >= 1% >= 10% >= 20% >= 50% >= 80% >= 90% >= 99% >= 99.9% >= 99.99% >=
    ... 1.6955 0 101 46 8 4 2 2 2 1 1 0 0 0
    ======================================================================================================================== ========================================================================================================================
    threadNum=6; loops=10000
    totalElapsedTime: 17048

    maxMethodTime(test_crudByHibernateSession): 148
    minMethodTime(test_crudByHibernateSession): 0
    method name avg time min time max time 0.01% >= 0.1% >= 1% >= 10% >= 20% >= 50% >= 80% >= 90% >= 99% >= 99.9% >= 99.99% >=
    ... 1.5853 0 148 53 6 3 2 2 2 1 1 0 0 0
    ======================================================================================================================== ========================================================================================================================
    threadNum=6; loops=10000
    totalElapsedTime: 22742

    maxMethodTime(test_crudByHibernateJPA): 67
    minMethodTime(test_crudByHibernateJPA): 0
    method name avg time min time max time 0.01% >= 0.1% >= 1% >= 10% >= 20% >= 50% >= 80% >= 90% >= 99% >= 99.9% >= 99.99% >=
    ... 2.2477 0 67 50 6 5 3 3 2 2 1 1 0 0
    ========================================================================================================================