· 9 years ago · Aug 10, 2017, 04:14 PM
1
2/* Sources: IBM textbook, Horstmann
3 Modified by Peter Liu (Nov. 11, 2002; July 26 2007)
4
5 Purpose: Retrieve data from a MySQL database table
6 - database name: accounts
7 - table name: Account
8 */
9
10
11 /* Step 1: load the JDBC driver */
12 /* Step 2: connect to the database
13 datasbase URL:
14 jdbc:<subprotocol>:<system url>/<collection name> */
15/* Step 3: Prepare the SQL statement object
16 Step 4: Execute the SQL statement object */
17/* Step 5: Analyze(Navigate) the query result(the ResultSet) */
18/* Setp 6: Close the connection */
19
20
21import java.sql.*;
22
23public class TestJDBC_mysql {
24
25 static final String drivername
26 = "com.mysql.jdbc.Driver"; // JDBC driver (a class name)
27
28 static final String sysName = "zenit.senecac.on.ca"; // database server
29
30 static final String dbName = "jac444_111a07"; // database name
31
32 static final String userid = "jac444_111a07";
33 static final String password = "akYT9629";
34
35 private Connection conn; // database connnection
36
37
38 public static void main(String args[]) {
39
40 System.out.println("running a JDBC application...");
41
42 TestJDBC_mysql test = new TestJDBC_mysql(); /* load the JDBC driver */
43
44 if ( test.testConnect( sysName, dbName ) ) /* test the connection */
45
46 test.testQueryAll(); /* make SQL queries */
47
48 System.exit(0);
49 }
50
51
52 public TestJDBC_mysql() { // Step 1: load the JDBC driver
53
54 try {
55 Class.forName( drivername ); // load the Java class(i.e. JDBC driver) at run time
56 }
57 catch( ClassNotFoundException ec) { ec.printStackTrace();
58 System.out.println( "MySQL JDBC driver not found!" );
59 System.exit(1);
60 }
61
62
63 System.out.println("JDBC class found");
64 }
65
66
67 public boolean testConnect(String sys, String databaseName) {
68
69 System.out.println( "Connecting to the MySQL server: " + sys + "...");
70
71 try
72 {
73 /* Step 2: connect to the database
74 datasbase URL:
75 jdbc:<subprotocol>:<system url>/<collection name> */
76
77 conn = DriverManager.getConnection( "jdbc:mysql:" +
78 "//" + sys +
79 "/" + databaseName,
80 userid, password
81 );
82 }
83
84 catch ( SQLException exc ) /* SQLException */
85 {
86 System.out.println( "connection failed with: " +
87 exc.getMessage() );
88 return false;
89 }
90
91 System.out.println( "database connection-OK" );
92
93 return true;
94 }
95
96 public boolean testQueryAll() {
97
98 /* Step 3: Prepare the SQL statement object
99 Step 4: Execute the SQL statement object */
100
101 // drop the table if it already exists!
102
103 try {
104 // SQL statements: create a table and insert data
105
106 Statement stat = conn.createStatement();
107
108 // SQL statement: database query
109 ResultSet rs = stat.executeQuery( "SELECT * FROM LOCATIONS" );
110
111 // an SQLException is thrown if the table name
112 // is incorrect
113
114 /* Step 5: Analyze(Navigate) the query result(the ResultSet) */
115
116 System.out.println("\n******* The Result of the Query *******");
117
118 while ( rs.next() ) {
119
120 System.out.println( "Location: " + rs.getString(1) +
121
122 ", X Coord: " + rs.getDouble(2) +
123 ", Y Coord: " + rs.getDouble(3) );
124
125 }
126
127 rs.close(); // close the ResultSet object
128 stat.close(); // close the Statement object
129 }
130 catch ( SQLException exc )
131 {
132 System.out.println( " query failed with: " + exc.getMessage() );
133 return false;
134 }
135 finally { // always executed
136
137 System.out.println( "\nclose the database connection..." );
138
139 try { if (conn != null )
140 conn.close(); // close the database connection
141 }
142 catch (SQLException se ) { se.printStackTrace(); }
143 }
144
145 System.out.println( "DONE" );
146
147 return true;
148
149 } // end testQueryAll
150
151 public void getMetadata( ResultSet rs ) {
152
153 /* Retrieve the metadata of a table */
154 System.out.println( "meatdata info:" );
155
156 try {
157 ResultSetMetaData rsmeta = rs.getMetaData();
158
159 int columnCount = rsmeta.getColumnCount();
160
161 System.out.println( "number of columns: " + columnCount );
162
163 for (int i=1; i <= columnCount; i++)
164 System.out.println( "column name: " + rsmeta.getColumnName(i) +
165 "\n data type: " + rsmeta.getColumnTypeName(i) );
166 }
167 catch( SQLException e ) { e.printStackTrace(); }
168 }
169
170} // end TestJDBC5