Solved

Using Java Prepared Statements

Posted on 2008-11-02
8
1,046 Views
Last Modified: 2012-05-05
I'm trying to create a dynamic SQL query using Java.  I've been told that prepared statements are the way to go since normal SQL statements can be a security risk.

My understanding of prepared statements is that I can create a statement like:

String sql = "SELECT * FROM myTable T WHERE T.attribute = ?";
PreparedStatement stmt = myconnection.prepareStatement(sql);
Then I can set ? at run time using
pstmt.setString(1, myVariable);

My question is centered around what is fair game for the ? replacement.  I've only ever seen it used in the WHERE clause (or in the values for an insert or update).  For example, can I choose the table in the FROM clause or selection criteria in the SELECT at run time?

I'm trying to understand how flexible prepared statements are.  

Thanks for any comments!
0
Comment
Question by:Zodan
8 Comments
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22863338
yes..
you can set the id in the runtime by modifying the below code...or if the type is decided at runtime, you can use setObject or getObject functions of PreparedStatement...


try {

  Connection con = DriverManager.getConnection(url);

  String select = "SELECT * FROM customer_tbl WHERE customer_id = ?";

  PreparedStatement ps = con.prepareStatement(select);

  ResultSet rs = null;

  for(int i=0;i<10;i++) {

//  PreparedStatement Example

    ps.setString(1,""+i);

    rs = ps.executeQuery();

  }

} catch (SQLException sqle) {sqle.printStackTrace();}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22863348
You can't parameterize the table name i'm afraid. But you can set parameters at run time
0
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22863350
An example of using setObject...

Best regards...
import java.sql.*;
 

public class PreparedStatementSetObject{

  public static void main(String[] args) {

    System.out.println("Prepared Statement Set Array Example!");

    Connection con = null;

    try{

      Class.forName("com.mysql.jdbc.Driver");

      con = DriverManager.getConnection

("jdbc:mysql://localhost:3306/jdbctutorial","root","root");

      try{

        PreparedStatement prest = con.prepareStatement("insert emp_sal values(?,?)");

        prest.setObject(1,"Sushil");

        prest.setObject(2,15000);

        int n = prest.executeUpdate();

        System.out.println(n + " Record is added in the table.");

        con.close();

      }

      catch (SQLException s){

        System.out.println("SQL statement is not executed!");

      }

    }

    catch (Exception e){

      e.printStackTrace();

    }

  }

} 

Open in new window

0
 
LVL 12

Accepted Solution

by:
jazzIIIlove earned 500 total points
ID: 22863386
Hi there;

and no for table name
You can only use the ? for "data" places. Not table name and not field/column names as far as i know...

Best regards...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

by:jamesgu
ID: 22864072
if the table name is only known at runtime, you cannot use binding parameters.

You may try this

String sql = "SELECT * FROM " + myTable + " T WHERE T.attribute = " + value;
PreparedStatement stmt = myconnection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
...
0
 
LVL 5

Expert Comment

by:Triguna
ID: 22864847
If you still didnt find an answer from the above post,
Go through the following link. You will definitely find an answer for all your queries.
http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html?page=1
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
0
 

Author Closing Comment

by:Zodan
ID: 31512540
Thank you for the code snips and explanation
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 22887872
jazzIIIlove, can you tell me why you accepted a comment that repeated one i made?
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now