What is faster in getting data from database: Statement or PreparedStatement in Java application?
What is better to use Statement
or PreparedStatement
. I test both objects to connect by JDBC to database and get data.
The first case will be select query. I use for it Microsoft example database: AdventureWorks2012
. I create New Project in Netbeans and add to Library node in project, JDBC driver to MSSQL Server.
SELECT query for Statement object.
I get all records from Person( schema Person )
table from AdventureWorks2012
database. This table contains data in 19 972 rows. I check how much time take using Statement
object.
package doraprojects; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Date; import java.util.logging.Logger; public class TestSelect { public static void main(String[] args) { long time_input = System.currentTimeMillis(); System.out.println(new Date(time_input)); new DataFromMSSQL(); long time_output = System.currentTimeMillis(); System.out.println(new Date(time_output)); System.out.println("start:"+time_input+", stop:"+time_output+", diffrence:"+(time_output-time_input)+" ms"); } } class DataFromMSSQL { private String url; private Driver driver; private Connection connection; private Statement statement; private ResultSet rs; public DataFromMSSQL() { url = "jdbc:sqlserver://localhost:1433; databaseName=AdventureWorks2012; user=sa; password=YourPasswordToDB" ; try { driver = new com.microsoft.sqlserver.jdbc.SQLServerDriver(); DriverManager.registerDriver(driver ); } catch (Exception e) { System.out.println("Problem with download driver for MSSQL: " + e.getMessage()); } try { connection= DriverManager.getConnection(url); } catch (Exception e) { System.out.println("Problem with connection to MSSQL: " + e.getMessage() ); } try { statement = connection.createStatement(); } catch (Exception e) { System.out.println("Problem with Statement of MSSQL" + e.getMessage() ); } try { rs = statement.executeQuery("select * from Person.Person"); while (rs.next()) { int id = rs.getInt(1); String type = rs.getString(2); byte nameStyle = rs.getByte(3); String title = rs.getString(4); String fName = rs.getString(5); String mName = rs.getString(6); String lName = rs.getString(7); String emailP = rs.getString(9); String rowGuid = rs.getString(12); String dateModified = rs.getString(13); System.out.println("id="+id+", type="+type+", nameStyle="+nameStyle+", title="+title+", fName="+fName+", mName="+mName+", lName="+lName+", emailP="+emailP+", rowGuid="+rowGuid+", dateModified="+dateModified ); } rs.close(); statement.close(); } catch (Exception e) { System.out.println("Problem with ResultSet of MSSQL" + e.getMessage() ); } try{ if(!connection.isClosed()) { connection.close(); System.out.println("Connection to MSSQL closed." ); } } catch (Exception e) { System.out.println("Problem with close connection of MSSQL" ); } } }
Result time:
start:1470412254811, stop:1470412261535, diffrence: 6724 ms
After run application You see this time. And what is with PreparedStatement ?
SELECT query for PreparedStatement object.
As in above example I get all records from Person
table from AdventureWorks2012
database and check how much time take using PreparedStatement
object.
package doraprojects; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Date; public class TestSelect { public static void main(String[] args) { long time_input = System.currentTimeMillis(); System.out.println(new Date(time_input)); new DataFromMSSQL(); long time_output = System.currentTimeMillis(); System.out.println(new Date(time_output)); System.out.println("start:"+time_input+", stop:"+time_output+", diffrence:"+(time_output- time_input)+" ms"); } } class DataFromMSSQL { private String url; private Driver driver; private Connection connection; private PreparedStatement ps; private ResultSet rs; public DataFromMSSQL() { url = "jdbc:sqlserver://localhost:1433; databaseName=AdventureWorks2012; user=sa; password=YourPassToDB" ; try { driver = new com.microsoft.sqlserver.jdbc.SQLServerDriver(); DriverManager.registerDriver(driver ); } catch (Exception e) { System.out.println("Problem with download driver for MSSQL: " + e.getMessage()); } try { connection= DriverManager.getConnection(url); } catch (Exception e) { System.out.println("Problem with connection to MSSQL: " + e.getMessage() ); } try { ps = connection.prepareStatement("select * from Person.Person"); rs = ps.executeQuery(); while (rs.next()) { int id = rs.getInt(1); String type = rs.getString(2); byte nameStyle = rs.getByte(3); String title = rs.getString(4); String fName = rs.getString(5); String mName = rs.getString(6); String lName = rs.getString(7); String emailP = rs.getString(9); String rowGuid = rs.getString(12); String dateModified = rs.getString(13); System.out.println("id="+id+", type="+type+", nameStyle="+nameStyle+", title="+title+", fName="+fName+", mName="+mName+", lName="+lName+", emailP="+emailP+", rowGuid="+rowGuid+", dateModified="+dateModified ); } rs.close(); ps.close(); } catch (Exception e) { System.out.println("Problem with ResultSet of MSSQL" + e.getMessage() ); } try{ if(!connection.isClosed()) { connection.close(); System.out.println("Connection to MSSQL closed." ); } } catch (Exception e) { System.out.println("Problem with close connection of MSSQL" ); } } }
Result:
start:1470414287545, stop:1470414293766, diffrence: 6221 ms
In this test better findings achives PreparedStatement object. Difference between them is 503 miliseconds.