• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Using java.sql.PreparedStatement.
 
sahar sa
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

krishna bala wrote:


Look up something called "SQL injection". Then you'll see why this is a bad idea.
 
sahar sa
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You'll need a Statement instance, and call executeQuery on that instance.
 
sahar sa
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear john ,roband krishna,
thank you sooooo much!
 
reply
    Bookmark Topic Watch Topic
  • New Topic