Database Standards | JDBC 3: Java Database Connectivity

Database vendors are numerous, and they all have developed proprietary protocols and languages. Fortunately, though, there are some industry standards.

SQL

SQL isn’t a complete programming language usable to build complex applications. It is commonly used within a host language that offers specific features for building complete applications. However, SQL is an industry standard to access databases. It enables data definition, manipulation, and management; access protection; and transaction control. SQL’s roots are in relational databases, and it handles many relational database objects, including tables, indexes, keys, rows, and columns. The American National Standards Institute (ANSI) standardized SQL in 1986 and defined it to be independent of any programming language and database management system.

The ANSI 1989 standard defines three programmatic interfaces to SQL:

Modules: Separate compiled modules may define procedures and then call them from a traditional programming language.
Embedded SQL: The specification defines embedded statements for a few traditional programming languages. It enables embedding static SQL statements within complete programs.
Direct invocation: Access is implementation-defined.

Although embedded SQL was the most popular choice a few years ago, it isn’t the best answer to the problem of querying databases in client/server environments. It is static, and this limitation makes it unsuitable for newer software architectures using the J2EE framework, for example. In fact, embedded SQL is supported when Java is the host language, too, but this depends on database types and isn’t fully portable.

SQL-92, the newer ANSI specification, addresses modern environment needs. It contains new features, such as support for dynamic SQL and for an advanced technique to access result sets called scrollable cursors. Although dynamic SQL isn’t as efficient as static SQL, it does enable SQL statements to be prepared, to include parameters, and to be generated at runtime. In the case of prepared statements, performance may be increased. In fact, dynamic SQL allows the database to prepare an access plan before the execution. This access plan is reused each time the statement is called.

The SQL language is usable for a variety of purposes, including the following:

Querying a database by entering SQL text directly
Querying a database within a program
Defining data organization
Administering data
Accessing multiple data servers
Managing transactions

Databases use SQL as a language for both data definition and data manipulation (called DDL [Data Definition Language] and DML [Data Manipulation Language]).

A simple SQL primer

The SQL language supports a set of verbs used to define, store, manipulate, and retrieve data. Following are the basic SQL verbs used to build SQL clauses for such data manipulation.

To create a table, use the following syntax:

CREATE TABLE table (column type         [ NOT NULL | PRIMARY KEY | UNIQUE | ... ] [, column type       [ NOT NULL | PRIMARY KEY | UNIQUE | ... ]]*)

For example, to create a table of employees, you can use the following:

CREATE TABLE employees (id           int PRIMARY KEY,  name         char(25) NOT NULL,  salary       int)

To drop a table, use the following syntax:

DROP TABLE table

Here’s an example:

DROP TABLE employees

To supply new record values, use the following syntax:

INSERT INTO table [ (column [, column ]*)] VALUES (expr [, expr ]*)

For example, to add Jones as employee number one, with a salary of $60,000/year, you can use the following code:

INSERT INTO employees VALUES ("1", "JONES", 60000)

To delete rows, use the following syntax:

DELETE FROM table WHERE column [ < | > | = | <= | >= | <> | LIKE ] expr [ AND | OR... ]*

Here’s an example of deleting all employees earning more than $150,000 a year:

DELETE FROM employees WHERE salary > 150000

To retrieve data, use the following syntax:

SELECT [ DISTINCT ] [table.]column [, [table.]column ]* FROM table [= name] [, table [=name] ]* [ WHERE [table.]column [ < | > | = | <= | >= | <> | LIKE ] expr [ AND | OR ... ]*] [ ORDER BY [table.]column [ ASC | DESC ] [, [table.]column [ ASC | DESC ]]] [ HAVING ... ]

For example, to retrieve all employees earning more than $50,000, sorted by salary (highest first) and name, you can use this:

SELECT * FROM employees WHERE salary > 50000 ORDER BY salary DESC, name

To modify data, use the following syntax:

UPDATE table SET column = expr [, column = expr ]* WHERE [table.]column [ < | > | = | <= | >= | <> | LIKE ] expr [ AND | OR ... ]*

The following example raises Jones’ salary to $70,000 (Jones is employee number one):

UPDATE employees SET salary = 70000 WHERE id = 1

To create an index, use the following syntax:

CREATE [ UNIQUE ] INDEX index ON table (column [, column ]*)

For example, you can use the following to create an index on the name field:

CREATE INDEX idx_employees ON employees (name)

To create a stored procedure, use the following syntax:

CREATE PROCEDURE procedure [[(]@parameter type [= default ] [ IN | OUT | INOUT ] [, @parameter type [= default ] [ IN | OUT | INOUT ]]* [)]] [ WITH RECOMPILE ] AS sqlstatement

For example, the following code creates a stored procedure returning the highest salary via a parameter:

CREATE PROCEDURE maxsalary (@themax int OUT) AS SELECT @themax = MAX(salary) FROM employees

Note

For more in-depth information about SQL syntax and complex operations such
as table joins and so on, you can refer to the Web page http://developer.java.sun.com/developer/onlineTraining/Database/JDBCShortCourse/jdbc/sql.html. This page also recommends some good books. In addition, DBMS reference books and manuals such as Sybase SQL Server 11 DBA Survival Guide from Sams Publishing may help by providing more details about specific implementations of SQL, which are often required when dealing with various databases.

SAG-X/Open CLI

The X/Open and SQL Access Group defined the call level interface (CLI). CLI is a library of function calls that support SQL statements. For example, Microsoft’s Open Database Connectivity (ODBC) is a call level interface. JDBC is also a call level interface. Most database vendors have optimized CLI implementations for their database management system products. ODBC and JDBC are less proprietary interfaces, although they intensively use these specific CLIs to access databases. The most important benefit for programmers using the ODBC CLI or the JDBC CLI is interoperability: All clients adhere to a standard programming interface. CLI requires neither host variables nor other embedded SQL concepts that would make it less flexible from a programmer’s perspective. It is still possible, however, to maintain and use specific functions of a database management system when accessing the database through a CLI.