SAX解析导入数据库,郁闷了两周~代码分享中
需求是这样的,从一个包含一个或多个表记录的XML中读取数据,插入到其对应的数据库表中,如果数据库有这条记录,则更新,没有的话做插入,执行下来效率很低,首先2000条速度还可以,但是一到后面就会很慢慢,过了一阵子又恢复一下,我怀疑是代码内存没有释放好,请大家帮忙看看~~!
public void startDocument() throws SAXException {
str = new StringBuffer();
conn = connect.getConnection();
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void processingInstruction(String taget, String data) throws SAXException {
}
public void startElement(String uri, String localName, String qName, Attributes attr) throws SAXException {
if( "TABLE ".equals(qName))//如果是TABLE这个标记开头的,证明是一条记录的开始
{
for(int i = 0;i <attr.getLength();i++)
{
tableName = attr.getValue(i);//将表名读出来
}
if(adm!=null)
{
MARKISNEW=adm.getMARKISNEW();
DELETEON = adm.getDELETEON();
TMPDATA = adm.getTMPDATA();
}
sql.append(tableName + " values( ");//将前半段SQL语句组织好
if(!table.equals(tableName))//table用来保存表名,如果table这个变量的值与表名不一致,证明是另一张表需要重新查一次主键列
{
sql1.append( "select * from syscolumns where id=object_id( ' "+ tableName + " ') ");
rs1 = connect.executeQuery(sql1.toString());
try {
rs1.last();
int row = rs1.getRow();
column = new String[row];//new一个行数大小的数组
rs1.beforeFirst();//把游标置到最顶端,不然用while循环将取不到任何数据,因为前面已经把游标放到最后了
while(rs1.next())//做循环
{
for(int i=0;i <row;i++)
{
column[i] = rs1.getString( "name ");//把每一列的列名存到数组中
tableid = rs1.getString( "id ");
rs1.next();
}
}
sql2.append( "SELECT dbo.sysobjects.name, dbo.sysobjects.id, dbo.sysindexkeys.colid "
+ " FROM dbo.sysindexes INNER JOIN "
+ " dbo.sysobjects ON dbo.sysindexes.name = dbo.sysobjects.name INNER JOIN "
+ " dbo.sysindexkeys ON dbo.sysindexes.id = dbo.sysindexkeys.id AND "
+ " dbo.sysindexes.indid = dbo.sysindexkeys.indid "
+ " WHERE (dbo.sysobjects.xtype = 'PK ') and dbo.sysindexes.id= ' " + tableid+ " ' ") ;
rs2 = connect.executeQuery(sql2.toString());
rs2.last();
int row2 = rs2.getRow();
pk = new String[row2];//得到包含着主键列名的数组,表有几个主键,这个数组中就有几个值
table = tableName;//将表名保存起来,以便下一次可以进行判断
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
if(rs1!=null)
{
try {
rs1.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(rs2!=null)
{
try {
rs2.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
else//如果不是TABLE字符串开始的,就不需要取表名了,直接把值赋上去就行
{
columnName = qName;
}
}
public void characters(char[] ch, int start, int length) throws SAXException {
str.append(new String(ch,start,length));
}
public void endElement(String uri, String localName, String qName) throws SAXException {
columnValue = str.toString().trim();
columnValue = toNull(columnValue);
for(int i=0;i <pk.length;i++)
{
pk[i] = column[i];
if(pk[i].equals(columnName))
{
if(i==0)
{
sql3.append(pk[i] + "= "+columnValue) ;
pkString.append( columnName + "= " + columnValue);
}
else
{
sql3.append( "and " + pk[i] + "= "+columnValue) ;
pkString .append( " and "+ columnName + "= " + columnValue) ;
}
try {
cs = conn.prepareCall( "{ call UP_IsExistByStat(?,?)} ");
cs.setString(1, tableName);
cs.setString(2,sql3.toString());
rs3 = cs.executeQuery();
while(rs3.next())
rowcnt = rs3.getInt( "rowcnt ");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
finally
{
if(rs3!=null)
{
try {
rs3.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
if(rowcnt> 0)//这一段代码在每一条数据中要保证只操作一次
{
sql.setLength(0);
sql.append( "update " + tableName + " set ") ;
}
}
}
if(! "null ".equals(columnValue)&&! "NULL ".equals(columnValue)&&columnValue!=null&&! " ".equals(columnValue))
{
columnValue = " ' "+ columnValue+ " ' ";
}
if( "null ".equals(columnValue)|| "NULL ".equals(columnValue))
{
columnValue = null;
}
if( " ".equals(columnValue))
{
columnValue = " ' ' ";
}
//if(columnName!=null&&columnName.indexOf( "DATE ")> 0)
//System.out.println( "column: "+columnValue);
if(! "DATA ".equals(qName)&&! "TABLE ".equals(qName))
{
if( "1 ".equals(MARKISNEW)&& "ISNEW ".equals(columnName))
{
columnValue = "0 ";
}
if(! "LASTUPDATE ".equals(columnName))
{
if(rowcnt> 0)
{
sql.append( columnName + "= "+columnValue+ ", ") ;
}
else
{
sql.append(columnValue + ", ") ;
}
}
if( "LASTUPDATE ".equals(columnName))
{
if(rowcnt> 0)
{
sql.append(columnName + "= "+columnValue + pkString) ;
}
else
{
sql .append( columnValue + ") ") ;
}
}
}
if( "TABLE ".equals(qName))
//pk = null;
columnName = null;
columnValue = null;
str.setLength(0);
if( "TABLE ".equals(qName))
{
//sql += pkString;
System.out.println(sql);
insert(sql.toString());
sql.setLength(0);
sql.append( "insert into ");
sql1.setLength(0);
sql2 .setLength(0);
sql3 .setLength(0);
pkString.setLength(0);
pkString .append( " where ");
}
}
public void endDocument() throws SAXException {
try {
conn.commit();
if(conn!=null&&!conn.isClosed())
{
conn.close();
}
if(connect!=null)
{
connect.closeConnect();
}
System.out.println( "数据导入成功!! ");
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
}
[解决办法]
适合于动态XML，就是XML中元素的名称是未知的，或者只知道一个元素名称，比如表名，但是表中的字段是不确定的着这种情况吗？
