CS 4400 Introduction to JDBC

1
CS 4400Introduction to JDBC

• Jeremy Rogers
• jrogers_at_cc
• Nov. 1, 2006

2
JDBC Resources

• Class Website
• Oracle, Java, SQL, JDBC information
• Using Java with Oracle (PDF)
• Example JDBC Program
• Sun
• JDBC resource center
• API Reference
• People!
• Classmates and Friends
• TA's and Teachers

3
JDBC What it is

• 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

4
Steps for JDBC

• Load JDBC Drivers
• Connect to Database
• Send Queries
• Handle Results
• Close Connection

5
JDBC Drivers

• Every database uses a different JDBC driver
• Oracle
• http//www.oracle.com/technology/software/tech/jav
  a/sqlj_jdbc/
• htdocs/jdbc_10201.html (signup required)
• mySQL
• http//dev.mysql.com/downloads/connector/j/5.0.htm
  l
• PostgresSQL
• http//jdbc.postgresql.org/download.html
• Others
• http//developers.sun.com/product/jdbc/drivers

6
Connecting to the Database

• Import the JDBC classes
• import java.sql.
• import oracle.jdbc.pool.OracleDataSource
• Create an OracleDataSource and set URL
• OracleDataSource ods new OracleDataSource()
• ods.setURL("jdbcoraclethin" user "/"
  password "_at_" "acme.gatech.edu1525public")
• put this into a try-catch(ClassNotFoundError)
  block
• Connect to the database
• Connection conn ods.getConnection()
• Interact with the database using JDBC
• Disconnect from the database
• conn.close()

7
Statements

• 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
• JDBC provides three classes for sending SQL
  statements to the database server
• Statement - used for standard SQL statements
• PreparedStatement - used for statements that have
  possible different parameters to be executed
  multiple times (pre-compiled and stored for
  future use)
• CallableStatement - used for executing stored
  procedures

8
More on Statements

• By default, the Connection object automatically
  commits changes after executing each statement
• If auto-commit has been disabled, then an
  explicit commit must be done to save the changes
  in the database
• Connection conn ods.getConnection()
• Statement stmt conn.createStatement ()
• No SQL is associated with the Statement (yet).

9
Query and Update

• For Create, Update, Insert, Delete use
  executeUpdate()
• String createuser "create table user ("
  "username varchar(8) not null," "firstname
  varchar(10) not null," "lastname varchar(10)
  not null," "password varchar(12) not null)"
• Statement stmt conn.executeUpdate(createuser)
• For database queries, use executeQuery()
• String query "select firstname, lastname from
  user where username\'admin\'"
• Statement stmt conn.createStatement(query)

10
Save time with PreparedStatements

• PreparedStatements give you flexibility in your
  queries without having to directly manipulate
  them every time.
• Using Statement
• String query "select firstname, lastname from
  user where username\'" uname "\' and id\'"
  ident "\'"
• Statement stmt conn.createStatement(query)
• Resultset rset stmt.executeQuery()
• Using PreparedStatement
• String query2 "select firstname, lastname from
  user where username? and id?"
• PreparedStatement pstmt conn.prepareStatement(que
  ry2)
• pstmt.setString(1, uname)
• pstmt.setInt(2, ident)
• ResultSet rset2 pstmt.executeQuery()

11
Handling ResultSets

• Query results are returned as a ResultSet
• A ResultSet is a Vector, accessible via iterators
• ResultSet rset pstmt.executeQuery()
• while ( rset.next() ) //go to next row
• // access by column name
• fname rset.getString("firstname")
• if ( !rset.wasNull() ) //end of results
• system.out.print("Name" fname)
• // access by column number
• lname rset3.getString(2)
• if ( !rset3.wasNull() )
• system.out.println(" " lname)

12
JDBC Transactions

• We can treat multiple SQL statements like a
  single transaction
• The Connection object controls transactions
• con.setAutoCommit(true)
• con.setAutoCommit(false)
• con.commit()
• con.rollback()

13
Error Handling

• SQL queries are "Risky Behavior".
• When you take risks, you want a safety net.
• PreparedStatement stmt2 conn.prepareStatement("i
  nsert into user values ('jfrost','Jack','Frost','1
  c3c07d') ")
• try
• stmt2.executeUpdate()
• catch (SQLException sqle)
• System.out.println("Error - Score was not added
  ")
• while (sqle ! null)
• System.out.println("Message "
  sqle.getMessage())
• sqle sqle.getNextException()

14
Practicing with SQL

• Access Acme (use SSH)
• Follow the setup instructions from the website.
• If you can't access your .profile, then follow
  these steps
• create a file named cs4400 (Ex pico cs4400)
• insert the content from .profile to cs4400.
• make cs4400 executable by typing chmod ux
  cs4400
• execute cs4400 by typing ./cs4400
• type sqlplus to practice your SQL!

15
(No Transcript)