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