General

Java - JDBC Code Examples for MSSQL

By Niall Kader1 min read

EXECUTING A SIMPLE QUERY

package database;

import java.sql.*;

public class TestJDBC {

  public void test(){

    String connectionUrl = "jdbc:sqlserver://192.168.204.180:1433;" +
      "databaseName=TestDB;user=testLogin;password=xxxxx";


    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
    
      //establish connection
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      con = DriverManager.getConnection(connectionUrl);

      String SQL = "SELECT user_name, user_email FROM users";
      stmt = con.createStatement();
      rs = stmt.executeQuery(SQL);

      while (rs.next()) {
        System.out.println(rs.getString(1) + " " rs.getString(2));
      }
    
    }catch(Exception e){

      e.printStackTrace();
    
    }finally{
    
      if(rs != null) try { rs.close(); } catch(Exception e) {}
      if(stmt != null) try { stmt.close(); } catch(Exception e) {}
      if(con != null) try { con.close(); } catch(Exception e) {}
    
    }

  }

}

PREPARED STATEMENT WITH PARAMETER BINDING

You can put any sql code in your statement, which I thought was handy. As you can see the statemet starts with an IF statement and runs a different query depending on the value of a param passed in. Also note that the connection does not specify a database, so to include that by prefixing the table name in SQL:

package database;

import java.sql.*;


public class TestJDBC {

  public void test(){

    String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
      "databaseName=testDB;user=testuser;password=xxxxxxx";

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
    
      //establish connection
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      con = DriverManager.getConnection(connectionUrl);

      // prepare the statement
      String SQL = 
      "DECLARE @test INT = ?\n" +
      "IF (@test IS NULL)\n" + 
      "  SELECT TOP 10 UserFirstname,UserLastName FROM [TestDB].dbo.Users" + 
      "  WHERE UserFirstname Like 'T%'\n" +
      "ELSE\n" +
      "  SELECT TOP 10 UserFirstname,UserLastName FROM [TestDB].dbo.Users" + 
      "  WHERE UserFirstname Like 'J%'\n";

      
      pstmt = con.prepareStatement(SQL); 
      pstmt.setNull(1,1); // To pass in Null
      //pstmt.setInt(1,1); // To pass in a non null value

      // run it and parse results
      rs = pstmt.executeQuery();

      while (rs.next()) {
        System.out.println(rs.getString(1) + ":" + rs.getString(2));
      }
    
    }catch(Exception e){

      e.printStackTrace();
    
    }finally{
    
      if(rs != null) try { rs.close(); } catch(Exception e) {}
      if(pstmt != null) try { pstmt.close(); } catch(Exception e) {}
      if(con != null) try { con.close(); } catch(Exception e) {}
    
    }
  
  }

}