Databases: Connectivity

Dr Andy Evans

Databases

Flat file, relational, or object orientated stores of data records and their relationships.

OracleMySQLMS SQL ServerPostgreSQL ("Postgres")Access (~format used by Arc Geodatabases)

Java DB: “Derby”Comes free with Java 7, or from:

http://www.oracle.com/technetwork/java/javadb/overview/index.html

Can add the jar files to any java project.

Nice, small, DB system, which can be rolled into even small mobile apps, and distributed.

Jar it up with your classfiles, by sticking them in the same directory and using the following Manifest to make a automatically running file:

Manifest-Version: 1.0Main-Class: YourClass

Class-Path: derby.jar

Software connections

Software can be written in many different languages.

Languages can represent even basic data types in different ways.

We therefore need something to do the translation.

Inter-process communication

To communicate we can:

Ensure everyone uses the same type of compiled code : JVM, .Net

Ensure everyone uses the same types of objects and give some way of invoking methods in a OS framework: COM

Supply software that can translate the binary code of one into the binary code of the other : CORBA, bridge drivers

Translation drivers

For Java Database communication we have various options:

Java → database with java interface/driver.Derby Driver

Java — standard protocol [PC or Network] → database that accepts standard protocols.

MySQL Connector/JJava → standardized driver → database

ODBC (Open Database Connectivity)

Database packages

Java Database Connectivity (JDBC)Packages:

java.sqljavax.sql

Opening a connection

1) Load the driver2) Open the connection3) Issue SQL4) Close the connection

Database driversIf the manufacturer supplied a java driver, you should be able to load it.

MySQL: http://www.mysql.com/products/connector/DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Oracle database: http://www.orafaq.com/wiki/JDBCDriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

SQL Server: http://msdn.microsoft.com/en-us/sqlserver/aa937724DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

Postgres: https://jdbc.postgresql.org/DriverManager.registerDriver(new org.postgresql.Driver());

Alternative method

As an alternative, you can load the driver into the JVM and DriverManager will find it. The following code uses the default classloader to do this:

Java DB:Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

Opening a connectionConnection conn = null;

try {

conn = DriverManager.getConnection

("jdbc:xxxx:URL");

} catch (SQLException e) {}

xxxx = DB driver (“mysql”, “derby”, “oracle:thin”, “sqlserver”, “postgresql”)

URL is a URL string or file:"jdbc:derby: " + "//" + serverName + ":" + portNumber + "/" + dbName

Where serverName can be "localhost", or you can give a real database directory:"jdbc:derby:c:\\databases\\myDB"

Closing a connection

As usual with connections to things, it is polite to close them:

try {

conn.close();

} catch (SQLException e) {}

ODBC (Open Database Connectivity)

Use where no driver available.

Driver Manager keeps a list of data sources.

ODBC Driver Manager opens a driver that communicates with a database when requested.

JDBC-ODBC bridge used to ask the Driver Manager to open a driver connection.

ODBC Data Source Manager

Driver managers in Windows and Mac.Windows: odbcad32.exe

ODBC Data Source Manager

Give the source a name and (for Excel) select the workbook.Note under “Options>>” that the default is “Read Only”.

Access

The same kinds of options, but some additional ones for databases.

Under “Advanced” you can set default passwords to use.

Opening/closing ODBC connection

Connection conn = null;

try {

conn = DriverManager.getConnection

("jdbc:odbc:sourceName");

} catch (SQLException e) {}

try {

conn.close();

} catch (SQLException e) {}

ODBC Pros

Rather inefficient:

Program → Bridge → Driver → Database

JBDC-ODBC bridge was meant to be a stop-gap and doesn’t implement all the JDBC methods.

Issues with Strings and Access. On Excel and text, see also: http://support.microsoft.com/kb/178717

ODBC Cons

Common to most machines and driver supplied.As long as the ODBC data is uptodate, you can refer to the database by name, not location.

javax.sql.DataSource

What happens if the URL changes and you are using another driver?

Nice thing about ODBC is that you call the database by name.

There is a java database registration setup called the Java Naming and Directory Interface (JNDI).

You can use this, with a driver, to get a DataSource object, which replaces the DriverManager. Databases are registered by name – if the location changes, the JND can be updated.

java.util.Properties

What is there are access restrictions on the database?

To understand this, we need to understand Properties.

Properties : convenience class for storing key:value String pairs.

Useful for loading and saving things like regionalised text in different languages and user settings.

Properties

Properties p = new Properties();

p.setProperty(key, value); // both Strings

String a = p.getProperty(key);

store() : methods taking in various output streams.load() : method taking in various input streams.

java.util.ResourceBundleString a =

ResourceBundle.getBundle("setup").getString("keyString");

Where setup.properties is a text file like this:

# This is a comment

keyString=Text

anotherKey=Text can\nhave escape characters

anotherKeyString=Some more text

in the directory the class files are in.

java.util.ResourceBundle

Good for regionalisation

# Filemenu resource bundlesaveMenu=SavecloseMenu=Close

String saveMenuText = ResourceBundle.getBundle("en-gb")

.getString("saveMenu");

Can be set up in more complex way in families for e.g. language regionalisation.

Connection properties

Using a password:Properties p = new Properties();

p.setProperty("user", userName);

p.setProperty("password", password);

conn = DriverManager.getConnection

("jdbc:derby:c:\\databases\myDB", p);

Creating Databases

Connection conn = null;

String strUrl =

"jdbc:derby:c:\\databases\\myDB;create=true";

try {

conn = DriverManager.getConnection(strUrl);

} catch (SQLException sqle) {

sqle.printStackTrace();

}