The attentive reader will notice that I’ve skipped over extracting any data from the URL. That’s because I couldn’t bring myself to make it a section unto itself. Basically, you can use Strings in Java to find whatever you need. For example:
| String siteinfo = "This site was last modified on 13 August 2000."; String sub = "modified"; String sub2 = "Porsche"; System.out.println(siteinfo.indexOf(sub)); // returns a positive integer that marks the location // of the first character where the string was found System.out.println(siteinfo.indexOf(sub2)); // returns -1: substring not found in siteinfo |
From there you can extract out whatever data you see fit. Once you’ve decided what you want, then you’re ready for the JDBC stuff.
With Oracle, to initialize the JDBC drivers:
| Class.forName ("oracle.jdbc.driver.OracleDriver"); |
With mySQL:
| Class.forName("org.gjt.mm.mysql.Driver").newInstance(); |
Now to connect to the database. Once you have connected, you can execute arbitrary SQL commands, including the typical insert, update, create, and delete. Note that the Oracle connection string parameters can be found in the tnsnames file. For Oracle, this is how you connect:
| String user="xx", // the username which the JDBC driver should use to connect to Oracle password="xx", // the password associated with the previous username SID="xx", // this is the Oracle SID (System Identifier) of the database host="xx", // either the IP address of the server or its name port="1521", // the port number at which Oracle is expecting connections. // 1521 should be the default table="test"; Connection con=null; ResultSet rs = null; ResultSetMetaData rsmd=null; String query=null; try { // this one is for Oracle con = DriverManager.getConnection ("jdbc:oracle:thin:@" + host+":"+port+":"+SID,user, password); // Create Oracle DatabaseMetaData object DatabaseMetaData meta = con.getMetaData (); // gets driver info: System.out.println("JDBC driver version is " + meta.getDriverVersion()); } catch (SQLException e) { con = null; System.out.print("Couldn't connect to database on<b>: " + host + "\n"); System.out.print(e+"\n"); e.printStackTrace(); return; } |
For mySQL, the connection portion looks like this:
| String user="xx", // the username which the JDBC driver should use to connect password="xx", // the password associated with the previous username host="xx", // either the IP address of the server or its name Connection con=null; ResultSet rs = null; ResultSetMetaData rsmd=null; String query=null; try { // mm.mysql connection string is of the format // jdbc:mysql://host.domain.com/user String datasource="jdbc:mysql://”+ host + ”/" + user; con = DriverManager.getConnection(datasource+"?user="+ user+"&password="+password, user, password); } catch (SQLException e) { con = null; System.out.print("Couldn't connect to database on<b>: " + host + "\n"); System.out.print(e+"\n"); e.printStackTrace(); return; } |
Once you have the connection, SQL queries are done like this:
| try { stmt = con.createStatement(); query="insert into "+table+"(info) values(\""+value+"\")"; rs = stmt.executeQuery(query); // System.out.print("\n<br>Query: "+query); // echo the query to the command line for debugging, if desired // now turn around pull out the contents of the table and display it to the screen query="select * from "+table; rs = stmt.executeQuery(query); rsmd = rs.getMetaData (); // now you would use rsmd to check for the results of a select query int numCols = rsmd.getColumnCount (); while (rs.next()) { for (int i = 1; i <= numCols; i++) { System.out.println(rs.getString(i)); } } // while } // try catch (SQLException e) { con = null; System.out.print("SQL Exception<b>: " + host + "\n"); System.out.print(e+"\n"); return; } finally { try { if (rs!=null) rs.close(); stmt.close(); if (con!=null) con.close(); } catch (SQLException e) { System.out.print("SQL Exception during close<b>: "+e+"\n"); } } |
Easy, right? So easy, in fact, that you’ll find it’s probably more work to setup the database on the SQL server. At this point, you’ve got all the pieces, so the next section will be very brief.
