001: /*
002: * DmlStatement.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.storage;
013:
014: import java.io.Closeable;
015: import java.io.File;
016: import java.io.FileInputStream;
017: import java.io.IOException;
018: import java.io.InputStream;
019: import java.io.Reader;
020: import java.io.StringReader;
021: import java.sql.PreparedStatement;
022: import java.sql.SQLException;
023: import java.util.Collections;
024: import java.util.LinkedList;
025: import java.util.List;
026:
027: import workbench.db.WbConnection;
028: import workbench.log.LogMgr;
029: import workbench.util.FileUtil;
030: import workbench.util.SqlUtil;
031: import workbench.util.NumberStringCache;
032: import workbench.util.NumberStringCache;
033:
034: /**
035: * A class to execute a SQL Statement and to create the statement
036: * from a given list of values.
037: *
038: * @author support@sql-workbench.net
039: */
040: public class DmlStatement {
041: private CharSequence sql;
042: private List<ColumnData> values;
043: private boolean usePrepared = true;
044: private String chrFunc;
045: private String concatString;
046: private String concatFunction;
047:
048: /**
049: * Create a new DmlStatement with the given SQL template string
050: * and the given values.
051: *
052: * The SQL string is expected to contain a ? for each value
053: * passed in aValueList. The SQL statement will be executed
054: * using a prepared statement.
055: */
056: public DmlStatement(CharSequence aStatement,
057: List<ColumnData> aValueList) {
058: if (aStatement == null)
059: throw new NullPointerException();
060: int count = this .countParameters(aStatement);
061: if (count > 0 && aValueList != null
062: && count != aValueList.size()) {
063: throw new IllegalArgumentException(
064: "Number of parameter tokens does not match number of parameters passed.");
065: }
066:
067: this .sql = aStatement;
068:
069: if (aValueList == null) {
070: this .values = Collections.emptyList();
071: } else {
072: this .values = aValueList;
073: }
074: }
075:
076: /**
077: * Execute the statement as a prepared statement
078: *
079: * @param aConnection the Connection to be used
080: * @return the number of rows affected
081: */
082: public int execute(WbConnection aConnection) throws SQLException {
083: List<Closeable> streamsToClose = new LinkedList<Closeable>();
084:
085: PreparedStatement stmt = null;
086: int rows = -1;
087:
088: try {
089: stmt = aConnection.getSqlConnection().prepareStatement(
090: this .sql.toString());
091: for (int i = 0; i < this .values.size(); i++) {
092: ColumnData data = this .values.get(i);
093: int type = data.getIdentifier().getDataType();
094: Object value = data.getValue();
095: if (value == null) {
096: stmt.setObject(i + 1, null);
097: } else if (SqlUtil.isClobType(type)
098: && value instanceof String) {
099: String s = (String) value;
100: Reader in = new StringReader(s);
101: stmt.setCharacterStream(i + 1, in, s.length());
102: streamsToClose.add(in);
103: } else if (value instanceof File) {
104: // Wenn storing data into a blob field, the GUI will
105: // put a File object into the DataStore
106: File f = (File) value;
107: try {
108: InputStream in = new FileInputStream(f);
109: stmt.setBinaryStream(i + 1, in, (int) f
110: .length());
111: streamsToClose.add(in);
112: } catch (IOException e) {
113: throw new SQLException("Input file ("
114: + f.getAbsolutePath()
115: + ") for BLOB not found!");
116: }
117: } else {
118: stmt.setObject(i + 1, value);
119: }
120: }
121: rows = stmt.executeUpdate();
122: } catch (SQLException e) {
123: LogMgr.logError("DmlStatement.execute()",
124: "Error executing statement " + sql.toString(), e);
125: throw e;
126: } finally {
127: FileUtil.closeStreams(streamsToClose);
128: SqlUtil.closeStatement(stmt);
129: }
130:
131: return rows;
132: }
133:
134: /**
135: * Returns true if a prepared statement is used
136: * to send the data to the database.
137: */
138: public boolean getUsePreparedStatement() {
139: return this .usePrepared;
140: }
141:
142: public void setConcatString(String concat) {
143: if (concat == null)
144: return;
145: this .concatString = concat;
146: this .concatFunction = null;
147: }
148:
149: public void setConcatFunction(String func) {
150: if (func == null)
151: return;
152: this .concatFunction = func;
153: this .concatString = null;
154: }
155:
156: public void setChrFunction(String aFunc) {
157: this .chrFunc = aFunc;
158: }
159:
160: /**
161: * Returns a "real" SQL Statement which can be executed
162: * directly. The statement contains the parameter values
163: * as literals. No placeholders are used.
164: *
165: * @param literalFormatter the Formatter for date and other literals
166: * @return a SQL statement that can be executed
167: */
168: public CharSequence getExecutableStatement(
169: SqlLiteralFormatter literalFormatter) {
170: if (this .values.size() > 0) {
171: StringBuilder result = new StringBuilder(this .sql.length()
172: + this .values.size() * 10);
173: boolean inQuotes = false;
174: int parmIndex = 0;
175: for (int i = 0; i < this .sql.length(); ++i) {
176: char c = sql.charAt(i);
177:
178: if (c == '\'')
179: inQuotes = !inQuotes;
180: if (c == '?' && !inQuotes
181: && parmIndex < this .values.size()) {
182: ColumnData data = this .values.get(parmIndex);
183: CharSequence literal = literalFormatter
184: .getDefaultLiteral(data);
185: if (this .chrFunc != null
186: && SqlUtil.isCharacterType(data
187: .getIdentifier().getDataType())) {
188: literal = this .createInsertString(literal);
189: }
190: result.append(literal);
191: parmIndex++;
192: } else {
193: result.append(c);
194: }
195: }
196: return result;
197: } else {
198: return this .sql;
199: }
200: }
201:
202: private CharSequence createInsertString(CharSequence aValue) {
203: if (aValue == null)
204: return null;
205: if (this .chrFunc == null)
206: return aValue;
207: boolean useConcatFunc = (this .concatFunction != null);
208:
209: if (!useConcatFunc && this .concatString == null)
210: this .concatString = "||";
211: StringBuilder result = new StringBuilder();
212: boolean funcAppended = false;
213: boolean quotePending = false;
214:
215: char last = 0;
216:
217: int len = aValue.length();
218: for (int i = 0; i < len; i++) {
219: char c = aValue.charAt(i);
220: if (c < 32) {
221: if (useConcatFunc) {
222: if (!funcAppended) {
223: StringBuilder temp = new StringBuilder(
224: concatFunction);
225: temp.append('(');
226: temp.append(result);
227: result = temp;
228: funcAppended = true;
229: }
230: if (quotePending && last >= 32) {
231: result.append(",\'");
232: }
233: if (last >= 32)
234: result.append('\'');
235: result.append(',');
236: result.append(this .chrFunc);
237: result.append('(');
238: result.append(NumberStringCache.getNumberString(c));
239: result.append(')');
240: quotePending = true;
241: } else {
242: if (last >= 32) {
243: result.append('\'');
244: result.append(this .concatString);
245: }
246: result.append(this .chrFunc);
247: result.append('(');
248: result.append(NumberStringCache.getNumberString(c));
249: result.append(')');
250: result.append(this .concatString);
251: quotePending = true;
252: }
253: } else {
254: if (quotePending) {
255: if (useConcatFunc)
256: result.append(',');
257: result.append('\'');
258: }
259: result.append(c);
260: quotePending = false;
261: }
262: last = c;
263: }
264: if (funcAppended) {
265: result.append(')');
266: }
267: return result;
268: }
269:
270: private int countParameters(CharSequence aSql) {
271: if (aSql == null)
272: return -1;
273: boolean inQuotes = false;
274: int count = 0;
275: for (int i = 0; i < aSql.length(); i++) {
276: char c = aSql.charAt(i);
277:
278: if (c == '\'')
279: inQuotes = !inQuotes;
280: if (c == '?' && !inQuotes) {
281: count++;
282: }
283: }
284: return count;
285: }
286:
287: public String getSql() {
288: return sql.toString();
289: }
290:
291: public String toString() {
292: return getSql();
293: }
294: }
|