aspose file tools*
The moose likes JDBC and the fly likes select .... where... query for a variable? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select .... where... query for a variable? " Watch "select .... where... query for a variable? " New topic
Author

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

sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
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.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19696
    
  20

Using java.sql.PreparedStatement.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
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.
krishna bala
Ranch Hand

Joined: Jul 20, 2009
Posts: 61
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
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
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19696
    
  20

krishna bala wrote:

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

Joined: Jul 06, 2009
Posts: 102
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.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19696
    
  20

You'll need a Statement instance, and call executeQuery on that instance.
sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
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.

John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
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


sahar sa
Ranch Hand

Joined: Jul 06, 2009
Posts: 102
Dear john ,roband krishna,
thank you sooooo much!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: select .... where... query for a variable?