Plain Old SQL examples

Plain Old SQL Statements - examples

Pre-requisities and imports

We use the Jacarta commons lang library (commons-lang-2.1.jar), you can get that at http://jakarta.apache.org/site/downloads/downloads_commons-lang.cgi .

Setting up the connection

We simply create a POSS connection from a JDBC connection:

   
   
Class.forName("com.mysql.jdbc.Driver").newInstance();


       
Connection myConnection = DriverManager.getConnection("jdbc:mysql://"


       
        +
hostPort + "/mysql?user=" + user + "&password=" + password);


       
PSconnection psConn = new PSconnection(myConnection);

Working directly from the connection

You can do most things directly from the connection. As long as you use bind variables (as appropriate) the SQL will be automatically cached, so there's no particular performance disadvantage to doing things this way;

        psConn.doSql("DROP TABLE IF EXISTS test1");



        psConn.doSql("CREATE TABLE test1 (num INT,name VARCHAR(30))");

        psConn.doSql("INSERT INTO test1 VALUES (1,'one'),(2,'two') ");

        psConn.commit();



        // fetch all the rows

        System.out.println(psConn.fetchAll("SELECT * FROM test1"));

        // Output: [[1, one], [2, two]]

        // fetch with a bind variable into result set

        RowSet rs = psConn.fetchAll("SELECT * FROM test1 WHERE num>?", 0);

        System.out.println(rs.getRows());

Creating SQL statements

Creating a SQL object usually leads to better code and has more options for dealing with the result set. You can bind variables either directly using a bindArgs method, or in the execute or fetchAll methods. RowSet and Row objects are all printable. A RowSet acts like an ArrayList of HashMaps. The HashMap is keyed by column name but you can retrieve by column number if you like:

       PlainSql mySql = psConn

           
    .createSql("SELECT * FROM test1 WHERE num=? AND
name=?");

        mySql.bindArgs(1, "one");

        mySql.execute();

        while (mySql.nextRow()) {

            Row r = mySql.fetchOne();

            System.out.println("num=" + r.get("num")); // access column by name

            System.out.println("value=" + r.get("value")); // access column by

           
           
           
           
        // number

        }



        // The quick way (but you need enough memory to get all the rows)

        RowSet rs = mySql.fetchAll(1, "one");

        for (Row row : rs.getRows()) {

            System.out.println(row);

        }

The cache

PlainSql statements are prespared statement objects that are kept open as long as the PlainSql object is open. When closed, they are kept in a LRU cache in case you request a prepare on the same SQL text. Obviously, using bind variables is neccessary to keep the cache efficient.

There is one cache per connection. It's 30 statements big by default, and statements are inserted at postition 20. This stops one-off SQLs from pushing repeatable SQLs out.

I need to add some methods to let you configure the cache.

Table objects and array inserts

A table object is like a Plain SQL that is based on a SELECT * FROM table. You can select from the table, and there are methods to limit the rows returned.

But the main thiing is that there is an array insert interface, as shown below:

    PlainTable test1 = psConn.createTable("test1");

        // A RowSet matching the table

        RowSet test1Rows = test1.emptyRowSet();

        // Create a row and add it to the rowset



        // Add some rows

        for (int i = 3; i < 10; i++) {

            test1Rows.addRow("num", i, "name", "row " + i);

        }

        //Add the rows as a batch

        test1.insertArray(test1Rows);

        psConn.commit();

        test1Rows.clear(); // in case we want to do it again

        System.out.println(psConn.fetchAll("SELECT * FROM test1").toString());

Other stuff

The PSsequence class is an efficient portable sequence number generator.

PlainTableDLL provides some convenient ways of creating tables. Its a work in progress.

PlainProc is a stored procedure object but it also is a work in progress.