lec 15
Download
Skip this Video
Download Presentation
Lec - 15

Loading in 2 Seconds...

play fullscreen
1 / 20

Lec - 15 - PowerPoint PPT Presentation


  • 97 Views
  • Uploaded on

Lec - 15. More on JDBC. Example Code. Executing SQL DML statements. Useful Statement Methods (N-I). executeQuery Executes the SQL query and returns the data in a table (ResultSet) The resulting table may be empty but never null ResultSet rs = stmt.executeQuery(“select * from table");.

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 ' Lec - 15' - gay-moses


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
example code

Example Code

Executing SQL DML statements

useful statement methods n i
Useful Statement Methods (N-I)
  • executeQuery
    • Executes the SQL query and returns the data in a table (ResultSet)
    • The resulting table may be empty but never null

ResultSet rs = stmt.executeQuery(“select * from table");

JDBC

example code 15 1 executing sql dml statements
Example Code 15.1Executing SQL DML Statements

/* This progarm will take two command line argument

that are used to update records in the database */

import java.sql.*; //step 1

public class JdbcDmlEx {

public static void main (String args [ ]){

try {

//steps 2 to 5

Class.forName(“driver name”);

Connection con=null;

con = DriverManager.getConnection(url, usr, pwd);

Statement st = con.createStatement();

JDBC

example code 15 1 executing sql dml statements cont
Example Code 15.1Executing SQL DML Statements (cont.)

//Step 6: Execute the Query / DML

String addVar = args[0];

String nameVar = args[1];

String sql = “UPDATE Person ” +

“ SET address = ‘ “ + addVar + ” ’ ” +

“ WHERE name = ‘ “ + nameVar + ” ’ ” ;

int num = st.executeUpdate(sql);

//Step 7: Process the results of the query

System.out.println(num + “ records updated”);

JDBC

example code 15 1 executing sql dml statements cont1
Example Code 15.1 Executing SQL DML Statements (cont.)

//Step 8: close the connection

con.close();

}catch (Exception sqlEx) {

System.out.println(sqlEx);

}

} //end main

}//end class

JDBC

compile execute
Compile & Execute

Before execution

After execution

JDBC

useful statement methods continued
Useful Statement Methods (Continued)
  • getMaxRows( ) / setMaxRows(int)
    • Determines the number of rows a ResultSet may contain
    • Unless explicitly set, the number of rows are unlimited (return value of 0)
  • getQueryTimeout( ) / setQueryTimeout(int)
    • Specifies the amount of a time (seconds) a driver will wait for a STATEMENT to complete before throwing a SQLException

JDBC

different types of statements
Different Types of Statements
  • Overview
    • Through the Statement object, SQL statements are sent to the database.
    • Three types of statement objects are available:
      • Statement
        • for executing a simple SQL statements
      • PreparedStatement
        • for executing a precompiled SQL statement passing in parameters
      • CallableStatement
        • for executing a database stored procedure

JDBC

prepared statements precompiled queries
Prepared Statements (Precompiled Queries)
  • Idea
    • If you are going to execute similar SQL statements multiple times, using “prepared” (parameterized)statements can be more efficient
    • Create a statement in standard form that is sent to the database for compilation before actually being used
    • Each time you use it, you simply replace some of the marked parameters (?) using some set methods

JDBC

prepared statement example
Prepared Statement, Example

PreparedStatement pStmt =

con.prepareStatement("UPDATE tableName " +

“SET columnName = ? " +

“WHERE columnName = ?");

  • First marked parameter(?) has index 1.

pStmt.setString(1, stringValue);

pStmt.setInt (2, intValue);

pStmt.executeUpdate();

JDBC

example code1

Example Code

Using Prepared Statements

example code modify jdbcdmlex java 15 1 executing prepared statements
Example Code: Modify JdbcDmlEx.java15.1Executing Prepared Statements

/* Modification to the last example code 15.1,

to show the usage of prepared statements */

import java.sql.*; // step1

public class JdbcDmlEx {

public static void main (String args [ ]){

try {

//steps 2 to 4

Class.forName(“driver name”);

Connection con=null;

con = DriverManager.getConnection(url, usr, pwd);

JDBC

example code modify jdbcdmlex java 15 1 executing prepared statements1
Example Code: Modify JdbcDmlEx.java15.1Executing Prepared Statements

//Step 5: Create the statement

PreparedStatement pStmt = null;

String sql = “UPDATE Person SET address = ? WHERE name = ? ” ;

pStmt = con.prepareStatement(sql);

//Step 6: Execute the Query

String addVar = args[0];

String nameVar = args[1];

pStmt.setString(1 , addVar);

pStmt.setString(2, nameVar);

// sql = “UPDATE Person SET address = “defence” WHERE name = “ali” ”

int num = pStmt.executeUpdate();

JDBC

example code modify jdbcdmlex java 15 1 executing prepared statements2
Example Code: Modify JdbcDmlEx.java15.1Executing Prepared Statements

//Step 7: Process the results of the query

System.out.println(num + “ records updated”);

//Step 8: close the connection

}catch (Exception sqlEx) {

………..

}

} //end main

}//end class

JDBC

compile execute1
Compile & Execute

Before execution

After execution

JDBC

resultset

0

1

2

3

id

1

2

3

Name

ali

usman

raza

Address

model town

gulberg

defence

phoneNum

9203256

8219065

5173946

ResultSet

Row numbers

ResultSet

JDBC

ad