When you start learning XML and database programming, everyone will start with a lot of documents and materials. The author is working in the work, and it is necessary to use it. Its feature is very simple, you have to use the Java language, import some data into the SQL database from the Access database.
demand:
Original Access database table structure:
Table: Production
Product model string type ......... Product number
Part map number string ......... Part number
Timber string .......................................
SQL data table structure:
Project product table
ID INT logo
Number ... varchar64 number
Product Part Table
ID INT logo
PID INT product identification number (associated with ID in the Project table)
Number ... varchar64 number
Production tool table corresponding to ComponentType and Parts
ID INT logo
AID INT Part Identification (association with the IDUCT table)
Number ... varchar64 number
The original numbers are required to be placed in the SQL three tables, and associated with the ID.
Considering that database connections may change, decide to use XML to configure data connections. The DBS.XML file content is as follows:
File Connpara.java, indicating database connection parameters represented by a class form.
Public Class Connpara
{
String dbclass = NULL;
String Url = NULL;
String username = null;
String password = NULL;
Public connpara () {}
Public Connpara (String Pdbclass, String Purl, String Puser, 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;}
}
Document DBXMLPARSER.JAVA encapsulates the operation of the XML file.
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 loadingdocument ()
{
Try
{
//factory
DocumentBuilderFactory dcfactory = documentbuilderfactory.newinstance ();
// Document constructor
DocumentBuilder DB = dcfactory.NewDocumentBuilder ();
// Context document
Document doc = db.parse (XMLFile);
/ Root elements
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 = loadingDocument ();
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 = loadingDocument ();
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;
}
// Get the value of the child element
Private string getChildElementValue (Node Node, String Subtagname)
{
String Returnstring = ""
IF (Node! = NULL)
{
Nodelist children = node.getchildnodes ();
For (int innerloop = 0; 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; } } Document dbinput.java is a real data copy section: 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.sqlser.sqlserverdriver); SRC = DriverManager.getConnection (srcpara.getUsername (), srcpara.getpassword ()); Dest = drivermanager.getConnection (destpara.getUrname (), destpara.getpassword ()); CONNFORMAX = drivermanager.getConnection (destpara.getUsername (), destpara.getpassword ()); } // Capture the loading driver exception Catch (classnotfoundexception cnfex) { System.err.println ("Load JDBC / ODBC driver failed."); CNFEX.PrintStackTrace (); System.exit (1); // Terminate Program } Catch (SQLException EX) { System.err.println ("Connection Fail"); EX.PrintStackTrace (); System.exit (1); // Terminate Program } } Public void copyproduct () { Statement ST = NULL; ResultSet Rset = NULL; String sqlstr; Try { / / Execute SQL statement String query = "select * from produuction"; 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); // Insert the product table PID = GetFromNumber ("Project", "Number", PNumber; IF (PID == 0) // Insert a new record { PID = GetMax ("Project"); // system.out.println (PID); SQLSTR = "Insert Into Project (ID, Number) VALUES (" PID ", '" PNumber ")" Execute (Destpara, SQLSTR); } // Insert into the part table Ljid = getIDFromNumber ("Product", "Number", LJNumber; IF (ljid == 0) // Insert a new record { Ljid = getmax ("product"); SQLSTR = "INSERT INTO PRODUCT (ID, Number) VALUES (" Ljid "," PID ", '" Ljnumber "" "; Execute (Destpara, SQLSTR); } // Insert the tool table CID = GetFromNumber ("ComponentType", "Number", cnumber; IF (CID == 0) // Insert a new record { 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 { / / Execute SQL statement 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.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; } / ** * Get the largest ID number in a table * / protected int getmax (string tname) { Int result; ResultSet RST = NULL; Try { / / Execute SQL statement 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; } / ** * Execute a certain SQL statement * / Public Static Void Execute (Connpara Connpara, String Stmt) THROWS SQLEXCEPTION { Connection conn = NULL; PreparedStatement PS = NULL; Try { Conn = drivermanager.getConnection (connpara.getUrname (), 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 (); } } problem: 1) Access database cannot be read directly from JDBC, the solution is to configure Access in the ODBC and then operate the Access database through ODBC. 2) Can't find the com.microsoft.jdbc.sqlser.sqlserverdriver class when executed, because when running this class, you want to download Microsoft's JDBC package, three files in this package: msbase.jar, mssqlser.jar, Msutil .jar, put these three files into in, there will be no problem.
