select .... where... query for a variable?

Dear,
I want to write a select ... where... mysql query in my java program (using jdbc). but the problem is that in where section i m gonna refer to a string variable. but i don know if it is possible?
here is the mentioned query for select where:
String select = "Select name,manfac,Qmax,Qmin,Qw,Pmax,Pmin,Pw,deadline,Tw"+" from tseller where name = 'targetName' order by Pmax";
but actually tagetName is a string variable. how should i write that?

thnx
Sahar.

Using java.sql.PreparedStatement.

Dear ,
I try to use prepared statements, but the examples i find are mostly using oracle but im working with MYSQL. any way i've tried this code. unfortunately it is not working! i think it can not fetch information from table in mysql. Do you have any idea what can i do?
here is my code:

Connection conn = null;
PreparedStatement pstmt = null;
String targetGood = "Celeron";

try{
Class.forName("com.mysql.jdbc.Driver").newInstance();

Connection con= DriverManager.getConnection("jdbc:mysql://localhost/booktrading?"+"user=root&password=444");
String select = "Select * from tseller where name= ? ";
pstmt = con.prepareStatement(select); // create a statement
pstmt.setString(1, "targetGood"); // set input parameter 1
ResultSet rows = pstmt.executeQuery();
while(rows.next())
{
String name = rows.getString("name");
String manfac = rows.getString("manfac");
double Tw = rows.getDouble("Tw");
System.out.println("the table shows: ");
System.out.println(name +" "+manfac+" "+Tw);
}
}
catch (ClassNotFoundException e)
{
System.out.println(e.getMessage());
e.printStackTrace();
System.out.println("failed to connect to DB");
System.exit(0);
}
catch(SQLException ex)
{
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
catch(Exception ex){ex.printStackTrace();}

}

thnx,
sahar.

Hi Sahar,

shouldn't you write

pstmt.setString(1, targetGood); // set input parameter 1

instead of

pstmt.setString(1, "targetGood"); // set input parameter 1

Regards,

John

krishna bala wrote: String targetName = "some value " ; StringBuilder query = new StringBuilder() ; query.append("Select name,manfac,Qmax,Qmin,Qw,Pmax,Pmin,Pw,deadline,Tw from tseller where name=").append(targetName).append(" order by Pmax"); then you can pass the query.toString() to the executeQuery method .

Look up something called "SQL injection". Then you'll see why this is a bad idea.

Dear ,
I ty it but it throws an error. her is the code and error:

code :

try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con= DriverManager.getConnection("jdbc:mysql://localhost/booktrading?"+"user=root&password=444");
StringBuilder query = new StringBuilder() ;
query.append("Select name,manfac,Qmax,Qmin,Qw,Pmax,Pmin,Pw,deadline,Tw from tseller where name=").append(targetGood).append(" order by Pmax");
ResultSet rows = executeQuery(query.toString());
while(rows.next())
{

String name = rows.getString("name");
}

error :

C:\Documents and Settings\student\My Documents\My research\Java\catalogue test\src\DB\DB.java:47: cannot find symbol
symbol : method executeQuery(java.lang.String)
location: class DB.DB
ResultSet rows = executeQuery(query.toString());
1 error

thnx,
Sahar.

You'll need a Statement instance, and call executeQuery on that instance.

Dear,
I run this code:
Connection con= DriverManager.getConnection("jdbc:mysql://localhost/booktrading?"+"user=root&password=444");
StringBuilder query = new StringBuilder() ;
String targetGood = "Celeron";
query.append("select * from tseller where name=").append(targetGood).append(" order by Pmax");
Statement s = con.createStatement();
String sahar=query.toString();
System.out.println(sahar);
ResultSet rows = s.executeQuery(sahar);

and it throws this error:
SQLException: Unknown column 'Celeron' in 'where clause'
SQLState: 42S22
VendorError: 1054

As i find this error is related to an un exist column.(as you can see in query) name refers to name as column!(which is actually exist!!) I dont know how it jumps suddenly to targetGood without reading name?

thnx,
Sahar.

Hi Sahar,

if you don't use parameters for your query you have to put your String into quotation marks. If not the DB thinks the value (in your case Celeron) is a column name.

But as Rob already mentioned you should prefer using parameters as you did in your first attempt.

John

Dear john ,roband krishna,
thank you sooooo much!