PreparedStatements.java An example to test the timing differences resulting from repeated raw queries vs. repeated calls #Programming Code Examples #Java/J2EE/J2ME #JDBC

package cwp;

import java.sql.*;

/** An example to test the timing differences resulting
 *  from repeated raw queries vs. repeated calls to
 *  prepared statements. These results will vary dramatically
 *  among database servers and drivers. With my setup
 *  and drivers, Oracle prepared statements took only half
 *  the time that raw queries required when using a modem
 *  connection, and took only 70% of the time that
 *  raw queries required when using a fast LAN connection.
 *  Sybase times were identical in both cases.
 *  

 */

public class PreparedStatements {
  public static void main(String[] args) {
    if (args.length  5) && (args[5].equals("print"))) {
      print = true;
    }
    Connection connection =
      getConnection(driver, url, username, password);
    if (connection != null) {
      doPreparedStatements(connection, print);
      doRawQueries(connection, print);
    }
  }

  private static void doPreparedStatements(Connection conn,
                                           boolean print) {
    try {
      String queryFormat =
        "SELECT lastname FROM employees WHERE salary > ?";
      PreparedStatement statement =
        conn.prepareStatement(queryFormat);
      long startTime = System.currentTimeMillis();
      for(int i=0; i<40; i++) {
        statement.setFloat(1, i*5000);
        ResultSet results = statement.executeQuery();
        if (print) {
          showResults(results);
        }
      }
      long stopTime = System.currentTimeMillis();
      double elapsedTime = (stopTime - startTime)/1000.0;
      System.out.println("Executing prepared statement " +
                         "40 times took " +
                         elapsedTime + " seconds.");
    } catch(SQLException sqle) {
      System.out.println("Error executing statement: " + sqle);
    }
  }

  public static void doRawQueries(Connection conn,
                                  boolean print) {
    try {
      String queryFormat =
        "SELECT lastname FROM employees WHERE salary > ";
      Statement statement = conn.createStatement();
      long startTime = System.currentTimeMillis();
      for(int i=0; i<40; i++) {
        ResultSet results =
          statement.executeQuery(queryFormat + (i*5000));
        if (print) {
          showResults(results);
        }
      }
      long stopTime = System.currentTimeMillis();
      double elapsedTime = (stopTime - startTime)/1000.0;
      System.out.println("Executing raw query " +
                         "40 times took " +
                         elapsedTime + " seconds.");
    } catch(SQLException sqle) {
      System.out.println("Error executing query: " + sqle);
    }
  } 

  private static void showResults(ResultSet results)
      throws SQLException {
    while(results.next()) {
      System.out.print(results.getString(1) + " ");
    }
    System.out.println();
  }
    
  private static Connection getConnection(String driver,
                                          String url,
                                          String username,
                                          String password) {
    try {
      Class.forName(driver);
      Connection connection =
        DriverManager.getConnection(url, username, password);
      return(connection);
    } catch(ClassNotFoundException cnfe) {
      System.err.println("Error loading driver: " + cnfe);
      return(null);
    } catch(SQLException sqle) {
      System.err.println("Error connecting: " + sqle);
      return(null);
    }
  }

  private static void printUsage() {
    System.out.println("Usage: PreparedStatements host " +
                       "dbName username password " +
                       "oracle|sybase [print].");
  }
}

Note: Brought from our old site: http://www.salearningschool.com/example_codes/ on Jan 2nd, 2017 From: http://sitestree.com/?p=10210
Categories:Programming Code Examples, Java/J2EE/J2ME, JDBC
Tags:Java/J2EE/J2MEJDBC
Post Data:2017-01-02 16:04:23

Shop Online: https://www.ShopForSoul.com/
(Big Data, Cloud, Security, Machine Learning): Courses: http://Training.SitesTree.com
In Bengali: http://Bangla.SaLearningSchool.com
http://SitesTree.com
8112223 Canada Inc./JustEtc: http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning)
Shop Online: https://www.ShopForSoul.com/
Medium: https://medium.com/@SayedAhmedCanada