SQL : Java Glossary

JDBC. Oracle’s official position is that expansion is wrong, 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.

One of the major benefits to SQL is that you always have a map of your file structure, fields and data types that is completely up to date. Without a database, it is up to programmers to maintain the file descriptions manually. If they let them slide, you won’t be able to export your data to a replacement application program.

The Vendor Lists

I have compiled four vendor lists, now split off into separate documents:

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

Alternatives to SQL

Classes of Statement

• DDL (Data Definition Language) statements define what data is contained in the database schema and its format and the indexexes for searching it. Examples of DDL statements include CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME.
• DML (Data Manipulation Language) managing data within schema objects. Some examples: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE, DCL (Data Control Language).
• DCL statements. Control who can use which data. Some examples: GRANT, REVOKE.
• TCL (Transaction Control Language) allows grouping changes into atomic units. e.g. COMMIT, SAVEPOINT, ROLLBACK, SET TRANSACTION.

Discovery

Comments

1. -- to end of line, Ada style.
2. /* or /** to matching */ C/C++/Java style
3. non-standard # to end of line, CSV (Comma-Separated Value) style.

Creating

To modify a table:

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

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

Simple Statement

// using a simple Statement
final Statement updater = conn.createStatement();

updater.executeUpdate( "UPDATE mboards "
                        + "SET formFactor="
                        + FormFactor.ATX.ordinal()
                        + ",widthInCm=30.5,heightInCm=22.4 "
                        + "WHERE manufacturer="
                        + manufacturer.ordinal()
                        + " AND model='GA-M750SLI-DS4'");

PreparedStatement

PrepareStatement protects you from composing ordinary Statements from Strings containing incidental 's, e.g. 'O'Toole'. It also protects you from using SQL keywords incidentally occuring in data from being interpreted as SQL commands, e.g. 'OAK PRIMARY SCHOOL TABLE'. For more about the security problem, read up on injections.

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. You still might use it even for one-shots just to achieve the security advantages.

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 (Standard Query Languages) support this. In Sybase, you must use set quoted_identifier on to enable the feature.

NULL

Like Java, SQL makes a distinction between an empty field and a null field, one for which the value is not known or does not exist. Unfortunately, Java null and NULL don’t automatically map onto each other. Part of the problem is null is not a permissible value for a primitive field. Further SQL has a different flavour of null corresponding to each data type. You can deal with SQL NULL fields with java.sql.PreparatedStatement.setNull and java.sql.ResultSet.isNull()

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 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 a time of the result set. This buffering is completely transparent to your application.

Let’s say, for example, you asked for 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

SQL uses = both for assignment and comparison unlike Java with uses = for assignment and == for comparison.

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.

SQL is missing features you would expect such as the ability to traverse forward and back in ResultSets. It is gradually improving; JDBC now has ResultSet.TYPE_SCROLL_INSENSITIVE which lets you examine ResultSets by random access. However, there is still no guarantee the version of your JDBC driver and the version of your SQL engine will support the crucial new feature you need. Support for almost every feature is optional.

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 beginning to dig in their heels and refuse to put up with lock-in proprietary extensions. There is now wide choice.

Escapes

For more information on escapes, see JDBC literals.

Atomic Updates

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 (Disk Operating System) files and ISAM (Indexed Sequential Access Method) 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. Unfortunately MySQL does not have this cleverness. It always hands your program the complete set of results.

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';

Happily the format of the resultset is completely up to the implementor. It will likely be a mixture of binary data and text. 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 (Remote Method Invocation) 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 (Graphic User Interface) 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

Book referral for Murach’s Oracle SQL and PL/SQL (Training & Reference)

	recommend book⇒Murach’s Oracle SQL and PL/SQL (Training & Reference)
	by	Joel Murach	978-1-890774-50-9	paperback
	publisher	Mike Murach
	published	2008-08-01
This talks about Oracle’s extended SQL, not just the generic SQL JDBC interface in Java. book website
Online bookstores carrying Murach’s Oracle SQL and PL/SQL (Training & Reference) abe books anz abe books.ca abe books.de amazon.ca amazon.de Chapters Indigo amazon.es Chapters Indigo eBooks iberlibro.com abe books.com abe books.fr amazon.com amazon.fr Barnes & Noble abe books.it Nook at Barnes & Noble amazon.it Kobo junglee.com Google play abe books.co.uk O’Reilly Safari amazon.co.uk Powells other stores
Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder.

Book referral for Guide to the SQL Standard: A User’s Guide to the Standard Database Language SQL

	recommend book⇒Guide to the SQL Standard: A User’s Guide to the Standard Database Language SQL
	by	Chris J. Date, yes the C.J. Date.	978-0-201-96426-4	paperback
	birth	1941 age:76
	publisher	Addison-Wesley
	published	1996-11-18
Considered the best on understanding the SQL standard.
Online bookstores carrying Guide to the SQL Standard: A User’s Guide to the Standard Database Language SQL abe books anz abe books.ca abe books.de amazon.ca amazon.de Chapters Indigo amazon.es Chapters Indigo eBooks iberlibro.com abe books.com abe books.fr amazon.com amazon.fr Barnes & Noble abe books.it Nook at Barnes & Noble amazon.it Kobo junglee.com Google play abe books.co.uk O’Reilly Safari amazon.co.uk Powells other stores
Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder.

see the list of JDBC books.

Learning More