副标题#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;}
}
文件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;
}
}
文件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,把这三个文件包括进去,就不会有问题了。
