Biegral Blog

最近在做项目的时候，有这么一个需求，因为某些表的数据量过大，考虑每年生成一张备份表，将上一年的数据备份到新生成的这张备份表，因为数据量大，所以就要考虑到数据查询的效率，也就是仅仅是复制表结构而不复制主键和索引，是不能满足要求的，通过多方面资料的查找，终于搞定了，现在把代码贴了，和大家分享(代码写得不咋滴，大家就将就着看，呵呵)。

package platform.business.data.client.database; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.lang.StringUtils; /** * * @author Calr He<br> * 创建日期：2010-05-08 * */ public class DBToolkit { /** * 将查询结果插入到一张新表(newTable)中, 如果 newTable 已经存在，则会先将 newTable 删除，再重新创建。 * @param fromTable * @param newTable * @param st * @param stmt1 * @param where 查询记录的 where 条件， 如： where field='A' * @throws SQLException * @author Calr He */ public static void createOldTableFromTable(String fromTable, String newTable, Statement st, Statement stmt1, String where) throws SQLException { String sql = null; //如果 newTable 已经存在，则删除 sql = "if exists (select 1 from sysobjects where id = object_id('" + newTable + "') and type = 'U') drop table " + newTable; st.execute(sql); if(where == null) where = ""; //将查询到的记录插入到 newTable sql = "select * into " + newTable + " from " + fromTable + where; st.execute(sql); int indid = 0; //主键或索引在系统表 sysindexes 中的 indid 字段的值 int keycnt = 0; //主键或索引涉及的列的数量， 如果主键索引或普通索引不是簇集索引（由status中的0x10位决定）的话，则为keycnt-1 String columnNames = null; //主键或索引涉及的列的名称 //查询表的主键 sql = "select indid, keycnt from sysindexes where status&2048=2048 and id=object_id('" + fromTable + "')"; ResultSet rs = st.executeQuery(sql); if(rs.next()){ //有设置主键 indid = rs.getInt(1); //主键索引的ID keycnt = rs.getInt(2); //主键涉及的列的数量， 如果主键索引不是簇集索引（由status中的0x10位决定）的话，则为keycnt-1 columnNames = getFieldNamesOfRelatedIndexByIndid(indid, keycnt, fromTable, st); sql = "ALTER TABLE " + newTable + " ADD CONSTRAINT PK_" + newTable + " PRIMARY KEY (" + columnNames + ")"; st.execute(sql); //给新表创建主键 } rs.close(); //查询表的索引 sql = "select indid, keycnt, name from sysindexes where status&2048!=2048 and indid > 0 and indid < 255 and id=object_id('" + fromTable + "')"; rs = st.executeQuery(sql); ResultSet rs1; String unique = ""; //是否为唯一索引的标识 String clustered = null; //是簇集索引还是非簇集索引的关键字 String indexName = null; //索引的名称 while (rs.next()) { indid = rs.getInt(1); keycnt = rs.getInt(2); indexName = rs.getString(3); //索引名称 //是否为唯一约束 sql = "select v.name from master.dbo.spt_values v, sysindexes i where i.status & v.number = v.number and v.type = 'I' and v.number = 2 and i.id = object_id('" + fromTable + "')" + " and i.indid = " + indid; rs1 = stmt1.executeQuery(sql); if (rs1.next()) { unique = StringUtils.trimToEmpty(rs1.getString(1)); }else{ unique = ""; } rs1.close(); //是否为簇集索引 if (indid == 1) { clustered = " clustered "; //簇集 } else { sql = " select count(1) from sysindexes i where status2 & 512 = 512 and i.indid = " + indid + " and i.id = object_id('" + fromTable + "')"; rs1 = stmt1.executeQuery(sql); if (rs1.next() && rs1.getInt(1) > 0) { clustered = " clustered "; //簇集 }else{ clustered = " nonclustered "; //非簇集 } rs1.close(); } columnNames = getFieldNamesOfRelatedIndexByIndid(indid, keycnt, fromTable, stmt1); sql = "create " + unique + clustered + " index " + indexName + " on " + newTable + " (" + columnNames + ") "; stmt1.executeUpdate(sql); //给新表创建索引 } } /** * 查询主键或索引涉及的列的名称 * @param indid 主键或索引在系统表 sysindexes 中的 indid 字段的值 * @param keycnt 主键或索引涉及的列的数量 * @param tableName 索引所在的表 * @param st 执行数据库查询的 java.sql.Statement 对象 * @return 主键或索引涉及的列的名称(如果有多个列，则各列的名称用逗号(,)连接), 如：field_0 或 field_1,field_2,field_3 * @throws SQLException * @author Calr He */ private static String getFieldNamesOfRelatedIndexByIndid(int indid, int keycnt, String tableName, Statement st) throws SQLException{ StringBuffer buff = new StringBuffer(); buff.append("select "); for (int i = 1; i <= keycnt; i++) { if(i>1) buff.append(", "); buff.append("index_col('").append(tableName).append("',").append(indid).append(",").append(i).append(")"); } buff.append(" from sysindexes where indid=").append(indid).append(" and id=object_id('").append(tableName).append("')"); ResultSet rs = st.executeQuery(buff.toString()); //查询主键涉及的列的列名 buff = new StringBuffer(); if(rs.next()){ String columnName = null; for (int i = 1; i <= keycnt; i++) { columnName = StringUtils.trimToNull(rs.getString(i)); if(columnName == null) break; buff.append(columnName).append(","); } } rs.close(); int length = buff.length(); return length == 0 ? buff.toString() : buff.substring(0, length-1); } public static void main(String[] args) { try { Class.forName("com.sybase.jdbc2.jdbc.SybDriver"); Connection con=DriverManager.getConnection("jdbc:sybase:Tds:192.168.0.201:5000/jcbase?charset=cp850&CHARSET_CONVERTER_CLASS=com.sybase.jdbc2.utils.Cp850PureConverter","sa","123456"); Statement st=con.createStatement(); /* // 根据视图名称查询创建视图的 SQL 语句 ResultSet rs = st.executeQuery("select text from sysobjects t,syscomments m where type='V' and m.id=t.id and t.name='V_VIEW_NAME'"); while(rs.next()) System.out.println(rs.getString(1)); */ createOldTableFromTable("T_TABLE_NAME", "T_TABLE_NAME_2010", st, con.createStatement(), "WHERE 1=2"); } catch (Exception e) { e.printStackTrace(); } } }