Java Database Connectivity JDBC is an API containing

Java Database Connectivity 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.

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

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. for. Name(“com. informix. jdbc. Ifx. Driver"); Class. for. Name(“com. novell. sql. LDAPDriver”); Class. for. Name("com. mysql. jdbc. Driver"); This depends on the database that will be used Then an instance of the class must be created. c. new. Instance(); 3

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

Connection URL Examples: jdbc: mysql: //localhost/test (for examples) jdbc: oracle: //oraserver jdbc: odbc: mydatabase jdbc: informixsqli: //hostname: port/dbname: INFORMIXSER VER=server. Name (for CGE databases) jdbc: ldap: //server; base. DN=base. DN; use. Cleart ext=true 5

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

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

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

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

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

excute. Update Examples • Create. Coffees: Creates a Coffees table in the test database. • Create. Suppliers: Creates a Suppliers table in the test Databes. • Drop. Coffees: Delete both tables. • My. Connection: encapsulates the connection to the database (to be used for other applications) • Insert. Coffees: Inserts rows in the table. • Insert. Supliers: Inserts rows in the table. • Insert. Coffees. Datos: Inserts rows from data readed from a file. • List. Coffes, List. Suppliers: Show the content (uses 11 execute. Query)

execute. Update return • The execute. Update 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> 12

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

The Result. Set rs = stmt. execute. Query(“select name where address like Santiago”); while (rs. next()) { String s = rs. get. String(“name”); int y = rs. get. Int(“yearofborn”); System. out. println(s+” “+y); } Result. Set 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. 14

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

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

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

Prepared Statements: Example Prepared. Statement update. Sales; String update. String = "update COFFEES " + "set SALES = ? where COF_NAME like ? "; update. Sales = con. prepare. Statement(update. String); int [] sales. For. Week = {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++) { update. Sales. set. Int(1, sales. For. Week[i]); update. Sales. set. String(2, coffees[i]); update. Sales. execute. Update(); } 18

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

Transactions To use Transactions, the auto-commit must be disabled. con. set. Auto. Commit(false) Prepared. Statement ps =. . ps. execute. Update(). . ps. excecute. Update(). . . con. commit(); 20

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

An Example To create a Stored procedure String crear. SP = “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. Create. Statement(); stmt. execute. Query(create. SP); to call the Stored Procedure Callable. Statement cs; cs = con. prepare. Call(“{call SHOW_SUPPLIERS}”); Result. Set rs = cs. execute. Query(); 22

Using metadata The metadata is the information about the structure of a databaste or a Result. Set It is obtained with the method get. Meta. Data() stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query(query); Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); int number. Of. Columns = rsmd. get. Column. Count(); int row. Count = 1; while (rs. next()) { System. out. println("Row " + row. Count + ": "); for (int i = 1; i <= number. Of. Columns; i++) { System. out. print(" Column " + i + ": "); System. out. println(rs. get. String(i)); } System. out. println(""); row. Count++; } 23

Using metadata Example to know the tables of a database. Database. Meta. Data dbmd = con. get. Meta. Data(); String dbms. Name = dbmd. get. Database. Product. Name(); Result. Set rs = dbmd. get. Table. Types(); System. out. print("The following types of tables are "); System. out. println("available in " + dbms. Name + ": "); while (rs. next()) { String table. Type = rs. get. String("TABLE_TYPE"); System. out. println(" " + table. Type); } 24