AbstractSqlCommand


1   /*
2    * AbstractSqlCommand.java
3    *
4    * Created on February 26, 2005, 3:04 PM
5    */
6   
7   package org.jdesktop.dataset.provider.sql;
8   import java.sql.PreparedStatement  ;
9   import java.sql.ResultSet  ;
10  import java.sql.SQLException  ;
11  import java.util.ArrayList  ;
12  import java.util.HashMap  ;
13  import java.util.HashSet  ;
14  import java.util.List  ;
15  import java.util.Map  ;
16  import java.util.Set  ;
17  import org.jdesktop.dataset.DataCommand;
18  import org.jdesktop.dataset.DataRow;
19  
20  /**
21   *
22   * @author rb156199
23   */
24  public abstract class AbstractSqlCommand extends DataCommand {
25      
26      /**
27       * @return a Statement, ready to execute, for the Select SQL statement.
28       */
29      protected abstract PreparedStatement   getSelectStatement(JDBCDataConnection conn) throws Exception  ;
30      protected abstract PreparedStatement   getInsertStatement(JDBCDataConnection conn, DataRow row) throws Exception  ;
31      protected abstract PreparedStatement   getUpdateStatement(JDBCDataConnection conn, DataRow row) throws Exception  ;
32      protected abstract PreparedStatement   getDeleteStatement(JDBCDataConnection conn, DataRow row) throws Exception  ;
33      
34      protected String   constructSql(String   sql, Map  <String  ,List  <Integer  >> indexes) {
35          //replace all of the named parameters in the sql with their
36          //corrosponding values. This is done by first converting the sql
37          //to proper JDBC sql by inserting '?' for each and every param.
38          //As this is done, a record is kept of which parameters go with
39          //which indexes. Then, the parameter values are applied.
40          StringBuilder   buffer = new StringBuilder  (sql);
41          //variable containing the index of the current parameter. So,
42          //for the first named param this is 0, then 1 for the next, and so on
43          int paramIndex = 0;
44  
45          //iterate through the buffer looking for a colon outside of any
46          //single or double quotes. This represents the beginning of a named
47          //parameter
48          boolean inSingleQuote = false;
49          boolean inDoubleQuote = false;
50          for (int i=0; i<buffer.length(); i++) {
51              char c = buffer.charAt(i);
52              if (c == '\'') {
53                  inSingleQuote = !inSingleQuote;
54              } else if (c == '\"') {
55                  inDoubleQuote = !inDoubleQuote;
56              } else if (c == ':' && !inSingleQuote && !inDoubleQuote) {
57                  //found the beginning of a named param. find the whole
58                  //name by looking from here to the first whitespace
59                  //character
60  
61                  int firstCharIndex = i;
62                  i++;
63                  boolean found = false;
64                  while (!found) {
65                      if (i >= buffer.length()) {
66                          //I've gotten to the end of the string, so I must
67                          //now have the entire variable name
68                          found = true;
69                      } else {
70                          char next = buffer.charAt(i);
71                          if (next == ' ' || next == '\n' || next == '\t' || next == '\r' || next == ',' || next == ')') {
72                              found = true;
73                          }
74                      }
75                      i++;
76                  }
77  
78                  //ok, i-1 is the index following the last character in this sequence.
79                  String   paramName = buffer.substring(firstCharIndex+1, i-1);
80  
81                  //now that I have the name, replace it with a ? and add it
82                  //to the map of paramName->index values.
83                  buffer.replace(firstCharIndex, i-1, "?");
84                  if (!indexes.containsKey(paramName)) {
85                      indexes.put(paramName, new ArrayList  <Integer  >());
86                  }
87                  List  <Integer  > list = indexes.get(paramName);
88                  list.add(paramIndex++);
89  
90                  //reposition "i" to a valid value since a lot of chars were
91                  //just removed
92                  i = firstCharIndex + 1;
93              }
94          }
95          return buffer.toString();
96      }
97      
98  //    protected PreparedStatement prepareStatement(String sql, Query q) throws SQLException {
99  //        //map containing the indexes for each named param
100 //        Map<String,List<Integer>> indexes = new HashMap<String,List<Integer>>();
101 //        sql = constructSql(sql, indexes);
102 //        PreparedStatement ps = dsc.getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
103 //
104 //        //now, apply the given set of parameters
105 //        for (String paramName : q.getParameterNames()) {
106 //            List<Integer> list = indexes.get(paramName);
107 //            if (list != null) {
108 //                for (int index : list) {
109 //                    ps.setObject(index + 1, q.getParameter(paramName));
110 //                }
111 //            }
112 //        }
113 //        return ps;
114 //    }
115     
116     public String  [] getParameterNames(String  [] statements) {
117         //searches the statements for param names, and returns the unique set of
118         //param names
119         StringBuilder   buffer = new StringBuilder  ();
120         for (String   s : statements) {
121             buffer.append(s);
122             buffer.append("\n");
123         }
124         Set  <String  > names = new HashSet  <String  >();
125 
126         //iterate through the buffer looking for a colon outside of any
127         //single or double quotes. This represents the beginning of a named
128         //parameter
129         boolean inSingleQuote = false;
130         boolean inDoubleQuote = false;
131         for (int i=0; i<buffer.length(); i++) {
132             char c = buffer.charAt(i);
133             if (c == '\'') {
134                 inSingleQuote = !inSingleQuote;
135             } else if (c == '\"') {
136                 inDoubleQuote = !inDoubleQuote;
137             } else if (c == ':' && !inSingleQuote && !inDoubleQuote) {
138                 //found the beginning of a named param. find the whole
139                 //name by looking from here to the first whitespace
140                 //character
141 
142                 int firstCharIndex = i;
143                 i++;
144                 boolean found = false;
145                 while (!found) {
146                     if (i >= buffer.length()) {
147                         //I've gotten to the end of the string, so I must
148                         //now have the entire variable name
149                         found = true;
150                     } else {
151                         char next = buffer.charAt(i);
152                         if (next == ' ' || next == '\n' || next == '\t' || next == '\r' || next == ',' || next == ')') {
153                             found = true;
154                         }
155                     }
156                     i++;
157                 }
158 
159                 //ok, i-1 is the index following the last character in this sequence.
160                 String   paramName = buffer.substring(firstCharIndex+1, i-1);
161                 names.add(paramName);
162             }
163         }
164         String  [] results = new String  [names.size()];
165         return names.toArray(results);
166     }
167 }
168
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags