SQL Mapper

SQLMapper supports the pre-defined sql in xml file. Here is the schema: SQLMapper.xsd. It's easy to generate class for the mapping id by Code Generation and used in: SQLExecutor, CassandraExecutor, CouchbaseExecutor. Here are the steps/samples about how to do it:

• Step 1: Write the sql scripts in the xml file. Here is a sample:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<sqlMapper>
	<sql id="insertAccount">INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES (#{firstName}, #{lastName}, #{gui}, #{lastUpdateTime}, #{createTime})</sql>

	<sql id="selectAccountById">select * from account WHERE account.id = #{id}</sql>

	<sql id="selectAllAccount">select * from account</sql>

	<sql id="updateAccountFirstNameById">UPDATE account SET first_name = #{firstName} WHERE account.id = #{id}</sql>

	<sql id="deleteAccountById">DELETE FROM account WHERE account.id = #{id}</sql>

	<sql id="deleteAllAccount">DELETE FROM account</sql>
</sqlMapper>

• Step 2: Generate the class for SQL mapper ids by the xml file

public static void generateSQLMapperIdTable() {
    String srcPath = "./src/";
    final String sqlMapperXmlFile = "./resources/codesSQLMapper.xml";
    final String targetClassName = "CodesSQLMapperIdList";

    CodeGenerator.generateSQLMapperIdTable(sqlMapperXmlFile, srcPath, packageName, targetClassName, null);
}

• Step 3: Initialize the SQLExecutor with SQLMapper

static final String dataSourcePath = "./resources/h2_dataSource.xml";

static final SQLMapper sqlMapper = new SQLMapper("./resources/codesSQLMapper.xml");

static final SQLExecutor sqlExecutor = new SQLExecutor(JdbcUtil.createDataSourceManager(dataSourcePath), null, sqlMapper);

• Programming with sql mapper ids

public void test_crud() {
    Account account = createAccount("firstName123", "lastName123");

    // insert
    long id = sqlExecutor.insert(INSERT_ACCOUNT, account);

    // read
    Account dbAccount = sqlExecutor.queryForEntity(Account.class, SELECT_ACCOUNT_BY_ID, id);
    N.println(dbAccount);

    // update
    String newFirstName = "newFirstName";
    dbAccount.setFirstName(newFirstName);
    sqlExecutor.update(UPDATE_ACCOUNT_FIRST_NAME_BY_ID, dbAccount);
    dbAccount = sqlExecutor.queryForEntity(Account.class, SELECT_ACCOUNT_BY_ID, id);
    assertEquals(newFirstName, dbAccount.getFirstName());

    // delete
    sqlExecutor.update(DELETE_ACCOUNT_BY_ID, dbAccount);
    dbAccount = sqlExecutor.queryForEntity(Account.class, SELECT_ACCOUNT_BY_ID, id);
    assertNull(dbAccount);
}