JDBC

1
JDBC

• CSCI 4370
• Meghana Viswanath

2
What is JDBC?

• Stands for Java Database Connectivity
• Technology that enables a java program to connect
  to a database
• Java API
• Developed by Javasoft, a SUN subsidiary

3
Main tasks that JDBC can perform

• 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 Product Components

• The JDBC API
• Enables a Java program communicate with a
  database i.e. execute SQL statements, obtain
  results
• Is part of the Java Platform
• Contains 2 packages java.sql and javax.sql
• JDBC Driver Manager
• a class in Java whose instances define objects
  that can connect to the JDBC driver

5
JDBC Architecture

• 2 tier 3 tier

Java Application
HTML Browser
Client
Client
JDBC
Java Application
Application Server
DBMS
JDBC
DB Server
DBMS
DB Server
6
Establishing a Connection

• import java.sql.public class MysqlConnect  p
  ublic static void main(String args)     System
  .out.println("MySQL Connect Example.")    Connec
  tion conn  null    String url  jdbcmysql//l
  ocalhost3306/"    String dbName  library"  
    String driver  "com.mysql.jdbc.Driver"    Str
  ing userName  "root"     String password  "roo
  t"    try       Class.forName(driver).newInsta
  nce() //Loads the driver      conn  DriverManag
  er.getConnection(urldbName,userName,password)  
      System.out.println("Connected to the database"
  )      conn.close()      System.out.println("D
  isconnected from database")     catch (Exceptio
  n e)       e.printStackTrace()      

7
Establishing a Connection

• Class.forName() a method in the
  java.lang.Class class used to load a driver
• DriverManager - a class of java.sql package that
  controls a set of JDBC drivers. Each driver has
  to register with this class.
• getConnection() a method in the DriverManager
  class which establishes the connection to the DB.

8
Example 1

• public class example1
• public static void main(String args)
• System.out.println("MySQL Connect Example.")
• Connection conn null
• String url "jdbcmysql//localhost3306/"
• String dbName "library"
• String driver "com.mysql.jdbc.Driver"
• String userName "root"
• String password "meghana"
• try
• Class.forName(driver).newInstance()
• conn DriverManager.getConnection(urldbNam
  e,userName,password)
• System.out.println("Connected to the
  database")
• Statement st conn.createStatement()
• //st.executeUpdate("CREATE DATABASE
  CSCI4370")
• st.executeUpdate("CREATE TABLE
  csci4370.STUDENT(name VARCHAR(20), sid
  varchar(20))")
• conn.close()
• System.out.println("Disconnected from
  database")
• catch (Exception e)

9
Example 1

• Statement st conn.createStatement()
• Creates a new SQL statement instance which can
  be used to execute multiple SQL statements

10
Example 2

• public static void main(String args)     -----
  
•     try      Class.forName(driver)      con 
   DriverManager.getConnection(urldb, user, pass)
        try         Statement st
  con.createStatement()
• ResultSet rs st.executeQuery("select from
  csci4370.student")
• while (rs.next())
• String name rs.getString("name")
• System.out.println(name)
• con.close()      
•        catch (SQLException s)
  s.printStackTrace()          catch (Exception
   e) e.printStackTrace()     

11
Example 2

• ResultSet rs st.executeQuery("select from
  csci4370.student")
• ResultSet - an interface that provides getter
  methods to retrieve data from DB tables.

12
Sources and References

• http//java.sun.com/docs/books/tutorial/jdbc/basic
  s/index.html
• http//www.roseindia.net/jdbc/jdbc.shtml