JDBC: A Java Library for Accessing Relational Databases, Slides of Computer Engineering and Programming

Download JDBC: A Java Library for Accessing Relational Databases and more Slides Computer Engineering and Programming in PDF only on Docsity! JDBC Docsity.com • Types of databases --Hierarchical --Relational --Object Relational Background: databases Docsity.com SQL Basics: Structure of a SQL Statement • Queries: SELECT statements SELECT columns FROM table; Or if we wish not to select all columns: SELECT columns FROM table WHERE expression Docsity.com SQL Basics: Structure of a SQL Statement • Queries: SELECT statements SELECT FirstName, LastName FROM USER WHERE ID = 2; Docsity.com SQL Basics: Structure of a SQL Statement • Updates: UPDATE statements UPDATE table SET column = value; Example: UPDATE table SET LastName = „Jones‟ WHERE ID = 2; Docsity.com JDBC Introduction  JDBC provides a standard library for accessing relational databases – API standardizes Way to establish connection to database Approach to initiating queries Method to create stored (parameterized) queries The data structure of query result (table) – Determining the number of columns – Looking up metadata, etc. – API does not standardize SQL syntax – JDBC class located in java.sql package  Note: JDBC is not officially an acronym; unofficially, “Java Database Connectivity” is commonly used Docsity.com On-line Resources  Sun’s JDBC Site – http://java.sun.com/products/jdbc/  JDBC Tutorial – http://java.sun.com/docs/books/tutorial/j dbc/  List of Available JDBC Drivers – http://industry.java.sun.com/products/jdb c/drivers/ Docsity.com JDBC Drivers  JDBC consists of two parts: – JDBC API, a purely Java-based API – JDBC Driver Manager,which communicates with vendor-specific drivers that perform the real communication with the database. Database JDBC Driver Manager Java Application JDBC API JDBC Driver API Vendor Specific JDBC Driver Vendor Specific ODBC Driver JDBC-ODBC Bridge Database Docsity.com JDBC: Details of Process, cont. 3. Establish the Connection String username = "jay_debesee"; String password = "secret"; Connection connection = DriverManager.getConnection(oracleURL, username, password); Docsity.com JDBC: Details of Process, cont. 4. Create a Statement Statement statement = connection.createStatement(); 5. Execute a Query String query = "SELECT col1, col2, col3 FROM sometable"; ResultSet resultSet = statement.executeQuery(query); – To modify the database, use executeUpdate, supplying a string that uses UPDATE, INSERT, or DELETE – Use statement.setQueryTimeout to specify a maximum delay to wait for results Docsity.com JDBC: Details of Process, cont. 6. Process the Result while(resultSet.next()) { System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3)); } – First column has index 1, not 0 – ResultSet provides various get methods that take a column index or name and returns the data 7. Close the Connection connection.close(); – As opening a connection is expensive, postpone this step if additional database operations are expected Docsity.com JDBC Basics: Statements • After you have a connection, you need to create a statement. • There are three alternatives, each with plusses and minuses. Statement—used for a query that will be executed once. PreparedStatement—used for a query that will be executed multiple times CallableStatement—used for a query that executes a stored procedure. Docsity.com JDBC Basics: Statement • The Statement object is the easiest to work with. • The Statement object is the least efficient. String query = “SELECT * FROM MYTABLE WHERE ID = 2”; Connection con = DriverManager.getConnection( url, user, pass ); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( query ); Docsity.com JDBC Basics: PreparedStatement • The PreparedStatement object requires more work. • The PreparedStatement object is the most efficient. • The query contains a question mark that is replaced. String query = “SELECT * FROM MYTABLE WHERE ID = ?”; Connection con = DriverManager.getConnection( url, user, pass ); PreparedStatement pstmt = con.prepareStatement( query ); pstmt.setString( 1, 494 ); ResultSet rs = pstmt.executeQuery(); This line substitutes 494 for the first question mark in the query. Docsity.com JDBC Basics: ResultSet • No matter which kind of statement you choose, the ResultSet object is used the same way. • As with the Connection object, you must close your ResultSet! Docsity.com try { String output = null; String query = “SELECT username from MYTABLE where pass=„foo‟ ”; Connection con = DriverManager.getConnection( url, us, pass); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( query ); while( rs.next() ) { output = rs.getString( “username” ); } rs.close(); stmt.close(); con.close(); } catch( SQLException sql ) { System.out.println( “…………………” ); } You must close these three items, in the reverse order that you opened them! Docsity.com