Reading Data From ResultSet
ResultSet interface consists of a number of methods for reading data from query result.These methods allow you to refer a column by number or by name, as shown below:
• Int getInt (int ColumnIndex)
• Int getInt(String ColumnName)
These methods allow you to read an integer value from the current row of ResultSet. The first method uses the column index and other specify the name of column. These are setXX() methods for every java primitive type and for some kind of java objects.
Implementation
The program written below demonstrate how to use ResultSet interface with PreparedStatement interface to query data from database table.
import java.sql.*;
import javax.swing.JOptionPane;
public class Resultset {
Connection con;
PreparedStatement pstmt;
Resultset()
{
try
{
Class.forName("com.mysql.jdbc.Driver"); //loading Mt-Sql Driver
//connecting to database
con =DriverManager.getConnection("jdbc:mysql://localhost:3306/college","root","bce");
}
catch(Exception e)
{ e.printStackTrace(); }
}
void fetchOne(int rollno)
{
try{
pstmt = con.prepareStatement("Select sname from students where rollno=?");
pstmt.setInt(1, rollno);
ResultSet rs = pstmt.executeQuery();
if(rs.next()==true)
{
String sname=rs.getString("sname");
System.out.println(sname);
}
else
JOptionPane.showMessageDialog(null, "Sorrryyyyyy");
rs.close();
pstmt.close();
}
catch(Exception e)
{ System.out.println(e.toString()); }
}
void fetchAll()
{
try{
pstmt = con.prepareStatement("Select * from students");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
int rollno=rs.getInt("rollno");
String sname=rs.getString("sname");
float per=rs.getFloat("per");
String branch=rs.getString("branch");
System.out.println(rollno+". "+sname+" "+branch+" "+per);
}
rs.close();
pstmt.close();
}
catch(Exception e)
{ System.out.println(e.toString()); }
}
void fetchRandomly()
{
int rollno;
float per;
String sname,branch;
try{
int scrollability=ResultSet.TYPE_SCROLL_INSENSITIVE;
int updateable=ResultSet.CONCUR_READ_ONLY;
pstmt = con.prepareStatement("Select * from students",scrollability,updateable );
ResultSet rs = pstmt.executeQuery();
rs.absolute(4); //put the CURSOR on 4th row
rollno=rs.getInt("rollno");
sname=rs.getString("sname");
per=rs.getFloat("per");
branch=rs.getString("branch");
System.out.println("4th row data : " + rollno+". "+sname+" "+branch+" "+per);
rs.absolute(2); //pot the CURSOR on 2nd row
rollno=rs.getInt("rollno");
sname=rs.getString("sname");
per=rs.getFloat("per");
branch=rs.getString("branch");
System.out.println(" 2nd row data : "+rollno+". "+sname+" "+branch+" "+per);
rs.close();
pstmt.close();
}
catch(Exception e)
{ System.out.println(e.toString()); }
}
public static void main(String args[])
{
Resultset m=new Resultset();
//m.fetchAll();
//m.fetchRandomly();
m.fetchOne(1014);
}
}
java.sql.ResultSetMetaData Interface
It is used to set descriptive information about the columns of table fetched from database, for example, number of columns, name of columns and their data types etc. It does not provide any information about number of rows it consists. You can create object of ResultSetMetaData object by writing the following code:-ResultSetMetaData rsmd = rs.getMetaData();
| Methods | Description |
|---|---|
| Public int getColumnCount() throws SQLException | It returns number of columns in ResultSet object. |
| Public String getColumnName(int index) | Returns the name of column at specific index |
| Public String getColumnLabel(int index) | Gives the name of column at specific index. |
| Public Boolean isReadOnly(int index) | Return true if the column is read only, else return false. |
| Public Boolean isWriteable(int index) | Return true if the column is writeable else return false. |
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Resultsetmetadata {
Connection con;
PreparedStatement pstmt;
Resultsetmetadata()
{
try
{
Class.forName("com.mysql.jdbc.Driver");
con =DriverManager.getConnection("jdbc:mysql://localhost:3306/college","root","bce");
}
catch(Exception e)
{ e.printStackTrace(); }
}
void fetchAll()
{
try{
pstmt = con.prepareStatement("Select * from students");
ResultSet rs = pstmt.executeQuery();
//Creating Object of ResultSetMetaData
ResultSetMetaData rsmd= rs.getMetaData();
int count=rsmd.getColumnCount();
System.out.println("No. of columns:"+count);
System.out.println("No. of columns:"+ rsmd.getTableName(2));
int i=1;
while(i<=count)
{
String colName=rsmd.getColumnName(i);
String colType=rsmd.getColumnTypeName(i);
boolean wa=rsmd.isWritable(i);
boolean ro=rsmd.isReadOnly(i);
System.out.println("Column Name: ["+colName+"] , Column Type: ["+colType+"]");
System.out.println(" isReadOnly: "+ro+" , writeAble="+wa);
i++;
}
rs.close();
pstmt.close();
}
catch(Exception e)
{ System.out.println(e.toString()); }
}
public static void main(String args[])
{
Resultsetmetadata m=new Resultsetmetadata();
m.fetchAll();
}
}
java.sql.CallableStatementInterface
callableStatement interface is sub interface of preparedStatement interface. This interface is used to execute SQL stored procedures. Stored procedures are just like methods of java code. Stored procedure are stored in your database on the database server.Stored proccedings generally refer to both procedure and functions. The difference between both is that procedure does not return value , where as function can return a value.
Your program code can stored procedures using CallableStatement object. A CallableStatement object can be created by calling method of Connection interface. The following methods of Connection interface can be used for creating CallableStatement Object.
• CallableStatement prepareCall (String SQL) throws SQLException:
It creates CallableStatement object for calling the specificed database stored procedure.
• Public CallableStatement prepareCall (String Sql, int resultSetType, int resultSetConcurrency) throws SQLException:
It creates a CallableStatement object, that will generate ResultSet object with given type and concurrency. The following code creates a CallableStatement object for a stored procedure:
String Sql = “{call procedureName}”;
CallableStatement stmt = connection.prepareCall (SQL);
Stored Procedure with Parameters
A Stored can also have parameters. Parameters can be of 3 following Types:• IN Parameter.
• OUT Parameter.
• INOUT Parameter.
• IN Parameter is used to pass data to the stored procedure. Through OUT parameter, the stored procedure pass data back to the caller. And INOUT parameters are used to pass data to procedure and the same parameter is used to send modified data back to the caller.
If a parameter is INOUT or OUT type, it must be register before execution of stored procedure. To register a parameter, use the following methods of CallableStatement interface.
• Public void registerOutParameter (int parmIndex , int SqlType);
• Public void registerOutparameter (int parmIndex, int SqlType, int scale);
The first method type can be used for all types of Output parameters. But if registered parameter is one of the numeric types, such as double, numeric, float or decimal, you should prefer second form of registerOutParaeter() method.
For example, to register a return type, that return a number with 3 decimal places, you can write.
Stmt.registerOutputParameter(1,Java.sql.Types.DOUBLE,3);
For example: The following code retrieves the percentage for the specific RollNo (a column in students table).
Here, the RollNo is IN parameter is OUT parameter:-
Stored Procedure with parameter with Return (Function):
As discussed earlier, a procedure which returns a value is known as function.Syntax:
{? = call functionName (?,? …)}
In the above syntax, there are placeholders. These placeholders act for IN, OUT and INOUT parameters or for return data from functions. In the above syntax, first placeholder (?) represents the receiver of value, it will be registered as output parameter. The other two placeholders (?) represent the values, which will be passed through setter methods of CallableStatement interface.
Implementation
The following program demonstrates, how to create CallableStatement object to call procedures (without Parameters , With parameters) and functions. Create the following procedures and function in My-Sql database before start writing the program.
| • Procedure to get all the record from “Students” table |
|
PROCEDURE showAll() BEGIN Select * from students; END; |
| • Procedure (using IN parameter) to get all records having specificed branch |
|
PROCEDURE showAll() BEGIN Select * from students where branch=branch; END; |
| • Procedure ( using IN & OUT Parameters) to a record for specificed rollno |
|
PROCEDURE showAll() BEGIN Select per into percentage from students where rollno=roll; END; |
| • Function (using IN Parameter) to get a record for specific rollno |
|
FUNCTION getOne (IN roll int) RETURN float BEGIN Declare pr float; Select per into pr from students where rollno=roll; RETURN pr; END; |
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.CallableStatement;
public class Callablestatement {
Connection con;
PreparedStatement pstmt;
//constructor function
public Callablestatement()
{
try
{
Class.forName("com.mysql.jdbc.Driver"); //loading My-Sql Driver
//connecting to database
con =DriverManager.getConnection("jdbc:mysql://localhost:3306/college","root","bce");
}
catch(Exception e)
{ e.printStackTrace(); }
}
//method calling : Stored Procedure without parameters
void withoutParameter()
{
String sql= "{call showAll()}";
try
{
java.sql.CallableStatement stmt=con.prepareCall(sql) ;
ResultSet rs= stmt.executeQuery();
while(rs.next())
{
int rollno=rs.getInt("rollno");
String sname=rs.getString("sname");
float per=rs.getFloat("per");
String branch=rs.getString("branch");
System.out.println(rollno+". "+sname+" "+branch+" "+per);
}
stmt.close();
rs.close();
}
catch(Exception ex){ ex.printStackTrace();}
}
//method calling : Stored Procedure using IN parameter
void withParameter()
{
String sql= "{call getSome(?)}";
try
{
java.sql.CallableStatement stmt=con.prepareCall(sql) ;
stmt.setString(1, "CSE");
ResultSet rs= stmt.executeQuery();
while(rs.next())
{
int rollno=rs.getInt("rollno");
String sname=rs.getString("sname");
float per=rs.getFloat("per");
String branch=rs.getString("branch");
System.out.println(rollno+". "+sname+" "+branch+" "+per);
rs.close();
}
}
catch(Exception ex){ex.printStackTrace();}
}
//method calling : function with IN parameter with return (using OUT parameter)
void withParameterNreturn(int rollno)
{
String sql= "{?=call getOne(?)}";
try
{
CallableStatement stmt=con.prepareCall(sql);
stmt.setInt(2, rollno);
stmt.registerOutParameter(1,java.sql.Types.FLOAT);
boolean b=stmt.execute();
float per=stmt.getFloat(1);
System.out.println( "Percentage= "+per);
stmt.close();
}
catch(Exception ex){ ex.printStackTrace();}
}
//method calling : Stored Procedure with IN parameter and OUT parameter
void withOutputParameter(int rollno)
{
String sql= "{call getPer(?,?)}";
try
{
java.sql.CallableStatement stmt=con.prepareCall(sql);
stmt.setInt(1, rollno); // pass value for IN parameter
stmt.registerOutParameter(2,java.sql.Types.FLOAT,2); //regestring OUT parameter
boolean b=stmt.execute();
float per=stmt.getFloat(2);
System.out.println( "Percentage= "+per);
}
catch(Exception ex){ ex.printStackTrace();}
}
public static void main(String[] args)
{
Callablestatement call=new Callablestatement();
call.withParameter();
System.out.println("--------------------------------");
call.withoutParameter();
System.out.println("--------------------------------");
call.withParameterNreturn(101);
System.out.println("--------------------------------");
call.withOutputParameter(102);
}
}
OUTPUT
101. Aman CSE 90.0
--------------------------------
101. Aman CSE 90.0
102. Raman ECE 94.0
103. Amir Khan IT 95.0
104. Robert CSE 98.0
--------------------------------
Percentage= 90.0
--------------------------------
Percentage= 94.0
Available on Amazon
Under the guidance of Founder & Author of "realJavaOnline.com".
M:9872246056
