insert into company_candidate(company_id,user_id,card_id,facebook_id,type,create_time,weight,score) VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE type=?,weight=?,score=?

@Documented@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.TYPE)public @interface Table {  /**   * 表名   * @return   */  String tableName() default "";  /**   * 數(shù)據(jù)庫名稱   * @return   */  String dbName();}

@Documented@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.FIELD)public @interface TableField {  /**   * 對應(yīng)數(shù)據(jù)庫字段名稱   * @return   */  String fieldName() default "";  /**   * 是否是主鍵   * @return   */  boolean pk() default false;  /**   * 是否忽略該字段   * @return   */  boolean ignore() default false;  /**   * 當(dāng)數(shù)據(jù)存在時(shí)，是否更新該字段   * @return   */  boolean update() default false;}

@Table(dbName = "company", tableName = "company_candidate")public class CompanyCandidateModel implements Serializable{ private static final long serialVersionUID = -1234554321773322135L; @TableField(fieldName = "company_id") private int companyId; @TableField(fieldName = "user_id") private int userId; //名片id @TableField(fieldName = "card_id") private int cardId; //facebookId @TableField(fieldName = "facebook_id") private long facebookId;  @TableField(fieldName="type", update = true) private int type; @TableField(fieldName = "create_time") private Date createTime; @TableField(fieldName = "update_time", ignore=true) private Date updateTime; // 權(quán)重  @TableField(fieldName="weight", update = true) private int weight; // 分值  @TableField(fieldName="score", update = true) private double score;

public class ReflectUtil {  /**   * <Class,<表定義Field名,Bean定義Field>>的map緩存   */  private static final Map<Class<?>, Map<string field="">> classTableBeanFieldMap = new HashMap<Class<?>, Map<string field="">>();  // 用來按順序填充SQL參數(shù)，其中存儲的Field和classTableBeanFieldMap保存同樣的順序，但數(shù)量多出ON DUPLICATE KEY UPDATE部分Field  private static final Map<Class<?>, List<field>> sqlParamFieldsMap = new HashMap<Class<?>, List<field>>();   private ReflectUtil(){};  /**   * 獲取該類上所有@TableField注解，且沒有忽略的字段的Map。   * <br />返回一個(gè)有序的LinkedHashMap類型   * <br />其中key為DB表中的字段，value為Bean類里的屬性Field對象   * @param clazz   * @return   */  public static Map<string field=""> getTableBeanFieldMap(Class<?> clazz) {   // 從緩存獲取   Map<string field=""> fieldsMap = classTableBeanFieldMap.get(clazz);   if (fieldsMap == null) {   fieldsMap = new LinkedHashMap<string field="">();      for (Field field : clazz.getDeclaredFields()) {// 獲得所有聲明屬性數(shù)組的一個(gè)拷貝       TableField annotation = field.getAnnotation(TableField.class);        if (annotation != null && !annotation.ignore() && !"".equals(annotation.fieldName())) {          field.setAccessible(true);// 方便后續(xù)獲取私有域的值         fieldsMap.put(annotation.fieldName(), field);        }  }      // 放入緩存      classTableBeanFieldMap.put(clazz, fieldsMap);   }   return fieldsMap;  }  /**   * 獲取該類上所有@TableField注解，且沒有忽略的字段的Map。ON DUPLICATE KEY UPDATE后需要更新的字段追加在list最后，為了填充參數(shù)值準(zhǔn)備   * <br />返回一個(gè)有序的ArrayList類型   * <br />其中key為DB表中的字段，value為Bean類里的屬性Field對象   * @param clazz   * @return   */  public static List<field> getSqlParamFields(Class<?> clazz) {   // 從緩存獲取   List<field> sqlParamFields = sqlParamFieldsMap.get(clazz);   if (sqlParamFields == null) {   // 獲取所有參數(shù)字段     Map<string field=""> fieldsMap = getTableBeanFieldMap(clazz);   sqlParamFields = new ArrayList<field>(fieldsMap.size() * 2);     // SQL后段ON DUPLICATE KEY UPDATE需要更新的字段     List<field> updateParamFields = new ArrayList<field>();   Iterator<Entry<string field="">> iter = fieldsMap.entrySet().iterator();   while (iter.hasNext()) {    Entry<string field=""> entry = (Entry<string field="">) iter.next();    Field field = entry.getValue();    // insert語句對應(yīng)sql參數(shù)字段    sqlParamFields.add(field);        // ON DUPLICATE KEY UPDATE后面語句對應(yīng)sql參數(shù)字段        TableField annotation = field.getAnnotation(TableField.class);    if (annotation != null && !annotation.ignore() && annotation.update()) {    updateParamFields.add(field);    }   }   sqlParamFields.addAll(updateParamFields);      // 放入緩存   sqlParamFieldsMap.put(clazz, sqlParamFields);   }   return sqlParamFields;  }  /**   * 獲取表名,對象中使用@Table的tableName來標(biāo)記對應(yīng)數(shù)據(jù)庫的表名，若未標(biāo)記則自動(dòng)將類名轉(zhuǎn)成小寫   *    * @param clazz   * @return   */  public static String getTableName(Class<?> clazz) {    Table table = clazz.getAnnotation(Table.class);    if (table != null && table.tableName() != null && !"".equals(table.tableName())) {      return table.tableName();    }    // 當(dāng)未配置@Table的tableName，自動(dòng)將類名轉(zhuǎn)成小寫    return clazz.getSimpleName().toLowerCase();  }  /**   * 獲取數(shù)據(jù)庫名,對象中使用@Table的dbName來標(biāo)記對應(yīng)數(shù)據(jù)庫名   * @param clazz   * @return   */  public static String getDBName(Class<?> clazz) {    Table table = clazz.getAnnotation(Table.class);    if (table != null && table.dbName() != null) {      // 注解@Table的dbName      return table.dbName();    }    return "";  }

insert into company_candidate(company_id,user_id,card_id,facebook_id,type,create_time,weight,score) VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE type=?,weight=?,score=?

public class SQLUtil {  private static final char COMMA = ',';  private static final char BRACKETS_BEGIN = '(';  private static final char BRACKETS_END = ')';  private static final char QUESTION_MARK = '?';  private static final char EQUAL_SIGN = '=';  private static final String INSERT_BEGIN = "INSERT INTO ";  private static final String INSERT_VALURS = " VALUES ";  private static final String DUPLICATE_UPDATE = " ON DUPLICATE KEY UPDATE ";  // 數(shù)據(jù)庫表名和對應(yīng)insertupdateSQL的緩存  private static final Map<string string=""> tableInsertSqlMap = new HashMap<string string="">();  /**   * 獲取插入的sql語句，對象中使用@TableField的fieldName來標(biāo)記對應(yīng)數(shù)據(jù)庫的列名，若未標(biāo)記則忽略   * 必須標(biāo)記@TableField(fieldName = "company_id")注解   * @param tableName   * @param fieldsMap   * @return   * @throws Exception   */  public static String getInsertSql(String tableName, Map<string field=""> fieldsMap) throws Exception {   String sql = tableInsertSqlMap.get(tableName);   if (sql == null) {   StringBuilder sbSql = new StringBuilder(300).append(INSERT_BEGIN);   StringBuilder sbValue = new StringBuilder(INSERT_VALURS);   StringBuilder sbUpdate = new StringBuilder(100).append(DUPLICATE_UPDATE);   sbSql.append(tableName);   sbSql.append(BRACKETS_BEGIN);   sbValue.append(BRACKETS_BEGIN);   Iterator<Entry<string field="">> iter = fieldsMap.entrySet().iterator();   while (iter.hasNext()) {    Entry<string field=""> entry = (Entry<string field="">) iter.next();    String tableFieldName = entry.getKey();    Field field = entry.getValue();    sbSql.append(tableFieldName);    sbSql.append(COMMA);    sbValue.append(QUESTION_MARK);    sbValue.append(COMMA);    TableField tableField = field.getAnnotation(TableField.class);    if (tableField != null && tableField.update()) {    sbUpdate.append(tableFieldName);    sbUpdate.append(EQUAL_SIGN);    sbUpdate.append(QUESTION_MARK);    sbUpdate.append(COMMA);    }   }   // 去掉最后的逗號   sbSql.deleteCharAt(sbSql.length() - 1);   sbValue.deleteCharAt(sbValue.length() - 1);   sbSql.append(BRACKETS_END);   sbValue.append(BRACKETS_END);   sbSql.append(sbValue);   if (!sbUpdate.toString().equals(DUPLICATE_UPDATE)) {    sbUpdate.deleteCharAt(sbUpdate.length() - 1);    sbSql.append(sbUpdate);   }   sql = sbSql.toString();   tableInsertSqlMap.put(tableName, sql);   }    return sql;  }

  /**   * 批量插入，如果主鍵一致則更新。結(jié)果返回更新記錄條數(shù)<br />   * @param dataList   *      要插入的對象List   * @param batchNum   *      每次批量插入條數(shù)   * @return 更新記錄條數(shù)   */  public int batchInsertSQL(List<? extends Object> dataList, int batchNum) throws Exception {   if (dataList == null || dataList.isEmpty()) {   return 0;   }    Class<?> clazz = dataList.get(0).getClass();    String tableName = ReflectUtil.getTableName(clazz);    String dbName = ReflectUtil.getDBName(clazz);    Connection connnection = null;    PreparedStatement preparedStatement = null;    // 獲取所有需要更新到DB的屬性域    Map<string field=""> fieldsMap = ReflectUtil.getTableBeanFieldMap(dataList.get(0).getClass());    // 根據(jù)需要插入更新的字段生成SQL語句    String sql = SQLUtil.getInsertSql(tableName, fieldsMap);    log.debug("prepare to start batch operation , sql = " + sql + " , dbName = " + dbName);    // 獲取和SQL語句同樣順序的填充參數(shù)Fields    List<field> sqlParamFields = ReflectUtil.getSqlParamFields(dataList.get(0).getClass());    // 最終更新結(jié)果條數(shù)    int result = 0;    int parameterIndex = 1;// SQL填充參數(shù)開始位置為1    // 執(zhí)行錯(cuò)誤的對象    List<object> errorsRecords = new ArrayList</object><object>(batchNum);//指定數(shù)組大小    // 計(jì)數(shù)器，batchNum提交后內(nèi)循環(huán)累計(jì)次數(shù)    int innerCount = 0;    try {      connnection = this.getConnection(dbName);      // 設(shè)置非自動(dòng)提交      connnection.setAutoCommit(false);      preparedStatement = connnection.prepareStatement(sql);      // 當(dāng)前操作的對象      Object object = null;      int totalRecordCount = dataList.size();      for (int current = 0; current < totalRecordCount; current++) {        innerCount++;        object = dataList.get(current);       parameterIndex = 1;// 開始參數(shù)位置為1       for(Field field : sqlParamFields) {       // 放入insert語句對應(yīng)sql參數(shù)          preparedStatement.setObject(parameterIndex++, field.get(object));       }       errorsRecords.add(object);        preparedStatement.addBatch();        // 達(dá)到批量次數(shù)就提交一次        if (innerCount >= batchNum || current >= totalRecordCount - 1) {          // 執(zhí)行batch操作          preparedStatement.executeBatch();          preparedStatement.clearBatch();          // 提交          connnection.commit();          // 記錄提交成功條數(shù)          result += innerCount;          innerCount = 0;          errorsRecords.clear();        }        // 盡早讓GC回收        dataList.set(current, null);      }      return result;    } catch (Exception e) {      // 失敗后處理方法      CallBackImpl.getInstance().exectuer(sql, errorsRecords, e);      BatchDBException be = new BatchDBException("batch run error , dbName = " + dbName + " sql = " + sql, e);      be.initCause(e);      throw be;    } finally {      // 關(guān)閉      if (preparedStatement != null) {       preparedStatement.clearBatch();        preparedStatement.close();      }      if (connnection != null)        connnection.close();    }  }

List<companycandidatemodel> updateDataList = new ArrayList<companycandidatemodel>(50000);// ...為updateDataList填充數(shù)據(jù)int result = batchJdbcTemplate.batchInsertSQL(updateDataList, 50);