SQL : Java Glossary

JDBC stands for Java Data base Connectivity. Sun’s official position is that it does not, although that is the generally accepted assumption. It describes a list of methods a Java programmer can use to access an SQL relational database. JDBC is similar to Microsoft’s ODBC (Open Data base Connectivity) interface to SQL databases.

The Vendor Lists

1. JDBC driver vendors.
2. SQL engine vendors including prices.
3. Java-friendly ISPs.
4. Canadian Internet Access Providers.

Alternatives to SQL

Discovery

SHOW DATABASES; -- examine list of supported databases
USE mydata; -- select mydata database
SHOW TABLES; -- examine tables in mydata database
DESCRIBE animals; -- look at column descriptions in the animals table
CREATE DATABASE plants; -- create a new database.

Comments

1. -- to end of line
2. /* or /** to matching */
3. non-standard # to end of line.

Creating

DROP TABLE animals;
DROP DATABASE mydata;
ALTER TABLE animals DROP COLUMN toothCount;

Querying

SELECT last_name, first_name, city, state
FROM contacts
WHERE state = 'MA'
ORDER BY last_name DESC;

SELECT confirm, ordertimestamp
FROM orders
WHERE confirm < 2000 AND vendorid = 1234
ORDER BY confirm DESC
LIMIT 1

There is some slick syntax like BETWEEN and IN writing terser WHERE clauses. LIKE 'Mc'; gives you wildcard matching. You can also summarise data with queries like this to get the count of people in each state (not bothering with states with one or fewer people.)

SELECT state, count(*)
FROM contacts
WHERE age > 18
GROUP BY state
HAVING COUNT(*) > 1
ORDER BY state;

Updating

UPDATE contacts
SET last_name='Brown', state='WA'
WHERE acct=2103 AND state='MA';

Inserting

INSERT INTO contacts(last_name, first_name, city, state)
VALUES('Brown','James','Seattle', 'WA');

LOAD INTO TABLE contacts FROM 'C:\temp\contacts.txt';

Deleting

DELETE FROM contacts
WHERE acct=2103;

PreparedStatement

In Oracle, it does not pay to use preparedStatement unless you are going to use the same query at least 50 times. In fine tuning, you want to discover where your own database’s break point is.

The server maintains an object to represent the prepared statement query. The server may plan its strategy when you submit the prepared statement, or may postpone that decision, or part of it, until it has the actual data values filled in, which give it further hints on whether a given index would be any use, particularly with LIKE clauses.

ResultSets

String employeeName = result.getString( "EmpName" );
int employeeNumber = result.getInt( "EmpNum" );

Quoting

One more complication: SQL sometimes uses double quotes ", to surround identifiers, e.g. table names that have spaces or other awkward characters in them. Not all SQLs support this. In Sybase, you must use set quoted_identifier on to enable the feature.

Transaction Processing

The basic idea is you can do a group of SQL operations. If any of them fail, all changes back out to the way they were when you started the group. The group of SQL operations is called a transaction. You mark the end of a transaction with Connection. commit. To force partially complete changes to be undone, use Connection. rollback. Control the how different transaction threads interact with Connection. setTransactionIsolation.

SQL Cleverness

SQL tries hard to avoid transporting data to and from the server. Instead of fetching records for you to look at at the client, you send a command to the server to do what you want and return just the summarised information.

Don’t be timid about creating huge result sets then only using part of them. Most database engines are quite clever, and only transmit a hundred records at at time of the result set. This buffering is completely transparent to your application.

Let’s say, for example, you asked for a a list of people living in France with a WHERE country = 'France' clause. While you were processing your giant ResultSet, one of these people moved to Belgium and somebody else updated their record. SQL will ensure either you get that person’s old record showing him living in France, or it will exclude him from the ResultSet before you process it. Your ResultSet is guaranteed to contain only people listed as living in France. If you update them, you might put a WHERE country = 'France' clause to ensure that fact has not recently changed.

You should only see the new state (sometimes, and probably very rarely) if the transaction isolation level is READ_UNCOMMITTED. If it’s any higher than that, then the update in another transaction should not cause the SELECT in this transaction to produce spurious results. Most database products default to a transaction isolation level higher than READ_UNCOMMITTED.

Gotchas

If you load your triggers individually they work. If you try to load them in batches, SQL gets confused about terminating semicolons. You can view your triggers with:

SELECT * FROM SYS.SYSTRIGGERS;

SQL uses CASE/WHEN/ELSE instead of SWITCH/CASE/DEFAULT. Its these little differences that often trip you up and leave you scratching your head. It is missing features you would expect such as the ability to traverse forward and back in result sets.

LIMIT row_count lets you limit the size of a result set. Unfortunately, this is not standard in all SQLs. Your vendor may do it a different way. SQL-2003 is the most standardised of all the variants. Users are refusing to put up with proprietary extensions. There is now wide choice.

Escapes

{d 'yyyy-mm-dd'}
-- e.g.
WHERE arrivalDate < {d '2002-12-31'}

{t 'hh:mm:ss'}
-- e.g.
WHERE arrivalTime < {t '23:59:59'}

{ts 'yyyy-mm-dd hh:mm:ss'}
-- e.g.
WHERE arrivalTimestamp < {ts '2002-12-31 23:59:59'}

For more information on escapes, see JDBC literals.

Atomic Updates

-- incrementing a field in one atomic operation
UPDATE bankAccount SET balance = balance+? WHERE accountNumber=?

-- setting a field to the highest value so far, all in one atomic operation
UPDATE vendors SET highestConfirm=GREATEST(?,highestConfirm) WHERE vendorId=?

Security

By entering GRANT commands into your database, you control who can access which tables from where with which passwords. You can separately control read and write access.

Text Searching

MySQL offers server-side full-text indexing and searching. Simply declare an index of type FULLTEXT on the TEXT/CHAR/VARCHAR table columns which contains the text you want to search, then query the table using the "MATCH()… AGAINST" syntax. There’s a manual section which describes this facility.

OpenFTS is a full text search third party add-on for Postgre. TSearch2 is another full text search for Postgre.

Oracle Text Search is Oracle’s facility in there Oracle 10 database. Read the Oracle Text Search How To.

Full text search is now becoming a common feature in databases.

Under The Hood

What actually happens is much more efficient and clever. When I first used the Sybase SQL engine I could not believe how fast it was compared with the Btrieve DOS files and ISAM I was used to. What makes it so fast?

First, it does not actually wade through all the records in each relevant table looking for matches. It has indexes. It uses those indexes to narrow down the search to likely candidates. Clever SQL engines even create new indexes on the fly without being asked to help them process queries faster.

Next, SQL engines cache as much of the database as they can in RAM. Sometimes databases are totally RAM-resident. This is quite feasible now-a-days with RAM as cheap as it is.

Next the SQL engine does not actually fetch the entire result set. It just grabs a decent sized chunk of it, say 15 rows worth and hands that to you in a chunk. When you have processed that, it gets you the next lump, or it may get the next chunk ready while you are processing the first chunk. This is why you can get away with creating giant result sets then using just the first few rows of them.

You might think the way you update a row is to submit a C-like binary struct representing it and that, to fetch a row, you would get such a beast back. Oddly, it does not work that way. Baud knows why. Instead you compose ASCII sentences to update fields. I kid you not. You have to painstakingly compose things like this:

UPDATE contacts
SET last_name='Brown', state='WA'
WHERE acct=2103 AND state='MA';

In the JDBC entry I talk about the conversations the client and server have. The client end is generally not very bright. It just relays requests to the server using a binary protocol.

O-R Tools

A Modest Proposal

Answers to queries come back as Iterators of row objects. These objects are custom classes with primitive fields with the same names precisely as the SQL columns. The corresponding Java for these classes needs to be generated ahead of time, much the way RMI stubs are.

Granted this does not allow fancy dynamic queries, but it does the bull work in a way that is much easier to write and read.

From there it is just one more step to smart GUI objects that automatically update when a query result comes in, and that can allow GUI objects to modify the row with a minimum of code.

Books

	recommend book⇒Guide to the SQL Standard: A User’s Guide to the Standard Database Language SQL
		paperback
	ISBN13:	978-0-201-96426-4
	ISBN10:	0-201-96426-0
	publisher:	Addison-Wesley
	published:	1996-11-18
	by:	Chris J. Date, yes the C.J. Date.
Considered the best on understanding the SQL "standard".
abe books.co.uk abe books.ca amazon.co.uk amazon.ca abe books.de chapters.indigo.ca amazon.de abe books.com abe books.fr amazon.com amazon.fr barnes and noble.com abe books.it powells.com iberlibro.com abe books anz

Learning More