JDBC

1
JDBC
2
What is JDBC

• JDBC is an acronym for
• Java Data Base Connectivity.
• It allows java program to connect to any database

3
How to use JDBC

• Step1 First make a database
• Step2 Create tables
• Step3 Create data source

4
JDBC library in Java

• To use JDBC in java, you need
• Connection
• Statement
• ResultSet
• DriverManager
• All these classes are provided in pacakkage
  java.sql..

5
Steps to use JDBC in Java

• Initialize variables
• Register or Load Driver
• Open a Connection
• Execute Sql Statement
• Close the Connection

6
Initialize Variables

• You need following variables
• Connection con
• Statement stmt
• Initialize variable to value null
• Connection con null
• Statement stmt null

7
Load Driver

• To load driver you need to use
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
• sun.jdbc.odbc.JdbcOdbcDriver is the name of
  driver.
• Class.forName() function loads the driver in
  memory.

8
Create Connection

• After loading driver, you need to create a
  connection
• Create connection means, you need to connect your
  java program with the database.
• To connect to a database you must configure the
  DSN first
• DSN is an acronym for Data Source Name

9
What is DSN

• DSN is an acronym for Data Source Name
• DSN act as an interface between your java
  application and Database
• To create DSN
• Open Control Panel
• Administrative Tools
• ODBC

10
Create Connection Contd

• After loading driver and creating DSN, next step
  is to create a connection.
• To create a connection
• conn DriverManager.getConnection("jdbcodbccdb"
  )
• DriverManager class getConnection functions takes
  DSN as input argument
• And returns an object of type Connection

11
Execute Query

• After connection is created successfully, you
  need to execute query
• There are 2 types of query
• That gets record from database
• That insert or update or delete record

12
Insert or update or delete record

• To insert record in database, use method
  executeUpdate of statement class.
• int i stmt.executeUpdate("INSERT INTO personal
  VALUES('"lname"','"fname"','"sex"','"salary
  "','"exp"')")
• This method takes as input Insert query
  statemetn.
• This method returns 1 (success) or 0 (failure).
• You can replace Insert query statemetn with
  update or delete query statement.

13
Get record from database

• To retrieve or get record from database
  executeQuery() method is used.
• ResultSet rs stmt.executeQuery(Select lname,
  fname from personal")
• The method executeQuery() takes select
  statement as input.
• The method executeQuery() returns an object of
  type ResultSet.

14
Complete Program for Inserting a record in
Database

• import javax.swing.
• import java.sql.
• class JdbcExample
• public static void main(String args)
• Connection conn null
• Statement stmt null
• int i 0
• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
• conn DriverManager.getConnection("jdbcodbccd
  b")
• stmt conn.createStatement()
• catch (Exception e)
• System.out.println("Error in conection")

15
Contd.

• String lname JOptionPane.showInputDialog("Ebter
  your last name")
• String fname JOptionPane.showInputDialog("Ebter
  your first name")
• String sex JOptionPane.showInputDialog("Ebter
  your sex male/female")
• String salary JOptionPane.showInputDialog("Ebter
  your salary")
• String exp JOptionPane.showInputDialog("Ebter
  number of years of experience")
• System.out.println("There has been some problem
  in saving your personal information. Kindly fill
  the personal information again")

16
Contd.

• try
• i stmt.executeUpdate("INSERT INTO personal
  VALUES('"lname"','"fname"','"sex"','"salary
  "','"exp"')")
• catch (Exception e)
• System.out.println("Exception")
• if (i 1)
• System.out.println("success")
• //response.sendRedirect("conjoint.html")
• else
• System.out.println("There has been some
  problem in saving your personal information.
  Kindly fill the personal information again")

17
Complete Program for Retrieving records from query

• import javax.swing.
• import java.sql.
• class JdbcExample
• public static void main(String args)
• Connection conn null
• Statement stmt null
• ResultSet rs null
• int i 0

18
Contd.

• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
• conn DriverManager.getConnection("jdbcodbccd
  b")
• stmt conn.createStatement()
• catch (Exception e)
• System.out.println("Error in conection")

19
Contd.

• try
• rs stmt.executeQuery("Select fname, lname
  from personal")
• while(rs.next())
• System.out.println(rs.getString(1) " "
  rs.getString(2))
• // rs.next()
• catch (Exception e)
• System.out.println("Exception")

20
JDBC Prepared Statements (1)

• But query compilation takes a (relatively) long
  time!
• This example is therefore inefficient.

int students 1, 2, 4, 7, 9for (int i 0
i lt students.length i) ResultSet rs
stmt.executeQuery("SELECT " "FROM STUDENT
WHERE sid " studentsi) while (rs.next())

21
JDBC Prepared Statements (2)

• To speed things up, prepare statements and bind
  arguments to them
• This also means you dont have to worry about
  escaping strings, formatting dates, etc.
• Problems with this lead to a lot of security
  holes (SQL injection)
• Suppose a user inputs the name OReilly

PreparedStatement stmt conn.prepareStatement("SE
LECT " "FROM STUDENT WHERE sid ? ")int
students 1, 2, 4, 7, 9for (int i 0 i lt
students.length i) stmt.setInt(1,
studentsi) ResultSet rs stmt.executeQuery()
while (rs.next())
22
PreparedStatement

• The contained SQL is sent to the database and
  compiled or prepared beforehand
• From this point on, the prepared SQL is sent and
  this step is bypassed. The more dynamic
• Statement requires this step on every execution.
• Depending on the DB engine, the SQL may be
  cached and reused even for a different
  PreparedStatement and most of the work is done by
  the DB engine rather than the driver

23
PreparedStatement cont.

• A PreparedStatement can take IN parameters, which
  act much like arguments to a method, for column
  values.
• PreparedStatements deal with data conversions
  that can be error prone in straight ahead, built
  on the fly SQL handling quotes and dates in a
  manner transparent to the developer

24
PreparedStatement Steps

• 1. You register the drive and create the db
  connection in the usual manner.
• 2. Once you have a db connection, create the
  prepared statement object
• PreparedStatement updateSales
• con.prepareStatement(UPDATE OFFER_TBL SET
• QUANTITY ? WHERE ORDER_NUM ? ")
• // ? are referred to as Parameter Markers
• // Parameter Markers are referred to by number,
  starting from 1, in left to right order.
• // PreparedStatement's setXXX() methods are used
  to set the IN parameters, which remain set until
  changed.

25
PreparedStatement Steps cont.

• 3. Bind in your variables. The binding in of
  variables is positional based
• updateSales.setInt(1, 75)
• updateSales.setInt(2, 10398001)
• 4. Once all the vairables have been bound, then
  you execute the prepared statement
• int iUpdatedRecords updateSales.executeUpdate()
  

26
PreparedStatement cont.

• If the prepared statement object is a select
  statement, then you execute it, and loop through
  the result set object the same as in the Basic
  JDBC example
• PreparedStatement itemsSold con.prepareStatement
  ("select o.order_num,
• o.customer_num, c.name, o.quantity from
  order_tbl o,
• customer_tbl c where o.customer_num
  c.customer_num and o.customer_num ?")
• itemsSold.setInt(1,10398001)
• ResultSet rsItemsSold itemsSold.executeQuery()
  
• while (rsItemsSold.next())
• System.out.println( rsItemsSold.getString(NAME"
  ) "sold " rsItemsSold.getString(QUANTITY")
  " unit(s)")

27
Read ResultSet MetaData andDatabaseMetaData

• Once you have the ResultSet or Connection
  objects, you can obtain the Meta Data about the
  database or the query
• This gives valuable information about the data
  that you are retrieving or the
• database that you are using
• ResultSetMetaData rsMeta rs.getMetaData()
• DatabaseMetaData dbmetadata
  connection.getMetaData()
• There are approximately 150 methods in the
  DatabaseMetaData class.

28
ResultSetMetaData Example

• ResultSetMetaData meta rs.getMetaData()
• //Return the column count
• int iColumnCount meta.getColumnCount()
• for (int i 1 i lt iColumnCount i)
• System.out.println(Column Name "
  meta.getColumnName(i))
• System.out.println(Column Type"
  meta.getColumnType(i))
• System.out.println("Display Size "
• meta.getColumnDisplaySize(i) )
• System.out.println("Precision "
  meta.getPrecision(i))
• System.out.println(Scale " meta.getScale(i)
  )