Chapter 17. SQL and JDBC

CONTENTS

•  A Quick and Dirty JDBC Session
•  Programming with JDBC and SQL
•  SQL Data Definition Language
•  SQL Data Manipulation Language
•  Putting It All Together Adding Database Support to the Address Book Application
•  Summary

Terms in This Chapter

• ANSI SQL

• autoexec.bat file

• Class path

• Database

• Database-aware dictionary

• Data Definition Language

• Data Manipulation Language

• Dirty flag

• Foreign key

• Garbage collector

• Java Virtual Machine

• JDBC

• Main block

• Primary key

• RDBMS

• Referential integrity

• Setter method

• Structured Query Language

• try except/try finally block

• WinZip

One of the advantages of storing data in a database is that other applications can readily access it. Another advantage is that the database takes care of indexing files and such, making data access easier and faster.

Databases are a huge topic, one that we can barely scratch the surface of. That said, let me tell you what this chapter won't cover: data normalization theory or any other database-specific topic. What it will cover is the following, which should be enough SQL for you to use JDBC with a database:

• Basic Structured Query Language (SQL)

• Rudimentary Data Definition Language (DDL)

• Connecting to a database with Java Database Connectivity (JDBC)

• Inserting, accessing, deleting, and updating data with JDBC

To get you up and running with the JDBC API, we'll do a quick interactive session. Then we'll get into SQL and DDL and finally add database support to our address book application.

For More Information

A good book on JDBC is JDBC Database Access with Java : A Tutorial and Annotated Reference (Hamilton, Cattell, and Fisher, 1997 Addison-Wesley). Also good is the free online documentation at the Javasoft Web site, java.sun.com.

A Quick and Dirty JDBC Session

Naturally I don't know what database you have access to, so on this book's Web site I'm using two standards: Microsoft Access and Java InstantDB. The exercises should work with any SQL database, as long as it has JDBC support. You may have to tweak the statements a little, but after this chapter you should feel comfortable with that.

For Microsoft Access we're going to use the JDBC ODBC bridge, which integrates ODBC-compliant databases with Java applications. (ODBC stands for Open Database Connectivity.) Keep in mind that there are different levels of compliance. These levels range from bridges to native client support to pure Java. I picked Access because it's ODBC compliant and because you're likely to have at least one such database on your computer.

Setting Up the ODBC Driver

If you're using Windows and ODBC, follow the instructions below. If you're using a non-Windows OS or a pure Java JDBC driver, follow its documentation.

1. Open the Windows' control panel, and double-click the ODBC icon.

2. Select the system DSN tab, and hit the Add button.

3. Select Microsoft Access from the list, or any database you want to use (see Figure 17-1), and hit the Finish button.

   Figure 17-1. The Database List

   

4. Type db_dev for the data source (see Figure 17-2).

   Figure 17-2. Microsoft Access Setup

   

5. Hit the Create button, and create a database as c:\db_dev\db_dev.mdb (see Figure 17-3).

   Figure 17-3. Creating a New Database

   

If you're using another database that's compliant with ODBC, the steps and the screen shots may vary, but the ideas are the same. If my instructions don't work, get some online help for the database you've chosen. Also, if you have problems with the system DSN, try creating a user DSN.

Setting up InstantDB

Whatever database you use, I recommend installing and using InstantDB, the one I use for all of the chapter examples. It's extremely easy, and it's open source (that means free). Here's how to set it up:

1. Create a directory on your hard drive, and name it C:\InstantDB.

2. Go to the Web site and navigate to the Resources\InstantDB folder.

3. Unzip the file idb.zip into the new directory using WinZip or an equivalent (get a copy of WinZip at the WinZip Web site).

4. Open C:\InstantDB\DOC\index.html.

5. On the left side (the left frame), select the Basic link.

6. Select Installation under the Basic link.

7. Follow the installation instructions, which tell you to add the Classes/idb.jar, Classes/idbf.jar, and Classes/idbexmpl.jar files to your class path. An easy way to do this, if you're using Windows, is to add the following lines to your autoexec.bat file:

   SET CLASSPATH=%CLASSPATH%;C:\InstantDB\Classes\idb.jar SET CLASSPATH=%CLASSPATH%;C:\InstantDB\Classes\idbf.jar SET CLASSPATH=%CLASSPATH%;C:\InstantDB\Classes\idbexmpl.jar

8. Configure the environment variable.

9. Run the sample application by navigating to C:\InstantDB\Examples> and entering javac sample.java at the prompt and then java sample. The DOS prompt session may look like this:

   C:\InstantDB\Examples>c:\autoexec.bat C:\InstantDB\Examples>javac sample.java C:\InstantDB\Examples>java sample

If you're going to use InstantDB for the first interactive session, be sure you have a copy of db_dev.prp in your working directory. If you don't, copy it from the scripts\chap17\InstantDB folder into the working directory.

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.

SQL Data Definition Language

SQL's Data Definition Language (DDL) is for working with tables and indexes. A table defines a set of columns for different fields of data and is similar to a class in Python. Columns are like class properties or variables; rows are like class instances.

DDL has five statements, which we'll look at in turn in the following sections.

Create Table

The CREATE TABLE statement defines the table. It has the following form:

CREATE TABLE table (field_name sql_type [(size)] [NOT NULL] [index1] , ...)

where

• field_name is the name of the field being defined

• sql_type is a valid SQL type

• size is the size of the field

• NOT NULL specifies that the column can't be empty or equal to None

The brackets in the statement denote optional parameters.

Here's how to create a table named Employee:

CREATE TABLE Employee (        EmpID        INT    NOT NULL,        Name         CHAR(20),        DeptID       INT    NOT NULL,

CHAR denotes a character, so CHAR(20)denotes a 20-character field.

Create Index

Indexing a table speeds data access. You create a table index with the CREATE INDEX statement, which has the form

CREATE INDEX index ON table (field, field, ...)

This is how to add an index to our Employee table:

CREATE INDEX SalaryIndex ON Employee (Salary)

Alter Table

You modify a table with the ALTER TABLE statement, which has the form

ALTER TABLE table ADD COLUMN field type[(size)] [NOT NULL]

To add an extra column to the Employee table, do this:

ALTER TABLE  Employee ADD COLUMN   Phone  CHAR(20)     NOT NULL

To drop a column do this:

ALTER TABLE table DROP COLUMN field

To drop a field in the column just added do this:

ALTER TABLE table DROP COLUMN field

Constraint

With the CONSTRAINT clause you can mark the EmpID column as the primary key. A primary key is a unique identifier that indexes the table and defines inter-table relationships. CONSTRAINT has the form

CREATE TABLE Employee (        EmpID       INT    NOT NULL,        Name        CHAR(20),        DeptID      INT    NOT NULL,        Salary      INT,        CONSTRAINT EMP_PK_KEY     PRIMARY KEY (EmpID) )

Here's another, probably more common, way to define a primary key (the only method supported by InstantDB v.3.12):

CREATE TABLE Employee (        EmpID       INT    PRIMARY KEY,        Name        CHAR(20),        DeptID      INT    NOT NULL,        Salary      INT,        Phone CHAR(20)     NOT NULL )

To create a table that's linked to another table, you create a foreign key (the reference to another table) in one to point to a primary key in the other. As an example, we'll define a foreign key in the Employee table to point to the primary key in a table called Department.

Define the Department table.

CREATE TABLE Department (        DeptID      INT    NOT NULL,        Name        CHAR(20),        CONSTRAINT DEPT_PK_KEY PRIMARY KEY (DeptID) )

Create the Employee table with a foreign key constraint that refers to the Department table's DeptID.

CREATE TABLE Employee (        EmpID       INT         NOT NULL,        Name        CHAR(20),        DeptID      INT         NOT NULL,        Salary      INT,        CONSTRAINT  EMP_PK_KEY  PRIMARY KEY (EmpID),        CONSTRAINT  DEPT_FK_KEY FOREIGN KEY (DeptID)                    REFERENCES Department (DeptID) )

The relationship created by the linking shows that each employee must be in a department.

No Foreign Keys or Referential Integrity in InstantDB

InstantDB doesn't support foreign keys or referential integrity. Microsoft Access does support these concepts, as do most SQL databases you're likely to run into.

Drop

The DROP statement has the form

DROP table_name DROP index on table

Here's how to use it to drop the Employee table, that is, delete it from the database:

DROP Employee

Obviously, the use of DROP requires caution.

Putting It All Together

Let's show DDL in action by running the Access/ddl.py module, which defines four DDL statement strings and then executes each one with the JDBC statement object's executeUpdate() method.

from java.sql import DriverManager from java.lang import Class Class.forName('sun.jdbc.odbc.JdbcOdbcDriver') url="jdbc:odbc:db_dev" connection=DriverManager.getConnection(url, "", "") statement = connection.createStatement() create_department = """       CREATE TABLE Department (             DeptID     INT   NOT NULL,             Name       CHAR(20),             CONSTRAINT DEPT_PK_KEY PRIMARY KEY (DeptID)       ) """ create_employee = """       CREATE TABLE Employee (             EmpID       INT   NOT NULL,             Name        CHAR(20),             DeptID      INT   NOT NULL,             Salary      INT,             CONSTRAINT EMP_PK_KEY   PRIMARY KEY (EmpID),             CONSTRAINT DEPT_FK_KEY FOREIGN KEY (DeptID)                        REFERENCES Department (DeptID)       ) """ alter_table = """       ALTER TABLE     Employee       ADD COLUMN  Phone     CHAR(20)     NOT NULL """ add_index = """       CREATE INDEX SalaryIndex ON Employee (Salary) """ statement.executeUpdate(create_department) raw_input("Department Table Defined   hit enter to continue") statement.executeUpdate(create_employee) raw_input("Employee Table Defined   hit enter to continue") statement.executeUpdate(alter_table) raw_input("Employee table altered by adding a Phone column.") statement.executeUpdate(add_index) raw_input("Adding a salary index to the Employee table.") connection.close()

InstantDB: A Little Different

InstantDB v.3.12 doesn't support the CONSTRAINT clause or foreign keys, nor can it handle the ALTER TABLE statement, even though the documentation says otherwise. Here's its version of ddl.py (InstantDB/ddl.py):

from java.sql import DriverManager from java.lang import Class Class.forName("jdbc.idbDriver") url = "jdbc:idb=db_dev.prp" connection=DriverManager.getConnection(url, "", "") statement = connection.createStatement() create_department = """        CREATE TABLE Department (               DeptID       INT    PRIMARY KEY,               Name         CHAR(20),        ) """ create_employee = """        CREATE TABLE Employee (               EmpID        INT    PRIMARY KEY,               Name         CHAR(20),               DeptID       INT    NOT NULL,               Salary       INT,               Phone CHAR(20)      NOT NULL        ) """ #Note that InstantDB does not understand foreign keys... # "No understanding of foreign keys or referential integrity checking " ##    REMOVED:      CONSTRAINT DEPT_FK_KEY FOREIGN KEY (DeptID) ##                        REFERENCES Department (DeptID) add_index = """        CREATE INDEX SalaryIndex ON Employee (Salary) """ statement.executeUpdate(create_department) raw_input("Department Table Defined   hit enter to continue") statement.executeUpdate(create_employee) raw_input("Employee Table Defined   hit enter to continue") #We are not able to alter the table once created log bug with InstantDB folks. #statement.executeUpdate(alter_table) #raw_input("Employee table altered by adding a Phone column   hit enter to continue") statement.executeUpdate(add_index) raw_input("Adding a salary index to the Employee table - hit enter to    continue") connection.close()

Try these exercises:

• Run ddl.py in both the Access and InstantDB directories. Note the differences.

• Run ddl.py with your RDBMS system and JDBC driver, and note the differences.

SQL Data Types

We've defined a few simple tables with the ddl.py module. Now let's look at the different fields that can show up in a table. Until now, we've been using only two SQL data types, CHAR and INT. Table 17-1 lists many more, most of which are similar to those in Java and Python. If you want to know what each type holds, an SQL reference can tell you.

Table 17-1. JDBC, Java, and Python Types

JDBC	Java	Python
CHAR	String	String
VARCHAR	String	String
LONGVARCHAR	String	String
NUMERIC	java.math.BigDecimal	java.math.BigDecimal
DECIMAL	java.math.BigDecimal	java.math.BigDecimal
BIT	boolean	Integer
TINYINT	byte	Integer
SMALLINT	short	Integer
INTEGER	int	Integer
BIGINT	long	Integer
REAL	float	Float
FLOAT	double	Float
DOUBLE	double	Float
BINARY	byte[]	jarray as byte[](sequence)
VARBINARY	byte[]	jarray as byte[](sequence)
LONGVARBINARY	byte[]	jarray as byte[](sequence)
DATE	java.sql.Date	java.sql.Date
TIME	java.sql.Time	java.sql.Time
TIMESTAMP	java.sql.Timestamp	java.sql.Timestamp

Notice that the left column in the table is headed JDBC, not SQL. One reason for this is that SQL doesn't have a LONGVARCHAR. In fact, each database vendor seems to have its own name for this type, so there's no one-to-one mapping between JDBC and SQL (but what there is is close enough).

Creating Fields with DDL

Let's illustrate how DDL creates fields of each type first in Microsoft Access (Access\DDL2.py).

from java.sql import DriverManager from java.lang import Class Class.forName('sun.jdbc.odbc.JdbcOdbcDriver') url="jdbc:odbc:db_dev" connection=DriverManager.getConnection(url, "", "") statement = connection.createStatement() create_type_table1 = """       CREATE TABLE TypeTable1 (              _bit         BIT,              _int         INT,              _smallint    SMALLINT,              _integer     INTEGER,              _numeric     NUMERIC,              _char        CHAR(200),              _varchar     VARCHAR(200),              _real        REAL,              _float       FLOAT,              _double      DOUBLE,              _binary      BINARY(200),              _varbinary   VARBINARY(200),              _date        DATE,              _time        TIME,              _timestamp   TIMESTAMP       ) """ statement.executeUpdate(create_type_table1) 

The statement defined here creates every SQL type that MSAccess supports:

create_type_table1 = """       CREATE TABLE TypeTable1 (              _bit         BIT,              _int         INT,              _smallint    SMALLINT,              _integer     INTEGER,              _numeric     NUMERIC,              _char        CHAR(200),              _varchar     VARCHAR(200),              _real        REAL,              _float       FLOAT,              _double      DOUBLE,              _binary      BINARY(200),              _varbinary   VARBINARY(200),              _date        DATE,              _time        TIME,              _timestamp   TIMESTAMP       ) """

JDBC's LONGVARCHAR and LONGVARBINARY aren't supported by standard SQL. Again, each vendor has its own names for these types. MSAccess supports them under the names LONGCHAR and LONGBINARY and creates tables with them.

create_type_table2 = """       CREATE TABLE TypeTable2 (              _longvarbinary     LONGBINARY,              _longvarchar LONGCHAR       ) """ statement.executeUpdate(create_type_table2) raw_input("Create a type table 2") 

There are three standard types not supported by MSAccess. In fact, if you try to run any code with them, JDBC will throw an exception. Here's an example (from ddl2.py):

create_type_table3 = """       CREATE TABLE TypeTable2 (              _decimal     DECIMAL,              _tinyint            TINYINT,              _bigint             BIGINT       ) """ statement.executeUpdate(create_type_table3)

Notice that the third table, type_table3, isn't created. Here's Access/ddl2.py in its entirety:

from java.sql import DriverManager from java.lang import Class Class.forName('sun.jdbc.odbc.JdbcOdbcDriver') url="jdbc:odbc:db_dev" connection=DriverManager.getConnection(url, "", "") statement = connection.createStatement() create_type_table1 = """       CREATE TABLE TypeTable1 (              _bit         BIT,              _int         INT,              _smallint    SMALLINT,              _integer     INTEGER,              _numeric     NUMERIC,              _char        CHAR(200),              _varchar     VARCHAR(200),              _real        REAL,              _float       FLOAT,              _double      DOUBLE,              _binary      BINARY(200),              _varbinary   VARBINARY(200),              _date        DATE,              _time        TIME,              _timestamp   TIMESTAMP       ) """ create_type_table2 = """       CREATE TABLE TypeTable2 (              _longvarbinary     LONGBINARY,              _longvarchar LONGCHAR      ) """ create_type_table3 = """       CREATE TABLE TypeTable2 (              _decimal     DECIMAL,              _tinyint     TINYINT,              _bigint      BIGINT       ) """ statement.executeUpdate(create_type_table1) raw_input("Create a type table 1") statement.executeUpdate(create_type_table2) raw_input("Create a type table 2") statement.executeUpdate(create_type_table3) raw_input("Create a type table 3") connection.close()

InstantDB Type Support

InstantDB supports fewer types than MSAccess does. In the following code (InstantDB/ddl2.py), the types it does support are shown in type_table1. Those it supports but for which it has no standard SQL names are shown in type_table2.

from java.sql import DriverManager from java.lang import Class Class.forName("jdbc.idbDriver") url = "jdbc:idb=db_dev.prp" connection=DriverManager.getConnection(url, "", "") statement = connection.createStatement() create_type_table1 = """        CREATE TABLE TypeTable1 (               _tinyint     TINYINT,               _smallint    SMALLINT,               _integer     INTEGER,               _int         INT,               _numeric     NUMERIC,               _decimal     DECIMAL,               _char        CHAR(200),               _varchar     VARCHAR(200),               _float       FLOAT,               _double      DOUBLE,               _binary      BINARY(200),               _varbinary   VARBINARY(200),               _date        DATE,        ) """ create_type_table2 = """        CREATE TABLE TypeTable2 (               _longvarbinary     LONGVARBINARY,        ) """ create_type_table3 = """        CREATE TABLE TypeTable2 (               _real        REAL,               _bit         BIT,               _time        TIME,               _timestamp   TIMESTAMP,               _bigint      BIGINT,               _longvarchar LONGVARCHAR        ) """ statement.executeUpdate(create_type_table1) raw_input("Create a type table 1") statement.executeUpdate(create_type_table2) raw_input("Create a type table 2") statement.executeUpdate(create_type_table3) raw_input("Create a type table 3") connection.close()

Strangely, the types not supported by InstantDB aren't the ones not supported by MSAccess. In other words, one unsupported list isn't a subset of another unsupported list. Fortunately, the most common SQL types are supported by both.

Try these exercises:

• Study and run either the MSAccess or the InstantDB version of ddl2.py, depending on the database type you're working with. Note the differences and similarities.

• Locate the JDBC drivers to the RDBMS system you use regularly. Create a database, run the dml2.sql scripts against it, and make changes as necessary. Note the differences and similarities. Let me know your results via email. I'd like a comparison for each type of database (DBw, DBASE, Paradox, Oracle, MySQL, etc.). State the version number and complete product name along with the JDBC driver information.

SQL Data Manipulation Language

SQL's Data Manipulation Language (DML) defines the format of data by insertion, selection, updating, and deletion. The four primary DML statements are INSERT, SELECT, UPDATE, and DELETE, which we'll look at individually in the following sections.

The very first exercise in this chapter illustrated all of these statements. Here we'll see them at work in a little more advanced database structure, continuing with our Employee and Department table examples.

Insert

If tables are like classes in Python, then rows are like instances. Since the INSERT statement inserts values into a table, we can think of it as analogous to calling a constructor, that is, creating an instance of a record.

INSERT takes the following form:

INSERT INTO table_name         (field_name1, field_name2, ...) VALUES         (value1, value2, ...)

Here it's adding values to the Employee table:

INSERT INTO         Employee         (EmpID, Name, DeptID, Salary) VALUES         (10507, 'Bob Jones', '1', 10)

Select

The SELECT statement selects rows from a table. We've already used it, so I won't belabor it at this point. Here's its form:

SELECT       table.field [as alias] FROM       table [as alias] [WHERE. criteria] [GROUP BY table.field, ...] [HAVING criteria] [ORDER BY table.field, ...]

The ORDER BY clause sorts the query results. The GROUP BY and HAVING clauses group items for aggregate functions, such as sum.

Here's a SELECT example:

SELECT       Name, Salary FROM Employee WHERE       (Salary > 10 )       and       (DeptID = 10) ORDER BY Name

which can be rewritten as

SELECT Name, Salary FROM Employee WHERE (Salary > 10 ) and (DeptID == 10) ORDER BY Name

With SELECT you can join tables to get columns from more than one table at a time. When you do this, you'll probably want to use an alias because the tables to be joined may have the same fields; aliasing allows such fields to be used in the WHERE clause.

The next example gets the employee's name, salary, and department. Since the department name is stored in the Department table, we have to join Department and Employee for the statement to work.

SELECT       e.Name as emp_name,       e.Salary as salary,       d.Name as dept_name FROM       Employee as e,       Department as d WHERE       (e.DeptID = d.DeptID) and       (e.Salary > 10 )     and       (e.DeptID = 10)

The first part of the WHERE clause specifies how the tables are to be joined. It defines our Employee and Department tables with the respective aliases e and d because both tables have DeptID and Name fields.

WHERE       (e.DeptID = d.DeptID) and

Without the aliases, we can't differentiate a department name and an employee name. Three field aliases are also defined: emp_name, salary, and dept_name. If we were to run this query via JDBC, we'd have to use the aliases for the columns returned, not their names, in order to access the results.

Notice the as in the first line of the SELECT statement (e.Name as emp_name). It's nice to have this syntax, although many programmers don't use it. Because it's optional, the following statement is equivalent to the preceeding one:

SELECT       e.Name              emp_name,       e.Salary    salary,       d.Name      dept_name FROM       Employee    e,       Department  d WHERE      (e.DeptID = d.DeptID) and      (e.Salary > 10 )     and      (e.DeptID = 10)

I like using as.

The ANSI SQL JOIN Clause

If the database has a later version of ANSI SQL installed, you can use the JOIN clause to join tables.

SELECT       e.Name as emp_name,       e.Salary as salary,       d.Name as dept_name FROM       Employee as e JOIN       Department as d              on e.DeptID = d.DeptID WHERE       (e.Salary > 10 )  and       (e.DeptID == 10)

Because it makes the syntax is more modular and easier to read (it separates the WHERE clause from the rest of the statement), I suggest you use JOIN if you have the choice.

Here's the last statement with the as syntax:

SELECT       e.Name            emp_name,       e.Salary    salary,       d.Name      dept_name FROM       Employee    e JOIN       Department  d              on e.DeptID = d.DeptID WHERE        (e.Salary > 10 )  and       (e.DeptID = 10)

 

INNER Join

Most databases assume an INNER JOIN if the JOIN clause is left by itself. However, MSAccess makes no such assumption and expects you to specify an INNER JOIN like this:

SELECT       e.Name              as emp_name,       e.Salary     as salary,       d.Name       as dept_name FROM       Employee     as e INNER JOIN       Department   as d             on e.DeptID = d.DeptID WHERE       (e.Salary > 10 )  and      (e.DeptID = 100)

The four types of JOIN are INNER, OUTER, LEFT, and RIGHT. We're going to cover only INNER JOINs, which are probably the most common.

By the way, you can join more than one table at a time by adding extra JOIN clauses. You can find the exact syntax in a good SQL reference.

Update

The UPDATE statement modifies the fields in a row. Its Python equivalent is setting an attribute of a class instance. UPDATE takes the form

UPDATE       table_name SET       field1=value1, field2=value2, ... [WHERE clause]

Here's an example of using UPDATE to give an employee a raise:

UPDATE       Employee SET       Salary=11 WHERE       EmpID = 10507

Delete

The DELETE statement removes records from a table. Deleting a record is like setting all object references to None (forcing the garbage collector to delete the object). DELETE takes the form

DELETE FROM       table_name [WHERE clause]

Deleting an employee record looks like this:

DELETE FROM       Employee WHERE       EmpID = 10507

Laying off an entire department looks like this:

DELETE FROM       Employee WHERE       DeptID = 100

Using DML

To show how DML works, we'll use it to insert some departments into our Department table. Define a Python format string.

d_insert = """       INSERT INTO             Department             (DeptID, Name)       VALUES             (%d, '%s') """

Define a set of tuples, departments, for sample departments.

departments = (        (100, "Engineering"),        (200, "Marketing"),        (300, "Human Resources"),        (400, "Manufacturing") ) #End of departments

Iterate through departments, and create rows in the Department table.

for dept in departments:       insert = d_insert % dept       print insert       statement.executeUpdate(insert)

Now we'll add employees to the Employee table. Define an insert format string.

e_insert = """       INSERT INTO       Employee              (EmpID, Name, DeptID, Salary, Phone)       VALUES              (%d, '%s', %d, %d, '%s') """

Define a tuple with some employees row data.

employees = (        (1000, "Rick Hightower", 100, 5, "555-1212"),        (1001, "Ricky Martin",   200, 10, "555-1213"),        (1002, "Adam Carr",      100, 6, "555-1214"),        (1003, "Bob Deanna",     100, 15, "555-1215"),        (1004, "Tim Simeonov",   100, 16, "555-1216"),        (1005, "Scott Faurbach", 100, 14, "555-1217"),        (1006, "Mary Basset",    300, 5, "555-1218"),        (1007, "Missy Carr",     400, 5, "555-1219"),        (1008, "Paul Ganz",      400, 5, "555-1220"),        (1009, "Tom Jones",      400, 5, "555-1221"),        (1010, "Martha Pena",    200, 25, "555-1222"),        (1011, "John Souza",     200, 15, "555-1223") ) #End of employees

Add the data in the employees tuple to the Employee table.

for emp in employees:       insert = e_insert % emp       print insert       statement.executeUpdate(insert)

Try these exercises:

• Study the code in access\dml_insert.py and access\connect.py; run access\dml_insert.py.

• Study the code in instantDB\dml_insert.py and instantDB\connect.py; run InstantDB\dml_insert.py.

The connect.py file varies depending on whether you connect to an ODBC database (such as MSAccess) or a pure Java database such as InstantDB. Here are both versions:

Access\connect.py:

from java.sql import DriverManager from java.lang import Class Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") url = "jdbc:odbc:db_dev" connection=DriverManager.getConnection(url, "", "") statement = connection.createStatement()

InstantDB\connect.py:

from java.sql import DriverManager from java.lang import Class Class.forName("jdbc.idbDriver") url = "jdbc:idb=db_dev.prp" connection=DriverManager.getConnection(url, "", "") statement = connection.createStatement()

Inserting data is the same in InstantDB and MSAccess, so the following code (dml_insert.py) applies to both. (The JDBC-specific code is highlighted in bold.)

from connect import *      # imports connection and statement object d_insert = """       INSERT INTO              Department              (DeptID, Name)       VALUES           (%d, '%s') """ e_insert = """       INSERT INTO              Employee              (EmpID, Name, DeptID, Salary, Phone)       VALUES              (%d, '%s', %d, %d, '%s') """ departments = (       (100, "Engineering"),       (200, "Marketing"),       (300, "Human Resources"),       (400, "Manufacturing") ) #End of departments employees = (       (1000, "Rick Hightower", 100, 5, "555-1212"),       (1001, "Ricky Martin",   200, 10, "555-1213"),       (1002, "Adam Carr",      100, 6, "555-1214"),       (1003, "Bob Deanna",     100, 15, "555-1215"),       (1004, "Tim Sieonov",    100, 16, "555-1216"),       (1005, "Scott Faurbach", 100, 14, "555-1217"),       (1006, "Mary Basset",    300, 5, "555-1218"),       (1007, "Missy Carr",     400, 5, "555-1219"),       (1008, "Paul Ganz",      400, 5, "555-1220"),       (1009, "Tom Jones",      400, 5, "555-1221"),       (1010, "Martha Pena",    200, 25, "555-1222"),       (1011, "John Souza",     200, 15, "555-1223") ) #End of employees hit = "  - hit enter to continue  - " raw_input ("testing inserts" + hit) raw_input("Adding departments" + hit) for dept in departments:       insert = d_insert % dept       print insert       statement.executeUpdate(insert) raw_input("Adding employees" + hit) for emp in employees:       insert = e_insert % emp       print insert       statement.executeUpdate(insert) raw_input("testing select" + hit) connection.close()

Notice that the first statement imports all of the objects from the connect.py module. This is so the module will hold all of the code needed for connecting. To connect to another database, just modify connect.py accordingly.

Using SELECT (with Multiple JOIN Statements)

Let's use a SELECT statement to join tables. The code that follows (Access\dml_select.py) shows a multi-table join with MSAccess.

from connect import * select = """        SELECT               e.Name        as emp_name,               e.Salary      as salary,               d.Name        as dept_name        FROM               Employee      as e        INNER JOIN               Department    as d                     on e.DeptID = d.DeptID        WHERE               (e.Salary > 10 )  and               (e.DeptID = 100) """ results = statement.executeQuery(select) emp_name="emp_name" salary="salary" dept_name="dept_name" format = "%(emp_name)s,\t\t%(dept_name)s,\t\t%(salary)3.2f" while results.next():        dict = {}        dict[emp_name]       =     results.getString(emp_name)        dict[salary]         =     results.getInt(salary)        dict[dept_name]      =     results.getString(dept_name)        print format % dict connection.close()

Here's the InstantDB version of a multi-table SELECT:

from connect import * select = """        SELECT               e.Name        as emp_name,               e.Salary      as salary,               d.Name        as dept_name        FROM               Employee      as e        JOIN               Department    as d                     on e.DeptID = d.DeptID        WHERE               (e.Salary > 10 ) and               (e.DeptID = 100) """ results = statement.executeQuery(select) ... ... (The rest is the same as before)

The two versions are nearly identical except that the JOIN's INNER modifier has been left off in the InstantDB code because it's assumed. (According to the documentation, JOIN should work when the modifier is supplied.) Also, I used the executeQuery() method of the statement object for the SELECT statement, whereas I used the executeUpdate() method for the INSERT statement. Always use the former for SELECTs and the latter for just about everything else updates, inserts, deletes, and any DDL operations. executeQuery() returns a result set for the query, and executeUpdate() returns the number of rows affected by any table modifications.

As an exercise, run the dml_select module for one or both of the databases (ODBC or pure Java). Try each of the following SELECT statements. Determine which ones work as expected and which ones don't.

No INNER modifer:

SELECT        e.Name       as emp_name,        e.Salary     as salary,        d.Name       as dept_name FROM        Employee     as e JOIN        Department   as d              on e.DeptID = d.DeptID WHERE        (e.Salary > 10 ) and        (e.DeptID = 100)

INNER modifier with as syntax for aliases:

SELECT        e.Name       emp_name,        e.Salary     salary,        d.Name       dept_name FROM        Employee     e INNER JOIN        Department   d              on e.DeptID = d.DeptID WHERE        (e.Salary > 10 ) and        (e.DeptID = 100)

INNER modifier without as syntax:

SELECT        e.Name       emp_name,        e.Salary     salary,        d.Name       dept_name FROM        Employee     e INNER JOIN        Department   d              on e.DeptID = d.DeptID WHERE        (e.Salary > 10 ) and (e.DeptID = 100)

Old-style JOIN (no JOIN clause):

SELECT        e.Name       emp_name,        e.Salary     salary,        d.Name       dept_name FROM        Employee     e,        Department   d WHERE        (e.DeptID = d.DeptID)   and        (e.Salary > 10 )        and        (e.DeptID = 100)

See if you can run these forms on both MSAccess and InstantDB. Then run them against your own database/JDBC driver pair.

Using DELETE

The following code (dml_delete.py) deletes the marketing department from the Department table. It's the same for MSAccess and InstantDB.

from connect import *     #import statement and connection objects. from java.lang import Exception marketingID = 200 delete = "delete from Department where DeptID = %d" % marketingID        # Try to delete the record.        # Close connection no matter what! try:               # Try to delete the record.               # If an exception occurs               # print out the exception's message.        try:               statement.executeUpdate(delete)        except Exception, e:               print "Unable to delete marketing"+e.message finally:        connection.close()

Notice the try...except and try...finally blocks. try...finally makes sure the connection is closed if there's an exception. try...except prints out the exception message. It's a good idea to wrap database calls in a try...finally block to ensure a closed connection.

Referential Integrity

As an exercise, see if you can run dml_delete.py against both InstantDB and MSAccess. You'll notice that it doesn't work with MSAccess but does work with InstantDB. MSAccess's behavior is preferred. Why?

Because the operation should fail to preserve referential integrity. Essentially, if you delete the department record and there are still employees in that department, your multiple JOIN SELECT statements will stop working. If that happens, you'll get this message:

Unable to delete marketing [Microsoft][ODBC Microsoft Access 97 Driver]. The record cannot be deleted or changed because table 'Employee' includes related records.

which means that, as long as the Employee table contains records for marketing employees, you can't delete the marketing record in the Department table. This is how MSAccess maintains its referential integrity.

If you're a little confused, the following exercises should clear things up:

• Study and run dml_delete.py in both the MSAccess and InstantDB directories.

• Study and run dml_select2.py in both. This module selects a group of employees who are in the marketing department, i.e., whose DeptID is equal to 200. What happens when you run this query against the InstantDB database? Against the MSAccess database? Which of the two exhibits the correct behavior?

• Write a query or a set of queries that will work with either MSAccess or InstantDB after dml_delete is run on both.

InstantDB/MSAccess Support for Referential Integrity

Because it's a lightweight, InstantDB makes no claim of supporting referential integrity. I don't consider MSAccess a heavyweight, but it does provide some referential integrity support, as we've just seen.

For the DELETE statement to work, we need to move every employee in marketing to a new department that is, change their DeptID to one for an existing department. An alternative is to delete them from the Employee table first (that is, lay them all off).

Achieving Referential Integrity with UPDATE and DELETE

What follows is an example that lays off some workers and moves others to a different department via the UPDATE and DELETE statements The code, dml_delete2.py, is the same for both MSAccess and InstantDB. (Be sure to read the comments. They tell the whole story.)

from connect import * from java.lang import Exception marketingID = 200 mfgID = 400 engID = 100       # Move employees whose salary is less       # than 10 and greater than 8       # to manufacturing. update1 = """       update             Employee       set             DeptID=%d       where             salary > 8 and salary <= 10 """ % mfgID       # Layoff employees whose salary       # is greater than 16.       # delete1 = """       delete from             Employee       where             salary > 16 """       # Move employees whose salary is less       # than 16 and greater than 10       # to engineering. Also adjust their salary to 10. update2 = """       update             Employee       set             DeptID=%d, salary=10       where             salary < 16 and salary > 10 """ % engID       # Remove the Marketing department       # from the Department table.       # It should work this time! delete2 = "delete from Department where DeptID = %d" % marketingID       #Try to delete the record.       #Close connection no matter what! try:             # Try to delete the record.             # If an exception occurs             # print out the exception's message.       try:             statement.executeUpdate(update1)             statement.executeUpdate(update2)             statement.executeUpdate(delete1)             statement.executeUpdate(delete2)       except Exception, e:             print "Unable to delete marketing" + e.getMessage() finally:       connection.close() 

As an exercise, run dml_delete2.py in both MSAccess and InstantDB. Use either the Microsoft Access GUI or the SQLBuilder GUI to look at the data. (For SQLBuilder, read the InstantDB documentation; also read the following sidebar.)

Working with SQLBuilder

Here's how to work with SQLBuilder:

1. Compile it.

   C:\InstantDB\Examples\SQLBuilder>javac *.java

2. Run it.

   C:\InstantDB\Examples\SQLBuilder>java SQLBuilder

3. Hit the Browse button, and select the appropriate database property file for example: C:\JPython_book\scripts\chap17\InstantDB\db_dev.prp.

4. Hit the Connect button.

5. Go to the Query tab.

6. Select the Employee table from the Tables dropdown list.

7. Hit the Submit button.

8. Repeat steps 6 and 7 for the Department table.

Putting It All Together Adding Database Support to the Address Book Application

Now we're ready to add database support to our address book application. First, though, let's review the application's main modules.

• Address.py contains the Address class, which represents an address entry, and the readAddresses()and writeAddresses() functions, which read and write a dictionary of Address class instances (the actual addresses).

• AddressModel.py contains the AddressModel class, which is both a list model and a table model for the user interface. AddressModel calls readAddresses() to read in a dictionary of Address class instances and maps the dictionary to the list and table models.

• AddressFormPane.py contains the AddressFormPane class, a user interface for editing address entries.

• AddressMain.py contains the AddressMain class the main frame. AddressMain manages the interaction of AddressModel's components and holds an instance of JTable, an instance of JList, and an instance of JTabbedPane (all of which are from javax.swing).

Making the Application More Modular

We've done a good job of keeping the application modular, but we can do better. First, we're going to abstract out the differences between a flat-file model and a database model. Then we're going to add database support with as few changes as possible to AddressModel.py.

What we'll do is move the file-specific dependencies from AddressModel's constructor and put readAddresses() and writeAddresses() in a module called AddressFileUtils.py. All modified and new files will be in a directory called AddressBook.2. Here's the original AddressModel constructor:

class AddressModel(AbstractTableModel, ListModel):        """The AddressModel is both a ListModel           and a TableModel."""        def __init__(self):              """Initialize the Address model.              Read the dictionary from the file."""                     # holds the dictionary of addresses.              self.dict = None                     # holds the sorted list of names,                     # which are keys into the dictionary.              self.list = None                     # to hold list of ListModelListeners                     self.listeners = []                     # Read the addresses from the file.                     # holds the file name that                     # holds the addresses              self.fname=".\\addr.dat"        self.dict = readAddresses(self.fname)                     # Store the sorted list of names.              self.list = self.dict.keys()              self.list.sort() ... ... def writeAddresses(self):        """Write the address data to the file."""        writeAddresses(self.fname, self.dict)

Here is the constructor as modified:

       ... class AddressModel(AbstractTableModel, ListModel):        """The AddressModel is both a ListModel           and a TableModel."""        def __init__(self):              """Initialize the Address model.              Read the dictionary from the file."""                     #holds the dictionary of addresses.              self.dict = None                     #holds the sorted list of names,                     # which are keys into the dictionary              self.list = None                     #to hold list of ListModelListeners              self.listeners = []                     # Read the addresses                     # from the Address utility.              self.dict = readAddresses()        ...        ... def writeAddresses(self):              """Write the address data to the file."""              writeAddresses(self.dict)

The following are the changes we made to readAddresses() and writeAddresses(), which are now in their own module, AddressFileUtils.py. Notice that both functions have a default value for filename that they didn't have before.

... def readAddresses(filename=".\\addr.dat"):       ... def writeAddresses(dict, filename=".\\addr.dat", bin=0):       ...

The result, essentially, is that all file-based functionality has been encapsulated out of AddressModel. The next step is testing.

How Modular Is Modular Enough?

The sad fact is that we don't have all the time in the world to tinker with our address book application. In the real world, customers want a working product as quickly as possible, even if it's less than perfect. If we strive for perfection we'll never get it.

That said, I think our code at this point is modular enough.

As an exercise, review the changes in the address book application and then run the new version. Remember that AddressMain.py is the main module, so to open the application you have to type JPython AddressMain at the system prompt. As a first step, we need to create a utility module, AddressDBUtil.py, which will

• Allow configuration of JDBC drivers and URLs

• Create the necessary tables

• Copy data from the flat file to the database

• Hold the readAddresses() and writeAddresses() functions

As a second step, we have to modify AddressModel to work with AddressDBUtils.

Working with Java Properties

In order to configure the necessary JDBC drivers and URLs, we have to use java.utils.Properties. This class writes properties out to a text file so that a user can manually edit text with a text editor. Here's an example.

Import the necessary classes.

>>> from java.util import Properties >>> from java.io import FileOutputStream, FileInputStream

Create a file output stream.

>>> file = "addr_db.props" >>> file = FileOutputStream(file)

Create an instance of Properties.

>>> props = Properties()

Set the JdbcUrl and JdbcDriver properties.

>>> props.setProperty("JdbcUrl", "jdbc:idb=db_dev.prp") >>> props.setProperty("JdbcDriver", "jdbc.idbDriver")

Store the properties in a file (addr_db.props).

>>> props.store(file, "Database properties")

Look at the file.

#Database properties #Tue Dec 28 14:46:27 PST 1999 JdbcUrl=jdbc\:idb\=db_dev.prp JdbcDriver=jdbc.idbDriver

Notice the file format, which uses # for comments (as Python does). The properties are stored in name/value pairs, with each property occupying one line. This makes reading the properties as easy as writing them.

Now we have to neaten up the property and file objects.

>>> file.close() >>> file = None >>> props = None

Notice that props has been set to None to show that the properties aren't in memory anymore.

Next we read the properties from a file. Open a file input stream.

>>> file = "addr_db.props" >>> file = FileInputStream(file)

Create a Properties instance, and load the properties with the file input stream.

>>> props = Properties() >>> props.load(file)

Show the values of the JdbcUrl and JdbcDriver properties.

>>> print props.getProperty("JdbcUrl") jdbc:idb=db_dev.prp >>> print props.getProperty("JdbcDriver") jdbc.idbDriver

Adding Property File Support

Let's add a function to AddressDBUtils.py that uses java.utils.Properties to read the JDBC properties. Let's also add some error recovery and some bullet-proof functionality to check if the file exists and to create one if not.

As an exercise, try editing the property file from AddressDBUtils.py. Also try deleting the file and then running the module (don't forget to back up the file first). What happens?

Here's the latest version of AddressDBUtils.py:

from java.util import Properties from java.io import FileOutputStream, FileInputStream, IOException, File PROP_FILE = "addr_db.props"      # holds the property file for the db                                    connection info. __jdbc_url = None                # holds the JDBC URL __jdbc_driver = None             # holds the JDBC Driver       #Set some default values. __jdbc_url="jdbc\:idb\=db_dev.prp" __jdbc_driver="jdbc.idbDriver"       def __loadProps():             global PROP_FILE             stream = None     #holds file input stream             props = None      #holds Properties             file = None       #Hold file associated                               #with the PROP_FILE filename.                    #See if the file exists. If not create it.             file = File(PROP_FILE)             if not file.exists():                    setProps()             props = Properties()                    #Try to load the properties.                    # Create a file input stream.                    # Load the properties with input stream.             try:                    try:                       stream = FileInputStream(PROP_FILE)                       props.load(stream)                    except IOException, e:                       print "Problem loading db properties file=" + PROP_FILE                       print e.message                       e.printStackTrace()             finally:                       stream.close()             return props       def __storeProps(props):             global PROP_FILE             file = None #to hold the file input stream             file = FileOutputStream(PROP_FILE)                    # Try to create a file output stream,                    # and store the properties.             try:                    try:                       props.store(file, "Database properties")                    except IOException, e:                       print "Problem storing db properties. file=" + PROP_FILE                       print e.message                       e.printStackTrace()             finally:                    file.close()       def getProps():             global __jdbc_url, __jdbc_driver                    #Load the properties             props = __loadProps()             jdbc_url = props.getProperty("JdbcUrl", "None")             jdbc_driver = props.getProperty("JdbcDriver")                    # If the prop file did not have                    # values then set some default values.             if jdbc_url == "None":                    setProps()       else:                    __jdbc_url = jdbc_url                    __jdbc_driver = jdbc_driver       def setProps():             global __jdbc_url, __jdbc_driver                    #Create properties and then store them.             props = Properties()             props.setProperty("JdbcUrl", __jdbc_url)             props.setProperty("JdbcDriver", __jdbc_driver)             __storeProps(props)       def getJdbcDriver():             return __jdbc_driver       def getJdbcUrl():             return __jdbc_url       getProps()

Adding DDL Support

We need DDL support in order to create the table for our address book application. To get it we first import class Class to load the driver and class DriverManager to create a connection object.

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

Then we execute the CREATE TABLE statement, which is held in the create_table variable.

create_table = """       CREATE TABLE Address (              AddrID       INT     PRIMARY KEY,              Name         VARCHAR(40),              Phone        VARCHAR(15),              Email        VARCHAR(50)       ) """

The createTable() method uses the create_table string to create the Address table.

def createTable():       """        Creates the Address table.       """        global create_table        executeUpdate(create_table)

It can't load the driver or create the connection or statement object without the assistance of helper methods.

JDBC Helper Methods

Helper methods do common things such as execute an update, load a driver, and create connection and statement objects. They also do error checking and cleanup. The following code illustrates some helpers. (Pay particular attention to the document strings.)

def loadDriver():       """       Loads the driver class into the JVM.       Checks to see if the class is already loaded.       """       global __loaded_driver       jdbc = getJdbcDriver()       try:             if not __loaded_driver:                   Class.forName(jdbc)                   __loaded_driver=1       except:             error = "Unable to load the driver "+jdbc             print error             raise DBException(error) def createConnection():       """       Creates a database connection and returns it.       Loads the Database driver with loadDriver().       returns a JDBC Connection object.       """       connection = None       try:             loadDriver()             jdbc=getJdbcUrl()             connection=DriverManager.getConnection(jdbc)       except Exception, e:             print e.message             error = "Problem creating connection, exception."             print error             raise DBException(error)       if connection == None:                   error = "Problem creating connection was None"                   print error                   raise DBException(error)       return connection def createStatement():       """       Creates a statement object.       Returns a tuple containing a JDBC statement       and connection object.       """       connection = createConnection()       return connection.createStatement(), connection def executeUpdate(str):       """       Executes an update statement.       Returns the number of rows involved.       """       statement, connection = None, None       num_rows=-1       try:             try:                   statement,connection=createStatement()                   num_rows = statement.executeUpdate(str)             except Exception, e:                   e.printStackTrace()                   print e.message                   print "Problem executing update\n"+str       finally:             if(connection):connection.close()       return num_rows

Notice all of the error checking and recovery going on. Since we plan to execute more statements, it's good to have all such tasks handled in one place.

Creating the Address Table

All the preliminaries are out of the way, leaving only the execution of the CREATE TABLE statement. This is going to be easy. All it takes is one call to executeUpdate().

def createTable():       """       Creates the Address table.       """       global create_table       executeUpdate(create_table)

We want to run createTable() from the command line, so we need to add command-line parameter parsing in the main block (if__name__=="__main__" block), which checks to see if "c" is passed to the command line and, if so, creates the table. (Passing "p" creates the properties file.)

if __name__ == "__main__":       from sys import argv             #Process the command line arguments.       for arg in argv:             if arg == "c":                   getProps()                   str = "Creating the table using "                   str = str+getJdbcDriver()+" at "+ getJdbcUrl()                   print str                   createTable()             if arg == "p":                   print "Creating the properties file: "+PROP_FILE                   getProps()                   print getJdbcDriver()                   print getJdbcUrl()

Now we have our AddressDBUtils.py module. Here's how to create it in DOS:

C:\JPython_book\scripts\chap17\AddressBook3>JPython AddressDBUtils.py c

Try creating the Address table yourself as an exercise.

Inserting Address Records into the Address Table

To get addresses into the database, we have to transform an instance of the Address class into a record. Define the format string for the insert entries.

params = """AddrID, Name, Phone, Email""" insert = """        INSERT INTO              Address              (""" + params + """)        VALUES              (%s)        """

Define functions to create an INSERT statement.

def insertAddress(address, statement=None):        """        Inserts the AddrID, Name, Phone,        Email into a row.        """        id = getID()        a = address        tup = (              str(id),              formatSQLStr(a.name()),              formatSQLStr(a.phoneNumber()),              formatSQLStr(a.email())        )        values = join(tup, ",")        if statement:              executeUpdate(insert % values, statement)        else:              print insert % values

Invoke the helper function formatSQLStr() to transform the address information into a string that SQL understands.

def formatSQLStr(str):        """Formats a string into a SQL string"""        str = "'%s'" % str        return str

Now we'll add all of our address book data to the database (users like you to do this for them). For this we need the loadFromFile() function, which reads the address.dat file and stores a given address in the Address table.

def loadFromFile():        """        Loads the flat file of addresses into        the Address table.        """        from AddressFileUtils import readAddresses        dict = readAddresses()              # Create a statement for the database.              # Add all of the addresses to the table.        try:              statement, connection = createStatement()              # Iterate through the collection              # of addresses. Add each address to the              # database.        for address in dict.values():              insertAddress(address, statement)        finally:              connection.close()

Next we add the following statements to the main block so we can read loadFromFile() from the command line.

if __name__ == "__main__":        from sys import argv              #Process the command line arguments.              for arg in argv: ... ...              if arg == "l":                     loadFromFile()

To load the data from the file, we run AddressDBUtils.py from the command line with the "1" option. Here's the DOS prompt:

C:\JPython_book\scripts\chap17\AddressBook3>JPython AddressDBUtils.py l

Reading Addresses from the Address Table

Now we need to implement the readAddresses() and writeAddresses() functions. readAddresses() is easy we just select all of the records and put them in a dictionary. writeAddresses() is more difficult. What if someone removes an address from the dictionary how will we know without changing AddressModel? We'll skip writing addresses for now and instead integrate AddressDBUtils.py into the application.

Here's the code for readAddresses() and writeAddresses():

def readAddresses():        """        Read the Address data               AddrID, Name, Phone, Email        returns a dictionary full of addresses        """        dict = None        try:               statement, connection = createStatement()               dict = {}               results = statement.executeQuery(select_all)               while results.next():                     id = results.getString("AddrID")                     name = results.getString("Name")                     phone = results.getString("Phone")                     email = results.getString("Email")                     dict[name]=Address(name, phone, email)        finally:               connection.close()        return dict def writeAddresses(dict):        pass

As you probably noticed, writeAddresses() isn't doing anything yet.

Integrating AddressModel with the Database

Instead of replacing the flat file, let's see how to polymorphically add the database as an option. Via the __import__ function, AddressModel imports functions from AddressIO that dynamically load the I/O utility module (AddressBook3\ AddressIO.py).

from java.util import Properties from java.io import FileInputStream, FileOutputStream, IOException        #holds property file for the db connection info. PROP_FILE = "addr.props" def readAddresses():        pass def writeAddresses(dict):        pass ... ... def getModuleName():              #Load the properties.        props = __loadProps()        return props.getProperty("module_name", "AddressDBUtils") ... ... module = __import__(getModuleName()) readAddresses = module.readAddresses writeAddresses = module.writeAddresses

To switch back to the flat file, all we have to do is edit addr.props.

#Database properties #Wed Dec 29 20:24:54 PST 1999 #module_name=AddressDBUtils module_name=AddressFileUtils

As an exercise, run the application in both modes, switching back and forth between them.

A Database-Aware Dictionary

How do we know which addresses need to be written out of the database? One way is to set a dirty flag, which will tell us when one of the instance variables has changed. But what about adding new addresses how much code will we have to modify?

In fact, we can add the functionality that will answer these questions without changing AddressModel or any of the GUI. All we need to do is define a database-aware dictionary that replaces the dictionary that AddressModel is presently using.

Recall the dictionary class that we created in Chapter 6. We can use what we learned from it to create a dictionary class that adds and removes rows in the Address table. We'll call it AddressDBDict and put it in the AddressBook4.py module.

class AddressDBDict: ... ...       def __setitem__(self, key, value):              statement, connection = createStatement()              try:                     addr = AddressDB()                     addr.fromAddress(value, statement)                     self.dict[key]=addr              finally:                     connection.close()       def __delitem__(self, key):              statement, connection = createStatement()              try:                     addr = self.dict[key]                     addr.remove(statement)                     del(self.dict[key])              finally:                     connection.close()

As you can see, the "overridden" __setitem__ and __delitem__ methods create a JDBC statement object and interact with AddressDBDict.

__setitem__ takes a regular address instance as an argument to create an instance of AddressDBDict. It then calls the fromAddress() method, passing it a JDBC statement object. fromAddress() is where the Address record is inserted into the Address table.

__delitem__ calls the AddressDBDict instance it retrieves from AddressModel and invokes its remove() method, passing it a JDBC statement object. remove() deletes the Address record from the Address table.

How Many Connections Are Opened and Closed?

It may look like AddressDBDict creates and closes a connection every time it adds or deletes a record, but that would be awfully expensive as well as inefficient. Actually, only one connection to the database is created. Take a look at the createStatement() function and the Connect class in the AddressBook4.py module to see how this is done.

A Database-Aware Address Class: AddressDB

AddressDB is a subclass of Address, which notifies it, via the markDirty() method, when its member variables change. Address defines markDirty() and calls it in its constructor. AddressDB overrides markDirty() and adds the ability to update the corresponding record in the Address table when changes are made.

Recall that the Address class has no setter methods for its variables but sets them through __init__. The first time __init__ is called, the dirty flag is set to 0 (false); at all subsequent calls, it's set to 1 (true).

Here's some of the code for Address and AddressDB showing the interaction between markDirty(), dirty, and isDirty():

class Address:              ...          __dirty = -1      def __init__(self, name="", phone_number="", email="",           address_lines=None):                   ...                   ...                   self.markDirty()              ...      ...      def markDirty(self):              ...              self.__dirty = self.__dirty + 1      def isDirty(self):              ...              return self.__dirty      class AddressDB(Address):      ...      ...              def markDirty(self):                   ...                   Address.markDirty(self)                   if(self.isDirty()):                   self.__update()      def __update(self):              ...              statement, connection = createStatement()              try:                   self.update(statement)              finally:                   connection.close()

markDirty() calls the Address of the class; then it calls its isDirty() method to determine if the data is dirty. If so, AddressDB's __update() method is called by its markDirty() method in order to create a JDBC statement object and to update the record in the database.

In this way the base class and the subclass interact to determine the state of the address instance. If the data is dirty, it's written out to the database and made clean again.

AddressDB's update() method updates the record; its remove() method deletes the record; and its fromAddress() method inserts a new one. The following code shows how these methods work:

where = """        WHERE              AddrID = %(_AddressDB__id)s        """ select = """        UPDATE              Address        SET              Name = '%(_Address__name)s',              Phone = '%(_Address__phone_number)s',              Email = '%(_Address__email)s'        """ + where delete = """        DELETE FROM              Address        """ + where insert = """       INSERT INTO              Address              (AddrID, Name, Phone, Email)        VALUES              (              %(_AddressDB__id)s,              '%(_Address__name)s',              '%(_Address__phone_number)s',              '%(_Address__email)s'              )        """ class AddressDB(Address):        ...        ...        def update(self, statement=None):              ...              if(statement):                     ...                     statement.executeUpdate(select % self.__dict__)                     self.__dict__["_Address__dirty"] = 0 ...        def remove(self, statement=None):              ...              if(statement):                     statement.executeUpdate(delete % self.__dict__) ...        def fromAddress(self, addr, statement=None):                     # Copy the address.        name = addr.name()        phone_number=addr.phoneNumber()        email=addr.email()        self.__dict__["_Address__dirty"] = -1        Address.__init__(self,name, phone_number, email, address_lines=None)                     # Insert the record into the database.        self.__id = getID()        str = insert % self.__dict__        if statement:                     statement.executeUpdate(str)        ...

AddressDB knows how to deal with the database, and, since it has the same interface as Address, the GUI knows how to work with either without knowing which is which. AddressDBDict, too, knows how to deal with the database. What's more, it has the same interface as a Python dictionary object, which means that, like a dictionary object, it can work with AddressModel, AddressMain, AddressFormPane, and so forth, without these classes having to be changed. This is polymorphism at its deceptive best.

To make the deception complete, AddressDBUtil's readAddresses() function has to get in on the action. Instead of a dictionary full of address instances, it now has to return AddressDBDict, which holds AddressDB instances. Here's its code (from AddressBook4\AddressDBUtils.py) with the interesting parts highlighted in bold:

def readAddresses():       ...       ...       try:              statement, connection = createStatement()       ...              dict = AddressDBDict()              results = statement.executeQuery(select_all)              while results.next():                    id = results.getString("AddrID")                    name = results.getString("Name")                    phone = results.getString("Phone")                    email = results.getString("Email")                    addr=AddressDB(name, phone, email)                    dict.dict[name] = addr                    addr.setID(id)       finally:              #connection.close()              pass       return dict

Using the Database-Supported Address Book Application

Now our address book application has database support. To get that support, and to keep the application backward-compatible with the flat-file format, we tapped into the power of Python and polymorphism. Try these exercises:

• Deploy the address book application in a different database than the one you've been using.

• Add the ability to track a phone conversation that is, take notes while you're talking and pull up a list of notes for a given address entry. To accomplish this you'll need a database table called Phone Log with a notes field, an identity field, and a foreign key field that references AddrID in the Address table. You'll also need GUI components to view, edit, and delete notes.

• Make it possible to travel with the address book application, working with a laptop and a database server.

• Add the ability to copy Address records from a database format to a flat-file format.

• Add a dialog box that allows a user to copy data to the flat-file format and work with the application in mobile mode that is, a mode that reads from the flat file instead of from the database server.

• Enable the user to reconnect to the database.

Summary

In this chapter we covered SQL/JDBC programming. We used what we learned to provide database support for our address book application. We took a brief tour of SQL fundamentals, including the SQL sublanguages, DDL and DML.

The exercises showed how to connect and use two different databases: a JDBC ODBC bridge (Microsoft Access) and a pure JDBC connection (InstantDB). We saw some of the difficulties in porting to and running on multiple databases.

We also saw the importance of modular design when we grafted database support onto the address book application. In addition, we used java.Utils.Properties to make changing properties easier. We also discovered the dynamic capabilities of Python's __import__ statement, which uses the module name in a property file to load an I/O utility module. The dynamism of Python and polymorphism was reinforced when we added database support to the AddressModel class by creating a database-aware subclass and a database-aware dictionary.

CONTENTS