11CSC 440Database Management SystemsJDBCThis presentation uses slides and lecture notes available fromhttp://www-db.stanford.edu/~ullman/dscb.html#slides2The Project: What You Will Need DBMS SQL (DDL and DML) Host languages (Java, C/C++, Perl, …) Web application servers (optional) SQL editors (optional) – e.g., Toad Tools for user interface (optional):forms, reports, etc.3Course DBMS Oracle Information about accessing the course DBMS:http://www.csc.ncsu.edu/techsupport/technotes/oracle.php24SQL A data-definition and data-manipulation language Can be used for ad-hoc queries on (relational)databases Generic SQL interface: users sit at terminalsand ask queries on database Can be used in programs in some host language Programs access (relational) database by“calls” to SQL statements5Connecting SQL to Host Language Embedded SQL Special SQL statements (not part of hostlanguage) Preprocessor transforms SQL statements intohost-language code Call-level interfaces: SQL/CLI (adaptation of ODBC) JDBC: links Java programs to databases6JDBC Basics Read the tutorial athttp://java.sun.com/docs/books/tutorial/jdbc/basics/37Two-Tier ModelData sourceApplicationJDBC Driver8Steps to Use JDBC Loading a driver for our db system Creates a DriverManager object Establishing a connection to database Creates instance of a Connection object Using the connection to: Create statement objects Place SQL statements “in” these objects Bind values to SQL statement parameters Execute the SQL statements Examine results tuple-at-a-time9DBMS Driver Specific information you need to know: see thesample JDBC program and the project FAQ on: Driver for the course DBMS Using the driver (add to classpath) Driver specifics for your programs410firstExample.java//"Loading"the " driver:Class.forName("oracle.jdbc.driver.OracleDriver");//Establishing"a"connection:Connection conn =DriverManager.getConnection(jdbcURL,user, passwd);11StatementsTwo JDBC classes: Statement: object that can acceptand execute a string that is a SQLstatement PreparedStatement: object that hasan associated SQL statement readyto execute12Using Statements in JDBC Creating statements: using methods in theConnection class Executing statements: executeUpdate: for database modifications executeQuery: for database queries513firstExample.java// Create a statement object that will be sending your// SQL statements to the DBMS:Statement stmt = conn.createStatement();//"Create"the"COFFEES"table:stmt.executeUpdate("CREATE TABLE COFFEES " + "(COF_NAME VARCHAR(32), SUP_ID INTEGER, " + "PRICE FLOAT, SALES INTEGER, TOTAL INTEGER)");//"Populate"the"COFFEES"table:stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES ('Colombian', 101, 7.99, 0, 0)");//"Get"data"from"the"COFFEES"table:ResultSet rs = stmt.executeQuery("SELECT COF_NAME,PRICE FROM COFFEES");14ResultSet An object of type ResultSet is like a cursor Method “next” advances cursor to next tuple: The first time next() returns the first tuple If no more tuples then next() returns FALSE Accessing components of tuples: Method getX(name), where X is some type andname is an attribute name15firstExample.java//"Now"rs"contains"the"rows " of"coffees"and"prices"from//"the"COFFEES"table."To"access"the"data,"use" t hemethod//"NEXT"to"access"all"rows"in"rs,"one"row"at"a"timewhile (rs.next()) {String s = rs.getString("COF_NAME");float n = rs.getFloat("PRICE");System.out.println(s + " " + n);}616JDBC URL (Oracle) The general form of a URL is jdbc:oracle:<drivertype>:<username/password>@<database>  The <drivertype> is one of thin Use Java sockets Recommended for our class oci Use Oracle OCI calls Works through SQL *Net kprb Mainly for stored procedures17Notes The <username/password> is either empty or ofthe form <username>/<password> A URL likejdbc:oracle:thin:/@mydatabase  has an empty username and password whereas thisURLjdbc:oracle:thin:@mydatabase  does not specify a username and password. Whenusing this form the username and password must beprovided some other way.18JDBC URL (Oracle) The <database> description depends on the driver type. (thin or oci driver and not bequeath) the databasedescription is one of the following: Service approach //<host>:<port>/<service> SID approach <host>:<port>:<SID> Discouraged; Oracle will stop supportingthis form. <TNSName> TNS (Transparent Network Substrate) You need to define the parameters for the TNSname719Example In our class:jdbc:oracle:thin:@//orca.csc.ncsu.edu:1521/ORCL.WORLDOrjdbc:oracle:thin:@orca.csc.ncsu.edu:1521:ORCL Driver type: Hostname: Port: Service: SID:20JDBC Object Summary Basic JDBC objects: DriverManager (DataSource is used instead inmost applications) Connection Abstract representation of a DBMS session Statement Can be used to execute queries and updatethe database ResultSet (= cursor) Used to hold answers to database queries21In-Class Exercises - sqlplus login to remote-linux.eos.ncsu.edu using yourunity account. add oracle10g run sqlplus Username: <unity ID>@orcl.world Password: 9 digit student ID check a few tables tabs: all tables the user has session_privs: privileges in this session session_roles: roles in this session Useful SQL statement: SELECT * FROM <table-name>;822In-Class Exercises - JDBC Preparation (see jdbc-prep.txt on course website) Install JDK Download JDBC driver Configure CLASSPATH Test running javac and java Should be done before class23In-Class Exercises - JDBC Ex. 1 Open and read firstExample.java Recognize critical steps just discussed in class Edit the program to put in your username andpassword Compile and run Report the output24In-Class Exercises - JDBC Ex. 2 Use sqlplus to connect the Oracle server Check tabs Check table COFFEES Useful SQL statements: SELECT * FROM <table-name>; DROP TABLE <table-name>;925In-Class Exercises - JDBC Ex. 3: The current program uses the service formatfor URL Change it to SID format Redo Ex 1 & 2 Useful SQL statements: SELECT * FROM <table-name>; DROP TABLE <table-name>;26In-Class Exercises - JDBC Ex. 4: It’s annoying to have to drop table COFFEESthrough sqlplus. Modify the program to drop table COFFEES