Solved

Using Java Prepared Statements

Posted on 2008-11-02
8
1,050 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
recursion example 16 124
Weekend adv creator 3 36
javap not working 8 43
How to convert from xls to xlsx using java 7 35
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
The viewer will learn how to implement Singleton Design Pattern in Java.

816 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

7 Experts available now in Live!

Get 1:1 Help Now