自动分表

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import java.util.Date;public abstract class AIPkCountSplitTable<T> {public abstract T mapRsRow(ResultSet rs) throws SQLException;public abstract boolean createTable(Connection con, int autokeyStart) throws SQLException;private String tableName;private String pkColName;private int splitCount;public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public String getPkColName() {return pkColName;}public void setPkColName(String pkColName) {this.pkColName = pkColName;}public int getSplitCount() {return splitCount;}public void setSplitCount(int splitCount) {this.splitCount = splitCount;}private void setStmtArg(PreparedStatement prepStatement, Object[] params) throws SQLException{if(null == params)return;for(int i = 0; i < params.length; ++i){Object param = params[i];   if (param instanceof Integer) {    int value = ((Integer) param).intValue();    prepStatement.setInt(i + 1, value);   } else if (param instanceof String) {    String s = (String) param;    prepStatement.setString(i + 1, s);   } else if (param instanceof Double) {    double d = ((Double) param).doubleValue();    prepStatement.setDouble(i + 1, d);   } else if (param instanceof Float) {    float f = ((Float) param).floatValue();    prepStatement.setFloat(i + 1, f);   } else if (param instanceof Long) {    long l = ((Long) param).longValue();    prepStatement.setLong(i + 1, l);   } else if (param instanceof Boolean) {    boolean b = ((Boolean) param).booleanValue();    prepStatement.setBoolean(i + 1, b);   } else if (param instanceof Date) {    Date d = (Date) param;    prepStatement.setDate(i+1, new java.sql.Date(d.getTime()));   } }}private boolean renameTable(Connection con, int tbIdx) throws SQLException{String newTableName = tableName + tbIdx;String sql = "RENAME TABLE " + tableName + " TO " + newTableName;Statement st = con.createStatement();st.executeUpdate(sql);return true;}private int  selectMaxId(Connection con) throws SQLException{int maxId = -1;String sql = "SELECT MAX("+ pkColName +") FROM " + tableName;PreparedStatement st = con.prepareStatement(sql);ResultSet rs = st.executeQuery(sql);if(rs.next())maxId = rs.getInt(1);rs.close();return maxId;}private int  selectCount(Connection con, String tbname, String sqlWhere, Object[] args) throws SQLException{int cnt = 0;String sql = "SELECT COUNT(*) FROM " + tbname + " " + sqlWhere;PreparedStatement st = con.prepareStatement(sql);this.setStmtArg(st, args);ResultSet rs = st.executeQuery(sql);if(rs.next())cnt = rs.getInt(1);rs.close();return cnt;}public boolean deleteById(Connection con, int id) throws SQLException{try{String sql = "DELETE FROM " + tableName + " WHERE " + pkColName + "=?";PreparedStatement st = con.prepareStatement(sql);st.setInt(1, id);if(1 == st.executeUpdate())return true;}catch(SQLException ex){}int idx = id / splitCount;String acTableName = this.tableName + idx;String sql2 = "DELETE FROM " + acTableName + " WHERE " + pkColName + "=?";PreparedStatement st2 = con.prepareStatement(sql2);st2.setInt(1, id);if(1 == st2.executeUpdate())return true;return false;}// where xxx=? and yyy=?public int delete(Connection con, String sqlWhere, Object[] args) throws SQLException{int nupdate = 0;int maxId = selectMaxId(con);int tableIdx = maxId / splitCount;++tableIdx;for(int i = 0 ; i < tableIdx; ++i){String subTableName = "";if(i == tableIdx-1)subTableName = this.tableName;elsesubTableName = this.tableName + i;String sql = "DELETE FROM " + subTableName + " " +  sqlWhere;PreparedStatement st = con.prepareStatement(sql);this.setStmtArg(st, args);nupdate += st.executeUpdate();}return nupdate;}private boolean innerInsert(Connection con, PreparedStatement insStmt, T value, PreparedStmtParamSetter<T> paramSetter) throws SQLException{int id = -1;paramSetter.setParams(insStmt, value);if(1 == insStmt.executeUpdate()){ResultSet keys = insStmt.getGeneratedKeys();if(keys.next()){Integer generatedId = keys.getInt(1);id = generatedId.intValue();}else{ return false;}}else{ return false;}// 达到分表灵界先删除插入成功的，从旧表中删除，生成新表在插入// 保证新建的表中有一条数据，select Max(pk) 才管用if(0 == id % this.splitCount){String delSql = "DELETE FROM " + this.tableName + " WHERE " + this.pkColName + "=" + id;Statement delStmt = con.createStatement();if(1 != delStmt.executeUpdate(delSql))return false;// 从0开始命名表，才能用 id/splitCount直接做表名索引 1999/2000 ＝ 0, 2000/2000=1,2000在表1中int tbIdx = id / splitCount - 1 ;renameTable(con, tbIdx);createTable(con, id);paramSetter.setParams(insStmt, value);if(1 == insStmt.executeUpdate()){ResultSet keys = insStmt.getGeneratedKeys();if(keys.next()){Integer generatedId = keys.getInt(1);id = generatedId.intValue();}else{ return false;}}else{ return false;}}paramSetter.setId(value, id);return true;}//" (col1, col2) values(?, 1,?)"public boolean insert(Connection con, String sql, T value, PreparedStmtParamSetter<T> paramSetter) throws SQLException{String insSql = "INSERT INTO " + this.tableName + sql;PreparedStatement insStmt = con.prepareStatement(insSql,Statement.RETURN_GENERATED_KEYS);return innerInsert(con, insStmt, value, paramSetter);}public boolean insertBatch(Connection con, String sql, List<T> values, PreparedStmtParamSetter<T> paramSetter) throws SQLException{if(null == values || values.size() == 0)return true;String insSql = "INSERT INTO " + this.tableName + sql;PreparedStatement insStmt = con.prepareStatement(insSql,Statement.RETURN_GENERATED_KEYS);for(int i = 0; i < values.size(); ++i){T value = values.get(i);if(!innerInsert(con, insStmt, value, paramSetter))return false;}return true;}private List<T> innerSelectAll(Connection con, String sqlWhere, Object[] args, int startTbIdx) throws SQLException{// 5999/2000 = 2,以前有0,1两张表int maxId = selectMaxId(con);int tableIdx = maxId / splitCount;++tableIdx; // 加成3，表名索引为2，就是当前表了，便于在一个循环中处理完List<T> elems = new ArrayList<T>();for(int i = startTbIdx ; i < tableIdx; ++i){String subTableName = "";if(i == tableIdx-1)subTableName = this.tableName;elsesubTableName = this.tableName + i;String sql = "SELECT * FROM " + subTableName + " " + sqlWhere;PreparedStatement st = con.prepareStatement(sql);this.setStmtArg(st, args);ResultSet rs = st.executeQuery();while(rs.next()){elems.add(mapRsRow(rs));}rs.close();}return elems;}// where xx=?public List<T> select(Connection con, String sqlWhere, Object[] args) throws SQLException{return innerSelectAll(con, sqlWhere, args, 0);}// 15 30 20 45public List<T> selectPage(Connection con, String sqlWhere, Object[] args, int offset, int limit)throws SQLException{int maxId = selectMaxId(con);int tableIdx = maxId / splitCount;++tableIdx;List<T> elems = new ArrayList<T>();int startTbIdx = tableIdx;int offsetCnt = 0;// 找到偏移起始表索引if(0 != offset){for(int i = 0 ; i < tableIdx; ++i){String subTableName = "";if(i == tableIdx-1)subTableName = this.tableName;elsesubTableName = this.tableName + i;int cnt = this.selectCount(con, subTableName, sqlWhere, args);offsetCnt += cnt;// 累计条数，0-i个表中的条数超过offset，说明要从第i个表开始偏移if(offsetCnt > offset){// offset 减去 0到i－1的总条数，就是在当前表中的偏移offset = offset - (offsetCnt-cnt);startTbIdx = i;break;}}}if(startTbIdx >= tableIdx)return elems;if(-1 == limit)return innerSelectAll(con, sqlWhere, args, startTbIdx);int newLimit = limit;for(int i = startTbIdx ; i < tableIdx; ++i){String subTableName = "";if(i == tableIdx-1)subTableName = this.tableName;elsesubTableName = this.tableName + i;String sql = "SELECT * FROM " + subTableName + " " + sqlWhere + "LIMIT " + offset + "," + newLimit;PreparedStatement st = con.prepareStatement(sql);this.setStmtArg(st, args);ResultSet rs = st.executeQuery();while(rs.next()){elems.add(mapRsRow(rs));--newLimit;}rs.close();offset = 0; // 只是第一张需要偏移if(newLimit <= 0) // never less thanbreak;}return elems;}public T selectById(Connection con, int id) throws SQLException{T elem = null;String sql = "SELECT * FROM " + this.tableName + " WHERE " + this.pkColName + "=?";PreparedStatement st = con.prepareStatement(sql);st.setInt(1, id);ResultSet rs = st.executeQuery();if(rs.next())elem = mapRsRow(rs);rs.close();if(null != elem)return elem;int idx = id / splitCount;String acTableName = this.tableName + idx;String sql2 = "SELECT * FROM " + acTableName + " WHERE " + this.pkColName + "=?";PreparedStatement st2 = con.prepareStatement(sql2);st2.setInt(1, id);ResultSet rs2 = st2.executeQuery();if(rs2.next())elem = mapRsRow(rs2);rs2.close();return elem;}// set xx=?, yy=? public boolean updateById(Connection con, String setSql, Object[] args, int id) throws SQLException{try{String sql1 = "UPDATE " + this.tableName + " " + setSql + " WHERE " + this.pkColName + "=?";PreparedStatement st1 = con.prepareStatement(sql1);this.setStmtArg(st1, args);st1.setInt(args.length+1, id);if(1 == st1.executeUpdate())return true;}catch(SQLException e){}int idx = id / splitCount;String acTableName = this.tableName + idx;String sql2 = "UPDATE " + acTableName + " "+ setSql + " WHERE " + this.pkColName + "=?";PreparedStatement st2 = con.prepareStatement(sql2);this.setStmtArg(st2, args);st2.setInt(args.length+1, id);if(1 == st2.executeUpdate())return true;return false;}// set xx=?, yy =? where cc=? and zz=2public int update(Connection con, String setSql, Object[] args) throws SQLException{int nupdate = 0;int maxId = selectMaxId(con);int tableIdx = maxId / splitCount;++tableIdx;for(int i = 0 ; i < tableIdx; ++i){String subTableName = "";if(i == tableIdx-1)subTableName = this.tableName;elsesubTableName = this.tableName + i;String sql = "UPDATE " + subTableName + " " +  setSql;PreparedStatement st = con.prepareStatement(sql);this.setStmtArg(st, args);nupdate += st.executeUpdate();}return nupdate;}}