Why Purpose Preparedstatement Inwards Coffee Jdbc – Illustration Tutorial

PreparedStatement inward Java is 1 of several ways to execute SQL queries using JDBC API. Java provides Statement,

PreparedStatement too CallableStatement for executing queries. Out of these three, Statement is used for full general piece of job queries, PreparedStatement is used for executing parametric query too CallableStatement is used for executing Stored Procedures. PreparedStatement is too a pop topic inward coffee interviews. Questions similar Difference betwixt Statement too PreparedStatement inward Java too How to forestall SQL Injection attacks inward Java are popular coffee interview questions. In this Java JDBC tutorial nosotros volition run into why should you lot usage employ PreparedStatement inward Java, What are the major advantages of using PreparedStatement inward Java too how PreparedStatement prevents SQL Injection attacks inward Java.

This article is inward continuation of my before postal service on database too coffee similar 4 tips to improve surgical physical care for of Java application alongside database too Difference betwixt truncate too delete inward SQL.If you lot haven’t read  them already you lot may found  those tutorial useful too interesting.

What is PreparedStatement inward Java

PreparedStatement is a shape inward java.sql bundle too allows Java programmer to execute SQL queries yesteryear using JDBC package. You tin larn PreparedStatement object yesteryear calling connection.prepareStatement() method.SQL queries passed to this method goes to Database for pre-compilation if JDBC driver supports it. If it doesn't than pre-compilation occurs when you lot execute prepared queries. Prepared Statement queries are pre-compiled on database too at that spot access innovation volition locomote reused to execute farther queries which allows them to execute much quicker than normal queries generated yesteryear Statement object. Here is an example of how to usage PreparedStatement inward Java:

public class PreparedStmtExample {

    public static void main(String args[]) throws SQLException {
        Connection conn = DriverManager.getConnection("mysql:\\localhost:1520", "root", "root");
        PreparedStatement preStatement = conn.prepareStatement("select distinct loan_type from loan where bank=?");
        preStatement.setString(1, "Citibank");
   
        ResultSet number = preStatement.executeQuery();
     
        while(result.next()){
            System.out.println("Loan Type: " + result.getString("loan_type"));
        }      
    }
} 
Output:
Loan Type: Personal Loan
Loan Type: Auto Loan
Loan Type: Home Loan
Loan Type: Gold Loan

In this instance of PreparedStatement same query too access path volition locomote used if you lot exceed a dissimilar parameter  e.g.

"Standard Charted" or "HSBC". ResultSet returned yesteryear prepared contestation execution is of "TYPE_FORWARD_ONLY" only tin locomote customized yesteryear using overloaded method of prepareStatement().

Benefits of Java Prepared Statement

PreparedStatement inward Java JDBC offers several benefits too it’s a recommended agency to execute SQL queries inward whatever corporation Java application or inward production code. Here are few advantages of using PreparedStatement in Java:

1. PreparedStatement allows you lot to write dynamic too parametric query.

By using PreparedStatement inward Java you lot tin write parametrized sql queries too transportation dissimilar parameters yesteryear using same sql queries which is lot ameliorate than creating dissimilar queries. Here is an instance of parametric query written using PreparedStatement inward java:

select interest_rate from loan where loan_type=?

Now you lot tin run this query for whatever loan type e.g. "personal loan”, "home loan" or "gold loan". This example of SELECT query is called parametric or parametrized query because it tin locomote invoked alongside dissimilar parameter. Here “?” is used every bit identify holder for parameter.

2. PreparedStatement is faster than Statement inward Java

One of the major benefits of using PreparedStatement is ameliorate performance. PreparedStatement gets pre compiled

In database too at that spot access innovation is too cached inward database, which allows database to execute parametric query written using prepared contestation much faster than normal query because it has less piece of job to do. You should e'er attempt to usage PreparedStatement inward production JDBC code to cut down charge on database. In social club to larn surgical physical care for practice goodness its worth noting to use alone parametrized version of sql query too non alongside string concatenation. Out of next 2 examples of SELECT queries, get-go instance of SELECT query  volition non offering whatever surgical physical care for benefit:

SQL Query 1: PreparedStatement alongside String concatenation

String loanType = getLoanType();
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=" + loanType);

SQL Query 2: Parameterized query using PreparedStatement

PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=?");
prestmt.setString(1,loanType);

Second SQL query is right usage of PreparedStatement inward Java too give ameliorate surgical physical care for than SQL query1.

3. PreparedStatement prevents SQL Injection attacks inward Java

If you lot convey been working inward Java spider web application you lot must locomote familiar alongside infamous SQL Injection attacks, concluding yr Sony got victim of SQL injection too compromised several Sony play station user data. In SQL Injection attack, malicious user exceed SQL meta-data combined alongside input which allowed them to execute sql query of at that spot choice, If non validated or prevented before sending query to database. By using parametric queries too PreparedStatement you lot forestall many forms of SQL injection because all the parameters passed every bit business office of place-holder volition locomote escaped automatically yesteryear JDBC Driver. Though It’s worth remembering that inward higher upward instance of 2 PreparedStatement alone instant instance volition forestall SQL injection attacks too get-go instance is non secure alongside SQL injection.

4. At concluding PreparedStatement queries are to a greater extent than readable too secure than cluttered string concatenated queries.

Limitation of Java PreparedStatement

Despite of existence really useful PreparedStatement also has few limitations:

1. In social club to forestall SQL Injection attacks inward Java, PreparedStatement doesn't allow multiple values for 1 placeholder (?) who makes it tricky to execute SQL query alongside IN clause. Following instance of SQL query alongside IN clause using prepared Statement volition non piece of job inward Java:

select * from loan where loan_type IN ( ?)
preparedSatement.setString(1, "'personal loan', 'home loan', 'gold loan'");

Though at that spot are closed to workarounds too ways to execute IN queries using PreparedStatement only those are

rather tricky or convey surgical physical care for impact.

Important points on PreparedStatement inward Java

Here are few of import points nearly PreparedStatement Class inward Java, worth remembering:

1. PreparedStatement inward Java allows you lot to write parametrized query which gives ameliorate surgical physical care for than Statement class inward Java.

2. In instance of PreparedStatement, Database usage an already compiled too defined access plan, this allows prepared contestation query to run faster than normal query.

3. Parametrized query written using PreparedStatement inward Java prevents many mutual SQL Injection attacks.

4. PreparedStatement allows you lot to write dynamic query inward Java.

5. PreparedStatement are associated alongside java.sql.Connection object, 1 time you lot drib a connexion all PreparedStatement  associated alongside that connexion volition locomote dropped yesteryear Database.

6. "?" is too called placeholder or IN parameter inward Java.

7. PreparedStatement query render FORWARD_ONLY ResultSet, then you lot tin alone motion inward 1 administration Also concurrency degree of ResultSet would locomote "CONCUR_READ_ONLY".

8. All JDBC Driver doesn't back upward pre compilation of SQL query inward that instance query is non sent to database when you lot telephone phone prepareStatement(..) method instead they would locomote sent to database when you lot execute PreparedStatement query.

9. Index of placeholder or parameter starts alongside "1" too non alongside "0", which is mutual crusade of java.sql.SQLException: Invalid column index . So inward a PreparedStatement t of 2 placeholder, get-go volition locomote referred yesteryear index 1 too instant volition locomote reference yesteryear index 2.

These were the reasons Why PreparedStatement in coffee is really pop too useful. You tin nonetheless usage Statement object for attempt programmers only visit PreparedStatement before moving to production.

Further Learning
JSP, Servlets too JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 too 2
10 versatile examples of Enum inward Java

Beginners guide to Generics inward Java

How to usage Factory pattern pattern inward Java alongside Example

10 Interview questions on Singleton inward Java

How to create out transaction inward database

How to usage mysqldump utility for db backup

Why Java doesn’t back upward multiple inheritance