diff options
Diffstat (limited to 'src/interfaces/jdbc/example/basic.java')
| -rw-r--r-- | src/interfaces/jdbc/example/basic.java | 172 |
1 files changed, 172 insertions, 0 deletions
diff --git a/src/interfaces/jdbc/example/basic.java b/src/interfaces/jdbc/example/basic.java new file mode 100644 index 00000000000..1ea20d1d932 --- /dev/null +++ b/src/interfaces/jdbc/example/basic.java @@ -0,0 +1,172 @@ +package example; + +import java.io.*; +import java.sql.*; +import java.text.*; + +/** + * This example tests the basic components of the JDBC driver, and shows + * how even the simplest of queries can be implemented. + * + * To use this example, you need a database to be in existence. This example + * will create a table called basic. + * + */ + +public class basic +{ + Connection db; // The connection to the database + Statement st; // Our statement to run queries with + + public basic(String args[]) throws ClassNotFoundException, FileNotFoundException, IOException, SQLException + { + String url = args[0]; + String usr = args[1]; + String pwd = args[2]; + + // Load the driver + Class.forName("postgresql.Driver"); + + // Connect to database + System.out.println("Connecting to Database URL = " + url); + db = DriverManager.getConnection(url, usr, pwd); + + System.out.println("Connected...Now creating a statement"); + st = db.createStatement(); + + // Clean up the database (in case we failed earlier) then initialise + cleanup(); + + // Now run tests using JDBC methods + doexample(); + + // Clean up the database + cleanup(); + + // Finally close the database + System.out.println("Now closing the connection"); + st.close(); + db.close(); + + } + + /** + * This drops the table (if it existed). No errors are reported. + */ + public void cleanup() + { + try { + st.executeUpdate("drop table basic"); + } catch(Exception ex) { + // We ignore any errors here + } + } + + /** + * This performs the example + */ + public void doexample() throws SQLException + { + System.out.println("\nRunning tests:"); + + // First we need a table to store data in + st.executeUpdate("create table basic (a int2, b int2)"); + + // Now insert some data, using the Statement + st.executeUpdate("insert into basic values (1,1)"); + st.executeUpdate("insert into basic values (2,1)"); + st.executeUpdate("insert into basic values (3,1)"); + + // For large inserts, a PreparedStatement is more efficient, because it + // supports the idea of precompiling the SQL statement, and to store + // directly, a Java object into any column. PostgreSQL doesnt support + // precompiling, but does support setting a column to the value of a + // Java object (like Date, String, etc). + // + // Also, this is the only way of writing dates in a datestyle independent + // manner. (DateStyles are PostgreSQL's way of handling different methods + // of representing dates in the Date data type.) + PreparedStatement ps = db.prepareStatement("insert into basic values (?,?)"); + for(int i=2;i<5;i++) { + ps.setInt(1,4); // "column a" = 5 + ps.setInt(2,i); // "column b" = i + ps.executeUpdate(); // executeUpdate because insert returns no data + } + ps.close(); // Always close when we are done with it + + // Finally perform a query on the table + System.out.println("performing a query"); + ResultSet rs = st.executeQuery("select a, b from basic"); + if(rs!=null) { + // Now we run through the result set, printing out the result. + // Note, we must call .next() before attempting to read any results + while(rs.next()) { + int a = rs.getInt("a"); // This shows how to get the value by name + int b = rs.getInt(2); // This shows how to get the value by column + System.out.println(" a="+a+" b="+b); + } + rs.close(); // again, you must close the result when done + } + + // Now run the query again, showing a more efficient way of getting the + // result if you don't know what column number a value is in + System.out.println("performing another query"); + rs = st.executeQuery("select * from basic where b>1"); + if(rs!=null) { + // First find out the column numbers. + // + // It's best to do this here, as calling the methods with the column + // numbers actually performs this call each time they are called. This + // really speeds things up on large queries. + // + int col_a = rs.findColumn("a"); + int col_b = rs.findColumn("b"); + + // Now we run through the result set, printing out the result. + // Again, we must call .next() before attempting to read any results + while(rs.next()) { + int a = rs.getInt(col_a); // This shows how to get the value by name + int b = rs.getInt(col_b); // This shows how to get the value by column + System.out.println(" a="+a+" b="+b); + } + rs.close(); // again, you must close the result when done + } + + // The last thing to do is to drop the table. This is done in the + // cleanup() method. + } + + /** + * Display some instructions on how to run the example + */ + public static void instructions() + { + System.out.println("\nThis example tests the basic components of the JDBC driver, demonstrating\nhow to build simple queries in java.\n"); + 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."); + System.exit(1); + } + + /** + * This little lot starts the test + */ + public static void main(String args[]) + { + System.out.println("PostgreSQL basic test v6.3 rev 1\n"); + + if(args.length<3) + instructions(); + + // This line outputs debug information to stderr. To enable this, simply + // add an extra parameter to the command line + if(args.length>3) + DriverManager.setLogStream(System.err); + + // Now run the tests + try { + basic test = new basic(args); + } catch(Exception ex) { + System.err.println("Exception caught.\n"+ex); + ex.printStackTrace(); + } + } +} |