D:\work\db\src\Example4.java
|
1
2 import java.sql.*;
3
4 /**
5 *
6 */
7 public class Example4 {
8 public static void main(String args[]) {
9 Example4 example = new Example4();
10 example.run();
11 }
12
13 private void run() {
14 Connection con = null;
15 try {
16 con = openConnection();
17
18 publishData(con);
19 showTableContent(con);
20
21 publishDataUsingPreparedStatement(con);
22 showTableContent(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 /**
34 * Using prepared statement that is faster than regular statement.
35 * @param con
36 * @throws SQLException
37 */
38 private void publishDataUsingPreparedStatement(Connection con) throws SQLException {
39 PreparedStatement stmt = con.prepareStatement("INSERT INTO info VALUES(?,?,?,?)");
40 stmt.setString(1, "Name 1");
41 stmt.setString(2, "00000000");
42 stmt.setString(3, "250000");
43 stmt.setString(4, "My Department");
44 stmt.executeUpdate();
45 stmt.setString(1, "Name 2");
46 stmt.setString(2, "111111");
47 stmt.setString(3, "887700");
48 stmt.setString(4, "My Department 2");
49 stmt.executeUpdate();
50 stmt.close();
51 }
52
53 /**
54 * This method will first clean up the table and then
55 * populate it with new data.
56 * @param con
57 * @throws java.sql.SQLException
58 */
59 private void publishData(Connection con) throws SQLException {
60 Statement stmt = con.createStatement();
61
62 System.out.println("Deleting previous tuples ...");
63 stmt.executeUpdate("DELETE FROM info");
64
65 System.out.println("Inserting Data ...");
66 stmt.executeUpdate("insert into info values ('Clinton, Bill', '9876543210', 400000, 'Ex. White House')");
67 stmt.executeUpdate("insert into info values ('Doll, Bob', '1234567890', 100000, 'House')");
68 stmt.executeUpdate("insert into info values ('Bush, George', '6543210987', 450000, 'White House')");
69 stmt.executeUpdate("insert into info values ('Gore, Al', '3210987654', 200000, 'donno')");
70 stmt.executeUpdate("insert into info values ('Bing, Chandler', '4321056789', 750000, 'Friends')");
71 stmt.close();
72 }
73
74 private void showTableContent(Connection con) throws SQLException {
75 Statement stmt = con.createStatement();
76 ResultSet result = stmt.executeQuery("SELECT * FROM info");
77
78 /*
79 We use ResultSetMetaData.getColumnCount() to know the number columns
80 that are contained.
81 */
82 ResultSetMetaData meta = result.getMetaData();
83 for (int col = 1; col <= meta.getColumnCount(); col++) {
84 System.out.println("Column" + col + ": " + meta.getColumnName(col) +
85 "\t, Type: " + meta.getColumnTypeName(col));
86 }
87
88 /*
89 Every time we call ResultSet.next(), its internal cursor will advance
90 one tuple. By calling this method continuously, we can iterate through
91 the whole ResultSet.
92 */
93 while (result.next()) {
94 for (int col = 1; col <= meta.getColumnCount(); col++) {
95 System.out.print("\"" + result.getString(col) + "\",");
96 }
97 System.out.println();
98 }
99
100 /*
101 It is always a good practice to close a statement as soon as we
102 no longer use it.
103 */
104 stmt.close();
105 }
106
107 /**
108 *
109 * @return a database connection
110 * @throws java.sql.SQLException when there is an error when trying to connect database
111 * @throws ClassNotFoundException when the database driver is not found.
112 */
113 private Connection openConnection() throws SQLException, ClassNotFoundException {
114 // Load the Oracle database driver
115 DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
116
117 /*
118 Here is the information needed when connecting to a database
119 server. These values are now hard-coded in the program. In
120 general, they should be stored in some configuration file and
121 read at run time.
122 */
123 String host = "shams.usc.edu";
124 String port = "1521";
125 String dbName = "cs585";
126 String userName = "temp";
127 String password = "temp585";
128
129 // Construct the JDBC URL
130 String dbURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName;
131 return DriverManager.getConnection(dbURL, userName, password);
132 }
133
134 /**
135 * Close the database connection
136 * @param con
137 */
138 private void closeConnection(Connection con) {
139 try {
140 con.close();
141 } catch (SQLException e) {
142 System.err.println("Cannot close connection: " + e.getMessage());
143 }
144 }
145 }