1 Helper
package com.xu.music.player.wrapper.sql;
import java.util.List;
import java.util.Map;
import java.sql.Connection;
public interface Helper {
Connection getConn();
int insert(String sql, Object... params);
int update(String sql, Object... params);
int delete(String sql, Object... params);
<T> List<T> select(String sql, Class<T> cls, Object... params);
List<Map<String, Object>> select(String sql, Object... params);
}
2 NewHelper
package com.xu.music.player.wrapper.sql;
import java.io.BufferedInputStream;
import java.io.File;
import java.lang.reflect.Field;
import lombok.extern.slf4j.Slf4j;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import com.xu.music.player.hander.DataBaseError;
import com.xu.music.player.utils.Utils;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
@Slf4j
public class NewHelper implements Helper {
private static final String DATABASE = "lib/sqlite/db/MusicPlayer.db";
private static final String MAC_OS = "lib/sqlite/sqlite-tools-osx-x64-3460000/sqlite3";
private static final String LINUX = "lib/sqlite/sqlite-tools-linux-x64-3460000/sqlite3";
private static final String WINDOWS = "lib/sqlite/sqlite-tools-win-x64-3460000/sqlite3.exe";
static {
try {
String path;
switch (SysType.getSystemMainType().type) {
case 2:
case 3:
path = new File(MAC_OS).getCanonicalPath();
break;
case 4:
path = new File(LINUX).getCanonicalPath();
break;
default:
path = new File(WINDOWS).getCanonicalPath();
}
System.setProperty("java.library.path", path + ";" + System.getProperty("java.library.path"));
Class.forName("org.sqlite.JDBC");
} catch (Exception e) {
throw new DataBaseError(e.getMessage());
}
}
private static String underline(String str) {
String reg = "[A-Z]+";
Matcher matcher = Pattern.compile(reg).matcher(str);
while (matcher.find()) {
String group = matcher.group();
str = str.replace(group, "_" + group.toLowerCase(Locale.ROOT));
}
return str;
}
@Override
public Connection getConn() {
try {
return DriverManager.getConnection("jdbc:sqlite:" + DATABASE);
} catch (Exception e) {
throw new DataBaseError(e.getMessage());
}
}
@Override
public int insert(String sql, Object... params) {
return update(sql, params);
}
@Override
public int update(String sql, Object... params) {
try (Connection conn = this.getConn()) {
if (ArrayUtil.isEmpty(params)) {
try (Statement state = conn.createStatement()) {
return state.executeUpdate(sql);
} catch (SQLException e) {
conn.rollback();
}
}
try (PreparedStatement state = conn.prepareStatement(sql)) {
setValues(state, params);
return state.executeUpdate(sql);
} catch (SQLException e) {
conn.rollback();
}
} catch (Exception e) {
throw new DataBaseError(e.getMessage());
}
return 0;
}
@Override
public int delete(String sql, Object... para) {
return insert(sql, para);
}
@Override
public <T> List<T> select(String sql, Class<T> cls, Object... params) {
List<Map<String, Object>> data = select(sql, params);
if (CollUtil.isEmpty(data)) {
return new ArrayList<>();
}
return data.stream().filter(Objects::nonNull)
.map(item -> convert(item, cls))
.collect(Collectors.toList());
}
@Override
public List<Map<String, Object>> select(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<>();
Connection conn = this.getConn();
try (PreparedStatement state = conn.prepareStatement(sql)) {
this.setValues(state, params);
ResultSet result = state.executeQuery();
ResultSetMetaData data = result.getMetaData();
int len = data.getColumnCount();
String[] col = new String[len];
for (int i = 0; i < len; i++) {
col[i] = data.getColumnName(i + 1);
}
while (result.next()) {
list.add(setValue(result, col, len));
}
IoUtil.close(result);
} catch (Exception e) {
throw new DataBaseError(e.getMessage());
} finally {
IoUtil.close(conn);
}
return list;
}
public void setValues(PreparedStatement state, Object... obj) {
if (null == state || ArrayUtil.isEmpty(obj)) {
return;
}
setValues(state, Arrays.asList(obj));
}
public void setValues(PreparedStatement state, List<Object> params) {
if (null == state || CollUtil.isEmpty(params)) {
return;
}
try {
for (int i = 0; i < params.size(); i++) {
Object object = params.get(i);
if (null == object) {
state.setString(i + 1, "");
continue;
}
String type = object.getClass().getSimpleName();
switch (type) {
case "SerialBlob":
state.setBlob(i + 1, (Blob) params.get(i));
break;
case "Integer":
state.setInt(i + 1, Integer.parseInt(String.valueOf(object)));
break;
case "Double":
state.setDouble(i + 1, Double.parseDouble(String.valueOf(object)));
break;
case "Float":
state.setFloat(i + 1, Float.parseFloat(String.valueOf(object)));
break;
case "Long":
state.setLong(i + 1, Long.parseLong(String.valueOf(object)));
break;
case "Short":
state.setShort(i + 1, Short.parseShort(String.valueOf(object)));
break;
case "Timestamp":
state.setTimestamp(i + 1, (Timestamp) params.get(i));
break;
default:
state.setString(i + 1, String.valueOf(object));
}
}
} catch (SQLException e) {
throw new DataBaseError(e.getMessage());
}
}
private Object getValue(Map<String, Object> map, Field field) {
if (MapUtil.isEmpty(map)) {
return null;
}
for (Map.Entry<String, Object> result : map.entrySet()) {
if (StrUtil.equalsAnyIgnoreCase(result.getKey(), field.getName(), underline(field.getName()))) {
return result.getValue();
}
}
return null;
}
private <T> T convert(Map<String, Object> map, Class<T> cls) {
if (MapUtil.isEmpty(map) || null == cls) {
return null;
}
try {
Field[] fields = cls.getDeclaredFields();
T t = cls.getDeclaredConstructor().newInstance();
for (Field field : fields) {
Object object = getValue(map, field);
if (null == object) {
continue;
}
field.setAccessible(true);
String name = field.getType().getSimpleName();
if (StrUtil.equalsAny(name, "Date", "LocalDate", "LocalTime", "LocalDateTime")) {
field.set(t, Utils.formatDateTime(object));
} else {
field.set(t, object);
}
}
return t;
} catch (Exception e) {
throw new DataBaseError(e.getMessage());
}
}
private byte[] blob2byte(Blob blob) {
try {
if (blob == null || blob.length() == 0) {
return new byte[0];
}
byte[] bt = new byte[(int) blob.length()];
BufferedInputStream stream = new BufferedInputStream(blob.getBinaryStream());
stream.read(bt, 0, bt.length);
IoUtil.close(stream);
return bt;
} catch (Exception e) {
throw new DataBaseError(e.getMessage());
}
}
private Map<String, Object> setValue(ResultSet result, String[] col, int len) throws Exception {
Map<String, Object> map = new HashMap<>();
for (int i = 0; i < len; i++) {
Object obj = result.getObject(col[i]);
if (obj == null) {
continue;
}
String typeName = obj.getClass().getSimpleName();
if ("BLOB".equals(typeName)) {
Blob blob = result.getBlob(col[i]);
map.put(col[i], blob2byte(blob));
} else {
map.put(col[i], result.getObject(col[i]));
}
}
return map;
}
}
3 SysType
package com.xu.music.player.wrapper.sql;
import cn.hutool.core.util.StrUtil;
import java.util.Locale;
public enum SysType {
WINDOWS(1, "Windows"), MAC_OS(2, "Mac OS"), AIX(10, "AIX"),
LINUX(4, "Linux"), OS2(5, "OS/2"), SOLARIS(6, "Solaris"),
SUN_OS(7, "SunOS"), MPEIX(8, "MPE/iX"), HP_UX(9, "HP-UX"),
OS390(11, "OS/390"), FREE_BSD(12, "FreeBSD"), IRIX(13, "Irix"),
DIGITAL_UNIX(14, "Digital Unix"), NET_WARE(15, "NetWare"),
OPEN_VMS(17, "OpenVMS"), ANY(18, "Any"), OTHERS(19, "Others"),
MAC_OS_X(3, "Mac OS X"), OSF1(16, "OSF1");
public final int type;
public final String name;
SysType(int type, String name) {
this.type = type;
this.name = name;
}
public static SysType getSystemType() {
String type = System.getProperty("os.name").toLowerCase(Locale.ROOT);
if (StrUtil.equals("digital", "unix")) {
return SysType.DIGITAL_UNIX;
} else if (StrUtil.containsAny(type, "mac", "os") && !StrUtil.equals(type, "x")) {
return SysType.MAC_OS;
} else if (StrUtil.containsAny(type, "mac", "os") && StrUtil.equals(type, "x")) {
return SysType.MAC_OS_X;
}
switch (type) {
case "linux":
return SysType.LINUX;
case "windows":
return SysType.WINDOWS;
case "os/2":
return SysType.OS2;
case "solaris":
return SysType.SOLARIS;
case "sunos":
return SysType.SUN_OS;
case "mpe/ix":
return SysType.MPEIX;
case "hp-ux":
return SysType.HP_UX;
case "aix":
return SysType.AIX;
case "os/390":
return SysType.OS390;
case "freebsd":
return SysType.FREE_BSD;
case "irix":
return SysType.IRIX;
case "netware":
return SysType.NET_WARE;
case "osf1":
return SysType.OSF1;
case "openvms":
return SysType.OPEN_VMS;
default:
return SysType.OTHERS;
}
}
public static SysType getSystemMainType() {
SysType type = getSystemType();
if (type.type == 1) {
return SysType.WINDOWS;
} else if (type.type >= 2 && type.type <= 3) {
return SysType.MAC_OS;
} else if (type.type >= 5 && type.type <= 17) {
return SysType.LINUX;
} else {
return SysType.OTHERS;
}
}
}
4 BasicWrapper
package com.xu.music.player.wrapper;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import cn.hutool.core.util.StrUtil;
import com.xu.music.player.utils.Utils;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
public class BasicWrapper<T> {
protected String last;
protected Class<T> bean;
protected String table;
protected String[] field;
protected List<String> condition = new LinkedList<>();
protected String dealValue(List<Object> list) {
return Optional.ofNullable(list).orElse(new ArrayList<>()).stream().map(item -> {
if (null == item) {
return "null";
}
if (item instanceof Date) {
SimpleDateFormat format = new SimpleDateFormat(Utils.FORMAT_DATE_TIME);
return "'" + format.format(item) + "'";
} else if (item instanceof LocalDateTime) {
DateTimeFormatter format = DateTimeFormatter.ofPattern(Utils.FORMAT_DATE_TIME);
return "'" + format.format((LocalDateTime) item) + "'";
} else if (item instanceof String) {
return "'" + item + "'";
}
return String.valueOf(item);
}).collect(Collectors.joining(","));
}
protected String dealValue(Object value) {
if (null == value) {
return "null";
}
if (value instanceof Date) {
SimpleDateFormat format = new SimpleDateFormat(Utils.FORMAT_DATE_TIME);
return "'" + format.format(value) + "'";
} else if (value instanceof LocalDateTime) {
DateTimeFormatter format = DateTimeFormatter.ofPattern(Utils.FORMAT_DATE_TIME);
return "'" + format.format((LocalDateTime) value) + "'";
} else if (value instanceof String) {
return "'" + value + "'";
}
return String.valueOf(value);
}
protected String dealField(String name) {
Matcher matcher = Pattern.compile("[A-Z]").matcher(name);
while (matcher.find()) {
name = name.replace(matcher.group(), "_" + matcher.group().toLowerCase(Locale.ROOT));
}
if (StrUtil.equalsAnyIgnoreCase(name, "index")) {
return "`" + name + "`";
}
return name;
}
}
5 QueryWrapper
package com.xu.music.player.wrapper;
import cn.hutool.core.collection.CollectionUtil;
import com.xu.music.player.wrapper.sql.Helper;
import com.xu.music.player.wrapper.sql.NewHelper;
import java.util.List;
public class QueryWrapper<T> extends BasicWrapper<T> {
public QueryWrapper() {
}
public QueryWrapper(Class<T> bean, String table, String... field) {
super.bean = bean;
super.table = table;
super.field = (null == field || field.length == 0) ? new String[]{"*"} : field;
}
public List<T> list() {
String sql = "select " + String.join(",", super.field) + " from " + super.table + " where 1 = 1 ";
sql += CollectionUtil.isEmpty(super.condition) ? "" : String.join(" ", super.condition);
sql += null == super.last ? "" : super.last;
Helper helper = new NewHelper();
return helper.select(sql, super.bean);
}
public QueryWrapper<T> apply(String sql) {
super.condition.add(" and (" + sql + ")");
return this;
}
public QueryWrapper<T> apply(boolean cond, String sql) {
return cond ? apply(sql) : this;
}
public QueryWrapper<T> eq(String filed, Object value) {
super.condition.add(" and " + filed + " = " + value);
return this;
}
public QueryWrapper<T> eq(boolean cond, String filed, Object value) {
return cond ? eq(filed, value) : this;
}
public QueryWrapper<T> last(String sql) {
super.last = " " + sql;
return this;
}
public QueryWrapper<T> last(boolean cond, String sql) {
return cond ? last(sql) : this;
}
public QueryWrapper<T> like(String filed, Object value) {
super.condition.add(" and " + filed + " like %" + value + "%");
return this;
}
public QueryWrapper<T> like(boolean cond, String filed, Object value) {
return cond ? like(filed, value) : this;
}
public QueryWrapper<T> likeLeft(String filed, Object value) {
super.condition.add(" and " + filed + " like %" + value + "%");
return this;
}
public QueryWrapper<T> likeLeft(boolean cond, String filed, Object value) {
return cond ? likeLeft(filed, value) : this;
}
public QueryWrapper<T> likeRight(String filed, Object value) {
super.condition.add(" and " + filed + " like %" + value + "%");
return this;
}
public QueryWrapper<T> likeRight(boolean cond, String filed, Object value) {
return cond ? likeRight(filed, value) : this;
}
}
6 UpdateWrapper
package com.xu.music.player.wrapper;
import java.lang.reflect.Field;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.xu.music.player.hander.DataBaseError;
import com.xu.music.player.wrapper.sql.Helper;
import com.xu.music.player.wrapper.sql.NewHelper;
import java.util.LinkedList;
import java.util.List;
public class UpdateWrapper<T> extends BasicWrapper<T> {
private final T data;
public UpdateWrapper(T data, String table) {
if (null == data || StrUtil.isBlank(table)) {
throw new DataBaseError("参数错误");
}
this.data = data;
this.table = table;
}
public int update() throws Exception {
String sql = sql(false);
sql += CollectionUtil.isEmpty(super.condition) ? "" : String.join(" ", super.condition);
sql += null == super.last ? "" : super.last;
Helper helper = new NewHelper();
return helper.update(sql);
}
public int insert() throws Exception {
String sql = sql(true);
Helper helper = new NewHelper();
return helper.update(sql);
}
public int delete(String last) {
String sql = "delete from " + super.table + " where 1 = 1 ";
sql += CollectionUtil.isEmpty(super.condition) ? "" : String.join(" ", super.condition);
sql += null == super.last ? "" : super.last;
Helper helper = new NewHelper();
return helper.update(sql);
}
private String sql(boolean insert) throws Exception {
if (insert) {
return add();
}
return modify();
}
private String modify() throws Exception {
Field[] fields = this.data.getClass().getDeclaredFields();
String sql = "update " + super.table + " set ";
List<String> modify = new LinkedList<>();
for (Field field : fields) {
field.setAccessible(true);
Object value = field.get(data);
if (null != value) {
modify.add(dealField(field.getName()) + " = " + dealValue(field.get(data)));
}
}
sql = sql + String.join(", ", modify);
return sql;
}
private String add() throws Exception {
Field[] fields = this.data.getClass().getDeclaredFields();
String sql = "insert into " + super.table;
List<String> fieldsList = new LinkedList<>();
List<Object> valuesList = new LinkedList<>();
for (Field field : fields) {
field.setAccessible(true);
Object value = field.get(data);
if (null != value) {
fieldsList.add(dealField(field.getName()));
valuesList.add(field.get(data));
}
}
sql = sql + "(" + String.join(", ", fieldsList) + ") values(" + dealValue(valuesList) + ")";
return sql;
}
public UpdateWrapper<T> apply(String sql) {
super.condition.add(" and (" + sql + ")");
return this;
}
public UpdateWrapper<T> apply(boolean cond, String sql) {
return cond ? apply(sql) : this;
}
public UpdateWrapper<T> eq(String filed, Object value) {
super.condition.add(" and " + filed + " = " + value);
return this;
}
public UpdateWrapper<T> eq(boolean cond, String filed, Object value) {
return cond ? eq(filed, value) : this;
}
public UpdateWrapper<T> last(String sql) {
super.last = " " + sql;
return this;
}
public UpdateWrapper<T> last(boolean cond, String sql) {
return cond ? last(sql) : this;
}
public UpdateWrapper<T> like(String filed, Object value) {
super.condition.add(" and " + filed + " like %" + value + "%");
return this;
}
public UpdateWrapper<T> like(boolean cond, String filed, Object value) {
return cond ? like(filed, value) : this;
}
public UpdateWrapper<T> likeLeft(String filed, Object value) {
super.condition.add(" and " + filed + " like %" + value + "%");
return this;
}
public UpdateWrapper<T> likeLeft(boolean cond, String filed, Object value) {
return cond ? likeLeft(filed, value) : this;
}
public UpdateWrapper<T> likeRight(String filed, Object value) {
super.condition.add(" and " + filed + " like %" + value + "%");
return this;
}
public UpdateWrapper<T> likeRight(boolean cond, String filed, Object value) {
return cond ? likeRight(filed, value) : this;
}
}
扫一扫
1686
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
