DerbyDemo.java
1: // This code demonstrates the creation and use of an embedded Derby
2: // database. If the DB doesn't exist it is created, a couple of records
3: // are inserted, and the results of a query are displayed.
4: // When run, the embedded DB is created in a sub-directory called "DerbyDemoDB",
5: // of the current directory. Also, the text file derby.log is created in the
6: // current directory.
7: //
8: // Written 4/2009 by Wayne Pollock, Tampa Florida USA
9: // Updated 4/2013 by WP: Fixed problem of dropping a non-existent table;
10: // Could ignore Exception instead of testing.
11: // Updated 3/2019 by WP: Updated directions.
12:
13: import java.sql.*;
14: import javax.swing.JOptionPane;
15:
16: public class DerbyDemo {
17:
18: public static void main ( String [] args ) {
19:
20: String dbName="DerbyDemoDB";
21: String connectionURL = "jdbc:derby:" + dbName + ";create=true";
22: // The ";create=true" will create the DB if not created yet.
23:
24: String SQL_CreateTable = "create table addresses ( "
25: + "ID int not null generated always as identity "
26: + " (start with 1000), "
27: + "lname varchar(40) not null, fname varchar(40) not null, "
28: + "phone varchar(14), notes varchar(256), "
29: + "primary key (ID) )";
30:
31: // This SQL inserts three records into the addresses table:
32: String SQL_Insert = "insert into addresses "
33: + "(lname, fname, phone, notes) values "
34: + "('Pollock', 'Wayne', '253-7213', 'Professor'), "
35: + "('Piffl', 'Hymie', NULL, 'Fake student name'), "
36: + "('Jojo', 'Mojo', NULL, 'Super-villan')";
37:
38: String SQL_Query = "SELECT * FROM addresses";
39:
40: Connection con = null;
41: Statement stmnt = null;
42:
43: // Try to connect to the DB:
44: try {
45: con = DriverManager.getConnection( connectionURL );
46: } catch ( Exception e ) {
47: System.err.println( "**** Cannot open connection to "
48: + dbName + "!" );
49: System.exit(1);
50: }
51:
52: // Drop (delete) the table if it exists. This is common for demo code,
53: // otherwise every time you run the code, it keeps adding copies of the
54: // data. Current versions of Derby throw an Exception if you try to drop
55: // a non-existing table, so check if it is there first:
56:
57: if ( tableExists( con, "addresses" ) ) {
58: System.out.println ( "Dropping table addresses..." );
59: try {
60: stmnt = con.createStatement();
61: stmnt.executeUpdate( "DROP TABLE addresses" );
62: stmnt.close();
63: } catch ( SQLException e ) {
64: String theError = e.getSQLState();
65: System.out.println( "Can't drop table: " + theError );
66: System.exit(1);
67: }
68: }
69:
70: // Create the table addresses if it doesn't exist:
71: if ( ! tableExists( con, "addresses" ) ) {
72: System.out.println ( "Creating table addresses..." );
73: try {
74: stmnt = con.createStatement();
75: stmnt.execute( SQL_CreateTable );
76: stmnt.close();
77: } catch ( SQLException e ) {
78: String theError = e.getSQLState();
79: System.out.println( "Can't create table: " + theError );
80: System.exit(1);
81: }
82: }
83:
84: // Insert records into table (Note if you run this code twice
85: // the same people get added but with different IDs):
86: try {
87: stmnt = con.createStatement();
88: System.out.println ( "Inserting rows into table addresses..." );
89: stmnt.executeUpdate( SQL_Insert ); // Add some rows
90: stmnt.close();
91: } catch ( SQLException e ) {
92: String theError = e.getSQLState();
93: System.out.println( "Can't insert rows in table: " + theError );
94: System.exit(1);
95: }
96:
97: // Query the table and display the results:
98: try {
99: stmnt = con.createStatement();
100: // This is dangerous if the query string contains any external text!
101: ResultSet rs = stmnt.executeQuery( SQL_Query );
102: displayResults( rs );
103: stmnt.close();
104:
105: // When not using your own data in SQL statement, you should use
106: // PreparedStatements instead of Statements, to prevent SQL injection
107: // attacks (a common security vulnerability in "textbook-quality"
108: // code). Here's an example to query the table with untrusted user data:
109:
110: // The SQL Query to use (note case-insensitive comparison):
111: String dangerousQuery =
112: "SELECT * FROM ADDRESSES WHERE UPPER(LNAME) = UPPER(?)";
113:
114: // Create a prepared statement to use:
115: PreparedStatement pStmnt = con.prepareStatement( dangerousQuery );
116:
117: // Get the last name to query for, from the user:
118: String lastName = JOptionPane.showInputDialog(
119: "Please enter a last name to search for: " );
120:
121: if ( lastName != null ) {
122: // Safely substitute data for "?" in query:
123: // (Note there are many type-checking set* methods, e.g. "setInt")
124: pStmnt.setString( 1, lastName );
125: ResultSet lastNameSearchResults = pStmnt.executeQuery();
126: System.out.println( "\n\tResults of last name query for \""
127: + lastName + "\"" );
128: displayResults( lastNameSearchResults );
129: }
130:
131: pStmnt.close();
132: con.close();
133: } catch ( SQLException e ) {
134: String theError = e.getSQLState();
135: System.out.println("Can't query table: " + theError );
136: System.exit(1);
137: }
138:
139: // Shut down all databases and the Derby engine, when done. Note,
140: // Derby always throws an Exception when shutdown, so ignore it:
141: System.out.println ( "Shutting down the database..." );
142: try {
143: DriverManager.getConnection("jdbc:derby:;shutdown=true");
144: } catch ( SQLException e ) {} // empty: ignore exception
145:
146: // Note that nothing breaks if you don't cleanly shut down Derby, but
147: // it will start in recovery mode next time (which takes longer to start).
148:
149: }
150:
151: // Derby doesn't support the standard SQL views. To see if a table
152: // exists you normally query the right view and see if any rows are
153: // returned (none if no such table, one if table exists). Derby
154: // does support a non-standard set of views which are complicated,
155: // but standard JDBC supports a DatabaseMetaData.getTables method.
156: // That returns a ResultSet but not one where you can easily count
157: // rows by "rs.last(); int numRows = rs.getRow()". Hence the loop.
158:
159: private static boolean tableExists ( Connection con, String table ) {
160: int numRows = 0;
161: try {
162: DatabaseMetaData dbmd = con.getMetaData();
163: // Note the args to getTables are case-sensitive!
164: ResultSet rs = dbmd.getTables( null, "APP", table.toUpperCase(), null);
165: while( rs.next() ) ++numRows;
166: } catch ( SQLException e ) {
167: String theError = e.getSQLState();
168: System.out.println("Can't query DB metadata: " + theError );
169: System.exit(1);
170: }
171: return numRows > 0;
172: }
173:
174: private static void displayResults ( ResultSet rs ) {
175: // Collect meta-data:
176: try {
177: ResultSetMetaData meta = rs.getMetaData();
178: String catalog = meta.getCatalogName(1);
179: String schema = meta.getSchemaName(1);
180: String table = meta.getTableName(1);
181: int numColumns = meta.getColumnCount();
182:
183: // Display results:
184: System.out.print( "\n\t\t---" );
185: if ( catalog != null && catalog.length() > 0 )
186: System.out.print( " Catalog: " + catalog );
187: if ( schema != null && schema.length() > 0 )
188: System.out.print( " Schema: " + schema );
189:
190: System.out.println( ", Table: " + table + " ---\n" );
191:
192: for ( int i = 1; i <= numColumns; ++i )
193: System.out.printf( "%-12s", meta.getColumnLabel( i ) );
194: System.out.println();
195:
196: while ( rs.next() ) { // Fetch next row, quit when no rows left.
197: for ( int i = 1; i <= numColumns; ++i ) {
198: String val = rs.getString( i );
199: if ( val == null )
200: val = "(null)";
201: System.out.printf( "%-12s", val );
202: }
203: System.out.println();
204: }
205: } catch ( SQLException e ) {
206: String theError = e.getSQLState();
207: System.out.println("Can't view resultSet: " + theError );
208: System.exit(1);
209: }
210: }
211: }