?
Solved

Using Java Prepared Statements

Posted on 2008-11-02
8
Medium Priority
?
1,065 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 12

Accepted Solution

by:
jazzIIIlove earned 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
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…
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 will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

765 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