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

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

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

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

项目：JFinal
1 2

评论区

wesleyxw

2018-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);
}
}
 回复

山东小木

2018-08-09 09:32

什么情况需要用这个 现在正常情况用着没问题啊
 回复

wesleyxw

2018-08-09 09:53

@山东小木 比如表名和字段名里有关键字，我的业务里有张表名叫order，然后就出现关键字‘order’错误
 回复

hawks2001

2018-10-19 10:04

sqlserver 2000 分页不支持
 回复

wesleyxw

2018-10-22 09:39

@hawks2001 我的生产环境是sqlserver2008的，分页也支持，其他版本没有测过
 回复
我要分享

热门分享

扫码入社