java 将intellij idea连接到sql server数据库

connect intellij idea to sql server database

提问by Cai Cruz

community!

社区！

I am currently learning Java using the awesome Intellij IDEA Ultimate edition. I am trying to connect to a local SQL Server Database, but I am having a hard time doing so. Initially, I tried using the built in tools(which I was able to connect successfully, however it would seem like all that does is give me the ability to run SQL queries on its on Intellij console. To be more specific I created a basic GUI in which a customer can input their personal info, and I would like that info to be stored in the Customers SQL table. I also tried using the JDBC, but for some reason I get the error "com.microsoft.sqlserver.jdbc.SQLServerDriver". I did download the required driver, and placed it in my project's lib folder, but I don't know what else to do. My guess is I am not properly linking or placing the jar file with the driver in my project. JetBrain's Intellij Documentation is very limited when it comes to SQL Tools. It only goes to basic functionality. If someone can pinpoint my error on my JDBC approach, or maybe knows more in depth about Intellij's SQL Tools, I would appreciated it. I thank you for your time in advanced for trying to help out this 6-month old newbie :) Here is my code:

我目前正在使用令人敬畏的 Intellij IDEA Ultimate 版本学习 Java。我正在尝试连接到本地 SQL Server 数据库，但我很难这样做。最初，我尝试使用内置工具（我能够成功连接，但似乎所有这些都使我能够在 Intellij 控制台上运行 SQL 查询。更具体地说，我创建了一个基本的 GUI客户可以在其中输入他们的个人信息，我希望将该信息存储在客户 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("[email protected]");

    //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 class that attempts a connection:

尝试连接的 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());

    }
   }
  }

采纳答案by duffymo

Add the /lib directory to your project under dependencies and you'll have more success.

将 /lib 目录添加到您的项目的依赖项下，您将获得更大的成功。

I don't believe your URL is correct. Try it like this once you get the /lib directory sorted:

我不相信你的网址是正确的。对 /lib 目录进行排序后，请像这样尝试：

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

Try this and see if it works better:

试试这个，看看它是否更好用：

/**
 * SQL utility methods
 * User: MDUFFY
 * Date: 5/31/2016
 * Time: 4:41 PM
 * @link http://stackoverflow.com/questions/37536372/connect-intellij-idea-to-sql-server-database/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();
    }
}

回答by Medet Koilybay

As @duffymo said, i am also not sure about correctness of your url. In addition, you missed your username and password in getConnection(url)method. It should be something like this:

正如@duffymo 所说，我也不确定您的网址是否正确。此外，您在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();

Hope this will help. Let me know.

希望这会有所帮助。让我知道。