Using Prepared Statements in JAVA

[suretd]

Good Day All,

Not sure if I should post this request here or under the SQL Forum. I am studying JAVA and my code is for JAVA, but my query is regarding the SQL prepared statements to be used in my JAVA code.

I have a program called SingleUser.java. The object of this program is to request a user's id and then list all the stocks that this user has. I refer to a Database StockTracker to get the details.

I am not sure if I am using the prepared statements correctly. I am getting an error: "Cannot resolve symbol", which I know one gets when a parameter is missing or if there is a spelling mistake. But I cannot see any spelling errors, so I must be missing a parameter. I am just starting to learn about SQL and SQL commands, so, if anyone has some advise ... especially if anyone knows about a site to learn SQL where it is in easy to understand English and terminology.

I am attaching my code ... and just to give some explanation: "userID", "symbol" and "name" used in the prepared statements are the headings of the columns in my database.

I have created a String usrID to get the user to insert a userID on the command prompt screen.

Here is the code:

import java.io.*;
import java.sql.*;

public class SingleUser
{
	public static void main(String[] args)throws Exception
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

		String url = "jdbc:odbc:StockTracker";

		Connection con = DriverManager.getConnection(url);
		PreparedStatement pStmt = con.createStatement("SELECT userID, symbol FROM UserStocks");
		PreparedStatement pStmt1 = con.createStatement("SELECT name FROM Stocks");

		System.out.println("Stock holdings for User: " + pStmt);
		System.out.println("Stock - Description");
		System.out.println("-------------------------------------------");

		BufferedReader dataIn = new BufferedReader(new InputStreamReader(System.in));

		for(int i = 0; 1 > 0; i++)
		{
			System.out.print("Enter User ID: ");
			String usrID = dataIn.readline();

			pStmt.setString(1, userID);
			pStmt1.setString(1, symbol);
			pStmt1.setString(2, name);
			pStmt.executeUpdate();
			pStmt1.executeUpdate();
		}

		pStmt.close();

	}
}

So, my final question is: what am I doing wrong so that my code is not compiling?

If you would like to see the error codes as well, here is it:

C:\Documents and Settings\Suret\My Documents\studies\Assignment 6\Question 5\SingleUser.java:21: cannot resolve symbol
symbol  : method createStatement (java.lang.String)
location: interface java.sql.Connection
		PreparedStatement pStmt = con.createStatement("SELECT userID, symbol FROM UserStocks");
                                             ^
C:\Documents and Settings\Suret\My Documents\studies\Assignment 6\Question 5\SingleUser.java:22: cannot resolve symbol
symbol  : method createStatement (java.lang.String)
location: interface java.sql.Connection
		PreparedStatement pStmt1 = con.createStatement("SELECT name FROM Stocks");
                                              ^
C:\Documents and Settings\Suret\My Documents\studies\Assignment 6\Question 5\SingleUser.java:33: cannot resolve symbol
symbol  : method readline ()
location: class java.io.BufferedReader
			String usrID = dataIn.readline();
                                             ^
C:\Documents and Settings\Suret\My Documents\studies\Assignment 6\Question 5\SingleUser.java:35: cannot resolve symbol
symbol  : variable userID 
location: class SingleUser
			pStmt.setString(1, userID);
                                           ^
C:\Documents and Settings\Suret\My Documents\studies\Assignment 6\Question 5\SingleUser.java:36: cannot resolve symbol
symbol  : variable symbol 
location: class SingleUser
			pStmt1.setString(1, symbol);
                                            ^
C:\Documents and Settings\Suret\My Documents\studies\Assignment 6\Question 5\SingleUser.java:37: cannot resolve symbol
symbol  : variable name 
location: class SingleUser
			pStmt1.setString(2, name);
                                            ^
6 errors

Tool completed with exit code 1

[JamesCherrill]

First error: it's saying there is no createStatement in the Connection class that takes a String as its parameter. Strangely enough, when I look at the API for Connection, that's what I find as well!

[suretd]

Thank you very much ... i have NO clue why I made it createStatement ... it had to be prepareStatement ...

I think I got the prepareStatement confused with the createStatement, as I was using the createStatement in my previous program ... :confused:

Thank You for pointing that out to me.
:)

[suretd]

I would like to ask another question on this thread. How do I link the user ID, that was inserted via the keyboard by the user, to the details of that userID in the database using the prepared statement?

[CameronBevis]

The situation you describe is exactly the case for using a prepared statement. A "regular" statement is static. You can't change the SQL. Changing the SQL requires getting another Statement.

Using a PreparedStatement allows you to parametize the SQL. It uses a "?" placeholder for parameters, and then you set the parameter before executing the PreparedStatement. This is much more efficient for re-executing a query where one or more elements of the SQL must change. The reason is that the RDBMS will precompile and optimize the parametized query ONCE, rather than having to do it all over again and again if you were to use new Statement objects.