connect intellij idea to sql server database

Question

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:

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:

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

    }
   }
  }

Solution 1

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

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

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

Solution 2

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:

    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.

Comments

[Cai Cruz]

Thank you! This is starting to look better. Adding the jar file in the project dependencies helped. After your suggestions and someone else who pointed out I was missing the credentials, it doesn't give me the missing driver error, however now I get the error "the result set has no current row." Which it does indeed. Actually it only has one row. Any thoughts?

[Cai Cruz]

Thanks, yes it helped out a bit. With @duffymo suggestions plus yours i believe i am connected, however i still get an error "the result set has no current row" when it exactly has one row. The one i am selecting. Any ideas?

[duffymo]

Yes you aren't using the typical idiom for using a ResultSet. Look at a JDBC tutorial. You need a call to next() to advance the cursor.

[Cai Cruz]

You sir are awesome, thanks. For some reason I though since I just wanted the first row I didn't need to tell the cursor to move. It works like a charm now. Super thankful!!