Programming for Geographical Information Analysis Advanced Skills Lecture

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

Java and Databases SQL Java and SQL

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

Java DB: “Derby” Comes free with Java 8, or from: http: //www. oracle. com/technetwork/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. 0 Main-Class: Your. 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. My. SQL Connector/J Java → standardized driver → database ODBC (Open Database Connectivity)

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

Opening a connection 1) 2) 3) 4) Load the driver Open the connection Issue SQL Close the connection

Database drivers If the manufacturer supplied a java driver, you should be able to load it. My. SQL: http: //www. mysql. com/products/connector/ Driver. Manager. register. Driver(new com. mysql. jdbc. Driver()); Oracle database: http: //www. orafaq. com/wiki/JDBC Driver. Manager. register. Driver(new oracle. jdbc. driver. Oracle. Driver()); SQL Server: http: //msdn. microsoft. com/en-us/sqlserver/aa 937724 Driver. Manager. register. Driver(new com. microsoft. sqlserver. jdbc. SQLServer. Driver()); Postgres: https: //jdbc. postgresql. org/ Driver. Manager. register. Driver(new org. postgresql. Driver());

Alternative method As an alternative, you can load the driver into the JVM and Driver. Manager will find it. The following code uses the default classloader to do this: Java DB: Class. for. Name("org. apache. derby. jdbc. Embedded. Driver");

Opening a connection Connection conn = null; try { conn = Driver. Manager. get. Connection ("jdbc: xxxx: URL"); } catch (SQLException e) {} xxxx = DB driver (“mysql”, “derby”, “oracle: thin”, “sqlserver”, “postgresql”) URL is a URL string or file: "jdbc: derby: " + "//" + server. Name + ": " + port. Number + "/" + db. Name Where server. Name can be "localhost", or you can give a real database directory: "jdbc: derby: c: \databases\my. DB"

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: odbcad 32. 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 = Driver. Manager. get. Connection ("jdbc: odbc: source. Name"); } catch (SQLException e) {} try { conn. close(); } catch (SQLException e) {}

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. ODBC Cons 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

javax. sql. Data. Source 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 Data. Source object, which replaces the Driver. Manager. Databases are registered by name – if the location changes, the JND can be updated.

java. util. Properties What if there access restrictions on the database? To understand this, we need to understand 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 p = new Properties(); p. set. Property(key, value); // both Strings String a = p. get. Property(key); store() : methods taking in various output streams. load() : method taking in various input streams.

java. util. Resource. Bundle String a = Resource. Bundle. get. Bundle("setup"). get. String ("key. String"); Where setup. properties is a text file like this: # This is a comment key. String=Text another. Key=Text cannhave escape characters another. Key. String=Some more text in the directory the class files are in.

java. util. Resource. Bundle Good for regionalisation # Filemenu resource bundle save. Menu=Save close. Menu=Close String save. Menu. Text = Resource. Bundle. get. Bundle("en-gb"). get. String("save. Menu"); 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. set. Property("user", user. Name); p. set. Property("password", password); conn = Driver. Manager. get. Connection ("jdbc: derby: c: \databasesmy. DB", p);

Creating Databases Connection conn = null; String str. Url = "jdbc: derby: c: \databases\my. DB; create=true"; try { conn = Driver. Manager. get. Connection(str. Url); } catch (SQLException sqle) { sqle. print. Stack. Trace(); }

Java and Databases SQL Java and SQL

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.

Creating Tables CREATE TABLE table. Name ( col 1 Name type, col 2 Name type ) List of datatypes at: http: //www. w 3 schools. com/sql_datatypes. asp CREATE TABLE Results ( Address varchar(255), Burglaries int ) Note the need to define String max size.

SELECT command SELECT column, column FROM tables in database WHERE conditions ORDER BY things to ordered on SELECT Addresses FROM crime. Tab WHERE crime. Type = burglary ORDER BY city

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 Tab 1 WHERE name LIKE '%[abc]%'

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_Latin 1_General_CP 1_CI_AS Alternatively, if you want to force case sensitivity: WHERE name = 'Bob' COLLATE SQL_Latin 1_General_CP 1_CS_AS

Counting Can include count columns. Count all records: COUNT (*) AS col. For. Answers Count all records in a column: COUNT (column) AS col. For. Answers Count distinct values: COUNT(DISTINCT column. Name) AS col. For. Answers SELECT crime. Type, COUNT(DISTINCT Address) AS Houses. Affected FROM crimes

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: Table 2 Table 1 P_key column. A P_key column. B id 1 A a HH 1 2 B b GG 1 3 C c YY 3 SELECT Table 1. column. A, Table 2. column. B FROM Table 1 JOIN Table 2 ON Table 1. P_Key=Table 2. id A HH A GG C YY

Altering data Two examples using UPDATE: UPDATE Table 1 SET column 1 = ‘string’ WHERE column 2 = 1 UPDATE Table 1 SET column 1 = column 2 WHERE column 3 = 1

SQL Introductory tutorials: http: //www. w 3 schools. com/sql/default. asp

Java and Databases SQL Java and SQL

JDBC SQL Three methods: Statements: Standard, simple, SQL. Prepared. Statements: Compiled SQL statements that are altered to new data through input parameters. Useful, for example, in looped structures. Callable. Statements: for SQL procedures stored in the database. Produce Result. Set objects.

Example: Select Statement st = conn. create. Statement(); Result. Set rs = st. execute. Query ("SELECT a, b, c FROM Table 1"); Three different execution methods: execute. Query : simple SQL queries. execute. Update : 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()

Example: Creating tables String create. Table = "CREATE TABLE Results (" + "Address varchar(255), " + "Burglaries int" + ")"; Statement st = null; try { st = conn. create. Statement(); st. execute (create. Table); } catch (SQLException ex) { ex. print. Stack. Trace(); }

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. set. Auto. Commit(boolean. Auto. Commit); 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.

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. execute. Query( "SELECT name FROM Table 1 WHERE Id LIKE '¬_%' {escape '¬'}"); In some cases the escape occurs in a table name, in which case treat literally by enclosing in [] e. g. [Sheet 1$]

Result. Sets Links to a cursor on the database and converts data to Java types. Result. Set rs = st. execute. Query ("SELECT a, b, c FROM Table 1"); while (rs. next()) { Can also use int i = rs. get. Int("a"); column index number (starting or String s = rs. get. String("b"); 1). or Object o = rs. get. Object("c"); } Use object if unsure, and cast. A new SQL query will close the current Results. Set.

Result. Sets 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. create. Statement( Result. Set. TYPE_SCROLL_SENSITIVE, Result. Set. CONCUR_UPDATABLE, Result. Set. HOLD_CURSORS_OVER_COMMIT); Only need to worry about this if you’re not autocommitting.

Scrollable Result. Set rs. before. First(); rs. after. Last(); rs. next() rs. previous() Looping: while (!rs. is. After. Last()) { Finding number of rows: rs. last(); int number. Of. Rows = rs. get. Row();

Scrollable Result. Set st. set. Fetch. Size(25); Fetch 25 rows at a time. rs. absolute(2); Move to row 2 rs. update. Int(3, 10); Update col 3 of current row. rs. update. Int("Name", 10); Update by col name. also update. Object, update. String, update. Float and others. update. Row(); 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.

Inserting a row rs. move. To. Insert. Row(); rs. update. String(1, "Bob"); rs. update. Object(2, some. Object); rs. insert. Row(); rs. first(); (In general the cursor points at the row used prior to insert. ) conn. commit(); If needed. rs. close();

Database metadata Especially useful in debugging is getting metadata about the database: Database. Meta. Data md = conn. get. Meta. Data(); Result. Set rs = md. get. Tables( null, "%", null); while (rs. next()) { System. out. println(rs. get. String(3)); }

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

Next Lecture XML Practical JDBC