server/DBInterface/DBLastInsertTest.java

import java.sql.*;

/**
 * A main method to test how to insert MULTIPLE rows to a mySQL
 * database table using the last_insert_id() SQL function.
 * This is required so that the id references may be inserted to
 * multiple tables using an autoincrement field.  Tables are
 * locked to increase performance when several inserts are made
 * with the same connection.
 *
 * CONCLUSION: This works on raptor.  It is probably the method
 * we shall use in the final database logging system.
 *
 * IMPORTANT: This method obtains the database user and database
 * connection password from the Password class.  As such, this
 * class may be safely placed in a public CVS repository.
 *
 * @author  $Author: pjm2 $
 * @version $Id: DBLastInsertTest.java,v 1.1 2000/12/05 08:25:33 pjm2 Exp $
 */
class DBLastInsertTest {

    public static void main(String[] args) throws InterruptedException{

        // Create an instance of the mySQL driver.
        try{
            Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
        }
        catch(Exception e){
            e.printStackTrace();
        }

        try {
            // Use the connection string for our group database.
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/co600_10_db?user="+Password.mySQLUser+"&password="+Password.mySQLPassword);

            // Create a statement object.
            Statement stmt = conn.createStatement();
            
            // Lock all two test tables.  (Waits until it can)
            // No other process will be able to use these tables
            // until they are unlocked.
            stmt.executeUpdate("LOCK TABLES packet_test WRITE, parameter_test WRITE;");
            
            // Execute an insert statement.
            stmt.executeUpdate("INSERT INTO packet_test (ip, machine_name, seq_no, sent_date, receipt_date) VALUES ('0.0.0.0', 'raptor', 1234, 12343, 12343);");

            // Get the last used insertion id for THIS connection ;-)
            ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID();");
            rs.next();
            String packet_id = rs.getString("LAST_INSERT_ID()");

            // insert some entries into the parameter_test table using
            // the id number from the packet_test insertion.
            stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
            stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
            stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
            stmt.executeUpdate("INSERT INTO parameter_test (packet_id, name, value) VALUES ("+packet_id+", 'packet.attributes.machine_name', 'raptor!');");
            
            // Remove the locks.
            stmt.executeUpdate("UNLOCK TABLES;");
            
            // Close the statement and database connection.
            stmt.close();
            conn.close();
        }
        catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
            System.out.println("VendorError:  " + e.getErrorCode());
        }
    }
}   
Revision:	1.2
Committed:	Tue Dec 5 12:12:03 2000 UTC (24 years, 11 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.
#	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	}