Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Java Database Connectivity

Java Database Connectivity

Java Database Connectivity

Avatar for Jussi Pohjolainen

Jussi Pohjolainen

October 20, 2016
Tweet

More Decks by Jussi Pohjolainen

Other Decks in Technology

Transcript

  1. 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
  2. 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..
  3. 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
  4. 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
  5. To use JDBC 1. Register driver 2. Access database 3.

    Do some SQL magic 4. Handle result 5. Close the connection
  6. 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; }
  7. 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.
  8. 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
  9. 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!
  10. 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
  11. 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
  12. ij

  13. 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
  14. 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;
  15. 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); } }
  16. 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
  17. 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'
  18. 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
  19. 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); } }
  20. Transactions • If you want to commit several sql –

    commands into one: – conn.setAutoCommit(false); – // do some sql – conn.commit(); – conn.rollback();
  21. 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();
  22. Values • TYPE_FORWARD_ONLY (default) • TYPE_SCROLL_INSENSITIVE • TYPE_SCROLL_SENSITIVE – all

    updates happens immediately • CONCUR_READONLY • CONCUR_UPDATABLE
  23. 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
  24. 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();