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 (23 years, 4 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.
#	User	Rev	Content
1	pjm2	1.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	pjm2	1.2	* we shall use in the final database logging system.
13	pjm2	1.1	*
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	pjm2	1.2	* @author $Author: pjm2 $
19			* @version $Id: DBLastInsertTest.java,v 1.1 2000/12/05 08:25:33 pjm2 Exp $
20	pjm2	1.1	*/
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	pjm2	1.2	// 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	pjm2	1.1
45			// Execute an insert statement.
46	pjm2	1.2	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	pjm2	1.1
48	pjm2	1.2	// 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	pjm2	1.1
60	pjm2	1.2	// Remove the locks.
61	pjm2	1.1	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			}