Java - JDBC Code Examples for MSSQL

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

}

}

}

 

 

Add a Comment...

 

Comments

vbex 420dating canadagame golibertytech sptools doubledubs automaintenancetips minigamesgalaxy minigamesmonster haigaoren - 5/09/2017

Hi, yup this paragraph is genuinely good and I have learned lot of things from it on the topic of blogging. thanks. maglie calcio - 4/26/2017