001/**
002 * Copyright (c) 2015-2022, Michael Yang 杨福海 (fuhai999@gmail.com).
003 * <p>
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * <p>
008 * http://www.apache.org/licenses/LICENSE-2.0
009 * <p>
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package io.jboot.db.dialect;
017
018import com.jfinal.plugin.activerecord.CPI;
019import com.jfinal.plugin.activerecord.Record;
020import com.jfinal.plugin.activerecord.Table;
021import com.jfinal.plugin.activerecord.dialect.OracleDialect;
022import io.jboot.db.model.Column;
023import io.jboot.db.model.Join;
024import io.jboot.db.model.SqlBuilder;
025
026import java.util.List;
027import java.util.Map;
028import java.util.Set;
029
030/**
031 * 达梦数据库的数据方言
032 */
033public class JbootDmDialect extends OracleDialect implements JbootDialect {
034
035    private static final char separator = '"';
036
037    public String wrap(String wrap) {
038        return "\"" + wrap.toUpperCase() + "\"";
039    }
040
041    @Override
042    public String forTableBuilderDoBuild(String tableName) {
043        return toUpperCase("select * from " + wrap(tableName) + " where rownum < 1");
044    }
045
046
047    @Override
048    // insert into table (id,name) values(seq.nextval, ？)
049    public void forModelSave(Table table, Map<String, Object> attrs, StringBuilder sql, List<Object> paras) {
050        sql.append("insert into ").append(wrap(table.getName())).append('(');
051        StringBuilder temp = new StringBuilder(") values(");
052        String[] pKeys = table.getPrimaryKey();
053        int count = 0;
054        for (Map.Entry<String, Object> e : attrs.entrySet()) {
055            String colName = e.getKey();
056            if (table.hasColumnLabel(colName)) {
057                Object value = e.getValue();
058                if (isPrimaryKey(colName, pKeys) && value == null) {
059                    continue;
060                }
061
062                if (count++ > 0) {
063                    sql.append(", ");
064                    temp.append(", ");
065                }
066
067
068                if (isPrimaryKey(colName, pKeys) && value instanceof String && ((String) value).endsWith(".nextval")) {
069                    sql.append(wrap(colName));
070                    temp.append(value);
071                } else {
072                    sql.append(wrap(colName));
073                    temp.append('?');
074                    paras.add(value);
075                }
076            }
077        }
078        sql.append(temp).append(')');
079    }
080
081    @Override
082    public String forModelDeleteById(Table table) {
083        String[] pKeys = table.getPrimaryKey();
084        StringBuilder sql = new StringBuilder(45);
085        sql.append("delete from ");
086        sql.append(wrap(table.getName()));
087        sql.append(" where ");
088        for (int i = 0; i < pKeys.length; i++) {
089            if (i > 0) {
090                sql.append(" and ");
091            }
092            sql.append(wrap(pKeys[i])).append(" = ?");
093        }
094        return sql.toString();
095    }
096
097    @Override
098    public void forModelUpdate(Table table, Map<String, Object> attrs, Set<String> modifyFlag, StringBuilder sql, List<Object> paras) {
099        sql.append("update ").append(wrap(table.getName())).append(" set ");
100        String[] pKeys = table.getPrimaryKey();
101        for (Map.Entry<String, Object> e : attrs.entrySet()) {
102            String colName = e.getKey();
103            if (modifyFlag.contains(colName) && !isPrimaryKey(colName, pKeys) && table.hasColumnLabel(colName)) {
104                if (paras.size() > 0) {
105                    sql.append(", ");
106                }
107                sql.append(wrap(colName)).append(" = ? ");
108                paras.add(e.getValue());
109            }
110        }
111        sql.append(" where ");
112        for (int i = 0; i < pKeys.length; i++) {
113            if (i > 0) {
114                sql.append(" and ");
115            }
116            sql.append(wrap(pKeys[i])).append(" = ?");
117            paras.add(attrs.get(pKeys[i]));
118        }
119    }
120
121
122    @Override
123    public String forModelFindById(Table table, String columns) {
124        StringBuilder sql = new StringBuilder("select ").append(columns).append(" from ");
125        sql.append(wrap(table.getName()));
126        sql.append(" where ");
127        String[] pKeys = table.getPrimaryKey();
128        for (int i = 0; i < pKeys.length; i++) {
129            if (i > 0) {
130                sql.append(" and ");
131            }
132            sql.append(wrap(pKeys[i])).append(" = ?");
133        }
134        return sql.toString();
135    }
136
137
138    @Override
139    public String forDbFindById(String tableName, String[] pKeys) {
140        tableName = tableName.trim();
141        trimPrimaryKeys(pKeys);
142
143        StringBuilder sql = new StringBuilder("select * from ").append(wrap(tableName)).append(" where ");
144        for (int i = 0; i < pKeys.length; i++) {
145            if (i > 0) {
146                sql.append(" and ");
147            }
148            sql.append(wrap(pKeys[i])).append(" = ?");
149        }
150        return sql.toString();
151    }
152
153    @Override
154    public String forDbDeleteById(String tableName, String[] pKeys) {
155        tableName = tableName.trim();
156        trimPrimaryKeys(pKeys);
157
158        StringBuilder sql = new StringBuilder("delete from ").append(wrap(tableName)).append(" where ");
159        for (int i = 0; i < pKeys.length; i++) {
160            if (i > 0) {
161                sql.append(" and ");
162            }
163            sql.append(wrap(pKeys[i])).append(" = ?");
164        }
165        return sql.toString();
166    }
167
168
169    @Override
170    public void forDbSave(String tableName, String[] pKeys, Record record, StringBuilder sql, List<Object> paras) {
171        tableName = tableName.trim();
172        trimPrimaryKeys(pKeys);
173
174        sql.append("insert into ");
175        sql.append(wrap(tableName)).append('(');
176        StringBuilder temp = new StringBuilder();
177        temp.append(") values(");
178
179        int count = 0;
180        for (Map.Entry<String, Object> e : record.getColumns().entrySet()) {
181
182            String colName = e.getKey();
183            Object value = e.getValue();
184
185            if (isPrimaryKey(colName, pKeys) && value == null) {
186                continue;
187            }
188
189            if (count++ > 0) {
190                sql.append(", ");
191                temp.append(", ");
192            }
193            sql.append(wrap(colName));
194
195            if (value instanceof String && isPrimaryKey(colName, pKeys) && ((String) value).endsWith(".nextval")) {
196                temp.append(value);
197            } else {
198                temp.append('?');
199                paras.add(value);
200            }
201        }
202        sql.append(temp).append(')');
203    }
204
205
206    @Override
207    public void forDbUpdate(String tableName, String[] pKeys, Object[] ids, Record record, StringBuilder sql, List<Object> paras) {
208        tableName = tableName.trim();
209        trimPrimaryKeys(pKeys);
210
211        // Record 新增支持 modifyFlag
212        Set<String> modifyFlag = CPI.getModifyFlag(record);
213
214        sql.append("update ").append(wrap(tableName)).append(" set ");
215        for (Map.Entry<String, Object> e : record.getColumns().entrySet()) {
216            String colName = e.getKey();
217            if (modifyFlag.contains(colName) && !isPrimaryKey(colName, pKeys)) {
218                if (paras.size() > 0) {
219                    sql.append(", ");
220                }
221                sql.append(wrap(colName)).append(" = ? ");
222                paras.add(e.getValue());
223            }
224        }
225        sql.append(" where ");
226        for (int i = 0; i < pKeys.length; i++) {
227            if (i > 0) {
228                sql.append(" and ");
229            }
230            sql.append(wrap(pKeys[i])).append(" = ?");
231            paras.add(ids[i]);
232        }
233    }
234
235    @Override
236    public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) {
237        int start = (pageNumber - 1) * pageSize;
238        int end = pageNumber * pageSize;
239        StringBuilder ret = new StringBuilder();
240        ret.append("select * from ( select row_.*, rownum rownum_ from (  ");
241        ret.append(findSql);
242        ret.append(" ) row_ where rownum <= ").append(end).append(") table_alias");
243        ret.append(" where table_alias.rownum_ > ").append(start);
244        return ret.toString();
245    }
246
247
248    /////////////////jboot////////////
249
250    @Override
251    public String forFindByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns, String orderBy, Object limit) {
252        StringBuilder sqlBuilder = SqlBuilder.forFindByColumns(alias, joins, table, loadColumns, columns, orderBy, separator);
253
254        if (limit != null) {
255            sqlBuilder.append(" LIMIT " + limit);
256        }
257        return toUpperCase(sqlBuilder.toString());
258    }
259
260    @Override
261    public String forFindCountByColumns(String alias, List<Join> joins, String table, String loadColumns, List<Column> columns) {
262        return toUpperCase(SqlBuilder.forFindCountByColumns(alias, joins, table, loadColumns, columns, separator));
263    }
264
265    @Override
266    public String forDeleteByColumns(String alias, List<Join> joins, String table, List<Column> columns) {
267        return toUpperCase(SqlBuilder.forDeleteByColumns(alias, joins, table, columns, separator));
268    }
269
270    @Override
271    public String forPaginateSelect(String loadColumns) {
272        return toUpperCase("SELECT " + loadColumns);
273    }
274
275
276    @Override
277    public String forPaginateFrom(String alias, List<Join> joins, String table, List<Column> columns, String orderBy) {
278        return toUpperCase(SqlBuilder.forPaginateFrom(alias, joins, table, columns, orderBy, separator));
279    }
280
281    @Override
282    public String forPaginateTotalRow(String select, String sqlExceptSelect, Object ext) {
283        String distinctSql = SqlBuilder.forPaginateDistinctTotalRow(select, sqlExceptSelect, ext);
284        return toUpperCase(distinctSql != null ? distinctSql : super.forPaginateTotalRow(select, sqlExceptSelect, ext));
285    }
286
287    public  String toUpperCase(String sql) {
288        return sql.toUpperCase();
289    }
290}