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:  $
 * @version $Id:  $
 */
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 three test tables.  (Waits until it can)
            stmt.executeUpdate("LOCK TABLES packet WRITE, cpu WRITE, disk WRITE;");
            
            // Execute an insert statement.
            stmt.executeUpdate("INSERT INTO packet (ip, seq_no, date, machine_name) VALUES ('0.0.0.0', 4543, 1423123, 'raptor');");

            // Wait 30 seconds.  No other process should be able to use the
            // tables in this time period.
            Thread.sleep(30000);

            // insert another one with the same id as above, but in the seq_no field.
            stmt.executeUpdate("INSERT INTO packet (ip, seq_no, date, machine_name) VALUES ('0.0.0.0', LAST_INSERT_ID(), 1423123, 'raptor');");            
            
            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.1
Committed:	Tue Dec 5 08:25:33 2000 UTC (23 years, 4 months ago) by pjm2
Branch:	MAIN
Log Message:	A test program to see how to insert to a table which has been write locked. This works as intended and will thus probably be the mechanism used in our final database logging interface.
#	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: $
19	* @version $Id: $
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 three test tables. (Waits until it can)
41	stmt.executeUpdate("LOCK TABLES packet WRITE, cpu WRITE, disk WRITE;");
42
43	// Execute an insert statement.
44	stmt.executeUpdate("INSERT INTO packet (ip, seq_no, date, machine_name) VALUES ('0.0.0.0', 4543, 1423123, 'raptor');");
45
46	// Wait 30 seconds. No other process should be able to use the
47	// tables in this time period.
48	Thread.sleep(30000);
49
50	// insert another one with the same id as above, but in the seq_no field.
51	stmt.executeUpdate("INSERT INTO packet (ip, seq_no, date, machine_name) VALUES ('0.0.0.0', LAST_INSERT_ID(), 1423123, 'raptor');");
52
53	stmt.executeUpdate("UNLOCK TABLES;");
54
55	// Close the statement and database connection.
56	stmt.close();
57	conn.close();
58	}
59	catch (SQLException e) {
60	System.out.println("SQLException: " + e.getMessage());
61	System.out.println("SQLState: " + e.getSQLState());
62	System.out.println("VendorError: " + e.getErrorCode());
63	}
64	}
65	}