Database Connectivity for Geographical Information Analysis

1. Programming for Geographical Information Analysis:Advanced Skills Lecture 5: Database connectivity Dr Andy Evans

2. Java and Databases SQL Java and SQL

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

4. Java DB: “Derby” Comes free with Java 8, 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

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

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

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

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

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

10. 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()); Postgres: https://jdbc.postgresql.org/ DriverManager.registerDriver(new org.postgresql.Driver());

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

12. Opening a connection Connection 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"

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

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

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

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

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

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

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

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

21. java.util.Properties What if 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.

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

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

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

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

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

27. Java and Databases SQL Java and SQL

28. SQL (Structured Query Language) ISO Standard for database management. Allows creation, alteration, and querying of databases. Broadly divided into: Data Manipulation Language (DML) : data operations Data Definition Language (DDL) : table & database operations Often not case-sensitive, but better to assume it is. Commands therefore usually written UPPERCASE. Some databases require a semi-colon at the end of lines.

29. Creating Tables CREATE TABLE tableName ( col1Name type, col2Name type ) List of datatypes at: http://www.w3schools.com/sql/sql_datatypes.asp CREATE TABLE Results ( Address varchar(255), Burglaries int ) Note the need to define String max size.

30. SELECT command SELECT column, column FROM tables in database WHERE conditions ORDER BY things to ordered on SELECT Addresses FROM crimeTab WHERE crimeType = burglary ORDER BY city

31. Wildcards * : All (objects) % : Zero or more characters (in a string) _ : One character [chars] : Any character listed [^charlist] or [!charlist] : Any character not listed E.g. Select all names with an a,b, or c somewhere in them: SELECT * FROM Tab1 WHERE name LIKE '%[abc]%'

32. Case sensitivity If you need to check without case sensitivity you can force the datatype into a case insensitive character set first: WHERE name = 'bob' COLLATE SQL_Latin1_General_CP1_CI_AS Alternatively, if you want to force case sensitivity: WHERE name = 'Bob' COLLATE SQL_Latin1_General_CP1_CS_AS

33. Counting Can include count columns. Count all records: COUNT (*) AS colForAnswers Count all records in a column: COUNT (column) AS colForAnswers Count distinct values: COUNT(DISTINCT columnName) AS colForAnswers SELECT crimeType, COUNT(DISTINCT Address) AS HousesAffected FROM crimes

34. Joining tables Primary key columns: Each value is unique to only one row. We can join two tables if one has a primary key column which is used in rows in the other: SELECT Table1.columnA, Table2.columnBFROM Table1JOIN Table2ON Table1.P_Key=Table2.id

35. Altering data Two examples using UPDATE: UPDATE Table1 SET column1 = ‘string’ WHERE column2 = 1 UPDATE Table1 SET column1 = column2 WHERE column3 = 1

36. SQL Introductory tutorials: http://www.w3schools.com/sql/default.asp

37. Java and Databases SQL Java and SQL

38. JDBC SQL Three methods: Statements: Standard, simple, SQL. PreparedStatements: Compiled SQL statements that are altered to new data through input parameters. Useful, for example, in looped structures. CallableStatements: for SQL procedures stored in the database. Produce ResultSetobjects.

39. Example: Select Statement st = conn.createStatement(); ResultSet rs = st.executeQuery ("SELECT a,b,c FROM Table1"); Three different execution methods: executeQuery : simple SQL queries. executeUpdate : anything that changes or creates a Table, e.g. UPDATE. Returns number of rows effected. execute: Complex, multi-return queries. st.close(); efficient, but will happen at conn.close()

40. Example: Creating tables String createTable = "CREATE TABLE Results (" + "Address varchar(255)," + "Burglaries int" + ")"; Statement st = null; try { st = conn.createStatement(); st.execute (createTable); } catch (SQLException ex) { ex.printStackTrace(); }

41. Auto-commit You can run multiple queries on the same statement. By default Connection objects are set to auto-commit – all changes are solidified after single statements are run. conn.setAutoCommit(booleanAutoCommit); If set to false (off) the changes will only be solidified when commit called: conn.commit(); Until then you can rollback all changes since the last commit, by calling: conn.rollback(); Also options to setup and rollback to savepoints.

42. Escape characters Remember that some characters, like “_”, are wildcards. If we want to use these literally, they need a backslash infront of them “\_”. However, backslash is a String escape character in Java, so you can define what is a special escape character to ignore in statement execution: st.executeQuery( "SELECT name FROM Table1 WHERE Id LIKE '¬_%' {escape '¬'}"); In some cases the escape occurs in a table name, in which case treat literally by enclosing in [] e.g. [Sheet1$]

43. ResultSets Links to a cursor on the database and converts data to Java types. ResultSet rs = st.executeQuery ("SELECT a, b, c FROM Table1"); while (rs.next()) { int i = rs.getInt("a"); or String s = rs.getString("b"); or Object o = rs.getObject("c"); } Use object if unsure, and cast. A new SQL query will close the current ResultsSet. Can also use column index number (starting 1).

44. ResultSets Standard results sets only let you read from beginning to end a line at a time. If you want to write to the data (without using SQL UPDATE) or go back and forwards, you need a scrollable statement. Statement st = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT); Only need to worry about this if you’re not auto-committing.

45. Scrollable ResultSet rs.beforeFirst(); rs.afterLast(); rs.next() rs.previous() Looping: while (!rs.isAfterLast()) { Finding number of rows: rs.last(); int numberOfRows = rs.getRow();

46. Scrollable ResultSet st.setFetchSize(25); Fetch 25 rows at a time. rs.absolute(2); Move to row 2 rs.updateInt(3, 10); Update col 3 of current row. rs.updateInt("Name",10); Update by col name. also updateObject, updateString, updateFloat and others. rs.updateRow(); Must call this after updating data in all the columns you want to change for that row. You also need to call rs.close() to commit all changes.

47. Inserting a row rs.moveToInsertRow(); rs.updateString(1, "Bob"); rs.updateObject(2, someObject); rs.insertRow(); rs.first(); (In general the cursor points at the row used prior to insert.) conn.commit(); If needed. rs.close();

48. Database metadata Especially useful in debugging is getting metadata about the database: DatabaseMetaData md = conn.getMetaData(); ResultSet rs = md.getTables( null, null, "%", null); while (rs.next()) { System.out.println(rs.getString(3)); }

49. Further info Online guide: http://docs.oracle.com/javase/tutorial/jdbc/basics/

50. Next Lecture XML Practical JDBC