com.fastchar.database.FastDb Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of FastChar Show documentation
Show all versions of FastChar Show documentation
FastChar is Web+ORM Framework in Java
Copyright (C) http://www.fastchar.com
All rights reserved.
The newest version!
package com.fastchar.database;
import com.fastchar.core.FastChar;
import com.fastchar.core.FastEntity;
import com.fastchar.database.info.FastDatabaseInfo;
import com.fastchar.database.info.FastSqlInfo;
import com.fastchar.exception.FastSqlException;
import com.fastchar.interfaces.IFastCacheProvider;
import com.fastchar.utils.FastArrayUtils;
import com.fastchar.utils.FastStringUtils;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
/**
* 数据库sql操作
*/
public class FastDb {
private static final ThreadLocal TRANSACTION_THREAD_LOCAL = new ThreadLocal();
public static boolean isTransaction() {
FastTransaction fastTransaction = TRANSACTION_THREAD_LOCAL.get();
return fastTransaction != null && fastTransaction.isValid();
}
public static FastTransaction doTransaction() {
FastTransaction fastTransaction = new FastTransaction();
TRANSACTION_THREAD_LOCAL.set(fastTransaction);
return fastTransaction;
}
private boolean log = true;
private boolean useCache = true;
private String database;
private FastDatabaseInfo getDatabaseInfo() {
return FastChar.getDatabases().get(database);
}
public Connection getConnection() throws Exception {
FastDatabaseInfo databaseInfo = getDatabaseInfo();
DataSource dataSource = databaseInfo.getDataSource();
if (dataSource == null) {
return null;
}
FastTransaction fastTransaction = TRANSACTION_THREAD_LOCAL.get();
if (fastTransaction != null) {
if (fastTransaction.isValid()) {
if (!fastTransaction.contains(databaseInfo.getName())) {
fastTransaction.setConnection(databaseInfo.getName(), dataSource.getConnection());
}
return fastTransaction.getConnection(databaseInfo.getName());
}
TRANSACTION_THREAD_LOCAL.remove();
}
return dataSource.getConnection();
}
private String buildCacheKeyBySql(String sqlStr, Object... params) {
FastDatabaseInfo fastDatabaseInfo = getDatabaseInfo();
return FastChar.getSecurity().MD5_Encrypt(fastDatabaseInfo.getName() + sqlStr + Arrays.toString(params));
}
private String buildCacheTag(String... tableNames) {
FastDatabaseInfo fastDatabaseInfo = getDatabaseInfo();
for (int i = 0; i < tableNames.length; i++) {
tableNames[i] = fastDatabaseInfo.getName() + "@" + tableNames[i];
}
return "@" + FastStringUtils.join(tableNames, "@") + "@";
}
private String buildCacheTagBySql(String sqlStr) {
return buildCacheTag(getDatabaseInfo().getAllTables(sqlStr).toArray(new String[]{}));
}
/**
* 执行查询（带参数）
*
* @param sqlStr
* @param params
* @return
*/
public List> select(String sqlStr, Object... params) throws Exception {
String cacheKey = buildCacheKeyBySql(sqlStr, params);
String cacheTag = buildCacheTagBySql(sqlStr);
if (getDatabaseInfo().isCache() && isUseCache()) {
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
if (iFastCacheProvider.exists(cacheTag, cacheKey)) {
List> cache = iFastCacheProvider.getCache(cacheTag, cacheKey);
if (cache != null) {
System.out.println(color("Cached ：" + sqlStr));
return cache;
}
}
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
if (connection == null) {
return null;
}
preparedStatement = connection.prepareStatement(sqlStr);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
Object value;
if (i < params.length) {
value = params[i];
} else {
throw new FastSqlException(FastChar.getLocal().getInfo("Db_Sql_Error1"));
}
if (value != null) {
preparedStatement.setObject(i + 1, value);
} else {
preparedStatement.setNull(i + 1, Types.NULL);
}
}
resultSet = preparedStatement.executeQuery();
List> listResult = new FastResultSet(resultSet).getListResult();
if (getDatabaseInfo().isCache() && isUseCache()) {
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
iFastCacheProvider.setCache(cacheTag, cacheKey, listResult);
}
return listResult;
} finally {
close(connection, preparedStatement, resultSet);
log(sqlStr, params);
}
}
public FastPage> select(int page, int pageSize, String sqlStr, Object... params)throws Exception {
FastPage> fastPage = new FastPage<>();
String type = getDatabaseInfo().getType();
if (page > 0) {
String countSql = FastSql.newInstance(type).getCountSql(sqlStr, "ct");
FastEntity countResult = selectFirst(countSql, params);
int countRow = countResult.getInt("ct");
fastPage.setTotalRow(countRow);
fastPage.setPage(page);
fastPage.setPageSize(pageSize);
fastPage.setTotalPage(countRow % pageSize == 0 ? countRow / pageSize : countRow / pageSize + 1);
}
String pageSql = FastSql.newInstance(type).getPageSql(sqlStr, page, pageSize);
fastPage.setList(select(pageSql, params));
return fastPage;
}
/**
* 执行查询（带参数）
*
* @param sqlStr
* @param params
* @return
*/
public FastEntity selectFirst(String sqlStr, Object... params) throws Exception {
String cacheKey = buildCacheKeyBySql(sqlStr, params);
String cacheTag = buildCacheTagBySql(sqlStr);
if (getDatabaseInfo().isCache() && isUseCache()) {
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
if (iFastCacheProvider.exists(cacheTag, cacheKey)) {
FastEntity cache = iFastCacheProvider.getCache(cacheTag, cacheKey);
if (cache != null) {
System.out.println(color("Cached ：" + sqlStr));
return cache;
}
}
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
if (connection == null) {
return null;
}
preparedStatement = connection.prepareStatement(sqlStr);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
Object value = null;
if (i < params.length) {
value = params[i];
} else {
throw new FastSqlException(FastChar.getLocal().getInfo("Db_Sql_Error1"));
}
if (value != null) {
preparedStatement.setObject(i + 1, value);
} else {
preparedStatement.setNull(i + 1, Types.NULL);
}
}
resultSet = preparedStatement.executeQuery();
FastEntity firstResult = new FastResultSet(resultSet).getFirstResult();
if (getDatabaseInfo().isCache() && isUseCache()) {
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
iFastCacheProvider.setCache(cacheTag, cacheKey, firstResult);
}
return firstResult;
} finally {
close(connection, preparedStatement, resultSet);
log(sqlStr, params);
}
}
/**
* 执行查询（带参数）
*
* @param sqlStr
* @param params
* @return
*/
public FastEntity selectLast(String sqlStr, Object... params) throws Exception {
String cacheKey = buildCacheKeyBySql(sqlStr, params);
String cacheTag = buildCacheTagBySql(sqlStr);
if (getDatabaseInfo().isCache() && isUseCache()) {
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
if (iFastCacheProvider.exists(cacheTag, cacheKey)) {
FastEntity cache = iFastCacheProvider.getCache(cacheTag, cacheKey);
if (cache != null) {
System.out.println(color("Cached ：" + sqlStr));
return cache;
}
}
}
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
if (connection == null) {
return null;
}
preparedStatement = connection.prepareStatement(sqlStr);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
Object value = null;
if (i < params.length) {
value = params[i];
} else {
throw new FastSqlException(FastChar.getLocal().getInfo("Db_Sql_Error1"));
}
if (value != null) {
preparedStatement.setObject(i + 1, value);
} else {
preparedStatement.setNull(i + 1, Types.NULL);
}
}
resultSet = preparedStatement.executeQuery();
FastEntity lastResult = new FastResultSet(resultSet).getLastResult();
if (getDatabaseInfo().isCache() && isUseCache()) {
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
iFastCacheProvider.setCache(cacheTag, cacheKey, lastResult);
}
return lastResult;
} finally {
close(connection, preparedStatement, resultSet);
log(sqlStr, params);
}
}
/**
* 执行更新sql
*
* @param sqlStr
* @param params
* @return 更新成功的数量
*/
public int update(String sqlStr, Object... params) throws Exception {
if (getDatabaseInfo().isCache() && isUseCache()) {
List allTables = getDatabaseInfo().getAllTables(sqlStr);
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
for (String table : allTables) {
Set tags = iFastCacheProvider.getTags("*" + buildCacheTag(table) + "*");
for (String tag : tags) {
iFastCacheProvider.deleteCache(tag);
}
}
}
int count = 0;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
if (connection == null) {
return -1;
}
preparedStatement = connection.prepareStatement(sqlStr);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
Object value;
if (i < params.length) {
value = params[i];
} else {
throw new FastSqlException(FastChar.getLocal().getInfo("Db_Sql_Error1"));
}
if (value != null) {
preparedStatement.setObject(i + 1, value);
} else {
preparedStatement.setNull(i + 1, Types.NULL);
}
}
count = preparedStatement.executeUpdate();
} finally {
close(connection, preparedStatement);
log(sqlStr, params);
}
return count;
}
/**
* 执行插入数据
*
* @param sqlStr
* @param params
* @return 主键
*/
public int insert(String sqlStr, Object... params) throws Exception {
if (getDatabaseInfo().isCache() && isUseCache()) {
List allTables = getDatabaseInfo().getAllTables(sqlStr);
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
for (String table : allTables) {
Set tags = iFastCacheProvider.getTags("*" + buildCacheTag(table) + "*");
for (String tag : tags) {
iFastCacheProvider.deleteCache(tag);
}
}
}
int primary = 0;
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
if (connection == null) {
return primary;
}
preparedStatement = connection.prepareStatement(sqlStr, Statement.RETURN_GENERATED_KEYS);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
Object value = null;
if (i < params.length) {
value = params[i];
} else {
throw new FastSqlException(FastChar.getLocal().getInfo("Db_Sql_Error1"));
}
if (value != null) {
preparedStatement.setObject(i + 1, value);
} else {
preparedStatement.setNull(i + 1, Types.NULL);
}
}
primary = preparedStatement.executeUpdate();
resultSet = preparedStatement.getGeneratedKeys();
while (resultSet.next()) {
primary = resultSet.getInt(1);
}
} finally {
close(connection, preparedStatement, resultSet);
log(sqlStr, params);
}
return primary;
}
/**
* 批量执行sql
*
* @param sqlStrs sql数组
* @return 执行结果
*/
public int[] batch(List sqlStrs, int batchSize) throws Exception {
if (getDatabaseInfo().isCache() && isUseCache()) {
for (String sqlStr : sqlStrs) {
List allTables = getDatabaseInfo().getAllTables(sqlStr);
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
for (String table : allTables) {
Set tags = iFastCacheProvider.getTags("*" + buildCacheTag(table) + "*");
for (String tag : tags) {
iFastCacheProvider.deleteCache(tag);
}
}
}
}
Connection connection = null;
Statement statement = null;
try {
List result = new ArrayList<>();
int count = 0;
connection = getConnection();
if (connection == null) {
return new int[0];
}
statement = connection.createStatement();
for (String sql : sqlStrs) {
statement.addBatch(sql);
if (++count % batchSize == 0) {
int[] executeBatch = statement.executeBatch();
Integer[] integers = FastArrayUtils.toObject(executeBatch);
if (integers != null) {
result.addAll(Arrays.asList(integers));
}
statement.clearBatch();
}
}
int[] executeBatch = statement.executeBatch();
Integer[] integers = FastArrayUtils.toObject(executeBatch);
if (integers != null) {
result.addAll(Arrays.asList(integers));
}
return FastArrayUtils.toPrimitive(result.toArray(new Integer[]{}));
} finally {
close(connection, statement);
for (String sql : sqlStrs) {
log(sql, null);
}
}
}
/**
* 批量执行sql
*
* @param sqlStr 共同的sql语句
* @param params 参数数据的数组
*/
public int[] batch(String sqlStr, List params, int batchSize) throws Exception {
if (getDatabaseInfo().isCache() && isUseCache()) {
List allTables = getDatabaseInfo().getAllTables(sqlStr);
IFastCacheProvider iFastCacheProvider = FastChar.getCache();
for (String table : allTables) {
Set tags = iFastCacheProvider.getTags("*" + buildCacheTag(table) + "*");
for (String tag : tags) {
iFastCacheProvider.deleteCache(tag);
}
}
}
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
int count = 0;
connection = getConnection();
if (connection == null) {
return new int[0];
}
List result = new ArrayList<>();
preparedStatement = connection.prepareStatement(sqlStr);
if (params != null) {
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
for (Object[] param : params) {
if (param == null) continue;
for (int i = 0; i < parameterMetaData.getParameterCount(); i++) {
Object value = null;
if (i < param.length) {
value = param[i];
}
if (value != null) {
preparedStatement.setObject(i + 1, value);
} else {
preparedStatement.setNull(i + 1, Types.NULL);
}
}
preparedStatement.addBatch();
if (++count % batchSize == 0) {
int[] executeBatch = preparedStatement.executeBatch();
Integer[] integers = FastArrayUtils.toObject(executeBatch);
if (integers != null) {
result.addAll(Arrays.asList(integers));
}
preparedStatement.clearBatch();
}
}
}
int[] executeBatch = preparedStatement.executeBatch();
Integer[] integers = FastArrayUtils.toObject(executeBatch);
if (integers != null) {
result.addAll(Arrays.asList(integers));
}
return FastArrayUtils.toPrimitive(result.toArray(new Integer[]{}));
} finally {
close(connection, preparedStatement);
log(sqlStr, params);
}
}
public int[] batchSaveEntity(List entities, int batchSize) throws Exception {
if (entities.size() == 0) {
return new int[0];
}
LinkedHashMap> batchSqlMap = new LinkedHashMap<>();
for (FastEntity entity : entities) {
entity.setDefaultValue();
FastSqlInfo sqlInfo = entity.toInsertSql();
if (sqlInfo == null) {
continue;
}
if (!batchSqlMap.containsKey(sqlInfo.getSql())) {
batchSqlMap.put(sqlInfo.getSql(), new ArrayList());
}
batchSqlMap.get(sqlInfo.getSql()).add(sqlInfo.toParams());
}
List result = new ArrayList<>();
for (String sql : batchSqlMap.keySet()) {
int[] batch = this.batch(sql, batchSqlMap.get(sql), batchSize);
for (int i : batch) {
result.add(i);
}
}
return FastArrayUtils.toPrimitive(result.toArray(new Integer[]{}));
}
public int[] batchUpdateEntity(List entities, int batchSize) throws Exception {
if (entities.size() == 0) {
return new int[0];
}
LinkedHashMap> batchSqlMap = new LinkedHashMap<>();
for (FastEntity entity : entities) {
FastSqlInfo sqlInfo = entity.toUpdateSql();
if (sqlInfo == null) {
continue;
}
if (!batchSqlMap.containsKey(sqlInfo.getSql())) {
batchSqlMap.put(sqlInfo.getSql(), new ArrayList());
}
batchSqlMap.get(sqlInfo.getSql()).add(sqlInfo.toParams());
}
List result = new ArrayList<>();
for (String sql : batchSqlMap.keySet()) {
int[] batch = this.batch(sql, batchSqlMap.get(sql), batchSize);
for (int i : batch) {
result.add(i);
}
}
return FastArrayUtils.toPrimitive(result.toArray(new Integer[]{}));
}
public int[] batchDeleteEntity(List entities, int batchSize) throws Exception {
if (entities.size() == 0) {
return new int[0];
}
LinkedHashMap> batchSqlMap = new LinkedHashMap<>();
for (FastEntity entity : entities) {
FastSqlInfo sqlInfo = entity.toDeleteSql();
if (sqlInfo == null) {
continue;
}
if (!batchSqlMap.containsKey(sqlInfo.getSql())) {
batchSqlMap.put(sqlInfo.getSql(), new ArrayList());
}
batchSqlMap.get(sqlInfo.getSql()).add(sqlInfo.toParams());
}
List result = new ArrayList<>();
for (String sql : batchSqlMap.keySet()) {
int[] batch = this.batch(sql, batchSqlMap.get(sql), batchSize);
for (int i : batch) {
result.add(i);
}
}
return FastArrayUtils.toPrimitive(result.toArray(new Integer[]{}));
}
public int[] batchUpdate(List sqlInfos, int batchSize) throws Exception {
if (sqlInfos.size() == 0) {
return new int[0];
}
LinkedHashMap> batchSqlMap = new LinkedHashMap<>();
for (FastSqlInfo sqlInfo : sqlInfos) {
if (!batchSqlMap.containsKey(sqlInfo.getSql())) {
batchSqlMap.put(sqlInfo.getSql(), new ArrayList());
}
batchSqlMap.get(sqlInfo.getSql()).add(sqlInfo.toParams());
setLog(sqlInfo.isLog());
}
List result = new ArrayList<>();
for (String sql : batchSqlMap.keySet()) {
int[] batch = this.batch(sql, batchSqlMap.get(sql), batchSize);
for (int i : batch) {
result.add(i);
}
}
return FastArrayUtils.toPrimitive(result.toArray(new Integer[]{}));
}
public void close(Connection connection) {
if (isTransaction()) {
return;
}
if (connection != null) {
try {
connection.close();
} catch (Exception ignored) {}
}
}
public void close(PreparedStatement preparedStatement) {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (Exception ignored) {
}
}
}
public void close(ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception ignored) {
}
}
}
public void close(Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (Exception ignored) {
}
}
}
public void close(Connection connection, PreparedStatement preparedStatement) {
close(preparedStatement);
close(connection);
}
public void close(Connection connection, PreparedStatement preparedStatement,
ResultSet resultSet, Statement statement) {
close(resultSet);
close(statement);
close(preparedStatement);
close(connection);
}
public void close(Connection connection, PreparedStatement preparedStatement,
ResultSet resultSet) {
close(resultSet);
close(preparedStatement);
close(connection);
}
public void close(Connection connection, Statement statement) {
close(statement);
close(connection);
}
public void log(String sql, Object params) {
if (FastChar.getConstant().isDebug() && isLog()) {
if (FastStringUtils.isEmpty(sql)) {
return;
}
sql = sql.replace("\n", " ");
LinkedHashMap printMap = new LinkedHashMap<>();
printMap.put("Sql", sql);
if (params != null) {
if (params instanceof Object[]) {
printMap.put("Params", Arrays.toString((Object[]) params));
}
if (params instanceof List) {
List list = (List) params;
for (int i = 0; i < list.size(); i++) {
printMap.put("Params-" + (i + 1), Arrays.toString((Object[]) list.get(i)));
}
}
}
int maxKeyLength = 0;
for (String key : printMap.keySet()) {
maxKeyLength = Math.max(maxKeyLength, key.length() + 1);
}
StringBuilder print = new StringBuilder();
for (String key : printMap.keySet()) {
String text = printMap.get(key);
if (FastStringUtils.isEmpty(text)) {
continue;
}
print.append(formatString(key, maxKeyLength)).append(text).append("\n");
}
System.out.println(color(print.toString()));
}
}
private static String color(String content) {
return "\033[35;1m" + content + "\033[0m";
}
private static String formatString(String target, int targetLength) {
StringBuilder targetBuilder = new StringBuilder(target);
for (int i = 0; i < targetLength; i++) {
if (i >= targetBuilder.length()) {
targetBuilder.append(" ");
}
}
target = targetBuilder.toString();
return target + "：";
}
public boolean isLog() {
return log;
}
public FastDb setLog(boolean log) {
this.log = log;
return this;
}
public boolean isUseCache() {
return useCache;
}
public FastDb setUseCache(boolean useCache) {
this.useCache = useCache;
return this;
}
public String getDatabase() {
return database;
}
public FastDb setDatabase(String database) {
this.database = database;
return this;
}
}