–Section– | —SP Statements– |
Part 1: Java Jdbc Basics | Statement Types, Methods and Stored Procedures |
Part 2: Execute SQL Queries with Java | Connection, Statement, PreparedStatement |
Part 3: Call Stored Procedures in Java | Statement, PreparedStatement, CallableStatement |
Part 4: Batch Execution and Bulk Copy | Java Batch and Bulk Copy |
[SQL Stored Procedures] | SQL Stored Procedures Cheat Sheet |
[SQL and TSQL Queries] | SQL and TSQL Queries Cheat Sheet |
Download SQL with Java Sheet Free!
![](http://helpercodes.com/wp-content/uploads/2024/08/sql-kit-banner.jpeg)
Part 1: Java JDBC Basics – Statement Types, Methods and Stored Procedures
Class | Type | Statement | Meaning |
---|---|---|---|
General | SP Return Types | there 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 | |
General | Statement Types | there 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 | |
General | Statement Methods | there 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 | |
Note | Stored 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
Class | Type | Statement | Meaning |
---|---|---|---|
Connection | create | Class.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 |
Connection | close | con.close(); | preferably done in finally block |
Statement | DQL 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 Statement | DQL 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 |
Statement | DDL 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 |
Statement | DML 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 Statement | DML 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
Clause | Type | Statement | Meaning | Stored Prod |
---|---|---|---|---|
Statement | SP 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 Statement | SP 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 parameters | create procedure GetDisplayNames (@personid int) as begin select fname,lname from people where id = @personid end |
Callable Statement | SP 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 Statement | SP 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 Statement | SP 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; |
execute | SP + 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. | |
execute | SP 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
Clause | Type | Statement | Meaning |
---|---|---|---|
Statement | Batch | Statement 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 Statement | Batch + Bulk Copy | Connection 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 |
SQLServerBulkCopy | Bulk 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. |
SQLServerBulkCopy | TSQL Bulk Copy | Statement stmt = con.createStatement()) { stmt.executeUpdate( “bulk insert Orders “+ “from ‘f:\\mydata\\data.tbl’ “+ “with ( FORMATFILE=’f:\\mydata\\data.fmt’ )”); | |
SQLServerBulkCopy | Bulk Copy from CSV | todo |
2 comments on SQL and JAVA Connection using JDBC – Free Download