Java Database Connectivity JDBC

1. Java Database Connectivity JDBC • JDBC is an API, containing classes and interfaces in the java programming language, to execute SQL sentences over an external database. • Offers a standard interface for connecting to any database available. • Helps obtaining the data more easily and comfortable in a client-server environments through the Internet/Intranet.

2. What does JDBC do? • Allow a connection to a database server. • Allow sending SQL sentences. • Process the results of these Sentences. • The classes that allow this, are in the package java.sql (it has to be imported)

3. Loading the Driver • It is necessary to load a class with the driver of the Database (This is provided by the DBMS vendor) • Example: Class c = Class.forName(“com.informix.jdbc.IfxDriver"); Class.forName(“com.novell.sql.LDAPDriver”); Class.forName("com.mysql.jdbc.Driver"); • This depends on the database that will be used • Then an instance of the class must be created. c.newInstance();

4. Establishing a Connection Connection con = DriverManager.getConnection ( url,"login", "password"); • In the examples the login and password will be discarded • A Connection Object represents a connection to a database • The class “DriverManager” tries to find a driver to connect to the database represented in the URL.

5. Connection • URL Examples: • jdbc:mysql://localhost/test (for examples) • jdbc:oracle://oraserver • jdbc:odbc:mydatabase • jdbc:informix-sqli://hostname:port/dbname:INFORMIXSERVER=serverName (for CGE databases) • jdbc:ldap://server;baseDN=baseDN;useCleartext=true

6. ...Connection • URL JDBC Syntaxis: • jdbc:<subprotocol>:<subname> • <subprotocol> is the name of the driver or mechanism of the connection. Example: odbc. • <subname> is the identification of the database. The format changes depending of the driver. Example: //server:port/db and parameters

7. Sending SQL sentences • JDBC allows to send any SQL sentence. although this is dependent to the database, the only risk is the incompatibility between different databases.

8. ...Sending SQL Sentences • JDBC provides 3 classes: • “Statement”: This object is used to send simple SQL sentences. It is created by the method: createStatement. • “PreparedStatement”: This object is used for sentences that needs one or more parameter. The sentence is pre-compiled and saved for a future use. • “CallableStatement”: It is used to execute procedures saved in the database.

9. The Statement class • Statement stmt = con.createStatement(); • In this moment the statement exists but doesn't have a SQL sentence to execute. This can be sended using the methods: • executeUpdate(String), used to create/modify tables (there is no results), commonly used for the create,update,delete sentences. • executeQuery(String) to make queries, returns results in an object from the class ResulSet, tipically for a select statement.

10. excuteUpdate Examples • stmt.executeUpdate(“create table students (name varchar(32), id integer, address varchar(40), yearborn integer, ...); • stmt.executeUpdate(“insert into students (‘Valeria Valencia ’, 00998852678, ‘Calle bonita 223 Vitacura Stgo’, 1974, ...); • stmt.executeUpdate(“update students set direccion = ‘?????’ where name = ‘Valeria Valencia’ ”);

11. excuteUpdate Examples • CreateCoffees: Creates a Coffees table in the test database. • CreateSuppliers: Creates a Suppliers table in the test Databes. • DropCoffees: Delete both tables. • MyConnection: encapsulates the connection to the database (to be used for other applications) • InsertCoffees: Inserts rows in the table. • InsertSupliers: Inserts rows in the table. • InsertCoffeesDatos: Inserts rows from data readed from a file. • ListCoffes, ListSuppliers: Show the content (uses executeQuery)

12. executeUpdate return • The executeUpdate method returns an integer value corresponding to the number of rows that the SQL sentences modified. • If it is a creation of a table, the return value is 0 • If it is an insert of a value in a row the value is 1. • The results is interesting when using the SQL sentence with the instruction: “update table set field = <expression> where <condition>

13. excuteQuery Examples • stmt.executeQuery(“select * from students “ + “where yearofborn = 1974”); • The results of a query is received in an ResultSet object. • ResultSet rs = stmt.executeQuery(....); • A ResultSet can be seen as an enumeration of rows that represents the results. • There are adequate methods to go through all the elements of this enumeration and obtain the values of the fields

14. The ResultSet ResultSet rs = stmt.executeQuery(“select name where address like Santiago”); while (rs.next()) { String s = rs.getString(“name”); int y = rs.getInt(“yearofborn”); System.out.println(s+” “+y); } • ResultSet rs contains a collection of rows with the results of the query instruction next advances a pointer which informs in what row are we now. At the beginning this is before the first row, so it is necessary to execute a next() method to point to the first row.

15. The getXXX Instruction • It can be used with two types of parameters: • getXXX(“Name of the field of the table”) example: getString(“name”) • getXXX(number of field of the table) example: getString(1) • getXXX tries to read what is on the field and converts it to the type specified in the “XXX”

16. The Instruction getXXX • Sometimes, even if the SQL data type does not exactly matches the XXX type a conversion is possible ( with getString & getObject almost everything can be retrieved) • In other cases the conversion is possible but with some loss of information (getByte for a numeric or longvarchar) • In other cases it is just impossible (use getLong for Time)

17. Prepared Statements • Everywhere a Statement has been used it is possible to use a PreparedStatement in order to make queries more efficient • An instruction containing a PreparedStatement will be translated to a native SQL statement of the database • Another advanatage is that it allows the usage of parameters • PreparedStatement us = con.prepareSatatement(“update alumnos set comuna = ? where direccion like = ?); • us.setString(1,’Vitacura’) us.setString(2,’Hualtatas’);

18. Prepared Statements: Example PreparedStatement updateSales; String updateString = "update COFFEES " + "set SALES = ? where COF_NAME like ?"; updateSales = con.prepareStatement(updateString); int [] salesForWeek = {175, 150, 60, 155, 90}; String [] coffees = {"Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf"}; int len = coffees.length; for(int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); }

19. Transactions • A transaction consists in one or more sentences that have been executed and then confirmed (commit) or deleted (rolled back) • Auto-commit is pre-set. • if Auto-commit is deactivated, the methods commit or rollback have to be used in a explicit way.

20. Transactions • To use Transactions, the auto-commit must be disabled. • con.setAutoCommit(false) • PreparedStatement ps = ..... • .... • ps.executeUpdate() .... • ps.excecuteUpdate() ... • con.commit();

21. Stored Procedures • Is a group of SQL sentences that are grouped logically in a unit to do a specific work. • Exists in most of the DBMS, but they are dependent to this (The way of how these procedures are written or executed isn't so standard) • Generally receives parameters. • They are “written” with an Update and executed with a Query.

22. An Example To create a Stored procedure String crearSP = “create prodcedure SHOW_SUPPLIERS”+ “ as ”+ “select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME”+ “from SUPPLIERS, COFFEES ”+ “where SUPPLIERS.SUP_ID = COFFEES.SUP_ID” Statement stmt = con.CreateStatement(); stmt.executeQuery(createSP); to call the Stored Procedure CallableStatement cs; cs = con.prepareCall(“{call SHOW_SUPPLIERS}”); ResultSet rs = cs.executeQuery();

23. Using metadata • The metadata is the information about the structure of a databaste or a ResultSet • It is obtained with the method getMetaData() stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int rowCount = 1; while (rs.next()) { System.out.println("Row " + rowCount + ": "); for (int i = 1; i <= numberOfColumns; i++) { System.out.print(" Column " + i + ": "); System.out.println(rs.getString(i)); } System.out.println(""); rowCount++; }

24. Using metadata • Example to know the tables of a database. DatabaseMetaData dbmd = con.getMetaData(); String dbmsName = dbmd.getDatabaseProductName(); ResultSet rs = dbmd.getTableTypes(); System.out.print("The following types of tables are "); System.out.println("available in " + dbmsName + ": "); while (rs.next()) { String tableType = rs.getString("TABLE_TYPE"); System.out.println(" " + tableType); }