源代码

001package io.jboot.db.dialect;
002
003import com.jfinal.plugin.activerecord.Record;
004import com.jfinal.plugin.activerecord.Table;
005import com.jfinal.plugin.activerecord.dialect.Dialect;
006import io.jboot.db.model.Column;
007import io.jboot.db.model.Join;
008import io.jboot.db.model.SqlBuilder;
009import io.jboot.exception.JbootException;
010
011import java.sql.PreparedStatement;
012import java.sql.SQLException;
013import java.util.List;
014import java.util.Map;
015import java.util.Set;
016
017public class JbootInformixDialect extends Dialect implements JbootDialect {
018
019
020    @Override
021    public String forTableBuilderDoBuild(String tableName) {
022        return "select * from " + tableName + " where 1 = 2";
023    }
024
025    @Override
026    public void forModelSave(Table table, Map<String, Object> attrs, StringBuilder sql, List<Object> paras) {
027        sql.append("insert into ").append(table.getName()).append('(');
028        StringBuilder temp = new StringBuilder(") values(");
029        for (Map.Entry<String, Object> e : attrs.entrySet()) {
030            String colName = e.getKey();
031            if (table.hasColumnLabel(colName)) {
032                if (paras.size() > 0) {
033                    sql.append(", ");
034                    temp.append(", ");
035                }
036                sql.append(colName);
037                temp.append('?');
038                paras.add(e.getValue());
039            }
040        }
041        sql.append(temp).append(')');
042    }
043
044    @Override
045    public String forModelDeleteById(Table table) {
046        String[] pKeys = table.getPrimaryKey();
047        StringBuilder sql = new StringBuilder(45);
048        sql.append("delete from ");
049        sql.append(table.getName());
050        sql.append(" where ");
051        for (int i = 0; i < pKeys.length; i++) {
052            if (i > 0) {
053                sql.append(" and ");
054            }
055            sql.append(pKeys[i]).append(" = ?");
056        }
057        return sql.toString();
058    }
059
060    @Override
061    public void forModelUpdate(Table table, Map<String, Object> attrs, Set<String> modifyFlag, StringBuilder sql, List<Object> paras) {
062        sql.append("update ").append(table.getName()).append(" set ");
063        String[] pKeys = table.getPrimaryKey();
064        for (Map.Entry<String, Object> e : attrs.entrySet()) {
065            String colName = e.getKey();
066            if (modifyFlag.contains(colName) && !isPrimaryKey(colName, pKeys) && table.hasColumnLabel(colName)) {
067                if (paras.size() > 0) {
068                    sql.append(", ");
069                }
070                sql.append(colName).append(" = ? ");
071                paras.add(e.getValue());
072            }
073        }
074        sql.append(" where ");
075        for (int i = 0; i < pKeys.length; i++) {
076            if (i > 0) {
077                sql.append(" and ");
078            }
079            sql.append(pKeys[i]).append(" = ?");
080            paras.add(attrs.get(pKeys[i]));
081        }
082    }
083
084    @Override
085    public String forModelFindById(Table table, String columns) {
086        StringBuilder sql = new StringBuilder("select ").append(columns).append(" from ");
087        sql.append(table.getName());
088        sql.append(" where ");
089        String[] pKeys = table.getPrimaryKey();
090        for (int i = 0; i < pKeys.length; i++) {
091            if (i > 0) {
092                sql.append(" and ");
093            }
094            sql.append(pKeys[i]).append(" = ?");
095        }
096        return sql.toString();
097    }
098
099    @Override
100    public String forDbFindById(String tableName, String[] pKeys) {
101        tableName = tableName.trim();
102        trimPrimaryKeys(pKeys);
103
104        StringBuilder sql = new StringBuilder("select * from ").append(tableName).append(" where ");
105        for (int i = 0; i < pKeys.length; i++) {
106            if (i > 0) {
107                sql.append(" and ");
108            }
109            sql.append(pKeys[i]).append(" = ?");
110        }
111        return sql.toString();
112    }
113
114    @Override
115    public String forDbDeleteById(String tableName, String[] pKeys) {
116        tableName = tableName.trim();
117        trimPrimaryKeys(pKeys);
118
119        StringBuilder sql = new StringBuilder("delete from ").append(tableName).append(" where ");
120        for (int i = 0; i < pKeys.length; i++) {
121            if (i > 0) {
122                sql.append(" and ");
123            }
124            sql.append(pKeys[i]).append(" = ?");
125        }
126        return sql.toString();
127    }
128
129    @Override
130    public void forDbSave(String tableName, String[] pKeys, Record record, StringBuilder sql, List<Object> paras) {
131        tableName = tableName.trim();
132        trimPrimaryKeys(pKeys);
133
134        sql.append("insert into ");
135        sql.append(tableName).append('(');
136        StringBuilder temp = new StringBuilder();
137        temp.append(") values(");
138
139        for (Map.Entry<String, Object> e : record.getColumns().entrySet()) {
140            if (paras.size() > 0) {
141                sql.append(", ");
142                temp.append(", ");
143            }
144            sql.append(e.getKey());
145            temp.append('?');
146            paras.add(e.getValue());
147        }
148        sql.append(temp).append(')');
149    }
150
151    @Override
152    public void forDbUpdate(String tableName, String[] pKeys, Object[] ids, Record record, StringBuilder sql, List<Object> paras) {
153        tableName = tableName.trim();
154        trimPrimaryKeys(pKeys);
155
156        sql.append("update ").append(tableName).append(" set ");
157        for (Map.Entry<String, Object> e : record.getColumns().entrySet()) {
158            String colName = e.getKey();
159            if (!isPrimaryKey(colName, pKeys)) {
160                if (paras.size() > 0) {
161                    sql.append(", ");
162                }
163                sql.append(colName).append(" = ? ");
164                paras.add(e.getValue());
165            }
166        }
167        sql.append(" where ");
168        for (int i = 0; i < pKeys.length; i++) {
169            if (i > 0) {
170                sql.append(" and ");
171            }
172            sql.append(pKeys[i]).append(" = ?");
173            paras.add(ids[i]);
174        }
175    }
176
177    /**
178     * sql.replaceFirst("(?i)select", "") 正则中带有 "(?i)" 前缀，指定在匹配时不区分大小写
179     */
180    @Override
181    public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) {
182//        int end = pageNumber * pageSize;
183//        if (end <= 0) {
184//            end = pageSize;
185//        }
186
187        int begin = (pageNumber - 1) * pageSize;
188        if (begin < 0) {
189            begin = 0;
190        }
191
192//        StringBuilder ret = new StringBuilder();
193//        ret.append(String.format("select skip %s first %s ", begin + "", pageSize + ""));
194//        ret.append(findSql.toString().replaceFirst("(?i)select", ""));
195
196        StringBuilder ret = new StringBuilder("select skip ");
197        ret.append(begin).append(" first ").append(pageSize);
198        ret.append(findSql, 6, findSql.length());
199        return ret.toString();
200    }
201
202    @Override
203    public void fillStatement(PreparedStatement pst, List<Object> paras) throws SQLException {
204        fillStatementHandleDateType(pst, paras);
205    }
206
207    @Override
208    public void fillStatement(PreparedStatement pst, Object... paras) throws SQLException {
209        fillStatementHandleDateType(pst, paras);
210    }
211
212
213    //for jbootDialect -----------------
214    @Override
215    public String forFindByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns, String orderBy, Object limit) {
216        StringBuilder sqlBuilder = SqlBuilder.forFindByColumns(alias, joins, table, loadColumns, columns, orderBy, ' ');
217        if (limit == null) {
218            return sqlBuilder.toString();
219        }
220
221        if (limit instanceof Number) {
222            StringBuilder ret = new StringBuilder("select first ");
223            ret.append(limit).append(" ");
224            ret.append(sqlBuilder, 6, sqlBuilder.length());
225            return ret.toString();
226        } else if (limit instanceof String && limit.toString().contains(",")) {
227            String[] startAndEnd = limit.toString().split(",");
228            String start = startAndEnd[0];
229            String size = startAndEnd[1];
230
231            StringBuilder ret = new StringBuilder("select skip ");
232            ret.append(start).append(" first ").append(size);
233            ret.append(sqlBuilder, 6, sqlBuilder.length());
234            return ret.toString();
235        } else {
236            throw new JbootException("sql limit is error!,limit must is Number of String like \"0,10\"");
237        }
238
239    }
240
241    @Override
242    public String forFindCountByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns) {
243        return SqlBuilder.forFindCountByColumns(alias, joins, table, loadColumns, columns, ' ');
244    }
245
246    @Override
247    public String forDeleteByColumns(String alias, List<Join> joins, String table, List<Column> columns) {
248        return SqlBuilder.forDeleteByColumns(alias, joins, table, columns, ' ');
249    }
250
251    @Override
252    public String forPaginateSelect(String loadColumns) {
253        return "select " + loadColumns;
254    }
255
256
257    @Override
258    public String forPaginateFrom(String alias, List<Join> joins, String table, List<Column> columns, String orderBy) {
259        return SqlBuilder.forPaginateFrom(alias, joins, table, columns, orderBy, ' ');
260    }
261
262    @Override
263    public String forPaginateTotalRow(String select, String sqlExceptSelect, Object ext) {
264        String distinctSql = SqlBuilder.forPaginateDistinctTotalRow(select, sqlExceptSelect, ext);
265        return distinctSql != null ? distinctSql : super.forPaginateTotalRow(select, sqlExceptSelect, ext);
266    }
267
268}