cq.database.helper.DBHelper Maven / Gradle / Ivy
The newest version!
package cq.database.helper;
import com.sun.rowset.CachedRowSetImpl;
import cq.database.exception.DBException;
import cq.database.util.ThisUtil;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SuppressWarnings("restriction")
public class DBHelper {
private Connection connection;
public Connection getConnection() {
return connection;
}
/**
* 配置,连接数据库
*
* @param config 数据库配置接口
* @see DBConfig
*/
public DBHelper(DBConfig config) {
try {
Class.forName(config.driver());
connection = DriverManager.getConnection(config.url(), config.user(), config.password());
} catch (ClassNotFoundException | SQLException e) {
throw new DBException(e);
}
}
/**
* DQL
* 获取未处理结果集
*
* @param sql 查询语句
*/
private CachedRowSetImpl doDQL(String sql) {
return process(() -> {
PreparedStatement pstmt = null;
ResultSet resultSet = null;
try {
pstmt = connection.prepareStatement(sql);
resultSet = pstmt.executeQuery();
CachedRowSetImpl rowSet = new CachedRowSetImpl();
rowSet.populate(resultSet);
return rowSet;
} catch (SQLException e) {
throw new DBException(e);
} finally {
close(pstmt, resultSet);
}
});
}
/**
* 根据类型解析结果
*
* @param sql
* @param resultType 结果类型
* @param
* @return
*/
public List getResultList(String sql, Class resultType) {
return process(() -> {
CachedRowSetImpl rs = doDQL(sql);
List list = new ArrayList<>();
if (canCast(resultType)) {
try {
while (rs.next()) {
T result;
try {
result = ThisUtil.objCast(rs.getObject(1), resultType);
} catch (SQLException e) {
e.printStackTrace();
result = null;
}
list.add(result);
}
} catch (SQLException e) {
throw new DBException(e);
} finally {
close(rs);
}
} else {
try {
while (rs.next()) {
list.add(parseResult(rs, resultType));
}
} catch (Exception e) {
throw new DBException(e);
} finally {
close(rs);
}
}
return list;
});
}
public T getResult(String sql, Class resultType) {
return process(() -> {
CachedRowSetImpl rs = doDQL(sql);
T result = null;
if (canCast(resultType)) {
try {
if (rs.next()) {
result = ThisUtil.objCast(rs.getObject(1), resultType);
}
} catch (SQLException e) {
throw new DBException(e);
} finally {
close(rs);
}
} else {
try {
if (rs.next()) {
result = parseResult(rs, resultType);
}
} catch (Exception e) {
throw new DBException(e);
} finally {
close(rs);
}
}
return result;
});
}
private T parseResult(CachedRowSetImpl rs, Class clazz) throws IllegalAccessException, InstantiationException {
T result = (T) clazz.newInstance();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
Object value;
try {
value = rs.getObject(field.getName());
} catch (Exception e) {
value = null;
}
if (value != null) {
field.set(result, ThisUtil.objCast(value, field.getType()));
}
}
return result;
}
private boolean canCast(Class clazz) {
return Number.class.isAssignableFrom(clazz) || clazz == Boolean.class || clazz == BigDecimal.class || clazz == Date.class || clazz == String.class;
}
/**
* 获取指定字段结果
*
* @param sql 查询语句
* @param fields 指定字段
*/
public List> getResultList(String sql, String... fields) {
return process(() -> {
List> results = new ArrayList<>();
HashMap map;
if (fields.length > 0) {
CachedRowSetImpl rs = doDQL(sql);
if (rs != null) {
try {
while (rs.next()) {
map = new HashMap<>();
for (String key : fields) {
map.put(key, rs.getObject(key));
}
results.add(map);
}
} catch (SQLException e) {
throw new DBException(e);
} finally {
close(rs);
}
}
}
return results;
});
}
public Map getResult(String sql, String... fields) {
return process(() -> {
Map map = new HashMap<>();
if (fields.length > 0) {
CachedRowSetImpl rs = doDQL(sql);
if (rs != null) {
try {
if (rs.next()) {
for (String key : fields) {
map.put(key, rs.getObject(key));
}
}
} catch (SQLException e) {
throw new DBException(e);
} finally {
close(rs);
}
}
}
return map;
});
}
/**
* DML操作
*
* @param sql
*/
public int doDML(String sql) {
return process(() -> {
PreparedStatement pstmt = null;
try {
pstmt = connection.prepareStatement(sql);
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new DBException(e);
} finally {
close(pstmt);
}
});
}
public void refresh() {
close(doDQL("select 1 from dual"));
}
public void setAutoCommit(boolean auto) {
try {
connection.setAutoCommit(auto);
} catch (SQLException e) {
throw new DBException(e);
}
}
public void commit() {
try {
connection.commit();
} catch (SQLException e) {
throw new DBException(e);
}
}
public void rollback() {
try {
connection.rollback();
} catch (SQLException e) {
throw new DBException(e);
}
}
private void close(ResultSet rs) {
close(null, rs);
}
private void close(Statement pstmt) {
close(pstmt, null);
}
private void close(Statement pstmt, ResultSet rs) {
try {
if (null != rs) {
rs.close();
}
if (null != pstmt) {
pstmt.close();
}
} catch (SQLException e) {
throw new DBException(e);
}
}
public void close() {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new DBException(e);
}
}
}
/**
* 语句执行过程
*/
private T process(Process p) {
T t;
if (null != connection) {
t = p.execute();
} else {
throw new DBException("Connection unavailable");
}
return t;
}
/**
* 过程接口
*/
private interface Process {
T execute();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy