logo标签列表

将IntelliJ IDEA连接到SQL Server数据库

javasql-serverjdbcintellij-idea
3

社区！

我目前正在使用强大的Intellij IDEA Ultimate版学习Java。我试图连接到本地SQL Server数据库，但是我做得很困难。起初，我尝试使用内置工具（我成功连接了，但似乎只能在Intellij控制台上运行SQL查询）。更具体地说，我创建了一个基本的GUI，在其中客户可以输入他们的个人信息，我希望该信息存储在Customers SQL表中。我还尝试使用JDBC，但出现错误“com.microsoft.sqlserver.jdbc.SQLServerDriver”。我已经下载了所需的驱动程序，并将其放置在项目的lib文件夹中，但我不知道还要做什么。我的猜测是我没有正确链接或将带有驱动程序的jar文件放置在项目中。JetBrain的Intellij文档在SQL工具方面非常有限。它只涉及基本功能。如果有人能确定我的JDBC方法中的错误，或者对Intellij的SQL工具有更深入的了解，我会非常感激。感谢您花费时间帮助这个6个月的新手 :)

以下是我的代码：

import java.sql.*;
import javafx.application.Application;
import javafx.geometry.Insets;
import javafx.scene.*;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TextField;
import javafx.scene.layout.GridPane;
import javafx.stage.Stage;

 public class Main extends Application {

 String name;
 Stage window;
 Scene scene;
 Button submitButton = new Button("Submit");
 Label fNameLabel = new Label("First Name:");
 Label lNameLabel = new Label("Last Name:");
 Label birthDateLabel = new Label("DOB:");
 Label addressLabel = new Label("Address:");
 Label emailLabel = new Label("E-mail:");
 Label phoneLabel = new Label("Phone:");
 TextField fNameTextField = new TextField();
 TextField lNameTextField = new TextField();
 TextField birthDateTextField = new TextField();
 TextField addressTextField = new TextField();
 TextField emailTextField = new TextField();
 TextField phoneTextField = new TextField();


 public static void main(String args[]) {

    launch(args);

 }

 @Override
 public void start(Stage primaryStage) throws Exception {

    window = primaryStage;
    window.setTitle("Customer Information");
    window.setOnCloseRequest(e -> {
        e.consume();
        closeWindow();
    });

    //create GripPane scene
    GridPane grid = new GridPane();
    grid.setPadding(new Insets(10,10,10,10));
    grid.setVgap(10);
    grid.setHgap(10);

    //set location of labels
    GridPane.setConstraints(fNameLabel,3,0);
    GridPane.setConstraints(lNameLabel,3,1);
    GridPane.setConstraints(birthDateLabel,3,2);
    GridPane.setConstraints(addressLabel,3,3);
    GridPane.setConstraints(emailLabel,3,4);
    GridPane.setConstraints(phoneLabel,3,5);

    //set location of TextFields
    GridPane.setConstraints(fNameTextField,4,0);
    GridPane.setConstraints(lNameTextField,4,1);
    GridPane.setConstraints(birthDateTextField,4,2);
    GridPane.setConstraints(addressTextField,4,3);
    GridPane.setConstraints(emailTextField,4,4);
    GridPane.setConstraints(phoneTextField,4,5);

    //set PromptText
    birthDateTextField.setPromptText("mm/dd/yyyy");
    emailTextField.setPromptText("example@example.com");

    //set button location
    GridPane.setConstraints(submitButton, 4, 6);

    //add all elements to grid
    grid.getChildren().addAll(fNameLabel,fNameTextField,lNameLabel,lNameTextField,
                              birthDateLabel,birthDateTextField,addressLabel,addressTextField,
                              emailLabel,emailTextField,phoneLabel,phoneTextField,submitButton);

    scene = new Scene(grid, 400, 400);

    window.setScene(scene);
    window.show();

   }

  //properly exit out of app
  private void closeWindow() {
     boolean answer = ConfirmationBox.display("title", "Are you sure you want to exit?");
      if (answer) {
        window.close();
    }
  }
}

尝试连接的SQL类：

import java.sql.*;
public class SQLMethods {
  public static void main(String[] args) {
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databaseName=CaiMaster";

        Connection conn = DriverManager.getConnection(url);
        Statement statement = conn.createStatement();
        ResultSet resultSet;

        resultSet = statement.executeQuery("SELECT Fname, Address FROM Customers WHERE Fname = 'Cai'");
        String lastName = resultSet.getString("Fname");
        String address = resultSet.getString("Address");
        System.out.println(lastName);
        System.out.println(address);
        conn.close();
      } catch (Exception e) {
        System.err.println("Got an exception! ");
        System.err.println(e.getMessage());

    }
   }
  }
2个回答
3

将/lib目录添加到您的项目依赖项中，您将获得更多成功的机会。

我不认为您的URL是正确的。在获取/lib目录后，请尝试像这样使用它：

String url = "jdbc:sqlserver://localhost:1433;databaseName=CaiMaster";

尝试这个方法，看看是否更有效：

/**
 * SQL utility methods
 * User: MDUFFY
 * Date: 5/31/2016
 * Time: 4:41 PM
 * @link https://dev59.com/Y5ffa4cB1Zd3GeqP9pC9#37536406?noredirect=1#comment62595302_37536406
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class SQLMethods {

    public static final String DEFAULT_DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    public static final String DEFAULT_URL = "jdbc:sqlserver://localhost:1433;databaseName=CaiMaster";
    private static final String DEFAULT_USERNAME = "";
    private static final String DEFAULT_PASSWORD = "";
    public static final String FIND_ALL_CUSTOMERS_QUERY = "SELECT Fname, Address FROM Customers ";
    private static final String BY_FIRST_NAME = "WHERE FNAME = ? ";


    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = SQLMethods.getConnection(DEFAULT_DRIVER_CLASS, DEFAULT_URL, DEFAULT_USERNAME, DEFAULT_PASSWORD);
            Customer customer = SQLMethods.findCustomer(connection, "Cai");
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            SQLMethods.close(connection);
        }
    }

    public static Connection getConnection(String driverClass, String url, String username, String password) throws SQLException, ClassNotFoundException {
        Class.forName(driverClass);
        return DriverManager.getConnection(url, username, password);
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Customer findCustomer(Connection connection, String name) {
        Customer customer = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(FIND_ALL_CUSTOMERS_QUERY + BY_FIRST_NAME);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.next()) {
                String firstName = rs.getString("Fname");
                String address = rs.getString("Address");
                customer = new Customer(address, firstName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            SQLMethods.close(rs);
            SQLMethods.close(ps);
        }
        return customer;
    }
}

class Customer {
    private final String firstName;
    private final String address;

    public Customer(String address, String firstName) {
        this.address = address;
        this.firstName = firstName;
    }

    public String getFirstName() {
        return firstName;
    }

    public String getAddress() {
        return address;
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("Customer{");
        sb.append("firstName='").append(firstName).append('\'');
        sb.append(", address='").append(address).append('\'');
        sb.append('}');
        return sb.toString();
    }
}
谢谢！这看起来越来越好了。将jar文件添加到项目依赖项中有所帮助。在您的建议和其他人指出我缺少凭据之后，它不再给我缺少驱动程序的错误，但现在我得到了“结果集没有当前行”的错误。实际上确实如此。实际上它只有一行。有什么想法吗？ - Cai Cruz
是的，您没有使用ResultSet的典型用法。请查看JDBC教程。您需要调用next()来推进游标。 - duffymo
您真是太棒了，谢谢。由于某种原因，我认为只想要第一行，所以不需要告诉光标移动。现在它完美地工作了。非常感激！ - Cai Cruz
1
正如@duffymo所说，我也不确定您的URL是否正确。此外，在getConnection（url）方法中，您错过了用户名和密码。应该是这样的：
    String url = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databaseName=CaiMaster";
    String username = "user";
    String password = "pass";
    Connection conn = DriverManager.getConnection(url,username,password);

    Statement st = conn.createStatement();

希望这能有所帮助。让我知道。
谢谢，是的，它有点帮助。通过@duffymo的建议和你的建议，我相信我已经连接上了，但是当它确实有一行时，我仍然会收到“结果集没有当前行”的错误。我选择的那一行。有什么想法吗？ - Cai Cruz
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接