JDBC TUTORIAL – Great Learning

Java Database Connectivity (JDBC) is an utility programming interface (API) for the programming language Java for database-independent connectivity between the Java programming language and a variety of databases. It’s a part of the Java Customary Version platform from Oracle Company that gives strategies to question and replace knowledge in a database and is oriented towards relational databases. Any connections to ODBC-accessible knowledge supply within the Java digital machine (JVM) host atmosphere are enabled because of the JDBC-to-ODBC bridge.

Additionally Learn: Java Tutorial

JDBC ARCHITECTURE

WHAT PROCESSING MODELS DOES JDBC API SUPPORT? 

  • Two-tier processing fashions

Within the two-tier mannequin, the JDBC driver permits the communication between the appliance and the info supply. When a consumer sends a question to the info supply, the solutions for these queries are despatched again to the consumer within the type of outcomes. Nonetheless, the info supply is probably not a single machine situated in a single place; it may be discovered on a distinct machine on a community to which a consumer is related. The consumer’s machine acts as a shopper, and the machine has the info supply working that acts because the server. This is called a shopper/server configuration. The community connection may be both intranet or Web.

  • Three-tier processing fashions –

Within the three-tier mannequin, the consumer’s instructions are despatched to middle-tier providers, from which the instructions are once more despatched to the info supply. The outcomes which can be generated are despatched again to the center tier and from there to the consumer. This kind of mannequin is discovered very helpful by administration and structural data system administrators. It makes it easy to take care of entry management and replace company knowledge, and the appliance deployment turns into simple and gives efficiency advantages. The pc language wherein the center tier is normally written in C or C++.

WHAT ARE THE DIFFERENT LAYERS OF JDBC ARCHITECTURE?

  • JDBC API: This gives the application-to-JDBC Supervisor connection, which makes use of a driver supervisor and database-specific drivers to offer clear connectivity to heterogeneous databases.
  • JDBC Driver API: This helps the JDBC Supervisor-to-Driver Connection. It ensures that the proper driver is used to entry every knowledge supply. The JDBC driver supervisor can assist a number of concurrent drivers related to a number of heterogeneous databases.

Structured Question Language (SQL) 

Structured Query Language (SQL) is a standardised language that enables the customers to put in writing database code independently of the underlying database and to carry out operations on a database, corresponding to creating new entries, studying content material, updating content material, and deleting entries.

Additionally Learn: SQL Tutorial

WHAT ARE THE BASIC SQL STATEMENTS THAT ARE REQUIRED TO UNDERSTAND JDBC CONCEPTS? 

  • CREATE DATABASE assertion – used to create new databases.

Syntax – 

SQL> CREATE DATABASE DATABASE_NAME;
  • DROP DATABASE assertion – used for deleting an current database.

Syntax – 

SQL> DROP DATABASE DATABASE_NAME;

Notice: Administrator privilege is required on the respective database server to create or drop a database. Deleting a database would end result within the lack of all the info saved within the database.

  • CREATE TABLE assertion – used for creating new tables.

Syntax – 

<!-- wp:paragraph -->
<p>SQL&gt; CREATE TABLE table_name</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>(</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;column_name column_data_type,</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;column_name column_data_type,</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;column_name column_data_type,</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;column_name column_data_type,</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;column_name column_data_type,</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;column_name column_data_type</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;...</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>);</p>
<!-- /wp:paragraph -->
  • DROP TABLE assertion – used for deleting an current desk.

Syntax – 

SQL> DROP TABLE table_name;

  • INSERT assertion – used to insert knowledge into the respective columns.

Syntax – 

SQL> INSERT INTO table_name VALUES (column1, column2, ...);
  • SELECT assertion – used to retrieve knowledge from a database.

Syntax – 

<!-- wp:paragraph -->
<p>SQL&gt; SELECT column_name, column_name, ...</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM table_name</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE circumstances;</p>
<!-- /wp:paragraph -->

WHERE clause can use the comparability operators corresponding to =, !=, <, >, <=, and >=, and likewise BETWEEN and LIKE operators.

  • UPDATE assertion – used to replace knowledge.

Syntax – 

<!-- wp:paragraph -->
<p>SQL&gt; UPDATE table_name</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET column_name = worth, column_name = worth, ...</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE circumstances;</p>
<!-- /wp:paragraph -->

WHERE clause can use the comparability operators corresponding to =, !=, <, >, <=, and >=, and likewise BETWEEN and LIKE operators.

  • DELETE assertion – used to delete knowledge from tables.

           Syntax – 

SQL> DELETE FROM table_name WHERE circumstances;

WHERE clause can use the comparability operators corresponding to =, !=, <, >, <=, and         >=, and likewise BETWEEN and LIKE operators.

WHAT ARE THE STEPS TO CREATE A JDBC APPLICATION?

The six steps concerned in constructing a JDBC utility are as follows –

  • Import the packages: Packages containing the JDBC lessons wanted for database programming have to be included. import java.sql.*  is used for the respective objective.
  • Register the JDBC driver: A driver needs to be initialised so that a communication channel with the database may be opened.
  • Open a connection: DriverManager.getConnection() technique is used to create a Connection object, which represents a bodily reference to the database.
  • Execute a question: An object of sort Assertion is used for constructing and submitting an SQL assertion to the database.
  • Extract knowledge from the end result set: ResultSet.getXXX()  technique is  used to retrieve the info from the end result set.
  • Clear up the atmosphere: Closing all database sources as a substitute of counting on the JVM’s rubbish assortment.

JDBC DRIVER

WHAT IS THE NEED OF A JDBC DRIVER AND STATE AND DEFINE THE VARIOUS TYPES OF JDBC DRIVER? 

A Java utility wants software program to work together with the database to carry out varied operations, with the assistance of JDBC Driver, the java utility can work together with the database.

  1. JDBC-ODBC bridge driver
  2. Native-API driver (partially java driver)
  3. Community Protocol driver (totally java driver)
  4. Skinny driver (totally java driver)

JDBC-ODBC bridge driver

WHAT IS JDBC-ODBC BRIDGE DRIVER

The JDBC-ODBC bridge driver is a software program that permits java functions to work together with the database by utilizing an ODBC driver. The JDBC-ODBC driver works by changing JDBC technique calls into ODBC perform calls. With the event of skinny drivers the usage of the respective driver has been discontinued (JAVA eight, doesn’t assist the JDBC-ODBC bridge driver.

WHAT ARE THE ADVANTAGES OF JDBC-ODBC BRIDGE DRIVER?

The benefits of the JDBC-ODBC bridge driver are that it’s simple to make use of and it may be simply related to any database.

WHAT ARE THE DISADVANTAGES OF JDBC-ODBC BRIDGE DRIVER?

The disadvantages of the JDBC-ODBC bridge driver are that due to the method of changing JDBC technique calls into ODBC perform name efficiency degraded and the ODBC driver can solely perform by putting in it on the shopper machine.

JDBC-ODBC bridge driver

Native-API driver (partially java driver)

WHAT IS NATIVE-API DRIVER?

The Native-API driver is a software program that permits java functions to work together with the database by utilizing the client-side libraries of the database. The Native-API driver works by changing JDBC technique calls into native calls of the database API. The language wherein it’s written shouldn’t be completely java.

WHAT ARE THE ADVANTAGES OF NATIVE-API DRIVER?

The benefits of the Native-API driver is that there’s an enchancment within the efficiency as in comparison with that of JDBC-ODBC bridge driver. 

WHAT ARE THE DISADVANTAGES OF NATIVE-API DRIVER?

The disadvantages of the Native-API driver is that with a view to use the driving force it must be put in on every shopper machine and the seller shopper library must be put in on the shopper machine. 

Native-API driver (partially java driver)

Community Protocol driver (totally java driver)

WHAT IS NETWORK PROTOCOL DRIVER? 

The Community Protocol driver is a software program that permits java functions to work together with the database by utilizing middleware (utility server). The Community Protocol driver is used for changing JDBC calls instantly or not directly into the vendor-specific database protocol. The language wherein it’s written is completely java.

WHAT ARE THE ADVANTAGES OF NETWORK PROTOCOL DRIVER?

The benefits of the Community Protocol driver is that no shopper aspect library is required due to an utility server that may carry out many duties like auditing, load balancing, logging and many others.

WHAT ARE THE DISADVANTAGES OF NETWORK PROTOCOL DRIVER?

The disadvantages of the Community Protocol driver is that community assist is required on shopper machines, it requires database-specific coding to be accomplished within the center tier and   upkeep of Community Protocol driver turns into expensive as a result of it requires database-specific coding to be accomplished within the center tier. 

Community Protocol driver (totally java driver)

Skinny Driver (totally java pushed)

WHAT IS THIN DRIVER? 

The Skinny Driver is a software program that permits java functions to work together with the database by utilizing middleware (utility server). The Skinny Driver that converts JDBC calls instantly or not directly into the vendor-specific database protocol. The language wherein it’s written is completely java.

WHAT ARE THE ADVANTAGES OF THIN DRIVER?

The benefits of the skinny driver is that no shopper aspect library is required due to an utility server that may carry out many duties like auditing, load balancing, logging and many others. The efficiency of the driving force is best than all different drivers. There is no such thing as a want for software program at shopper aspect or server aspect.

WHAT ARE THE DISADVANTAGES OF THIN DRIVER?

The drawback of the Community Protocol driver is that the drivers rely on the database. 

Skinny Driver (totally java pushed)

RESULT SETS

The SQL statements that return the info in a end result set after studying knowledge from a database question. The usual method to choose rows from a database and look at them in a end result set is SELECT assertion. The end result set of a database question is represented by java.sql.ResultSet interface.

A ResultSet object maintains a cursor, the perform of the cursor is to level the present row within the end result set. The row and column knowledge contained in a ResultSet object is termed as “end result set”.

WHAT ARE THE DIFFERENT CATEGORIES IN WHICH THE METHODS OF THE RESULT SET INTERFACE CAN BE BROKEN DOWN?

  • Navigational strategies:  The navigational technique is used to maneuver the cursor round.
  • Get strategies: The get technique is used to view the info within the columns of the present row being pointed by the cursor.
  • Replace strategies: The replace technique is used to replace the info within the columns of the present row. The updates can then be up to date additional within the respective database.

The properties of the ResultSet are designated when the corresponding Assertion that generates the ResultSet is created, for instance the cursor is movable based mostly on the properties of the ResultSet.

WHAT ARE THE CONNECTION METHODS JDBC PROVIDES TO CREATE STATEMENTS WITH DESIRED RESULT SET? 

  • createStatement(int RSType, int RSConcurrency);
  • prepareStatement(String SQL, int RSType, int RSConcurrency);
  • prepareCall(String sql, int RSType, int RSConcurrency)

WHAT ARE THE DIFFERENT TYPES OF RESULT SET AND STATE THEIR DESCRIPTION?

TYPE DESCRIPTION
ResultSet.TYPE_FORWARD_ONLY The cursor within the resultset can solely transfer ahead within the end result set.
ResultSet.TYPE_SCROLL_INSENSITIVE The cursor within the end result set can scroll ahead and backward, after the creation of the end result set  the respective end result set shouldn’t be delicate to adjustments made by others to the database.
ResultSet.TYPE_SCROLL_SENSITIVE. The cursor within the end result set can scroll ahead and backward, after the creation of the end result set the respective end result set is delicate to adjustments made by others to the database that happen.

Notice: If the ResultSet sort shouldn’t be talked about, then by default the consumer will get the TYPE_FORWARD_ONLY

DEFINE THE VARIOUS CONCURRENCY TYPES OF RESULT SET.

CONCURRENCY DESCRIPTION
ResultSet.CONCUR_READ_ONLY A learn solely end result set is created. (Default)
ResultSet.CONCUR_UPDATABLE An updatable end result set is created.

Notice:  If any Concurrency sort shouldn’t be specified, then by default the consumer will get the CONCUR_READ_ONLY.

WHAT ARE THE COMMONLY USED METHODS OF RESULT SET INTERFACE AND STATE THEIR DESCRIPTION?

METHODS DESCRIPTION
public void beforeFirst()  Is used to maneuver the cursor simply earlier than the primary row.
public void afterLast() Is used to maneuver the cursor simply after the final row.
public boolean first() Is used to maneuver the cursor to the primary row.
public void final() Is used to maneuver the cursor to the final row.
public boolean absolute(int row) Is used to maneuver the cursor to the desired row.
public boolean relative(int row) Is used to maneuver the cursor the given variety of rows ahead or backward, from the place it’s at the moment pointing.
public boolean earlier() Is used to maneuver the cursor to the earlier row. If the earlier row is off the end result set then the tactic returns false.
public boolean subsequent() Is used to maneuver the cursor to the following row. If there aren’t any extra rows within the end result set then the tactic returns false.
public int getRow() Is used to return the row quantity that the cursor is pointing to.
public void moveToInsertRow() Is used to maneuver the cursor to a particular row within the end result set that can be utilized to insert a brand new row into the database. The placement of the present cursor is remembered.
public void moveToCurrentRow() Is used to maneuver the cursor again to the present row if the cursor is at the moment on the insert row; in any other case, this technique does nothing.

WHAT ARE THE METHODS THAT ARE USED TO VIEW THE RESULT SET AND STATE THEIR DESCRIPTIONS?

The ResultSet interface accommodates get technique for every of the attainable knowledge sorts, and every get technique has two variations – 

  • One which takes in a column identify (String columnName).
  • One which takes in a column index (int columnIndex)
METHODS DESCRIPTION
public int getInt(String columnName) Is used to return the int within the present row within the column named columnName.
public int getInt(int columnIndex) throws SQLException Is used to return the int within the present row within the specified column index. The column index begins at 1, that means the primary column of a row is 1, the second column of a row is 2, and so forth.

HOW TO UPDATE RESULT SET?

The ResultSet interface consists of get strategies for updating the info of a end result set, for every knowledge sort there are two replace strategies −

  • One which takes in a column identify (String columnName).
  • One which takes in a column index (int columnIndex)
METHODS DESCRIPTION 

public void updateString(int columnIndex, String s)
Is used to vary the String within the specified column to the worth of s.
public void updateString(String columnName, String s) Is used to vary the String within the specified column to the worth of s, besides that the column is specified by its identify as a substitute of its index.

Whereas updating a row within the end result set, columns of the present row within the ResultSet object are modified, however these adjustments should not made within the underlying database. Strategies to replace your adjustments to the row within the database –

METHODS DESCRIPTION

public void updateRow()
Is used to replace the present row by updating the corresponding row within the database.
public void deleteRow() Is used to delete the present row from the database
public void refreshRow() Is used to refresh the info within the end result set to replicate any current adjustments within the database.
public void cancelRowUpdates() Is used to cancel any updates made on the present row.
public void insertRow() Is used to insert a row into the database. The situation wherein the tactic may be invoked is when the cursor is pointing to the insert row.

Knowledge Sorts

The Java knowledge sort is first transformed to the suitable JDBC sort, earlier than sending it to the database, by the JDBC driver.A default mapping is utilized by the driving force for many knowledge sorts (Java int is transformed to an SQL INTEGER). To take care of consistency between drivers default mappings are created.

TABULATE THE VARIOUS JDBC DATA TYPE AND THE JAVA DATA TYPE THEY ARE CONVERTED INTO WHEN setXXX () METHOF OF PREPARED STATEMENT OR CALLABLE STATEMENT OBJECT OR THE ResultSet.updateXXX ()  METHOD ARE CALLED AND ALSO SHOW THE CORRESPONDING getXXX() METHOD FOR EACH DATATYPE.

SQL JDBC/JAVA setXXX updateXXX getXXX
VARCHAR java.lang.String setString updateString getString
CHAR java.lang.String setString updateString getString
LONGVARCHAR java.lang.String setString updateString getString
BIT Boolean setBoolean updateBoolean getBoolean
NUMERIC java.math.BigDecimal setBigDecimal updateBigDecimal getBigDecimal
TINYINT byte setByte updateByte getByte
SMALLINT brief setShort updateShort getShort
INTEGER int setInt updateInt GetInt
BIGINT lengthy setLong updateLong getLong
REAL float setFloat updateFloat getfloat
FLOAT float setFloat updateFloat getFloat
DOUBLE double setDouble updateDouble getDouble
VARBINARY byte[] setBytes updateBytes getBytes
BINARY byte[] setBytes updateBytes getBytes
DATE java.sql.Date setDate updateDate getDate
TIME java.sql.Time setTime updateTime getTime
TIMESTAMP java.sql.Timestamp setTimestamp updateTimestamp getTimestamp
CLOB java.sql.Clob setClob updateClob getClob
BLOB java.sql.Blob setBlob updateBlob getBlob
ARRAY java.sql.Array setARRAY updateARRAY getARRAY
REF java.sql.Ref setRef updateRef getRef
STRUCT java.sql.Struct setStruct updateStruct getStruct

HOW TO HANDLE SQL NULL VALUES IN JAVA?

To correctly deal with the SQL null values in JAVA we must always maintain the given factors

  1. The consumer ought to keep away from the usage of getXXX() technique, as these strategies are used to return primitive knowledge sorts. 
  2. The consumer ought to encourage the usage of wrapper lessons for knowledge sorts which can be primitive in nature.

Transactions 

Each SQL assertion is dedicated to the database upon its completion, as JDBC Connection is in auto-commit mode by default.

WHAT ARE THE BENEFITS OF ENABLING TRANSACTIONS?

The consumer ought to flip off the auto-commit and handle your individual transactions as a result of; firstly the efficiency of the functions will increase, secondly it maintains the integrity of enterprise processes and final however not the least the distributed transactions may be enabled. 

Enabling transactions permits the consumer to regulate if, and when, adjustments are utilized to the respective database. A single SQL assertion or a gaggle of SQL statements are handled as one logical unit due to which the entire transaction fails if any one of many SQL statements fails.

Handbook- transaction assist may be enabled by utilizing the Connection object’s setAutoCommit() technique. Auto-commit may be turned off by passing boolean false to setAutoCommit().  To show it again on once more the consumer can move boolean true.

STATE THE USE OF COMMIT AND ROLLBACK METHOD.

  1. commit() technique on connection object may be referred to as if the consumer desires to commit the adjustments  
  2. rollback() technique may be referred to as if the consumer desires to rollback updates to the database utilizing the respective connection used to make the updates.

WHAT ARE SAVEPOINTS?

If the consumer desires further transactional management, the Savepoint interface supplied by JDBC three.zero can be utilized. Savepoints are supported by most trendy DBMS inside their environments corresponding to Oracle’s PL/SQL.

A logical rollback level is outlined inside a transaction when a savepoint is outlined by the consumer. Rollback technique can be utilized to undo both all of the adjustments or solely the adjustments made after the savepoint, when an error happens previous a savepoint.

STATE THE DIFFERENT METHODS THAT THE USER CAN USE TO MANAGE SAVEPOINTS BY IMPLEMENTING THE CONCEPT OF CONNECTION OBJECT.

  • setSavepoint(String savepointName): A brand new savepoint is outlined and likewise a Savepoint object is obtained after the execution of the SQL assertion.
  • releaseSavepoint(Savepoint savepointName):  The respective savepoint is deleted. The situation which is required is that the Savepoint object needs to be declared as a parameter. 

Rollback technique will also be used to rollback work to the desired savepoint by utilizing  rollback (String savepointName) technique.

Exceptions

WHAT ARE EXCEPTIONS?

Distinctive circumstances corresponding to program-defined errors may be in a managed style by the consumer, utilizing exception dealing with.

An exception is thrown when an exception situation happens. The execution of present program stops

The 2 attainable outcomes are –

  • When a nearest relevant catch clause exists, then the management is redirected in the direction of it.
  • When no relevant catch clause exists, then this system’s execution ends. 

The most typical exception that the consumer can face is java.sql.SQLException.

WHAT ARE SQL EXCEPTIONS?

When SQLException happens both within the driver or database, an object of sort SQLException might be handed to the catch clause.

WHAT ARE THE METHODS IN WHICH THE USER CAN RETRIEVE ADDITIONAL INFORMATION ABOUT THE PASSED SQL EXCEPTIONS AND STATE THEIR DESCRIPTION?

METHOD DESCRIPTION
getErrorCode( ) The consumer can retrieve the error quantity related to the exception.
getMessage( ) The consumer can retrieve the JDBC driver’s error message for an error, dealt with by the driving force or will get the Oracle error quantity and message for a database error. 
getSQLState( ) The consumer can retrieve the XOPEN SQLstate string. When a JDBC driver error is said after the execution, no helpful data is returned from this technique. When a database error is said after execution, the five-digit XOPEN SQLstate code is returned. The strategy may also return null.
getNextException( ) The consumer can retrieve the following Exception object within the exception chain.
printStackTrace( ) The consumer can print  the present exception, or throwable, and it’s backtrace to an ordinary error stream.
printStackTrace(PrintStream s) The consumer can print this throwable and it’s backtrace to the print stream you specify.
printStackTrace(PrintWriter w) The consumer can print  this throwable and it’s backtrace to the print author you specify.

Batch Processing

Customers can group the associated SQL statements right into a batch and submit them with one name to the database by utilizing Batch Processing.

The consumer can ship a number of SQL statements to the database without delay, due to which the  quantity of communication reduces and because of this there may be an enchancment within the efficiency.

HOW TO CHECK IF JDBC DRIVER SUPPORTS BATCH PROCESSING? 

The consumer decide if the goal database helps batch replace processing by utilizing  the DatabaseMetaData.supportsBatchUpdates() technique. If the respective JDBC driver helps the characteristic then the tactic returns true.

  • The consumer can add particular person statements to the batch by utilizing 
    • addBatch() technique of Assertion,
    • addBatch() technique of PreparedStatement
    • addBatch() technique of CallableStatement 
  • The consumer can execute of all of the statements which can be grouped collectively by utilizing executeBatch().The perform of executeBatch() is to  return an array of integers, the up to date rely for the respective replace assertion is being represented by every factor of the array.
  • All of the statements which can be added utilizing the addbatch() technique may be eliminated by utilizing clearbatch() technique, however the consumer can’t choose a selected assertion as a result of the tactic shouldn’t be selective in nature.

Batching with Assertion Object

WHAT ARE THE STEPS REQUIRED FOR USING BATCH PROCESSING WITH STATEMENT OBJECT?

  • The consumer first has to create a Assertion Object. The respective job may be accomplished with the assistance of createStatement() strategies.
  • Now by utilizing setAutoCommit() the consumer has to set the worth of auto-commit to false. 
  • The consumer can add any desired variety of SQL statements right into a batch by executing addBatch() technique on the created assertion object. 
  •  Now the consumer has to execute the all of the SQL statements by utilizing executeBatch() technique on created assertion object.
  • The ultimate step is to commit all of the adjustments which can be made by utilizing commit() technique.

Batching with PrepareStatement Object

WHAT ARE THE STEPS REQUIRED FOR USING BATCH PROCESSING WITH PREPARE STATEMENT OBJECT?

  1. The consumer first has to create SQL statements with placeholders.
  2. Now by utilizing prepareStatement() strategies the consumer has to create PrepareStatement object.
  3. Now by utilizing setAutoCommit() the consumer has to set the worth of auto-commit to false. 
  4. The consumer can add any desired variety of SQL statements right into a batch by executing addBatch() technique on the created assertion object. 
  5. Now the consumer has to execute the all of the SQL statements by utilizing executeBatch() technique on created assertion object.
  6. The ultimate step is to commit all of the adjustments which can be made by utilizing commit() technique.

Statements and Saved Process

After establishing the reference to the database, the consumer can use the JDBC Assertion strategies of, CallableStatement, and PreparedStatement interfaces to ship SQL or PL/SQL instructions and obtain knowledge out of your database.

Interfaces Advisable Use
Assertion When the consumer accesses the database for normal objective Statements are used. They’re helpful when the consumer makes use of static SQL statements throughout runtime. Parameters cannot be accepted by the Assertion interface.
PreparedStatement When there’s a want to make use of SQLStatement a number of instances PreparedStatements are useful. Enter parameters are accepted by the PreparedStatement throughout runtime.
CallableStatement Use this once you need to entry the database saved procedures. The CallableStatement interface may also settle for runtime enter parameters.

The assertion object

HOW TO CREATE STATEMENT OBJECT AND DESCRIBE THE THREE EXECUTE METHODS?

The consumer has to create a press release object by utilizing Connection object’s createStatement( ) technique , solely then the consumer can use a Assertion object to execute a SQL assertion. 

After making a Assertion Object it may be used to execute an SQL assertion with one in every of its three execute strategies.

  • boolean execute (String SQL)
    • The strategy is used firstly, to execute SQL DDL statements and lastly, when there’s a want to make use of extremely dynamic SQL. 
    • CONDITION:
      • If ResultSet object may be retrieved, then Returns a boolean worth of true 
      • If ResultSet object can’t be retrieved, then Returns a boolean worth of false
  • int executeUpdate (String SQL)
    • The strategy is used when various rows are anticipated to get affected after executing SQL statements (for instance, an INSERT, UPDATE, or DELETE assertion).
  • ResultSet executeQuery (String SQL):
    • The strategy is used when their is a expectation of getting a end result set (for instance, SELECT assertion)

HOW TO CLOSE STATEMENT OBJECT?

The shut() technique is used to shut assertion objects. Closing the connection object, it would routinely end result within the closing of the Assertion object. To make sure a correct cleanup the Assertion object have to be closed individually.

The PreparedStatement Objects

The PreparedStatement interface provides further performance with a few benefits over a normal Assertion object. Arguments may be equipped dynamically which gives flexibility for the consumer to work together with the statements. 

HOW TO CREATE PREPAREDSTATEMENT OBJECT?

Parameter marker ?, represents all parameters in JDBC. Earlier than executing SQL statements values have to be equipped for each parameter.

The consumer can bind a worth of a selected Java knowledge sort (XXX) to the enter parameter outlined by utilizing setXXX() technique.

Ordinal positions are assigned to respective markers, the place first marker represents place one and second marker represents place two and so forth. 

Assertion object’s strategies utilized by the consumer to work together with the database (execute(), executeQuery(), executeUpdate()) work with the PreparedStatement object. The strategies enable the consumer to enter the parameters by utilizing SQL statements. 

HOW TO CLOSE PREPAREDSTATEMENT OBJECT?

Closing a PreparedStatement object is essential.

This motion may be carried out by merely utilizing the shut() technique. Closing the Connection object, will routinely shut the PreparedStatement object. To make sure correct cleanup the PreparedStatement object needs to be closed individually.

The CallableStatement Objects

WHAT IS CALLABLESTATEMENT OBJECTS?

Operate – used to execute a name to a database saved process.

Connection object creates CallableStatement object

HOW TO CREATE CALLABLESTATEMENT OBJECT?

Sorts of parameters:

  • IN parameter utilized by PreparedStatement object solely
  • OUT parameter
  • INOUT parameter

Nonetheless, the CallableStatement object can use all of the three parameters.

Parameter Description
IN The  worth of the parameter is unknown,  when the SQL assertion is created. The consumer makes use of the setXXX() strategies to bind values to IN parameters.
OUT The  worth of the parameter is equipped by the SQL assertion it returns. The consumer makes use of the getXXX() strategies to retrieve values from theOUT parameters.
INOUT Each enter and output values are supplied. The consumer makes use of the setXXX() strategies to bind variables and makes use of getXXX() strategies to getXXX() strategies.

The saved process, with parameter placeholders, is being represented by the string variable SQL.

The CallableStatement objects are used within the style more likely to that of PreparedStatement objects. The consumer has to bind values to all of the parameters earlier than executing the assertion, or the SQL instructions gained’t execute and the method will obtain an SQLException.

The consumer can use IN parameters,  by simply following the identical guidelines and methods that apply to a PreparedStatement object; the setXXX() technique can be utilized for binding the Java knowledge sort that’s required by the consumer.

Whereas utilizing OUT  and INOUT parameters the consumer should make use of a further CallableStatement technique, registerOutParameter(). The registerOutParameter() technique is used for binding the JDBC knowledge sort to the info sort ( the saved process is anticipated to return).

After calling the respective saved process, the consumer can use the suitable getXXX() technique to retrieve the worth from the OUT parameter. The strategy casts the retrieved worth of SQL sort to a Java knowledge sort.

HOW TO CLOSE A CALLABLESTATEMENT OBJECT?

Closing CallableStatement objects is essential.

This motion may be carried out by merely utilizing the shut() technique. Closing the Connection object, will routinely shut the CallableStatement object. To make sure correct cleanup the CallableStatement object needs to be closed individually.

JDBC SQL ESCAPE SYNTAX

WHAT IS JDBC SQL ESCAPE SYNTAX AND DESCRIBE THE VARIOUS TYPES OF ESCAPE SEQUENCES?

There are lots of options of a database (particular in nature) that aren’t out there for the consumer, however with the assistance of escape syntax these options are made out there to customers with the assistance of ordinary JDBC strategies and properties. 

SQL escape syntax format (normal) −

key phrase ‘parameters’

The varied varieties of escape sequences are as follows:

d, t, ts Key phrases

  • Operate – date, time, and timestamp literals are acknowledged by this escape sequence.
  • The illustration of time and date is rarely the identical approach for any two DBMS.
  • The date or time will get rendered by the driving force within the focused database’s format.

d ‘yyyy-mm-dd’

The place yyyy stands for yr, mm stands for month; dd stands for  date. 

  • you should utilize one of many following two syntaxes, both t or ts −

The place hh stands for hour; mm stands for minute; ss stands for second. 

  • The mixed syntax of the above two syntax for ‘d’ and ‘t’ to symbolize timestamp.

escape Key phrase

  • Operate – escape characters are recognized.

fn Key phrase 

  • Operate –  scalar capabilities are represented that are utilized in a DBMS. C

name Key phrase

  • Operate – used to name the saved procedures.

oj Key phrase

  • Operate – used to suggest outer joins. 
  • Syntax

The place outer-join = desk RIGHT OUTERJOIN outer-join on search-condition. 

Streaming Knowledge

Enter and output streams can be utilized by the PreparedStatement object to provide parameter knowledge. Database Columns are designed to comprise massive values for instance CLOB and BLOB knowledge sorts, the respective technique permits the consumer to retailer whole information into these databases.

WHAT ARE THE METHODS THAT CAN BE IMPLEMENTED TO STREAM DATA? 

The strategy that may be applied in order that the consumer can stream knowledge are as follows

  • setAsciiStream(): Giant ASCII values are equipped by means of this technique.
  • setCharacterStream(): Giant UNICODE values are equipped by means of this technique.
  • setBinaryStream(): Giant binary values are equipped by means of this technique.

WHAT ARE THE PARAMETER REQUIRED BY THE setXXXStream() METHOD?

The setXXXStream() technique requires 

  1. placeholder(parameter): used for the positional parameters of a PreparedStatement.
  2. file measurement (parameter):  the quantity of knowledge that must be despatched to the database whereas executing stream strategies is knowledgeable by the file measurement parameter. 

zero

Supply

Leave a Comment