It's not a secret anymore!*
The moose likes JDBC and the fly likes using more than one sql statements in a preparedstatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "using more than one sql statements in a preparedstatement" Watch "using more than one sql statements in a preparedstatement" New topic
Author

using more than one sql statements in a preparedstatement

mahesh chinthalapudi
Greenhorn

Joined: Sep 07, 2011
Posts: 2

import java.sql.*;

public class retrive {
public static void main(String args[])throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
//System.out.println("1");//verification of driver
Connection con=DriverManager.getConnection("jdbcracle:thin:@localhost:1521rcl1","system","task");

String dq="select * from employee" ;
String dq1="select count(*),max(sal),min(sal),avg(sal),sum(sal) from employee";
PreparedStatement ps=con.prepareStatement(dq);
PreparedStatement ps1=con.prepareStatement(dq1);
ResultSet rs=ps.executeQuery(dq);
ResultSet rs1=ps1.executeQuery(dq1);


while(rs.next())
{
int sno=rs.getInt(1);
String name=rs.getString(2);
float salary=rs.getFloat(3);
System.out.println(sno+"\t"+name+"\t"+salary);
int i=st.executeUpdate(rs);
}rs1.next();
System.out.println("emp details from employee database");
System.out.println("-----------------------------------");
System.out.println("totol no of records="+rs1.getInt(1));
System.out.println("max sal of an employee="+rs1.getInt(2));
System.out.println("min sal of an employee="+rs1.getInt(3));
System.out.println("avg sal of an employee="+rs1.getInt(4));
System.out.println("totol sal of all employees="+rs1.getInt(5));
System.out.println("-----------------------------------");
rs.close();
rs1.close();
ps.close();
ps1.close();
con.close();
}

}
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Yes you can. But what do you want to achieve?
I do not understand what your code is doing.

You are calling rs.executeUpdate() on a select statement.

OCUP UML fundamental and ITIL foundation
youtube channel
Vigneswaran Marimuthu
Greenhorn

Joined: Aug 30, 2011
Posts: 24

Jan Cumps wrote:I do not understand what your code is doing.



I came to know about another Interface through which we can use precompiled SQL For your question Jan, he is just executing a SQL statement which retrieves the content of the table employee. In second statement he is using SQL functions like count(),max() etc.

Jan Cumps wrote:You are calling rs.executeUpdate() on a select statement.


He is not using rs.executeUpdate() itself Jan. Moreover resultset object cannot be used for executing SQL statements. Its used to hold the information, which is the result ofexecution of your SQL statement. You should use either Statement object or preparedStatement object to execute SQL statements.




Regards,

Vigneswaran.M
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Vigneswaran Marimuthu wrote: For your question Jan, he is just executing a SQL statement which retrieves the content of the table employee. In second statement he is using SQL functions like count(),max() etc.
That I knew I didn't get what the poster wanted to achieve with the code.

Vigneswaran Marimuthu wrote:He is not using rs.executeUpdate() itself Jan.

I was pointing to this line: int i=st.executeUpdate(rs);
It is an odd piece of code, because the variable st is not declared, rs does not have an operation called executeUpdate(), and the executeUpdate() I know of don't take a ResultSet as parameter.


(And java convention says that a class name should start with uppercase)
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

AND: Welcome to JavaRanch !!
mahesh chinthalapudi
Greenhorn

Joined: Sep 07, 2011
Posts: 2
Thank you for your suggestions.I got the output with was expected but rather than using multiple string objects for SQL Queries can i use string array?

code=java]


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;

public class perform {

public static void main(String args[])throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
//System.out.println("1");
Connection con=DriverManager.getConnection("jdbcracle:thin:@localhost:1521rcl1","system","task");

String []dq={"select * from employee","select count(*),max(salary),min(salary),avg(salary),sum(salary) from employee"} ;
ResultSet rs1=null;
for (int i=0;i<2;i++)
{ PreparedStatement ps=con.prepareStatement(dq[i]);

rs1=ps.executeQuery(dq[i]);

ps.close();
}

while(rs1.next())
{
int sno=rs1.getInt(1);
String name=rs1.getString(2);
float salary=rs1.getFloat(3);
System.out.println(sno+"\t"+name+"\t"+salary);

}
rs1.next();
System.out.println("emp details from employee database");
System.out.println("-----------------------------------");
System.out.println("totol no of records="+rs1.getInt(1));
System.out.println("max sal of an employee="+rs1.getInt(2));
System.out.println("min sal of an employee="+rs1.getInt(3));
System.out.println("avg sal of an employee="+rs1.getFloat(4));
System.out.println("totol sal of all employees="+rs1.getInt(5));
System.out.println("-----------------------------------");
rs1.close();
rs1.close();

ps1.close();
con.close();
}

}

[/code]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

mahesh chinthalapudi wrote:

That's not going to work. Only the recordset open in the last pass through the loop will be available, of course, previous ones will be lost. This is no different from Java's handling of object references in general: when you assign a new instance to the rs1 variable, the previous instance is lost. In this particular case, the recordset is even not properly closed, which might in general lead to resource leaks.

It should be possible to submit more SQL queries at once to the database, separated by the databases command separator (typically a semicolon), and then sequentially access all of the generated ResultSets through the Statement.getMoreResults() method. I haven't used this method myself, so I cannot provide more details. Examples should be available on the net, though.
 
wood burning stoves
 
subject: using more than one sql statements in a preparedstatement
 
Similar Threads
JDBC-CONNECTION POOL
Problem in inserting from one table to another
need help with a logic
Query help
How can I generate dynamic tables taking values from database