New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Intr to Database Systems

by: Alayna Veum

Intr to Database Systems CS 4400

Alayna Veum

GPA 3.81


Almost Ready


These notes were just uploaded, and will be ready to view shortly.

Purchase these notes here, or revisit this page.

Either way, we'll remind you when they're ready :)

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Class Notes
25 ?




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.

Similar to CS 4400 at

Popular in ComputerScienence


Reviews for Intr to Database Systems


Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

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


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Jennifer McGill UCSF Med School

"Selling my MCAT study guides and notes has been a great source of side revenue while I'm in school. Some months I'm making over $500! Plus, it makes me happy knowing that I'm helping future med students with their MCAT."

Steve Martinelli UC Los Angeles

"There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."


"Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.