org.apache.ibatis.jdbc.SQL Java Examples

public String getInvtHeadListByCopNoSql(final String copNo) {
	return new SQL() {{
		this.SELECT("guid");
		this.SELECT("pre_no");
		this.SELECT("invt_no");
		this.SELECT("detailscode");
		this.SELECT("applycode");
		this.SELECT("audit_state");
		this.SELECT("bw_name");
		this.SELECT("apply_date");
		this.SELECT("payecode");
		this.SELECT("payename");
		this.FROM("imp_invt_head");
		
		if (!StringUtils.isEmpty(copNo)) {
			this.WHERE("cop_no = '" + copNo + "'");
		} else {
			this.WHERE("1 = 2");
		}
	}}.toString();
}
 

public String comparisonDeliveryByLogisticsNoSql(final String logisticsCode, final String logisticsNo, final String headGuid) {
	return new SQL() {{
		this.SELECT("cih.head_guid as invt_head_guid");
		this.SELECT("cdh.head_guid as delivery_head_guid");
		this.SELECT("cdh.cop_no");
		this.SELECT("cdh.voyage_no as delivery_voyage_no");
		this.SELECT("cih.voyage_no as invt_voyage_no");
		this.SELECT("cdh.bill_no as delivery_bill_no");
		this.SELECT("cih.bill_no as invt_bill_no");
		this.SELECT("cdh.traf_mode as delivery_traf_mode");
		this.SELECT("cih.traf_mode as invt_traf_mode");
		this.SELECT("cdh.traf_no as delivery_traf_no");
		this.SELECT("cih.traf_no as invt_traf_no");
		this.SELECT("cih.logistics_code");
		this.SELECT("cih.logistics_no");
		this.FROM("ceb2_invt_head cih");
		this.INNER_JOIN("ceb2_delivery_head cdh on cih.logistics_code = cih.logistics_code");
		this.INNER_JOIN("ceb2_delivery_list cdl on cih.logistics_no = cdl.logistics_no and cdh.head_guid = cdl.head_guid");
		this.WHERE("cih.head_guid = '" + headGuid + "'");
		this.WHERE("cih.logistics_code = '" + logisticsCode + "'");
		this.WHERE("cih.logistics_no = '" + logisticsNo + "'");
		this.ORDER_BY("cdh.head_guid");
	}}.toString();
}
 

public String getInvtHeadMonthCountSql() {
	return new SQL() {{
		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();
}
 

public String getUsersSql(final UserUser user) {
	return new SQL() {{
		this.SELECT("user_id, login_name, user_name");
		this.FROM("user_user");
		this.WHERE("1 = 1");
		if (!StringUtils.isEmpty(user.getUserId())) {
			this.WHERE("user_id = '" + user.getUserId() + "'");
		}
		
		if (!StringUtils.isEmpty(user.getLoginName())) {
			this.WHERE("login_name like '%" + user.getLoginName() + "%'");
		}
		
		if (!StringUtils.isEmpty(user.getUserName())) {
			this.WHERE("user_name = '" + user.getUserName() + "'");
		}
		
		this.ORDER_BY("oper_time");
	}}.toString();
}
 

/**
 * id如果传入了值，会被insert使用；如果id为null，不会被insert的columns列出
 */
public static String insert(Object obj) {
    Map<String, String> map;
    try {
        map = getFieldsMap(obj, true);
    } catch (IllegalArgumentException | IllegalAccessException e) {
        throw new RuntimeException(e);
    }
    return new SQL() {
        {
            INSERT_INTO(getTableName(obj));
            for (String col : map.keySet()) {
                VALUES(col, map.get(col));
            }
        }
    }.toString();
}
 

/**
 * This method was generated by MyBatis Generator.
 * This method corresponds to the database table user
 *
 * @mbg.generated
 */
public String insertSelective(User record) {
    SQL sql = new SQL();
    sql.INSERT_INTO("user");
    
    if (record.getId() != null) {
        sql.VALUES("id", "#{id,jdbcType=INTEGER}");
    }
    
    if (record.getUsername() != null) {
        sql.VALUES("username", "#{username,jdbcType=VARCHAR}");
    }
    
    if (record.getPsw() != null) {
        sql.VALUES("psw", "#{psw,jdbcType=VARCHAR}");
    }
    
    return sql.toString();
}
 

public String getAuthBy(@Param("id") long id, @Param("userId") int userId, @Param("fileId") long fileId, @Param
        ("fileName") String fileName, @Param("offset") int offset) {
    String sql = new SQL() {{
        SELECT("a.id,a.user_id,a.file_id,u.username,f.name file_name,f.local_url,a.is_downloadable,a" + "" + "" +
                ".is_uploadable,a.is_deletable,a.is_updatable,a.is_visible,a.create_time");
        FROM("auth a");
        JOIN("user u on u.id=a.user_id");
        JOIN("file f on f.id=a.file_id");
        if (id > 0) {
            WHERE("a.id=#{id}");
        }
        if (userId > 0) {
            WHERE("u.id=#{userId}");
        }
        if (fileId > 0) {
            WHERE("f.id=#{fileId}");
        } else if (Checker.isNotEmpty(fileName)) {
            WHERE("f.local_url like '%" + fileName + "%'");
        }
        ORDER_BY("a." + EfoApplication.settings.getStringUseEval(ConfigConsts.AUTH_ORDER_BY_OF_SETTINGS));
    }}.toString();
    int size = EfoApplication.settings.getIntegerUseEval(ConfigConsts.AUTH_PAGE_SIZE_OF_SETTINGS);
    return sql + " limit " + (offset * size) + "," + size;
}
 

public String getImpPayHeadListSql(final ImpPayHead impPayHead) {
	return new SQL() {{
		this.SELECT("guid");
		this.SELECT("app_time");
		this.SELECT("app_status");
		this.SELECT("pay_code");
		this.SELECT("pay_name");
		this.SELECT("pay_transaction_id");
		this.SELECT("pay_time");
		this.SELECT("uuid");
		this.FROM("imp_pay_head");
		
		OracleTool.where(this, "guid", impPayHead.getGuid());
		OracleTool.where(this, "pay_code", impPayHead.getPayCode());
		OracleTool.where(this, "pay_name", impPayHead.getPayName(), true);
		OracleTool.where(this, "pay_transaction_id", impPayHead.getPayTransactionId(), true);
		OracleTool.where(this, "uuid", impPayHead.getUuid());
		OracleTool.orderBy(this, impPayHead.getOrderBy());
		
	}}.toString();
}
 

public String getDeclareTopTenSql(final InvtHead invtHead) {
	return new SQL() {{
		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();
}
 

public String getUserBy(@Param("permission") int permission, @Param("condition") String condition, @Param
        ("offset") int offset) {
    String sql = new SQL() {{
        SELECT("*");
        FROM("user");
        if (permission == DefaultValues.THREE_INT) {
            WHERE("permission<3");
        } else if (permission == DefaultValues.TWO_INT) {
            WHERE("permission<2");
        } else {
            WHERE("permission<0");
        }
        if (Checker.isNotEmpty(condition)) {
            WHERE("username like '%" + condition + "%' or email like '%" + condition + "%' or real_name like '" +
                    condition + "'");
        }
        ORDER_BY(EfoApplication.settings.getStringUseEval(ConfigConsts.USER_ORDER_BY_OF_SETTINGS));
    }}.toString();
    int size = EfoApplication.settings.getIntegerUseEval(ConfigConsts.USER_PAGE_SIZE_OF_SETTINGS);
    return sql + " limit " + (offset * size) + "," + size;
}
 

private String getBaseSql(boolean isDownloaded) {
    return new SQL() {{
        SELECT("distinct f.id,f.user_id,u.username,u.avatar,f.name file_name,f.size,f.create_time,c.name " +
                "category_name,f"
                + ".description,f.tag,f.check_times,f.download_times,f.visit_url,f.is_uploadable,f.is_deletable,"
                + "f.is_updatable,f.is_downloadable,f.is_visible");
        if (isDownloaded) {
            SELECT("d.create_time download_time");
        }
        FROM("file f");
        JOIN("user u on u.id=f.user_id");
        JOIN("category c on c.id=f.category_id");
        if (isDownloaded) {
            JOIN("download d on d.file_id=f.id");
        } else {
            JOIN("auth a on a.file_id=f.id");
        }
    }}.toString();
}
 

public String selectSql() {
    return new SQL() {{
        SELECT("*");
        FROM(TABLE_NAME);
        WHERE("account=#{account}");
    }}.toString();
}
 

public String getCustomsSql() {
	return new SQL() {{
		this.SELECT("customs_code");
		this.SELECT("abbr_cust");
		this.FROM("customs");
	}}.toString();
}
 

public String getRoleListByMemberCodeSql(final String memberCode) {
	return new SQL() {{
		this.SELECT("r.code");
		this.SELECT("r.name");
		this.SELECT("r.descript");
		
		this.FROM("member m");
		this.LEFT_OUTER_JOIN("group_rel_member grm on grm.member_code = m.code");
		this.LEFT_OUTER_JOIN("group_rel_role grr on grm.group_code = grr.group_code");
		this.LEFT_OUTER_JOIN("role r on r.code = grr.role_code");
		
		this.WHERE("m.code = '" + memberCode + "'");
	}}.toString();
}
 

public String countPubRtnByBizGuidSql(final String bizGuid) {
	return new SQL() {{
		this.SELECT("count(1)");
		this.FROM("ceb2_pub_rtn");
		this.WHERE("biz_guid = '" + bizGuid + "'");
	}}.toString();
}
 

public String getCountriesSql() {
	return new SQL() {{
		this.SELECT("country_code");
		this.SELECT("coun_c_name");
		this.FROM("country");
	}}.toString();
}
 

public String get(Map<String, Object> dataMap) {
    T findParams = (T) dataMap.get("findParams");
    initFromThreadLocal();
    SQL sql = SELECT_FROM();
    sql = WHERE(sql, findParams, OPERATOR_EQUAL);
    return sql.toString();
}
 

public String getDxpReceEndListSql(final DxpReceEnd dxpReceEnd) {
	return new SQL() {{
		this.SELECT("msg_guid");
		this.SELECT("dxp_mode");
		this.SELECT("dxp_status");
		this.SELECT("dxp_host");
		this.SELECT("msg_type");
		this.SELECT("msg_name");
		this.SELECT("msg_id");
		this.SELECT("msg_key");
		this.SELECT("msg_time");
		this.SELECT("send_ad");
		this.SELECT("rece_ad");
		this.SELECT("sys_date");
		this.FROM("dxp_rece_end");
		
		OracleTool.where(this, "msg_guid", dxpReceEnd.getMsgGuid());
		OracleTool.where(this, "dxp_mode", dxpReceEnd.getDxpMode());
		OracleTool.where(this, "dxp_status", dxpReceEnd.getDxpStatus());
		OracleTool.where(this, "dxp_host", dxpReceEnd.getDxpHost());
		OracleTool.where(this, "msg_type", dxpReceEnd.getMsgType());
		OracleTool.where(this, "msg_name", dxpReceEnd.getMsgName());
		OracleTool.where(this, "msg_id", dxpReceEnd.getMsgId());
		OracleTool.where(this, "msg_key", dxpReceEnd.getMsgKey());
		OracleTool.where(this, "send_ad", dxpReceEnd.getSendAd());
		OracleTool.where(this, "rece_ad", dxpReceEnd.getReceAd());
		OracleTool.where(this, "sys_date", dxpReceEnd.getBeginSysDate(), ">=");
		OracleTool.where(this, "sys_date", dxpReceEnd.getEndSysDate(), "<=");
		OracleTool.where(this, "sys_date", dxpReceEnd.getSysDateStr(), "=");
	}}.toString();
}
 

public String getUserByUserIdSql(final String userId) {
	return new SQL() {{
		this.SELECT("user_id, login_name, user_name");
		this.FROM("user_user");
		this.WHERE("user_id = '" + userId + "'");
	}}.toString();
}
 

public String calculationPackWtSql(final String distNo) {
	return new SQL() {{
		this.UPDATE("pre_dist_head pdh");
		this.SET("(pdh.total_pack_num, pdh.total_gross_wt) = (select count(pdbl.pack_num), sum(pdbl.gross_wt) from pre_dist_bill_list pdbl where pdbl.dist_no = '" + distNo + "')");
		this.WHERE("pdh.dist_no = '" + distNo + "'");
	}}.toString();
}
 

public String deleteServerSystemSql(final String ip) {
	return new SQL() {{
		this.DELETE_FROM("server_system");
		
		this.WHERE("ip = '" + ip + "'");
	}}.toString();
}
 

public String updateServerSystemSql(final ServerSystem serverSystem) {
	return new SQL() {{
		this.UPDATE("server_system");
		
		OracleTool.set(this, "ip", serverSystem.getIp());
		OracleTool.set(this, "port", serverSystem.getPort());
		OracleTool.set(this, "description", serverSystem.getDescription());
		
		this.WHERE("ip = '" + serverSystem.getUpdateIp() + "'");
	}}.toString();
}
 

public String getTransfByCodeSql(final String transfCode) {
	return new SQL() {{
		this.SELECT("traf_code");
		this.SELECT("traf_spec");
		this.FROM("transf");
		
		if (!StringUtils.isEmpty(transfCode)) {
			this.WHERE("traf_code = '" + transfCode + "'");
		} else {
			this.WHERE("1 = 2");
		}
	}}.toString();
}
 

public String getServerSystemListSql(final ServerSystem serverSystem) {
	return new SQL() {{
		this.SELECT("ip");
		this.SELECT("port");
		this.SELECT("description");
		this.FROM("server_system");
		
		OracleTool.where(this, "ip", serverSystem.getIp());
		OracleTool.where(this, "port", serverSystem.getPort());
		OracleTool.where(this, "description", serverSystem.getDescription(), true);
	}}.toString();
}
 

public String getServerSystemByIpSql(final String ip) {
	return new SQL() {{
		this.SELECT("ip");
		this.SELECT("port");
		this.SELECT("description");
		this.FROM("server_system");
		
		this.WHERE("ip = '" + ip + "'");
	}}.toString();
}
 

public String insert(final T t) throws IllegalAccessException, InvocationTargetException, NoSuchFieldException {
    initFromThreadLocal();
    // 设置默认值
    if (t.getId() == null) {
        t.setId(UUID.randomUUID().toString());
    }
    if (t instanceof EntityWithTime) {
        Date now = Calendar.getInstance().getTime();
        if (((EntityWithTime) t).getCreateTime() == null) {
            ((EntityWithTime) t).setCreateTime(now);
        }
        if (((EntityWithTime) t).getModifyTime() == null) {
            ((EntityWithTime) t).setModifyTime(now);
        }
    }

    return new SQL() {
        {
            INSERT_INTO(tableName);

            Map<String, Property> properties = ModelUtils.getProperties(t, ColumnTarget.INSERT);
            for (Property property : properties.values()) {
                // 过滤不允许更新的字段
                if (isIgnoreUpdate(property, t)) {
                    continue;
                }

                VALUES(property.getColumnName(), "#{" + property.getName() + "}");
            }
        }
    }.toString();
}
 

public String getCountryByCodeSql(final String code) {
	return new SQL() {{
		this.SELECT("country_code");
		this.SELECT("coun_c_name");
		this.FROM("country");

		if (!StringUtils.isEmpty(code)) {
			this.WHERE("country_code = '" + code + "'");
		} else {
			this.WHERE("1 = 2");
		}
	}}.toString();
}
 

private SQL COUNT_FROM() {
    return new SQL() {
        {
            SELECT("COUNT(" + parseIdColumn() + ")");
            FROM(tableName);
        }
    };
}
 

public String getUnitByCodeSql(final String unitCode) {
	return new SQL() {{
		this.SELECT("unit_code");
		this.SELECT("unit_name");
		this.FROM("unit");
		
		if (!StringUtils.isEmpty(unitCode)) {
			this.WHERE("unit_code = '" + unitCode + "'");
		} else {
			this.WHERE("1 = 2");
		}
	}}.toString();
}
 

public String delete(Object id) {
    initFromThreadLocal();
    return new SQL() {
        {
            DELETE_FROM(tableName);
            WHERE(parseIdColumn() + " = #{id}");
        }
    }.toString();
}