PreparedStatement : Best practice of Database in Java
UPDATED: 14 February 2013
Tags:
Database
,
Prepared Statement
What is PreparedStatement?
Its an object that represents a precompiled SQL statement. It allows to execute the same or similar database statements repeatedly with high efficiency. Its template for your query that you use more than one time in your application.
Why you need to use PreparedStatement over SQL statement?
- SQL Statement : Create query model each time you fire the query in database. It will consume time to create model of your query.
select * from user_table where(uid=1); / Creates model for query where uid=1 select * from user_table where(uid=2); / Creates model for query where uid=2 select * from user_table where(uid=1); / Creates model for query where uid=3
- PreparedStatement : Create query model template on first time you fire the query in database. It'll use template already available in database. It don't need to create model when you change the uid parameter.
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
PreparedStatement userRecord_stmt = con.prepareStatement("select * from user_table where(uid=?);");
//Creates global model and used in future whether you use 1,2...n
userRecord_stmt.setInt(1,103);
ResultSet userRecord_rs = userRecord_stmt.executeQuery();
//next you know what to do...
Firing insert query each time when you need to insert 1000 or say more records its really time consuming. Best database practice is prepare one query that contains 1000 records in one query to insert.
// Worst practice insert into notification values(1,"hello world!"); // 1 request sent over network insert into notification values(2,"hello world!"); // 1+1 request sent over network insert into notification values(3,"hello world!"); // 1+1+1 request sent over network // Best practice insert into notification (a,b) values (1,"hell world!"),(2,"hell world!"),(3,"hell world!"); // Only 1 request sent over network
How to bind data in PreparedStatement?
Let me give you code snippet to perform bulk insert using PreparedStatement.
PreparedStatement bulk_stmt = con.prepareStatement("insert into notification values(?,?);");
for (int i = 0; i < 10; i++) {
bulk_stmt.setInt(1, (i + 1));
bulk_stmt.setString(2, "hello world!");
bulk_stmt.addBatch();
}
bulk_stmt.execute();
Find bulk data using "where column_name in( )"
Getting bulk information from table using single column comparison.
- Scenario : Find user details for the following uid : 1, 42, 33, 40, 103
// Worst practice select * from user_table where(uid=1); select * from user_table where(uid=42); select * from user_table where(uid=33); // ... and so on // Best practice select * from user_table where uid in(1,42,33,40,103) ORDER BY uid;
Tags:
Database
,
Prepared Statement
0 comments :