Chapter 5: Advanced SQL

1. Chapter 5: Advanced SQL

2. Chapter 5: Advanced SQL • Accessing SQL From a Programming Language • JDBC and ODBC

3. ODBC and JDBC • API (application-program interface) for a program to interact with a database server • Application makes calls to • Connect with the database server • Send SQL queries to the database server • Fetch tuples of result one-by-one into program variables • ODBC (Open Database Connectivity) works with C, C++, C#, and Visual Basic • JDBC (Java Database Connectivity) works with Java

4. JDBC • JDBC is a Java API for communicating with database systems supporting SQL • JDBC supports a variety of features for querying and updating data, and for retrieving query results • JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes • Model for communicating with the database: • Open a connection • Create a “statement”/ “PreparedStatement” object • Execute queries using the Statement/PreparedStatement object to send queries and fetch results • Exception mechanism to handle errors

5. Common JDBC Classes and Methods • DriverManager • getConnection(url, userID, passwd) • Connection • createStatement(), prepareStatement(string), close() • Statement • executeUpdate(string), executeQuery(string), close() • PreparedStatement • setInt(pos, int), setString(pos, string), executeUpdate(), executeQuery() • ResultSet • getString(Attr), getInt(Attri), getFloat(Attri), next(), first(), last()

6. Using Methods • To open a connection to the database (to create a Connection object) • Connection conn = DriverManager.getConnection(url, userID, passwd); • To create a Statement object for shipping statements to the database • Statement stmt = conn.createStatement(); • To execute a statement stmt.executeUpdate(string); //for update, insert, delete, create table stmt.executeQuery(string); //for a query that returns a result set

7. Using Method (Cont.) • To create a PreparedStatement object for shipping prepared statements to the database PreparedStatement.pStmt = conn.prepareStatement(string); • To set values in a prepared statement pStmt.setInt(pos, int); // pStmt.setString(pos, string); // • To execute a prepared statement pStmt.executeUpdate(); // pStmt.executeQuery(); //

8. Using Method (Cont.) • To create a ResultSet object ResultSet rset = stmt.executeQuery(string); ResultSet rset = pStmt.executeQuery(); • To retrieve the result of a query ResultSet • rset.getString(Attr); • rset.getInt(Attri); • rset.getFloat(Attri); • rset.next(); //get the next row • rset.first(); //get the first row • rset.last(); //get the last row

9. JDBC Code import java.sql.*; public class CreateCoffees { public static void main(String args[]) { String url = "jdbc:mysql://web2.eeecs.qub.ac.uk/userid"; Connection con; Statement stmt; try { Class.forName ("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection( url, userid, passwd); stmt = conn.createStatement(); stmt.executeUpdate("Create Table Coffees (….)"); stmt.close(); conn.close(); } catch (SQLException sqle) { System.out.println("SQLException : " + sqle); } }

10. JDBC Code (Cont.) • Update to databasetry { stmt.executeUpdate( "insert into instructor values(’77987’, ’Kim’, ’Physics’, 98000)");} catch (SQLException sqle){ System.out.println("Could not insert tuple. " + sqle);} • Execute query and fetch and print results ResultSet rset = stmt.executeQuery( "select dept_name, avg (salary) from instructor group by dept_name");while (rset.next()) { System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));}

11. JDBC Code Details • Getting result fields: • rs.getString(“dept_name”) and rs.getString(1) equivalent if dept_name is the first argument of select result. • Dealing with Null values • int a = rs.getInt(“a”); if (rs.wasNull()) Systems.out.println(“Got null value”);

12. Prepared Statement • PreparedStatement pStmt = conn.prepareStatement( "insert into instructor values(?,?,?,?)");pStmt.setString(1, "88877");pStmt.setString(2, "Perry");pStmt.setString(3, "Finance");pStmt.setInt(4, 125000);pStmt.executeUpdate();pStmt.setString(1, "88878");pStmt.executeUpdate(); • A prepared statement is an SQL statement template with some slots to be filled in with values • Always use prepared statements when taking an input from the user and adding it to a query

13. End of Chapter