· 9 years ago · May 20, 2017, 05:28 AM
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 = "com.mysql.jdbc.Driver"; // JDBC driver (a class name)
26
27 static final String sysName = "zenit.senecac.on.ca"; // database server
28
29 static final String dbName = "jac444_101a12"; // database name
30
31 static final String userid = "jac444_101a12";
32 static final String password = "19766113";
33
34 private Connection conn; // database connnection
35
36
37 public static void main(String args[]) {
38
39 System.out.println("running a JDBC application...");
40
41 TestJDBC_mysql test = new TestJDBC_mysql(); /* load the JDBC driver */
42
43 if ( test.testConnect( sysName, dbName ) ) /* test the connection */
44
45 test.testQueryAll(); /* make SQL queries */
46
47 System.exit(0);
48 }
49
50
51 public TestJDBC_mysql() { // Step 1: load the JDBC driver
52
53 try {
54 Class.forName( drivername ); // load the Java class(i.e. JDBC driver) at run time
55 }
56 catch( ClassNotFoundException ec) { ec.printStackTrace();
57 System.out.println( "MySQL JDBC driver not found!" );
58 System.exit(1);
59 }
60
61
62 System.out.println("JDBC class found");
63 }
64
65
66 public boolean testConnect(String sys, String databaseName) {
67
68 System.out.println( "Connecting to the MySQL server: " + sys + "...");
69
70 try
71 {
72 /* Step 2: connect to the database
73 datasbase URL:
74 jdbc:<subprotocol>:<system url>/<collection name> */
75
76 conn = DriverManager.getConnection( "jdbc:mysql:" +
77 "//" + sys +
78 "/" + databaseName,
79 userid, password
80 );
81 }
82
83 catch ( SQLException exc ) /* SQLException */
84 {
85 System.out.println( "connection failed with: " +
86 exc.getMessage() );
87 return false;
88 }
89
90 System.out.println( "database connection-OK" );
91
92 return true;
93 }
94
95 public boolean testQueryAll() {
96
97 /* Step 3: Prepare the SQL statement object
98 Step 4: Execute the SQL statement object */
99
100 // drop the table if it already exists!
101
102 System.out.println( "dropping a table if it exists..." );
103
104 try {
105 Statement dropTable = conn.createStatement();
106
107 dropTable.executeUpdate( "DROP TABLE " +
108 "Locations" ); // table name: Locations
109 //dropTable.close();
110
111 dropTable.executeUpdate( "DROP TABLE " +
112 "Test" ); // table name: Test
113 dropTable.close();
114 }
115 catch( SQLException e ) { }
116
117 System.out.println( "creating a database table..." );
118
119 try {
120 // SQL statements: create a table and insert data
121
122 Statement stat = conn.createStatement();
123
124 stat.executeUpdate( "CREATE table Locations (Address varchar(255), X Decimal(5,2), Y Decimal(5,2), Constraint pk_address_locations PRIMARY KEY (Address))" );
125
126 // What if there's SQL syntax error? e.g. missing ')'
127
128 stat.executeUpdate( "INSERT INTO Locations values ('Seneca College', 1.2,3.4)" );
129
130 stat.executeUpdate( "INSERT INTO Locations values ('Humber College', 5.6,7.8)" );
131
132 System.out.println( "querying the database table..." );
133
134 // SQL statement: database query
135 ResultSet rs = stat.executeQuery( "SELECT * FROM Locations" );
136
137 // an SQLException is thrown if the table name
138 // is incorrect
139
140 /* Step 5: Analyze(Navigate) the query result(the ResultSet) */
141
142 System.out.println("\n******* The Result of the Query *******");
143
144 while ( rs.next() ) {
145
146 System.out.println( "Line 1: " + rs.getString(1) +
147
148 "Line 2: " + rs.getString(2) );
149
150 }
151
152 rs.close(); // close the ResultSet object
153 stat.close(); // close the Statement object
154 }
155 catch ( SQLException exc )
156 {
157 System.out.println( " query failed with: " + exc.getMessage() );
158 return false;
159 }
160 finally { // always executed
161
162 System.out.println( "\nclose the database connection..." );
163
164 try { if (conn != null )
165 conn.close(); // close the database connection
166 }
167 catch (SQLException se ) { se.printStackTrace(); }
168 }
169
170 System.out.println( "DONE" );
171
172 return true;
173
174 } // end testQueryAll
175
176 public void getMetadata( ResultSet rs ) {
177
178 /* Retrieve the metadata of a table */
179 System.out.println( "metadata info:" );
180
181 try {
182 ResultSetMetaData rsmeta = rs.getMetaData();
183
184 int columnCount = rsmeta.getColumnCount();
185
186 System.out.println( "number of columns: " + columnCount );
187
188 for (int i=1; i <= columnCount; i++)
189 System.out.println( "column name: " + rsmeta.getColumnName(i) +
190 "\n data type: " + rsmeta.getColumnTypeName(i) );
191 }
192 catch( SQLException e ) { e.printStackTrace(); }
193 }
194
195} // end TestJDBC5