E77C4PZq

1import java.sql.*;
2
3class jdbcMysql {
4	public static void main(String[] Args) {
5
6		String url = "sql2.njit.edu";
7		String ucid = "";	//your ucid
8		String dbpassword = "";	//your MySQL password
9
10
11		System.out.println("This example program will create a table in MySQL and "+
12			"populate that table with three rows of sample data. The program " +
13			"will then query the database for the contents of the table and " +
14			"display the result.");
15
16		System.out.println("Starting test . . .");
17		
18		
19		System.out.println("Loading driver . . .");
20		try {
21			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
22		}
23		catch (Exception e) {
24			System.err.println("Unable to load driver.");
25			e.printStackTrace();
26		}
27		System.out.println("Driver loaded.");
28		System.out.println("Establishing connection . . . ");
29		try {
30			Connection conn;
31	 
32			conn = DriverManager.getConnection("jdbc:mysql://"+url+"/"+ucid+"?user="+ucid+"&password="+dbpassword);
33			
34			System.out.println("Connection established.");			
35			System.out.println("Creating a Statement object . . . ");
36
37			Statement stmt = conn.createStatement();
38			System.out.println("Statement object created.");
39			stmt.executeUpdate("DROP TABLE IF EXISTS user");
40			System.out.println("Old table dropped (if it existed).");
41
42			System.out.println("Creating a table . . .");
43			stmt.executeUpdate("CREATE TABLE user("+
44				"idnum INT NOT NULL AUTO_INCREMENT, "+
45				"PRIMARY KEY(idnum), "+
46				"userid VARCHAR(100) UNIQUE NOT NULL, "+
47				"fullname VARCHAR(100) NOT NULL, "+
48				"email VARCHAR(100) NOT NULL, "+
49				"notes TEXT)");
50			System.out.println("Table created.");
51
52			System.out.println("Inserting data in table . . .");
53
54			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
55				"VALUES(\"john\", \"john smith\", \"john@njit.edu\", \"blah, blah . . \")");
56			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
57				"VALUES(\"jane\", \"jane doe\", \"jane@njit.edu\", \"blah, blah . . \")");
58			stmt.executeUpdate("INSERT INTO user (userid, fullname, email, notes) "+
59				"VALUES(\"zip\", \"zip zippy\", \"zippy@njit.edu\", \"blah, blah . . \")");
60
61			System.out.println("Inserted data.");
62
63			System.out.println("Querying table  . . . ");
64			
65			ResultSet rs = stmt.executeQuery("SELECT * from user");
66			while (rs.next()) {
67				System.out.print(rs.getString("idnum")+"\t"+rs.getString("userid")+"\t"+rs.getString("fullname")+"\t"+
68					rs.getString("email")+"\t"+rs.getString("notes"));
69				System.out.println();			}
70
71			rs.close();
72			stmt.close();
73			conn.close();
74			System.out.println("If you see three rows of data above, the test example sucessfully completed.");
75		}
76		catch (SQLException E) {
77			System.out.println("SQLException: " + E.getMessage());
78			System.out.println("SQLState:     " + E.getSQLState());
79			System.out.println("VendorError:  " + E.getErrorCode());
80		}
81	}	
82}