Java Database Connectivity

Java Database Connectivity

Transcript

1. JDBC Jussi Pohjolainen
2. JDBC Intro • Java Database Connectivity in Java since 1.1 – Platform independent, Database independen • Wide range of data sources possible: – SQL, spreadsheets, flat files • JDBC API – Estabish a connection to database – Execute SQL – Process the results
3. Drivers • To access a database, you need a driver • To code in Java is always the same, changing the driver changes the connection to different database. • Lot’s of drivers for different databases: MySQL, Text, MS SQL Server..
4. Driver Types https://ejbvn.wordpress.com/category/week-2-entity-beans-and-message-driven-beans/day-09-using-jdbc-to-connect-to-a-database/
5. Driver Types • JDBC Drivers are divided into four categories – Type 1 that calls native code of the locally available ODBC driver. – Type 2 that calls database vendor native library on a client side. – Type 3, the pure-java driver that talks with the server-side middleware that then talks to database. – Type 4, the pure-java driver that uses database native protocol. • In most cases you can access the same database with four different type of drivers • List of drivers: – http://developers.sun.com/product/jdbc/drivers
6. JDBC Classes http://docstore.mik.ua/orelly/linux/sql/ch14_01.htm
7. Versions • JDBC was first released in 1997 – Forward only, no updatable result sets • JDBC 2.0 – Scrollable an updatable result sets • JDBC 3.0 was included in J2SE 1.4 – More metadata, named parameters in callable statements, some new datatypes • JDBC 4.0, Java SE 6 – Easier driver loading, RowSet for offline use, XML-support – JDBC 4.1, Java SE 7 – JDBC 4.2, Java SE 8
8. To use JDBC 1. Register driver 2. Access database 3. Do some SQL magic 4. Handle result 5. Close the connection
9. public String jdbcConnection() { String result = ""; try { // 1. Register driver. Driver String is given to you by the driver // documentation. Driver (.jar) must be in classpath! Class.forName("com.mysql.jdbc.Driver"); // 2. Connect to database. Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "username", "password"); // 3. Some SQL Magic. Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery("SELECT * FROM Clients"); // 4. Handle Result while (rs.next()) { result += rs.getString("Firstname") + "<br>"; } // 5. Close the connection rs.close(); statement.close(); conn.close(); } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } return result; }
10. About ResultSet rs.next(); rs.next(); rs.next(); // Prints "Williams" System.out.println(rs.getString("Last Name"));
11. public String jdbcConnection() { String result = ""; Connection conn = null; Statement statement = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost/test"); statement = conn.createStatement(); rs = statement.executeQuery("SELECT * FROM Clients"); } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } finally { try { if(rs != null) { rs.close(); } if(statement != null) { statement.close(); } if(conn != null) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } The proper way of closing connection.
12. public String jdbcConnection() { String result = ""; Connection conn = null; Statement statement = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost/test"); statement = conn.createStatement(); rs = statement.executeQuery("SELECT * FROM Clients"); } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } finally { try { // if(rs != null) { // rs.close(); // } if(statement != null) { statement.close(); } if(conn != null) { conn.close(); } } catch(SQLException e) { e.printStackTrace(); } } return result; } A ResultSet object is automatically closed when the Statement object that generated it is closed
13. public String jdbcConnection() { String result = ""; try { Class.forName("com.mysql.jdbc.Driver"); // Java 7 autocloseable! try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test")) { // Java 7 autocloseable! try(Statement statement = conn.createStatement()) { ResultSet rs = statement.executeQuery("SELECT * FROM Clients"); while (rs.next()) { result += rs.getString("Firstname") + "<br>"; } } } } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } return result; } Java 7 features autocloseable which closes the connection in right manner!
14. public String jdbcConnection() { String result = ""; try { Class.forName("com.mysql.jdbc.Driver"); // Java 7 autocloseable! try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test")) { // Java 7 autocloseable! try(Statement statement = conn.createStatement()) { ResultSet rs = statement.executeQuery("SELECT * FROM Clients"); ResultSetMetaData rsmd = rs.getMetaData(); int numCols = rsmd.getColumnCount (); result = "<table>"; while (rs.next()) { result += "<tr>"; for (int i=1; i<=numCols; i++) { result += "<td>" + rs.getString(i) + "</td>"; } result += "</tr>"; } result += "</table>"; } } } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } return result; } If you don't know the column names you can use ResultSetMetaData
15. DERBY
16. Apache Derby • Apache Derby is relational database developed by Apache – Really small, only 2.6 MB for base engine and JDBC driver – Supports ANSI/ISO SQL – Supports JDBC, ODBC, Perl, PHP, CLI • Oracle distributes Apache Derby under the name of Java DB. It's included in JDK and in Glassfish! • ij – app for giving SQL commands to database
17. Derby Path
18. Set Path to bin/
19. Create DERBY_HOME
20. ij
21. Mac OS X Sierra export PATH=$PATH:/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/ Home/db/bin export DERBY_HOME=/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/ Home/db/ export JAVA_HOME=$(/usr/libexec/java_home)
22. Embedded vs Network Server • 1) Embedded – Database in same process than the "client" – Database files are locked to the client – Only one app accessing the data • 2) Network mode – Database runs in it's own process – Several clients can access the database over the network
23. 1) Creating Embedded Database • Create directory for your database • Go to this working directory and give command ij • Create database in the working directory – CONNECT 'jdbc:derby:mydatabase;create=true'; • Give SQL commands – CREATE TABLE CUSTOMERS (....); – INSERT INTO CUSTOMERS VALUES (...); • And exit when ready – exit;
24. 1) Embedded Database Client import java.sql.*; import java.io.*; public class DerbyJdbc { public static void main(String [] args) { String result = ""; String driverUrl = "org.apache.derby.jdbc.EmbeddedDriver"; String connectionUrl = "jdbc:derby:path/to/mydatabase;create=true"; String sql = "SELECT * FROM CUSTOMERS"; String columnName = "name"; try { Class.forName(driverUrl); try (Connection c = DriverManager.getConnection(connectionUrl)) { try(Statement statement = c.createStatement()) { ResultSet rs = statement.executeQuery(sql); while (rs.next()) { result += rs.getString(columnName) + "\n"; } } } } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } System.out.println(result); } }
25. 1) Embedded Derby Driver • When running your app the Derby Driver (jar) is not by default in classpath • Windows – java -cp .;%DERBY_HOME%\lib\derby.jar DerbyJdbc • Mac OS X – java -cp .:$DERBY_HOME/lib/derby.jar DerbyJdbc
26. 2) Creating Server DB • Start Network Server – Windows • java -jar %DERBY_HOME%\lib\derbyrun.jar server start – Mac • java -jar $DERBY_HOME/lib/derbyrun.jar server start • Notice that the directory where you start your server is important. – All the databases are saved to the dir where you started the network server • Once your network server runs, open another terminal window and start ij and connect & create database – CONNECT 'jdbc:derby://localhost:1527/seconddb;create=true'
27. 2) Creating Client • Change driver – org.apache.derby.jdbc.ClientDriver • Change connection url • jdbc:derby://localhost:1527/seconddb;create=true – When running, use different jar in classpath – Windows • java -cp .;%DERBY_HOME%\lib\derbyclient.jar DerbyJdbc – Mac OS X • java -cp .:$DERBY_HOME/lib/derby.jar DerbyJdbc
28. 2) Client import java.sql.*; import java.io.*; // java -cp .:$DERBY_HOME/lib/derbyclient.jar DerbyJdbc public class DerbyJdbc { public static void main(String [] args) { String result = ""; String driverUrl = "org.apache.derby.jdbc.ClientDriver"; String connectionUrl = "jdbc:derby://localhost:1527/seconddb;create=true"; String sql = "SELECT * FROM CUSTOMERS"; String columnName = "name"; try { Class.forName(driverUrl); try (Connection c = DriverManager.getConnection(connectionUrl)) { try(Statement statement = c.createStatement()) { ResultSet rs = statement.executeQuery(sql); while (rs.next()) { result += rs.getString(columnName) + "\n"; } } } } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } System.out.println(result); } }
29. Transactions • If you want to commit several sql – commands into one: – conn.setAutoCommit(false); – // do some sql – conn.commit(); – conn.rollback();
30. Prepared Statements • Prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency • Simple way to protect against SQL injection String sql = "UPDATE table1 set one = ?, two = ?"; PreparedStatement preStmt = con.prepareStatement(sql); preStmt.setInt(1, 123); preStmt.setString(2, "myNewValue2"); preStmt.executeUpdate();
31. Scrollable Resultset Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery(SQLQuery);
32. Values • TYPE_FORWARD_ONLY (default) • TYPE_SCROLL_INSENSITIVE • TYPE_SCROLL_SENSITIVE – all updates happens immediately • CONCUR_READONLY • CONCUR_UPDATABLE
33. Methods for Scrollable ResultSet • next() • previous() • beforeFirst() • afterLast() • absolute(int x)
34. Update Result • It’s possible to update the result using methods – updateString() – updateInt() – updateFloat().. • Once updated, call updateRow(), which will move the result to database
35. Example: updateString while(rs.next()){ System.out.println(rs.getString("Firstname")); } rs.previous(); rs.updateString("Firstname", "Max"); rs.updateRow();
36. Example Adding and Deleting a Row rs.moveToInsertRow(); rs.updateString("firstname", "Jack"); rs.updateString("lastname", "Smith"); rs.updateInt("idnumber", 20); rs.insertRow(); rs.last(); rs.deleteRow();