9
* $Id: basic.java,v 1.11 2001/11/25 23:26:56 barry Exp $
11
* This example tests the basic components of the JDBC driver, and shows
12
* how even the simplest of queries can be implemented.
14
* To use this example, you need a database to be in existence. This example
15
* will create a table called basic.
17
* Note: This will only work with post 7.0 drivers.
23
Connection db; // The connection to the database
24
Statement st; // Our statement to run queries with
26
public basic(String args[]) throws ClassNotFoundException, FileNotFoundException, IOException, SQLException
33
Class.forName("org.postgresql.Driver");
35
// Connect to database
36
System.out.println("Connecting to Database URL = " + url);
37
db = DriverManager.getConnection(url, usr, pwd);
39
System.out.println("Connected...Now creating a statement");
40
st = db.createStatement();
42
// Clean up the database (in case we failed earlier) then initialise
45
// Now run tests using JDBC methods
48
// Clean up the database
51
// Finally close the database
52
System.out.println("Now closing the connection");
56
//throw postgresql.Driver.notImplemented();
60
* This drops the table (if it existed). No errors are reported.
66
st.executeUpdate("drop table basic");
70
// We ignore any errors here
75
* This performs the example
77
public void doexample() throws SQLException
79
System.out.println("\nRunning tests:");
81
// First we need a table to store data in
82
st.executeUpdate("create table basic (a int2, b int2)");
84
// Now insert some data, using the Statement
85
st.executeUpdate("insert into basic values (1,1)");
86
st.executeUpdate("insert into basic values (2,1)");
87
st.executeUpdate("insert into basic values (3,1)");
89
// This shows how to get the oid of a just inserted row
91
st.executeUpdate("insert into basic values (4,1)");
92
long insertedOID = ((org.postgresql.Statement)st).getLastOID();
93
System.out.println("Inserted row with oid " + insertedOID);
95
// Now change the value of b from 1 to 8
96
st.executeUpdate("update basic set b=8");
97
System.out.println("Updated " + st.getUpdateCount() + " rows");
100
st.executeUpdate("delete from basic where a<3");
101
System.out.println("deleted " + st.getUpdateCount() + " rows");
103
// For large inserts, a PreparedStatement is more efficient, because it
104
// supports the idea of precompiling the SQL statement, and to store
105
// directly, a Java object into any column. PostgreSQL doesnt support
106
// precompiling, but does support setting a column to the value of a
107
// Java object (like Date, String, etc).
109
// Also, this is the only way of writing dates in a datestyle independent
110
// manner. (DateStyles are PostgreSQL's way of handling different methods
111
// of representing dates in the Date data type.)
112
PreparedStatement ps = db.prepareStatement("insert into basic values (?,?)");
113
for (int i = 2;i < 5;i++)
115
ps.setInt(1, 4); // "column a" = 5
116
ps.setInt(2, i); // "column b" = i
117
ps.executeUpdate(); // executeUpdate because insert returns no data
119
ps.close(); // Always close when we are done with it
121
// Finally perform a query on the table
122
System.out.println("performing a query");
123
ResultSet rs = st.executeQuery("select a, b from basic");
126
// Now we run through the result set, printing out the result.
127
// Note, we must call .next() before attempting to read any results
130
int a = rs.getInt("a"); // This shows how to get the value by name
131
int b = rs.getInt(2); // This shows how to get the value by column
132
System.out.println(" a=" + a + " b=" + b);
134
rs.close(); // again, you must close the result when done
137
// Now run the query again, showing a more efficient way of getting the
138
// result if you don't know what column number a value is in
141
System.out.println("performing another query");
142
rs = st.executeQuery("select * from basic where b>1");
145
// First find out the column numbers.
147
// It's best to do this here, as calling the methods with the column
148
// numbers actually performs this call each time they are called. This
149
// really speeds things up on large queries.
151
int col_a = rs.findColumn("a");
152
int col_b = rs.findColumn("b");
154
// Now we run through the result set, printing out the result.
155
// Again, we must call .next() before attempting to read any results
158
int a = rs.getInt(col_a); // This shows how to get the value by name
159
int b = rs.getInt(col_b); // This shows how to get the value by column
160
System.out.println(" a=" + a + " b=" + b);
162
rs.close(); // again, you must close the result when done
165
// Now test maxrows by setting it to 3 rows
169
System.out.println("performing a query limited to " + st.getMaxRows());
170
rs = st.executeQuery("select a, b from basic");
173
int a = rs.getInt("a"); // This shows how to get the value by name
174
int b = rs.getInt(2); // This shows how to get the value by column
175
System.out.println(" a=" + a + " b=" + b);
177
rs.close(); // again, you must close the result when done
179
// The last thing to do is to drop the table. This is done in the
184
* Display some instructions on how to run the example
186
public static void instructions()
188
System.out.println("\nThis example tests the basic components of the JDBC driver, demonstrating\nhow to build simple queries in java.\n");
189
System.out.println("Useage:\n java example.basic jdbc:postgresql:database user password [debug]\n\nThe debug field can be anything. It's presence will enable DriverManager's\ndebug trace. Unless you want to see screens of items, don't put anything in\nhere.");
194
* This little lot starts the test
196
public static void main(String args[])
198
System.out.println("PostgreSQL basic test v6.3 rev 1\n");
203
// This line outputs debug information to stderr. To enable this, simply
204
// add an extra parameter to the command line
206
DriverManager.setLogStream(System.err);
211
basic test = new basic(args);
215
System.err.println("Exception caught.\n" + ex);
216
ex.printStackTrace();