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