Click here to Skip to main content
15,868,058 members
Articles / Database Development

Prepared Statements for Database Driven Application

Rate me:
Please Sign up or sign in to vote.
3.00/5 (4 votes)
15 Jan 2014CPOL1 min read 20K   6   10
Prepared statements for database driven application

A Prepared Statement is commonly used by an application to execute the same parameterized SQL statement again and again. Prepared statements are compiled only once by the DBMS. If we need to execute a statement multiple times, then execution of a prepared statement is faster, as it is compiled only once, while if we are using direct statements, then each statement is first compiled before execution. So, time taken in prepared execution is lesser as compared to the time taken in direct execution.

Prepared statements are also known as parameterized queries. Parameterized queries and prepared statements are features of database management systems that basically act as templates in which SQL can be executed.

Example of Prepared Statement using Java and C#

We are using Emp table. Here id is the primary key of the table. The following query will retrieve all the data of a row for id = 1.

SQL
SELECT * FROM Emp WHERE id = 1

Now, if we create a template of above statement and use that for multiple values of id, then it will look like:

SQL
SELECT * FROM Emp WHERE id = ?

Here, ? is called placeholder. It represents the place where actual values will be used in the SQL query. Placeholders are also known as bound parameters, since they are essential parameters that are passed to SQL that "bind" to the SQL at a later time.

Prepared Statement in JDBC

Java
java.sql.PreparedStatement stmt = connection.prepareStatement("SELECT * FROM Emp WHERE id = ?");
for (int i = 1; i<= 100; i++)
{
        stmt.setString(i, id);
        stmt.executeQuery();
}

Parameterized Statements in SQL Server using C#

C#
String sql = "SELECT * FROM Emp WHERE id = @id";
for(int i =1; i<=100; i++)
{
        cmd.Parameters.AddWithValue("@id", i);
       //execution of cmd
        cmd.Parameters.Clear();
}

In the above code, cmd is a SqlCommand object.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
Software Engineer having hands-on experience with C, C++, C#, .NET, ASP.NET, SQL, Website designing technologies, Joomla CMS, Application development, COM, MFC, Installshield, Installscript project, Basic MSI

http://newapputil.blogspot.in/
http://nvivekgoyal.blogspot.in/

Comments and Discussions

 
GeneralMy vote of 3 Pin
nullpointer20-Jan-14 14:35
nullpointer20-Jan-14 14:35 
GeneralMy vote of 2 Pin
eimaideveloper15-Jan-14 23:46
professionaleimaideveloper15-Jan-14 23:46 
GeneralRe: My vote of 2 Pin
Vivek Goyal15-Jan-14 23:51
Vivek Goyal15-Jan-14 23:51 
GeneralMy vote of 2 Pin
r.wcs15-Jan-14 20:42
r.wcs15-Jan-14 20:42 
GeneralRe: My vote of 2 Pin
Vivek Goyal15-Jan-14 21:08
Vivek Goyal15-Jan-14 21:08 
Hi, please provide some feedback where I can update this article. I will definitely come with update. Smile | :)
GeneralRe: My vote of 2 Pin
r.wcs15-Jan-14 21:17
r.wcs15-Jan-14 21:17 
GeneralRe: My vote of 2 Pin
Vivek Goyal15-Jan-14 21:56
Vivek Goyal15-Jan-14 21:56 
GeneralRe: My vote of 2 Pin
Komal Mangal15-Jan-14 22:45
Komal Mangal15-Jan-14 22:45 
GeneralRe: My vote of 2 Pin
r.wcs15-Jan-14 22:56
r.wcs15-Jan-14 22:56 
GeneralRe: My vote of 2 Pin
Vivek Goyal15-Jan-14 22:56
Vivek Goyal15-Jan-14 22:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.