本文介绍了一种兼容Oracle和SQL Server数据库的分页查询方法，并提供了详细的Spring框架配置示例与自定义表单数据展示的技术实现。
兼容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 dao
package 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
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 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 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
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
select t. * from UserInfo t where rownum > 10 and rownum <= 20
上 面两种写法都取不到数据的。
对于Sqlserver 和Oracle实现取表其中的第11至第20条记录如果有更好的写法 ，可以贴出来学习下。
扫一扫
943
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
