KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hsqldb > sample > Testdb


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32 package org.hsqldb.sample;
33
34 import java.sql.Connection JavaDoc;
35 import java.sql.DriverManager JavaDoc;
36 import java.sql.ResultSet JavaDoc;
37 import java.sql.ResultSetMetaData JavaDoc;
38 import java.sql.SQLException JavaDoc;
39 import java.sql.Statement JavaDoc;
40
41 /**
42  * Title: Testdb
43  * Description: simple hello world db example of a
44  * standalone persistent db application
45  *
46  * every time it runs it adds four more rows to sample_table
47  * it does a query and prints the results to standard out
48  *
49  * Author: Karl Meissner karl@meissnersd.com
50  */
51 public class Testdb {
52
53     Connection JavaDoc conn; //our connnection to the db - presist for life of program
54
55     // we dont want this garbage collected until we are done
56 public Testdb(String JavaDoc db_file_name_prefix) throws Exception JavaDoc { // note more general exception
57
58         // Load the HSQL Database Engine JDBC driver
59 // hsqldb.jar should be in the class path or made part of the current jar
60 Class.forName("org.hsqldb.jdbcDriver");
61
62         // connect to the database. This will load the db files and start the
63 // database if it is not alread running.
64 // db_file_name_prefix is used to open or create files that hold the state
65 // of the db.
66 // It can contain directory names relative to the
67 // current working directory
68 conn = DriverManager.getConnection("jdbc:hsqldb:"
69                                            + db_file_name_prefix, // filenames
70 "sa", // username
71 ""); // password
72 }
73
74     public void shutdown() throws SQLException JavaDoc {
75
76         Statement JavaDoc st = conn.createStatement();
77
78         // db writes out to files and performs clean shuts down
79 // otherwise there will be an unclean shutdown
80 // when program ends
81 st.execute("SHUTDOWN");
82         conn.close(); // if there are no other open connection
83 }
84
85 //use for SQL command SELECT
86 public synchronized void query(String JavaDoc expression) throws SQLException JavaDoc {
87
88         Statement JavaDoc st = null;
89         ResultSet JavaDoc rs = null;
90
91         st = conn.createStatement(); // statement objects can be reused with
92
93         // repeated calls to execute but we
94 // choose to make a new one each time
95 rs = st.executeQuery(expression); // run the query
96
97         // do something with the result set.
98 dump(rs);
99         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 JavaDoc expression) throws SQLException JavaDoc {
110
111         Statement JavaDoc st = null;
112
113         st = conn.createStatement(); // statements
114
115         int i = st.executeUpdate(expression); // run the query
116
117         if (i == -1) {
118             System.out.println("db error : " + expression);
119         }
120
121         st.close();
122     } // void update()
123
124     public static void dump(ResultSet JavaDoc rs) throws SQLException JavaDoc {
125
126         // the order of the rows in a cursor
127 // are implementation dependent unless you use the SQL ORDER statement
128 ResultSetMetaData JavaDoc meta = rs.getMetaData();
129         int colmax = meta.getColumnCount();
130         int i;
131         Object JavaDoc o = null;
132
133         // the result set is a cursor into the data. You can only
134 // point to one row at a time
135 // assume we are pointing to BEFORE the first row
136 // rs.next() points to next row and returns true
137 // or false if there is no next row, which breaks the loop
138 for (; rs.next(); ) {
139             for (i = 0; i < colmax; ++i) {
140                 o = rs.getObject(i + 1); // Is SQL the first column is indexed
141
142                 // with 1 not 0
143 System.out.print(o.toString() + " ");
144             }
145
146             System.out.println(" ");
147         }
148     } //void dump( ResultSet rs )
149
150     public static void main(String JavaDoc[] args) {
151
152         Testdb db = null;
153
154         try {
155             db = new Testdb("db_file");
156         } catch (Exception JavaDoc ex1) {
157             ex1.printStackTrace(); // could not start db
158
159             return; // bye bye
160 }
161
162         try {
163
164             //make an empty table
165 //
166 // by declaring the id column IDENTITY, the db will automatically
167 // generate unique values for new rows- useful for row keys
168 db.update(
169                 "CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)");
170         } catch (SQLException JavaDoc ex2) {
171
172             //ignore
173 //ex2.printStackTrace(); // second time we run program
174 // should throw execption since table
175 // already there
176 //
177 // this will have no effect on the db
178 }
179
180         try {
181
182             // add some rows - will create duplicates if run more then once
183 // the id column is automatically generated
184 db.update(
185                 "INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100)");
186             db.update(
187                 "INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200)");
188             db.update(
189                 "INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300)");
190             db.update(
191                 "INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400)");
192
193             // do a query
194 db.query("SELECT * FROM sample_table WHERE num_col < 250");
195
196             // at end of program
197 db.shutdown();
198         } catch (SQLException JavaDoc ex3) {
199             ex3.printStackTrace();
200         }
201     } // main()
202 } // class Testdb
203
204
Popular Tags