Solved

Using Java Prepared Statements

Posted on 2008-11-02
8
1,058 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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
 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
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.

688 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