ViewVC Help
View File | Revision Log | Show Annotations | Revision Graph | Root Listing
root/i-scream/experimental/server/DBInterface/DBLastInsertTest.java
Revision: 1.2
Committed: Tue Dec 5 12:12:03 2000 UTC (23 years, 3 months ago) by pjm2
Branch: MAIN
CVS Tags: PROJECT_COMPLETION, HEAD
Changes since 1.1: +20 -12 lines
Log Message:
Changed to reflect the structure of our new DB design.  Also uses a lock
on the tables to ensure that many inserts are run quickly.  An ACID state
is ensured throughout.

File Contents

# Content
1 import java.sql.*;
2
3 /**
4 * A main method to test how to insert MULTIPLE rows to a mySQL
5 * database table using the last_insert_id() SQL function.
6 * This is required so that the id references may be inserted to
7 * multiple tables using an autoincrement field. Tables are
8 * locked to increase performance when several inserts are made
9 * with the same connection.
10 *
11 * CONCLUSION: This works on raptor. It is probably the method
12 * we shall use in the final database logging system.
13 *
14 * IMPORTANT: This method obtains the database user and database
15 * connection password from the Password class. As such, this
16 * class may be safely placed in a public CVS repository.
17 *
18 * @author $Author: pjm2 $
19 * @version $Id: DBLastInsertTest.java,v 1.1 2000/12/05 08:25:33 pjm2 Exp $
20 */
21 class DBLastInsertTest {
22
23 public static void main(String[] args) throws InterruptedException{
24
25 // Create an instance of the mySQL driver.
26 try{
27 Class.forName("org.gjt.mm.mysql.Driver").newInstance();
28 }
29 catch(Exception e){
30 e.printStackTrace();
31 }
32
33 try {
34 // Use the connection string for our group database.
35 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/co600_10_db?user="+Password.mySQLUser+"&password="+Password.mySQLPassword);
36
37 // Create a statement object.
38 Statement stmt = conn.createStatement();
39
40 // Lock all two test tables. (Waits until it can)
41 // No other process will be able to use these tables
42 // until they are unlocked.
43 stmt.executeUpdate("LOCK TABLES packet_test WRITE, parameter_test WRITE;");
44
45 // Execute an insert statement.
46 stmt.executeUpdate("INSERT INTO packet_test (ip, machine_name, seq_no, sent_date, receipt_date) VALUES ('0.0.0.0', 'raptor', 1234, 12343, 12343);");
47
48 // Get the last used insertion id for THIS connection ;-)
49 ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID();");
50 rs.next();
51 String packet_id = rs.getString("LAST_INSERT_ID()");
52
53 // insert some entries into the parameter_test table using
54 // the id number from the packet_test insertion.
55 stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
56 stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
57 stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
58 stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
59
60 // Remove the locks.
61 stmt.executeUpdate("UNLOCK TABLES;");
62
63 // Close the statement and database connection.
64 stmt.close();
65 conn.close();
66 }
67 catch (SQLException e) {
68 System.out.println("SQLException: " + e.getMessage());
69 System.out.println("SQLState: " + e.getSQLState());
70 System.out.println("VendorError: " + e.getErrorCode());
71 }
72 }
73 }