JDBC demystified.

JDBC (Java DabaBase Connectivity) is an API interface for relational databases connections, such as Oracle RDBMS, SQL Server, MySQL, and Microsoft SQL DB.

In Java 7 includes JDBC 4.1 reduces the amounts of code required to work with databases. It is most commonly used as in web-based applications hosted in J2EE servers, including JBOSS, Tomcat, WebSphere.

Android has its own API SQLite to work with local database. Calls can be made from Android application to access larger databases through web services hosted by middleware servers.

The Spring application framework includes something called JDBC Template. It simplifies the amount of code using JDBC to talk to the database. Hibernate is the most popular data mapping APIs using an object-relational mapping mechanism. It represents the database structure with Java classes and objects. In the background it's still using JDBC to communicate with the database.

Applications that use the JDBC API require drivers. A JDBC driver is a software library that encapsulates the logic required to communicate between the application and the database management system. JDBC driver rules are defined in Java Standard Edition.

A driver library package will contain specific implementations of these Java interfaces.

• Connection - which lets you connect to the database, 
• ResultSet - which encapsulates data returned from the database, 
• Statement - requests to the database
• PreparedStatement - represent requests to the database
• CallableStatement - represent requests to the database

Typically, a driver package can be downloaded from the database vendors themselves, a MySQL driver from MySQL an Oracle driver for Oracle, et cetera. Most of JDBC drivers will support these five interfaces.

There are four distinct types of drivers, distinguished by their architecture.

Type 1 JDBC Driver

• JDBC-ODBC bridge driver + ODBC driver
• it is the oldest type. 
• Installed on the client system.
• Started in the mid to late '90s when JDBC got started, ODBC or the Open Database Connectivity protocol was the dominant model for communicating with the database. 
• At runtime, requests go from the application through the JDBC API to the Bridge driver from there to the ODBC driver and then to the database. 
• Not fast, but it is dependable, 
• Can work with any database for which an ODBC driver existed (pretty much every RDBMS) 
• Cons: 
• the ODBC Bridge driver is not 100% Java and therefore not portable between operating systems. 
• working with two drivers and both have to be on the same computer as the application, so you have increased maintenance.
• the ODBC driver has to match the database version, and so if database on the server, is updated, all the client applications have to be updated as well. 

Type 2 JDBC Driver

• Native protocol API driver + Java driver
• Both are installed on the client system just like the Bridge driver and ODBC driver.
• Fast - because primarily working with native APIs, you get the best performance.
• 
  
• Not 100% Java so it's not portable between operating systems.
• The native API driver has to be installed on the application client and maintained, and once again, if the database is updated, the client software has to be updated as well.

Type 3 JDBC Driver

• Net protocol + Java driver
• installed in multiple locations
• 100% Java driver that's installed in the client along with the application
• a middleware server which hosts its own application
• requests go at runtime from the application to the Type 3 driver that's installed on the client, to the network to the middleware server and then to the database.
• The middleware driver can be native, and so the communication between the middleware and the database can be very fast.
• but at the cost of the maintenance challenges with more than one driver to maintain. 

Type 4 JDBC Driver

• All 100% Java thin driver + 100% Java driver
• the most common.
• Only one driver package with Java application itself.
• Can be on a client computer, in a web environment on J2EE server.
• Requests go from the application to the driver that's on the client and then through JDBC through the thin driver to the database server if it's out on the web, or to the database file if it's on the local hard disk. 
• With the Java thin driver, you're communicating directly from the application to the database. No additional layers to install or maintain so maintenance is greatly simplified. 
• Cons: a different driver package is needed for each database to work with.

Working with Multiple Database Types in Single Application

• Most applications will only use a single database type, but if you're working with more than one database management system, you'll need to provide multiple drivers. 
• For example, you may have one MySQL database server hosted in the Cloud that is accessible over the web and an Apache Derby SQL Database that is initialized with local files and runs in the same Java process of the application.
• Use Type 4 pure Java drivers to make the code as portable as possible. That's the idea of encapsulated applications.

Simple Type 4 Java driver example

private Connection getConnection (String dbName) {
    Connection connection = null;
    try {
        String dbDirectory = "./Resources";
        System.setProperty("derby.system.home", dbDirectory);
        String dbUrl = "jdbc:derby:" + dbName + ";create=true";

        connection = DriverManager.getConnection (dbUrl);
        return connection;
    }
    catch (SQLException e) {
        for (Throwable t : e) t.printStackTrace();
        System.err.println(e);
        return null;
    }
}

private void getResultSet (Connection cn) {
    String sql  = "SELECT * FROM Runners ";
        try ( PreparedStatement ps = cn.prepareStatement(sql);
              ResultSet rs = ps.executeQuery();
        ) {
              readRS(rs);
              disconnect();
        }
        catch (SQLException e) {
              System.err.println(e);
        }
}

private void readRS (ResultSet rs) {
    try {
        while (rs.next()) {
            ThreadRunner t = new ThreadRunner 
                (rs.getString(1), rs.getInt(2), rs.getInt(3));
            t.setName(rs.getString(1));
            runners.add( t );
         }
     }
     catch (SQLException e) {
         System.err.println(e);
     }
}

private boolean disconnect () {
    try {
        String shutdownURL = "jdbc:derby:;shutdown=true";
        DriverManager.getConnection(shutdownURL);
    }
    catch (SQLException e) {
        if (e.getMessage().equals(
             "Derby system shutdown."))
        return true;
    }
    return false;
}

SQL Tips

Guidelines

• Document as you go.
• Leave bread crumbs on the trail.
• Keep it simple.
• Use prefixes or suffixes to make it obvious.
• Use consistent coding style.
• Add comments when it's not obvious.
• Anticipate disasters proactively.
• Testing
• DELETE
• INSERT
• SELECT
• UPDATE
• DELETE

Terms

• DDL - Data Definition Language
• DML - Data Manipulation Language
• PL/SQL - Procedural Language for SQL
• RDBMS - Relational DataBase Management System

Example

CREATE TABLE author (
id          number,
name        varchar2(100),
birth_date  date,
gender      varchar2(30)
);

Using IJ

% ./bin/ij
ij version 10.8
ij> connect 'jdbc:derby:myDB;create=true'; 

CREATE TABLE Products
     (
         ProductCode VARCHAR(10),
         Description VARCHAR(40),
         Price DOUBLE
);
0 rows inserted/updated/deleted
ij>

INSERT INTO Products
     VALUES ('candy', 'chocolate''s flavor', 5.25);
1 row inserted/updated/deleted
ij> disconnect;
ij> exit;

Running Script

% ./bin/ij
ij version 10.8
ij> connect 'jdbc:derby:myDB';
ij> run 'buildMyDB.sql';
ij> disconnect;
exit;

# to run script from command line
% java org.apache.derby.tools.ij myDBCreate.sql

# to start the Derby server
% java org.apache.derby.drda.NetworksServerControl start

# to stop the Derby server
% java org.apache.derby.drda.NetworksServerControl shutdown

Links

• Beginning PL/SQL
• Apache Derby Manual
• Apache Derby FAQ
• Apache Derby Tutorial
• Apache Derby Install Page

SQL 102.

Concepts

• Primary Key vs Foreign Key
• Scale
• Schema
• Records/Rows
• Fields/Columns
• Using Alias to shorten SQL syntax

SELECT RTrim(name) + ' ( ' + RTrim(country) + ')' AS 
title
FROM Students
ORDER BY name;

SELECT name, contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'THISID';

Using JOIN

// to retrieve all order from the customer who ordered
// '123' 
SELECT id, name
FROM orderlist
WHERE id = (SELECT id
            FROM orderlist
            WHERE id = '123');

// Using JOIN for the same query
SELECT p1.id, p1.name
FROM orderlist AS p1, orderlist AS p2
WHERE p1.id = p2.id
  AND p2.id = '123';

// Standard Join/Inner Join vs Outer Join
// Natural Joins eliminate repeated columns from the inner join
SELECT c.*, o.order_num, o.order_date, oi.prod_id, 
       oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems as oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

// Use outer Join to include rows that have no related rows
// INNER JOIN example
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id

// OUTER JOIN
//    must use RIGHT or LEFT keyword to specify 
//    which table to include all rows
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON orders.cust_id = customers.cust_id;

// Simplified OUTER JOIN
SELECT customers.cust_id, orders.order_num
FROM customers, orders
WHERE customers.cust_id *= orders.cust_id;

// Using JOIN with Aggregate Functions
SELECT customers.cust_name, 
       customers.cust_id,
       Count(orders.order_num) AS num_order
FROM customers LEFT INNER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name, 
         customers.cust_id;

SELECT customers.cust_name, 
       customers.cust_id,
       Count(orders.order_num) AS num_order
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name, 
         customers.cust_id;

SubQuery and Combined Queries Using UNION

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price >= 5 OR vend_id IN (1001,1002);

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price >= 5 
// without UNION ALL, SQL eliminate duplicate rows
UNION     
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
// ORDER BY vend_id, prod_price;

Full-Text Searching

EXEC sp_fulltext_database 'enable';
CREATE FULLTEXT CATALOG catalog_my;
CREATE FULLTEXT INDEX ON productnotes (note_text)
KEY INDEX key_productnotes
ON catalog_my;
// KEY INDEX is used to provide the name of the table's primary key.

ALTER FULLTEXT CATALOG catalog_my REBUILD;
SELECT * FROM sys.fulltext_catalogs;

// FulltextCatalogProperty() function
//    accepts a catalog name and the property to be checked
//    IndexSize
//    PopulateStatus

// FREETEX - simple search, matching by meaning or exact text match
// CONTAINS - search for phrases, synonyms
SELECT note_id, note_text
FROM notes
WHERE note_text LIKE '%bird food%';

SELECT note_id, note_text
FROM notes
WHERE FREETEXT(note_text, 'bird food'); // look for anything that
                                        // could mean bird food

// WHERE CONTAINS (note_text, '"iron*"'); // match anything with iron
// WHERE CONTAINS (note_text, 'bird food');
//    CONTAINS is functionally identical to LIKE note_text = '%match%'
//    CONTAINS search typically is quicker, especially as table size
//        increases.

// More CONTAIN examples
WHERE CONTAINS (note_text, 'safe AND sound');
WHERE CONTAINS (note_text, 'bird AND NOT food');
WHERE CONTAINS (note_text, 'grass NEAR cheese');
// look for any words that share the same stem as 'vary', such as 'varies'
WHERE CONTAINS (note_text, 'FORMSOF (INFLECTIONAL, vary)');

// Ranking
// The following query use FREETEXTTABLE function to return a table that
//     contain words meaning bird and food and gives the table an alias 
//     of 'f'
SELECT f.rank, note_id, note_text
FROM notes,
    FREETEXTTABLE (notes, note_text, 'bird food') f
WHERE notes.note_id = f.[key]
ORDER BY rank DESC;

SQL 101.

COMMANDS

• SELECT <column-1> [, column-2] ...
• FROM <table-1>
     { INNER | LEFT OUTER | RIGHT OUTER} JOIN table-2
     ON table-1.column-1 {=|<|>|<=|>=|<>} table-2.column-2
• WHERE selection-criteria
• ORDER BY column-1 [ASC | DESC] [, column-2 [ASC | DESC]] ...
• INSERT INTO <table-name> [(column-list)]
• VALUES (value-list)
• UPDATE <table-name>
• SET  <expression-1> [, expression-2] ...
• WHERE selection-criteria
• DELETE FROM <table-name>

SELECT

SELECT ProductCode, Description, Price
FROM Products
WHERE Price > 50
ORDER BY ProductCode ASC

SELECT * FROM PRODUCTS

SELECT p.ProductCode, p.Price, li.Quantity,
            p.Price * li.Quantity AS Total
     FROM Products p
           INNER JOIN LineItems li
           ON p.ProductCode = li.ProductCode
     WHERE p.Price > 50
     ORDER BY p.ProductCode ASC;

SELECT p.ProductCode, p.Price, li.Quantity,
             p.Price * li.Quantity AS Total
     FROM Products p, LineItems li
     WHERE p.ProductCode = li.ProductCode AND p.Price > 50
     ORDER BY p.ProductCode ASC;

SELECT TOP(5) p.prod_name FROM products p, LineItem li
TABLESAMPLE (3 ROWS)
ORDER BY p.prod_price;

SELECT products.prod_name FROM products
ORDER By products.prod_price DESC, products.prod_name
TABLESAMPLE (50 PERCENT);

// Use [] to delimit the column name when there is space btwn
// and alias it using 'AS' to be 'LastName'
SELECT [Last Name] AS LastName

INSERT

INSERT INTO Products (ProductCode, Description, Price)
VALUES ('casp', 'ASP.NET Web Programming with C#', 54.50)

// without the column list
INSERT INTO Products
VALUES ('casp', 'ASP.NET Web Programming with C#', 54.50)

UPDATE

// Update a single row
UPDATE Products
SET Description =
       'Murach''s ASP.NET Web Programming with C#',
    Price = 49.50
WHERE ProductCode = 'casp'
// Update multiple rows
UPDATE Products
SET Price = 49.95
WHERE Price = 49.50

DELETE

DELETE FROM Products WHERE ProductCode = 'casp'
DELETE FROM Invoices WHERE AmountDue = 0
DELETE FROM Invoices

WHERE, IN, NOT

SELECT prod_name, prod_price
FROM products p, LineItem li
TABLESAMPLE (3 ROWS)
WHERE prod_price <> 10, prod_name = 'fuses'
ORDER BY price;

...
WHERE price BETWEEN 5 AND 10;
WHERE vend_id = 1002 AND price <= 10;
WHERE vend_id = 1002 OR vend_id = 2001 AND price <= 10;
WHERE prod_name LIKE 's%e'; // % is wildcard, 
WHERE prod_name LIKE 'jet%'; // matching any 'jet' in the beginning
WHERE prod_name LIKE '%anvil%'; // %...%, match anywhere

WHERE name LIKE '_ley'; // _ match one char
WHERE name LIKE '[EJ]%'; // match E or J as the start char
WHERE name LIKE '[^EJ]%'; // ^ NOT match, any name not begin with E or J
WHERE NOT name LIKE '[EJ]%'; // same as '[^EJ]%'

// WHERE OPERATOR:
// =, <>, !=, <, <=, !<, >, >=, !>, BETWEEN, IS NULL

Fields/Column Concatenation

SELECT name + ' (' + age + ')'
FROM students
ORDER BY name;

// T-SQL RTrim: trim all space from the right
//       LTrim: trim all space from the left
SELECT RTrim(name) + ' (' + LTrim(age) + ')' AS
student_title
FROM students
ORDER BY name;

Using MATH

SELECT id, quantity, price,
    quantity * price AS total_price
FROM orderlist
WHERE order_num > 2000;
// T-SQL math operators: + - * / %

Using Function

// Common Text Functions
// CharIndex()
// Left(), Right()
// Len()
// Lower(), Upper()
// LTrim(), RTrim()
// Replace()
// Soundex() : soundex value when they sound similar
// Str()
// SubString()

SELECT name, UPPER(name) AS name_upper
FROM orderlist
WHERE order_num > 2000;

// Common Data and Time functions
// DateAdd()
// DateDiff()
// DateName()
// DatePart()
// Day()
// GetData()
// Month()
// Year()

// Supported Date Parts:
// day (dd or d)
// dayofyear (dy or y)
// hour (hh)
// millisecond (ms)
// minute (mi or n)
// month (m or mm)
// quarter (q or qq)
// second (ss or s)
// week (wk or ww)
// weekday (DatePart() only), (dw)
// year (yy or yyyy)

SELECT name, 
    DatePart(weekday, orderdate) AS weekday
FROM orderlist;

// to return named weekdays instead of numbered day
SELECT name, 
    DateName(weekday, DatePart(weekday, orderdate) ) AS weekday
FROM orderlist;

// Day(),          Month(),         and Year() are shortcuts for 
// DatePart(day,), DatePart(month,) and DatePart(year,)

// when comparing dates, always use DateDiff()
SELECT name, id
FROM orderlist
WHERE DateDiff (month, order_date, '2005-09-01') = 0;

...
Where Year(order_date) = 2005 AND Month(order_date) = 9;

// Numeric Functions
// Abs()
// Cos()
// Exp()
// Pi()
// Rand()
// Round()
// Sin()
// Sqrt()
// Square()
// Tan()

// SQL Aggregate Functions
// Avg(), Count(), Max(), Min(), Sum()
SELECT Avg(price) AS avg_price // Null columns are ignored by Avg()
FROM products
WHERE id = 1003;

SELECT Count(*) AS num_customers // count all rows
FROM customers;
SELECT Count(customer_email) AS num_customers // count only those with emails
FROM customers;
SELECT Sum(price*quantity) AS total_price
FROM orderlist
WHERE order_num = 2000;

// Use ALL (default) or DISTINCT for unique items
SELECT Avg(DISTINCT price) AS avg_price_ofunique
FROM products
WHRE id = 1002;

Filtering Groups

// group by vend_id and calcute all products from that
// vendor
SELECT vend_id, Count(*) AS num_prods
FROM products
GROUP BY vend_id;

SELECT cust_id, Count(*) AS orders
FROM orderlist
WHERE price >= 10
GROUP BY cust_id
HAVING Count(*) >= 2;

// HAVING support all WHERE operators
// HAVING filters after data is grouped.
// WHERE filters before data is grouped 
//    (rows eliminated by WHERE are not included in the group)
//    (This can change the calculated values used in HAVING clause.

// ORDER BY
//    sorts generated output, can use any column, not required
// GROUP BY
//    groups rows but may not be in order,
//    only selected columns or expression columns may be used
//    every selected column expression must be used
//    required if using columns (or expressions) with Count/Avg/Sum/Min/Max

SELECT order_num, Sum(quantity*price) AS ordertotal
FROM orderlist
GROUP BY order_num
HAVING Sum(quantity*price) >= 50;
// ORDER BY ordertotal; // optional

SQL Servre 2005

% USE mydatabase;
% sp_databases;
% sp_tables;
% sp_tables NULL, dbo, mydatabase, "'TABLE'";
% sp_columns customers;
% sp_server_info;
% sp_space_used;
% sp_statistics;
% sp_helpuser;
% sp_helplogins;