JDBC - PowerPoint PPT Presentation

1 / 51
About This Presentation
Title:

JDBC

Description:

dynamically load a java class at runtime ... This option allows for the Java program to make changes to the database ... JDO (http://java.sun.com/products/jdo ... – PowerPoint PPT presentation

Number of Views:104
Avg rating:3.0/5.0
Slides: 52
Provided by: mili3
Category:
Tags: jdbc | java

less

Transcript and Presenter's Notes

Title: JDBC


1
JDBC
2
JDBC OVERVIEW
  • The JDBC API is the industry standard for
    database-independent connectivity between the
    Java programming language and a wide range of
    databases.
  • The JDBC API provides a call-level API for
    SQL-based database access.
  • JDBC technology allows you to use the Java
    programming language to exploit "Write Once, Run
    Anywhere" capabilities for applications that
    require access to enterprise data.

3
JDBC ARCHITECTURE
  • The JDBC API contains two major sets of
    interfaces the first is the JDBC API for
    application writers, and the second is the
    lower-level JDBC driver API for driver writers.
  • JDBC technology drivers fit into one of four
    categories, shown in the next slide.
  • Applications and applets can access databases via
    the JDBC API using pure Java JDBC
    technology-based drivers, as shown in the next
    slide.

4
JDBC ARCHITECTURE




TYPE 1
TYPE 2
5
JDBC ARCHITECTURE
  • TYPE1
  • This style of driver converts JDBC calls into the
    network protocol used directly by DBMSs, allowing
    a direct call from the client machine to the DBMS
    server and providing a practical solution for
    intranet access.
  • TYPE2
  • This style of driver translates JDBC calls into
    the middleware vendor's protocol, which is then
    translated to a DBMS protocol by a middleware
    server. The middleware provides connectivity to
    many different databases

6
JDBC ARCHITECTURE
D
TYPE 3
TYPE 4
7
JDBC ARCHITECTURE
  • TYPE3
  • This style of driver converts JDBC calls into the
    network protocol used directly by DBMSs, allowing
    a direct call from the client machine to the DBMS
    server and providing a practical solution for
    intranet access.
  • TYPE4
  • This style of driver translates JDBC calls into
    the middleware vendor's protocol, which is then
    translated to a DBMS protocol by a middleware
    server. The middleware provides connectivity to
    many different databases.

8
JDBC Drivers
  • Vendor-specific implementation of the JDBC
    interfaces
  • Transparent to the database application
    developers.

9
JDBC specifications
JDBC 2.0 Specification
JDBC 2.0 Core API (java.sql package) Scrollable
ResultSets Streams
JDBC 1.0 Specification Basic database connectivity
JDBC 2.0 Standard Extension API (javax.sql
package) DataSource Connection pooling
Distributed transactions Rowsets
10
LOADING THE DRIVER
  • Class.forName(driverClassName).newInstance()
  • Class.forName(com.mysql.jdbc.Driver).newIns
    tance()
  • dynamically load a java class at runtime
  • executes newInstance() to create an object of
    class Driver calling the default constructor for
    the class.
  • The constructor executes code to register the
    class with the DriverManager

11
CONNECTING TO THE DATABASE
  • Connection DriverManager.getConnection(String
    url)
  • DriverManager.getConnection(
  • jdbcmysql//localhost/accounts?userrootpasswor
    dcis384)

12
URL
  • URL
  • jdbc mysql //localhost/accounts
  • ///
  • //localhost/accounts
  • //192.156.44.3/prod
  • //db.mycompany.com/prod
  • //db.mycompany.com4544/prod
  • when not specified, connector/J will default to
    port 3306.

13
DRIVER MANAGER -getConnection() method
14
getConnection() Example
  • Properties info new Properties()
  • info.setProperty(user, username)
  • info setProperty(password, pwd)
  • Connection con DriverManager.getConnection(url,
    prop)
  • OR
  • String usernameusername
  • String passwordpassword
  • Connection con DriverManager.getConnection(url,
    username, password)

15
QUERIES Statement Object
  • used to send a SQL statement to the database
  • executes the SQL statement
  • returns back the results of the SQL statement

16
createStatement()
17
executeQuery()
  • no assumption is made on the validity of the
    query
  • if the SQL execute successfully it returns a
    ResultSet object containing rows from the
    database
  • if the SQL fails it will raise a SQLException

18
Executing a Statement - Example
  • ResultSet rs
  • stmt.executeStatement(select name from pets)
  • ResultSet

Initial cursor position
next()
next()
19
ResultSet Object
  • A table of data representing a database result
    set
  • maintains a cursor pointing to its current row of
    data
  • Initially the cursor is positioned before the
    first row
  • The next() method moves the cursor to the next
    row
  • next() returns false when there are no more rows
    in the ResultSet object
  • A default ResultSet object is not updatable and
    has a cursor that moves forward only

20
Basic Getter Methods
  • int getInt(int columnIndex)
  • int getInt(String columnName)
  • String getString(int columnIndex)
  • String getString(String columnName)
  • Date getDate(int columnIndex)
  • Date getDate(String columnName)

21
HANDLING ERRORS
  • Connector/J Driver throws a SQLException
  • errors connecting with the database
  • errors executing SQL statements
  • To know more about a single Exception use the
    SQLException methods
  • getMessage()
  • getSQLState()
  • getErrorCode()

22
executeUpdate() method
Returns either the row count for INSERT, UPDATE
or DELETE statements, or 0 for SQL statements
that return nothing
23
Inserting a Row - Example
  • Statement stmt connection.createStatement()
  • int i stmt.executeUpdate(INSERT INTO pet
    VALUES(12, minou, Gwen, cat))

24
Updating a Row - Example
  • Statement stmt connection.createStatement()
  • int i stmt.executeUpdate(UPDATE pet SET
    ownerLucy where owner Gwen )

25
Deleting a Row - Example
  • Statement stmt connection.createStatement()
  • int i stmt.executeUpdate(DELETE FROM pet WHERE
    owner Gwen )

26
PREPARED STATEMENTS-SQL
  • ability to set up a statement once, and then
    execute it many times with different parameters.
  • replace building ad hoc query strings, and do so
    in a more efficient manner.
  • First implemented in the C API
  • Available in Connector/J server-side starting
    from version 3.1

27
HOW DATABASES EXECUTE QUERIES?
  • parse the query
  • invoke the optimizer to determine best query
    execution plan
  • caches the plan query is the key to fetch plan
    from cache

28
Prepared Statement - Example
  • PREPARE sel_stmt FROM SELECT name FROM pet WHERE
    id?
  • SET _at_pet_id1
  • EXECUTE sel_stmt USING _at_pet_id

29
JDBC Dynamic Query Example
  • Statement stmt con.createStatement()
  • for int(i1 i
  • String stmtString select name from pet
    where id
  • I
  • ResultSet rs stmt.executeQuery(stmtString)
  • while (rs.next())
  • System.out.println(rs.getString(1))
  • rs.close()
  • statement is parsed by the database each time
  • new query plan is created for each select
    statement and cached (entire stmt being the key)

30
JDBC PreparedStatement
  • PreparedStatement ps con.prepareStatement(
  • select name from pet
    where id ? )
  • for int(i1 i
  • ps.setInt(1, i) -- variable binding
  • ResultSet rs ps.executeQuery()
  • while (rs.next())
  • System.out.println(rs.getString(1))
  • rs.close()
  • ? is called placeholder
  • query is parsed only once and only 1 execution
    plan is created and caches for it
  • executed many times after binding variables
  • MUCH MORE EFFICIENT!

31
PLACEHOLDERS- Setter methods
32
DISCONNECTING FROM THE DATABASE
  • close a connection to the database to release
    resources
  • Make sure you first close all component that use
    that connection
  • Close components in the reverse order you opened
    them.
  • rs.close()
  • stmt.close()
  • conn.close()

33
ResultSet
  • ResultSet objects provide access to the tables
    generated as results of executing a Statement
    queries
  • Only one ResultSet per Statement can be open at
    the same time!
  • The table rows are retrieved in sequence
  • A ResultSet maintains a cursor pointing to its
    current row
  • The next() method moves the cursor to the next
    row

34
ResultSet Methods
  • boolean next()
  • activates the next row
  • the first call to next() activates the first row
  • returns false if there are no more rows
  • void close()
  • disposes of the ResultSet
  • allows you to re-use the Statement that created
    it
  • automatically called by most Statement methods

35
ResultSet Methods
  • Type getType(int columnIndex)
  • returns the given field as the given type
  • indices start at 1 and not 0!
  • Type getType(String columnName)
  • same, but uses name of field
  • less efficient
  • For example getString(columnIndex),
    getInt(columnName), getTime, getBoolean,
    getType,...
  • int findColumn(String columnName)
  • looks up column index given column name

36
ResultSet Methods
  • JDBC 2.0 includes scrollable result sets.
    Additional methods included are first,
    last, previous, and other methods.

37
ResultSet Example
  • Statement stmt  con.createStatement()
  • ResultSet rs  stmt. executeQuery("select lname,s
    alary from employees")    
  • // Print the result
  • while(rs.next())  System.out.print(rs.getString(
    1)  "") System.out.println(rs.getDouble(salar
    y"))

38
Transactions and JDBC
  • Transaction more than one statement that must
    all succeed (or all fail) together
  • e.g., updating several tables due to customer
    purchase
  • If one fails, the system must reverse all
    previous actions
  • Also cant leave DB in inconsistent state halfway
    through a transaction
  • COMMIT complete transaction
  • ROLLBACK cancel all actions

39
Example
  • Suppose we want to transfer money from bank
    account 13 to account 72

PreparedStatement pstmt con.prepareStatement("u
pdate BankAccount set amount amount
? where accountId ?") pstmt.setInt(1,-100)
pstmt.setInt(2, 13) pstmt.executeUpdate() pst
mt.setInt(1, 100) pstmt.setInt(2,
72) pstmt.executeUpdate()
What happens if this update fails?
40
Transaction Management
  • Transactions are not explicitly opened and closed
  • The connection has a state called AutoCommit mode
  • if AutoCommit is true, then every statement is
    automatically committed
  • if AutoCommit is false, then every statement is
    added to an ongoing transaction
  • Default true

41
AutoCommit
setAutoCommit(boolean val)
  • If you set AutoCommit to false, you must
    explicitly commit or rollback the transaction
    using Connection.commit() and Connection.rollback(
    )
  • Note DDL statements (e.g., creating/deleting
    tables) in a transaction may be ignored or may
    cause a commit to occur
  • The behavior is DBMS dependent

42
Scrollable ResultSet
  • Statement createStatement( int resultSetType, int
    resultSetConcurrency)
  • resultSetType
  • ResultSet.TYPE_FORWARD_ONLY
  • -default same as in JDBC 1.0
  • -allows only forward movement of the cursor
  • -when rset.next() returns false, the data is no
    longer available and the result set is closed.
  • ResultSet.TYPE_SCROLL_INSENSITIVE
  • -backwards, forwards, random cursor movement.
  • -changes made in the database are not seen in the
    result set object in Java memory.
  • ResultSetTYPE_SCROLL_SENSITIVE
  • -backwards, forwards, random cursor movement.
  • -changes made in the database are seen in the
  • result set object in Java memory.

43
Scrollable ResultSet (contd)
  • resultSetConcurrency
  • ResultSet.CONCUR_READ_ONLY
  • This is the default (and same as in JDBC 1.0) and
    allows only data to be read from the database.
  • ResultSet.CONCUR_UPDATABLE
  • This option allows for the Java program to make
    changes to the database based on new methods and
    positioning ability of the cursor.
  • Example
  • Statement stmt conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY)
  • ResultSetrset stmt.executeQuery( SHOW TABLES)

44
Scrollable ResultSet (contd)
  • public boolean absolute(int row) throws
    SQLException
  • -If the given row number is positive, this method
    moves the cursor to the given row number (with
    the first row numbered 1).
  • -If the row number is negative, the cursor moves
    to a relative position from the last row.
  • -If the row number is 0, an SQLException will be
    raised.
  • public boolean relative(int row) throws
    SQLException
  • This method call moves the cursor a relative
    number of rows, either positive or negative.
  • An attempt to move beyond the last row (or before
    the first row) in the result set positions the
    cursor after the last row (or before the first
    row).
  • public boolean first() throws SQLException
  • public boolean last() throws SQLException
  • public boolean previous() throws SQLException
  • public boolean next() throws SQLException

45
Scrollable ResultSet (contd)
  • public void beforeFirst() throws SQLException
  • public void afterLast() throws SQLException
  • public boolean isFirst() throws SQLException
  • public boolean isLast() throws SQLException
  • public boolean isAfterLast() throws SQLException
  • public boolean isBeforeFirst() throws
    SQLException
  • public int getRow() throws SQLException
  • getRow() method retrieves the current row number
    The first row is number 1, the second number 2,
    and so on.

46
JDBC Usage in Industry
  • Apace DbUtils (http//jakarta.apache.org/commons/d
    butils/)
  • ORM (Object Relational Mappers)
  • Hibernate (http//www.hibernate.org/),
  • JDO (http//java.sun.com/products/jdo/),
  • TopLink (http//www.oracle.com/technology/products
    /ias/toplink/index.html)

47
ResultSet Meta-Data
A ResultSetMetaData is an object that can be used
to get information about the properties of the
columns in a ResultSet object
An example write the columns of the result set
ResultSetMetaData rsmd rs.getMetaData() int
numcols rsmd.getColumnCount() for (int i 1
i olumnLabel(i)" ")
48
Using JDBC Meta Data
  • Using JDBC MetaData
  • Metadata data about the data
  • Not How many customers have sales over
    100K? but
  • How many columns have a datatype of BIT
    (boolean)?
  • System-wide data
  • connection.getMetaData().getDatabaseProductName(
    )
  • connection.getMetaData().getDatabaseProductVersi
    on()
  • Table-specific data
  • resultSet.getMetaData().getColumnCount()
  • When using the result, remember that
  • the index starts at 1, not 0
  • resultSet.getMetaData().getColumnName()

49
Using Meta Data
  • private void showTable(String driver,
  • String url,
  • String username,
  • String password,
  • String tableName,
  • PrintWriter out)
  • try
  • Class.forName(driver)
  • Connection connection
  • DriverManager.getConnection(url, username,
    password)
  • DatabaseMetaData dbMetaData connection.getMetaDa
    ta()
  • out.println("
      ")
    • String productName dbMetaData.getDatabaseProduct
      Name()
    • out.println("
    • Database "
      productName)
    • String productVersion
    • dbMetaData.getDatabaseProductVersion()
    • out.println("
    • Version "
      productVersion)
    • out.println("
    ")

50
Using MetaData(Contd)
  • Statement statement connection.createStatement()
  • String query
  • "SELECT FROM " tableName
  • ResultSet resultSet statement.executeQuery(query
    )
  • out.println("")
  • ResultSetMetaData resultsMetaData
    resultSet.getMetaData()
  • int columnCount resultsMetaData.getColumnCount()
  • out.println("")
  • for(int i1 i
  • out.print("" resultsMetaData.getColumnName(i
    ))
  • while(resultSet.next())
  • out.println("")
  • for(int i1 i
  • out.print("" resultSet.getString(i))
  • out.println()
  • out.println("")

51
THANKYOU
NAME MILI SHAH SID 109-424-91
Write a Comment
User Comments (0)
About PowerShow.com