use java variable in sql query

[miraj0072004]

hey....I have a question...this seems such a trivial question but please help me...I'm trying to use a java variable ex:-
int x=request.getParameter(ID) and then use it in an sql query,like,

select name,date,tel_no from items where ID=x

it doesn't work...im very new to sql and jsp...so please help me get around this..thanks in advance :) (I'm using jsp pages in netbeans)

[peter_budo]

If you looked into the post on the top of JSP section you would find 2 of few options how to do it.
Simple by hacking together SQL query string such as this Statement example

Statement stmt = null;

	String strQuery = 
	"SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup "
	+"FROM user u, usergroup ug WHERE uid='"+userName+"' AND password='"+password+"' AND groupName IN"
	+" (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))";
	stmt = conn.createStatement();
	rs = stmt.executeQuery( strQuery);

which is the most basic and most error prone as the single and double quotes including plus sign to join strings could become quickly very confusing with complex queries.

Or perhaps with use PreparedStatement which is cleaner

PreparedStatement preparedStatement = null;
	String strQuery = 
	"SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup "
	+"FROM user u, usergroup ug WHERE uid=? AND password=? AND groupName IN"
	+" (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))";
				
	preparedStatement = conn.prepareStatement(strQuery);
	preparedStatement.setString(1,userName);
	preparedStatement.setString(2,password);
	rs = preparedStatement.executeQuery();

where the question marks in the query string are replaced by the values suplied just before executing the query through setString, setInt and similar methods available in this class

[cliver]

taxn for the post

am having a similar problem

try {
                PreparedStatement preparedStatement = null;
                String strQuery="SELECT pd_id,pd_name,pd_brand,pd_price FROM product WHERE pd_id=?";

                preparedStatement = connection.prepareStatement(strQuery);
                preparedStatement.setInt(1, pid);
                ResultSet rsx = preparedStatement.executeQuery();
                
                String nom  =rsx.getString("pd_name");
                String bran =rsx.getString("pd_brand");
                String cost = rsx.getString("pd_price");
                String quan = amt.getText();
                inmodel.insertRow(intable.getRowCount(),new Object[]{pid,nom,bran,quan,cost});

error
java.sql.SQLException: Before start of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5656)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5576)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5616)
at DepartmentStore.POS.insertRow(POS.java:123)

line 123 is

String nom  =rsx.getString("pd_name");