一、總結
二、Bug描述：Mybatis中parameterType使用
mapper層中使用parameterType="java.lang.Integer"基本類型，代碼報錯：
//org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: // There is no getter for property named 'siteId' in 'class java.lang.Integer'
解決辦法，當入參為基本數據類型的使用，使用_parameter代替基本數據類型，如下：
<select id="getRulesInfoBysiteId" parameterType="java.lang.Integer" resultMap="RulesMap" >
SELECT
a.site_id,
a.site_name,
b.id AS city_id,
b.`name` AS city_name,
c.id AS region_id,
c.`name` AS region_name
FROM
idc_site a,
city b,
area c
WHERE
a.region = c.`name`
AND a.city = b.`name`
AND a.is_deleted = 'n'
AND b.is_deleted = 'n'
AND c.is_deleted = 'n'
<if test="_parameter != null">
AND a.site_id = #{_parameter,jdbcType=INTEGER}
</if>
</select>
或者在mapper層的接口中，給這個方法的參數加上@param(value=“siteId”)，這樣就能在.xml中使用#{siteId,jdbcType=INTEGER}了，僅使用於基本數據類型。
//mapper層對應的接口中必須加@Resource注解，否則在Dao層注入*Ext會失敗
@Resource public interface SiteMapperExt extends SiteMapper { //mapper層對應的接口中加mybatis提供的注解@Param("siteId") public RulesInfo getRulesInfoBysiteId(@Param("siteId")Integer siteId); }
更多使用詳情請看最後附文。
三、Bug描述：
/**
* 自動分配物流供應商
*/
@Override
public void autoAssignSupplier(RuleInfos ruleInfos, String deviceType, WorkOrderMain workOrder, int amounts) {
// 精確匹配規則制定 物流供應商
LogisticsAssignRules bean = getExactMatchSPId(ruleInfos.getSourceRegionId(), ruleInfos.getTargetRegionId(),
ruleInfos.getSourceCityId(), ruleInfos.getTargetCityId(),
ruleInfos.getSourceSiteName(), ruleInfos.getTargetSiteName(),
deviceType, amounts);
if (null == bean) {
// 按比例規則制定物流供應商
Map<String, String> supplierRatesMap = getSpRates();
Map<String, String> logicOf90DaysBefore = getAssignRates();
String supplierId = getSupplierIdBy90Days(supplierRatesMap, logicOf90DaysBefore, amounts);
logisticsWorkOrderBo.LogisticsAssigned(workOrder, WorkOrderStatsCst.LogisticsOrderState.unassigned,
supplierId, getSpRatesDesc(), WorkOrderCst.DEFAULT_VALUE_YES);
logger.info("auto assign supplier as rates,supplierId = {}, description = {}", supplierId, getSpRatesDesc());
} else {
// 精確匹配,直接分配物流供應商
logisticsWorkOrderBo.LogisticsAssigned(workOrder, WorkOrderStatsCst.LogisticsOrderState.unassigned,
bean.getSpId().toString(), bean.getRuleJsonVal(),
WorkOrderCst.DEFAULT_VALUE_YES);
logger.info("auto assign supplier start as rules, supplierId = {}, description = {}",
bean.getSpId().toString(), getSpRatesDesc());
}
}
在接口調用中，當傳遞屬性過多的時候，可以考慮用對象來傳遞，方便以後的擴展。如本代碼中，當後續添加規則時，需要更新方法。另外對於公用的東西，盡量維護在靜態枚舉值中。
四、Bug描述：方法入口處統一獲取當前時間new Date()
在代碼中的時間要作為條件來篩選數據，如果同一個方法中，在多個地方出現new Date(),算上程序執行的納秒級別的時間，可能在當前日期的“23:59:59 納秒”產生跨界時間的問題，給代碼造成概率極低的隱患。
SELECT
d.sp_id,
COUNT(a.sn) AS asset_counts
FROM
idc_asset_list a
LEFT JOIN idc_work_order_main b ON a.order_id = b.id
LEFT JOIN idc_order_atomic_list c ON c.order_id = b.id
LEFT JOIN idc_atomic_logistics d ON d.atomic_id = c.atomic_id
WHERE
a.is_deleted = 'n'
AND b.is_deleted = 'n'
AND c.is_deleted = 'n'
AND d.is_deleted = 'n'
AND d.sp_id IS NOT NULL
AND b.gmt_create < CONCAT('2016-08-04', '23:59:59')
AND b.gmt_create > date_sub(
'2016-08-04 00:00:00',
INTERVAL 3 MONTH
)
AND (
b.state != 'cancle'
OR b.sub_state != 'cancle'
)
GROUP BY
d.sp_id
ORDER BY
sp_id DESC
因為要將上述數據緩存到JVM中，數據結構在集群中的一台機器上只維護一份。一天最多查詢8次。
使用到的SQL如下：
<select id = "getLogisticsList90DaysBefore" parameterType="java.lang.String" resultType ="java.util.Map">
SELECT
d.sp_id AS spId,
COUNT(a.sn) AS assetCounts
FROM
idc_asset_list a
LEFT JOIN idc_work_order_main b ON a.order_id = b.id
LEFT JOIN idc_order_atomic_list c ON c.order_id = b.id
LEFT JOIN idc_atomic_logistics d ON d.atomic_id = c.atomic_id
WHERE
a.is_deleted = 'n'
AND b.is_deleted = 'n'
AND c.is_deleted = 'n'
AND d.is_deleted = 'n'
AND d.sp_id IS NOT NULL
AND (b.state != 'cancle' OR b.sub_state != 'cancle')
<if test = "_parameter != null and _parameter !=''">
AND a.gmt_create <= CONCAT(#{yesterday},' 23:59:59')
AND a.gmt_create >= DATE_SUB(CONCAT(#{yesterday},' 00:00:00'), INTERVAL 3 MONTH)
</if>
GROUP BY
d.sp_id
ORDER BY
sp_id DESC
</select>
mapper層的代碼中，我們使用了mysql函數date_sub(concat(""), interval 3 month),並且返回resultType="java.util.Map",我們使用結構List<String,Map<String,Object>>結構來接收查詢結果，而沒有采用resultMap封裝對象來接收結果。
SQL執行之後的返回結果為list，通過斷點跟蹤獲悉sp_id為Integer類型，asset_counts為Long類型。
//獲取spId
Integer spId = map.get("spId");
//獲取assetCounts
Long assetCounts = map.get("assetCounts");
故使用如下代碼獲取查詢結果，但是代碼中封裝了數據類型，所以統一采用Object來獲取。
五、Bug描述：考慮到線上缺失配置文件，添加空指針判斷；為程序健壯性，必須在前後端同時對參數完整性作出校驗。
/**
* 校驗參數的完整性 {設備類型與數量必填，用於規則匹配校驗}
*/
private void checkParameters(AssignSupplierRulesDTO dto) {
// 數量合理性校驗
if (StringUtils.isNotBlank(dto.getAssetNum())) {
if (dto.getAssetNum().toCharArray().length <= 1) {
throw new ServiceException(ErrorCode.Params_Lost);
} else {
if (!(StringUtils.isNumeric(dto.getAssetNum().substring(1)))) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
if (!("><=≤≥≠".contains(dto.getAssetNum().substring(0, 1)))) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
// 供應商必填
if (null == dto.getSpId()) {
throw new ServiceException(ErrorCode.Params_Lost);
}
// 規則名稱必填
if (StringUtils.isBlank(dto.getRuleName())) {
throw new ServiceException(ErrorCode.Params_Lost);
}
// 當指定規則類型的時候，關聯性校驗
if (StringUtils.isNotBlank(dto.getRuleType())) {
// 同城校驗
if (dto.getRuleType().equals(WorkOrderCst.RelocationType.SameCity.name())) {
if (!(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceCity()))
&& !(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceCity()))) {
if (dto.getSourceCity() != dto.getTargetCity()) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
// 同區域內校驗
if (dto.getRuleType().equals(WorkOrderCst.RelocationType.RegionalIn)) {
if (!(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceRegion()))
&& !(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getTargetRegion()))) {
if (StringUtils.isNotBlank(dto.getSourceRegion()) && StringUtils.isNotBlank(dto.getTargetRegion())) {
// 區域必須相等
if (!(dto.getSourceRegion().equals(dto.getTargetRegion()))) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
if (!(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceCity()))
&& !(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getTargetCity()))) {
if (StringUtils.isNotBlank(dto.getSourceCity()) && StringUtils.isNotBlank(dto.getTargetCity())) {
if (!addressBo.whetherCityInTheSameArea(dto.getSourceCity(), dto.getTargetCity())) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
if (!(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceSite()))
&& !(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getTargetSite()))) {
if (StringUtils.isNotBlank(dto.getSourceSite()) && StringUtils.isNotBlank(dto.getTargetSite())) {
if (!addressBo.whetherSiteInTheSameArea(dto.getSourceSite(), dto.getTargetSite())) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
}
// 不同區域的校驗
if (dto.getRuleType().equals(WorkOrderCst.RelocationType.RegionalOut)) {
if (!(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceRegion()))
&& !(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getTargetRegion()))) {
if (StringUtils.isNotBlank(dto.getSourceRegion()) && StringUtils.isNotBlank(dto.getTargetRegion())) {
if (dto.getSourceRegion().equals(dto.getTargetRegion())) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
if (!(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceCity()))
&& !(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getTargetCity()))) {
if (StringUtils.isNotBlank(dto.getSourceCity()) && StringUtils.isNotBlank(dto.getTargetCity())) {
if (addressBo.whetherCityInTheSameArea(dto.getSourceCity(), dto.getTargetCity())) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
if (!(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getSourceSite()))
&& !(WorkOrderCst.MATCH_ALL_PARAMETERS.equals(dto.getTargetSite()))) {
if (StringUtils.isNotBlank(dto.getSourceSite()) && StringUtils.isNotBlank(dto.getTargetSite())) {
if (addressBo.whetherSiteInTheSameArea(dto.getSourceSite(), dto.getTargetSite())) {
throw new ServiceException(ErrorCode.Params_Invalid);
}
}
}
}
}
}
六、Bug描述：String轉Integer；String轉int的熟練使用。
public class Test {
public static void main(String[] args) {
String number = "520";
Integer a = 521;
int b = 522;
//String轉Integer
Integer.valueOf(number);
//String轉int
Integer.parseInt(number);
new Integer(number).intValue();
//Integer轉String
a.toString();
//Integer轉int
a.intValue();
//int轉String
String.valueOf(b);
Integer.toString(b);
String str = "" + b;
//int轉Integer
new Integer(b);
//String轉BigDecimal
new BigDecimal(number);
//獲取今天日期
new Date(System.currentTimeMillis()); // Fri Aug 05 20:16:07 CST 2016
DateFormat.getDateInstance().format(new Date()); // 2016-8-5
}
}
七、List和數組的轉換
public class Test {
public static void main(String[] args) {
String[] family = { "XuG", "XuX", "GaiZ", "LianW" };
List<String> list = new ArrayList<String>(Arrays.asList("XuG", "XuX", "GaiZ", "LianW"));
//數組轉list
List<String> list_01 = new ArrayList<String>(Arrays.asList(family));
//list轉數組
String[] str = (String[])list.toArray();
}
}
八、Bug描述：變量命名規范。
變量的命名規范要有意義，在數據庫建表，創建java bean的時候，一定要保證單詞使用的正確性。如label和lable；region和regin。要注意到變量的命名可能跟數據庫的關鍵字或java的關鍵字有沖突，可以采用下劃線的原則處理關鍵字沖突。
九、Bug描述：邏輯嚴謹性。
private String getSupplierIdBy90Days(Map<String, String> supplierRatesMap, Map<String, String> logicOf90DaysBefore,
int dispatchNum) {
int ratesCount = 0, dispatchCount = 0;
for (String spId : supplierRatesMap.keySet()) {
ratesCount = ratesCount + Integer.parseInt(supplierRatesMap.get(spId));
}
for (String spId : logicOf90DaysBefore.keySet()) {
dispatchCount = dispatchCount + Integer.parseInt(logicOf90DaysBefore.get(spId));
}
Map<String, String> idealizedMap = new HashMap<String, String>();
for (String spId : supplierRatesMap.keySet()) {
Integer dispathNum = (dispatchCount * Integer.parseInt(supplierRatesMap.get(spId))) / ratesCount;
idealizedMap.put(spId, dispathNum.toString());
}
int gap = -1;
String supplierId = StringUtils.EMPTY;
if (CollectionUtils.isNotEmpty(logicOf90DaysBefore.keySet())) {
for (String spId : logicOf90DaysBefore.keySet()) {
if (null != idealizedMap.get(spId)) {
int mix = Integer.parseInt(idealizedMap.get(spId))
- Integer.parseInt(logicOf90DaysBefore.get(spId));
if (mix < gap) {
gap = mix;
supplierId = spId;
}
} else {
supplierId = spId; // 新添加的供應商比例
}
}
} else {
supplierId = new ArrayList<String>(supplierRatesMap.keySet()).get(0);
}
return supplierId;
}
有判斷if條件的地方，要考慮到else的可能出現情況，尤其是if else 嵌套多層的時候，可能某些else的情況遺漏，會給程序帶來問題。如上述代碼中的else的缺失，可能在“新添加供應商比例”的情況下，出現沒有分配供應商的情況。
十、VPN工具
VPN工具下載使用：Cisco AnyConnect VPN Client 64位下載
附：Mybatis中parameterType和resultType的相關用法
1、parameterType用法，parameterType的傳入參數類型大致分為如下幾類：
基本數據類型(_parameter接收參數)
<!-- mapper對應接口層 -->
public RulesInfo getRulesInfoBysiteId(Integer siteId);
<!-- mapper中指定Integer類型，用_parameter來接收 -->
<select id="getRulesInfoBysiteId" parameterType="java.lang.Integer" resultMap="RulesMap" >
SELECT
a.site_id,
a.site_name,
b.id AS city_id,
b.`name` AS city_name,
c.id AS region_id,
c.`name` AS region_name
FROM
idc_site a,
city b,
area c
WHERE
a.region = c.`name`
AND a.city = b.`name`
AND a.is_deleted = 'n'
AND b.is_deleted = 'n'
AND c.is_deleted = 'n'
<if test="_parameter != null">
AND a.site_id = #{_parameter,jdbcType=INTEGER}
</if>
</select>
數組類型(foreach中的collection必須是array，不管變量的具體名稱是什麼。如下面變量名為ids，collection卻是array）
<!-- mapper對應接口層：根據工單角色獲取有權限的機房 -->
public List<User> findUserListByIdList(int[] ids);
<!-- mapper中指定java.util.List類型，一般用於MySQL中IN語句中 -->
<select id="findUserListByIdList" parameterType="java.util.HashList" resultType="User">
select * from user
<where>
user.ID in (
<foreach item="guard" index="index" collection="array" separator=",">
#{guard}
</foreach>
)
</where>
</select>
List類型(單獨傳入list時，foreach中的collection必須是list，不管變量的具體名稱是什麼。比如代碼中變量名為staffIds,collection中確實list)
<!-- mapper對應接口層 -->
public List<IdcStaff> selectStaffsbyIdnumbers(List<String> staffIds);
<!-- mapper中指定java.util.List類型，一般用於MySQL中IN語句中 -->
<select id="selectStaffsbyIdnumbers" parameterType="java.util.List" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM idc_staff where id_number IN
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
AND IS_DELETED='n'
ORDER BY staff_status
</select>
Map類型(MAP中有list或array時，foreach中的collection必須是具體list或array的變量名。同上)
<!--*BoImpl.java層構造傳入的map參數-->
public List<SiteUserVo> getSiteUserPermission(String siteName, Long roleId, String workNo) {
Map<String, Object> siteByUser = new HashMap<String, Object>();
if (StringUtils.isNotBlank(siteName) && !"".equals(siteName.trim())) {
siteByUser.put("site", siteName);
}
if (null != roleId && 0 != roleId) {
siteByUser.put("roleId", roleId);
}
if (StringUtils.isNotBlank(workNo) && !"".equals(workNo.trim())) {
siteByUser.put("workNo", workNo);
}
return siteMapperExt.getSiteUserPermission(siteByUser);
}
<!-- mapper對應接口層：根據工單角色獲取有權限的機房 -->
public List<SiteUserVo> getSiteUserPermission(Map siteByUser);
<!-- mapper中指定java.util.List類型，一般用於MySQL中IN語句中 -->
<select id="getSiteUserPermission" parameterType="java.util.HashMap" resultMap="SiteUserVoMap">
SELECT
i.site_name,
t.role_id,
h.work_no
FROM
idc_site_user t
LEFT JOIN idc_site i ON t.site_id = i.site_id
LEFT JOIN app_user h ON t.user_id = h.id
WHERE
t.IS_DELETED = 'n'
AND h.IS_DELETED = 'n'
AND i.IS_DELETED = 'n'
<IF test = "site!=null" >
AND i.site_name = #{site}
</IF >
<IF test = "roleId!=null" >
AND t.role_Id = #{roleId}
</IF >
<IF test = "workNo!=null" >
AND h.work_no = #{workNo}
</IF >
</select>
Java對象
<!-- mapper對應接口層：根據工單角色獲取有權限的機房 -->
List<AssignRulesVo> selectByQuery(QueryAssignRulesrDo query);
<!-- mapper中指定對象類型，對象的屬性名可以直接使用;如果要在SQL中判定對象是否為空,還要用_parameter -->
<select id="selectByQuery" parameterType="com.alibaba.tboss.dal.mysql.workOrder.query.QueryAssignRulesrDo" resultMap="BaseResult">
SELECT
a.id,
a.is_valid,
a.rule_lable,
a.rule_name,
a.type,
b.sp_id,
b.sp_name,
a.rule_content,
c.user_name,
a.gmt_modified,
a.ordering,
a.rule_json_val
FROM
idc_logistics_assign_rules a
LEFT JOIN app_user c on c.work_no=a.modifier and c.is_deleted='n',
idc_sp_info b
WHERE
a.is_deleted = 'n'
AND b.is_deleted = 'n'
AND a.sp_id = b.sp_id
<if test="ruleId != null">
AND a.id = #{ruleId,jdbcType=BIGINT}
</if>
<if test="ruleName != null and ruleName != ''">
AND a.rule_name IN (${ruleName})
</if>
ORDER BY ordering asc
<if test="doPage == true">
limit #{skip,jdbcType=INTEGER}, #{take,jdbcType=INTEGER}
</if>
</select>
補充${}會導致SQL注入攻擊，不建議使用。
//取值的時候用的是#{}。它具體的意思是告訴MyBatis創建一個預處理語句參數。
//使用JDBC，這樣的一個參數在SQL中會由一個“?”來標識，並被傳遞到一個新的預處理語句中。
//一般情況下，我們采用#{}取值，產生預處理語句，但是有時我們可能不希望Mybatis來幫我們預處理。
//${columnName},這裡MyBatis不會修改或轉義字符串。而是直接拼接到SQL字符串後面。
