Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
org.fastquery.core.DB Maven / Gradle / Ivy
/*
* Copyright (c) 2016-2088, fastquery.org and/or its affiliates. All rights reserved.
*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* For more information, please see http://www.fastquery.org/.
*
*/
package org.fastquery.core;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.regex.Pattern;
import java.util.stream.Stream;
import java.util.stream.Stream.Builder;
import com.alibaba.fastjson.JSONArray;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.RegExUtils;
import org.apache.commons.lang3.StringUtils;
import org.fastquery.util.TypeUtil;
import org.fastquery.struct.RespUpdate;
import org.fastquery.struct.SQLValue;
import org.fastquery.util.BeanUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
/**
* DB 最基本操作
*
* @author [email protected]
*/
@Slf4j
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class DB
{
private static void statSetObject(PreparedStatement stat, int parameterIndex, Object object) throws SQLException
{
if (object instanceof Enum)
{
Enum e = (Enum) object;
int index = e.ordinal() + 1; // 数据库中枚举元素对应的数字索引
stat.setObject(parameterIndex, index);
}
else if(object instanceof JSON)
{
stat.setObject(parameterIndex, object.toString());
}
else
{
stat.setObject(parameterIndex, object);
}
}
public static List> find(SQLValue sqlValue)
{
String sql = sqlValue.getSql();
List objs = sqlValue.getValues();
List> keyvals;
Connection conn = QueryContext.getConn();
PreparedStatement stat = null;
ResultSet rs = null;
try
{
QueryContext.addSqls(sql);
info(sql, objs);
stat = conn.prepareStatement(sql);
// 设置sql参数值
int lenTmp = objs.size(); // objs 源头上已经控制禁止为null
for (int i = 0; i < lenTmp; i++)
{
statSetObject(stat, i + 1, objs.get(i));
}
// 设置sql参数值 End
rs = stat.executeQuery();
keyvals = rs2Map(rs);
stat.close();
}
catch (Exception e)
{
throw new RepositoryException(e.getMessage(), e);
}
finally
{
close(rs, stat);
}
return keyvals;
}
/**
* @param sqlValues 待执行的SQL集
* @param hasPK 是否需要返回主健
* @return 改操作响应数据
*/
static List modify(List sqlValues, boolean hasPK)
{
List rus;
Connection conn = QueryContext.getConn(); // 由QueryContext自动关闭
try
{
QueryContext.disableAutoCommit(); // 关闭自动提交
rus = modify(sqlValues, hasPK, conn);
QueryContext.commit(); // 提交事务
}
catch (Exception e)
{
try
{
QueryContext.rollback();
}
catch (SQLException e1)
{
throw new RepositoryException(e1.getMessage(), e1);
}
throw new RepositoryException(e.getMessage(), e);
}
return rus;
}
private static List modify(List sqlValues, boolean hasPK, Connection conn) throws SQLException
{
List rus = new ArrayList<>();
for (SQLValue sqlValue : sqlValues)
{
ResultSet rs = null;
PreparedStatement stat = null;
RespUpdate ru = new RespUpdate();
try
{
String sql = sqlValue.getSql();
QueryContext.addSqls(sql);
info(sql, sqlValue.getValues());
if (hasPK)
{
stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
}
else
{
stat = conn.prepareStatement(sql);
}
List values = sqlValue.getValues();
int len = values.size();
for (int i = 0; i < len; i++)
{
// 设置sql参数值
statSetObject(stat, i + 1, values.get(i));
}
ru.setEffect(stat.executeUpdate());
if (hasPK)
{
rs = stat.getGeneratedKeys();
if (rs.next())
{
ru.setPk(rs.getLong(1));
}
}
stat.close();
}
catch (SQLException e)
{
throw new SQLException(e);
}
finally
{
close(rs, stat);
}
rus.add(ru);
}
return rus;
}
/**
* 改操作,若:isEffect=true,返回影响行数;若:isEffect=false,返回主键值.
*
* @param sql 语句
* @param isEffect 是否返回影响行数
* @return 影响行数 或 主键值
*/
static Object update(String sql, boolean isEffect)
{
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
Object key = null;
try
{
conn = QueryContext.getConn();
QueryContext.addSqls(sql);
QueryContext.disableAutoCommit();
log.info(sql);
if (isEffect)
{
stat = conn.prepareStatement(sql); // 不需要返回主键
}
else
{
stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
}
int e = stat.executeUpdate();
if (isEffect)
{
QueryContext.commit();
return e;
}
else
{
rs = stat.getGeneratedKeys();
// 获取主键
if (rs.next())
{
key = rs.getObject(1);
}
QueryContext.commit();
return key;
}
}
catch (SQLException e)
{
if (conn != null)
{
try
{
QueryContext.rollback();
}
catch (SQLException e1)
{
throw new RepositoryException(e1);
}
}
throw new RepositoryException(e);
}
finally
{
close(rs, stat);
}
}
static int update(Object bean, String dbName, String where)
{
Connection conn = null;
PreparedStatement stat = null;
Object[] updateInfo = (where == null || StringUtils.EMPTY.equals(where)) ? BeanUtil.toUpdateSQL(bean, dbName, false)
: BeanUtil.toUpdateSQL(bean, dbName, where);
if (updateInfo == null || updateInfo.length == 0)
{
return 0;
}
String sql = updateInfo[0].toString();
log.info(sql);
@SuppressWarnings("unchecked")
List args = (List) updateInfo[1];
int count = args.size();
int effect;
try
{
conn = QueryContext.getConn();
QueryContext.disableAutoCommit();
QueryContext.addSqls(sql);
info(sql, args);
stat = conn.prepareStatement(sql);
for (int i = 1; i <= count; i++)
{
statSetObject(stat, i, args.get(i - 1));
}
effect = stat.executeUpdate();
QueryContext.commit();
}
catch (SQLException e)
{
if (conn != null)
{
try
{
QueryContext.rollback();
}
catch (SQLException e1)
{
throw new RepositoryException(e1);
}
}
throw new RepositoryException(e);
}
finally
{
close(null, stat);
}
return effect;
}
// 查询一条数据然后转换成一个实体
static Object select(String sql, Object bean)
{
Class cls = (bean instanceof Class) ? (Class) bean : bean.getClass();
Connection conn;
Statement stat = null;
ResultSet rs = null;
try
{
conn = QueryContext.getConn();
stat = conn.createStatement();
log.info(sql);
QueryContext.addSqls(sql);
rs = stat.executeQuery(sql);
List> maps = rs2Map(rs);
if (maps.isEmpty())
{
return null;
}
//return JSON.toJavaObject(new JSONObject(maps.get(0)), cls)
return TypeUtil.map2Obj(cls, maps.get(0));
}
catch (Exception e)
{
throw new RepositoryException(e);
}
finally
{
close(rs, stat);
}
}
static boolean exists(String sql)
{
Connection conn;
Statement stat = null;
ResultSet rs = null;
try
{
conn = QueryContext.getConn();
stat = conn.createStatement();
QueryContext.addSqls(sql);
rs = stat.executeQuery(sql);
return rs.next();
}
catch (SQLException e)
{
throw new RepositoryException(e);
}
finally
{
close(rs, stat);
}
}
public static boolean exists(SQLValue sqlValue)
{
String sql = sqlValue.getSql();
List objs = sqlValue.getValues();
Connection conn = QueryContext.getConn();
PreparedStatement stat = null;
ResultSet rs = null;
try
{
QueryContext.addSqls(sql);
info(sql, objs);
stat = conn.prepareStatement(sql);
// 设置sql参数值
int lenTmp = objs.size(); // objs 源头上已经控制禁止为null
for (int i = 0; i < lenTmp; i++)
{
statSetObject(stat, i + 1, objs.get(i));
}
// 设置sql参数值 End
rs = stat.executeQuery();
return rs.next();
}
catch (Exception e)
{
throw new RepositoryException(e.getMessage(), e);
}
finally
{
close(rs, stat);
}
}
private static Stream parserSQLFile(String name)
{
Builder builder = Stream.builder();
try (FileReader reader = new FileReader(name); BufferedReader br = new BufferedReader(reader))
{
StringBuilder buff = new StringBuilder();
String str;
while ((str = br.readLine()) != null)
{
str = str.trim();
if (!StringUtils.EMPTY.startsWith(str) && !str.startsWith("#") && !str.startsWith("--"))
{
buff.append(str);
buff.append(' ');
int index = buff.indexOf(";");
if (index != -1)
{
builder.add(buff.substring(0, index).trim());
buff.delete(0, index + 1);
}
}
}
String lastStr = buff.toString().trim();
if (!StringUtils.EMPTY.equals(lastStr))
{
builder.add(lastStr);
}
}
catch (Exception e)
{
throw new RepositoryException(e);
}
return builder.build();
}
static int[] executeBatch(String sqlFile, String[] quotes, BiConsumer consumer)
{
Connection conn = QueryContext.getConn();
Statement stat = null;
Stream stream = parserSQLFile(sqlFile);
try
{
QueryContext.disableAutoCommit();
final Statement st = conn.createStatement();
stat = st;
stream.forEach(s -> {
if (quotes != null)
{
int len = quotes.length;
for (int i = 0; i < len; i++)
{
String val = quotes[i];
if (val == null)
{
val = StringUtils.EMPTY;
}
Pattern p = RegexCache.getPattern("\\$\\[" + i + "]");
s = RegExUtils.replaceAll(s,p,val);
}
}
consumer.accept(st, s);
});
int[] ints = stat.executeBatch();
stat.clearBatch();
QueryContext.commit();
return ints;
}
catch (Exception e)
{
try
{
QueryContext.rollback();
}
catch (SQLException e1)
{
throw new RepositoryException(e1.getMessage(), e1);
}
throw new RepositoryException(e);
}
finally
{
close(null, stat);
}
}
/**
* 释放资源
*
* @param rs ResultSet实例
* @param stat Statement实例
*/
private static void close(ResultSet rs, Statement stat)
{
try
{
if (rs != null)
{
rs.close();
}
}
catch (SQLException e)
{
throw new RepositoryException(e);
}
finally
{
if(stat != null)
{
try
{
stat.close();
}
catch (SQLException e)
{
throw new RepositoryException("stat 关闭异常", e);
}
}
}
}
/**
* 将 rs 的结果集 转换成 List<Map>,rs没有结果则返回空对象(该方法永不返回null).
*
* @param rs 结果集
* @return List map结果集
* @throws SQLException SQL异常
*/
private static List> rs2Map(ResultSet rs) throws SQLException
{
List> keyvals = new ArrayList<>();
Map keyval;
// 获取列信息
ResultSetMetaData resultSetMetaData;
// 总列数
int columnCount;
String key;
Object obj;
while (rs.next())
{
resultSetMetaData = rs.getMetaData();
columnCount = resultSetMetaData.getColumnCount();
keyval = new HashMap<>();
for (int i = 1; i <= columnCount; i++)
{
key = resultSetMetaData.getColumnLabel(i); // 获取列别名,若没有别名那么就获取本身名称(getColumnName)
obj = rs.getObject(i);
String columnType = resultSetMetaData.getColumnTypeName(i);
if("JSON".equals(columnType))
{
if(obj == null || "null".equals(obj))
{
String columnName = resultSetMetaData.getColumnName(i);
if(columnName.endsWith("Obj"))
{
obj = new JSONObject();
}
else if(columnName.endsWith("Array"))
{
obj = new JSONArray();
}
}
else
{
obj = JSON.parse(obj.toString());
}
}
keyval.put(key, obj);
}
keyvals.add(keyval);
}
return keyvals;
}
/**
* 输出执行日志
*
* @param sql sql语句
* @param objs 参数
*/
private static void info(String sql, List objs)
{
if (log.isInfoEnabled())
{ // 这个输出要做很多事情,在此判断一下很有必要,生产环境通常是warn级别
StringBuilder sb = new StringBuilder("\n正在准备执行SQL:");
sb.append(sql);
sb.append(StringUtils.LF);
if (objs != null && !objs.isEmpty())
{
int len = objs.size();
for (int i = 0; i < len; i++)
{
sb.append(String.format("第%d个\"?\"对应的参数值是:%s;%n", i + 1, objs.get(i)));
}
}
log.info(sb.toString());
}
}
}
