java database connectivity jdbc n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Java Database Connectivity (JDBC) PowerPoint Presentation
Download Presentation
Java Database Connectivity (JDBC)

Loading in 2 Seconds...

play fullscreen
1 / 12

Java Database Connectivity (JDBC) - PowerPoint PPT Presentation


  • 99 Views
  • Uploaded on

Java Database Connectivity (JDBC). java.sql package to query and update the database. JDBC is an API that allows java to communicate with a database server using SQL commands. Most important members, such as Connection , Statement , and ResultSet , are interfaces instead of being classes.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Java Database Connectivity (JDBC)' - shiloh


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
java database connectivity jdbc
Java Database Connectivity (JDBC)
  • java.sql package to query and update the database.
  • JDBC is an API that allows java to communicate with a database server using SQL commands.
  • Most important members, such as Connection, Statement, and ResultSet, are interfaces instead of being classes.
    • This is because, the whole point of JDBC is to hide the specifics of accessing a particular database.
  • An application programmer doesn’t have to worry about the implementation of the underlying classes.
    • All you have to do is to use the methods defined by the various interfaces.
    • The implementation of the underlying classes is done in the vendor provided driver and associated classes.
slide2

Basics

  • The DriverManager class is responsible for keeping track of all the JDBC drivers that are available on a system.
  • First task of a JDBC program is to load an appropriate driver for the type of database being used.
  • After that a JDBC program should connect to the database by calling DriverManager.getConnection().
    • You specify the database to connect with a jdbc:URL. This URL has the following general syntax: jdbc:subprotocol:host:port:databasename

import java.sql.*;

class InsertMovie

{

public static void main (String args []) throws SQLException

{

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection

("jdbc:oracle:thin:@oracle.csc.uvic.ca:1521:TEACH", "thomo", “password");

// @machineName:port:SID, userid, password

slide3

Creating JDBC Statements

  • A Statement object is what sends your SQL statement to the DBMS.
  • You simply create a Statement object and then execute it:
    • For a SELECT statement, the method to use is executeQuery.
    • For statements that create or modify tables, the method to use is executeUpdate.
  • It takes an instance of an active connection to create a Statement object.
  • In the following example, we use our Connection object conn to create the Statement object stmt:
    • Statement stmt = conn.createStatement();
  • At this point stmt exists, but it does not have an SQL statement to pass on to DBMS.
  • We need to supply that with execute…
slide4

import java.sql.*;

class InsertMovie {

public static void main (String args []) throws SQLException

{

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection

("jdbc:oracle:thin:@oracle.csc.uvic.ca:1521:TEACH", "thomo", “password");

// @machineName:port:SID, userid, password

String title = "Movie ABCDEF";

int year = 2005;

int length = 200;

String studioName = "UVic";

String statementString = "INSERT INTO Movie(title, year, length, studioName) " +

"VALUES( '" + title + "'," + year + "," + length + ",'" + studioName + "')";

Statement stmt = conn.createStatement();

stmt.executeUpdate(statementString);

stmt.close();

}

}

slide5

Getting Data

  • Recall the impediance mismatch problem…
  • Well, Java provides us a class ResultSet which helps us bridge the gap.

ResultSet rset = stmt.executeQuery(

"SELECT title, year " +

"FROM Movie");

while (rset.next()) …

  • The variable rset, which is an instance of ResultSet, contains the rows of the query result.
  • In order to access the titles and years, we will go to each row and retrieve the values according to their types.
  • The first call of the method next moves the cursor to the first row and makes it the current row.
  • Successive invocations of the method next move the cursor down one row at a time from top to bottom.
slide6

Using the getXXX methods

  • Use the getXXX method of the appropriate type to retrieve the value in each column.
    • For example, the first column in each row of the previous rset stores a value of SQL type VARCHAR. The method for retrieving a value of SQL type VARCHAR is getString.
    • The second column in each row stores a value of SQL type INT, and the method for retrieving values of that type is getInt.

while (rset.next())

{

String s = rset.getString("Title");

int n = rset.getInt("Year");

System.out.println(s + " " + n);

}

However we can also say instead: (which is slightly more efficient)

String s = rset.getString(1);

int n = rset.getInt(2);

Column

Name

Column

Position

slide7

Using the getXXX methods (Continued)

  • JDBC allows a lot of flexibility as far as which getXXX methods you can use to retrieve the different SQL types.
  • For example, the method getInt can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to an int;
    • that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer out of the VARCHAR .
  • The getString method can retrieve any other datatype. However, in such a case we should convert strings to numbers.
slide8

import java.sql.*;

class dbAccess {

public static void main (String args []) throws SQLException

{

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection

("jdbc:oracle:thin:@orcus.csc.uvic.ca:1521:TEACH", "thomo", “password");

// @machineName:port:SID, userid, password

Statement stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery(

"SELECT title, year " +

"FROM Movie");

while (rset.next())

System.out.println (rset.getString("title") + " " + rset.getString("year"));

stmt.close();

}

}

slide9

Prepared statements

  • Sometimes it is more convenient or more efficient to use a PreparedStatement object for sending SQL statements to the database.
    • This special type of statement is derived from the more general interface, Statement, that you already know.
  • If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead.
  • The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created.
  • The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled.
    • As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled.
    • This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first.
slide10

Prepared statements (Continued)

  • Using our open connection conn from previous examples, we create a PreparedStatement:

PreparedStatement updateMovies = conn.prepareStatement(

"UPDATE Movie SET studioName = ? WHERE studioName = ?");

  • The variable updateMovies now contains the SQL statement,

"UPDATE Movie SET studioName = ? WHERE studioName = ?" ,

which has also, been sent to the DBMS and been precompiled.

slide11

import java.sql.*;

class UpdateMovie

{

public static void main (String args []) throws SQLException

{

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection

("jdbc:oracle:thin:@oracle.csc.uvic.ca:1521:TEACH", "thomo", “password");

// @machineName:port:SID, userid, password

PreparedStatement updateMovieStatement;

String updateMovieString = "UPDATE Movie " +

"SET studioName = ? " +

"WHERE studioName LIKE ?";

updateMovieStatement = conn.prepareStatement(updateMovieString);

slide12

String studiosBoughtByParamount [] = {"Disney", "Fox"};

for(int i=0; i<studiosBoughtByParamount.length; i++)

{

updateMovieStatement.setString(1, "Paramount");

updateMovieStatement.setString(2, "%"+studiosBoughtByParamount[i]+"%");

updateMovieStatement.executeUpdate();

}

updateMovieStatement.close();

}

}