Mybatis操作Sybase数据库游标分页

2024-02-23 09:08
文章标签 操作 数据库 分页 游标 mybatis sybase

本文主要是介绍Mybatis操作Sybase数据库游标分页，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

声明

欢迎转载请注明原文地址，谢谢。

简介

sybase数据库本身是不支持分页sql语句，想用mybatis操作sybase分页很麻烦，在网上也看了不少使用数据库存储过程解决的。
但总觉得局限性太大出现很多问题，所以本文介绍结合mybatis进行游标分页处理，不影响mybatis本身写法的限制，
只需遵循分页接口规范即可（可支持多数据库分页，不过效率没有数据库本身分页效率高）。
本文源码地址：https://github.com/ssp1523/sybase-mybatis-pagination，最好先下载源码结合文本效果更佳。

第一步：创建maven web项目
maven pom文件配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.smp</groupId><artifactId>sybase-mybatis-pagination</artifactId><version>1.0-SNAPSHOT</version><packaging>war</packaging><description>Mybatis 基于sybase数据库游标分页例子</description><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><configuration><source>1.7</source><target>1.7</target></configuration></plugin></plugins></build><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><javax-activation.version>1.1</javax-activation.version><spring.version>4.0.4.RELEASE</spring.version><mysql-connector-java.version>5.1.34</mysql-connector-java.version><java.version>1.7</java.version></properties><dependencies><!-- spring start --><dependency><groupId>org.springframework</groupId><artifactId>spring-aop</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-aspects</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-beans</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-context-support</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-core</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-dao</artifactId><version>2.0.8</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-expression</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-mock</artifactId><version>2.0.8</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-orm</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-test</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-tx</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-web</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-webmvc</artifactId><version>${spring.version}</version></dependency><!-- spring end --><dependency><groupId>commons-dbcp</groupId><artifactId>commons-dbcp</artifactId><version>1.4</version></dependency><dependency><groupId>net.sourceforge.jtds</groupId><artifactId>jtds</artifactId><version>1.3.1</version></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.4.0</version></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId><version>1.3.0</version></dependency></dependencies></project>

web.xml文件配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5"><context-param><param-name>log4jConfigLocation</param-name><param-value>classpath:log4j.properties</param-value></context-param><filter><filter-name>encodingFilter</filter-name><filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class><init-param><param-name>encoding</param-name><param-value>utf-8</param-value></init-param></filter><filter-mapping><filter-name>encodingFilter</filter-name><url-pattern>/*</url-pattern></filter-mapping><servlet><servlet-name>springMvc</servlet-name><servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class><init-param><param-name>contextConfigLocation</param-name><param-value>classpath:spring/ApplicationContext.xml</param-value></init-param><load-on-startup>1</load-on-startup></servlet><servlet-mapping><servlet-name>springMvc</servlet-name><url-pattern>/</url-pattern></servlet-mapping>
</web-app>

resources/spring/ApplicationContext.xml spring 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"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"><!-- 启用注解 --><context:annotation-config/><context:component-scan base-package="com.smp"/><bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="dataSource"/></bean><bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"><property name="location" value="classpath:dbconfig.properties" /></bean><bean id="dataSource"                class="org.springframework.jdbc.datasource.DriverManagerDataSource" ><property name="driverClassName" value="${driverClassName}"/><property name="url" value="${url}"/><property name="username" value="${username}"/><property name="password" value="${password}"/></bean><!-- 配置mybatis --><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="dataSource"/><!-- mapper扫描 --><property name="mapperLocations" value="classpath:com/smp/mapper/*.xml"/></bean><!--mapper扫描--><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"><property name="basePackage" value="com.smp.mapper"/><property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/></bean><bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"><constructor-arg ref="sqlSessionFactory"/></bean>
</beans>

第二步：创建分页相关配置和java类

数据库连接配置文件 resources/dbconfig.properties，由于是演示项目所以配置较简单

url=你的数据库url地址
driverClassName=net.sourceforge.jtds.jdbc.Driver
username=你的数据库用户名
password=你的数据库密码

分页核心类 SybasePagination

package com.smp;import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.stereotype.Component;import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;/*** sybase 分页* Created by ssp on 2017/3/17.*/
@Component
public class SybasePagination {@Resource(name = "sqlSessionFactory")private SqlSessionFactory sqlSessionFactory;private BoundSql getBoundSql(String mapperId, Object parameter) {return getMappedStatement(mapperId).getBoundSql(parameter);}private MappedStatement getMappedStatement(String mapperId) {return sqlSessionFactory.getConfiguration().getMappedStatement(mapperId);}/*** mybatis游标分页方法* @param mapperId mybatis 映射id 如：com.smp.mapper.CustomMapper.findByListPage* @param params 分页参数 暂时只支持Page类型参数* @return 分页结果*/public List<PageData> listPage(String mapperId, Object params) throws SQLException, NoSuchFieldException, IllegalAccessException {return queryForListPage(sqlSessionFactory.openSession().getConnection(), getBoundSql(mapperId, params), getMappedStatement(mapperId), (Page) params);}/*** 分页核心方法，处理游标分页操作* 由于是使用jdbc游标分页，所以此处使用JdbcTemplate简化操作。* @param connection 数据库连接* @param boundSql 分页BoundSql* @param mappedStatement 分页 MappedStatement* @param page 分页Page类* @return 分页结果*/@SuppressWarnings("unchecked")private List<PageData> queryForListPage(final Connection connection, final BoundSql boundSql, final MappedStatement mappedStatement, final Page page) throws SQLException, NoSuchFieldException, IllegalAccessException {//分页countPagePlugin.pageCount(connection, mappedStatement, boundSql, page);return (List) new JdbcTemplate(new JdbcTemplateDataSource(connection)).query(new MyPreparedStatementCreator(boundSql, mappedStatement, page), new RowMapperResultSetExtractor(new ColumnPageDataRowMapper(page)) {@Overridepublic List extractData(ResultSet rs) throws SQLException {if (page.getCurrentResult() != 0) {//将游标移动到第一条记录rs.first();//   游标移动到要输出的第一条记录rs.relative(page.getCurrentResult() - 1);}return super.extractData(rs);}});}private class JdbcTemplateDataSource extends AbstractDataSource {private Connection connection;JdbcTemplateDataSource(Connection connection) {this.connection = connection;}public Connection getConnection() throws SQLException {return connection;}public Connection getConnection(String username, String password) throws SQLException {return connection;}}private class MyPreparedStatementCreator implements PreparedStatementCreator {private BoundSql boundSql;private MappedStatement mappedStatement;private Page page;MyPreparedStatementCreator(BoundSql boundSql, MappedStatement mappedStatement, Page page) {this.boundSql = boundSql;this.mappedStatement = mappedStatement;this.page = page;}public PreparedStatement createPreparedStatement(Connection con) throws SQLException {PreparedStatement pstat = con.prepareStatement(boundSql.getSql(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);PagePlugin.setParameters(pstat, mappedStatement, boundSql, boundSql.getParameterObject());pstat.setMaxRows(page.getCurrentResult() + page.getShowCount());return pstat;}}private class ColumnPageDataRowMapper implements RowMapper<PageData> {private Page page;ColumnMapRowMapper columnMapRowMapper = new ColumnMapRowMapper() {@Overrideprotected Map<String, Object> createColumnMap(int columnCount) {return new PageData();}@Overrideprotected String getColumnKey(String columnName) {if (page.getCamelName())return StringUtil.camelName(columnName);elsereturn super.getColumnKey(columnName);}};private ColumnPageDataRowMapper(Page page) {this.page = page;}public PageData mapRow(ResultSet rs, int rowNum) throws SQLException {return (PageData) columnMapRowMapper.mapRow(rs, rowNum);}}
}

Page分页vo类，存储分页相关信息，作为分页参数使用

package com.smp;public class Page {private int showCount; //每页显示记录数private int totalPage;        //总页数private int totalResult;    //总记录数private int currentPage;    //当前页private int currentResult;    //当前记录起始索引private boolean entityOrField;    //true:需要分页的地方，传入的参数就是Page实体；false:需要分页的地方，传入的参数所代表的实体拥有Page属性private PageData pd = new PageData();private Boolean camelName = false;public Page() {}public int getTotalPage() {if (totalResult % showCount == 0)totalPage = totalResult / showCount;elsetotalPage = totalResult / showCount + 1;return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public int getTotalResult() {return totalResult;}public void setTotalResult(int totalResult) {this.totalResult = totalResult;}public int getCurrentPage() {if (currentPage <= 0)currentPage = 1;if (currentPage > getTotalPage())currentPage = getTotalPage();return currentPage;}public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}public int getShowCount() {return showCount;}public void setShowCount(int showCount) {this.showCount = showCount;}public int getCurrentResult() {currentResult = (getCurrentPage() - 1) * getShowCount();if (currentResult < 0)currentResult = 0;return currentResult;}public void setCurrentResult(int currentResult) {this.currentResult = currentResult;}public boolean isEntityOrField() {return entityOrField;}public void setEntityOrField(boolean entityOrField) {this.entityOrField = entityOrField;}public PageData getPd() {return pd;}public void setPd(PageData pd) {this.pd = pd;}public Boolean getCamelName() {return camelName;}public void setCamelName(Boolean camelName) {this.camelName = camelName;}
}

PageData类，分页查询的方法结果一条记录为一个PageData

package com.smp;import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;public class PageData extends HashMap<String, Object> implements Map<String, Object> {private static final long serialVersionUID = 1L;private Map<String, Object> map = null;public PageData() {map = new HashMap<>();}@Overridepublic Object get(Object key) {return map.get(key);}public String getString(Object key) {return (String) get(key);}@SuppressWarnings("unchecked")@Overridepublic Object put(String key, Object value) {return map.put(key, value);}@Overridepublic Object remove(Object key) {return map.remove(key);}public void clear() {map.clear();}public boolean containsKey(Object key) {return map.containsKey(key);}public boolean containsValue(Object value) {return map.containsValue(value);}public Set<Map.Entry<String, Object>> entrySet() {return map.entrySet();}public boolean isEmpty() {return map.isEmpty();}public Set<String> keySet() {return map.keySet();}@SuppressWarnings("unchecked")public void putAll(Map t) {map.putAll(t);}public int size() {return map.size();}public Collection<Object> values() {return map.values();}}

PagePlugin类，分页工具类，处理总条数和参数设置。

package com.smp;import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;class PagePlugin  {static void pageCount(Connection connection, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws NoSuchFieldException, IllegalAccessException, SQLException {String countSql = "select count(0) from (" + sqlProcess(boundSql.getSql()) + ")  tmp_count"; //记录统计 == oracle 加 as 报错(SQL command not properly ended)PreparedStatement countStmt = connection.prepareStatement(countSql);BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);setParameters(countStmt, mappedStatement, countBS, parameterObject);ResultSet rs = countStmt.executeQuery();int count = 0;if (rs.next()) {count = rs.getInt(1);}rs.close();countStmt.close();Page page;if (parameterObject instanceof Page) {    //参数就是Page实体page = (Page) parameterObject;page.setEntityOrField(true);page.setTotalResult(count);} else {    //参数为某个实体，该实体拥有Page属性Field pageField = ReflectHelper.getFieldByFieldName(parameterObject, "page");if (pageField != null) {page = (Page) ReflectHelper.getValueByFieldName(parameterObject, "page");if (page == null)page = new Page();page.setEntityOrField(false);page.setTotalResult(count);ReflectHelper.setValueByFieldName(parameterObject, "page", page); //通过反射，对实体对象设置分页对象} else {throw new NoSuchFieldException(parameterObject.getClass().getName() + "不存在 page 属性！");}}}/*** sql处理** @param sql 被处理的sql* @return 处理后的sql语句*/private static String sqlProcess(String sql) {//去掉sql语句 order by 语句int orderIndex;if ((orderIndex = sql.toLowerCase().lastIndexOf("order")) != -1) {return sql.substring(0, orderIndex);}return sql;}/*** 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler*/@SuppressWarnings("unchecked")static void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();if (parameterMappings != null) {Configuration configuration = mappedStatement.getConfiguration();TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);for (int i = 0; i < parameterMappings.size(); i++) {ParameterMapping parameterMapping = parameterMappings.get(i);if (parameterMapping.getMode() != ParameterMode.OUT) {Object value;String propertyName = parameterMapping.getProperty();PropertyTokenizer prop = new PropertyTokenizer(propertyName);if (parameterObject == null) {value = null;} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {value = parameterObject;} else if (boundSql.hasAdditionalParameter(propertyName)) {value = boundSql.getAdditionalParameter(propertyName);} else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) {value = boundSql.getAdditionalParameter(prop.getName());if (value != null) {value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));}} else {value = metaObject == null ? null : metaObject.getValue(propertyName);}TypeHandler typeHandler = parameterMapping.getTypeHandler();if (typeHandler == null) {throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());}typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());}}}}}

ReflectHelper类，反射工具类

package com.smp;import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;class PagePlugin  {static void pageCount(Connection connection, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws NoSuchFieldException, IllegalAccessException, SQLException {String countSql = "select count(0) from (" + sqlProcess(boundSql.getSql()) + ")  tmp_count"; //记录统计 == oracle 加 as 报错(SQL command not properly ended)PreparedStatement countStmt = connection.prepareStatement(countSql);BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);setParameters(countStmt, mappedStatement, countBS, parameterObject);ResultSet rs = countStmt.executeQuery();int count = 0;if (rs.next()) {count = rs.getInt(1);}rs.close();countStmt.close();Page page;if (parameterObject instanceof Page) {    //参数就是Page实体page = (Page) parameterObject;page.setEntityOrField(true);page.setTotalResult(count);} else {    //参数为某个实体，该实体拥有Page属性Field pageField = ReflectHelper.getFieldByFieldName(parameterObject, "page");if (pageField != null) {page = (Page) ReflectHelper.getValueByFieldName(parameterObject, "page");if (page == null)page = new Page();page.setEntityOrField(false);page.setTotalResult(count);ReflectHelper.setValueByFieldName(parameterObject, "page", page); //通过反射，对实体对象设置分页对象} else {throw new NoSuchFieldException(parameterObject.getClass().getName() + "不存在 page 属性！");}}}/*** sql处理** @param sql 被处理的sql* @return 处理后的sql语句*/private static String sqlProcess(String sql) {//去掉sql语句 order by 语句int orderIndex;if ((orderIndex = sql.toLowerCase().lastIndexOf("order")) != -1) {return sql.substring(0, orderIndex);}return sql;}/*** 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler*/@SuppressWarnings("unchecked")static void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();if (parameterMappings != null) {Configuration configuration = mappedStatement.getConfiguration();TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);for (int i = 0; i < parameterMappings.size(); i++) {ParameterMapping parameterMapping = parameterMappings.get(i);if (parameterMapping.getMode() != ParameterMode.OUT) {Object value;String propertyName = parameterMapping.getProperty();PropertyTokenizer prop = new PropertyTokenizer(propertyName);if (parameterObject == null) {value = null;} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {value = parameterObject;} else if (boundSql.hasAdditionalParameter(propertyName)) {value = boundSql.getAdditionalParameter(propertyName);} else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) {value = boundSql.getAdditionalParameter(prop.getName());if (value != null) {value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));}} else {value = metaObject == null ? null : metaObject.getValue(propertyName);}TypeHandler typeHandler = parameterMapping.getTypeHandler();if (typeHandler == null) {throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());}typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());}}}}}

StringUtil类，字符串工具类

package com.smp;
/*** 字符串相关方法**/
public class StringUtil {/*** 将以逗号分隔的字符串转换成字符串数组* @param valStr* @return String[]*/public static String[] StrList(String valStr){int i = 0;String TempStr = valStr;String[] returnStr = new String[valStr.length() + 1 - TempStr.replace(",", "").length()];valStr = valStr + ",";while (valStr.indexOf(',') > 0){returnStr[i] = valStr.substring(0, valStr.indexOf(','));valStr = valStr.substring(valStr.indexOf(',')+1 , valStr.length());i++;}return returnStr;}/*** 将下划线大写方式命名的字符串转换为驼峰式。如果转换前的下划线大写方式命名的字符串为空，则返回空字符串。</br>* 例如：HELLO_WORLD->helloWorld** @param name 转换前的下划线大写方式命名的字符串* @return 转换后的驼峰式命名的字符串*/public static String camelName(String name) {StringBuilder result = new StringBuilder();// 快速检查if (name == null || name.isEmpty()) {// 没必要转换return "";} else if (!name.contains("_")) {// 不含下划线，仅将首字母小写return name.substring(0, 1).toLowerCase() + name.substring(1);}// 用下划线将原始字符串分割String camels[] = name.split("_");for (String camel : camels) {// 跳过原始字符串中开头、结尾的下换线或双重下划线if (camel.isEmpty()) {continue;}// 处理真正的驼峰片段if (result.length() == 0) {// 第一个驼峰片段，全部字母都小写result.append(camel.toLowerCase());} else {// 其他的驼峰片段，首字母大写result.append(camel.substring(0, 1).toUpperCase());result.append(camel.substring(1).toLowerCase());}}return result.toString();}
}

PaginationMapper类，Mapper分页接口，需要分页的Mapper类继承该类。

package com.smp.mapper;import com.smp.Page;
import com.smp.PageData;import java.util.List;/*** 分页mapper* Created by ssp on 2017/3/18.*/
public interface PaginationMapper{List<PageData> findByListPage(Page page);}

第三步：创建分页测试类及数据
使用Custom进行分页测试
首先创建 Custom 实体

package com.smp;public class Custom {private String firstName;private String lastName;public String getFirstName() {return firstName;}public void setFirstName(String firstName) {this.firstName = firstName;}public String getLastName() {return lastName;}public void setLastName(String lastName) {this.lastName = lastName;}
}

Service，Controller，Mapper，xml

package com.smp;import com.smp.mapper.CustomMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.sql.SQLException;
import java.util.Collections;
import java.util.List;@Service
public class CustomService {@Autowiredprivate CustomMapper customMapper;@Autowiredprivate SybasePagination sybasePagination;List<PageData> findByListPage(Page page){try {return sybasePagination.listPage(CustomMapper.class.getName() + ".findByListPage", page);} catch (SQLException | NoSuchFieldException | IllegalAccessException e) {e.printStackTrace();}return Collections.emptyList();}}

package com.smp;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestController
public class CustomController {@Autowiredprivate CustomService customService;@RequestMapping("/page")public void page(){Page page = new Page();//字段名称转换成小驼峰式true，不转换为falsepage.setCamelName(true);//设置当前是第几页page.setCurrentPage(2);//每页有多条数据page.setShowCount(5);
//        page.getPd().put("lastName", "1");List<PageData> byListPage = customService.findByListPage(page);System.out.println("一页条数："+byListPage.size());System.out.println("本页内容："+byListPage);System.out.println("总记录数："+page.getTotalResult());}}

package com.smp.mapper;import org.springframework.stereotype.Repository;@Repository
public interface CustomMapper extends PaginationMapper {}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.smp.mapper.CustomMapper"><resultMap id="custom" type="com.smp.Custom"><result column="first_name" property="firstName"/><result column="last_name" property="lastName"/></resultMap><sql id="Base_Column_List">first_name,last_name</sql><select id="findByListPage" parameterType="com.smp.Page" resultType="com.smp.PageData" useCache="false">select<include refid="Base_Column_List"/>from custom<where><if test="pd.firstName != null and pd.firstName != ''">and first_name = #{pd.firstName}</if><if test="pd.lastName != null and pd.lastName != ''">and last_name = #{pd.lastName}</if></where></select></mapper>

数据库sql脚本，建表及测试数据

if exists (select 1from  sysobjectswhere id = object_id('custom')and   type = 'U')drop table custom
go/*==============================================================*/
/* Table: t_rule_prepaid_card                                   */
/*==============================================================*/
create table custom (first_name             varchar(100)                   null,last_name             varchar(100)                   null,
)
goINSERT INTO custom (first_name, last_name) VALUES ('1', '1') go
INSERT INTO custom (first_name, last_name) VALUES ('2', '2') go
INSERT INTO custom (first_name, last_name) VALUES ('3', '3') go
INSERT INTO custom (first_name, last_name) VALUES ('4', '4') go
INSERT INTO custom (first_name, last_name) VALUES ('5', '5') go
INSERT INTO custom (first_name, last_name) VALUES ('6', '6') go
INSERT INTO custom (first_name, last_name) VALUES ('7', '7') go
INSERT INTO custom (first_name, last_name) VALUES ('8', '8') go
INSERT INTO custom (first_name, last_name) VALUES ('9', '8') go
INSERT INTO custom (first_name, last_name) VALUES ('10', '10') go
INSERT INTO custom (first_name, last_name) VALUES ('11', '11') go
INSERT INTO custom (first_name, last_name) VALUES ('12', '12') go
INSERT INTO custom (first_name, last_name) VALUES ('13', '13') go

第四步：运行环境进行测试
将改工程部署到tomcat上，运行测试
访问地址:http://localhost:8080/page 查看到控制台打印的信息

一页条数：5
本页内容：[{firstName=6, lastName=6}, {firstName=7, lastName=7}, {firstName=8, lastName=8}, {firstName=9, lastName=8}, {firstName=10, lastName=10}]
总记录数：13

可以根据自己的测试需要调整当前页和每页的条数（此处为了测试方便使用直接赋值方式，可以根据自己的需要改成变量赋值）

最后
有什么疑问可以在评论里留言
源码地址：https://github.com/ssp1523/sybase-mybatis-pagination

这篇关于Mybatis操作Sybase数据库游标分页的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/738131。

相关文章

Linux使用fdisk进行磁盘的相关操作

Linux使用fdisk进行磁盘的相关操作

《Linux使用fdisk进行磁盘的相关操作》fdisk命令是Linux中用于管理磁盘分区的强大文本实用程序,这篇文章主要为大家详细介绍了如何使用fdisk进行磁盘的相关操作,需要的可以了解下... 目录简介基本语法示例用法列出所有分区查看指定磁盘的区分管理指定的磁盘进入交互式模式创建一个新的分区删除一个存
阅读更多...
Golang操作DuckDB实战案例分享

Golang操作DuckDB实战案例分享

《Golang操作DuckDB实战案例分享》DuckDB是一个嵌入式SQL数据库引擎,它与众所周知的SQLite非常相似,但它是为olap风格的工作负载设计的,DuckDB支持各种数据类型和SQL特性... 目录DuckDB的主要优点环境准备初始化表和数据查询单行或多行错误处理和事务完整代码最后总结Duck
阅读更多...
IDEA如何切换数据库版本mysql5或mysql8

IDEA如何切换数据库版本mysql5或mysql8

《IDEA如何切换数据库版本mysql5或mysql8》本文介绍了如何将IntelliJIDEA从MySQL5切换到MySQL8的详细步骤,包括下载MySQL8、安装、配置、停止旧服务、启动新服务以及... 目录问题描述解决方案第一步第二步第三步第四步第五步总结问题描述最近想开发一个新应用，想使用mysq
阅读更多...
在MyBatis的XML映射文件中＜trim＞元素所有场景下的完整使用示例代码

在MyBatis的XML映射文件中＜trim＞元素所有场景下的完整使用示例代码

《在MyBatis的XML映射文件中＜trim＞元素所有场景下的完整使用示例代码》在MyBatis的XML映射文件中,trim元素用于动态添加SQL语句的一部分,处理前缀、后缀及多余的逗号或连接符,示... 在MyBATis的XML映射文件中，<trim>元素用于动态地添加SQL语句的一部分，例如SET或W
阅读更多...
Mybatis官方生成器的使用方式

Mybatis官方生成器的使用方式

《Mybatis官方生成器的使用方式》本文详细介绍了MyBatisGenerator（MBG）的使用方法,通过实际代码示例展示了如何配置Maven插件来自动化生成MyBatis项目所需的实体类、Map... 目录1. MyBATis Generator 简介2. MyBatis Generator 的功能3
阅读更多...
C# 读写ini文件操作实现

C# 读写ini文件操作实现

《C#读写ini文件操作实现》本文主要介绍了C#读写ini文件操作实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录一、INI文件结构二、读取INI文件中的数据在C#应用程序中，常将INI文件作为配置文件，用于存储应用程序的
阅读更多...
Python使用qrcode库实现生成二维码的操作指南

Python使用qrcode库实现生成二维码的操作指南

《Python使用qrcode库实现生成二维码的操作指南》二维码是一种广泛使用的二维条码,因其高效的数据存储能力和易于扫描的特点,广泛应用于支付、身份验证、营销推广等领域,Pythonqrcode库是... 目录一、安装 python qrcode 库二、基本使用方法1. 生成简单二维码2. 生成带 Log
阅读更多...
Java操作ElasticSearch的实例详解

Java操作ElasticSearch的实例详解

《Java操作ElasticSearch的实例详解》Elasticsearch是一个分布式的搜索和分析引擎,广泛用于全文搜索、日志分析等场景,本文将介绍如何在Java应用中使用Elastics... 目录简介环境准备1. 安装 Elasticsearch2. 添加依赖连接 Elasticsearch1. 创
阅读更多...
Oracle数据库使用 listagg去重删除重复数据的方法汇总

Oracle数据库使用 listagg去重删除重复数据的方法汇总

《Oracle数据库使用listagg去重删除重复数据的方法汇总》文章介绍了在Oracle数据库中使用LISTAGG和XMLAGG函数进行字符串聚合并去重的方法,包括去重聚合、使用XML解析和CLO... 目录案例表第一种：使用wm_concat() + distinct去重聚合第二种：使用listagg，
阅读更多...
java Stream操作转换方法

java Stream操作转换方法

《javaStream操作转换方法》文章总结了Java8中流(Stream)API的多种常用方法,包括创建流、过滤、遍历、分组、排序、去重、查找、匹配、转换、归约、打印日志、最大最小值、统计、连接、... 目录流创建1、list 转 map2、filter（）过滤3、foreach遍历4、groupingB
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2