IDEA 链接Mysql数据库并执行查询操作的完整代码

1、先写个 Mysql 的链接设置页面

package com.wretchant.fredis.menu.mysql;import com.intellij.notification.NotificationType;import com.intellij.openapi.actionSystem.AnAction;import com.intellij.openapi.actionSystem.AnActionEvent;import com.wretchant.fredis.gui.dialog.TableDialog;import com.wretchant.fredis.util.NotifyUtils;import com.wretchant.fredis.util.PropertiesUtils;import org.jetbrains.annotations.NotNull;import javax.swing.*;import java.util.Map;import java.util.Properties;/** * @author Created by 谭健 on 2020/8/26. 星期三. 15:24. * © All Rights Reserved. */public class MysqlConfig extends AnAction {    @Override    public void actionPerformed(@NotNull AnActionEvent event) {        Properties properties = PropertiesUtils.readFromSystem();        if (properties != null) {            TableDialog.TableField build = TableDialog.TableField.build(properties.stringPropertyNames());            TableDialog dialog = new TableDialog("Mysql 连接配置", build);            for (int i = 0; i < dialog.getLabels().size(); i++) {                JLabel label = dialog.getLabels().get(i);                JTextField textField = dialog.getInputs().get(i);                String property = properties.getProperty(label.getText());                textField.setText(property);            }            dialog.show();            if (dialog.isOK()) {                Map<String, String> valueMap = dialog.getValueMap();                valueMap.forEach(properties::setProperty);                PropertiesUtils.write2System(properties);            }        } else {            NotifyUtils.notifyUser(event.getProject(), "读取配置文件失败，配置文件不存在", NotificationType.ERROR);        }    }}

package com.wretchant.fredis.support;import cn.hutool.core.util.StrUtil;import com.intellij.notification.NotificationType;import com.intellij.openapi.actionSystem.AnActionEvent;import com.intellij.openapi.actionSystem.PlatformDataKeys;import com.intellij.openapi.editor.SelectionModel;import com.wretchant.fredis.util.ClipboardUtils;import com.wretchant.fredis.util.NotifyUtils;import com.wretchant.fredis.util.PropertiesUtils;import com.wretchant.fredis.value.StringValue;import org.apache.commons.lang.StringUtils;import org.jetbrains.annotations.NotNull;import java.sql.*;import java.util.*;/** * @author Created by 谭健 on 2020/8/12. 星期三. 17:42. * © All Rights Reserved. */public class Mysql {    /**     * 执行查询语句的返回结果     */    public static class Rs {        public Rs(List<Map<String, Object>> r) {            this.r = r;            this.count = r.size();        }        private List<Map<String, Object>> r = new ArrayList<>();        private int count;        public List<Map<String, Object>> getR() {            return r;        }        public void setR(List<Map<String, Object>> r) {            this.r = r;        }        public int getCount() {            return count;        }        public void setCount(int count) {            this.count = count;        }        public Map<String, Object> one() {            if (Objects.isNull(r) || r.isEmpty()) {                return null;            }            return r.get(0);        }        public Object oneGet(String key) {            return one().get(key);        }    }    // 参考： https://www.cnblogs.com/jyroy/p/9637149.html    public static class JDBCUtil {        /**         * 执行sql 并返回 map 数据         *         * @param sql         * @return         */        public static Rs rs(String sql) {            Connection connection = null;            Statement statement = null;            ResultSet resultSet = null;            List<Map<String, Object>> r = new ArrayList<>();            try {                connection = Mysql.DatabaseUtils.getConnection();                statement = connection.createStatement();                resultSet = statement.executeQuery(sql);                // 基础信息                ResultSetMetaData metaData = resultSet.getMetaData();                // 返回了多少个字段                int columnCount = metaData.getColumnCount();                while (resultSet.next()) {                    Map<String, Object> valueMap = new LinkedHashMap<>();                    for (int i = 0; i < columnCount; i++) {                        // 这个字段是什么数据类型                        String columnClassName = metaData.getColumnClassName(i);                        // 字段名称                        String columnName = metaData.getColumnName(i);                        Object value = resultSet.getObject(columnName);                        valueMap.put(columnName, value);                    }                    r.add(valueMap);                }            } catch (Exception e1) {                NotifyUtils.notifyUser(null, "error", NotificationType.ERROR);                e1.printStackTrace();            } finally {                release(connection, statement, resultSet);            }            return new Rs(r);        }        public static ResultSet es(String sql) {            Connection connection;            Statement statement;            ResultSet resultSet = null;            try {                connection = Mysql.DatabaseUtils.getConnection();                statement = connection.createStatement();                resultSet = statement.executeQuery(sql);            } catch (Exception e1) {                NotifyUtils.notifyUser(null, "error", NotificationType.ERROR);                e1.printStackTrace();            }            return resultSet;        }        public static void release(Connection connection, Statement st, ResultSet rs) {            closeConn(connection);            closeRs(rs);            closeSt(st);        }        public static void closeRs(ResultSet rs) {            try {                if (rs != null) {                    rs.close();                }            } catch (SQLException e) {                e.printStackTrace();            } finally {                rs = null;            }        }        private static void closeSt(Statement st) {            try {                if (st != null) {                    st.close();                }            } catch (SQLException e) {                e.printStackTrace();            } finally {                st = null;            }        }        private static void closeConn(Connection connection) {            try {                if (connection != null) {                    connection.close();                }            } catch (SQLException e) {                e.printStackTrace();            } finally {                connection = null;            }        }    }    public static class DatabaseUtils {        private static Connection connection = null;        static {            Properties properties = PropertiesUtils.readFromSystem();            try {                if (properties != null) {                    Class.forName("com.mysql.cj.jdbc.Driver");                    connection = DriverManager.getConnection(                            properties.getProperty("mysql.url"),                            properties.getProperty("mysql.username"),                            properties.getProperty("mysql.password")                    );                    NotifyUtils.notifyUser(null, "数据库连接成功", NotificationType.INFORMATION);                }            } catch (Exception e) {                NotifyUtils.notifyUser(null, "数据库连接失败", NotificationType.ERROR);                e.printStackTrace();            }        }        public static Connection getConnection() {            return connection;        }    }    public static void exec(@NotNull AnActionEvent event, Template template) {        StringValue stringValue = new StringValue(template.getDefaultValue());        Optional.ofNullable(event.getData(PlatformDataKeys.EDITOR)).                ifPresent(editor -> {                    SelectionModel selectionModel = editor.getSelectionModel();                    String selectedText = selectionModel.getSelectedText();                    if (StringUtils.isNotBlank(selectedText)) {                        stringValue.setValue(StrUtil.format(template.getDynamicValue(), selectedText));                    }                });        ClipboardUtils.clipboard(stringValue.getValue());        NotifyUtils.notifyUser(event.getProject(), stringValue.getValue(), NotificationType.INFORMATION);    }    /**     * sql 语句模版     */    public enum Template {        SELECT("SELECT * FROM x WHERE 1 = 1 AND ", "SELECT * FROM {} WHERE 1 = 1 AND ", "查询语句"),        UPDATE("UPDATE x SET x = x WHERE 1 = 1 AND ", "UPDATE {} SET x = x WHERE 1 = 1 AND ", "更新语句"),        DELETE("DELETE FROM x WHERE 1 = 1 ", "DELETE FROM {} WHERE 1 = 1 ", "删除语句"),        INSERT("INSERT INTO * (x) VALUES (x) ", "INSERT INTO {} (x) VALUES (x) ", "新增语句"),        ;        Template(String defaultValue, String dynamicValue, String describe) {            this.defaultValue = defaultValue;            this.dynamicValue = dynamicValue;            this.describe = describe;        }        public String getDynamicValue() {            return dynamicValue;        }        public String getDefaultValue() {            return defaultValue;        }        public String getDescribe() {            return describe;        }        /**         * 模版内容:默认值         */        private final String defaultValue;        /**         * 动态内容         */        private final String dynamicValue;        /**         * 内容描述         */        private final String describe;    }}

package com.wretchant.fredis.menu.mysql;import com.intellij.notification.NotificationType;import com.intellij.openapi.actionSystem.AnAction;import com.intellij.openapi.actionSystem.AnActionEvent;import com.wretchant.fredis.support.Mysql;import com.wretchant.fredis.util.NotifyUtils;import org.jetbrains.annotations.NotNull;import java.sql.ResultSet;/** * @author Created by 谭健 on 2020/9/15. 星期二. 10:17. * © All Rights Reserved. */public class MysqlConn extends AnAction {    @Override    public void actionPerformed(@NotNull AnActionEvent event) {        try {            ResultSet es = Mysql.JDBCUtil.es("select 1 as ct");            es.next();            int ct = es.getInt("ct");            if (ct == 1) {                NotifyUtils.notifyUser(null, "连接是正常的", NotificationType.INFORMATION);            } else {                NotifyUtils.notifyUser(null, "连接不正常", NotificationType.ERROR);            }            Mysql.JDBCUtil.closeRs(es);        } catch (Exception e1) {            e1.printStackTrace();            NotifyUtils.notifyUser(null, "连接不正常", NotificationType.ERROR);        }    }}

更多相关文章

随机推荐