Example usage for org.apache.ibatis.jdbc SQL SQL

List of usage examples for org.apache.ibatis.jdbc SQL SQL

  1. HOME
  2. Java
  3. org.apache.ibatis
  4. org.apache.ibatis.jdbc.*
  5. SQL
  6. SQL

Introduction

In this page you can find the example usage for org.apache.ibatis.jdbc SQL SQL.

Prototype

SQL

Source Link

Usage

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String getInvtHeadByInvtNoSql(final String invtNo) {
    final InvtHeadSqlProvide self = this;
    return new SQL() {
        {//from  ww w .j ava  2  s .  c om
            for (String field : self.selectField()) {
                this.SELECT(field);
            }
            this.SELECT("app_type");
            this.SELECT("port_code");
            this.SELECT("ebp_code");
            this.SELECT("ebp_name");
            this.SELECT("ie_date");
            this.SELECT("loct_no");
            this.SELECT("ems_no");
            this.SELECT("buyer_name");
            this.SELECT("country");
            this.SELECT("traf_no");
            this.SELECT("wrap_type");
            this.SELECT("pack_no");
            this.SELECT("buyer_id_type");
            this.SELECT("consignee_address");
            this.SELECT("voyage_no");
            this.SELECT("assure_code");
            this.SELECT("gross_weight");
            this.SELECT("buyer_id_number");
            this.SELECT("license_no");
            this.SELECT("bill_no");
            this.SELECT("insured_fee");
            this.SELECT("net_weight");
            this.SELECT("buyer_telephone");
            this.SELECT("traf_mode");
            this.SELECT("freight");
            this.SELECT("currency");
            this.SELECT("note");
            this.FROM("ceb2_invt_head");

            if (!StringUtils.isEmpty(invtNo)) {
                this.WHERE("head_guid = '" + invtNo + "'");
            } else {
                this.WHERE("1 = 2");
            }

        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String getInvtHeadByHeadGuidSql(final String headGuid) {
    final InvtHeadSqlProvide self = this;
    return new SQL() {
        {//  ww w  .j a v  a2s .c o m
            for (String field : self.selectField()) {
                this.SELECT(field);
            }
            this.SELECT("app_type");
            this.SELECT("port_code");
            this.SELECT("ebp_code");
            this.SELECT("ebp_name");
            this.SELECT("ie_date");
            this.SELECT("loct_no");
            this.SELECT("ems_no");
            this.SELECT("buyer_name");
            this.SELECT("country");
            this.SELECT("traf_no");
            this.SELECT("wrap_type");
            this.SELECT("pack_no");
            this.SELECT("buyer_id_type");
            this.SELECT("consignee_address");
            this.SELECT("voyage_no");
            this.SELECT("assure_code");
            this.SELECT("gross_weight");
            this.SELECT("buyer_id_number");
            this.SELECT("license_no");
            this.SELECT("bill_no");
            this.SELECT("insured_fee");
            this.SELECT("net_weight");
            this.SELECT("buyer_telephone");
            this.SELECT("traf_mode");
            this.SELECT("freight");
            this.SELECT("currency");
            this.SELECT("note");
            this.FROM("ceb2_invt_head");
            this.WHERE("head_guid = '" + headGuid + "'");

        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String getInvtHeadListSql(final InvtHead invtHead) {
    final InvtHeadSqlProvide self = this;
    return new SQL() {
        {/* www  . j  a  v  a  2s  .  co  m*/
            this.SELECT("cih.head_guid");
            this.SELECT("cih.app_status");
            this.SELECT("cih.app_time");
            this.SELECT("cih.sys_date");
            this.SELECT("cih.app_sender_id");
            this.SELECT("cih.order_no");
            this.SELECT("cih.ebc_code");
            this.SELECT("cih.ebc_name");
            this.SELECT("cih.logistics_no");
            this.SELECT("cih.logistics_code");
            this.SELECT("cih.logistics_name");
            this.SELECT("cih.cop_no");
            this.SELECT("cih.pre_no");
            this.SELECT("cih.invt_no");
            this.SELECT("cih.agent_code");
            this.SELECT("cih.agent_name");
            this.SELECT("cih.area_code");
            this.SELECT("cih.area_name");
            this.SELECT("cih.customs_code");
            this.SELECT("cih.dist_status");
            this.SELECT("cih.trade_mode");

            this.FROM("ceb2_invt_head cih");

            if (!StringUtils.isEmpty(invtHead.getDistinct())) {
                this.INNER_JOIN(
                        "(select min(head_guid) as inner_head_guid from ceb2_invt_head group by ebc_code, order_no) cih1 on cih.head_guid = cih1.inner_head_guid");
            }

            if (!StringUtils.isEmpty(invtHead.getDistNo())) {
                this.INNER_JOIN("pre_dist_bill_list pdbl on pdbl.bill_no = cih.invt_no and pdbl.dist_no = '"
                        + invtHead.getDistNo() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getSearchText())) {
                this.WHERE("cih.invt_no like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.cop_no like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.ebc_code like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.ebc_name like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.order_no like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.agent_code like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.agent_name like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.logistics_code like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.logistics_name like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.logistics_no like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.area_code like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.area_name like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.customs_code like '%" + invtHead.getSearchText() + "%'");
                this.OR().WHERE("cih.trade_mode like '%" + invtHead.getSearchText() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getHeadGuid())) {
                this.WHERE("cih.head_guid = '" + invtHead.getHeadGuid() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getAppStatus())) {
                this.WHERE("cih.app_status = '" + invtHead.getAppStatus() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getBeginAppTime())) {
                this.WHERE("to_char(cih.sys_date, 'yyyy-mm-dd') >= '" + invtHead.getBeginAppTime() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getEndAppTime())) {
                this.WHERE("to_char(cih.sys_date, 'yyyy-mm-dd') <= '" + invtHead.getEndAppTime() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getSysDateStr())) {
                this.WHERE("to_char(cih.sys_date, 'yyyy-mm-dd') = '" + invtHead.getSysDateStr() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getAppSenderId())) {
                this.WHERE("cih.app_sender_id = '" + invtHead.getAppSenderId() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getOrderNo())) {
                this.WHERE("cih.order_no like '%" + invtHead.getOrderNo() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getEbcCode())) {
                this.WHERE("cih.ebc_code = '" + invtHead.getEbcCode() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getEbcName())) {
                this.WHERE("cih.ebc_name like '%" + invtHead.getEbcName() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getLogisticsNo())) {
                this.WHERE("cih.logistics_no like '%" + invtHead.getLogisticsNo() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getLogisticsCode())) {
                this.WHERE("cih.logistics_code = '" + invtHead.getLogisticsCode() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getLogisticsName())) {
                this.WHERE("cih.logistics_name like '%" + invtHead.getLogisticsName() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getCopNo())) {
                this.WHERE("cih.cop_no = '" + invtHead.getCopNo() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getInvtNo())) {
                this.WHERE("cih.invt_no like '%" + invtHead.getInvtNo() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getAgentCode())) {
                this.WHERE("cih.agent_code = '" + invtHead.getAgentCode() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getAgentName())) {
                this.WHERE("cih.agent_name like '%" + invtHead.getAgentName() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getAreaCode())) {
                this.WHERE("cih.area_code = '" + invtHead.getAreaCode() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getAreaName())) {
                this.WHERE("cih.area_name like '%" + invtHead.getAreaName() + "%'");
            }

            if (!StringUtils.isEmpty(invtHead.getDistStatus())) {
                this.WHERE("cih.dist_status = '" + invtHead.getDistStatus() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getCustomsCode())) {
                this.WHERE("cih.customs_code = '" + invtHead.getCustomsCode() + "'");
            }

            if (!StringUtils.isEmpty(invtHead.getTradeMode())) {
                this.WHERE("cih.trade_mode = '" + invtHead.getTradeMode() + "'");
            }

            OracleTool.where(this, "cih.bill_no", invtHead.getBillNo());
            OracleTool.where(this, "cih.voyage_no", invtHead.getVoyageNo());
            OracleTool.where(this, "cih.buyer_telephone", invtHead.getBuyerTelephone());
            OracleTool.where(this, "cih.buyer_id_number", invtHead.getBuyerIdNumber());

            if (!StringUtils.isEmpty(invtHead.getDeclareStatus())) {
                if ("1".equals(invtHead.getDeclareStatus())) {
                    this.WHERE("cih.app_status in ('1', '01', '100')");
                } else {
                    this.WHERE("cih.app_status not in ('1', '01', '100')");
                }
            }

            if (null != invtHead.getCopNoList() && !invtHead.getCopNoList().isEmpty()) {
                StringBuffer stringBufferIn = new StringBuffer("(");
                for (String copNo : invtHead.getCopNoList()) {
                    stringBufferIn.append("'" + copNo + "',");
                }
                stringBufferIn.deleteCharAt(stringBufferIn.length() - 1);
                stringBufferIn.append(")");
                this.WHERE("cih.cop_no in " + stringBufferIn.toString());
            }

        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String getDeclareTopTenSql(final InvtHead invtHead) {
    return new SQL() {
        {//from   w  ww  .ja v  a  2s . co  m
            this.SELECT("ebc_name");
            this.SELECT("count(1) as count");

            if (!StringUtils.isEmpty(invtHead.getAppStatus())) {
                this.SELECT("round(count(1) / (select count(1) from ceb2_invt_head where app_status = '"
                        + invtHead.getAppStatus() + "') * 100, 2) as percentage");
                this.SELECT("(select count(1) from ceb2_invt_head where app_status = '"
                        + invtHead.getAppStatus() + "') as total");
            } else {
                this.SELECT("round(count(1) / (select count(1) from ceb2_invt_head) * 100, 2) as percentage");
                this.SELECT("(select count(1) from ceb2_invt_head) as total");
            }
            this.FROM("ceb2_invt_head");

            if (!StringUtils.isEmpty(invtHead.getAppStatus())) {
                this.WHERE("app_status = '" + invtHead.getAppStatus() + "'");
            }

            this.GROUP_BY("ebc_name");
            this.ORDER_BY("count(1) desc");
        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String getInvtHeadMonthCountSql() {
    return new SQL() {
        {//w ww.ja  va2  s  .c om
            this.SELECT("to_char(sys_date, 'yyyy-mm') as ebc_name");
            this.SELECT("count(1) as count");
            this.FROM("ceb2_invt_head");
            this.GROUP_BY("to_char(sys_date, 'yyyy-mm')");
            this.ORDER_BY("to_char(sys_date, 'yyyy-mm')");
        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String getInvtHeadCountSql(final String countType) {
    return new SQL() {
        {//w ww . ja  v a  2  s.co m
            this.SELECT("count(1) as count");
            this.FROM("ceb2_invt_head");

            Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("Asia/Shanghai"), Locale.CHINA);
            calendar.setFirstDayOfWeek(Calendar.MONDAY);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            if ("w".equals(countType)) {
                calendar.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
                this.WHERE("to_char(sys_date, 'yyyy-mm-dd') >= '" + sdf.format(calendar.getTime()) + "'");
                calendar.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
                this.WHERE("to_char(sys_date, 'yyyy-mm-dd') <= '" + sdf.format(calendar.getTime()) + "'");
            } else if ("pw".equals(countType)) {
                calendar.add(Calendar.WEEK_OF_YEAR, -1);
                calendar.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
                this.WHERE("to_char(sys_date, 'yyyy-mm-dd') >= '" + sdf.format(calendar.getTime()) + "'");
                calendar.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
                this.WHERE("to_char(sys_date, 'yyyy-mm-dd') <= '" + sdf.format(calendar.getTime()) + "'");
            } else if ("m".equals(countType)) {
                sdf = new SimpleDateFormat("yyyy-MM");
                this.WHERE("to_char(sys_date, 'yyyy-mm') = '" + sdf.format(calendar.getTime()) + "'");
            } else if ("pm".equals(countType)) {
                sdf = new SimpleDateFormat("yyyy-MM");
                calendar.add(Calendar.MONTH, -1);
                this.WHERE("to_char(sys_date, 'yyyy-mm') = '" + sdf.format(calendar.getTime()) + "'");
            } else if ("y".equals(countType)) {
                sdf = new SimpleDateFormat("yyyy");
                this.WHERE("to_char(sys_date, 'yyyy') = '" + sdf.format(calendar.getTime()) + "'");
            } else if ("py".equals(countType)) {
                sdf = new SimpleDateFormat("yyyy");
                calendar.add(Calendar.YEAR, -1);
                this.WHERE("to_char(sys_date, 'yyyy') = '" + sdf.format(calendar.getTime()) + "'");
            } else if ("d".equals(countType)) {
                this.WHERE("to_char(sys_date, 'yyyy-mm-dd') = '" + sdf.format(calendar.getTime()) + "'");
            } else if ("pd".equals(countType)) {
                calendar.add(Calendar.DAY_OF_YEAR, -1);
                this.WHERE("to_char(sys_date, 'yyyy-mm-dd') = '" + sdf.format(calendar.getTime()) + "'");
            }
        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String exportInvtHeadListSql(final InvtHead invtHead) {
    return new SQL() {
        {/*w w w .  j a va2s  .com*/
            this.SELECT("cih.invt_no");
            this.SELECT("cih.app_status");
            this.SELECT("cpr.rtn_info");
            this.SELECT("cpr.rtn_time");
            this.SELECT("cpr.sys_date as rtn_sys_date");
            this.SELECT("iih.detailscode");
            this.SELECT("iih.audit_state");
            this.SELECT("iih.bw_name");
            this.SELECT("cih.ebc_name");
            this.SELECT("cih.order_no");
            this.SELECT("cih.logistics_name");
            this.SELECT("cih.logistics_no");
            this.SELECT("iih.payename");
            this.SELECT("iih.applycode");
            this.SELECT("cih.bill_no");
            this.SELECT("cih.sys_date");
            this.SELECT("iih.goodsvalue");
            this.SELECT("cih.consignee_address");
            this.SELECT("cih.gross_weight");
            this.SELECT("cih.net_weight");
            this.SELECT("pdbl.dist_no");
            this.SELECT("pdh.dist_stat");
            this.SELECT("cih.dist_time");

            this.FROM("ceb2_invt_head cih");

            if (!StringUtils.isEmpty(invtHead.getDistNo())) {
                this.INNER_JOIN("pre_dist_bill_list pdbl on pdbl.bill_no = cih.invt_no and pdbl.dist_no = '"
                        + invtHead.getDistNo() + "'");
            }

            this.LEFT_OUTER_JOIN(
                    "imp_invt_head@ggfw_zhengzhou iih on iih.order_no = cih.order_no and iih.ebc_code = cih.ebc_code and iih.logistics_code = cih.logistics_code and iih.logistics_no = cih.logistics_no");
            this.LEFT_OUTER_JOIN(
                    "ceb2_pub_rtn cpr on cpr.biz_guid = cih.head_guid and cpr.rtn_status = cih.app_status left outer join (select tt.biz_guid, tt.rtn_status,max(tt.sys_date) as max_sys_date from ceb2_pub_rtn tt group by tt.biz_guid, tt.rtn_status) tt0 on tt0.biz_guid = cpr.biz_guid and tt0.rtn_status = cpr.rtn_status and tt0.max_sys_date = cpr.sys_date");
            this.LEFT_OUTER_JOIN(
                    "pre_dist_bill_list pdbl on pdbl.bill_no = cih.invt_no left outer join pre_dist_head pdh on pdh.seq_no = pdbl.seq_no");

            OracleTool.where(this, "cih.head_guid", invtHead.getHeadGuid());
            OracleTool.where(this, "cih.app_status", invtHead.getAppStatus());
            OracleTool.where(this, "cih.sys_date", invtHead.getBeginAppTime(), ">=");
            OracleTool.where(this, "cih.sys_date", invtHead.getEndAppTime(), "<=");
            OracleTool.where(this, "cih.sys_date", invtHead.getSysDateStr(), "=");
            OracleTool.where(this, "cih.app_sender_id", invtHead.getAppSenderId());
            OracleTool.where(this, "cih.order_no", invtHead.getOrderNo(), true);
            OracleTool.where(this, "cih.ebc_code", invtHead.getEbcCode());
            OracleTool.where(this, "cih.ebc_name", invtHead.getEbcName(), true);
            OracleTool.where(this, "cih.logistics_no", invtHead.getLogisticsNo(), true);
            OracleTool.where(this, "cih.logistics_code", invtHead.getLogisticsCode());
            OracleTool.where(this, "cih.logistics_name", invtHead.getLogisticsName(), true);
            OracleTool.where(this, "cih.cop_no", invtHead.getCopNo());
            OracleTool.where(this, "cih.invt_no", invtHead.getInvtNo(), true);
            OracleTool.where(this, "cih.agent_code", invtHead.getAgentCode());
            OracleTool.where(this, "cih.agent_name", invtHead.getAgentName(), true);
            OracleTool.where(this, "cih.area_code", invtHead.getAreaCode());
            OracleTool.where(this, "cih.area_name", invtHead.getAreaName(), true);
            OracleTool.where(this, "cih.dist_status", invtHead.getDistStatus());
            OracleTool.where(this, "cih.customs_code", invtHead.getCustomsCode());
            OracleTool.where(this, "cih.trade_mode", invtHead.getTradeMode());
            OracleTool.where(this, "cih.bill_no", invtHead.getBillNo());
            OracleTool.where(this, "cih.voyage_no", invtHead.getVoyageNo());
            OracleTool.where(this, "cih.buyer_telephone", invtHead.getBuyerTelephone());

            if (!StringUtils.isEmpty(invtHead.getDeclareStatus())) {
                if ("1".equals(invtHead.getDeclareStatus())) {
                    this.WHERE("cih.app_status in ('1', '01', '100')");
                } else {
                    this.WHERE("cih.app_status not in ('1', '01', '100')");
                }
            }

            if (null != invtHead.getCopNoList() && !invtHead.getCopNoList().isEmpty()) {
                StringBuffer stringBufferIn = new StringBuffer("(");
                for (String copNo : invtHead.getCopNoList()) {
                    stringBufferIn.append("'" + copNo + "',");
                }
                stringBufferIn.deleteCharAt(stringBufferIn.length() - 1);
                stringBufferIn.append(")");
                this.WHERE("cih.cop_no in " + stringBufferIn.toString());
            }
        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String getReleaseBackStaggeredInvtListSql() {
    return new SQL() {
        {//from w  w w .j  ava  2  s. c  o  m
            this.SELECT("cih.head_guid");
            this.FROM("rtn_status_group rsg");
            this.INNER_JOIN(
                    "rtn_status_group rsg1 on rsg.rtn_status = '100' and rsg1.rtn_status = '800' and rsg.biz_guid = rsg1.biz_guid and rsg.max_rtn_time > rsg1.max_rtn_time and rsg.max_sys_date <= rsg1.max_sys_date");
            this.INNER_JOIN("ceb2_invt_head cih on cih.head_guid = rsg.biz_guid and cih.app_status = '800'");
        }
    }.toString();
}

From source file:online.zhaopei.myproject.sqlprovide.ecssent.InvtHeadSqlProvide.java

public String updateInvtHeadStatusSql(final String headGuid, final String status) {
    return new SQL() {
        {/*from  ww w  .  jav  a2 s.  c  o m*/
            this.UPDATE("ceb2_invt_head");
            this.SET("app_status = '" + status + "'");
            this.WHERE("head_guid = '" + headGuid + "'");
        }
    }.toString();
}

From source file:org.languagetool.server.DatabaseLoggerTest.java

License:Open Source License

@Test
public void testHTTPServer() throws Exception {
    HTTPServerConfig config = new HTTPServerConfig(HTTPTools.getDefaultPort());
    config.setDatabaseDriver("org.mariadb.jdbc.Driver");
    config.setDatabaseUrl("jdbc:mysql://localhost:3306/lt_test");
    config.setDatabaseUsername("lt");
    config.setDatabasePassword("languagetool");
    DatabaseAccess.init(config);/*  ww  w .  j  a v a 2 s  . c  o m*/
    DatabaseAccess db = DatabaseAccess.getInstance();
    DatabaseLogger logger = DatabaseLogger.getInstance();
    try {
        logger.createTestTables(true);
        DatabaseAccess.createAndFillTestTables(true);

        HTTPServer server = new HTTPServer(config);
        Language en = Languages.getLanguageForShortCode("en-US");
        try {
            server.run();
            check(en, "This is a test.", UserDictTest.USERNAME1, UserDictTest.API_KEY1, null, null);
            check(en, "This is a test.", null, null, null, "agent1");
            check(en, "This is a test.", UserDictTest.USERNAME2, UserDictTest.API_KEY2, null, "agent2");
            check(en, "This is an mistak.", null, null, "123456789", null);
            check(en, "This is a mistak.", null, null, "123456789", null);
            check(en, "This is a mistake.", null, null, "123456789", null);
            check(en, "This is not a mistake.", null, null, "987654321", null);
            SQL checkLogQuery = new SQL() {
                {
                    SELECT("id", "user_id", "text_session_id", "client", "server");
                    FROM("check_log");
                    ORDER_BY("id");
                }
            };
            Long serverId = db.getOrCreateServerId();
            Long agent1 = db.getOrCreateClientId("agent1");
            Long agent2 = db.getOrCreateClientId("agent2");
            Long user1 = db.getUserId(UserDictTest.USERNAME1, UserDictTest.API_KEY1);
            Long user2 = db.getUserId(UserDictTest.USERNAME2, UserDictTest.API_KEY2);
            Thread.sleep(DatabaseLogger.SQL_BATCH_WAITING_TIME);
            try (ResultSet results = DatabaseAccess.executeStatement(checkLogQuery)) {
                results.next();
                assertEquals(results.getLong(1), 1);
                assertEquals(Long.valueOf(results.getLong(2)), user1);
                assertNull(results.getObject(3));
                assertNull(results.getObject(4));
                assertEquals(Long.valueOf(results.getLong(5)), serverId);

                results.next();
                assertEquals(results.getLong(1), 2);
                assertNull(results.getObject(2));
                assertNull(results.getObject(3));
                assertEquals(Long.valueOf(results.getLong(4)), agent1);
                assertEquals(Long.valueOf(results.getLong(5)), serverId);

                results.next();
                assertEquals(results.getLong(1), 3);
                assertEquals(Long.valueOf(results.getLong(2)), user2);
                assertNull(results.getObject(3));
                assertEquals(Long.valueOf(results.getLong(4)), agent2);
                assertEquals(Long.valueOf(results.getLong(5)), serverId);

                results.next();
                assertEquals(results.getLong(1), 4);
                assertNull(results.getObject(2));
                assertEquals(results.getInt(3), 123456789);
                assertNull(results.getObject(4));
                assertEquals(Long.valueOf(results.getLong(5)), serverId);

                results.next();
                assertEquals(results.getLong(1), 5);
                assertNull(results.getObject(2));
                assertEquals(results.getInt(3), 123456789);
                assertNull(results.getObject(4));
                assertEquals(Long.valueOf(results.getLong(5)), serverId);

                results.next();
                assertEquals(results.getLong(1), 6);
                assertNull(results.getObject(2));
                assertEquals(results.getInt(3), 123456789);
                assertNull(results.getObject(4));
                assertEquals(Long.valueOf(results.getLong(5)), serverId);

                results.next();
                assertEquals(results.getLong(1), 7);
                assertNull(results.getObject(2));
                assertEquals(results.getInt(3), 987654321);
                assertNull(results.getObject(4));
                assertEquals(Long.valueOf(results.getLong(5)), serverId);
            }

            SQL ruleMatchQuery1 = new SQL() {
                {
                    SELECT("match_id", "check_id", "rule_id", "match_count");
                    FROM("rule_matches");
                    WHERE("check_id in (1, 2, 3)");
                }
            };
            try (ResultSet results = DatabaseAccess.executeStatement(ruleMatchQuery1)) {
                assertFalse(results.next());
            }

            SQL ruleMatchQuery2 = new SQL() {
                {
                    SELECT("r.match_id", "r.check_id", "r.rule_id", "r.match_count");
                    FROM("rule_matches r");
                    INNER_JOIN("check_log c on c.id = r.check_id");
                    WHERE("c.text_session_id = 123456789");
                    ORDER_BY("r.match_id");
                }
            };
            try (ResultSet results = DatabaseAccess.executeStatement(ruleMatchQuery2)) {
                results.next();
                assertEquals(results.getLong(1), 1);
                assertEquals(results.getLong(2), 4);
                assertEquals(results.getString(3), "EN_A_VS_AN");
                assertEquals(results.getInt(4), 1);

                results.next();
                assertEquals(results.getLong(1), 2);
                assertEquals(results.getLong(2), 4);
                assertEquals(results.getString(3), "MORFOLOGIK_RULE_EN_US");
                assertEquals(results.getInt(4), 1);

                results.next();
                assertEquals(results.getLong(1), 3);
                assertEquals(results.getLong(2), 5);
                assertEquals(results.getString(3), "MORFOLOGIK_RULE_EN_US");
                assertEquals(results.getInt(4), 1);
            }

            int check_count;
            // test committing after batch size is reached
            SQL checkCount = new SQL() {
                {
                    SELECT("COUNT(*)");
                    FROM("check_log");
                }
            };
            try (ResultSet results = DatabaseAccess.executeStatement(checkCount)) {
                results.next();
                check_count = results.getInt(1);
            }
            for (int i = 0; i < DatabaseLogger.SQL_BATCH_SIZE; i++) {
                check(en, String.format("This is the check with batch number %d.", i), null, null, null, null);
            }
            int check_count2;
            try (ResultSet results = DatabaseAccess.executeStatement(checkCount)) {
                results.next();
                check_count2 = results.getInt(1);
                assertThat(check_count2, is(check_count + DatabaseLogger.SQL_BATCH_SIZE));
            }
            // test committing after wait time elapsed
            check(en, "This is a check I am waiting for.", null, null, null, null);
            int check_count3;
            try (ResultSet results = DatabaseAccess.executeStatement(checkCount)) {
                results.next();
                check_count3 = results.getInt(1);
                assertThat(check_count2, is(check_count3));
            }
            Thread.sleep(DatabaseLogger.SQL_BATCH_WAITING_TIME);
            int check_count4;
            try (ResultSet results = DatabaseAccess.executeStatement(checkCount)) {
                results.next();
                check_count4 = results.getInt(1);
                assertThat(check_count4, is(check_count3 + 1));
            }
        } finally {
            server.stop();
        }
    } finally {
        logger.dropTestTables();
        DatabaseAccess.deleteTestTables();
    }
}