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。

相关文章

SQL server数据库如何下载和安装

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步：打开官网下载对应文件第二步：程序安装配置第三部：安装工具SQL Server Manageme
阅读更多...
C#连接SQL server数据库命令的基本步骤

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用：如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.
阅读更多...
MyBatis中$与#的区别解析

MyBatis中$与#的区别解析

《MyBatis中$与#的区别解析》文章浏览阅读314次，点赞4次，收藏6次。MyBatis使用#{}作为参数占位符时，会创建预处理语句（PreparedStatement），并将参数值作为预处理语句... 目录一、介绍二、sql注入风险实例一、介绍#（井号）：MyBATis使用#{}作为参数占位符时，会
阅读更多...
mybatis执行insert返回id实现详解

mybatis执行insert返回id实现详解

《mybatis执行insert返回id实现详解》MyBatis插入操作默认返回受影响行数,需通过useGeneratedKeys+keyProperty或selectKey获取主键ID,确保主键为自... 目录 两种方式获取自增 ID：1. ​​useGeneratedKeys+keyProperty（推
阅读更多...
Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys
阅读更多...
Java操作Word文档的全面指南

Java操作Word文档的全面指南

《Java操作Word文档的全面指南》在Java开发中,操作Word文档是常见的业务需求,广泛应用于合同生成、报表输出、通知发布、法律文书生成、病历模板填写等场景,本文将全面介绍Java操作Word文... 目录简介段落页头与页脚页码表格图片批注文本框目录图表简介Word编程最重要的类是org.apach
阅读更多...
MyBatis-Plus 中 nested() 与 and() 方法详解(最佳实践场景)

MyBatis-Plus 中 nested() 与 and() 方法详解(最佳实践场景)

《MyBatis-Plus中nested()与and()方法详解(最佳实践场景)》在MyBatis-Plus的条件构造器中,nested()和and()都是用于构建复杂查询条件的关键方法,但... 目录MyBATis-Plus 中nested()与and()方法详解一、核心区别对比二、方法详解1.and()
阅读更多...
Python实现对阿里云OSS对象存储的操作详解

Python实现对阿里云OSS对象存储的操作详解

《Python实现对阿里云OSS对象存储的操作详解》这篇文章主要为大家详细介绍了Python实现对阿里云OSS对象存储的操作相关知识,包括连接,上传,下载,列举等功能,感兴趣的小伙伴可以了解下... 目录一、直接使用代码二、详细使用1. 环境准备2. 初始化配置3. bucket配置创建4. 文件上传到os
阅读更多...
mysql表操作与查询功能详解

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE
阅读更多...
MySQL数据库中ENUM的用法是什么详解

MySQL数据库中ENUM的用法是什么详解

《MySQL数据库中ENUM的用法是什么详解》ENUM是一个字符串对象,用于指定一组预定义的值,并可在创建表时使用,下面：本文主要介绍MySQL数据库中ENUM的用法是什么的相关资料,文中通过代码... 目录mysql 中 ENUM 的用法一、ENUM 的定义与语法二、ENUM 的特点三、ENUM 的用法1
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2