This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

using more than one sql statements in a preparedstatement

 
mahesh chinthalapudi
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Vigneswaran Marimuthu
Greenhorn
Posts: 24
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.



 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
AND: Welcome to JavaRanch !!
 
mahesh chinthalapudi
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic