[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert Existing Code to Prepared Statement -- Question for TimYates

Posted on 2005-04-07
15
Medium Priority
?
268 Views
Last Modified: 2010-03-31
TimYates --

You helped me a few days ago with a problem and you recommended I use a prepared statement to resolve my issue. Below is the code I am using right now and I would like to convert this to a prepared statement. Please look this over and provide a recommended solution for converting this code to a prepared statement.

Thanks RCMB

      protected boolean performAdd(DBControllerImpl dbcon, Hashtable params) {
            boolean flag = false;
            String name = (String) params.get("name");
            String dirnum = (String) params.get("dir_number");
            String dirtype = (String) params.get("dir_type");
            String chnum = (String) params.get("ch_number");
            String dirdate = (String) params.get("dir_date");
            String orig = (String) params.get("originator");
            String chdate = (String) params.get("ch_date");
            String distlist = (String) params.get("dist_list");
            String dirstatus = (String) params.get("dir_status");
            String remarks = (String) params.get("remarks");
            remarks = remarks.replace('\'', ' ');
            String dirsubj = (String) params.get("dir_subj");
            String entby = (String) params.get("entered_by");
            String entdate = (String) params.get("entered_dt");
            String action = (String) params.get("action");
            String modby = (String) params.get("modified_by");
            String moddate = (String) params.get("modified_dt");
            String dirid = (String) params.get("directiveid");

            byte[] src = (byte[]) params.get("src");
            if (name == null || src == null) {
                  setErrorMsg("The file parameters are incorrect.");
                  setErrorDetails("");
                  return false;
            }
            int pos = name.lastIndexOf('.');
            String extension = pos > -1 ? name.substring(pos + 1) : "";
            String sql = "select FT.Ft_ID as [type], FTD.Ft_ID as [def_type] from FileType FT "
                        + "left outer join FileType FTD on FTD.Ft_Ext='"
                        + extension
                        + "' " + "where FT.Ft_Ext=''";
            java.sql.ResultSet rs = dbcon.execQuery(sql);
            try {
                  if (!rs.next()) {
                        setErrorMsg("Default MIME type is not found.");
                        setErrorDetails("");
                        return false;
                  }

                  String def_type = rs.getString("type");
                  String type = rs.getString("def_type");
                  ArrayList sql_params = new ArrayList();
                  Hashtable p = new Hashtable();
                  p.put("type", new Integer(Types.BLOB));
                  p.put("value", src);
                  sql_params.add(p);
                  //add new directive
                  if (action.equals("adddirective")) {
                        //add new directive with file upload
                        if (pos > -1) {
                              flag = dbcon.execSQL("INSERT INTO pers_directives "
                                          + "(dir_number, dir_type, dir_change, dir_date, "
                                          + "originator, ch_date, dist_list, dir_status, "
                                          + "dir_remarks, dir_subj, filename, filetype_id, "
                                          + "entered_by, entered_dt, file_source) "
                                          + "VALUES ('" + dirnum + "', '" + dirtype + "', '"
                                          + chnum + "', '" + dirdate + "', '" + orig
                                          + "', '" + chdate + "', '" + distlist + "', '"
                                          + dirstatus + "', '" + remarks + "', " + "'"
                                          + dirsubj + "', '" + name + "', "
                                          + (type == null ? def_type : type) + ", " + "'"
                                          + entby + "', '" + entdate + "', ?)", sql_params);

                              if (!flag) {
                                    setErrorMsg("Adding of file failed");
                                    setErrorDetails("");
                              }
0
Comment
Question by:rcmb
  • 7
  • 7
15 Comments
 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 13731183
Would you accept assistance from someone other than Tim? If not, I'll defer to him.
:-)

Regards,
Jim Cakalic
0
 
LVL 12

Author Comment

by:rcmb
ID: 13731305
sure -- i was just giving tim the first cut because he helped with the other issue.

RCMB
0
 
LVL 92

Expert Comment

by:objects
ID: 13731463
Does DBControllerImpl support PreparedStatements?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Author Comment

by:rcmb
ID: 13731996
yes
0
 
LVL 92

Expert Comment

by:objects
ID: 13732030
>         + entby + "', '" + entdate + "', ?)", sql_params);

actually that trailing ? seems to suggest it is already using a PreparedStatement.

Looks like what you need to do is replace all the parameters you are currently inserting in the query with ?, and add the parameter values to the sql_params list.
0
 
LVL 12

Author Comment

by:rcmb
ID: 13732095
objects,

are you suggesting something like:

statement.setString(5, entby);
statement.setString(6, endate);
statement.set???(7, sql_params);

What would I enter for the ??? -- BinaryStream?

RCMB
0
 
LVL 92

Expert Comment

by:objects
ID: 13732170
I'm not aware of the details of the class you are using so can't say, do you have javadoc available where I could view it?
What is the sql_params list used for?
0
 
LVL 12

Author Comment

by:rcmb
ID: 13732623
sql_params is used to capture the BLOB and add it to the insert statement. The BLOB is an uploaded file.

not sure what you mean here:

"do you have javadoc available where I could view it"

RCMB
0
 
LVL 92

Expert Comment

by:objects
ID: 13732645
i need to know what methods are available in that class to be able to determine how to set the parameters.

> sql_params is used to capture the BLOB and add it to the insert statement

how does the class achieve this
0
 
LVL 12

Author Comment

by:rcmb
ID: 13732718
Here is the entire page:

package mil.navy.snadis.web.model;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Vector;

import mil.navy.snadis.db.model.DBControllerImpl;
import mil.navy.snadis.util.ConvertDate;

public class FileImpl extends SimpleListImpl {

      public FileImpl() {
            setTable("pers_directives");
      }

      public String getName() {
            return "file";
      }

      public String getAction() {
            return "action";
      }

      public String getDirname() {
            return "dirname";
      }

      public void setDirtype(int inDirtype) {
            this.Dirtype = inDirtype;
      }

      public void setDirtype(String inDirtype) {
            int Dirtype;

            Dirtype = Integer.parseInt(inDirtype);
            setDirtype(Dirtype);
      }

      public int getDirtype() {
            return Dirtype;
      }

      public void setDirid(int inDirid) {
            this.Dirid = inDirid;
      }

      public void setDirid(String inDirid) {
            int Dirid;

            Dirid = Integer.parseInt(inDirid);
            setDirid(Dirid);
      }

      public int getDirid() {
            return Dirid;
      }

      public String getChnum() {
            return "chnum";
      }

      private void setDirdate(Date tmpDirdate) {
            this.Dirdate = tmpDirdate;
      }

      public void setDirdate(String inDirdate) {
            this.Dirdate = ConvertDate.StringToDate(inDirdate);
      }

      public String getDirdate() {
            return "dirdate";
      }

      public String getOrig() {
            return "orig";
      }

      private void setChdate(Date tmpChdate) {
            this.Chdate = tmpChdate;
      }

      public void setChdate(String inChdate) {
            this.Chdate = ConvertDate.StringToDate(inChdate);
      }

      public String getChdate() {
            return "chdate";
      }

      public String getDistlist() {
            return "distlist";
      }

      public String getDirstatus() {
            return "dirstatus";
      }

      public String getRemarks() {
            return "remarks";
      }

      public String getDirsubj() {
            return "dirsubj";
      }

      public String getEntby() {
            return "entby";
      }

      public String getModby() {
            return "modby";
      }

      private void setEntdate(Date tmpEntdate) {
            this.Entdate = tmpEntdate;
      }

      public void setEntdate(String inEntdate) {
            this.Entdate = ConvertDate.StringToDate(inEntdate);
      }

      public String getEntdate() {
            return "Entdate";
      }

      private void setModdate(Date tmpModdate) {
            this.Moddate = tmpModdate;
      }

      public void setModdate(String inModdate) {
            this.Moddate = ConvertDate.StringToDate(inModdate);
      }

      public String getModdate() {
            return "Moddate";
      }

      protected int Dirtype;

      protected int Dirid;

      private Date Entdate;

      private Date Dirdate;

      private Date Chdate;

      private Date Moddate;

      protected boolean performAdd(DBControllerImpl dbcon, Hashtable params) {
            boolean flag = false;
            String name = (String) params.get("name");
            String dirnum = (String) params.get("dir_number");
            String dirtype = (String) params.get("dir_type");
            String chnum = (String) params.get("ch_number");
            String dirdate = (String) params.get("dir_date");
            String orig = (String) params.get("originator");
            String chdate = (String) params.get("ch_date");
            String distlist = (String) params.get("dist_list");
            String dirstatus = (String) params.get("dir_status");
            String remarks = (String) params.get("remarks");
            remarks = remarks.replace('\'', ' ');
            String dirsubj = (String) params.get("dir_subj");
            String entby = (String) params.get("entered_by");
            String entdate = (String) params.get("entered_dt");
            String action = (String) params.get("action");
            String modby = (String) params.get("modified_by");
            String moddate = (String) params.get("modified_dt");
            String dirid = (String) params.get("directiveid");

            byte[] src = (byte[]) params.get("src");
            if (name == null || src == null) {
                  setErrorMsg("The file parameters are incorrect.");
                  setErrorDetails("");
                  return false;
            }
            int pos = name.lastIndexOf('.');
            String extension = pos > -1 ? name.substring(pos + 1) : "";
            String sql = "select FT.Ft_ID as [type], FTD.Ft_ID as [def_type] from FileType FT "
                        + "left outer join FileType FTD on FTD.Ft_Ext='"
                        + extension
                        + "' " + "where FT.Ft_Ext=''";
            java.sql.ResultSet rs = dbcon.execQuery(sql);
            try {
                  if (!rs.next()) {
                        setErrorMsg("Default MIME type is not found.");
                        setErrorDetails("");
                        return false;
                  }

                  String def_type = rs.getString("type");
                  String type = rs.getString("def_type");
                  ArrayList sql_params = new ArrayList();
                  Hashtable p = new Hashtable();
                  p.put("type", new Integer(Types.BLOB));
                  p.put("value", src);
                  sql_params.add(p);
                  //add new directive
                  if (action.equals("adddirective")) {
                        //add new directive with file upload
                        if (pos > -1) {
                              flag = dbcon.execSQL("INSERT INTO pers_directives "
                                          + "(dir_number, dir_type, dir_change, dir_date, "
                                          + "originator, ch_date, dist_list, dir_status, "
                                          + "dir_remarks, dir_subj, filename, filetype_id, "
                                          + "entered_by, entered_dt, file_source) "
                                          + "VALUES ('" + dirnum + "', '" + dirtype + "', '"
                                          + chnum + "', '" + dirdate + "', '" + orig
                                          + "', '" + chdate + "', '" + distlist + "', '"
                                          + dirstatus + "', '" + remarks + "', " + "'"
                                          + dirsubj + "', '" + name + "', "
                                          + (type == null ? def_type : type) + ", " + "'"
                                          + entby + "', '" + entdate + "', ?)", sql_params);
                              
                              if (!flag) {
                                    setErrorMsg("Adding of file failed");
                                    setErrorDetails("");
                              }
                              //add new directive without file upload
                        } else if (pos < 0) {
                              flag = dbcon.execSQL("INSERT INTO pers_directives "
                                          + "(dir_number, dir_type, dir_change, dir_date, "
                                          + "originator, ch_date, dist_list, dir_status, "
                                          + "dir_remarks, dir_subj, entered_by, entered_dt) "
                                          + "VALUES ('" + dirnum + "', '" + dirtype + "', '"
                                          + chnum + "', '" + dirdate + "', '" + orig
                                          + "', '" + chdate + "', '" + distlist + "', '"
                                          + dirstatus + "', '" + remarks + "', " + "'"
                                          + dirsubj + "', '" + entby + "', '" + entdate
                                          + "')");
                              if (!flag) {
                                    setErrorMsg("Adding of information failed");
                                    setErrorDetails("");
                              }
                        }
                        //update directive information
                  } else if (action.equals("updatedirective")) {
                        //update directive information with file upload
                        if (pos > -1) {
                              flag = dbcon.execSQL("UPDATE pers_directives SET "
                                          + "dir_number='" + dirnum + "', dir_type='"
                                          + dirtype + "', " + "dir_change='" + chnum
                                          + "', dir_date='" + dirdate + "', "
                                          + "originator='" + orig + "', ch_date='" + chdate
                                          + "', " + "dist_list='" + distlist
                                          + "', dir_status='" + dirstatus + "', "
                                          + "dir_remarks='" + remarks + "', dir_subj='"
                                          + dirsubj + "', " + "filename='" + name
                                          + "', filetype_id="
                                          + (type == null ? def_type : type) + ", "
                                          + "modified_by='" + modby + "', modified_dt='"
                                          + moddate + "', "
                                          + "file_source=? WHERE directives_id= '" + dirid
                                          + "'", sql_params);

                              if (!flag) {
                                    setErrorMsg("Updating of file failed");
                                    setErrorDetails("");
                              }
                              //update directive information without file upload
                        } else if (pos < 0) {
                              flag = dbcon.execSQL("UPDATE pers_directives SET "
                                          + "dir_number='" + dirnum + "', dir_type='"
                                          + dirtype + "', " + "dir_change='" + chnum
                                          + "', dir_date='" + dirdate + "', "
                                          + "originator='" + orig + "', ch_date='" + chdate
                                          + "', " + "dist_list='" + distlist
                                          + "', dir_status='" + dirstatus + "', "
                                          + "dir_remarks='" + remarks + "', dir_subj='"
                                          + dirsubj + "', " + "modified_by='" + modby
                                          + "', modified_dt='" + moddate + "' "
                                          + "WHERE directives_id= '" + dirid + "'");

                              if (!flag) {
                                    setErrorMsg("Updating of file failed");
                                    setErrorDetails("");
                              }
                        }
                  }
            } catch (Exception e) {
                  setErrorMsg("SQLError" + e.getMessage());
                  setErrorDetails(e.getMessage() + " " + sql);
                  return false;
            } finally {
                  try {
                        rs.close();
                  } catch (Exception ee) {
                  }
            }
            return flag;
      }

      protected Hashtable performGet(Hashtable params) {
            String id = (String) params.get("id");
            if (id == null) {
                  setErrorMsg("The file id is incorrect.");
                  setErrorDetails("");
                  return null;
            }
            Hashtable hash = new Hashtable();
            DBControllerImpl dbcon = null;
            try {
                  if (getDBCon() == null)
                        dbcon = new DBControllerImpl();
            } catch (Exception e) {
                  setErrorMsg("Database connection failed");
                  setErrorDetails(e.getMessage());
                  return null;
            }
            String sql = "select F.*, FT.Ft_Mime from pers_directives F "
                        + "inner join FileType FT on F.filetype_id = FT.Ft_ID "
                        + "where directives_id=" + id;
            try {
                  java.sql.ResultSet rs = dbcon.execQuery(sql);

                  if (!rs.next()) {
                        setErrorMsg("File is not found.");
                        setErrorDetails("");
                        return null;
                  }
                  hash.put("id", rs.getInt("directives_id") + "");
                  hash.put("name", rs.getString("filename"));
                  hash.put("desc", rs.getString("dir_subj"));
                  hash.put("mime", rs.getString("Ft_Mime"));

                  InputStream is = null;
                  try {
                        is = rs.getBinaryStream("file_source");
                  } catch (Exception ex) {
                        setErrorMsg("Can't cc read file from DB.");
                        setErrorDetails("");
                        return null;
                  }

                  if (is == null) {
                        setErrorMsg("Can't read file from DB.");
                        setErrorDetails("");
                        return null;
                  }

                  byte buf[] = new byte[1];
                  int bytesRead = 0;
                  int numBytes = 0;
                  int currentLen = 1;
                  byte content[] = new byte[currentLen];
                  try {
                        while ((bytesRead = is.read(buf)) != -1) {
                              if (numBytes + bytesRead > currentLen) {
                                    byte old[] = content;
                                    content = new byte[currentLen * 2];
                                    for (int ct = 0; ct < currentLen; ct++) {
                                          content[ct] = old[ct];
                                    }
                                    currentLen *= 2;
                              }
                              for (int ct = 0; ct < bytesRead; ct++) {
                                    content[numBytes + ct] = buf[ct];
                              }
                              numBytes += bytesRead;
                        }
                  } catch (IOException e) {
                        setErrorMsg("Can't read file from DB.");
                        setErrorDetails("");
                        return null;
                  }
                  hash.put("src", content);
                  rs.close();
            } catch (Exception e) {
                  setErrorMsg("SQLError");
                  setErrorDetails(e.getMessage() + " " + sql);
                  return null;
            }
            return hash;
      }

      public Vector performGetAll(Hashtable param) {

            Vector arr = new Vector();
            DBControllerImpl dbcon = null;
            try {
                  if (getDBCon() == null)
                        dbcon = new DBControllerImpl();
            } catch (Exception e) {
                  setErrorMsg("Database connection failed");
                  setErrorDetails(e.getMessage());
                  return null;
            }
            String sql = "select * from pers_directives ";
            try {
                  java.sql.ResultSet rs = dbcon.execQuery(sql);
                  while (rs.next()) {
                        Hashtable hash = new Hashtable();
                        hash.put("id", rs.getInt("directives_id") + "");
                        hash.put("name", rs.getString("filename"));
                        hash.put("desc", rs.getString("dir_subj"));
                        arr.addElement(hash);
                  }
                  rs.close();
            } catch (Exception e) {
                  setErrorMsg("SQLError");
                  setErrorDetails(e.getMessage() + " " + sql);
                  return null;
            }
            return arr;
      }

}
0
 
LVL 92

Expert Comment

by:objects
ID: 13732753
Its the DBControllerImpl class that I'm referring to. It is creating the PreparedStatement and somehow assigning the parameters.
0
 
LVL 12

Author Comment

by:rcmb
ID: 13732781
Here is DBControllerImpl class:

package mil.navy.snadis.db.model;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Hashtable;

import mil.navy.snadis.db.api.DBController;


public class DBControllerImpl {
      private Connection con;
      private DBController connMgr;
      String strPoolName       = null;

      public DBControllerImpl (){
        try {
                  //create an object of DBController class.
                  connMgr = DBController.getInstance();
            }catch (Exception e){
                  System.out.println("DB Connection problem");
            }
    }

    protected void getConnection(){
        if(connMgr == null){
           connMgr = DBController.getInstance();
            }
              //get the name of the connection pool
            String strPoolName  = connMgr.getPoolName();
            //get the connection from the pool
            con = connMgr.getConnection(strPoolName);
            if(con==null){
                  System.out.println("Error in Database connection");
                  return;
        }
    }

    protected void freeConnection(){
        if(con!=null && strPoolName!=null)
            try{
                connMgr.freeConnection(strPoolName , con);
            }catch(Exception e){}
    }

    public boolean execSQL(String sql){
             boolean blBool = false;
            try      {
            getConnection();
            Statement st = con.createStatement();
                st.executeUpdate( sql );
                 blBool = true;
            } catch(Exception e)      {
                    System.out.println("Exception at" + sql + "     : " + e);
                        return false;
            } finally{
            freeConnection();
        }
            return blBool;
    }

    public boolean execSQL(String sql, ArrayList params){
             boolean blBool = false;
            try      {
            getConnection();
            PreparedStatement prstmt = con.prepareStatement(sql);
            Hashtable item;
            for(int i=0;i<params.size();i++){
                item = (Hashtable)params.get(i);
                Integer itype = (Integer)item.get("type");
                Object ivalue = item.get("value");
                if(itype==null || ivalue==null)continue;
                int type = itype.intValue();
                switch(type){
                    case  Types.BLOB:{
                        ByteArrayInputStream is = new ByteArrayInputStream((byte[])ivalue);
                        prstmt.setBinaryStream(i+1,is,is.available());
                    }
                    break;
                    default: prstmt.setObject(i+1,item.get("value"),type);
                }
            }
            prstmt.execute();
                 blBool = true;
            } catch(Exception e)      {
                    System.out.println("Exception at" + sql + "     : " + e);
                        return false;
            } finally{
            freeConnection();
        }
            return blBool;
    }

      public ResultSet execQuery(String queryString ) {
            ResultSet rs = null;
            try      {
                getConnection();
                PreparedStatement pstmt = con.prepareStatement(queryString);
                      rs = pstmt.executeQuery();
            }catch(Exception e)      {
                  System.out.println("Exception at " + queryString + "     : " + e);
                  return null;
        }finally{
            freeConnection();
        }
            return rs;
      }
}
0
 
LVL 92

Accepted Solution

by:
objects earned 2000 total points
ID: 13733041
ok thanks, that list is where you need to add the parameters

For example the following existing code adds the value for the blob parameter:

   Hashtable p = new Hashtable();
   p.put("type", new Integer(Types.BLOB));
   p.put("value", src);
   sql_params.add(p);

So what you need to change is firstly the sql statement replacing the parameters with placeholders:

                         flag = dbcon.execSQL("INSERT INTO pers_directives "
                                   + "(dir_number, dir_type, dir_change, dir_date, "
                                   + "originator, ch_date, dist_list, dir_status, "
                                   + "dir_remarks, dir_subj, filename, filetype_id, "
                                   + "entered_by, entered_dt, file_source) "
                                   + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", sql_params);

Then you need to add each of the values to sql_params (in order):

   Hashtable p1 = new Hashtable();
   p1.put("type", new Integer(Types.STRING));
   p1.put("value", dirnum);
   sql_params.add(p1);
   Hashtable p2 = new Hashtable();
   p2.put("type", new Integer(Types.STRING));
   p2.put("value", dirtype);
   sql_params.add(p2);
   etc.    

Let me know how you go
0
 
LVL 12

Author Comment

by:rcmb
ID: 13735398
Worked like a charm.

Thanks for your help.

RCMB
0
 
LVL 92

Expert Comment

by:objects
ID: 13741044
no worries :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This video teaches viewers about errors in exception handling.
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.
Suggested Courses
Course of the Month19 days, 16 hours left to enroll

872 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