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.
me.magicall.db.util.DbUtil Maven / Gradle / Ivy
/*
* Copyright (c) 2024 Liang Wenjian
* magicall is licensed under Mulan PSL v2.
* You can use this software according to the terms and conditions of the Mulan PSL v2.
* You may obtain a copy of Mulan PSL v2 at:
* http://license.coscl.org.cn/MulanPSL2
* THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
* EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
* MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
* See the Mulan PSL v2 for more details.
*/
package me.magicall.db.util;
import me.magicall.db.FieldComparator;
import me.magicall.db.meta.DbColumn;
import me.magicall.db.meta.TableMeta;
import me.magicall.lang.LabelStyle;
import me.magicall.dear_sun.Named;
import me.magicall.dear_sun.coll.CollKit;
import me.magicall.dear_sun.coll.ElementTransformer;
import me.magicall.lang.java.Kits;
import me.magicall.lang.java.StrKit;
import me.magicall.dear_sun.time.TimeFormatter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class DbUtil {
//=============================== 一些工具常量 some tooling constants
public static final String PLACE_HOLDER = " ? ";
/**
* java.sql.ResultSet的列下标是从1开始的
*/
public static final int RESULT_SET_COLUMN_START_INDEX = 1;
/**
* java.sql.PreparedStatement参数列表下标是从1开始的.
*/
public static final int PREPARED_STATEMENT_PARAMETER_START_INDEX = 1;
public static final String COMMON_ID_FIELD_NAME = "id";
public static final String COMMON_ID_COLUMN_NAME = javaNameToDbName(COMMON_ID_FIELD_NAME);
/**
* 为集合中的每一个元素预留一个占位符
*/
public static final ElementTransformer ELEMENTS_COUNT_TO_PLACE_HOLDER
= (index, element) -> PLACE_HOLDER;
public static final DbTypeConvert COMMON_DB_TYPE_CONVERTER = ResultSet::getObject;
public static final char TABLE_NAME_COLUMN_NAME_SEPARATOR_CHAR = '$';
public static final String TABLE_NAME_COLUMN_NAME_SEPARATOR = String.valueOf(TABLE_NAME_COLUMN_NAME_SEPARATOR_CHAR);
private static final char[] QUOTE_SIGNS = {'`', '\'', '\"'};
/**
* 将列名转化成字段名
*/
public static final ElementTransformer COLUMN_NAME_TO_FIELD_NAME
= (index, element) -> dbNameToJavaName(element);
private static final Map, DbTypeConvert> DB_TYPE_CONVERTERS = new LinkedHashMap<>();
private static final Collection> DB_TYPES;
static {
//int
final DbTypeConvert i = ResultSet::getInt;
DB_TYPE_CONVERTERS.put(int.class, i);
DB_TYPE_CONVERTERS.put(Integer.class, i);
//string
DB_TYPE_CONVERTERS.put(String.class, ResultSet::getString);
//long
final DbTypeConvert l = ResultSet::getLong;
DB_TYPE_CONVERTERS.put(long.class, l);
DB_TYPE_CONVERTERS.put(Long.class, l);
//java.util.date & timestamp
final DbTypeConvert t = ResultSet::getTimestamp;
DB_TYPE_CONVERTERS.put(Date.class, t);
DB_TYPE_CONVERTERS.put(Timestamp.class, t);
//boolean
final DbTypeConvert b = ResultSet::getBoolean;
DB_TYPE_CONVERTERS.put(boolean.class, b);
DB_TYPE_CONVERTERS.put(Boolean.class, b);
//float
final DbTypeConvert f = ResultSet::getFloat;
DB_TYPE_CONVERTERS.put(float.class, f);
DB_TYPE_CONVERTERS.put(Float.class, f);
//double
final DbTypeConvert d = ResultSet::getDouble;
DB_TYPE_CONVERTERS.put(double.class, d);
DB_TYPE_CONVERTERS.put(Double.class, d);
//byte
final DbTypeConvert bt = ResultSet::getByte;
DB_TYPE_CONVERTERS.put(byte.class, bt);
DB_TYPE_CONVERTERS.put(Byte.class, bt);
//short
final DbTypeConvert s = ResultSet::getShort;
DB_TYPE_CONVERTERS.put(short.class, s);
DB_TYPE_CONVERTERS.put(Short.class, s);
//char
final DbTypeConvert c = ResultSet::getCharacterStream;
DB_TYPE_CONVERTERS.put(char.class, c);
DB_TYPE_CONVERTERS.put(Character.class, c);
//java.sql.Date
DB_TYPE_CONVERTERS.put(java.sql.Date.class, ResultSet::getDate);
//time
DB_TYPE_CONVERTERS.put(Time.class, ResultSet::getTime);
//object
DB_TYPE_CONVERTERS.put(Object.class, COMMON_DB_TYPE_CONVERTER);
//comparable
DB_TYPE_CONVERTERS.put(Comparable.class, COMMON_DB_TYPE_CONVERTER);
//XXX:Number
DB_TYPES = DB_TYPE_CONVERTERS.keySet();
}
public static DbTypeConvert getDbTypeConvert(final Class clazz) {
final var convert = DB_TYPE_CONVERTERS.get(clazz);
return convert == null ? COMMON_DB_TYPE_CONVERTER : convert;
}
public static boolean isDbType(final Class clazz) {
return CollKit.deepContains(DB_TYPES, clazz);
}
/**
* 在指定的表的元数据中寻找指定的字段所对应的列的元数据.
*
* @param tableMeta
* @param javaFieldName
* @return
*/
public static DbColumn findColumn(final TableMeta tableMeta, final String javaFieldName) {
return tableMeta.getColumn(javaNameToDbName(javaFieldName));
}
/**
* //注：目前只支持单字段主键
* //目前只支持Integer和Long的id
*
* @param tableMeta
* @return
*/
public static boolean idIsInt(final TableMeta tableMeta) {
return tableMeta.getPrimaryKey().getColumns().get(0).getType() == FieldType.INTEGER;
}
//------------------------------- about sql
public static StringBuilder buildSql_inParamName(final StringBuilder sb, final Collection coll,
final String paramNamePrefix) {
return StrKit.join(sb.append(" in("), coll, ",",
(ElementTransformer) (index, element) -> paramNamePrefix + index).append(')');
}
/**
* 拼接in子句,使用'?'作为占位符,个数=coll.size()
*
* @param sb
* @param coll
* @return
*/
public static StringBuilder appendInPlaceHolder(final StringBuilder sb, final Collection coll) {
return appendParameterPlaceHolder(sb.append(" in("), coll).append(')');
}
public static StringBuilder appendParameterPlaceHolder(final StringBuilder sb, final Collection coll) {
return StrKit.join(sb, coll, ",", ELEMENTS_COUNT_TO_PLACE_HOLDER);
}
public static StringBuilder buildSql_Insert(final List columnNames, final String tableName,
final ElementTransformer placeHolderTF) {
final var sb = new StringBuilder("insert into ").append(tableName)//
.append('(').append(String.join(",", columnNames))//
.append(")values(");
// return appendParameterPlaceHolder(sb, columnNames).append(")");
StrKit.join(sb, columnNames, ",", placeHolderTF);
return sb.append(')');
}
public static StringBuilder buildSql_Insert(final TableMeta tableMeta,
final ElementTransformer placeHolderTF) {
return buildSql_Insert(Named.toNames(tableMeta.getColumns()), tableMeta.name(), placeHolderTF);
}
public static StringBuilder buildSql_UpdateSetValuePart(final StringBuilder sb, final List columnNames,
final ElementTransformer placeHolderTF,
final boolean escapeId) {
var index = 0;
if (escapeId) {
for (final var column : columnNames) {
if (!COMMON_ID_COLUMN_NAME.equalsIgnoreCase(column)) {
sb.append(column).append('=').append(placeHolderTF.transform(index, column)).append(',');
}
++index;
}
} else {
for (final var column : columnNames) {
sb.append(column).append('=').append(placeHolderTF.transform(index, column)).append(',');
++index;
}
}
return sb.deleteCharAt(sb.length() - 1);
}
public static StringBuilder buildSql_SelectAllFrom(final String tableName, final List columnNames) {
return new StringBuilder("select ").append(String.join(",", columnNames))//
.append(" from ").append(tableName);
}
public static StringBuilder buildSql_SelectAllFromWhereId(final String tableName, final List columnNames) {
return buildSql_SelectAllFromWhereId(tableName, columnNames, COMMON_ID_COLUMN_NAME);
}
public static StringBuilder buildSql_SelectAllFromWhereId(final String tableName, final List columnNames,
final String idColumnName) {
return buildSql_SelectAllFrom(tableName, columnNames).append(" where ").append(idColumnName);
}
public static StringBuilder buildSql_SelectAllFrom(final TableMeta tableMeta) {
return buildSql_SelectAllFrom(tableMeta.name(), Named.toNames(tableMeta.getColumns()));
}
public static StringBuilder buildSql_SelectAllFromWhereId(final TableMeta tableMeta) {
return buildSql_SelectAllFrom(tableMeta).append(" where ").append(COMMON_ID_FIELD_NAME);
}
public static StringBuilder appendLimit(final StringBuilder sb, final PageInfo pageInfo) {
if (pageInfo != null) {
final var size = pageInfo.getSize();
final var offset = pageInfo.getOffset();
if (size < Integer.MAX_VALUE) {
sb.append(" LIMIT ");
if (offset > 0) {
sb.append(offset).append(',');
}
return sb.append(size);
}
}
return sb;
}
public static StringBuilder buildDeleteFromTable(final String modelName) {
return new StringBuilder("delete from ").append(javaNameToDbName(modelName));
}
public static StringBuilder appendOrderBy(final StringBuilder sb, final FieldComparator fieldComparator) {
if (fieldComparator != null) {
final var comparingFieldsNamesAndOrders = fieldComparator.getComparingFieldsNamesAndOrders();
if (!Kits.COLL.isEmpty(comparingFieldsNamesAndOrders)) {
sb.append(" ORDER BY ");
comparingFieldsNamesAndOrders.forEach(t -> {
final var fieldName = t.first;
final var order = t.second;
sb.append(fieldName).append(' ');
if (order == null) {
sb.append(DbOrder.ASC.toSql()).append(',');
} else {
sb.append(order.toSql()).append(',');
}
});
sb.deleteCharAt(sb.length() - 1);
}
}
return sb;
}
//------------------------------- about name
//------------------------------- about resultSet
public static Object getIdFromResultSet(final ResultSet resultSet, final TableMeta tableMeta) throws SQLException {
//1，直接写id=resultSet.getObject，返回的是一个Long
//2，如果写成 id = idIsInt ? resultSet.getInt(...) : resultSet.getLong(...)，因为冒号两端会被弄成相同类型，最终左侧无论如何也会被包装成一个Long
if (idIsInt(tableMeta)) {
return resultSet.getInt(COMMON_ID_COLUMN_NAME);
} else {
return resultSet.getLong(COMMON_ID_COLUMN_NAME);
}
}
/**
* 将 xx_yy_zz 转换成 xxYyZz
*
* @param dbName
* @return
*/
public static String dbNameToJavaName(final String dbName) {
final var unquoted = unquote(dbName);
final var sb = new StringBuilder();
final var len = unquoted.length();
for (var i = 0; i < len; ++i) {
var c = unquoted.charAt(i);
if (c == '_') {
++i;
c = Character.toUpperCase(unquoted.charAt(i));
} else {
c = Character.toLowerCase(c);
}
sb.append(c);
}
return sb.toString();
}
/**
* 将xx_yy_zz转换成xxYyZz
*
* @param source
* @return
*/
public static List dbNameToJavaName(final List source) {
return Kits.LIST.transform(source, COLUMN_NAME_TO_FIELD_NAME);
}
/**
* convert xxYyZz to xx_yy_zz
*
* @param javaName
* @return
*/
public static String javaNameToDbName(final String javaName) {
return LabelStyle.CAMEL.convertTo(LabelStyle.UNDERLINE_CONNECTOR, javaName);
}
/**
* 取出最外层小括号[第一个'('和最后一个')']中的字符串.
*
* @param source
* @return
*/
public static String strInBrackets(final String source) {
final var indexOfLeft = source.indexOf('(');
if (indexOfLeft < 0) {
return null;
}
final var beginIndex = indexOfLeft + 1;
return source.substring(beginIndex, source.lastIndexOf(')'));
}
/**
* 去掉字符串两端的引号,包括单引号、双引号、反引号（`)
*
* @param name
* @return
*/
public static String unquote(final String name) {
var trimmed = name.trim();
for (final var c : QUOTE_SIGNS) {
if (trimmed.charAt(0) == c) {
trimmed = trimmed.substring(1);
}
final var len = trimmed.length();
if (trimmed.charAt(len - 1) == c) {
trimmed = trimmed.substring(0, len - 1);
}
}
return trimmed;
}
/**
* 用反引号括起字符串
*
* @param name
* @return
*/
public static String quoteDbName(final String name) {
return '`' + name + '`';
}
/**
* FIXME:注:此方法的实现与springJdbc耦合
*
* @param fieldName
* @return
*/
public static String namedParam(final String fieldName) {
return ':' + fieldName;
}
/**
* 将value拼接到sb.如果是数字或布尔型,直接拼接;如果是时间类型,将其格式化为yyyy-MM-dd HH:mm:ss:SSS;其他类型当做字符串,加上引号
*
* @param sb
* @param value
* @return
*/
public static StringBuilder formatSqlValue(final StringBuilder sb, final Object value) {
if (value instanceof Number || value instanceof Boolean) {
sb.append(value);
} else if (value instanceof Date) {
sb.append('\'').append(TimeFormatter.Y4_M2_D2_H2_MIN2_S2_MS3.format((Date) value)).append('\'');
} else {
sb.append('\'').append(value).append('\'');
}
return sb;
}
public static void main(final String... args) {
System.out.println("@@@@@@DbUtil.main():" + javaNameToDbName("device_aaa"));
}
/**
* 调用ResultSet的getXxx方法将数据转化成相应的java格式.
* (本接口其实就是把ResultSet的一系列getXxx方法转变成一系列对象).
*
* @author MaGiCalL
*/
@FunctionalInterface
public interface DbTypeConvert {
Object convert(ResultSet rs, int columnIndex) throws SQLException;
}
}
