Oracle 分页查询
1.情景展示
Oracle 分页查询用法
SELECT F.*
FROM ( SELECT E.*, ROWNUM ROWNO
FROM ( SELECT 1 FROM VIRTUAL_CARDLOG T ORDER BY T.LOGTIME) E
WHERE ROWNUM <= 200) F
WHERE F.ROWNO >= 101
2024年1月31日15:15:02
java动态赋值
分页查询
private final static String ORACLE_GET_TABLE_DATAS = "SELECT *\n" +
" FROM (SELECT F.*, ROWNUM ROWNO\n" +
" FROM (SELECT T.* FROM TABLE_NAME T ORDER BY T.PRIMARY_KEY) F\n" +
" WHERE ROWNUM <= END)\n" +
" WHERE ROWNO >= START";
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
* 生成JdbcTemplate
* @param driverClassName
* @param url
* @param username
* @param password
* @return
*/
public static JdbcTemplate getJdbcTemplate(String driverClassName,
String url,
String username,
String password) {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return new JdbcTemplate(dataSource);
}
/**
* 获取oracle表分页数据
* @param database 数据库对象
* @param tableName 表名
* @return
*/
public static List<Map<String, Object>> getOracleTableDatas(FlinkDatabase database, String tableName, String primaryKey, Long start, Long end) {
JdbcTemplate template = getJdbcTemplate(database.getDatabasedriver(), database.getDatabaseurl(), database.getUsername(), database.getPassword());
// 要执行的SQL
String executeSql = ORACLE_GET_TABLE_DATAS;
// 替换表名
executeSql = executeSql.replace("TABLE_NAME", tableName);
// 替换主键
executeSql = executeSql.replace("PRIMARY_KEY", primaryKey);
// 替换开始页
executeSql = executeSql.replace("START", start + "");
// 替换结束页
executeSql = executeSql.replace("END", end + "");
return template.queryForList(executeSql);
}
查询总行数
// 获取指定表名的所有行数
private final static String GET_TABLE_TOTAL_ROWS = "SELECT COUNT(0) TOTAL FROM TABLE_NAME";
/**
* 查询指定表总行数
* @param database 数据库对象
* @param tableName 表名
* @return
*/
public static Long getTableRows(FlinkDatabase database, String tableName) {
JdbcTemplate template = JDBCUtils.getJdbcTemplate(database.getDatabasedriver(), database.getDatabaseurl(), database.getUsername(), database.getPassword());
String executeSql = GET_TABLE_TOTAL_ROWS;
// 替换表名
executeSql = executeSql.replace("TABLE_NAME", tableName);
return template.queryForObject(executeSql, Long.class);
}
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容,欢迎留言!!!
相关推荐:
本文来自博客园,作者:Marydon,转载请注明原文链接:https://www.cnblogs.com/Marydon20170307/p/12671801.html