1. What is Exception Handling in JDBC? 2. How to fix SQLException in JDBC? 3.SQLException Programming Example
What is Exception Handling in JDBC?
Exception is such type of condition when program encounters problem in execution and quit with a problematic error message. In JDBC, when program gets trouble in data source, it throws SQLException. However, there are several many exceptions in Java but while we are studying JDBC, SQLException is most common and we will cover it through this chapter.
A SQLException can occur in JDBC Driver or inside database. In this chapter you will learn how to handle this exception.
Gets the error number associated with the exception.
Gets the JDBC driver’s error message for an error, handled by the driver or gets the Oracle error number and message for a database error.
Gets the XOPEN SQLstate string. For a JDBC driver error, no useful information is returned from this method. For a database error, the five-digit XOPEN SQLstate code is returned. This method can return null.
Gets the next Exception object in the exception chain.
Prints the current exception, or throwable, and it’s backtrace to a standard error stream.
Prints this throwable and its backtrace to the print stream you specify.
Prints this throwable and it’s backtrace to the print writer you specify.
1. What is Callable Statement in JDBC? 2. How to use Store Procedure in JDBC? 3. Programming Example
What is Callable Statement in JDBC?
The CallableStatement Interface is used in accessing and executing Store Procedure and Function. Store Procedure is a group of SQL Statement that encapsulates all the queries and gets compiled. Once store procedure gets compiled it can be executed so many times without compilation. This makes it robust, faster and high performing. It accepts parameter for sql queries that is hidden inside it and gets executed. The Callable Statement in JDBC is used to manipulating these store procedure.
How to use Callable Statement to Call Store Procedure?
Here, I am going to present a simple callable statement example. This example includes following steps.
1. First step is creating a store procedure. I will create a store procedure to insert data into ITEM table. 2. Second Step is Using CallableStatement, I will execute this store procedure and insert data to table.
1. Store Procedure: Creating Store Procedure
You need to execute this statement in your database server sql window. Here, I am using MySQL and used PHPMyAdmin page to create this store procedure.
In this chapter you learned how to use CallableStatement in JDBC to execute store procedure in MySQL Database. I have kept this tutorial simple and clean with complete programming example. Hope, this will help you to understand basics of callable statement in JDBC.
1. What is Batch Processing in JDBC? 2. How to Batch Processing in JDBC? 3. Methods and Programming Example
What is Batch Processing in JDBC?
The word “Batch Processing” tells everything itself by its name. Process of executing multiple queries at once is called Batch Processing. Batch Processing makes the performance fast and efficient, that executes multiple sql statements quickly. JDBC Batch Processing provides method for implementing bulk query execution.
1. What is Transaction Management in JDBC? 2. Advantage of Transaction Management 3. Programming Example
What is Transaction Management in JDBC?
Transaction Management works great when you need to execute set of task and each task executes when previous task completes. Transaction Management ensures that all the task executes successfully and if one task fails, the whole task would be rollback to previous state. Simply, either all the statements are executed, or none of the statements is executed.
Fact About Transaction
1. ACID: It represents ACID Properties.
Atomicity means either all successful or none.
Consistency ensures bringing the database from one consistent state to another consistent state.
Isolation ensures that transaction is isolated from other transaction.
Durability means once a transaction has been committed, it will remain so, even in the event of errors, power loss etc.
2. Disable Auto Commit Mode: When the connection is created it is auto commit mode. It means all the individual SQL Statements will be treated as Transaction. When using Transaction, Disable Auto Commit Mode and call it explicitly. After disabling commit mode, no statement will be execute until you call Commit() method explicitly. Each statement will be executed after the previous call of Commit method.
In JDBC, Connection interface provides methods to manage transaction.
void setAutoCommit (boolean status)
It is true bydefault means each transaction is committed bydefault.
commits the transaction.
cancels the transaction.
Advantage of Transaction Management
1.Transaction Management widely used in financial application where you want to ensure that if any problem happens meanwhile the payment process, the whole transaction rollback. 2.Transaction Management is also very beneficial when you need to insert multiple row in multiple table simultaneously. It ensures that all the table gets successful execution of statement and if any table fails to execute statement, all the row roll backed and none of table affected. 3.Transaction Treats all the SQL statements as a single logical unit and if one statement fails the entire transaction fails. 4.Commit and Rollback : Commit() method does the changes in database table and Rollback methods undo all the changes done by current connection con.
In this programming example, I will insert two row in a two different table using Transaction. This example explains Commit() method. In the next example you will get Rollback.
Step 1. Create Two Tables table1 and table2 with following description
You can use your own table to execute query. If you don’t have any, then create table using above query.
In this example I will use Rollback method to ensure all the successful insertion in both table. You will see what happen when statement execution fails in table 2.
Example 1: With Commit(false)
In this program I have Passed long value in PRICE column for table2. It will raise exception because of size of PRICE is set to varchar(10). This is for showing you what happened when one query executed but another get failed in transaction. You will notice that there were no changes in table.
Output Table1 Successfull Cannot connect ! com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘PRICE’ at row 1
Example 2: Without Commit() method or Commit(true)
See this example. In this example I haven’t set Commit(false) so when the program executes query, first statement successfully inserted but raise exception in second statement. It is because I have passed more than 10 character long in PRICE that is set to varchar(10) only.
Table1 Successfull Cannot connect ! com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column ‘PRICE’ at row 1
In this tutorial you learned what is Transaction Management in JDBC and how to use it in Programming. The Commit(false) ensures all the successful execution of statement. If any of them not execute, the whole process gets failed. Rollback() method reverse all the changes that is made by current connection.
1. What is ResultSetMetaData? 2. Programming Example
What is ResultSetMetaData
ResultSetMetaData object is used to collect all the information of ResultSet like types and properties of the column, number of column, name of column, datatype of column etc. In simple words, It is used to gather additional information of ResultSet.
Commonly Used Method of ResultSetMetaData
getColumnCount() – Tell Total Column in Table getColumnName(int Column_Index) – Tell Column Name of Column Index. getColumnTypeName(int Column_Index) – Tells Column Data Type getTableName(int Table_Index) – Tells Table Name
1. What is DatabaseMetaData in JDBC? 2. How to use DatabaseMetaData to gather Database Info
What is DatabaseMetaData in JDBC?
The DatabaseMetaData provides comprehensive details about Database. It is maintained by driver vendors to let users the capabilities of DBMS. In simple word, this interface tells you everything about database like product name, version, total table number, structure of table, structure of database, name of views inside database etc.
Most commonly used methods
Retrieves the major version number of the underlying database.
Retrieves the name of this database product.
Retrieves the name of this JDBC driver.
Retrieves the maximum number of characters this database allows for a column name.
Retrieves the maximum number of columns this database allows in a table.
Retrieves the maximum number of concurrent connections to this database that are possible.
Retrieves the URL for this DBMS.
Retrieves the user name as known to this database.
Retrieves whether this database supports batch updates.
How to use DatabaseMetaData to gather Database Info
Product Name : MySQL Product Version : 5.7.17-0ubuntu0.16.04.1 Driver Name : MySQL Connector Java Logged User : [email protected] Driver Versionmysql-connector-java-5.1.40 ( Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 ) Max Column in Table 512 Supports Batch Update true Closing the connection. _
In this chapter you learned how to use DatabaseMetaData object to gather MySQL database info. This info is very helpful when you are database administrator or RDBMS Designer. The next chapter is ResultSetMetaData in JDBC.
1. What is PreparedStatement in Java? 2. How it is used for storing or updating data? 3. Programming Example
What is PreparedStatement?
The PreparedStatement object is derived from Statement class. Statement is used for sending and executing query in database but it is more convenient to use PreparedStatement to send and execute command in database. PreparedStatement uses parameterized query and keeps precompiled sql statement so it is faster than Statement class because it is compiled once and can be used multiple times.
PreparedStatementps=con.prepareStatement("INSERT INTO ITEM VALUES(?,?)");
PreparedStatement is used to execute parameterized query. It uses ? parameter at the place of value and later store value in it. As we initialize PreparedStatement, it gets compiled and then can be used so many times directly. Once database engine compiles PreparedStatement, It doesn’t get compiled next time and executed directly. It makes it much efficient and faster than Statement class.
This method executes sql query in PreparedStatement and returns ResultSet.
This method executes sql query in PreparedStatement and used for Only with Insert, Update or Delete command or sql statement that return nothing.
public void setInt(int paramIndex, int value)
sets the integer value to the given parameter index.
public void setString(int paramIndex, String value)
sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value)
sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value)
sets the double value to the given parameter index.
ID : 1, PRODUCT : MousePad, PRICE : 190 ID : 2, PRODUCT : Stationary, PRICE : 2870 ID : 3, PRODUCT : Books, PRICE : 765 ID : 4, PRODUCT : HardDisk, PRICE : 3887 ID : 5, PRODUCT : Ram, PRICE : 953 ID : 6, PRODUCT : Printer, PRICE : 8746 ID : 7, PRODUCT : Keyboard, PRICE : 646 ID : 8, PRODUCT : Mouse, PRICE : 947 ID : 9, PRODUCT : HDMI CABLE, PRICE : 850 ID : 11, PRODUCT : Charger, PRICE : 1800 ID : 12, PRODUCT : MainJava Logo, PRICE : 900 ID : 14, PRODUCT : JDBC eBook, PRICE : 830 Closing the connection.
In this tutorial you learned how to use PreparedStatement for Storing, Retrieving, Updating or Deleting row in MySQL table using JDBC. This chapter includes all the programming examples that demonstrate all the theory well.
It is very easy to use Statement Interface in JDBC programming for executing queries in database. However, it is not efficient way to run query in database. Instead of Statement you can use PreparedStatement, which is efficient and faster than Statement Object. In the next chapter you will learn about PreparedStatement in JDBC.
1. How to insert row in Database Table using JDBC RowSet
This chapter will teach you, how can you use ResultSet to insert row in database table. Always remember in order to update or insert row in database table you need to set ResultSet TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE.
If you don’t set these two parameter ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, then you will get following error message on screen.
com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
It will update 9th row of PRODUCT column with string value ‘HDMI CABLE’ in RowSet. Remember, this update reflects only rowset and it will not reflect in database table until you use updateRow() method.
3. Execute updateRow() command to make changes in database table.
Update Row in MySQL Database using ResultSet Object