1.MySql
驱动名称:com.mysql.cj.jdbc.Driver
连接地址:jdbc:mysql://IP:PORT/TABLE_NAME
分页语句:select * from 表名 limit startrow,pagesize
表存在则删除表语句:DROP TABLE IF EXISTS XX;
2.oracle
驱动名称:oracle.jdbc.driver.OracleDriver
连接地址:jdbc:oracle:thin:@://IP:PORT:SERVICENAME
分页语句:select * from (select a.*,rownum rc from 表名 where rownum<=endrow) a where a.rc>=startrow
表存在则删除表:
declare
num number;
begin
select count(1) into num from all_tables where TABLE_NAME = '[判断的表名]';
if num = 1 then
execute immediate 'drop table [删除的表名]';
end if;
end;
3.达梦
驱动名称:dm.jdbc.driver.DmDriver
连接地址:jdbc:dm://IP:PORT;databaseName=TABLE_NAME
分页语句:select * from (select a.*,rownum rc from 表名 where rownum<=endrow) a where a.rc>=startrow
表存在则删除表语句:DROP TABLE IF EXISTS XX;
4.db2
驱动名称:com.ibm.db2.jcc.DB2Driver
连接地址:jdbc:db2://IP:PORT/TABLE_NAME
分页语句:select * from (select rownumber() over() as rc,a.* from (select * from 表名 order by 列名) as a) where rc between startrow and endrow
5.SQLserver
驱动名称:com.microsoft.sqlserver.jdbc.SQLServerDriver
连接地址:jdbc:sqlserver://IP:PORT;databaseName=TABLE_NAME
分页语句:
- Top Not IN 方式(查询靠前的数据较快)
语法格式:
select top pageSize 列名 from tablename
select top 条数 * from tablename
where Id not in (select top pageSize*(pageIndex-1) Id from tablename)
示例:
SELECT TOP 2 * FROM Users WHERE Id NOT IN (SELECT TOP 2 Id FROM Users)
- ROW_NUMBER() OVER()方式 (查询靠后的数据速度比上一种较快)
语法格式:
SELECT * FROM (SELECT *,ROW_NUMBER() OVER(Order by Id ) AS RowNumber from tablename ) as b where RowNumber between pageIndex-1*pageSize and pageIndex*pageSize
示例:
SELECT* FROM (SELECT*,ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Users ) as b where RowNumber BETWEEN 0 and 3
- offset fetch next方式 (速度优于前两者,限制Sql2012以上可以使用)
语法格式:
select * from tablename order by Id offset pageIndex row fetch next pageSize row only
示例:select * from Users order by Id offset 2 row fetch next 5 row only
6.神通
驱动名称:com.oscar.Driver
连接地址:jdbc:oscar://IP:PORT/TABLE_NAME
分页语句:
7.人大金仓 kingbaseES V7
驱动名称:com.kingbase.Driver
连接地址:jdbc:kingbase://IP:PORT/TABLE_NAME
分页语句:select * from 表名 limit startrow,pagesize
select * from 表名 limit pagesize offset startrow
8.GBase8s
驱动名称:com.gbasedbt.jdbc.IfxDriver
连接地址:jdbc:gbasedbt-sqli://IP:PORT/TABLE_NAME:GBASEDBTSERVER=SERVICENAME
分页语句:select skip 2 first 3 * from 表名; 从第三位开始提取 3 个记录
select top 2,3 * from 表名;
9.GBase8t
驱动名称:com.informix.jdbc.IfxDriver
连接地址:jdbc:informix-sqli://IP:PORT/TABLE_NAME:GBASEDBTSERVER=SERVICENAME
分页语句:select skip 2 first 3 * from 表名; 从第三位开始提取 3 个记录
select top 2,3 * from 表名;