AbacusUtil Docs | Download | Javadoc | FAQ | Community

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