D:\work\db\src\Example2.java
|
1
2 import java.sql.*;
3
4 /**
5 * This example is based on Example1.
6 */
7 public class Example2 {
8 public static void main(String args[]) {
9 Example2 example = new Example2();
10 example.run();
11 }
12
13 /**
14 * In this method, we divide the whole query process into three functions:
15 * searchAllTuples(), showMetaDataOfResultSet(), showResultSet() for better
16 * understanding the logic of the code. (You can also put all the code
17 * inside one method, of course.)
18 */
19 private void run() {
20 Connection con = null;
21 ResultSet result = null;
22 try {
23 con = openConnection();
24
25 result = searchAllTuples(con);
26
27 showMetaDataOfResultSet(result);
28
29 showResultSet(result);
30 } catch (SQLException e) {
31 System.err.println("Errors occurs when communicating with the database server: " + e.getMessage());
32 } catch (ClassNotFoundException e) {
33 System.err.println("Cannot find the database driver");
34 } finally {
35 // Never forget to close database connection
36 closeConnection(con);
37 }
38 }
39
40 private ResultSet searchAllTuples(Connection con) throws SQLException {
41 Statement stmt = con.createStatement();
42 return stmt.executeQuery("SELECT * FROM info");
43 }
44
45 private void showMetaDataOfResultSet(ResultSet result) throws SQLException {
46 ResultSetMetaData meta = result.getMetaData();
47 for (int col = 1; col <= meta.getColumnCount(); col++) {
48 System.out.println("Column: " + meta.getColumnName(col) +
49 "\t, Type: " + meta.getColumnTypeName(col));
50 }
51 }
52
53 private void showResultSet(ResultSet result) throws SQLException {
54 ResultSetMetaData meta = result.getMetaData();
55 int tupleCount = 1;
56 while (result.next()) {
57 System.out.print("Tuple " + tupleCount++ + " : ");
58 for (int col = 1; col <= meta.getColumnCount(); col++) {
59 System.out.print("\"" + result.getString(col) + "\",");
60 }
61 System.out.println();
62 }
63 }
64
65 /**
66 *
67 * @return a database connection
68 * @throws SQLException when there is an error when trying to connect database
69 * @throws ClassNotFoundException when the database driver is not found.
70 */
71 private Connection openConnection() throws SQLException, ClassNotFoundException {
72 // Load the Oracle database driver
73 DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
74
75 /*
76 Here is the information needed when connecting to a database
77 server. These values are now hard-coded in the program. In
78 general, they should be stored in some configuration file and
79 read at run time.
80 */
81 String host = "shams.usc.edu";
82 String port = "1521";
83 String dbName = "cs585";
84 String userName = "temp";
85 String password = "temp585";
86
87 // Construct the JDBC URL
88 String dbURL = "jdbc:oracle:thin:@"" + host + ":" + port + ":" + dbName;
89 return DriverManager.getConnection(dbURL, userName, password);
90 }
91
92 /**
93 * Close the database connection
94 * @param con
95 */
96 private void closeConnection(Connection con) {
97 try {
98 con.close();
99 } catch (SQLException e) {
100 System.err.println("Cannot close connection: " + e.getMessage());
101 }
102 }
103 }