pl-i group a: assignment no 1 1 java database connectivity ......callable statement in java if the...

By Mrs. B.A.Khivsara

Assistant Professor,

Department Of computer Engineering

SNJB’s Late Sau KBJ COE, Chandwad

–

PL-I Group A: Assignment No 1 1

Java DataBase Connectivity and

MySQL Procedure call

What is JDBC?

2

“An API that lets you access

virtually any tabular data source

from the Java programming

language”

14-3

JDBC Driver Types

14-4

JDBC Components

Basic steps to use a database in

Java

5

1.Establish a connection

2.Create JDBC Statements

3.Execute SQL Statements

4.GET ResultSet

5.Close connections

1. Establish a connection

6

import java.sql.*;

Load driver

• Class.forName("oracle.jdbc.driver.OracleDriver");

Make the connection• Connection con = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/DatabaseName","username"," passwd ",);

2. Create JDBC statement(s)

7

Statement stmt = con.createStatement() ;

Creates a Statement object for sending SQL statements to the database

Executing SQL Statements

8

String sql = “INSERT INTO t1 VALUES (‘abc’,21)";

stmt.executeUpdate(sql);

executeUpdate – for insert, update and delete queries

Get ResultSet (SELECT Query)

9

String sql = "SELECT name, age FROM t1";

ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {String name1 = rs.getString("NAME");

int age1 = rs.getInt(“AGE");}

Close connection

10

stmt.close();

con.close();

Sample program

11

import java.sql.*;

public class MySQLProcedure {

public static void main(String[] args) {

try{

Class.forName("com.mysql.jdbc.Driver");

Connection c = DriverManager.getConnection

("jdbc:mysql://localhost:3306/db1","root",”");

Statement sm = c.createStatement();

String sql = "SELECT name, age FROM t1";

ResultSet rs = sm.executeQuery(sql);

Sample program(cont)

12

while(rs.next()){

String first = rs.getString("name");

int age = rs.getInt("age");

System.out.print("name " + first);

System.out.println(", Age: " + age);

}

}

catch(Exception ee)

{

System.out.println(ee);

}

}

}

MySQL Procedure Example

Mysql> Delimiter $

Mysql> Create procedure P1 (In name1

varchar(20),Out age1 int)

begin

select age into age1 from t1 where

name=name1;

end;

$

Mysql> Delimiter ;

Mysql>call P1 („aaa‟,@age1);

Mysql> Select @age1;13

Callable statement in Java

A CallableStatement object provides a way to call

stored procedures

The syntax for invoking a stored procedure in JDBC is shown below.

{call procedure_name(?, ?, ...)}

14

Callable statement in Java

Creating a CallableStatement Object

CallableStatement cs = con.prepareCall( "{call P1(?, ?)}");

IN and OUT Parameters

Passing in any IN parameter values to a CallableStatementobject is done using the setXXX methods

Eg. cs.setString(1, “aaa");

15

Callable statement in Java

If the stored procedure returns OUT parameters, the SQL type of each OUT parameter must be registered

Registering the SQL type is done with the method registerOutParameter

cs.registerOutParameter

(2, java.sql.Types.INTEGER);

16

Callable statement in Java

Then after the statement has been executed

Eg. cs.executeUpdate();

CallableStatement's getXXX methods retrieve the parameter value

Eg. int age1 = cs.getInt(2);

17

Example of Callable statement in java

import java.sql.*;

public class MySQLProcedure {

public static void main(String[] args) {

CallableStatement cs = null;

try{

Class.forName("com.mysql.jdbc.Driver");

Connection c =

DriverManager.getConnection

("jdbc:mysql://localhost:3306/db1","root","root");

18

Example of Callable statement in java

cs = c.prepareCall("{call p1(?,?)}");

cs.setString(1, "bb");

cs.registerOutParameter(2, java.sql.Types.INTEGER);

cs.executeUpdate();

int age1 = cs.getInt(2); // return value

System.out.println(age1);

}

catch(Exception ee)

{

System.out.println(ee);

}

}

}19