1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package org.jumpmind.symmetric.load;
23
24 import java.sql.Types;
25 import java.util.ArrayList;
26 import java.util.HashSet;
27 import java.util.Set;
28
29 import org.apache.commons.collections.CollectionUtils;
30 import org.apache.commons.lang.ArrayUtils;
31 import org.apache.commons.lang.NotImplementedException;
32 import org.apache.ddlutils.model.Column;
33
34 public class StatementBuilder {
35 public enum DmlType {
36 INSERT, UPDATE, DELETE, UPDATE_NO_KEYS
37 };
38
39 protected DmlType dmlType;
40
41 protected String sql;
42
43 protected int[] types;
44
45 protected String quote;
46
47 public StatementBuilder(DmlType type, String tableName, Column[] keys, Column[] columns,
48 boolean isBlobOverrideToBinary, boolean isDateOverrideToTimestamp,
49 String identifierQuoteString) {
50 quote = identifierQuoteString == null ? "" : identifierQuoteString;
51 if (type == DmlType.INSERT) {
52 sql = buildInsertSql(tableName, columns);
53 types = buildTypes(columns, isBlobOverrideToBinary, isDateOverrideToTimestamp);
54 } else if (type == DmlType.UPDATE) {
55 sql = buildUpdateSql(tableName, keys, columns);
56 types = buildTypes(keys, columns, isBlobOverrideToBinary, isDateOverrideToTimestamp);
57 } else if (type == DmlType.UPDATE_NO_KEYS) {
58 columns = removeKeysFromColumns(keys, columns);
59 sql = buildUpdateSql(tableName, keys, columns);
60 types = buildTypes(keys, columns, isBlobOverrideToBinary, isDateOverrideToTimestamp);
61 } else if (type == DmlType.DELETE) {
62 sql = buildDeleteSql(tableName, keys);
63 types = buildTypes(keys, isBlobOverrideToBinary, isDateOverrideToTimestamp);
64 } else {
65 throw new NotImplementedException("Unimplemented SQL type: " + type);
66 }
67 dmlType = type;
68 }
69
70 protected Column[] removeKeysFromColumns(Column[] keys, Column[] columns) {
71 Column[] columnsWithoutKeys = new Column[columns.length - keys.length];
72 Set<Column> keySet = new HashSet<Column>();
73 CollectionUtils.addAll(keySet, keys);
74 int n = 0;
75 for (int i = 0; i < columns.length; i++) {
76 Column column = columns[i];
77 if (!keySet.contains(column)) {
78 columnsWithoutKeys[n++] = column;
79 }
80 }
81 return columnsWithoutKeys;
82 }
83
84 protected int[] buildTypes(Column[] keys, Column[] columns, boolean isBlobOverrideToBinary,
85 boolean isDateOverrideToTimestamp) {
86 int[] columnTypes = buildTypes(columns, isBlobOverrideToBinary, isDateOverrideToTimestamp);
87 int[] keyTypes = buildTypes(keys, isBlobOverrideToBinary, isDateOverrideToTimestamp);
88 return ArrayUtils.addAll(columnTypes, keyTypes);
89 }
90
91 protected int[] buildTypes(Column[] columns, boolean isBlobOverrideToBinary,
92 boolean isDateOverrideToTimestamp) {
93 ArrayList<Integer> list = new ArrayList<Integer>(columns.length);
94 for (int i = 0; i < columns.length; i++) {
95 if (columns[i] != null) {
96 list.add(columns[i].getTypeCode());
97 }
98 }
99 int[] types = new int[list.size()];
100 int index = 0;
101 for (Integer type : list) {
102 if (type == Types.BLOB && isBlobOverrideToBinary) {
103 type = Types.BINARY;
104 } else if (type == Types.DATE && isDateOverrideToTimestamp) {
105 type = Types.TIMESTAMP;
106 } else if (type == Types.FLOAT || type == Types.DOUBLE) {
107 type = Types.DECIMAL;
108 }
109 types[index++] = type;
110 }
111 return types;
112 }
113
114 public String buildInsertSql(String tableName, String[] columnNames) {
115 StringBuilder sql = new StringBuilder("insert into " + tableName + "(");
116 appendColumns(sql, columnNames);
117 sql.append(") values (");
118 appendColumnQuestions(sql, columnNames.length);
119 sql.append(")");
120 return sql.toString();
121 }
122
123 public String buildInsertSql(String tableName, Column[] columns) {
124 StringBuilder sql = new StringBuilder("insert into " + tableName + "(");
125 int columnCount = appendColumns(sql, columns);
126 sql.append(") values (");
127 appendColumnQuestions(sql, columnCount);
128 sql.append(")");
129 return sql.toString();
130 }
131
132 public String buildUpdateSql(String tableName, String[] keyNames, String[] columnNames) {
133 StringBuilder sql = new StringBuilder("update ").append(tableName).append(" set ");
134 appendColumnEquals(sql, columnNames, ", ");
135 sql.append(" where ");
136 appendColumnEquals(sql, keyNames, " and ");
137 return sql.toString();
138 }
139
140 public String buildUpdateSql(String tableName, Column[] keyColumns, Column[] columns) {
141 StringBuilder sql = new StringBuilder("update ").append(tableName).append(" set ");
142 appendColumnEquals(sql, columns, ", ");
143 sql.append(" where ");
144 appendColumnEquals(sql, keyColumns, " and ");
145 return sql.toString();
146 }
147
148 public String buildDeleteSql(String tableName, String[] keyNames) {
149 StringBuilder sql = new StringBuilder("delete from ").append(tableName).append(" where ");
150 appendColumnEquals(sql, keyNames, " and ");
151 return sql.toString();
152 }
153
154 public String buildDeleteSql(String tableName, Column[] keyColumns) {
155 StringBuilder sql = new StringBuilder("delete from ").append(tableName).append(" where ");
156 appendColumnEquals(sql, keyColumns, " and ");
157 return sql.toString();
158 }
159
160 public void appendColumnEquals(StringBuilder sql, String[] names, String separator) {
161 for (int i = 0; i < names.length; i++) {
162 sql.append(quote).append(names[i]).append(quote).append(" = ?").append(i + 1 < names.length ? separator : "");
163 }
164 }
165
166 public void appendColumnEquals(StringBuilder sql, Column[] columns, String separator) {
167 int existingCount = 0;
168 for (int i = 0; i < columns.length; i++) {
169 if (columns[i] != null) {
170 if (existingCount++ > 0) {
171 sql.append(separator);
172 }
173 sql.append(quote).append(columns[i].getName()).append(quote).append(" = ?");
174 }
175 }
176 }
177
178 public void appendColumns(StringBuilder sql, String[] names) {
179 for (int i = 0; i < names.length; i++) {
180 sql.append(quote).append(names[i]).append(quote).append(i + 1 < names.length ? "," : "");
181 }
182 }
183
184 public int appendColumns(StringBuilder sql, Column[] columns) {
185 int existingCount = 0;
186 for (int i = 0; i < columns.length; i++) {
187 if (columns[i] != null) {
188 if (existingCount++ > 0) {
189 sql.append(",");
190 }
191 sql.append(quote).append(columns[i].getName()).append(quote);
192 }
193 }
194 return existingCount;
195 }
196
197 public void appendColumnQuestions(StringBuilder sql, int number) {
198 for (int i = 0; i < number; i++) {
199 sql.append("?").append(i + 1 < number ? "," : "");
200 }
201 }
202
203 public String getSql() {
204 return sql;
205 }
206
207 public DmlType getDmlType() {
208 return dmlType;
209 }
210
211 public int[] getTypes() {
212 return types;
213 }
214 }