com.jdon.util.DbUtil Maven / Gradle / Ivy
package com.jdon.util;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
public class DbUtil {
public final static String ORACLE = "oracle";
public final static String MYSQL = "mysql";
public final static String SQLSERVER = "sql server";
public final static String INTERBASE = "interbase";
public final static String POSTGRES = "postgres";
private static boolean init = false;
// True if the database requires large text fields to be streamed.
public static boolean streamLargeText;
// True if the database supports the Statement.setMaxRows() method.
public static boolean supportsMaxRows;
// True if the database supports the Statement.setFetchSize() method.
public static boolean supportsFetchSize;
// True if the database supports correlated subqueries.
public static void testConnection(Connection con) {
if (init)
return;
init = true;
// Set defaults for other meta properties
streamLargeText = false;
supportsMaxRows = true;
supportsFetchSize = true;
try {
DatabaseMetaData metaData = con.getMetaData();
// Supports transactions?
// Get the database name so that we can perform meta data settings.
String dbName = metaData.getDatabaseProductName().toLowerCase();
String driverName = metaData.getDriverName().toLowerCase();
// Oracle properties.
if (dbName.indexOf(ORACLE) != -1) {
streamLargeText = true;
// The i-net AUGURO JDBC driver
if (driverName.indexOf("auguro") != -1) {
streamLargeText = false;
supportsFetchSize = true;
supportsMaxRows = false;
}
}
// Postgres properties
else if (dbName.indexOf(POSTGRES) != -1) {
supportsFetchSize = false;
}
// Interbase properties
else if (dbName.indexOf(INTERBASE) != -1) {
supportsFetchSize = false;
supportsMaxRows = false;
}
// SQLServer, JDBC driver i-net UNA properties
else if (dbName.indexOf(SQLSERVER) != -1 &&
driverName.indexOf("una") != -1) {
supportsFetchSize = true;
supportsMaxRows = false;
}
// MySQL properties
else if (dbName.indexOf(MYSQL) != -1) {
}
} catch (Exception ex) {
}
}
public static int getProductAllCount(DataSource ds, String key, String sql_allcount) throws Exception {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
int ret = 0;
try {
c = ds.getConnection();
ps = c.prepareStatement(sql_allcount,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, key);
rs = ps.executeQuery();
if (rs.first()) {
ret = rs.getInt(1);
}
} catch (SQLException se) {
throw new Exception("SQLException: " + se.getMessage());
} finally {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (c != null)
c.close();
}
return ret;
}
//该方法不能返回hasNext，待完善！
public List getDataList(DataSource ds, String key, String sql, int start, int count) throws
Exception {
Connection c = null;
Statement stmt = null;
ResultSet rs = null;
List items = new ArrayList(count);
try {
c = ds.getConnection();
stmt = c.createStatement();
DbUtil.testConnection(c);
if (DbUtil.supportsMaxRows)
stmt.setMaxRows(count + start + 1);
rs = stmt.executeQuery(sql);
if (DbUtil.supportsFetchSize)
rs.setFetchSize(count);
if (start >= 0 && rs.absolute(start + 1)) {
do {
items.add(rs.getString(1));
}
while ( (rs.next()) && (--count > 0));
}
} catch (SQLException se) {
throw new Exception("SQLException: " + se.getMessage());
} finally {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (c != null)
c.close();
}
return items;
}
}