Intr to Database Systems
Intr to Database Systems CS 4400
Popular in Course
Popular in ComputerScienence
This 0 page Class Notes was uploaded by Alayna Veum on Monday November 2, 2015. The Class Notes belongs to CS 4400 at Georgia Institute of Technology - Main Campus taught by Staff in Fall. Since its upload, it has received 11 views. For similar materials see /class/234131/cs-4400-georgia-institute-of-technology-main-campus in ComputerScienence at Georgia Institute of Technology - Main Campus.
Reviews for Intr to Database Systems
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 11/02/15
Doww m0 0mm 3930 J f k Oracle JDBC JDBC is an application programming interface that enables database access in Java It consists of a set of classes and interfaces written in Java It allows the programmer to send SQL statements to a database server for execution and retrieve query results for SQL queries Provides for portability across database servers and hardware architectures f KK Interaction with the database using JDBC I Import the JDBC classes Load the JDBC drivers Connect to the database Interact with the database using JDBC Disconnect from the database f f I Developing JDBC Applications I Import the JDBC classes import javasql import oraclejdbcdriver import oraclesqi Load the JDBC drivers DriverManagerregisterDrivernew oraclejdbcdriverOracleDriver Connect to the database OracIeConnection conn 2 OracIeConnection DriverManagergetConnection jdbcorac1eoci8ccdb userpass Interact with the database using JDBC Disconnect from the database connclose f f I Creating JDBC Statements I A JDBC statement object is used to send your SQL statement to the database server A JDBC statement object is associated with an open connection and not any single SQL statement By default the Connection object automatically commits changes after executing each statement If autocommit has been disabled then an explicit commit must be done to save the changes in the database JDBC provides three classes for sending SQL statements to the database server Statement used for SQL statements Without parameters PreparedStatement used When the same statement With possible different parameters is to be executed multiple times it is pre compiled and stored for future use CallableStatement used for executing stored procedures J K Creating JDBC Statements I 0 Statement The following code snippet uses the Connection object con to create a Statement object Statement stmt connc1 eateStatement No SQL statement is associated with it f f KK I Creating JDBC Statements I o PreparedStatement With this subclass of Statement an SQL statement is provided with it when the PreparedStatement object is created and the SQL statement is passed to the database server right away where it is compiled The following code snippet uses the Connection object con to create a parameterized SQL statement with four input parameters PreparedStatement stmt connprepareStatement insert into students values Before a PreparedStatement can be executed we will need to supply values for the parameters which can be done by calling one of the setXXX methods de ned in the class PreparedStatement stmtsetIntlid stmtsetString2name stmtsetString3city stmt setFloat 4salary It can be executed with the following stmtexecuteUpdate f Creating JDBC Statements I o Executing Create Insert Update Statements Create table stmtexecuteUpdate create table scores sid varchar25 not null te1 m varchar210 not null linen0 number4 not null compname varchar215 not null points number4 checkpoints gt 0 f f f I Creating JDBC Statements I o Executing Create Insert Update Statements Insert PreparedStatement stmt2 connprepareStatement insert into scores values termin m is cname Update String query2 update scores set points ns 3 Where sid 7 id and compname 7 and term 2 7 termin and lineno cname is stmt executeUpdatequery2 Delete String query delete scores Where sid 7 id m and term 2 7 termin and lineno 1s stmt executeUpdatequery3 KK f I Creating JDBC Statements I o Executing Select statements Use the method executeQuery which returns its results as a ResuItSet object String query select points compname from scores Where term 2 7 termin and Iinen0 7 Is m and sid PreparedStatement stmt2 connprepareStatementquery3 f 10 f I Creating JDBC Statements I o Executing Select statements stmt2setStringlstudentid ResuItSet rset3 rset3 stmt2executeQuery while rset3next points rset3getIntl if lrset3wasNulI system0utprint points points compmame rset3getString2 if lrset3wasNulI system0utprintln compname comp ame f f I Creating JDBC Statements I o Executing Select statements The bag of tuples resulting from the query are contained in the variable rset3 which is an instance of the ResultSet The ResultSet provides a cursor which can be used to access each row in the result The cursor is initially set just before the rst row and each invocation of the method next causes it to move to the next row and return TRUE if one exists and FALSE if there is no remaining row We can use the getXXX method of the appropriate type to retrieve the attributes of a row for example id rsetgetIntl name rsetgetString2 salary rsetgetFloat3 f 11 12 f I Transactions and JDBC I JDBC allows SQL statements to be grouped together into a single transaction Transaction control is performed by the Connection object default mode is autocommit ie each SQL statement is treated as a transaction We can turn off the autocommit mode with consetAutoCommit false and turn it back on with consetAutoCommit true Once autocommit is off no SQL statement will be committed until an explicit commit is invoked concommit At this point all changes done by the SQL statements will be made permanent in the database f f f I Transactions and JDBC I If we don t want certain changes to be made permanent we can issue conrollback Any changes made since the last commit will be ignored usually rollback is used in combination with Java s exception handling ability to recover from unpredictable errors Example consetAutoCommit false Statement stmt concreateStatement stmtexecuteUpdate INSERT INTO scores VALUES 12345 fall 2001 4400 quiz 1 0 conrollback stmtexecuteUpdate INSERT INTO scores VALUES 12345 fall 2001 4400 quiz 1730 concommit consetAutoCommit true 13 f I Handling Errors with Exceptions I Programs should recover and leave the database in a consistent state In Java statements which are expected to throw an exception or a warning are enclosed in a try block If a statement in the try block throws an exception or warning it can be caught in one of the corresponding catch statements Example PreparedStatement stmt2 connprepareStatement insert into scores values 11111 fall 2001 4400 quiz 1 0 tryl stmt2executeUpdate catch SQLException e Systemoutprintln Score was not added Errorl while e l null Systemoutprintln Message egetMessage e egetNextException f 14 I Simple JDBC Program I import javasq1 import oraclejdbcdriver import 0raclesq1 import javamathBigDecimaI import javautiIMap import javai0 class simple public static void main String args throws SQLException IOException String user pass user readEntry userid pass 2 readEntry passw0rd f 15 16 I Simple JDBC Program I Connect DriverManagerregisterDrivernew oraclejdbcdriverOracleDriver OracIeConnection conn 2 OracIeConnection DriverManagergetConnection jdbc0rac1e0ci8ccdb userpass Statement stmt conncreateStatement ResuItSet rset stmtexecuteQuery select distinct en0enameziphdate from employees while rsetnext System0utprintlnrsetget1ntl rsetgetString2 rsetgetInt3 rsetgetDate4 c0nnclose f KK I Simple JDBC Program I readEntry function to read input string static String readEntryString prompt try StringBuffer buffer new StringBufferO Systemout print prompt Systemout ush int c Systeminread whilec l n 8282 c l 1 bufferappend charc c Systeminread return buffertoStringtrim catch IOException e return f 17 f I Compilation and Execution I o The following library information should be added to your pro le le on ACMEX export FMHOMEusrlocallibframe export ORACLEHOMEusrlocaloracle8l7 export ORACLESIDccdb export LDLIBRARYPATHORACLEJHOMElib export PATHZ PATHzWRECKDIRbinXl 1 usrjaval 2bin export PATHZ PATHFMHOMEbinz export PATHZ PATHIORACLEEOMEbinz 2 JDBC speci c export CLASSPATH ORACLEHOMEjdbc0RACLEllOMEjdbclibclassesl2zip ORACLEHOMEjdbclibnlscharsetl2zip export LDLIBRARYZPATH LDLIBRARYPATHLDLIBRARYPATHjdbClib 18