org.nutz.dao.impl.jdbc.sqlserver2005.Sqlserver2005JdbcExpert Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of nutz Show documentation
Show all versions of nutz Show documentation
Nutz, which is a collections of lightweight frameworks, each of them can be used independently
package org.nutz.dao.impl.jdbc.sqlserver2005;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.nutz.conf.NutConf;
import org.nutz.dao.DB;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Entity;
import org.nutz.dao.entity.MappingField;
import org.nutz.dao.entity.PkType;
import org.nutz.dao.impl.entity.macro.SqlFieldMacro;
import org.nutz.dao.impl.jdbc.AbstractJdbcExpert;
import org.nutz.dao.jdbc.JdbcExpertConfigFile;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.PItem;
import org.nutz.dao.sql.Pojo;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.Pojos;
/**
*
* @author wendal
*/
public class Sqlserver2005JdbcExpert extends AbstractJdbcExpert {
// private static String COMMENT_TABLE =
// "EXECUTE sp_updateextendedproperty N'Description', '$tableComment', N'user', N'dbo', N'table', N'$table', NULL, NULL";
private static String COMMENT_COLUMN = "EXECUTE sp_addextendedproperty N'MS_Description', '$columnComment', N'user', N'dbo', N'table', N'$table', N'column', N'$column'";
public Sqlserver2005JdbcExpert(JdbcExpertConfigFile conf) {
super(conf);
}
public String getDatabaseType() {
return DB.SQLSERVER.name();
}
public boolean createEntity(Dao dao, Entity en) {
StringBuilder sb = new StringBuilder("CREATE TABLE " + en.getTableName() + "(");
// 创建字段
for (MappingField mf : en.getMappingFields()) {
if (mf.isReadonly())
continue;
sb.append('\n').append(mf.getColumnNameInSql());
sb.append(' ').append(evalFieldType(mf));
// 非主键的 @Name，应该加入唯一性约束
if (mf.isName() && en.getPkType() != PkType.NAME) {
sb.append(" UNIQUE NOT NULL");
}
// 普通字段
else {
if (mf.isUnsigned())
sb.append(" UNSIGNED");
if (mf.isNotNull())
sb.append(" NOT NULL");
if (mf.isAutoIncreasement())
sb.append(" IDENTITY");
if (mf.hasDefaultValue())
addDefaultValue(sb, mf);
}
sb.append(',');
}
// 创建主键
List pks = en.getPks();
if (!pks.isEmpty()) {
sb.append('\n');
sb.append("PRIMARY KEY (");
for (MappingField pk : pks) {
sb.append(pk.getColumnNameInSql()).append(',');
}
sb.setCharAt(sb.length() - 1, ')');
sb.append("\n ");
}
// 结束表字段设置
sb.setCharAt(sb.length() - 1, ')');
// 执行创建语句
dao.execute(Sqls.create(sb.toString()));
// 创建索引
dao.execute(createIndexs(en).toArray(new Sql[0]));
// 创建关联表
createRelation(dao, en);
// 添加注释(表注释与字段注释)
addComment(dao, en, COMMENT_COLUMN);
return true;
}
private void addComment(Dao dao, Entity en, String commentColumn) {
// TODO 表注释 SQLServer2005中貌似不行
// 字段注释
if (en.hasColumnComment()) {
List sqls = new ArrayList();
for (MappingField mf : en.getMappingFields()) {
if (mf.hasColumnComment()) {
Sql columnCommentSQL = Sqls.create(commentColumn);
columnCommentSQL.vars()
.set("table", en.getTableName())
.set("column", mf.getColumnName())
.set("columnComment", mf.getColumnComment());
sqls.add(columnCommentSQL);
}
}
// 执行创建语句
dao.execute(sqls.toArray(new Sql[sqls.size()]));
}
}
public String evalFieldType(MappingField mf) {
if (mf.getCustomDbType() != null)
return mf.getCustomDbType();
switch (mf.getColumnType()) {
case BOOLEAN:
return "BIT";
case TIMESTAMP:
return "DATETIME";// TODO 值得讨论
case DATETIME:
case DATE:
case TIME:
return "DATETIME";
case INT:
// 用户自定义了宽度
if (mf.getWidth() > 0)
return "NUMERIC(" + mf.getWidth() + ")";
// 用数据库的默认宽度
return "INT";
case FLOAT:
// 用户自定义了精度
if (mf.getWidth() > 0 && mf.getPrecision() > 0) {
return "decimal(" + mf.getWidth() + "," + mf.getPrecision() + ")";
}
// 用默认精度
if (mf.getMirror().isDouble())
return "decimal(15,10)";
return "float";
case VARCHAR:
if (NutConf.SQLSERVER_USE_NVARCHAR)
return "NVARCHAR(" + mf.getWidth() + ")";
return "VARCHAR(" + mf.getWidth() + ")";
case BINARY:
return "varbinary(max)";
//case TEXT :
// return "nvarchar(max)";
default :
break;
}
return super.evalFieldType(mf);
}
public void formatQuery(Pojo pojo) {
Pager pager = pojo.getContext().getPager();
if (null != pager && pager.getPageNumber() > 0) {
// -----------------------------------------------------
// TODO XXX 这个写法灰常暴力!!But , it works!!!! 期待更好的写法
PItem pi = pojo.getItem(0);
StringBuilder sb = new StringBuilder();
pi.joinSql(pojo.getEntity(), sb);
String str = sb.toString();
if (str.trim().toLowerCase().startsWith("select")) {
pojo.setItem(0, Pojos.Items.wrap(str.substring(6)));
} else
return;// 以免出错.
pojo.insertFirst(Pojos.Items.wrapf( "select * from(select row_number()over(order by __tc__)__rn__,* from(select top %d 0 __tc__, ",
pager.getOffset() + pager.getPageSize()));
pojo.append(Pojos.Items.wrapf(")t)tt where __rn__ > %d order by __rn__", pager.getOffset()));
}
}
@Override
public void formatQuery(Sql sql) {
Pager pager = sql.getContext().getPager();
// 需要进行分页
if (null != pager && pager.getPageNumber() > 0) {
// -----------------------------------------------------
// TODO XXX 这个写法灰常暴力!!But , it works!!!! 期待更好的写法
if (!sql.getSourceSql().toUpperCase().startsWith("SELECT "))
return;// 以免出错.
String xSql = sql.getSourceSql().substring(6);
String pre = String.format( "select * from(select row_number()over(order by __tc__)__rn__,* from(select top %d 0 __tc__, ",
pager.getOffset() + pager.getPageSize());
String last = String.format(")t)tt where __rn__ > %d", pager.getOffset());
sql.setSourceSql(pre + xSql + last);
}
}
protected String createResultSetMetaSql(Entity en) {
return "SELECT top 1 * FROM " + en.getViewName();
}
public Pojo fetchPojoId(Entity en, MappingField idField) {
String autoSql = "SELECT @@@@IDENTITY as $field";
Pojo autoInfo = new SqlFieldMacro(idField, autoSql);
autoInfo.setEntity(en);
return autoInfo;
}
public boolean addColumnNeedColumn() {
return false;
}
public String wrapKeyword(String columnName, boolean force) {
if (force || keywords.contains(columnName.toUpperCase()))
return "[" + columnName + "]";
return null;
}
public boolean isSupportGeneratedKeys() {
return false;
}
public List getIndexNames(Entity en, Connection conn) throws SQLException {
List names = new ArrayList();
String showIndexs = "SELECT i.name FROM sys.indexes AS i "
+ "INNER JOIN sys.tables AS o ON i.[object_id] = o.[object_id] "
+ "WHERE o.name = '"+en.getTableName()+"' AND i.is_primary_key = 0";
PreparedStatement ppstat = conn.prepareStatement(showIndexs);
ResultSet rest = ppstat.executeQuery();
while (rest.next()) {
String index = rest.getString(1);
names.add(index);
}
return names;
}
}