JFinal

登录 注册

Sqlserver关键字问题-修改方言简单解决方案(修改SqlServerDialect)

正文发代码显示不出来,看评论

另外在用到SqlServerDialect方言的地方都改成NewSqlServerDialect

目前项目用用没有问题,但也不保证完全没问题!!!慎用

评论

  • 08-04 16:04
    package com.hexie.core.utils;

    import com.jfinal.plugin.activerecord.Record;
    import com.jfinal.plugin.activerecord.Table;
    import com.jfinal.plugin.activerecord.dialect.Dialect;

    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;

    /**
    * 主要解决sqlserver部分关键字问题导致的使用Model数据库操作异常
    * 通过对表名和条件字段名加上[]处理
    * 以下情况仍需手写SQL处理:
    * 1.分页sql
    * 2.SELECT order ... 请手写SQL改为 SELECT [order] ...
    *
    * @author WesleyOne
    * @see com.jfinal.plugin.activerecord.dialect.SqlServerDialect 为OSC 网友战五渣贡献代码:http://www.oschina.net/question/2333909_234198
    * @create 2018/8/4
    */

    public class NewSqlServerDialect extends Dialect {

    @Override
    public String forTableBuilderDoBuild(String tableName) {
    return "select * from [" + tableName + "] where 1 = 2";
    }

    @Override
    public void forModelSave(Table table, Map attrs, StringBuilder sql, List paras) {
    sql.append("insert into ").append('[').append(table.getName()).append(']').append("(");
    StringBuilder temp = new StringBuilder(") values(");
    for (Map.Entry e: attrs.entrySet()) {
    String colName = e.getKey();
    if (table.hasColumnLabel(colName)) {
    if (paras.size() > 0) {
    sql.append(", ");
    temp.append(", ");
    }
    sql.append('[').append(colName).append(']');
    temp.append('?');
    paras.add(e.getValue());
    }
    }
    sql.append(temp.toString()).append(')');
    }

    @Override
    public String forModelDeleteById(Table table) {
    String[] pKeys = table.getPrimaryKey();
    StringBuilder sql = new StringBuilder(45);
    sql.append("delete from ");
    sql.append('[').append(table.getName()).append(']');
    sql.append(" where ");
    for (int i=0; i if (i > 0) {
    sql.append(" and ");
    }
    sql.append('[').append(pKeys[i]).append(']').append(" = ?");
    }
    return sql.toString();
    }

    @Override
    public void forModelUpdate(Table table, Map attrs, Set modifyFlag, StringBuilder sql, List paras) {
    sql.append("update ").append('[').append(table.getName()).append(']').append(" set ");
    String[] pKeys = table.getPrimaryKey();
    for (Map.Entry e : attrs.entrySet()) {
    String colName = e.getKey();
    if (modifyFlag.contains(colName) && !isPrimaryKey(colName, pKeys) && table.hasColumnLabel(colName)) {
    if (paras.size() > 0) {
    sql.append(", ");
    }
    sql.append('[').append(colName).append(']').append(" = ? ");
    paras.add(e.getValue());
    }
    }
    sql.append(" where ");
    for (int i=0; i if (i > 0) {
    sql.append(" and ");
    }
    sql.append('[').append(pKeys[i]).append(']').append(" = ?");
    paras.add(attrs.get(pKeys[i]));
    }
    }

    @Override
    public String forModelFindById(Table table, String columns) {
    StringBuilder sql = new StringBuilder("select ").append(columns).append(" from ");
    sql.append('[').append(table.getName()).append(']');
    sql.append(" where ");
    String[] pKeys = table.getPrimaryKey();
    for (int i=0; i if (i > 0) {
    sql.append(" and ");
    }
    sql.append('[').append(pKeys[i]).append(']').append(" = ?");
    }
    return sql.toString();
    }

    @Override
    public String forDbFindById(String tableName, String[] pKeys) {
    tableName = tableName.trim();
    trimPrimaryKeys(pKeys);

    StringBuilder sql = new StringBuilder("select * from ").append('[').append(tableName).append(']').append(" where ");
    for (int i=0; i if (i > 0) {
    sql.append(" and ");
    }
    sql.append('[').append(pKeys[i]).append(']').append(" = ?");
    }
    return sql.toString();
    }

    @Override
    public String forDbDeleteById(String tableName, String[] pKeys) {
    tableName = tableName.trim();
    trimPrimaryKeys(pKeys);

    StringBuilder sql = new StringBuilder("delete from ").append('[').append(tableName).append(']').append(" where ");
    for (int i=0; i if (i > 0) {
    sql.append(" and ");
    }
    sql.append('[').append(pKeys[i]).append(']').append(" = ?");
    }
    return sql.toString();
    }

    @Override
    public void forDbSave(String tableName, String[] pKeys, Record record, StringBuilder sql, List paras) {
    tableName = tableName.trim();
    trimPrimaryKeys(pKeys);

    sql.append("insert into ");
    sql.append('[').append(tableName).append(']').append('(');
    StringBuilder temp = new StringBuilder();
    temp.append(") values(");

    for (Map.Entry e: record.getColumns().entrySet()) {
    if (paras.size() > 0) {
    sql.append(", ");
    temp.append(", ");
    }
    sql.append('[').append(e.getKey()).append(']');
    temp.append('?');
    paras.add(e.getValue());
    }
    sql.append(temp.toString()).append(')');
    }

    @Override
    public void forDbUpdate(String tableName, String[] pKeys, Object[] ids, Record record, StringBuilder sql, List paras) {
    tableName = tableName.trim();
    trimPrimaryKeys(pKeys);

    sql.append("update ").append('[').append(tableName).append(']').append(" set ");
    for (Map.Entry e: record.getColumns().entrySet()) {
    String colName = e.getKey();
    if (!isPrimaryKey(colName, pKeys)) {
    if (paras.size() > 0) {
    sql.append(", ");
    }
    sql.append('[').append(colName).append(']').append(" = ? ");
    paras.add(e.getValue());
    }
    }
    sql.append(" where ");
    for (int i=0; i if (i > 0) {
    sql.append(" and ");
    }
    sql.append('[').append(pKeys[i]).append(']').append(" = ?");
    paras.add(ids[i]);
    }
    }

    /**
    * sql.replaceFirst("(?i)select", "") 正则中带有 "(?i)" 前缀,指定在匹配时不区分大小写
    */
    @Override
    public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) {
    int end = pageNumber * pageSize;
    if (end <= 0) {
    end = pageSize;
    }
    int begin = (pageNumber - 1) * pageSize;
    if (begin < 0) {
    begin = 0;
    }
    StringBuilder ret = new StringBuilder();
    ret.append("SELECT * FROM ( SELECT row_number() over (order by tempcolumn) temprownumber, * FROM ");
    ret.append(" ( SELECT TOP ").append(end).append(" tempcolumn=0,");
    ret.append(findSql.toString().replaceFirst("(?i)select", ""));
    ret.append(")vip)mvp where temprownumber>").append(begin);
    return ret.toString();
    }

    @Override
    public void fillStatement(PreparedStatement pst, List paras) throws SQLException {
    fillStatementHandleDateType(pst, paras);
    }

    @Override
    public void fillStatement(PreparedStatement pst, Object... paras) throws SQLException {
    fillStatementHandleDateType(pst, paras);
    }
    }
  • 08-09 09:32
    什么情况需要用这个 现在正常情况用着没问题啊
  • 08-09 09:53
    @山东小木 比如表名和字段名里有关键字,我的业务里有张表名叫order,然后就出现关键字‘order’错误
  • 发送