SQLExecutor

接着前面的例子，再给大家介绍一个SQLExecutor工具类。SQLExecutor可以像iBatis一样用来执行任何SQL，包括parameterized SQL with named parameters。在某一些方面比iBatis更简便，下面用一个CRUD（Create/Read/Update/Delete）的例子来作一个简单的比较：

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

    // 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);
}

• 用iBatis实现

// 先准备一个Mapper类
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);
}