当前位置:天才代写 > tutorial > JAVA 教程 > Java+XML将数据导入SQL Server中

Java+XML将数据导入SQL Server中

2017-11-10 08:00 星期五 所属: JAVA 教程 浏览:471

副标题#e#

在开始进修XML和数据库编程时,各人都对一大堆的文档和资料,无从入手。作者在事情中,正好要用到了这些,利市头的一个措施举办整理,其成果很简朴,用Java语言从access数据库中,把一些数据导入到SQL数据库中。

原Access数据库表布局:

表:production

产物型号 字符串型 产物编号
零件图号 字符串型 零件编号
图号 字符串型 东西编号

SQL Server中数据表布局:

Project 产物表

Id int 标识
Number varchar64 编号

Product 零件表

Id int 标识
pid int 产物标识号(与project表中的id相关联)
Number varchar64 编号

Componenttype 与零件对应的出产东西表

Id int 标识
aid int 零件标识(与product表中的id关联)
Number varchar64 编号


#p#副标题#e#

要求把本来的编号别离放到下面的SQL Server数据库的三个表格中,并用id关联起来。

思量到数据库毗连大概有变革,抉择利用xml来设置数据毗连。Dbs.xml文件内容如下:

<?xml version="1.0" encoding="GBK"?>
<dbcopy>
 <source>
  <class>sun.jdbc.odbc.JdbcOdbcDriver</class>
  <url>jdbc:odbc:asrs</url>
  <user>""</user>
  <password>""</password>
 </source>
 <dest>
  <class>com.microsoft.jdbc.sqlserver.SQLServerDriver</class>
  <url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=vxms</url>
  <user>vxms</user>
  <password>vxms</password>
 </dest>
</dbcopy>

文件ConnPara.java,暗示以类形式暗示的数据库毗连参数。

public class ConnPara
{
 String dbClass=null;
 String url=null;
 String username=null;
 String password=null;
 public ConnPara() { }
 public ConnPara(String pdbClass,String purl,String pusername,String ppassword)
 {
  dbClass=pdbClass;
  url=purl;
  username=pusername;
  password=ppassword;
 }
 public String getDbClass(){return dbClass;}
 public String getUrl(){return url;}
 public String getUsername(){return username;}
 public String getPassword(){return password;}
 public void setDbClass(String str){ dbClass=str;}
 public void setUrl(String str){ url=str;}
 public void setUsername(String str){username=str;}
 public void setPassword(String str){password=str;}
}

#p#副标题#e#

文件DbXmlParser.java封装了对xml文件的操纵。

import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;
import java.io.*;
public class DbXmlParser
{
 static String xmlfile;
 public DbXmlParser(String filename)
 {
  xmlfile=filename;
 }
 public static Element loadDocument()
 {
  try
  {
   //工场
   DocumentBuilderFactory dcfactory=DocumentBuilderFactory.newInstance();
   //文档结构器
   DocumentBuilder db=dcfactory.newDocumentBuilder();
   //结构的文档
   Document doc=db.parse(xmlfile);
   //根元素
   Element root=doc.getDocumentElement();
   return root;
  }catch( ParserConfigurationException e){
   System.out.println("ParserConfigurationException");
   e.printStackTrace();
  }catch(IOException e) {
   System.out.println("IOException ");
   e.printStackTrace();
  }catch(SAXException e) {
   System.out.println("SAXException ");
   e.printStackTrace();
  }catch(Exception e) {
   e.printStackTrace();
  }
  return null;
 }
 public ConnPara getSource()
 {
  Element root=loadDocument();
  if( root==null) { return null; }
  NodeList nodes=root.getElementsByTagName("source");
  if(nodes.getLength()>0)
  {
   Node node=nodes.item(0);
   String connclass=getChildElementValue(node,"class");
   String url=getChildElementValue(node,"url");
   String username=getChildElementValue(node,"user");
   String password=getChildElementValue(node,"password");
   return new ConnPara(connclass,url,username,password);
  }
  return null;
 }
 public ConnPara getDest()
 {
  Element root=loadDocument();
  if( root==null) return null;
   NodeList nodes=root.getElementsByTagName("dest");
  if(nodes.getLength()>0)
  {
   Node node=nodes.item(0);
   String connclass=getChildElementValue(node,"class");
   String url=getChildElementValue(node,"url");
   String username=getChildElementValue(node,"user");
   String password=getChildElementValue(node,"password");
   return new ConnPara(connclass,url,username,password);
  }
  return null;
 }
 //获得子元素的值
 private String getChildElementValue(Node node,String subTagName)
 {
  String returnString = "";
  if(node != null)
  {
   NodeList children = node.getChildNodes();
   for(int innerLoop = 0; innerLoop < children.getLength(); innerLoop++)
   {
    Node child = children.item(innerLoop);
    if(child == null || child.getNodeName() == null || !child.getNodeName().equals(subTagName))
     continue;
    Node grandChild = child.getFirstChild();
    if(grandChild.getNodeValue() != null)
     return grandChild.getNodeValue();
   }
  }
  return returnString;
 }
}

#p#副标题#e#

文件DbInput.java则是真正的数据拷贝部门:

#p#分页标题#e#

import java.sql.*;
public class DbInput
{
 Connection src=null;
 Connection dest=null;
 Connection connformax=null;
 ConnPara srcPara;
 ConnPara destPara;
 public DbInput(){}
 public void dbInit()
 {
  DbXmlParser xmlparser=new DbXmlParser("dbs.xml");
  srcPara=xmlparser.getSource();
  destPara=xmlparser.getDest();
  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver" );
   Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
   src = DriverManager.getConnection( srcPara.getUrl(),rcPara.getUsername(),srcPara.getPassword());
   dest =DriverManager.getConnection( destPara.getUrl(), destPara.getUsername(),destPara.getPassword());
   connformax= DriverManager.getConnection( destPara.getUrl(), destPara.getUsername(),destPara.getPassword());
  }
  //捕捉加载驱动措施异常
  catch ( ClassNotFoundException cnfex ) {
   System.err.println( "装载 JDBC/ODBC 驱动措施失败。" );
   cnfex.printStackTrace();
   System.exit( 1 ); // terminate program
  } catch ( SQLException ex )
  {
   System.err.println( "毗连失败" );
   ex.printStackTrace();
   System.exit( 1 ); // terminate program
  }
 }
 public void copyproduct()
 {
  Statement st=null;
  ResultSet rset=null;
  String sqlstr;
  try {
   //执行SQL语句
   String query = "select * from production";
   st = src.createStatement();
   rset = st.executeQuery(query);
   int pid,ljid,cid,ciid;
   while(rset.next())
   {
    String pnumber=rset.getString(1);
    String ljnumber=rset.getString(2);
    String cnumber=rset.getString(3);
    //插入到产物表
    pid=getIdfromNumber("project","number",pnumber);
    if(pid==0) //插入一条新记录
    {
     pid=getMax("project");//System.out.println(pid);
     sqlstr="Insert into project(id,number) values("+pid+",'"+pnumber+"')";
     execute(destPara,sqlstr);
    }
    //插入到零件表
    ljid=getIdfromNumber("product","number",ljnumber);
    if(ljid==0) //插入一条新记录
    {
     ljid=getMax("product");
     sqlstr="Insert into product(id,pid,number) values("+ljid+","+pid+",'"+ljnumber+"')";
     execute(destPara,sqlstr);
    }
    //插入东西表
    cid=getIdfromNumber("componenttype","number",cnumber);
    if(cid==0) //插入一条新记录
    {
     cid=getMax("componenttype");
     sqlstr="Insert into componenttype(id,aid,number)values("+cid+","+ljid+",'"+cnumber+"')";
     execute(destPara,sqlstr);
    }
   }
  }
  catch ( SQLException sqlex ) {
   sqlex.printStackTrace();
  }
 }
 protected boolean alreadyin(String tname,String colname, String value)
 {
  int result;
  ResultSet rst=null;
  try {
   //执行SQL语句
   String query = "select "+colname+" from "+tname+" where "+colname+"='"+value+"'";
   Statement statement = connformax.createStatement();
   rst = statement.executeQuery( query );
   if(rst.next())
   {
    statement.close();
    rst.close();
    return true;
   }
  }
  catch ( SQLException sqlex ) {
   sqlex.printStackTrace();
   return false;
  }
  return false;
 }
 protected int getIdfromNumber(String tname,String colname, String value)
 {
  int result;
  ResultSet rst=null;
  try {
   Connection conn= DriverManager.getConnection( destPara.getUrl(), destPara.getUsername(),destPara.getPassword());
   String query = "select id,"+colname+" from "+tname+" where "+colname+"='"+value+"'";
   System.out.println(query);
   Statement statement = conn.createStatement();
   rst = statement.executeQuery( query );
   if(rst.next())
   {
    return rst.getInt("id");
   }
  }
  catch ( SQLException sqlex ) {
   sqlex.printStackTrace();
   return 0;
  }
  return 0;
 }
 /**
  * 获得某个表中的最大的id号
 */
 protected int getMax(String tname)
 {
  int result;
  ResultSet rst=null;
  try {
   //执行SQL语句
   String query = "select Max(id) from "+tname;
   Statement statement = connformax.createStatement();
   rst = statement.executeQuery( query );
   if(rst.next())
   {
    return rst.getInt(1)+1;
   }
  }
  catch ( SQLException sqlex ) {
   sqlex.printStackTrace();
   return 0;
  }
  return 1;
 }
 /**
  * 执行某一段sql语句
 */
 public static void execute(ConnPara connpara,String stmt) throws SQLException
 {
  Connection conn=null;
  PreparedStatement ps = null;
  try {
   conn=DriverManager.getConnection( connpara.getUrl(), connpara.getUsername(), connpara.getPassword());
   System.out.println(stmt);
   ps = conn.prepareStatement(stmt);
   ps.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
   System.out.println(e.getMessage());
  } finally {
   if (ps!=null) ps.close();
   if (conn!=null)conn.close();
  }
 }
 public static void main(String argc[])
 {
  DbInput copydb=new DbInput();
  copydb.dbInit();
  copydb.copyproduct();
 }
}

问题:

1) access数据库不能直接由jdbc读写,办理步伐是先把access设置在ODBC中,然后再通过JBDC-ODBC桥来操纵access数据库。

#p#分页标题#e#

2) 执行时找不到com.microsoft.jdbc.sqlserver.SQLServerDriver类,这是因为在运行这个类时,要下载微软的jdbc包,这个包中有三个文件: msbase.jar,mssqlserver.jar,msutil.jar,把这三个文件包括进去,就不会有问题了。

 

    关键字:

[contact-form-7 id="17097" title="form2"]天才代写-代写联系方式