兼容Oracle 和 sql server 使用sql来进行复杂自定义标签formData的使用
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:/db.properties</value>
</list>
</property>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
<value>${database.driver}</value>
</property>
<property name="url">
<value>${database.url}</value>
</property>
<property name="username">
<value>${database.username}</value>
</property>
<property name="password">
<value>${database.password}</value>
</property>
</bean>
<!--
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>CCB.oraclePool</value>
</property>
<property name="resourceRef">
<value>false</value>
</property>
<property name="jndiEnvironment">
<props>
<prop key="java.naming.provider.url">t3://128.128.96.252:7001</prop>
<prop key="java.naming.factory.initial">weblogic.jndi.WLInitialContextFactory</prop>
</props>
</property>
</bean>
<bean id="dataSourceJdbc" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>CCB.oraclePool</value>
</property>
<property name="resourceRef">
<value>false</value>
</property>
<property name="jndiEnvironment">
<props>
<prop key="java.naming.provider.url">t3://128.128.96.252:7001</prop>
<prop key="java.naming.factory.initial">weblogic.jndi.WLInitialContextFactory</prop>
</props>
</property>
</bean>
-->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="mappingDirectoryLocations">
<list>
<value>classpath:/com/fa/ibox/plugin/entity</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.statement_cache.size">2500</prop>
<prop key="hibernate.c3p0.max_size">500000</prop>
<prop key="hibernate.c3p0.min_size">10</prop>
<prop key="hibernate.c3p0.timeout">5000000</prop>
<prop key="hibernate.c3p0.max_statements">200</prop>
<prop key="hibernate.c3p0.idle_test_period">5000000</prop>
<prop key="hibernate.c3p0.acquire_increment">2</prop>
<prop key="hibernate.dialect">
${database.dialect}
<!--
com.fa.core.dao.SQLServer2005Dialect
org.hibernate.dialect.SQLServerDialect
-->
</prop>
<prop key="hibernate.max_fetch_depth">3</prop>
<prop key="hibernate.jdbc.fetch_size">10000</prop>
<prop key="hibernate.jdbc.batch_size">3000</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.cascade">true</prop>
</props>
</property>
</bean>
<!-- config hibernate manger -->
<bean id="hibernateManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref local="sessionFactory" />
</property>
</bean>
<!-- config hibernate transaction -->
<bean id="transactionInterceptor"
class="org.springframework.transaction.interceptor.TransactionInterceptor">
<property name="transactionManager">
<ref bean="hibernateManager" />
</property>
<property name="transactionAttributes">
<props>
<prop key="get*">PROPAGATION_REQUIRED,readOnly</prop>
<prop key="is*">PROPAGATION_REQUIRED,readOnly</prop>
<prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>
<prop key="report*">PROPAGATION_REQUIRED,readOnly</prop>
<prop key="query*">PROPAGATION_REQUIRED,readOnly</prop>
<prop key="*">PROPAGATION_REQUIRED</prop>
</props>
</property>
</bean>
<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<bean
class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
<property name="beanNames">
<value>*Service</value>
</property>
<property name="interceptorNames">
<list>
<value>transactionInterceptor</value>
</list>
</property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- <bean id="jdbcDao" class="com.fa.core.plugin.dao.jdbc.OracleJdbcDao">
<property name="jdbcTemplate" ref="jdbcTemplate" />
<property name="jdbcTransactionManager" ref="jdbcTransactionManager" />
</bean> -->
<bean id="jdbcDao" class="com.fa.core.plugin.dao.jdbc.SqlServerJdbcDao">
<property name="jdbcTemplate" ref="jdbcTemplate" />
<property name="jdbcTransactionManager" ref="jdbcTransactionManager" />
</bean>
<bean id="jdbcTransactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref local="dataSource" />
</property>
</bean>
</beans>具体的jdbc观察下边这两节
<!-- <bean id="jdbcDao" class="com.fa.core.plugin.dao.jdbc.OracleJdbcDao">
<property name="jdbcTemplate" ref="jdbcTemplate" />
<property name="jdbcTransactionManager" ref="jdbcTransactionManager" />
</bean> -->
<bean id="jdbcDao" class="com.fa.core.plugin.dao.jdbc.SqlServerJdbcDao">
<property name="jdbcTemplate" ref="jdbcTemplate" />
<property name="jdbcTransactionManager" ref="jdbcTransactionManager" />
</bean><%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page contentType="text/html; charset=utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<%@ taglib uri="http://ibox.fortuneage.com/tags/ibox" prefix="ibox"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="css/complex.css" />
<link rel="stylesheet" type="text/css" href="css/tempFA.css" />
<script type="text/javascript" src="js/jquery.js"></script>
</head>
<body>
<h1>ibox:formData 使用：</h1>
<br>
<ibox:formData tableName="FO_DS_yi_test2">
<ul>
<li>{NAME(fdCreateAt)}</li>
<li>{NAME(fdUserID)}</li>
<li>{NAME(fdyi_testField2)}</li>
</ul>
</ibox:formData>
<div id="IBOX_FORM_0"></div>
</body>
</html>
上边是页面，具体看到就是下边的样子：
调用流程：
public String doGetDataFromPattern() throws Exception { try { long formId = ServletRequestUtils.getLongParameter(getRequest(), "formId", -1); if (formId == -1) { log.error("该 自定义表不存在！请输入正确的自定义表名称！"); return null; } int pageSize = ServletRequestUtils.getIntParameter(getRequest(), "pageSize", Page.getDEFAULT_PAGE_SIZE()); ; int pageNo = ServletRequestUtils.getIntParameter(getRequest(), "pageNo", 1); String where = ServletRequestUtils.getStringParameter(getRequest(), "where", ""); ; int showReply = ServletRequestUtils.getIntParameter(getRequest(), "showReply", 0); int topCount = ServletRequestUtils.getIntParameter(getRequest(), "topCount", 0); String pattern = ServletRequestUtils.getStringParameter( getRequest(), "pattern", ""); pattern = URLDecoder.decode(pattern, "utf-8"); String result = foFormService.pageQueryDataFront(formId, pageSi/** * 不知为什么，不能是查询相同的两个字段 * * 根据模板，获自字义表单的记录，在前台显示 * * @param formId * 表的id * @param pageSize * 第页显示的记录数 * @param pageNo * 显示第几页 * @param pattern * 显示的模板 * @param where * 表的查询条件 * @param showReply * 是否在前台显示表单数 * @param topCount * 整个表单，共显示前几条。 * @return 返回填充后的模板 */ public String pageQueryDataFront(long formId, int pageSize, int pageNo, String pattern, String where, int showReply, int topCount) { StringBuffer result = new StringBuffer(); StringBuffer sql = new StringBuffer(); List<String> fieldsPattern = getPatternField(pattern); if (PublicUtil.checkEmptyList(fieldsPattern)) { log.error("没有自定义表单是查询字段，不执行是查询！！"); return ""; } String sqls [] = getQuerySqlFront(pageNo,pageSize,formId, sql, fieldsPattern, where); log.info("执行的自定义表单是查询语句为： " + sql.toString()); Page page = null; try { //2018年1月22日18:18:29 根据db配置进行判断数据库类型 William Properties props = PropertiesUtils.getPropertiesFile("db.properties"); String driverClass = props.getProperty("database.driver"); if (!StringUtils.isEmpty(driverClass) && driverClass.toLowerCase().contains("sqlserver") && jdbcDao instanceof SqlServerJdbcDao) page = this.jdbcDao.pagedQueryBySqlServer(sqls, pageNo, pageSize); else page = this.jdbcDao.pagedQuery(sqls[0], pageNo, pageSize); } catch (Exception e) { page = this.jdbcDao.pagedQuery(sqls[0], pageNo, pageSize); } List<ListOrderedMap> records = (List) page.getData(); for (ListOrderedMap record : records) { String aRecord = fillPattern(pattern, record, fieldsPattern); result.append(aRecord); } return result.toString(); }
ze,pageNo, pattern, where, showReply, topCount);log.info(result);this.renderText(this.getResponse(), result);return null;} catch (Exception e) {e.printStackTrace();return null;}}/** * 获取自字义表单的sql语句表显示的标题 * William 2018年1月22日17:15:21 * @param formId * @param sql * @param title */ private String[] getQuerySqlFront(int pageNo,int pageSize,long formId, StringBuffer sql, List<String> fields, String where) { String tableName = this.foFormDao.getTableNameByFormId(formId); if (PublicUtil.checkEmptyString(tableName)) { return new String[]{"",""}; } //分页查询sql Server语句接拼 William 2018年1月22日17:03:32 /*SELECT top 10 A.* FROM (select top 20 * from FO_DS_yi_test2) A order by A.fdID desc */ //sql.append("select top " + pageSize + " A.* "); List<String> list = getPatternSqlField(fields); // for(String sqlField:list){ // sql.append(sqlField + ", " ); // } StringBuffer sqlSelectParams = new StringBuffer(); StringBuffer sqlCountSql = new StringBuffer("select "); for (int i = 0; i < list.size(); i++) { sqlSelectParams.append((String) list.get(i)); sqlCountSql.append((String) list.get(i)); if (i != list.size() - 1) { sqlSelectParams.append(" , "); sqlCountSql.append(" , "); } } sql.append("select top %1$d " + sqlSelectParams.toString() + " " ); //这里的 %1$d 或者 pageSize 为起始index sql.append(" from (select top %1$d * " + " "); //这里的 %1$d 或者 pageSize * pageNo 为区间闭合区域边界值 sql.append(" from " + tableName); sqlCountSql.append(" from " + tableName); if (!PublicUtil.checkEmptyString(where.trim())) { sql.append(" where " + where); sqlCountSql.append(" where " + where);package plugin.dao.jdbc; //~--- non-JDK imports -------------------------------------------------------- import java.util.List; import java.util.Map; import org.apache.commons.collections.map.ListOrderedMap; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.util.Assert; import plugin.pager.Page; import plugin.utils.Display; import plugin.utils.PublicUtil; import plugin.dao.FoFieldPro; /** * jdbcDao测试 * * @author bifulin * */ public abstract class JdbcDao extends JdbcDaoSupport { private static final Log log = LogFactory.getLog(JdbcDao.class); public List queryList(String sql, Object[] params) throws DataAccessException { return getJdbcTemplate().queryForList(sql, params); } /** * * 因为oracle里面的表名、字段是区分大小写的，而在sql server里面是没有区分的 * 所有为了统一起见，所有的表名、字段名在建立时，统一改成大写。 * * 为了防止注入攻击，将所有的空格转换成: "",，将所有的";"转换成:"" * * @param name * @return */ public String toSql(String name) { if (name == null) { return name; } name = name.toUpperCase(); name.replaceAll(" ", ""); name.replaceAll(";", ""); return name; } /** * 去除 sql 中的 select 子句 * * @param sql * @return String - sql */ private final String removeSelect(String sql) { Assert.hasText(sql); int beginPos = sql.toLowerCase().indexOf("from"); Assert.isTrue(beginPos != -1, " sql : " + sql + " must has a keyword 'from'"); return sql.substring(beginPos); } /** * 去除 sql 中的 order by 子句 * * @param sql * @return sql */ private final String removeOrder(String sql) { Assert.hasText(sql); return StringUtils.lowerCase(sql).replaceAll( "(order\\s+by\\s+[a-z,._\\s+]+(\\s+(asc|desc)?))", " "); } // /** * 获取本地的分页语句 * */ protected abstract String getLocalQuerySql(String sql); // oracle 及 mysql // 数据库用 protected abstract String getLocalQuerySql(String sql, String pk); // mssql数据库用 /*** * 获取本地的分页参数 * * @param startIndex * @param pageSize * @param params * @return */ protected abstract Object[] getParams(int startIndex, int pageSize, Object[] params); public abstract void addField(String tableName, FoFieldPro pro); public abstract void modifyField(String tableName, FoFieldPro pro); public abstract void dropField(String tableName, FoFieldPro pro); public abstract void dropIndex(String tableName, FoFieldPro pro); public abstract void createIndex(String tableName, FoFieldPro pro); public abstract void modifyNull(String tableName, FoFieldPro pro); public abstract void modifyNotNull(String tableName, FoFieldPro pro); public abstract void modifyDefault(String tableName, FoFieldPro pro); public abstract void createBaseTable(String tableName, List<FoFieldPro> fields, String pkField); public abstract boolean dropTable(String tableName); /*** * 获取开始的行号 * * @param pageNo * @param pageSize * @return */ private int getStartIndex(int pageNo, int pageSize) { if (!PublicUtil.checkBigerThanZero(pageSize)) { pageSize = Page.getDEFAULT_PAGE_SIZE(); } if (pageNo <= 0) { return 0; } return (pageNo - 1) * pageSize + 1; } public Page pagedQuery(String sql, int pageNo, int pageSize, Object[] params, RowMapper rowMapper) { int startIndex = getStartIndex(pageNo, pageSize); return pagedQuery(startIndex, pageSize, sql, params, rowMapper); } public Page pagedQuery(String sql, int pageNo, int pageSize, Object[] params) { int startIndex = getStartIndex(pageNo, pageSize); return pagedQuery(startIndex, pageSize, sql, params, null); } public Page pagedQuery(String sql, int pageNo, int pageSize) { return pagedQuery(sql, pageNo, pageSize, null); } public Page pagedQuery(String sql, int pageNo) { return pagedQuery(sql, pageNo, Page.getDEFAULT_PAGE_SIZE()); } public Page pagedQuery(int startIndex, int pageSize, String sql, Object[] params) { return pagedQuery(startIndex, pageSize, sql, params, null); } public Page pagedQuery(int startIndex, int pageSize, String sql) { return pagedQuery(startIndex, pageSize, sql, null); } public Page pagedQuery(int startIndex, String sql) { return pagedQuery(startIndex, 0, sql, null); } public Page pagedQuery(int startIndex, int pageSize, String sql, Object[] params, RowMapper rowMapper) { Assert.hasText(sql); Assert.isTrue(startIndex >= 0, "startIndex should start from 0"); // Count查询 String countQueryString = " select count (*) " + removeSelect(removeOrder(sql)); int totalCount = this.getJdbcTemplate().queryForInt(countQueryString, params); // 如果开始数大于总记录数，那么就取最后一页 if (startIndex > totalCount) { startIndex = totalCount - pageSize + 1; } int endIndex = startIndex + pageSize; if (totalCount < 1) return new Page(); if (pageSize == 0) { pageSize = Integer.MAX_VALUE; } // 实际查询返回分页对象 Object[] desParams = getParams(startIndex, pageSize, params); List list = null; if (desParams != null && rowMapper == null) { list = getJdbcTemplate().queryForList(getLocalQuerySql(sql), desParams); } else if (desParams != null && rowMapper != null) { list = getJdbcTemplate().query(getLocalQuerySql(sql), desParams, rowMapper); } Page page = new Page(startIndex, totalCount, pageSize, list); page.setCurrentPageSize(list.size()); return page; } @SuppressWarnings({ "unused", "rawtypes" }) public Page pagedQueryBySqlServer(String[] sqls, int pageNo, int pageSize) { int startIndex = getStartIndex(pageNo, pageSize); Assert.hasText(sqls[0]); Assert.hasText(sqls[1]); Assert.isTrue(startIndex >= 0, "startIndex should start from 0"); // Count查询 String countQueryString = " select count (*) " + removeSelect(removeOrder(sqls[0])); int totalCount = this.getJdbcTemplate().queryForInt(countQueryString, null); // 如果开始数大于总记录数，那么就取最后一页 if (startIndex > totalCount) { startIndex = totalCount - pageSize + 1; } int endIndex = startIndex + pageSize; if (totalCount < 1) return new Page(); if (pageSize == 0) { pageSize = Integer.MAX_VALUE; } // 实际查询返回分页对象 Object[] desParams = getParams(startIndex, pageSize, null); List list = null; if (desParams != null) { list = getJdbcTemplate().queryForList(getLocalQuerySql(String.format(sqls[1], startIndex,startIndex*pageSize))); } Page page = new Page(startIndex, totalCount, pageSize, list); page.setCurrentPageSize(list.size()); return page; } // private DataSourceTransactionManager jdbcTransactionManager; public DataSourceTransactionManager getJdbcTransactionManager() { return jdbcTransactionManager; } public void setJdbcTransactionManager( DataSourceTransactionManager jdbcTransactionManager) { this.jdbcTransactionManager = jdbcTransactionManager; } }
}sql.append(" ) A order by A.fdID desc ");log.info(sqlCountSql.toString());log.info(sql.toString());return new String[] {sqlCountSql.toString(),sql.toString()};}对应的jdbcdao
例如：oracle daopackage plugin.dao.jdbc; import java.util.ArrayList; import java.util.List; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.RowMapper; import plugin.pager.Page; import plugin.utils.Display; import plugin.utils.PublicUtil; import plugin.dao.DbType; import plugin.dao.FoFieldPro; public class OracleJdbcDao extends JdbcDao { private static final Log log = LogFactory.getLog(OracleJdbcDao.class); /** * 根据原始查询语句生成相应的分页SQL语句 * * @param sql * @return String */ public String getLocalQuerySql(String sql) { StringBuffer sqlBuffer = new StringBuffer(); sqlBuffer.append("SELECT * FROM(SELECT A.*,ROWNUM RN FROM("); sqlBuffer.append(sql); sqlBuffer.append(") A WHERE ROWNUM<?) WHERE RN>=?"); return sqlBuffer.toString(); } public String getLocalQuerySql(String sql, String pk) { return getLocalQuerySql(sql); } /** * 获取分页参数 */ public Object[] getParams(int startIndex, int pageSize, Object[] params) { Object[] des = null; Object[] append = { startIndex + pageSize, startIndex }; if (params == null) { return des = append; } else { des = new Object[params.length + append.length]; System.arraycopy(params, 0, des, 0, params.length); System.arraycopy(append, 0, des, params.length, append.length); } Display.display(des); return des; } // 增加字段 alter table FO_DS_BI3 add FD_TEST5 varchar(40) default '1' not // null;; public void addField(String tableName, FoFieldPro pro) { tableName = toSql(tableName); String columnName = toSql(pro.getName()); String type = DbType.getDbType(pro.getType()); boolean isRequire = pro.isRequire(); boolean isIndex = pro.isIndex(); String defaultVal = toSql(pro.getDefaultVal()); // 建立字段 String sql = " alter table " + tableName + " add " + columnName + " " + type; if (defaultVal != null) { sql = sql + " default " + "'" + defaultVal + "'"; } if (isRequire) { sql = sql + " not null "; } getJdbcTemplate().execute(sql); // 建立索引 if (isIndex) { sql = " create index " + tableName + "_index_" + columnName + " on " + tableName + ("(") + columnName + ")"; getJdbcTemplate().execute(sql); } } // 修改字段 alter table FO_DS_BI2 modify FDCREATEAT varchar2(50); /** * 因为字段类型的关系，此函数极易修改失败。 */ public void modifyField(String tableName, FoFieldPro pro) { tableName = toSql(tableName); String columnName = toSql(pro.getName()); String type = DbType.getDbType(pro.getType()); String sql = " alter table " + tableName + " modify " + columnName + " " + type; getJdbcTemplate().execute(sql); } // 删除字段 alter table FO_DS_BI2 drop column FDCODE; public void dropField(String tableName, FoFieldPro pro) { tableName = toSql(tableName); String columnName = toSql(pro.getName()); String sql = " alter table " + tableName + " drop column " + columnName; getJdbcTemplate().execute(sql); } // 建立根本表 public void createBaseTable(String tableName, List<FoFieldPro> fields, String pkField) { tableName = toSql(tableName); pkField = toSql(pkField); // 表名不能为空 if (PublicUtil.checkEmptyString(tableName)) { log.error("表名不能为空"); return; } // 至少要有一个字段 if (PublicUtil.checkEmptyList(fields)) { log.error("字段不能为空，至少需要有一个字段"); return; } StringBuffer sql = new StringBuffer(" create table " + tableName + " ("); List<String> indexs = new ArrayList<String>(); // 组合字段语句 for (FoFieldPro pro : fields) { String columnName = toSql(pro.getName()); String type = pro.getType(); String columnType = DbType.getDbType(type); boolean isRequire = pro.isRequire(); boolean isIndex = pro.isIndex(); String defaultVal = toSql(pro.getDefaultVal()); sql.append(toSql(columnName) + " " + toSql(columnType)); // //建立字段 if (defaultVal != null) { sql.append(" default " + "'" + defaultVal + "'"); } if (isRequire) { sql.append(" not null "); } if (columnName.equalsIgnoreCase(pkField)) { sql.append(" primary key "); } if (isIndex) { String index = " create index " + tableName + "_index_" + columnName + " on " + tableName + ("(") + columnName + ")"; indexs.add(index); } sql.append(" ,"); } // 删除最后的逗号 sql = new StringBuffer(sql.substring(0, sql.lastIndexOf(",") - 1)); sql.append(" ) "); log.info(sql.toString()); getJdbcTemplate().execute(sql.toString()); // 建立索引 if (!PublicUtil.checkEmptyList(indexs)) { for (String s : indexs) { getJdbcTemplate().execute(s); } } } // 删除数据表 public boolean dropTable(String tableName) { tableName = toSql(tableName); // 删除表 String sqlDropTable = "drop table " + tableName; try { getJdbcTemplate().execute(sqlDropTable); return true; } catch (Exception e) { e.printStackTrace(); return false; } } public void dropIndex(String tableName, FoFieldPro pro) { String columnName = toSql(pro.getName()); tableName = toSql(tableName); String sql = " drop index " + tableName + "_index_" + columnName; getJdbcTemplate().execute(sql); } public void createIndex(String tableName, FoFieldPro pro) { String columnName = toSql(pro.getName()); tableName = toSql(tableName); String sql = " create index " + tableName + "_index_" + columnName + " on " + tableName + ("(") + columnName + ")"; getJdbcTemplate().execute(sql); } public void modifyNull(String tableName, FoFieldPro pro) { String columnName = toSql(pro.getName()); tableName = toSql(tableName); String sql = "alter table " + tableName + " modify " + columnName + " null"; getJdbcTemplate().execute(sql); } public void modifyNotNull(String tableName, FoFieldPro pro) { String columnName = toSql(pro.getName()); tableName = toSql(tableName); String sql = "alter table " + tableName + " modify " + columnName + " not null"; getJdbcTemplate().execute(sql); } /** * * alter table STUDENT modify name default 1; alter table STUDENT modify * NAME default null; * */ public void modifyDefault(String tableName, FoFieldPro pro) { String columnName = toSql(pro.getName()); tableName = toSql(tableName); String sql = "alter table " + tableName + " modify " + columnName + " default " + pro.getDefaultVal(); getJdbcTemplate().execute(sql); } public static void main(String[] args) { String[] configLocations = { "classpath*:spring/*.xml" }; ApplicationContext applicationContext = new ClassPathXmlApplicationContext( configLocations); JdbcDao dao = (JdbcDao) applicationContext.getBean("jdbcDao"); int startIndex = 3; int pageSize = 5; int pageNo = 2; String sql = "select * from fo_ds_bi"; // Object[] params = {"%1%"}; Object[] params = null; // RowMapper rowMapper =new StudentRowMapper(); RowMapper rowMapper = null; // Page page = dao.pagedQuery(startIndex, pageSize, sql, params, // rowMapper); Page page = dao.pagedQuery(sql, pageNo, pageSize, params, rowMapper); log.info(page.getTotalCount()); List list = (List) page.getData(); Display.display(list); // for(int i=0;i<list.size();i++){ // Student student = (Student)list.get(i); // log.info(student.getName() + " " + student.getId()); // } } }参考了以下文章
https://www.cnblogs.com/liguangxi8/archive/2010/05/21/1740708.html
文章内容
浅淡SqlServer的Top与Oracle的RowNum
平时的项目开发中，分页存储过程是用的比较多的存储过程,SqlServer分页存储过程中经常要用到top,Oracle中则经常用到了RowNum.
现在，有一个UserInfo表，一个字段是UserId,另一个字段是UserName,其中是UserId是自动增长的,步长是1.表中共有30条数据，其中UserId的值不一定是连续的。现在要实现的目的是取其中的第11至第20条记录。先看SqlServer的几种做法:
第一种写法：
select top 10 *
from UserInfo
where UserId in
(
select top 20 UserId
from UserInfo
)
order by UserId desc
第二种写法：
select top 10 * from UserInfo where UserId not in(select top 10 UserId from UserInfo )
第三种写法：
select top 10 * from UserInfo where UserId >
( select max (UserId) from(select top10 UserId from UserInfo order by UserId) a)
第四种写法（只可在Sqlserver 2005中)：
select * from ( select Row_Number() over(Order by UserId) as RowId ,* from UserInfo) U
where U.RowId between 10 and 20
Sqlserver 中其实还有另外几种写法，不一一写出。四种方法中，后两种的写法要比前两种写法效率要高些，但第四种只能写在SqlServer 2005中。
在看Oracle中实现取其中的第11至第20条记录的做法之前，先看看一些人在使用RowNum遇到的莫名其妙的怪事。表同样是UserInfo,30条数据
select t. * from userinfo t where rownum > 10查询结果：
理论上应该是有20条数据才对啊，问题出现在哪呢？
因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (这里要强调的一点是：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。所以对于rownum>10没有数据是否可以这样理解：
ROWNUM 是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，最后的查询结果为空。
再看下面一条sql语句：
select t. * from userinfo t where rownum != 10查询结果：
查出的来结果不是21条，而是9条。可以这样理解：rownum 为9后的记录的 rownum为10，因条件为 !=10，所以去掉，其后记录补上，rownum又是10，也去掉，一直这样下去，最后的结果只有9条了。
如果把后面的条件改为 where rownum>1 时，会发现查不到一条数据，如果是where rownum>0 或是where rownum>＝1时则可以查询到所有的数据。原因很简单：因为 rownum 是在查询到的结果集后加上去的，它总是从1开始。
between 1 and 20 或者 between 0 and 20 能查到结果，而用 between 2 and 20 却得不到结果，原因同上一样，因为 rownum 总是从 1 开始。
所以要实现取UserInfo表其中的第11至第20条记录,可以这样写：
select * from
( select rownum as rn,t. * from userinfo t where rownum > 0 )
where rn between 10 and 20查询结果：
当然也可以这样写：
select * from UserInfo where rownum < 20
minus
select * from UserInfo where rownum < 10这种写法没有前面那种效率高。
但不能这样写：
select t. * from UserInfo t where rownum between 10 and 20
select t. * from UserInfo t where rownum > 10 and rownum <= 20上 面两种写法都取不到数据的。
对于Sqlserver 和Oracle实现取表其中的第11至第20条记录如果有更好的写法 ，可以贴出来学习下。
6216
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
