|
D:\work\db\src\Example3.java
|
1
2 import java.sql.*;
3
4 /**
5 * This example is based on Example2
6 */
7 public class Example3 {
8 public static void main(String args[]) {
9 Example3 example = new Example3();
10 example.run();
11 }
12
13 private void run() {
14 Connection con = null;
15 ResultSet result = null;
16 try {
17 con = openConnection();
18 publishData(con);
19 result = searchAllTuples(con);
20 showMetaDataOfResultSet(result);
21 showResultSet(result);
22 } catch (SQLException e) {
23 System.err.println("Errors occurs when communicating with the database server: " + e.getMessage());
24 } catch (ClassNotFoundException e) {
25 System.err.println("Cannot find the database driver");
26 } finally {
27 // Never forget to close database connection
28 closeConnection(con);
29 }
30 }
31
32 /**
33 * This method will first clean up the table and then
34 * populate it with new data.
35 * @param con
36 * @throws SQLException
37 */
38 private void publishData(Connection con) throws SQLException {
39 Statement stmt = con.createStatement();
40
41 System.out.println("Deleting previous tuples ...");
42 stmt.executeUpdate("delete from info");
43
44 System.out.println("Inserting Data ...");
45 stmt.executeUpdate("insert into info values ('Clinton, Bill', '9876543210', 400000, 'Ex. White House')");
46 stmt.executeUpdate("insert into info values ('Doll, Bob', '1234567890', 100000, 'House')");
47 stmt.executeUpdate("insert into info values ('Bush, George', '6543210987', 450000, 'White House')");
48 stmt.executeUpdate("insert into info values ('Gore, Al', '3210987654', 200000, 'donno')");
49 stmt.executeUpdate("insert into info values ('Bing, Chandler', '4321056789', 750000, 'Friends')");
50 stmt.close();
51 }
52
53 private ResultSet searchAllTuples(Connection con) throws SQLException {
54 Statement stmt = con.createStatement();
55 return stmt.executeQuery("SELECT * FROM info");
56 }
57
58 private void showMetaDataOfResultSet(ResultSet result) throws SQLException {
59 ResultSetMetaData meta = result.getMetaData();
60 for (int col = 1; col <= meta.getColumnCount(); col++) {
61 System.out.println("Column: " + meta.getColumnName(col) +
62 ",\t Type: " + meta.getColumnTypeName(col));
63 }
64 }
65
66 private void showResultSet(ResultSet result) throws SQLException {
67 ResultSetMetaData meta = result.getMetaData();
68 int tupleCount = 1;
69 while (result.next()) {
70 System.out.print("Tuple " + tupleCount++ + ": ");
71 for (int col = 1; col <= meta.getColumnCount(); col++) {
72 System.out.print("\"" + result.getString(col) + "\",\t");
73 }
74 System.out.println();
75 }
76 }
77
78 /**
79 *
80 * @return a database connection
81 * @throws java.sql.SQLException when there is an error when trying to connect database
82 * @throws ClassNotFoundException when the database driver is not found.
83 */
84 private Connection openConnection() throws SQLException, ClassNotFoundException {
85 // Load the Oracle database driver
86 DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
87
88 /*
89 Here is the information needed when connecting to a database
90 server. These values are now hard-coded in the program. In
91 general, they should be stored in some configuration file and
92 read at run time.
93 */
94 String host = "shams.usc.edu";
95 String port = "1521";
96 String dbName = "cs585";
97 String userName = "temp";
98 String password = "temp585";
99
100 // Construct the JDBC URL
101 String dbURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName;
102 return DriverManager.getConnection(dbURL, userName, password);
103 }
104
105 /**
106 * Close the database connection
107 * @param con
108 */
109 private void closeConnection(Connection con) {
110 try {
111 con.close();
112 } catch (SQLException e) {
113 System.err.println("Cannot close connection: " + e.getMessage());
114 }
115 }
116 }