SQL with Java – JDBC Connectivity

Post Updated on | By hanucodes@gmail.com | 2 comments
–Section–—SP Statements–
Part 1: Java Jdbc BasicsStatement Types, Methods and Stored Procedures
Part 2: Execute SQL Queries with JavaConnection, Statement, PreparedStatement
Part 3: Call Stored Procedures in JavaStatement, PreparedStatement, CallableStatement
Part 4: Batch Execution and Bulk CopyJava Batch and Bulk Copy
[SQL Stored Procedures]SQL Stored Procedures Cheat Sheet
[SQL and TSQL Queries]SQL and TSQL Queries Cheat Sheet

Download this Cheat Sheet:

Part 1: Java JDBC Basics – Statement Types, Methods and Stored Procedures

ClassTypeStatementMeaning
GeneralSP Return Typesthere are 3 ways to return some data from stored procedure

1. return statement
   – can return only 1 number

2. output parameters
   – can return multiple numbers/string
   – cannot return a tableValued output Parameter
   – table valued parameters are input and read only

3. select statement
   – will return the tabular data (ResultSet in Java)

4. select + update statements
   – SP can have multiple select / update statements
   – will return multiple ResultSets / updateCounts in Java
GeneralStatement Typesthere are 3 types of Statements to use in 3 scenarios

1. Statement
   – required for no parameters

2. PreparedStatement
   – required for input parameters

3. CallableStatement
   – required for output parameters in SP

GeneralStatement Methodsthere are 3 main types of Statement methods

1. executeQuery()
   – required to run DQL or SP
   – gets a single resultSet

2. executeUpdate()
   – required to run DML or DDL
   – gets updateCount

3. execute()
   – required to get any combo of DQL, DML or SP
   – gets multiple resultSet and updateCount
NoteStored Procedure Stored procedures can return update counts and multiple result sets.

Multiple result sets and update counts should be retrieved before the OUT parameters are retrieved.

That is, the Java code should retrieve all of the ResultSet objects and update counts before retrieving the OUT parameters by using the CallableStatement.getter methods.

Otherwise, the ResultSet objects and update counts that haven’t already been retrieved will be lost when the OUT parameters are retrieved.

Part 2: Execute SQL Queries with Java – Connection, Statement, PreparedStatement

ClassTypeStatementMeaning
ConnectioncreateClass.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);  

String connectionUrl = “jdbc:sqlserver://localhost;encrypt=true;database=hc;”  

Connection con = DriverManager.getConnection(connectionUrl, dbUser, dbPass);  		create connection to any type of database
Connectionclosecon.close();preferably done in finally block
StatementDQL
without parameters		String SQL = “select lname, fname, … “+
             “from person order by lname”;

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(SQL);

while (rs.next()) {
  String last = rs.getString(“lname”);
  String first = rs.getString(“fname”));
  …
}		Statement can be used to get any ResultSet from a query where no parameters are required
Prepared StatementDQL
with parameters		String SQL = “select lname, fname, … “+
             “from person where lname = ?”;

PreparedStatement pstmt = con.prepareStatement(SQL);
pstmt.setString(1, “Smith”);

ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
  String last = rs.getString(“lname”);
  String first = rs.getString(“fname”));
  …
}		PreparedStatement is required if parameters are being passed into the SQL Query from Java

? represents the parameters in the query

Parameters are set by their number
StatementDDL
without parameters		String SQL = “CREATE TABLE TestTable (Col1 int IDENTITY, Col2 varchar(50), Col3 int)”;

Statement stmt = con.createStatement();
int count = stmt.executeUpdate(SQL);

System.out.println(“rows affected: ” + count);		rows affected count for DDL will be 0
StatementDML
without parameters		String SQL = “INSERT INTO TestTable (Col2, Col3) VALUES (‘a’, 10)”;

Statement stmt = con.createStatement();
int count = stmt.executeUpdate(SQL);

System.out.println(“rows affected: ” + count);		rows affected will provide the number of rows inserted / updated.
Prepared StatementDML
with parameters		String SQL = “INSERT INTO TestTable (Col2, Col3) VALUES (?, ?)”;

PreparedStatement pstmt = con.prepareStatement(SQL);
pstmt.setString(1, “a”);
pstmt.setString(2, 10);

int count = pstmt.executeUpdate(SQL);

System.out.println(“rows affected: ” + count);		If you must use an SQL statement that contains parameters to modify the data in a SQL Server database, you should use the executeUpdate method of the PreparedStatement class.

Part 3: Call Stored Procedures in Java – Statement, PreparedStatement, CallableStatement

ClauseTypeStatementMeaningStored Prod
StatementSP
without params		Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(
  “{call dbo.GetDisplayNames}”
);

while (rs.next()) {  
  System.out.println(rs.getString(“FormalName”));  
}  		stored procedure
– has no inputs and
– executes a select query for returning a restlt set		create procedure
GetDisplayNames
as begin  
   select fname+’ ‘+lname
   as displayName
   from people  
end
Prepared StatementSP
with input params		PreparedStatement pstmt = con.prepareStatement(
  “{call dbo.GetDisplayNames(?)}”
);

pstmt.setInt(1, 50);  
ResultSet rs = pstmt.executeQuery();  

while (rs.next()) {
  String last = rs.getString(“lname”);
  String first = rs.getString(“fname”));
  …
}		Prepared Statement is required for passing input parameterscreate procedure
GetDisplayNames
(@personid int)
as begin  
   select fname,lname
   from people
   where id = @personid
end
Callable StatementSP
with output params		CallableStatement cstmt = con.prepareCall(
   “{call dbo.GetManager(?, ?)}”
);

cstmt.setInt(“employeeID”, 5);  
cstmt.registerOutParameter(“managerID”,
                            java.sql.Types.INTEGER);  
cstmt.execute();  

System.out.println(“ManagerID:”+cstmt.getInt(“managerID”));  		Callable Statement is required for getting output parameters.

execute method is used because the stored procedure did not also return a result set. If it did, the executeQuery method would be used.		create procedure
GetManager(  
   @employeeID int,  
   @managerID int output
) as
begin
   select @managerID = mgr_id
   from employee
   where emp_id = @employeeID  
end
Callable StatementSP
with return		CallableStatement cstmt = con.prepareCall(
  “{? = call dbo.ValidateCity (?)}”
);

cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, “Atlanta”);
cstmt.execute();

System.out.println(“City Valid: ” + cstmt.getInt(1));		setters and getters on Statements can either use numbers (in the order) or the name of the variable in SP

for return, we always have to use number sinc ethere is no variable name in SP		create procedure
ValidateCity
(@cityName char(50))
as begin
  declare @cityCount = (
  select count(*)
  from Person
  where city = @cityName);

  if(@cityCount > 1)
    return 1
  else
    return 0
end
Callable StatementSP
with update count		CallableStatement cstmt = con.prepareCall(
  “{call dbo.UpdateTable(?, ?)}”
);

cstmt.setString(1, “A”);
cstmt.setInt(2, 100);
cstmt.execute();

int count = cstmt.getUpdateCount();
System.out.println(“rows affected:” + count);		if we do not want to return update counts from stored procedure,
use the set no count statement inside SP.		create procedure
UpdateTable  
   @Col2 varchar(50),  
   @Col3 int  
as begin  
   update TestTable  
   set Col2 = @Col2,
       Col3 = @Col3  
end;  
executeSP + SQL
multiple RS + UC		Statement stmt = con.createStatement();
String sqlWithUnknownResults =
  “{call dbo.uspGetEmployeeManagers(50)};”+
  “SELECT TOP 10 * FROM Person.Contact”;

boolean results = stmt.execute(sqlWithUnknownResults);
int count = 0;
do {
  if (results) {
    ResultSet rs = stmt.getResultSet();
    //Result set data displayed here.
  } else {
    count = stmt.getUpdateCount();
    if (count >= 0) {
      //DDL or update data displayed here.
    } else {
      //No more results to process.
    }
  }
  results = stmt.getMoreResults();
}
while (results || count != -1);		inline SQL or SP can return more than one result set

execute() will return a boolean value
true = return is one/more result sets
false = return is an update count.
this is only to determine first result

getResultSet() to access the first result set

getUpdateCount() to access the update count value

getMoreResults() will return boolean value
true = more result sets are available.
false = no more result sets to process

If more result sets are available, you can call the getResultSet method again to access them, continuing the process.
executeSP
multiple RS		Statement stmt = con.createStatement();
String SQL = “SELECT TOP 10 * FROM Person.Contact;”+
             “SELECT TOP 20 * FROM Person.Contact”;

boolean results = stmt.execute(SQL);
int rsCount = 0;

// Loop through the available result sets.
do {
  if (results) {
    ResultSet rs = stmt.getResultSet();
    rsCount++;

    // Show data from the result set.
    System.out.println(“RESULT SET #” + rsCount);
    while (rs.next()) {
      String last = rs.getString(“lname”);
      String first = rs.getString(“fname”));
    }
  }

  results = stmt.getMoreResults();
} while (results);		same as above

Part 4: Batch Execution and Bulk Copy in Java

ClauseTypeStatementMeaning
StatementBatchStatement stmt = con.createStatement();

stmt.addBatch(“insert into TestTable (Col2, Col3) VALUES (‘X’, 100)”);

stmt.addBatch(“insert into TestTable (Col2, Col3) VALUES (‘Y’, 200)”);

stmt.addBatch(“insert into TestTable (Col2, Col3) VALUES (‘Z’, 300)”);

int[] updateCounts = stmt.executeBatch();

stmt.close();		The Statement, PreparedStatement, and CallableStatement can all be used to submit batch updates.

addBatch() method is used to add a command.

clearBatch() method is used to clear the list of commands.

executeBatch() method is used to submit all commands for processing.

Only (DDL) and (DML) statements that return a simple update count can be run as part of a batch.

The executeBatch() method returns an array of int values that correspond to the update count of each command.
Prepared StatementBatch + Bulk CopyConnection con = DriverManager.getConnection(
  “jdbc:sqlserver:host:port;database=mydb;”+
  “useBulkCopyForBatchInsert=true;”);

//or use con.setUseBulkCopyForBatchInsert()
//to enable bulk copy with batch

Connection con = DriverManager.getConnection(
  connectionUrl, user, password);

PreparedStatement pstmt = con.prepareStatement(
  “insert into ” + tableNameBulkCopyAPI +
  ” values (?, ?)”);

for (int i = 0; i < 1000; i++) {
  pstmt.setInt(1, i);
  pstmt.setString(2, “test” + i);
  pstmt.addBatch();
}

pstmt.executeBatch();
Microsoft JDBC Driver for SQL Server supports using the Bulk Copy API for batch insert operations.

This feature allows users to enable the driver to do Bulk Copy operations underneath when executing batch insert operations to improve performace.

Pre-requisite:

1. The query must be an insert query
2. Wildcards (?) are the only supported parameters
SQLServerBulkCopyBulk Copy//source and target connections
Connection sourceConnection = DriverManager.getConnection(sourceUrl);
Connection targetConnection = DriverManager.getConnection(targetUrl);

//get data from source table into resultset
Statement stmt = sourceConnection.createStatement();
ResultSet rsSourceData = stmt.executeQuery(
  “SELECT product_id, name, cost “+
  “FROM Product”);

//setup bulk copy options
SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setBulkCopyTimeout(1000); //seconds
copyOptions.setBatchSize(100);
            
//setup sql server bulk copy
SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection);
bulkCopy.setDestinationTableName(destinationTable);
bulkCopy.setBulkCopyOptions(copyOptions);

//setup column mappings between source and target tables
bulkCopy.addColumnMapping(“product_id”, “product_id”);
bulkCopy.addColumnMapping(“name”, “name”);
bulkCopy.addColumnMapping(“cost”, “cost”);

bulkCopy.writeToServer(rsSourceData);		The SQLServerBulkCopy class can be used to write data only to SQL Server tables.

But the data source isn’t limited to SQL Server; any data source (like Oracle) can be used

Source Data should be read with a ResultSet and passed to BulkCopy API.
SQLServerBulkCopyTSQL Bulk CopyStatement stmt = con.createStatement()) {
stmt.executeUpdate(
  “bulk insert Orders “+
  “from ‘f:\\mydata\\data.tbl’ “+
  “with ( FORMATFILE=’f:\\mydata\\data.fmt’ )”);
SQLServerBulkCopyBulk Copy from CSVtodo

[Download PDF – SQL with Java]

Category: Uncategorised

2 comments on SQL with Java – JDBC Connectivity

  1. Pingback: SQL Queries Cheat Sheet PDF - Helper Codes
  2. Pingback: SQL Stored Procedure Cheat Sheet PDF - Helper Codes

Leave a Reply

Your email address will not be published. Required fields are marked *