依赖
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>21.9.0.0</version>
</dependency>
一、建表测试
-- 创建表之前判断表是否存在，如果存在则删除已有表
DECLARE num NUMBER;
BEGIN
SELECT count( 1 ) INTO num FROM user_tables WHERE table_name = upper( 'discounts' );
IF num > 0 THEN
execute IMMEDIATE 'drop table discounts';
END IF;
END;
-- 创建表
CREATE TABLE discounts (
discount_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
discount_name VARCHAR2(255) NOT NULL,
amount NUMBER(3,1) NOT NULL,
start_date DATE NOT NULL,
expired_date DATE NOT NULL
);
INSERT INTO discounts(discount_name, amount, start_date, expired_date)VALUES('双11电脑特价', 6.5, DATE '2017-11-11', DATE '2017-11-12');
INSERT INTO discounts(discount_name, amount, expired_date, start_date)VALUES('2017长期折扣', 9.5, DATE '2017-12-31', CURRENT_DATE);
INSERT INTO discounts(discount_name, amount, expired_date, start_date)VALUES('2023长期折扣', 8.0, DATE '2017-12-31', SYSDATE);
-- 查询第31-40行数据
-- 40 为 pageCurrent * pageSize，30 应为 (pageCurrent - 1) * pageSize
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM DISCOUNTS
WHERE 1 = 1 -- 条件
ORDER BY discount_id DESC -- 排序
) A
WHERE ROWNUM <= 40
)
WHERE RN > 30
二、工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class OJdbcUtil {
private static final Integer BATCH_COUNT = 100;
/**
* 获取数据库连接
* @return Connection
*/
public static Connection getDataSource(String dburl, String username, String password) {
Connection conn = null;
try {
//驱动名
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(dburl, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 根据sql获取结果
*
* @param connection 数据库连接
* @param sql sql语句
* @return 查询结果
*/
public static ArrayList<LinkedHashMap<String, String>> getSelectSqlResult(Connection connection, String sql) {
ArrayList<LinkedHashMap<String, String>> result = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
List<String> field = new ArrayList<>();
try {
pstmt = connection.prepareStatement(sql);
rs = pstmt.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
// 获取SQL查询结果的列名，下标从1开始
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
field.add(resultSetMetaData.getColumnLabel(i));
}
while (rs.next()) {
LinkedHashMap<String, String> data = new LinkedHashMap<>();
for (String f : field) {
data.put(f, rs.getString(f));
}
result.add(data);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != rs) {
rs.close();
}
if (null != pstmt) {
pstmt.close();
}
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
return result;
}
/**
* delete/insert/update
*
* @param con 数据库连接
* @param update_sql SQL语句
* @return 更新记录条数
*/
public static int updateSql(Connection con, String update_sql) {
int row = 0;
PreparedStatement pstmt = null;
try {
System.out.println("SQL: " + update_sql);
con.setAutoCommit(false);
pstmt = con.prepareStatement(update_sql);
row = pstmt.executeUpdate();
//执行完更新语句后手动提交
con.commit();
} catch (SQLException e) {
// TODO: handle exception
try {
//sql执行出现异常后回滚
con.rollback();
con.setAutoCommit(true);
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
con.setAutoCommit(true);
if (null != pstmt) {
pstmt.close();
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
return row;
}
/**
* 执行sql，自动判断是更新还是查询SQL
*
* @param con 数据库连接
* @param sql SQL语句
* @return 返回结果map。map字段解析：status----true/false，sql是否执行成功。type-----select/update，sql执行结果类型，select为查询sql，update为更新sql。row----更新数据行数。data----sql查询结果，ArrayList<LinkedHashMap<String, String>>
*/
public static Map<String, Object> execSql(Connection con, String sql) {
PreparedStatement pstmt = null;
ResultSet rs = null;
Map<String, Object> resultMap = new HashMap<>();
try {
System.out.println("SQL: " + sql);
int updateCount = 0;
con.setAutoCommit(false);
pstmt = con.prepareStatement(sql);
// true表示有resultset，false表示有更新数据条数或者没有resultset
boolean currentExec = pstmt.execute();
while (true) {
if (currentExec) {
rs = null;
rs = pstmt.getResultSet();
ArrayList<LinkedHashMap<String, String>> dataList = getDataResultByResultSet(rs);
resultMap.put("type", "select");
resultMap.put("data", dataList);
} else {
updateCount = pstmt.getUpdateCount();
if (updateCount == -1) {
// 没有更多结果了
break;
} else {
resultMap.put("type", "update");
resultMap.put("row", updateCount);
}
}
currentExec = pstmt.getMoreResults();
}
con.commit();
resultMap.put("status", true);
} catch (Exception e) {
resultMap.put("status", false);
try {
con.rollback();
con.setAutoCommit(true);
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
con.setAutoCommit(true);
if (null != rs) {
rs.close();
}
if (null != pstmt) {
pstmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return resultMap;
}
/**
* JDBC 批处理 SQL 语句
* @param connection 数据库连接
* @param sqlList SQL列表
*/
public static void updateBatch(Connection connection, List<String> sqlList) {
Statement statement = null;
try {
connection.setAutoCommit(false);
statement = connection.createStatement();
long begin = System.currentTimeMillis();
int index = 0, count = 0;
for (String sql : sqlList) {
index++;
statement.addBatch(sql);
System.out.println("SQL: " + sql);
// 如果有 BATCH_COUNT 条记录时，则执行批量一次
if (index % BATCH_COUNT == 0) {
count++;
System.out.println("每 " + BATCH_COUNT + " 条批量执行一次");
// 批量执行
statement.executeBatch();
connection.commit();
// 一把清空
statement.clearBatch();
}
}
// 总条数不是批量数值的整数倍需要再额外的执行一次
if (sqlList.size() % BATCH_COUNT != 0) {
System.out.println("剩余未执行 " + (sqlList.size() - count * BATCH_COUNT) + " 条批量执行一次");
// 批量执行
statement.executeBatch();
connection.commit();
// 一把清空
statement.clearBatch();
}
long end = System.currentTimeMillis();
System.out.println("批处理 SQL 语句执行耗时: " + (end - begin) + " ms");
} catch (Exception e) {
try {
connection.rollback();
connection.setAutoCommit(true);
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
connection.setAutoCommit(true);
if (null != statement) {
statement.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 根据sql获取结果
*
* @param rs ResultSet
* @return 查询结果
*/
public static ArrayList<LinkedHashMap<String, String>> getDataResultByResultSet(ResultSet rs) {
ArrayList<LinkedHashMap<String, String>> result = new ArrayList<>();
List<String> field = new ArrayList<>();
try {
ResultSetMetaData resultSetMetaData = rs.getMetaData();
// 获取SQL查询结果的列名，下标从1开始
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
field.add(resultSetMetaData.getColumnLabel(i));
}
while (rs.next()) {
LinkedHashMap<String, String> data = new LinkedHashMap<>();
for (String f : field) {
data.put(f, rs.getString(f));
}
result.add(data);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
三、测试
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.UUID;
public class Main {
public static void main(String[] args) {
// TODO 自动生成的方法存根
// 格式为：jdbc:oracle:thin:@IP地址:1521:数据库SID
String dburl = "jdbc:oracle:thin:@192.168.100.123:1521:ORCL";
String username = "wxhntmy";
String password = "CWCcwy12";
Connection connection = OJdbcUtil.getDataSource(dburl, username, password);
String sql = "SELECT * FROM \r\n"
+ "( \r\n"
+ "SELECT A.*, ROWNUM RN \r\n"
+ "FROM (SELECT * FROM DISCOUNTS \r\n"
+ " WHERE 1 = 1 \r\n"
+ " ORDER BY discount_id DESC \r\n"
+ " ) A \r\n"
+ " WHERE ROWNUM <= 33 \r\n"
+ " ) \r\n"
+ "WHERE RN > 30";
sql = sql.replace("\r\n", "");
ArrayList<LinkedHashMap<String, String>> list = OJdbcUtil.getSelectSqlResult(connection, sql);
for (LinkedHashMap<String, String> linkedHashMap : list) {
System.out.println(linkedHashMap);
}
Map<String, Object> map = OJdbcUtil.execSql(connection, sql);
if (map.containsKey("status") && (boolean)map.getOrDefault("status", false)
&& "select".equals(map.getOrDefault("type", ""))) {
@SuppressWarnings("unchecked")
ArrayList<LinkedHashMap<String, String>> dataList = (ArrayList<LinkedHashMap<String, String>>) map.get("data");
for (LinkedHashMap<String, String> linkedHashMap : dataList) {
System.out.println(linkedHashMap);
}
}
List<String> sqlList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
//特别特别注意！！！！！！！！！！！！！！！！
//最末尾不用写分号，留一个空格就行
sqlList.add("INSERT INTO DISCOUNTS(discount_name, amount, start_date, expired_date) " + "VALUES('"
+ UUID.randomUUID().toString() + "', " + new Random().nextInt(9) + ".5, DATE '"
+ new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + "', SYSDATE) ");
}
OJdbcUtil.updateBatch(connection, sqlList);
try {
if (null != connection) {
connection.close();
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
