Databases: Connectivity

1. Databases: Connectivity Dr Andy Evans

2. Databases Flat file, relational, or object orientated stores of data records and their relationships. Oracle MySQL MS SQL Server Access (~format used by Arc Geodatabases)

3. 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

4. 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.

5. 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

6. 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/J Java → standardized driver → database ODBC (Open Database Connectivity)

7. Database packages Java Database Connectivity (JDBC) Packages: java.sql javax.sql

8. Opening a connection • Load the driver • Open the connection • Issue SQL • Close the connection

9. Database drivers If 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/JDBC DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); SQL Server: http://msdn.microsoft.com/en-us/sqlserver/aa937724 DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

10. 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");

11. Opening a connection Connection conn = null; try { conn = DriverManager.getConnection ("jdbc:xxxx:URL"); } catch (SQLException e) {} xxxx = DB driver (“mysql”, “derby”, “oracle:thin”, “sqlserver”) 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"

12. Closing a connection As usual with connections to things, it is polite to close them: try { conn.close(); } catch (SQLException e) {}

13. 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.

14. ODBC Data Source Manager Driver managers in Windows and Mac. Windows: odbcad32.exe

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

16. Access The same kinds of options, but some additional ones for databases. Under “Advanced” you can set default passwords to use.

17. Opening/closing ODBC connection Connection conn = null; try { conn = DriverManager.getConnection ("jdbc:odbc:sourceName"); } catch (SQLException e) {} try { conn.close(); } catch (SQLException e) {}

18. ODBC Pros 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. 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

19. 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.

20. 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.

21. 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.

22. java.util.ResourceBundle String 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.

23. java.util.ResourceBundle Good for regionalisation # Filemenu resource bundle saveMenu=Save closeMenu=Close String saveMenuText = ResourceBundle.getBundle(“en-gb") .getString("saveMenu"); Can be set up in more complex way in families for e.g. language regionalisation.

24. 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);

25. Creating Databases Connection conn = null; String strUrl = "jdbc:derby:c:\\databases\\myDB;create=true"; try { conn = DriverManager.getConnection(strUrl); } catch (SQLException sqle) { sqle.printStackTrace(); }