Saturday, October 31, 2009

Programming with JDBC and SQL




I l@ve RuBoard









Programming with JDBC and SQL


To start things off, we're going to create a table, insert some values, and query them.


Import the DriverManager and Class classes to load the JDBC driver.



>>> from java.sql import DriverManager
>>> from java.lang import Class

Load the driver using Class.forName, which dynamically loads a class into the Java Virtual Machine (JVM). (See the Java API documentation for more details.) For Microsoft Access via ODBC (or any ODBC database):



>>> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
<jclass sun.jdbc.odbc.JdbcOdbcDriver at -416861389>

For InstantDB:



>>> Class.forName("jdbc.idbDriver")
<jclass jdbc.idbDriver at -282805662>

Just as an HTML page has an HTTP URL, so a database has a JDBC URL. The last part of the URL for JdbcOdbcDriver refers to the DSN that we set up with the ODBC data source administrator in the last section. Enter this:



>>> url = "jdbc:odbc:db_dev"

The last part of the URL for InstantDB refers to the database properties file. Enter this:



>>> url = "jdbc:idb=db_dev.prp"

Now we can pass the URL to DriverManager's getConnection() method and pass the user name and password (both of which are blank, i.e., " "). Once we have a connection, we can create a table. Ours will have two columns, Name and Age, and we'll call it PERSON.


Create the JDBC connection object.



>>> connection = DriverManager.getConnection(url, "","")

Create the JDBC statement object.



>>> statement = connection.createStatement()

Create an SQL DDL statement that defines a database table.



>>> create_table = """CREATE TABLE PERSON (name VARCHAR(50), age INT)"""

Execute the SQL DDL statement.



>>> statement.executeUpdate(create_table)
-1

Close the connection. (Leave it open if you're going on to the next section.)



>>> connection.close()

Go to the database tools, and see that the table was created. The Microsoft Access version is shown in Figure 17-4. (For the InstantDB version, use SQLBuilder, which comes in the InstantDB examples directory.)


Figure 17-4. The PERSON Table�MS Access Version



Inserting Data


Now we can start adding data to our table. If you've disconnected or restarted your JPython interactive session, you'll have to reconnect first.



Reconnecting Your JPython Session


Import DriverManager and Class.



>>> from java.sql import DriverManager
>>> from java.lang import Class

For ODBC:



>>> Class.forName('sun.jdbc.odbc.JdbcOdbcDriver')
<jclass sun.jdbc.odbc.JdbcOdbcDriver ...>
>>> url="jdbc:odbc:db_dev"

For InstantDB:



>>> Class.forName("jdbc.idbDriver")
<jclass jdbc.idbDriver at -282805662>
>>> url = "jdbc:idb=db_dev.prp"

Get the connection, and create the statement.



>>> connection=DriverManager.getConnection(url, "", "")
>>> statement = connection.createStatement()


Once you have a JDBC statement object, you have to create an insert format string in order to insert data. (Notice the single quotes around %s. They're important for proper SQL syntax.)



>>> insert = "insert into Person (name, age) values ('%s', %d)"

Pass a tuple containing name and age to the string format.



>>> insert % ("Whitney", 3)
"insert into Person (name, age) values ('Whitney', 3)"

Add Whitney to the database.



>>> statement.executeUpdate(insert % ("Whitney", 3))
1

Add Kiley.



>>> statement.executeUpdate(insert % ("Kiley", 23))
1

Add Scott and Nick.



>>> statement.executeUpdate(insert % ("Scott", 34))
1

>>> statement.executeUpdate(insert % ("Nick", 3))
1

Add Mary and Adam.



>>> statement.executeUpdate(insert % ("Mary", 2))
1

>>> statement.executeUpdate(insert % ("Adam", 23))
1

Notice that after each addition the executeUpdate() method returns the number of rows affected.


Go to your database management tools, and view PERSON. Figure 17-5 shows its Microsoft Access version; use SQLBuilder to see the InstantDB version.


Figure 17-5. The PERSON Table with Data Added



By the way, keep your connection open. If you've already closed it, see the sidebar on reconnecting.



Accessing Data


To access the names and ages in our table we query the results object (an instance of java.sql.ResultSet), which uses the following methods:



  • next()�
    iterates through the table; returns 1 if there are more rows



  • getString()�
    gets the name specified in the name string passed to it



  • getInt()�
    gets the age specified in the age string passed to it





>>> results = statement.executeQuery("select name, age from Person")

Let's see how these methods work interactively.


Move to the first row.



>>> results.next()
1

Get the name.



>>> results.getString("name")
'Whitney'

Get the age.



>>> print results.getInt("age")
3

Define a function that can be used repeatedly to get the rest of the results.



>>> def showResults(results):
...     while(results.next()):
...             name = results.getString("name")
...             age = results.getInt("age")
...             print name + " is " + `age` + " years old."
...

Print the results.



>>> showResults(results)
Kiley is 23 years old.
Scott is 34 years old.
Nick is 3 years old.
Mary is 2 years old.
Adam is 23 years old.

Here's how we filter our results to get, say, only persons under the age of four:



>>> select = "select name, age from Person where age < 4"
>>> showResults(statement.executeQuery(select))
Whitney is 3 years old.
Nick is 3 years old.
Mary is 2 years old.

or persons over the age of twenty:



>>> select = "select name, age from Person where age > 20"
>>> showResults(statement.executeQuery(select))
Kiley is 23 years old.
Scott is 34 years old.
Adam is 23 years old.

Functions like count(), sum(), and stdev() allow us to get aggregate data, such as the count of persons in the table.



>>> results = statement.executeQuery("select count(name) as total from Person")
>>> results.next()
1
>>> print results.getString("total")
6

See if you can figure out what the next two interactive sessions are doing. (They won't work with InstantDB as of v.3.12.)



>>> r = statement.executeQuery("select stdev(age) as st from Person")
>>> r.next()
1
>>> print "Standard deviation" + r.getString("st")
Standard deviation=13.7501515143167
>>> r = statement.executeQuery("select sum(age) as [sum] from Person")
>>> r.next()
1
>>> print "Sum of ages " + r.getString("sum")
Sum of ages 88.


Removing and Changing Data


The first thing you want to do is import the JdbcTour.py module from the InstantDB and Access directories so you can use its showResults() function in the next interactive sessions. (If you disconnected, refer to the sidebar on reconnecting.)


Let's remove everyone over twenty. (Leave out the asterisk (*) in the method arguments if you're using InstantDB.)



>>> statement.executeUpdate("delete * from Person where age > 20")
3

The executeUpdate() method returns 3, which means that three records were deleted.


Now let's do some modifying. In this next session we'll show Whitney's current age and then change it. In other words, we'll update her record. Show Whitney's current age.



>>> select = "select name, age from Person where name = 'Whitney'"
>>> results = statement.executeQuery(select)
>>> showResults(results)
Whitney is 3 years old.

Change it to four.



>>> statement.executeUpdate("update Person set age = 4 where name = 'Whitney'")
1

Show Whitney's new age.



>>> results = statement.executeQuery(select)
>>> showResults(results)
Whitney is 4 years old.


SQL Subsets in InstantDB versus Microsoft Access


InstantDB is a lightweight database that supports only a subset of the SQL syntax. Microsoft Access supports only a subset as well, but a larger one. The real problem is that the two databases don't always support the same subset.










    I l@ve RuBoard



    Posted by at

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)