001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.sample;
032:
033: import java.sql.Connection;
034: import java.sql.DriverManager;
035: import java.sql.ResultSet;
036: import java.sql.ResultSetMetaData;
037: import java.sql.SQLException;
038: import java.sql.Statement;
039:
040: /**
041: * Title: Testdb
042: * Description: simple hello world db example of a
043: * standalone persistent db application
044: *
045: * every time it runs it adds four more rows to sample_table
046: * it does a query and prints the results to standard out
047: *
048: * Author: Karl Meissner karl@meissnersd.com
049: */
050: public class Testdb {
051:
052: Connection conn; //our connnection to the db - presist for life of program
053:
054: // we dont want this garbage collected until we are done
055: public Testdb(String db_file_name_prefix) throws Exception { // note more general exception
056:
057: // Load the HSQL Database Engine JDBC driver
058: // hsqldb.jar should be in the class path or made part of the current jar
059: Class.forName("org.hsqldb.jdbcDriver");
060:
061: // connect to the database. This will load the db files and start the
062: // database if it is not alread running.
063: // db_file_name_prefix is used to open or create files that hold the state
064: // of the db.
065: // It can contain directory names relative to the
066: // current working directory
067: conn = DriverManager.getConnection("jdbc:hsqldb:"
068: + db_file_name_prefix, // filenames
069: "sa", // username
070: ""); // password
071: }
072:
073: public void shutdown() throws SQLException {
074:
075: Statement st = conn.createStatement();
076:
077: // db writes out to files and performs clean shuts down
078: // otherwise there will be an unclean shutdown
079: // when program ends
080: st.execute("SHUTDOWN");
081: conn.close(); // if there are no other open connection
082: }
083:
084: //use for SQL command SELECT
085: public synchronized void query(String expression)
086: throws SQLException {
087:
088: Statement st = null;
089: ResultSet rs = null;
090:
091: st = conn.createStatement(); // statement objects can be reused with
092:
093: // repeated calls to execute but we
094: // choose to make a new one each time
095: rs = st.executeQuery(expression); // run the query
096:
097: // do something with the result set.
098: dump(rs);
099: st.close(); // NOTE!! if you close a statement the associated ResultSet is
100:
101: // closed too
102: // so you should copy the contents to some other object.
103: // the result set is invalidated also if you recycle an Statement
104: // and try to execute some other query before the result set has been
105: // completely examined.
106: }
107:
108: //use for SQL commands CREATE, DROP, INSERT and UPDATE
109: public synchronized void update(String expression)
110: throws SQLException {
111:
112: Statement st = null;
113:
114: st = conn.createStatement(); // statements
115:
116: int i = st.executeUpdate(expression); // run the query
117:
118: if (i == -1) {
119: System.out.println("db error : " + expression);
120: }
121:
122: st.close();
123: } // void update()
124:
125: public static void dump(ResultSet rs) throws SQLException {
126:
127: // the order of the rows in a cursor
128: // are implementation dependent unless you use the SQL ORDER statement
129: ResultSetMetaData meta = rs.getMetaData();
130: int colmax = meta.getColumnCount();
131: int i;
132: Object o = null;
133:
134: // the result set is a cursor into the data. You can only
135: // point to one row at a time
136: // assume we are pointing to BEFORE the first row
137: // rs.next() points to next row and returns true
138: // or false if there is no next row, which breaks the loop
139: for (; rs.next();) {
140: for (i = 0; i < colmax; ++i) {
141: o = rs.getObject(i + 1); // Is SQL the first column is indexed
142:
143: // with 1 not 0
144: System.out.print(o.toString() + " ");
145: }
146:
147: System.out.println(" ");
148: }
149: } //void dump( ResultSet rs )
150:
151: public static void main(String[] args) {
152:
153: Testdb db = null;
154:
155: try {
156: db = new Testdb("db_file");
157: } catch (Exception ex1) {
158: ex1.printStackTrace(); // could not start db
159:
160: return; // bye bye
161: }
162:
163: try {
164:
165: //make an empty table
166: //
167: // by declaring the id column IDENTITY, the db will automatically
168: // generate unique values for new rows- useful for row keys
169: db
170: .update("CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)");
171: } catch (SQLException ex2) {
172:
173: //ignore
174: //ex2.printStackTrace(); // second time we run program
175: // should throw execption since table
176: // already there
177: //
178: // this will have no effect on the db
179: }
180:
181: try {
182:
183: // add some rows - will create duplicates if run more then once
184: // the id column is automatically generated
185: db
186: .update("INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100)");
187: db
188: .update("INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200)");
189: db
190: .update("INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300)");
191: db
192: .update("INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400)");
193:
194: // do a query
195: db.query("SELECT * FROM sample_table WHERE num_col < 250");
196:
197: // at end of program
198: db.shutdown();
199: } catch (SQLException ex3) {
200: ex3.printStackTrace();
201: }
202: } // main()
203: } // class Testdb
|