001 /*
002 *
003 * $Revision: 13085 $ $Date: 2008-02-06 18:27:24 +0100 (Mi, 06 Feb 2008) $
004 *
005 * This file is part of *** M y C o R e ***
006 * See http://www.mycore.de/ for details.
007 *
008 * This program is free software; you can use it, redistribute it
009 * and / or modify it under the terms of the GNU General Public License
010 * (GPL) as published by the Free Software Foundation; either version 2
011 * of the License or (at your option) any later version.
012 *
013 * This program is distributed in the hope that it will be useful, but
014 * WITHOUT ANY WARRANTY; without even the implied warranty of
015 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016 * GNU General Public License for more details.
017 *
018 * You should have received a copy of the GNU General Public License
019 * along with this program, in a file called gpl.txt or license.txt.
020 * If not, write to the Free Software Foundation Inc.,
021 * 59 Temple Place - Suite 330, Boston, MA 02111-1307 USA
022 */
023
024 package org.mycore.backend.sql;
025
026 import java.util.Enumeration;
027 import java.util.LinkedList;
028 import java.util.List;
029 import java.util.Properties;
030 import java.util.Vector;
031
032 import org.mycore.common.MCRConfiguration;
033 import org.mycore.common.MCRUtils;
034
035 /**
036 * @author Frank Lützenkirchen
037 * @author Thomas Scheffler (yagee)
038 *
039 * @version $Revision: 13085 $ $Date: 2008-02-06 18:27:24 +0100 (Mi, 06 Feb 2008) $
040 */
041 public class MCRSQLStatement {
042 protected final static String NULL = "NULL";
043
044 protected Properties values;
045
046 protected Properties conditions;
047
048 protected Vector columns;
049
050 protected List sqlColumns;
051
052 protected String tableName;
053
054 private static String MASK_WHAT = "'";
055
056 private static String MASK_WITH;
057
058 static {
059 MCRConfiguration config = MCRConfiguration.instance();
060
061 if (config.getString("MCR.persistence_sql_database_url").indexOf(":db2:") > 0) {
062 MASK_WITH = "''";
063 } else {
064 MASK_WITH = "\\'";
065 }
066 }
067
068 public MCRSQLStatement(String tableName) {
069 this.tableName = tableName;
070 this.values = new Properties();
071 this.conditions = new Properties();
072 this.columns = new Vector();
073 this.sqlColumns = new LinkedList();
074 }
075
076 public final MCRSQLStatement setValue(String columnName, String columnValue) {
077 if (columnValue == null) {
078 values.put(columnName, NULL);
079 } else {
080 values.put(columnName, mask(columnValue));
081 }
082
083 // new behaviour
084 sqlColumns.add(new MCRSQLColumn(columnName, columnValue, "string"));
085
086 return this;
087 }
088
089 public final MCRSQLStatement setValue(MCRSQLColumn column) {
090 if (column != null) {
091 sqlColumns.add(column);
092 }
093
094 return this;
095 }
096
097 public final MCRSQLStatement setCondition(String columnName, String columnValue) {
098 if (columnValue == null) {
099 conditions.put(columnName, NULL);
100 } else {
101 conditions.put(columnName, mask(columnValue));
102 }
103
104 return this;
105 }
106
107 public final MCRSQLStatement addColumn(String columnDefinition) {
108 columns.addElement(columnDefinition);
109
110 return this;
111 }
112
113 protected final String getSQLValue(String key) {
114 String value = values.getProperty(key);
115
116 return ((value == NULL) ? "NULL" : ("'" + value + "'"));
117 }
118
119 protected final String condition() {
120 if (conditions.isEmpty()) {
121 return "";
122 }
123
124 StringBuffer sql = new StringBuffer(" WHERE ");
125
126 Enumeration keys = conditions.keys();
127
128 while (keys.hasMoreElements()) {
129 String key = (String) (keys.nextElement());
130 String value = conditions.getProperty(key);
131
132 sql.append(key).append(" ");
133
134 if (value == NULL) {
135 sql.append("IS NULL");
136 } else {
137 if (value.indexOf("%") == -1) {
138 sql.append("= '").append(value).append("'");
139 } else {
140 sql.append("LIKE '").append(value).append("'");
141 }
142 }
143
144 if (keys.hasMoreElements()) {
145 sql.append(" AND ");
146 }
147 }
148
149 return sql.toString();
150 }
151
152 public final String toInsertStatement() {
153 StringBuffer statement = new StringBuffer("INSERT INTO ");
154 statement.append(tableName).append(" (");
155
156 StringBuffer columnList = new StringBuffer();
157 StringBuffer valueList = new StringBuffer();
158
159 Enumeration keys = values.keys();
160
161 while (keys.hasMoreElements()) {
162 String column = (String) (keys.nextElement());
163 String value = getSQLValue(column);
164
165 columnList.append(" ").append(column);
166 valueList.append(" ").append(value);
167
168 if (keys.hasMoreElements()) {
169 columnList.append(",");
170 valueList.append(",");
171 }
172 }
173
174 statement.append(columnList.toString()).append(" ) VALUES (");
175 statement.append(valueList.toString()).append(" )");
176
177 return statement.toString();
178
179 // new behaviour - needs more tests
180 // return toTypedInsertStatement();
181 }
182
183 public final String toTypedInsertStatement() {
184 StringBuffer statement = new StringBuffer("INSERT INTO ");
185 statement.append(tableName).append(" (");
186
187 StringBuffer columnList = new StringBuffer();
188 StringBuffer valueList = new StringBuffer();
189
190 for (int i = 0; i < sqlColumns.size(); i++) {
191 MCRSQLColumn col = (MCRSQLColumn) sqlColumns.get(i);
192 String column = col.getName();
193 String value = col.getValue();
194
195 if ((value != null) && (value != "null")) {
196 if (col.getType().toLowerCase().equals("string")) {
197 value = "'" + value + "'";
198 } else if (col.getType().toLowerCase().equals("date") || col.getType().toLowerCase().equals("time") || col.getType().toLowerCase().equals("timestamp")) {
199 // date
200 value = "'" + value + "'";
201 } else if (col.getType().toLowerCase().equals("integer")) {
202 // integer
203 try {
204 value = "" + Integer.parseInt(value);
205 } catch (Exception e) {
206 value = "0";
207 }
208 } else if (col.getType().toLowerCase().equals("decimal")) {
209 // decimal
210 try {
211 value = "" + Double.parseDouble(value.replaceAll(",", "."));
212 } catch (Exception e) {
213 value = "0";
214 }
215 } else if (col.getType().toLowerCase().equals("boolean")) {
216 // boolean
217 if (value.toLowerCase() == "true") {
218 value = "1";
219 } else if (value.toLowerCase() == "false") {
220 value = "0";
221 }
222 }
223
224 columnList.append(" ").append("`" + column + "`");
225 valueList.append(" ").append(value);
226
227 if (i < (sqlColumns.size() - 1)) {
228 columnList.append(",");
229 valueList.append(",");
230 }
231 }
232 }
233
234 statement.append(columnList.toString()).append(" ) VALUES (");
235 statement.append(valueList.toString()).append(" )");
236
237 return statement.toString();
238 }
239
240 public final String toUpdateStatement() {
241 StringBuffer statement = new StringBuffer("UPDATE ");
242 statement.append(tableName).append(" SET");
243
244 Enumeration keys = values.keys();
245
246 while (keys.hasMoreElements()) {
247 String key = (String) (keys.nextElement());
248 String value = getSQLValue(key);
249
250 statement.append(" ").append(key).append(" =");
251 statement.append(" ").append(value);
252
253 if (keys.hasMoreElements()) {
254 statement.append(", ");
255 }
256 }
257
258 statement.append(condition());
259
260 return statement.toString();
261 }
262
263 public final String toCreateTableStatement() {
264 StringBuffer statement = new StringBuffer("CREATE TABLE ");
265 statement.append(tableName).append(" (");
266
267 for (int i = 0; i < columns.size(); i++) {
268 statement.append(" ").append(columns.elementAt(i));
269
270 if (i < (columns.size() - 1)) {
271 statement.append(",");
272 }
273 }
274
275 statement.append(" )");
276
277 return statement.toString();
278 }
279
280 public final String toIndexStatement() {
281 StringBuffer statement = new StringBuffer("CREATE INDEX ");
282 statement.append(tableName).append("_INDEX ON ").append(tableName).append(" (");
283
284 for (int i = 0; i < columns.size(); i++) {
285 statement.append(" ").append(columns.elementAt(i));
286
287 if (i < (columns.size() - 1)) {
288 statement.append(",");
289 }
290 }
291
292 statement.append(" )");
293
294 return statement.toString();
295 }
296
297 public final String toRowSelector() {
298 return new StringBuffer(tableName).append(condition()).toString();
299 }
300
301 public final String toSelectStatement() {
302 return "SELECT * FROM " + toRowSelector();
303 }
304
305 public final String toSelectStatement(String columns) {
306 return new StringBuffer("SELECT ").append(columns).append(" FROM ").append(toRowSelector()).toString();
307 }
308
309 public final String toDeleteStatement() {
310 return "DELETE FROM " + toRowSelector();
311 }
312
313 public final String toCountStatement(String column) {
314 return "SELECT COUNT( DISTINCT " + column + " ) AS NUMBER FROM " + toRowSelector();
315 }
316
317 /**
318 * masks the character ' in an sql statement
319 *
320 * @param value
321 * to be masked
322 * @return value with masked '
323 */
324 private final String mask(String value) {
325 if (value.indexOf(MASK_WHAT) >= 0){
326 return MCRUtils.replaceString(value, MASK_WHAT, MASK_WITH);
327 }
328 return value;
329 }
330 }