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, 11 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.
#	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			* 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			}