cn.handyplus.lib.db.SqlService Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of HandyLib Show documentation
handy的Minecraft快速开发框架
The newest version!
package cn.handyplus.lib.db;

import cn.handyplus.lib.db.enums.DbTypeEnum;
import org.bukkit.Bukkit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;

/**
 * sql基础方法
 *
 * @author handy
 * @since 1.0.4
 */
public class SqlService {

    private SqlService() {
    }

    public static SqlService getInstance() {
        return SqlService.SingletonHolder.INSTANCE;
    }

    /**
     * 执行普通sql
     *
     * @param sql           sql
     * @param storageMethod 储存方式
     * @since 2.9.3
     */
    public void executionSql(String sql, String storageMethod) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = SqlManagerUtil.getInstance().getConnection(storageMethod);
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException e) {
            Bukkit.getLogger().log(Level.SEVERE, "executionSql 发生异常", e);
        } finally {
            SqlManagerUtil.getInstance().closeSql(conn, ps, null);
        }
    }

    /**
     * 查询表字段
     *
     * @param sql           sql
     * @param storageMethod 存储方法
     * @return true/成功
     * @since 1.2.3
     */
    public List getTableInfo(String sql, String storageMethod) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rst = null;
        List fieldNameList = new ArrayList<>();
        try {
            conn = SqlManagerUtil.getInstance().getConnection(storageMethod);
            ps = conn.prepareStatement(sql);
            rst = ps.executeQuery();
            while (rst.next()) {
                String fieId;
                if (DbTypeEnum.SQLite.getType().equalsIgnoreCase(storageMethod)) {
                    fieId = rst.getString("name");
                } else {
                    fieId = rst.getString("column_name");
                }
                fieldNameList.add(fieId);
            }
        } catch (SQLException e) {
            Bukkit.getLogger().log(Level.SEVERE, "getTableInfo 发生异常", e);
        } finally {
            SqlManagerUtil.getInstance().closeSql(conn, ps, rst);
        }
        return fieldNameList;
    }

    /**
     * 查询表索引字段
     *
     * @param sql           sql
     * @param storageMethod 储存方式
     * @return true/成功
     * @since 2.9.3
     */
    public List getMysqlTableIndex(String sql, String storageMethod) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rst = null;
        List columnNameList = new ArrayList<>();
        try {
            conn = SqlManagerUtil.getInstance().getConnection(storageMethod);
            ps = conn.prepareStatement(sql);
            rst = ps.executeQuery();
            while (rst.next()) {
                String columnName = rst.getString("Column_name");
                columnNameList.add(columnName);
            }
        } catch (SQLException e) {
            Bukkit.getLogger().log(Level.SEVERE, "getMysqlTableIndex 发生异常", e);
        } finally {
            SqlManagerUtil.getInstance().closeSql(conn, ps, rst);
        }
        return columnNameList;
    }

    /**
     * 自定义sql查询并返回map
     *
     * @param sql sql
     * @return Map
     * @since 3.0.4
     */
    public Map selectMap(String sql) {
        return this.selectMap(sql, SqlManagerUtil.getInstance().getStorageMethod());
    }

    /**
     * 自定义sql查询并返回map
     *
     * @param sql           sql
     * @param storageMethod 存储方法
     * @return Map
     * @since 3.0.3
     */
    public Map selectMap(String sql, String storageMethod) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rst = null;
        Map map = new HashMap<>();
        try {
            conn = SqlManagerUtil.getInstance().getConnection(storageMethod);
            ps = conn.prepareStatement(sql);
            rst = ps.executeQuery();
            ResultSetMetaData rstMetaData = rst.getMetaData();
            for (int i = 1; i <= rstMetaData.getColumnCount(); i++) {
                map.put(rstMetaData.getColumnLabel(i), rst.getObject(i));
            }
        } catch (SQLException e) {
            Bukkit.getLogger().log(Level.SEVERE, "selectMap 发生异常", e);
        } finally {
            SqlManagerUtil.getInstance().closeSql(conn, ps, rst);
        }
        return map;
    }

    /**
     * 自定义sql查询并返回List
     *
     * @param sql sql
     * @return List
     * @since 3.0.4
     */
    public List> selectListMap(String sql) {
        return this.selectListMap(sql, SqlManagerUtil.getInstance().getStorageMethod());
    }

    /**
     * 自定义sql查询并返回List
     *
     * @param sql           sql
     * @param storageMethod 存储方法
     * @return List
     * @since 3.0.3
     */
    public List> selectListMap(String sql, String storageMethod) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rst = null;
        List> list = new ArrayList<>();
        try {
            conn = SqlManagerUtil.getInstance().getConnection(storageMethod);
            ps = conn.prepareStatement(sql);
            rst = ps.executeQuery();
            ResultSetMetaData rstMetaData = rst.getMetaData();
            while (rst.next()) {
                Map map = new HashMap<>();
                for (int i = 1; i <= rstMetaData.getColumnCount(); i++) {
                    map.put(rstMetaData.getColumnName(i), rst.getObject(i));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            Bukkit.getLogger().log(Level.SEVERE, "selectListMap 发生异常", e);
        } finally {
            SqlManagerUtil.getInstance().closeSql(conn, ps, rst);
        }
        return list;
    }

    private static class SingletonHolder {
        private static final SqlService INSTANCE = new SqlService();
    }

}