Jdbc Prepared Statement execute()

[Tolls]

I already have.

The OP is taking queries entered into the cmd line and wants to determine whether they are simple queries/inserts or whether they are an insert that requires further prompting for parameters. Of course this requires that the person entering the SQL knows what they have to enter for it to be valid, but that's a higher level issue than the immediate question of how to identify the two types of query.

[nitishjtm]

Then do a indexOf() on the sql string to see if there are any '?'. If there are then do your else stuff. Don't execute the prepared statement until after the if/else.

Of course this is not exactly going to be robust, but I'm guessing this is not a business app.

Yes sir, this is not a business app. I am learning jdbc.

[nitishjtm]

Don't execute the prepared statement until after the if/else.

I didn't got clearly what you said. Please explain me.
I tried with indexOf(), and it is recognizing ?.

package com.nitish.jdbc.preparedstatement;
import java.sql.*;
import	java.io.*;

public class Jdbc13{
	public static void main(String []s)throws Exception{
		System.setProperty("jdbc.drivers","oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","nitish");
		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
		String query=" ";
		while(!query.equals("exit")){
			       query = br.readLine();
		           PreparedStatement ps = con.prepareStatement(query);
	               Boolean b = ps.execute();
			       if(b){
				    ResultSet rs = ps.getResultSet();
				    while(rs.next()){
					System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3));
				}//while
			}//if
			else{
				     /*System.out.println("Enter Student ID :: ");
			         int stid=Integer.parseInt(br.readLine());
		    	     System.out.println("Enter Student Name :: ");
			         String name=br.readLine();
				     System.out.println("Enter Student Fee :: ");
				     double stfee = Double.parseDouble(br.readLine());
                     ps.setInt(1,stid);
			         ps.setString(2,name);
			    	 ps.setDouble(3,stfee);*/
				     int count = ps.getUpdateCount();
				     System.out.println("Rows Affected::"+count);
			}//else
		}//while
	}//main
}//class

If I comment the portion of the code written in else and give query such as

insert into student_details values(1,'Nitish',5000)
then its working. In case of
insert into student_details values(?,?,?)
rather than prompting me for entering the input, it is still waiting for input, which I don't know.
It is asking me to enter the details when I give query after removing the comment as:
insert into student_details values(1,'Nitish',5000)

[Tolls]

I didn't got clearly what you said. Please explain me.
I tried with indexOf(), and it is recognizing ?.

Well, the code you just posted doesn't show the indexOf part at all, so I'm guessing this isn't your latest version?

Anyway, this is the problem bit:

			       query = br.readLine();
		           PreparedStatement ps = con.prepareStatement(query);
	               Boolean b = ps.execute();  [B]<-- why execute here?[/B]
			       if(b){
... do stuff with resultset
				}//while
			}//if
			else{
... set all the values, [B]except you never execute this query[/B]
			}//else
		}//while
	}//main
}//class

So what I'm saying is, prepare the query, then do the indexOf check. If there is a '?' then do the stuff you currently have in else (to set the values for the insert). Then you execute the query.

If I comment the portion of the code written in else and give query such as

insert into student_details values(1,'Nitish',5000)
then its working. In case of
insert into student_details values(?,?,?)
rather than prompting me for entering the input, it is still waiting for input, which I don't know.
It is asking me to enter the details when I give query after removing the comment as:
insert into student_details values(1,'Nitish',5000)

I don't quite understand this bit. Of course when you comment out your prompt (the println()) you won't see it, so the system will then wait for your input.

[nitishjtm]

Sir, how can I invoke indexOf() on ps.

Well, the code you just posted doesn't show the indexOf part at all, so I'm guessing this isn't your latest version?

Earlier I did stupid job, I simply invoked indexOf() on query.

[Tolls]

indexOf() is a method on String. You use it on the SQL string you read in from the command line. You would know this if you'd clicked on the link I provided for it above.

[nitishjtm]

That I had already did:
System.out.println(query.indexOf("?"));
And it is returning the required index.
Here's the updated code:

package com.nitish.jdbc.preparedstatement;
import java.sql.*;
import	java.io.*;

public class Jdbc13{
	public static void main(String []s)throws Exception{
		System.setProperty("jdbc.drivers","oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","nitish");
		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
		String query=" ";
		while(!query.equals("exit")){
			try{
			       query = br.readLine();
				   PreparedStatement ps = con.prepareStatement(query);
				   System.out.println(query.indexOf("?"));
				   //Boolean b = ps.execute();
				   /*if(b){
				    ResultSet rs = ps.getResultSet();
				    while(rs.next()){
					System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3));
				}//while
			}//if*/
			//else{
				     System.out.println("Enter Student ID :: ");
			         int stid=Integer.parseInt(br.readLine());
		    	     System.out.println("Enter Student Name :: ");
			         String name=br.readLine();
				     System.out.println("Enter Student Fee :: ");
				     double stfee = Double.parseDouble(br.readLine());
                     ps.setInt(1,stid);
			         ps.setString(2,name);
			    	 ps.setDouble(3,stfee);
                     ps.execute();
				     System.out.println("Rows Affected::"+ps.getUpdateCount());
			       //}//else
			}//try
			catch(Exception e){}
		}//while
	}//main
}//class

I did it and its working but what about if{} part? I mean to say as excute() returns true for select query and false for update query and I want both of these operations to be performed.

[Tolls]

Can you please try and format that code properly.
Don;t use tabs...use spaces (4 is good for each indentation).

Your if statement should be

if (query.indexOf('?') > -1) {
    // Do whatever is required for setting values in the prepared statement.

[nitishjtm]

Can you please try and format that code properly.
Don;t use tabs...use spaces (4 is good for each indentation).

Sir, it is because I use EditPlus for typing the code.

Your if statement should be

if (query.indexOf('?') > -1) {
    // Do whatever is required for setting values in the prepared statement.

I did that what its returning me is this:

D:\AdvJava\PreparedStatement>javac -d . Jdbc13.java

D:\AdvJava\PreparedStatement>java com.nitish.jdbc.preparedstatement.Jdbc13
select * from Student_details
Enter Student ID ::

[Tolls]

Show your code, because I doubt you're doing what I have just suggested.

[nitishjtm]

Here's the code:

package com.nitish.jdbc.preparedstatement;
import java.sql.*;
import	java.io.*;

public class Jdbc13{
	public static void main(String []s)throws Exception{
		System.setProperty("jdbc.drivers","oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","nitish");
		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
		String query=" ";
		while(!query.equals("exit")){
			try{
			       query = br.readLine();
				   PreparedStatement ps = con.prepareStatement(query);
		      if(query.indexOf('?')>-1){
				    ResultSet rs = ps.getResultSet();
				    while(rs.next()){
					System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3));
				}//while
			}//if
			else{
				     System.out.println("Enter Student ID :: ");
			         int stid=Integer.parseInt(br.readLine());
		    	     System.out.println("Enter Student Name :: ");
			         String name=br.readLine();
				     System.out.println("Enter Student Fee :: ");
				     double stfee = Double.parseDouble(br.readLine());
                     ps.setInt(1,stid);
			         ps.setString(2,name);
			    	 ps.setDouble(3,stfee);
					 System.out.println("Rows Affected::"+ps.getUpdateCount());
			       }//else
				   ps.execute();
			}//try
			catch(Exception e){}
		}//while
	}//main
}//class

[Tolls]

OK.
If the query string has a '?' in it what do you want to do?

Please try and think through your logic because I am not writing this for you.
I have explained the order of things to you several times now.

[nitishjtm]

I got the clarity. indexOf() will -1 if it does not find '?'.
I had corrected it.
The code is as follows:

 if(query.indexOf('?')>-1){
				     System.out.println("Enter Student ID :: ");
			         int stid=Integer.parseInt(br.readLine());
		    	     System.out.println("Enter Student Name :: ");
			         String name=br.readLine();
				     System.out.println("Enter Student Fee :: ");
				     double stfee = Double.parseDouble(br.readLine());
                     ps.setInt(1,stid);
			         ps.setString(2,name);
			    	 ps.setDouble(3,stfee);
					 System.out.println("Rows Affected::"+ps.getUpdateCount());    
			}//if
			else{
					 ResultSet rs = ps.getResultSet();
				    while(rs.next()){
					     System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3));
				     }//while
			       }//else
				   ps.execute();

But I am getting update count 0. While in database the record has been inserted.

Doesn't it working against the behaviour of execute()? I mean to say, it returns true when we query the database, so in this it will return resultset while updating it returns false so it returns update count. So, again it is not working for select query.

[Tolls]

OK.
You are getting the updateCount before actually executing the statement.

Similarly in the else, you are getting the result set before you are exceuting the query.

I'd pull these into their own methods:

if ('?') {
    insert(query, con);
} else {
    select(query, con);
}

And create the prepared statement inside each of those methods, and execute them there. That way you can test them individually, because you are getting yourself confused by the volume of stuff going on here.

[nitishjtm]

I didn't said that I am getting resultset, what I got is this:

D:\AdvJava\PreparedStatement>java com.nitish.jdbc.preparedstatement.Jdbc13
insert into Student_details values(?,?,?)
Enter Student ID ::
12
Enter Student Name ::
most
Enter Student Fee ::
5000
Rows Affected::0
select * from Student_details
_

Means, I am not getting anything for select query. Anyway I will try what you suggested me.

[Tolls]

And that's happening becaue you haven't executed the query at theat point:

if(query.indexOf('?')>-1) {
    // set the prepared statement values
    System.out.println("Rows Affected::"+ps.getUpdateCount()); // You do updateCount here
} else {
    ResultSet rs = ps.getResultSet();  // You do getResultSet() here
}
ps.execute();  // But you don't actually execute the statement until here!

[nitishjtm]

Thanks sir, I corrected it and its working properly.
During initial discussion you told that this is not the effective way to do this. Is there any other way to perform the same functionality.

[Tolls]

Well, anything that involves getting a query direct from the user, and where that query has to be of one of two types, is seriously prone to user error. Also it's hardly secure. What's to stop the user writing "DELETE * FROM student_details"?

If there's only one of two options then I'd keep the two SQL statements in the system and ask the user:
What action do you want?
1) add a new student
2) list all students

Or something to that effect.