org.redkale.source.DataJdbcSource Maven / Gradle / Ivy
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package org.redkale.source;
import java.io.Serializable;
import java.net.URL;
import java.sql.*;
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.atomic.*;
import java.util.function.*;
import java.util.logging.Level;
import org.redkale.service.Local;
import org.redkale.util.*;
/**
* DataSource的JDBC实现类
*
*
* 详情见: https://redkale.org
*
* @author zhangjx
*/
@Local
@AutoLoad(false)
@SuppressWarnings("unchecked")
@ResourceType(DataSource.class)
public class DataJdbcSource extends DataSqlSource {
public DataJdbcSource(String unitName, URL persistxml, Properties readprop, Properties writeprop) {
super(unitName, persistxml, readprop, writeprop);
}
@Override
protected final String prepareParamSign(int index) {
return "?";
}
@Override
protected final boolean isAsync() {
return false;
}
@Override
protected PoolSource createPoolSource(DataSource source, String rwtype, ArrayBlockingQueue queue, Semaphore semaphore, Properties prop) {
return new PoolJdbcSource(this.name, this.persistxml, rwtype, queue, semaphore, prop, this.logger);
}
@Override
protected CompletableFuture insertDB(EntityInfo info, T... values) {
Connection conn = null;
try {
int c = 0;
conn = writePool.poll();
final String sql = info.getInsertPrepareSQL(values[0]);
final Class primaryType = info.getPrimary().type();
final Attribute primary = info.getPrimary();
Attribute[] attrs = info.insertAttributes;
conn.setReadOnly(false);
conn.setAutoCommit(true);
PreparedStatement prestmt = createInsertPreparedStatement(conn, sql, info, values);
try {
int[] cs = prestmt.executeBatch();
int c1 = 0;
for (int cc : cs) {
c1 += cc;
}
c = c1;
} catch (SQLException se) {
if (info.tableStrategy == null || !info.isTableNotExist(se)) throw se;
synchronized (info.tables) {
final String oldTable = info.table;
final String newTable = info.getTable(values[0]);
if (!info.tables.contains(newTable)) {
try {
Statement st = conn.createStatement();
st.execute(info.tablecopySQL.replace("${newtable}", newTable).replace("${oldtable}", oldTable));
st.close();
info.tables.add(newTable);
} catch (SQLException sqle) { //多进程并发时可能会出现重复建表
if (newTable.indexOf('.') > 0 && info.isTableNotExist(se)) {
Statement st;
try {
st = conn.createStatement();
st.execute("CREATE DATABASE " + newTable.substring(0, newTable.indexOf('.')));
st.close();
} catch (SQLException sqle1) {
logger.log(Level.SEVERE, "create database(" + newTable.substring(0, newTable.indexOf('.')) + ") error", sqle1);
}
try {
st = conn.createStatement();
st.execute(info.tablecopySQL.replace("${newtable}", newTable).replace("${oldtable}", oldTable));
st.close();
info.tables.add(newTable);
} catch (SQLException sqle2) {
logger.log(Level.SEVERE, "create table2(" + info.tablecopySQL.replace("${newtable}", newTable).replace("${oldtable}", oldTable) + ") error", sqle2);
}
} else {
logger.log(Level.SEVERE, "create table(" + info.tablecopySQL.replace("${newtable}", newTable).replace("${oldtable}", oldTable) + ") error", sqle);
}
}
}
}
prestmt.close();
prestmt = createInsertPreparedStatement(conn, sql, info, values);
int[] cs = prestmt.executeBatch();
int c1 = 0;
for (int cc : cs) {
c1 += cc;
}
c = c1;
}
if (info.autoGenerated) { //由数据库自动生成主键值
ResultSet set = prestmt.getGeneratedKeys();
int i = -1;
while (set.next()) {
if (primaryType == int.class) {
primary.set(values[++i], set.getInt(1));
} else if (primaryType == long.class) {
primary.set(values[++i], set.getLong(1));
} else {
primary.set(values[++i], set.getObject(1));
}
}
set.close();
}
prestmt.close();
//------------------------------------------------------------
if (info.isLoggable(logger, Level.FINEST)) { //打印调试信息
char[] sqlchars = sql.toCharArray();
for (final T value : values) {
//-----------------------------
StringBuilder sb = new StringBuilder(128);
int i = 0;
for (char ch : sqlchars) {
if (ch == '?') {
Object obj = attrs[i++].get(value);
if (obj != null && obj.getClass().isArray()) {
sb.append("'[length=").append(java.lang.reflect.Array.getLength(obj)).append("]'");
} else {
sb.append(FilterNode.formatToString(obj));
}
} else {
sb.append(ch);
}
}
String debugsql = sb.toString();
if (info.isLoggable(logger, Level.FINEST, debugsql)) logger.finest(info.getType().getSimpleName() + " insert sql=" + debugsql.replaceAll("(\r|\n)", "\\n"));
}
} //打印结束
return CompletableFuture.completedFuture(c);
} catch (SQLException e) {
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) writePool.offerConnection(conn);
}
}
protected PreparedStatement createInsertPreparedStatement(final Connection conn, final String sql,
final EntityInfo info, T... values) throws SQLException {
Attribute[] attrs = info.insertAttributes;
final PreparedStatement prestmt = info.autoGenerated ? conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) : conn.prepareStatement(sql);
for (final T value : values) {
int i = 0;
if (info.autouuid) info.createPrimaryValue(value);
for (Attribute attr : attrs) {
Serializable val = attr.get(value);
if (val instanceof byte[]) {
Blob blob = conn.createBlob();
blob.setBytes(1, (byte[]) val);
prestmt.setObject(++i, blob);
} else if (val instanceof AtomicInteger) {
prestmt.setObject(++i, ((AtomicInteger) val).get());
} else if (val instanceof AtomicLong) {
prestmt.setObject(++i, ((AtomicLong) val).get());
} else {
prestmt.setObject(++i, val);
}
}
prestmt.addBatch();
}
return prestmt;
}
@Override
protected CompletableFuture deleteDB(EntityInfo info, Flipper flipper, String sql) {
Connection conn = null;
try {
conn = writePool.poll();
conn.setReadOnly(false);
conn.setAutoCommit(true);
sql += ((flipper == null || flipper.getLimit() < 1) ? "" : (" LIMIT " + flipper.getLimit()));
if (info.isLoggable(logger, Level.FINEST, sql)) logger.finest(info.getType().getSimpleName() + " delete sql=" + sql);
final Statement stmt = conn.createStatement();
int c = stmt.executeUpdate(sql);
stmt.close();
return CompletableFuture.completedFuture(c);
} catch (SQLException e) {
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) writePool.offerConnection(conn);
}
}
@Override
protected CompletableFuture updateDB(EntityInfo info, T... values) {
Connection conn = null;
try {
conn = writePool.poll();
conn.setReadOnly(false);
conn.setAutoCommit(true);
final String updateSQL = info.getUpdatePrepareSQL(values[0]);
final PreparedStatement prestmt = conn.prepareStatement(updateSQL);
Attribute[] attrs = info.updateAttributes;
final boolean debugfinest = info.isLoggable(logger, Level.FINEST);
char[] sqlchars = debugfinest ? updateSQL.toCharArray() : null;
final Attribute primary = info.getPrimary();
for (final T value : values) {
int k = 0;
for (Attribute attr : attrs) {
Serializable val = attr.get(value);
if (val instanceof byte[]) {
Blob blob = conn.createBlob();
blob.setBytes(1, (byte[]) val);
prestmt.setObject(++k, blob);
} else if (val instanceof AtomicInteger) {
prestmt.setObject(++k, ((AtomicInteger) val).get());
} else if (val instanceof AtomicLong) {
prestmt.setObject(++k, ((AtomicLong) val).get());
} else {
prestmt.setObject(++k, val);
}
}
prestmt.setObject(++k, primary.get(value));
prestmt.addBatch();//------------------------------------------------------------
if (debugfinest) { //打印调试信息
//-----------------------------
int i = 0;
StringBuilder sb = new StringBuilder(128);
for (char ch : sqlchars) {
if (ch == '?') {
Object obj = i == attrs.length ? primary.get(value) : attrs[i++].get(value);
if (obj != null && obj.getClass().isArray()) {
sb.append("'[length=").append(java.lang.reflect.Array.getLength(obj)).append("]'");
} else {
sb.append(FilterNode.formatToString(obj));
}
} else {
sb.append(ch);
}
}
String debugsql = sb.toString();
if (info.isLoggable(logger, Level.FINEST, debugsql)) logger.finest(info.getType().getSimpleName() + " update sql=" + debugsql.replaceAll("(\r|\n)", "\\n"));
} //打印结束
}
int[] pc = prestmt.executeBatch();
int c = 0;
for (int p : pc) {
if (p >= 0) c += p;
}
prestmt.close();
return CompletableFuture.completedFuture(c);
} catch (SQLException e) {
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) writePool.offerConnection(conn);
}
}
@Override
protected CompletableFuture updateDB(EntityInfo info, Flipper flipper, String sql, boolean prepared, Object... params) {
Connection conn = null;
try {
conn = writePool.poll();
conn.setReadOnly(false);
conn.setAutoCommit(true);
if (prepared) {
final PreparedStatement prestmt = conn.prepareStatement(sql);
int index = 0;
for (Object param : params) {
Blob blob = conn.createBlob();
blob.setBytes(1, (byte[]) param);
prestmt.setBlob(++index, blob);
}
int c = prestmt.executeUpdate();
prestmt.close();
return CompletableFuture.completedFuture(c);
} else {
if (info.isLoggable(logger, Level.FINEST, sql)) logger.finest(info.getType().getSimpleName() + " update sql=" + sql);
final Statement stmt = conn.createStatement();
int c = stmt.executeUpdate(sql);
stmt.close();
return CompletableFuture.completedFuture(c);
}
} catch (SQLException e) {
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) writePool.offerConnection(conn);
}
}
@Override
protected CompletableFuture> getNumberMapDB(EntityInfo info, String sql, FilterFuncColumn... columns) {
Connection conn = null;
try {
conn = readPool.poll();
conn.setReadOnly(true);
final Statement stmt = conn.createStatement();
ResultSet set = stmt.executeQuery(sql);
final Map map = new HashMap<>();
if (set.next()) {
int index = 0;
for (FilterFuncColumn ffc : columns) {
for (String col : ffc.cols()) {
Object o = set.getObject(++index);
Number rs = ffc.getDefvalue();
if (o != null) rs = (Number) o;
map.put(ffc.col(col), rs);
}
}
}
set.close();
stmt.close();
return CompletableFuture.completedFuture(map);
} catch (SQLException e) {
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
@Override
protected CompletableFuture getNumberResultDB(EntityInfo info, String sql, Number defVal, String column) {
Connection conn = null;
try {
conn = readPool.poll();
conn.setReadOnly(true);
final Statement stmt = conn.createStatement();
Number rs = defVal;
ResultSet set = stmt.executeQuery(sql);
if (set.next()) {
Object o = set.getObject(1);
if (o != null) rs = (Number) o;
}
set.close();
stmt.close();
return CompletableFuture.completedFuture(rs);
} catch (SQLException e) {
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
@Override
protected CompletableFuture> queryColumnMapDB(EntityInfo info, String sql, String keyColumn) {
Connection conn = null;
try {
conn = readPool.poll();
conn.setReadOnly(true);
final Statement stmt = conn.createStatement();
Map rs = new LinkedHashMap<>();
ResultSet set = stmt.executeQuery(sql);
ResultSetMetaData rsd = set.getMetaData();
boolean smallint = rsd == null ? false : rsd.getColumnType(1) == Types.SMALLINT;
while (set.next()) {
rs.put((K) (smallint ? set.getShort(1) : set.getObject(1)), (N) set.getObject(2));
}
set.close();
stmt.close();
return CompletableFuture.completedFuture(rs);
} catch (SQLException e) {
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
@Override
protected CompletableFuture findDB(EntityInfo info, String sql, boolean onlypk, SelectColumn selects) {
Connection conn = null;
try {
conn = readPool.poll();
conn.setReadOnly(true);
final PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(1);
final ResultSet set = ps.executeQuery();
T rs = set.next() ? info.getValue(selects, set) : null;
set.close();
ps.close();
return CompletableFuture.completedFuture(rs);
} catch (SQLException e) {
if (info.tableStrategy != null && info.isTableNotExist(e)) return CompletableFuture.completedFuture(null);
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
@Override
protected CompletableFuture findColumnDB(EntityInfo info, String sql, boolean onlypk, String column, Serializable defValue) {
Connection conn = null;
try {
conn = readPool.poll();
conn.setReadOnly(true);
final Attribute attr = info.getAttribute(column);
final PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(1);
final ResultSet set = ps.executeQuery();
Serializable val = defValue;
if (set.next()) {
if (attr.type() == byte[].class) {
Blob blob = set.getBlob(1);
if (blob != null) val = blob.getBytes(1, (int) blob.length());
} else {
val = (Serializable) set.getObject(1);
}
}
set.close();
ps.close();
return CompletableFuture.completedFuture(val == null ? defValue : val);
} catch (SQLException e) {
if (info.tableStrategy != null && info.isTableNotExist(e)) return CompletableFuture.completedFuture(defValue);
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
@Override
protected CompletableFuture existsDB(EntityInfo info, String sql, boolean onlypk) {
Connection conn = null;
try {
conn = readPool.poll();
conn.setReadOnly(true);
final PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
final ResultSet set = ps.executeQuery();
boolean rs = set.next() ? (set.getInt(1) > 0) : false;
set.close();
ps.close();
if (info.isLoggable(logger, Level.FINEST, sql)) logger.finest(info.getType().getSimpleName() + " exists (" + rs + ") sql=" + sql);
return CompletableFuture.completedFuture(rs);
} catch (SQLException e) {
if (info.tableStrategy != null && info.isTableNotExist(e)) return CompletableFuture.completedFuture(false);
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
@Override
protected CompletableFuture> querySheetDB(EntityInfo info, boolean needtotal, SelectColumn selects, Flipper flipper, FilterNode node) {
Connection conn = null;
try {
conn = readPool.poll();
conn.setReadOnly(true);
final SelectColumn sels = selects;
final List list = new ArrayList();
final Map joinTabalis = node == null ? null : node.getJoinTabalis();
final CharSequence join = node == null ? null : node.createSQLJoin(this, false, joinTabalis, new HashSet<>(), info);
final CharSequence where = node == null ? null : node.createSQLExpress(info, joinTabalis);
final String dbtype = this.readPool.getDbtype();
if ("mysql".equals(dbtype) || "postgresql".equals(dbtype)) {
final String listsql = "SELECT " + info.getQueryColumns("a", selects) + " FROM " + info.getTable(node) + " a" + (join == null ? "" : join)
+ ((where == null || where.length() == 0) ? "" : (" WHERE " + where)) + createSQLOrderby(info, flipper) + (flipper == null || flipper.getLimit() < 1 ? "" : (" LIMIT " + flipper.getLimit() + " OFFSET " + flipper.getOffset()));
if (info.isLoggable(logger, Level.FINEST, listsql)) {
logger.finest(info.getType().getSimpleName() + " query sql=" + listsql);
}
PreparedStatement ps = conn.prepareStatement(listsql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet set = ps.executeQuery();
while (set.next()) {
list.add(infoGetValue(info, sels, set));
}
set.close();
ps.close();
long total = list.size();
if (needtotal) {
final String countsql = "SELECT COUNT(*) FROM " + info.getTable(node) + " a" + (join == null ? "" : join) + ((where == null || where.length() == 0) ? "" : (" WHERE " + where));
if (info.isLoggable(logger, Level.FINEST, countsql)) {
logger.finest(info.getType().getSimpleName() + " query countsql=" + countsql);
}
ps = conn.prepareStatement(countsql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
set = ps.executeQuery();
if (set.next()) total = set.getLong(1);
set.close();
ps.close();
}
return CompletableFuture.completedFuture(new Sheet<>(total, list));
}
final String sql = "SELECT " + info.getQueryColumns("a", selects) + " FROM " + info.getTable(node) + " a" + (join == null ? "" : join)
+ ((where == null || where.length() == 0) ? "" : (" WHERE " + where)) + info.createSQLOrderby(flipper);
if (info.isLoggable(logger, Level.FINEST, sql)) {
logger.finest(info.getType().getSimpleName() + " query sql=" + sql + (flipper == null || flipper.getLimit() < 1 ? "" : (" LIMIT " + flipper.getLimit() + " OFFSET " + flipper.getOffset())));
}
conn.setReadOnly(true);
final PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
if (flipper != null && flipper.getLimit() > 0) ps.setFetchSize(flipper.getLimit());
final ResultSet set = ps.executeQuery();
if (flipper != null && flipper.getOffset() > 0) set.absolute(flipper.getOffset());
final int limit = flipper == null || flipper.getLimit() < 1 ? Integer.MAX_VALUE : flipper.getLimit();
int i = 0;
while (set.next()) {
i++;
list.add(info.getValue(sels, set));
if (limit <= i) break;
}
long total = list.size();
if (needtotal && flipper != null) {
set.last();
total = set.getRow();
}
set.close();
ps.close();
return CompletableFuture.completedFuture(new Sheet<>(total, list));
} catch (SQLException e) {
if (info.tableStrategy != null && info.isTableNotExist(e)) return CompletableFuture.completedFuture(new Sheet<>());
CompletableFuture future = new CompletableFuture();
future.completeExceptionally(e);
return future;
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
/**
* 直接本地执行SQL语句进行增删改操作，远程模式不可用
* 通常用于复杂的更新操作
*
* @param sql SQL语句
*
* @return 结果数组
*/
@Local
@Override
public int directExecute(String sql) {
return directExecute(new String[]{sql})[0];
}
/**
* 直接本地执行SQL语句进行增删改操作，远程模式不可用
* 通常用于复杂的更新操作
*
* @param sqls SQL语句
*
* @return 结果数组
*/
@Local
@Override
public int[] directExecute(String... sqls) {
if (sqls.length == 0) return new int[0];
Connection conn = writePool.poll();
try {
conn.setReadOnly(false);
final Statement stmt = conn.createStatement();
final int[] rs = new int[sqls.length];
int i = -1;
for (String sql : sqls) {
rs[++i] = stmt.execute(sql) ? 1 : 0;
}
stmt.close();
return rs;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (conn != null) writePool.offerConnection(conn);
}
}
/**
* 直接本地执行SQL语句进行查询，远程模式不可用
* 通常用于复杂的关联查询
*
* @param 泛型
* @param sql SQL语句
* @param handler 回调函数
*
* @return 结果
*/
@Local
@Override
public V directQuery(String sql, Function handler) {
final Connection conn = readPool.poll();
try {
if (logger.isLoggable(Level.FINEST)) logger.finest("direct query sql=" + sql);
conn.setReadOnly(true);
final Statement statement = conn.createStatement();
//final PreparedStatement statement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
final ResultSet set = statement.executeQuery(sql);// ps.executeQuery();
V rs = handler.apply(set);
set.close();
statement.close();
return rs;
} catch (Exception ex) {
throw new RuntimeException(ex);
} finally {
if (conn != null) readPool.offerConnection(conn);
}
}
}