CS 4400 Introduction to JDBC

1
CS 4400Introduction to JDBC
2
JDBC Resources

• Class Website
• Oracle, Java, SQL, JDBC Information
• Using Java with Oracle
• Example JDBC Program
• Sun
• JDBC resource centre
• 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
  (Dont forget to check that the version of driver
  is compatible with the version of the database
  you are using)
• 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
• Signup Required
• PostgresSQL
• http//jdbc.postgresql.org/download.html
• Others
• http//developers.sun.com/product/jdbc/drivers

6
Demo
7
Connecting to Oracle on Acme

• Connect to acme.gatech.edu using your gatech id
• on your command prompt,
• Just type in these commands line by line(!)
• export ORACLE_HOME/u1/oracle/10.2.0
• export ORACLE_SIDpublic
• export ORAENV_ASKNo . oraenv unset ORAENV_ASK
• export LD_LIBRARY_PATHORACLE_HOME/lib
• export CLASSPATH"ORACLE_HOME/jdbcORACLE_HOME/j
  dbc/lib/classes12.zipORACLE_HOME/jdbc/lib/nls_ch
  arset12.zip."
• export LD_LIBRARY_PATH"LD_LIBRARY_PATHLD_LIBRA
  RY_PATH/jdbc/lib"
• export PATHPATHORACLE_HOME/bin
• And type in sqlplus /

8
Connecting to the Oracle 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()

9
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

10
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).

11
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.createStament()
• Stmt.executeUpdate(createUser)
• For database queries, use executeQuery()
• String query "select firstname, lastname from
  user where username\'admin\'"
• ResultSet rs stmt.executeQuery(query)

12
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()
• Resultset rset stmt.executeQuery(query)
• 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()

13
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)

14
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()

15
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()

16
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!