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);
    }
  }
}

　　2、然后简单的写个 JDBC 操作数据库的支持类

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;

  }
}

　　3、写个测试连接的类，测试一下 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.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);
    }
  }
}

　　以上就是IDEA 链接Mysql数据库并执行查询操作的完整代码的详细内容，更多关于IDEA 链接Mysql执行查询操作 的资料请关注脚本之家其它相关文章！