Convert Existing Code to Prepared Statement -- Question for TimYates

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("");
                              }
LVL 12
rcmbAsked:
Who is Participating?
 
objectsConnect With a Mentor Commented:
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
 
Jim CakalicSenior Developer/ArchitectCommented:
Would you accept assistance from someone other than Tim? If not, I'll defer to him.
:-)

Regards,
Jim Cakalic
0
 
rcmbAuthor Commented:
sure -- i was just giving tim the first cut because he helped with the other issue.

RCMB
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
objectsCommented:
Does DBControllerImpl support PreparedStatements?
0
 
rcmbAuthor Commented:
yes
0
 
objectsCommented:
>         + 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
 
rcmbAuthor Commented:
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
 
objectsCommented:
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
 
rcmbAuthor Commented:
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
 
objectsCommented:
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
 
rcmbAuthor Commented:
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
 
objectsCommented:
Its the DBControllerImpl class that I'm referring to. It is creating the PreparedStatement and somehow assigning the parameters.
0
 
rcmbAuthor Commented:
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
 
rcmbAuthor Commented:
Worked like a charm.

Thanks for your help.

RCMB
0
 
objectsCommented:
no worries :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.