CENG 316 Software Engineering Lab1: JDBC

1
CENG 316 Software EngineeringLab1 JDBC

• Izmir Institute of Technology
• Department of Computer Engineering

2
JDBC

• The JDBC (Java Database Connectivity) API defines
  interfaces and classes for writing database
  applications in Java by making database
  connections.
• Using JDBC you can send SQL, PL/SQL statements to
  almost any relational database.
• JDBC is a Java API for executing SQL statements
  and supports basic SQL functionality.
• It provides RDBMS access by allowing you to embed
  SQL inside Java code.

3
JDBC

• JDBC helps you to write java applications that
  manage these three programming activities
• Connect to a data source, like a database
• Send queries and update statements to the
  database
• Retrieve and process the results received from
  the database in answer to your query

4
JDBC Architecture

• Java application calls the JDBC library.
• JDBC loads a driver which talks to the database.

5
Eclipse JDBC setup

• Install driver
• Download Oracle JDBC driver ("ojdbc14.jar") from
  course web page
• Add "ojdbc14.jar" to "C\programs\Java\jdk1.6.0_1
  1\jre\lib"
• Add ojdbc14.jar to Eclipse project
• Project ? Properties ? Java Build Path ?
  Libraries ? Add External JARs

6
JDBC Steps

• Connect to database
• Query database ( or insert / update / delete )
• Process results
• Close connection to database

7
Connect to Database

• Load JDBC driver
• Class.forName("oracle.jdbc.driver.OracleDriver")
• Make connection
• Connection connection DriverManager.getConnectio
  n(url, username, password)
• url jdbcsubprotocolhostport/database
• Url "jdbcoraclethin_at_//127.0.0.11521/xe"

8
Query Database

• Create statement
• Statement statement connection.createStatement()
  
• statement object sends SQL commands to database
• Methods
• executeQuery() for SELECT statements
• executeUpdate() for INSERT, UPDATE, DELETE,
  statements
• Send SQL statements
• statement.executeQuery("SELECT ")
• statement.executeUpdate("INSERT ")

9
Process Results

• Result of a SELECT statement (rows/columns)
  returned as a ResultSet object
• ResultSet rs statement.executeQuery("SELECT
  FROM employees")
• Step through each row in the result
• rs.next()
• Get column values in a row
• String first_name rs.getString("first_name")
• int employee_id rs.getInt("employee_id")

10
Print Table

• ResultSet rs statement.executeQuery("SELECT
  FROM employees")
• while (rs.next())
• int employee_id rs.getInt(1)
• String first_name rs.getString("first_name")
• String last_name rs.getString("last_name")
• String email rs.getString(4)
• String phone_number rs.getString("phone_number")
  
• System.out.println(employee_id " " first_name
  " " last_name " " email " "
  phone_number)

11
Insert a row

• String str "insert into employees
  (employee_id,first_name,last_name,email,phone_numb
  er,hire_date, job_id, salary,department_id)
  values (207,'James','King','jking_at_itpro.org','515.
  123.4679','21-SEP-99', 'IT_PROG', '20000',90')
• int rows statement.executeUpdate(str)
• // Returns number of rows in table

12
Close Connection

• Close the ResultSet object
• rs.close()
• Close the Statement object
• statement.close()
• Close the connection
• connection.close()

13

• import java.sql.
• public class TestDB
• public static void main (String args) throws
  ClassNotFoundException, SQLException
• Class.forName("oracle.jdbc.driver.OracleDriver")
  
• String username "HR"
• String password "HR"
• String url "jdbcoraclethin_at_//127.0.0.11521
  /xe"
• Connection connection DriverManager.getConnecti
  on (url, username, password)
• Statement statement connection.createStatement(
  )
• ResultSet rset statement.executeQuery("sel
  ect from employees")
• while (rset.next())
• System.out.println("ID
  "rset.getInt("employee_id")" Name
  "rset.getString("first_name"))

14
Transactions

• Currently every executeUpdate() is finalized
  right away
• Sometimes want a set of updates to all fail or
  all succeed
• Treat both inserts as one transaction
• Transaction
• Used to group several SQL statements together
• Either all succeed or all fail

15
Transactions

• Commit
• Execute all statements as one unit
• Finalize updates
• Rollback
• To rollback the changes done in a transaction
  give rollback statement.
• Rollback restore the state of the database to the
  last commit point.
• Savepoint
• Specify a point in a transaction to which later
  you can roll back.

16
Transactions in JDBC

• Disable auto-commit for the connection
• connection.setAutoCommit(false)
• Call necessary executeUpdate() statements
• Commit or rollback
• connection.commit()
• connection.rollback()

17
Example

• int rs1 stmt.executeUpdate("insert into regions
  (region_id,region_name) values (5,'Australia')")
• Savepoint a conn.setSavepoint("A")
• int rs2 stmt.executeUpdate("insert into regions
  (region_id,region_name) values (6,'Antartica')")
• conn.rollback(a)
• conn.commit()

18
Labwork 1

• Write a Java program that creates a new table for
  employees holding the new raised salaries for
  each employee.
• The new employee table (new_employees) will be a
  copy of the old one except for the new raised
  salary values
• The employees in the departments Administration
  and IT will have 20 increase in salary, and
  10 increase will be applied to the other
  employees
• This new table should keep the new salary values
• The modifications should be performed as one
  transaction