| –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 |
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 |
|---|---|---|---|
| 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 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 |
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 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 |
The post Java Database JDBC Connectivity first appeared on Helper Codes.
