Win a copy of Cloud Native Transformation: Practical Patterns for InnovationE this week in the Cloud/Virtualization forum
or The Go Workshop in the Go forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Joe Ess
  • salvin francis

using more than one sql statements in a preparedstatement

 
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("jdbc:oracle:thin:@localhost:1521:orcl1","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();
}

}
 
Bartender
Posts: 2657
19
Netbeans IDE C++ Linux
  • 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.
 
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: 2657
19
Netbeans IDE C++ Linux
  • 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: 2657
19
Netbeans IDE C++ Linux
  • 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("jdbc:oracle:thin:@localhost:1521:orcl1","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]
 
Sheriff
Posts: 3838
66
Netbeans IDE Oracle Firefox Browser
  • 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.
 
Get off me! Here, read this tiny ad:
Sauce Labs - World's Largest Continuous Testing Cloud for Websites and Mobile Apps
https://coderanch.com/t/722574/Sauce-Labs-World-Largest-Continuous
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!