1 package example;
2
3 import java.io;
4 import java.sql;
5 import java.text;
6
7 /**
8 *
9 * $Id: basic.java,v 1.1.1.1 2002/01/22 08:51:50 synmscott Exp $
10 *
11 * This example tests the basic components of the JDBC driver, and shows
12 * how even the simplest of queries can be implemented.
13 *
14 * To use this example, you need a database to be in existence. This example
15 * will create a table called basic.
16 *
17 * Note: This will only work with post 7.0 drivers.
18 *
19 */
20
21 public class basic
22 {
23 Connection db; // The connection to the database
24 Statement st; // Our statement to run queries with
25
26 public basic(String args[]) throws ClassNotFoundException, FileNotFoundException, IOException, SQLException
27 {
28 String url = args[0];
29 String usr = args[1];
30 String pwd = args[2];
31
32 // Load the driver
33 Class.forName("org.postgresql.Driver");
34
35 // Connect to database
36 System.out.println("Connecting to Database URL = " + url);
37 db = DriverManager.getConnection(url, usr, pwd);
38
39 System.out.println("Connected...Now creating a statement");
40 st = db.createStatement();
41
42 // Clean up the database (in case we failed earlier) then initialise
43 cleanup();
44
45 // Now run tests using JDBC methods
46 doexample();
47
48 // Clean up the database
49 cleanup();
50
51 // Finally close the database
52 System.out.println("Now closing the connection");
53 st.close();
54 db.close();
55
56 //throw postgresql.Driver.notImplemented();
57 }
58
59 /**
60 * This drops the table (if it existed). No errors are reported.
61 */
62 public void cleanup()
63 {
64 try {
65 st.executeUpdate("drop table basic");
66 } catch(Exception ex) {
67 // We ignore any errors here
68 }
69 }
70
71 /**
72 * This performs the example
73 */
74 public void doexample() throws SQLException
75 {
76 System.out.println("\nRunning tests:");
77
78 // First we need a table to store data in
79 st.executeUpdate("create table basic (a int2, b int2)");
80
81 // Now insert some data, using the Statement
82 st.executeUpdate("insert into basic values (1,1)");
83 st.executeUpdate("insert into basic values (2,1)");
84 st.executeUpdate("insert into basic values (3,1)");
85
86 // Now change the value of b from 1 to 8
87 st.executeUpdate("update basic set b=8");
88 System.out.println("Updated "+st.getUpdateCount()+" rows");
89
90 // For large inserts, a PreparedStatement is more efficient, because it
91 // supports the idea of precompiling the SQL statement, and to store
92 // directly, a Java object into any column. PostgreSQL doesnt support
93 // precompiling, but does support setting a column to the value of a
94 // Java object (like Date, String, etc).
95 //
96 // Also, this is the only way of writing dates in a datestyle independent
97 // manner. (DateStyles are PostgreSQL's way of handling different methods
98 // of representing dates in the Date data type.)
99 PreparedStatement ps = db.prepareStatement("insert into basic values (?,?)");
100 for(int i=2;i<5;i++) {
101 ps.setInt(1,4); // "column a" = 5
102 ps.setInt(2,i); // "column b" = i
103 ps.executeUpdate(); // executeUpdate because insert returns no data
104 }
105 ps.close(); // Always close when we are done with it
106
107 // Finally perform a query on the table
108 System.out.println("performing a query");
109 ResultSet rs = st.executeQuery("select a, b from basic");
110 if(rs!=null) {
111 // Now we run through the result set, printing out the result.
112 // Note, we must call .next() before attempting to read any results
113 while(rs.next()) {
114 int a = rs.getInt("a"); // This shows how to get the value by name
115 int b = rs.getInt(2); // This shows how to get the value by column
116 System.out.println(" a="+a+" b="+b);
117 }
118 rs.close(); // again, you must close the result when done
119 }
120
121 // Now run the query again, showing a more efficient way of getting the
122 // result if you don't know what column number a value is in
123 System.out.println("performing another query");
124 rs = st.executeQuery("select * from basic where b>1");
125 if(rs!=null) {
126 // First find out the column numbers.
127 //
128 // It's best to do this here, as calling the methods with the column
129 // numbers actually performs this call each time they are called. This
130 // really speeds things up on large queries.
131 //
132 int col_a = rs.findColumn("a");
133 int col_b = rs.findColumn("b");
134
135 // Now we run through the result set, printing out the result.
136 // Again, we must call .next() before attempting to read any results
137 while(rs.next()) {
138 int a = rs.getInt(col_a); // This shows how to get the value by name
139 int b = rs.getInt(col_b); // This shows how to get the value by column
140 System.out.println(" a="+a+" b="+b);
141 }
142 rs.close(); // again, you must close the result when done
143 }
144
145 // The last thing to do is to drop the table. This is done in the
146 // cleanup() method.
147 }
148
149 /**
150 * Display some instructions on how to run the example
151 */
152 public static void instructions()
153 {
154 System.out.println("\nThis example tests the basic components of the JDBC driver, demonstrating\nhow to build simple queries in java.\n");
155 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.");
156 System.exit(1);
157 }
158
159 /**
160 * This little lot starts the test
161 */
162 public static void main(String args[])
163 {
164 System.out.println("PostgreSQL basic test v6.3 rev 1\n");
165
166 if(args.length<3)
167 instructions();
168
169 // This line outputs debug information to stderr. To enable this, simply
170 // add an extra parameter to the command line
171 if(args.length>3)
172 DriverManager.setLogStream(System.err);
173
174 // Now run the tests
175 try {
176 basic test = new basic(args);
177 } catch(Exception ex) {
178 System.err.println("Exception caught.\n"+ex);
179 ex.printStackTrace();
180 }
181 }
182 }
Save This Page