benchmark.java

多种开源数据库（mysql、hsqldb、derby等等）性能比较源文件

/*
 * Main.java
 *
 * Created on 25 February 2005, 14:27
 */

package com.ideasasylum.dbbenchmark;

import java.sql.*;
import java.util.logging.*;
import org.apache.commons.cli.*;
import java.io.*;

/**
 * A Quick Little Database Benchmark
 * @author jel03r
 */
public abstract class Benchmark {
    private static final Logger logger = Logger.getLogger("com.ideasasylum.dbbenchmark");
    private static final String TEST_STRING = "This is a test string";
    
    protected Connection connection;
    private String db;
    private String user;
    private String password;
    private int rows;
    private long createTime;
    private long insertTime;
    private long selectTime;
    
    
    /** Creates a new instance of BenchMark */
    public Benchmark(){
    }
    
    public void execute(){
        try {
            // create the table
            long t1 = System.currentTimeMillis();
            openConnection();
            createTable();
            closeConnection();
            long t2 = System.currentTimeMillis();
            
            // test INSERT
            long t3 = System.currentTimeMillis();
            openConnection();
            testInsert();
            closeConnection();
            long t4 = System.currentTimeMillis();
            
            // test SELECT
            long t5 = System.currentTimeMillis();
            openConnection();
            testSelect();
            closeConnection();
            long t6 = System.currentTimeMillis();
            
            this.createTime = t2-t1;
            this.insertTime = t4-t3;
            this.selectTime = t6-t5;
            System.out.println(createTime + "\t"+ insertTime + "\t" + selectTime);
        } catch(SQLException sqle){
            logger.log(Level.SEVERE, sqle.getMessage(), sqle);
        }
    }
    
    protected abstract String getSelectSQL();
    protected abstract String getInsertSQL();
    protected abstract String getCreateSQL();
    protected abstract String getDropSQL();
    
    /**
     * Test INSERTs
     */
    protected void testInsert() throws SQLException {
        long currentTime = System.currentTimeMillis();
        Date date = new Date(currentTime);
        String insert = getInsertSQL();
        PreparedStatement pstmt = connection.prepareStatement(insert);
        
        for(int i=0; i<rows; i++){
            pstmt.setString(1, TEST_STRING);
            pstmt.setInt(2, 5000);
            pstmt.setDate(3, date);
            pstmt.setLong(4, currentTime);
            pstmt.setFloat(5, 1.3333f);
            pstmt.execute();
        }
    }
    
    /**
     * Test SELECTs
     */
    protected void testSelect() throws SQLException {
        String select = getSelectSQL();
        PreparedStatement pstmt = connection.prepareStatement(select);
//        if(select.indexOf("?") != -1)
//            pstmt.setInt(1, rows);
        pstmt.execute();
        ResultSet results = pstmt.getResultSet();
        while(results.next()){
            results.getInt(1);
            results.getString(2);
            results.getInt(3);
            results.getDate(4);
            results.getLong(5);
            results.getFloat(6);
        }
    }
    
    /**
     * (Re-)Create a table in the database, DROPping it first
     */
    protected void createTable() throws SQLException {
        String drop = getDropSQL();
        Statement stmt = connection.createStatement();
        try {
            stmt.execute(getDropSQL());
        } catch (SQLException sqle){
            // note that some databases do not support DROP IF EXISTS and so and error will be thrown but we can
            // catch it here and just warn the user
            logger.log(Level.WARNING, sqle.getMessage(), sqle);
        }
        
        String create = getCreateSQL();
        stmt.execute(create);
    }
    
    /**
     * Open the database connection
     */
    protected void openConnection() throws SQLException {
        if(user != null && password != null && password.length()>0 && user.length()>0)
            connection = DriverManager.getConnection(db, user, password);
        else
            connection = DriverManager.getConnection(db);
    }
    
    /**
     * Close the db connection
     */
    protected void closeConnection() throws SQLException {
        connection.close();
    }
    
    /**
     * Main entry point
     * @param args the command line arguments.
     */
    public static void main(String[] args) {
        Options options = new Options();
        try {
            options.addOption("j", "driver", true, "JDBC Driver");
            options.addOption("b", "benchmark", true, "The class name of the benchmark to execute");
            options.addOption("d", "database", true, "The JDBC url of the database");
            options.addOption("u", "username", true, "The database username");
            options.addOption("p", "password", true, "The database password");
            options.addOption("r", "rows", true, "The number of selects to perform (selects should be >= inserts)");
            options.addOption("n", "runs", true, "The number of benchmark run to perform");
            options.addOption("o", "output", true, "The output file name (CSV format)");
            options.addOption("I", "increment", true, "Increment the select and inserts for load testing");
            options.getOption("j").setRequired(true);
            options.getOption("b").setRequired(true);
            options.getOption("d").setRequired(true);
            CommandLineParser parser = new PosixParser();
            CommandLine commands = parser.parse(options, args);
            
            // defaults
            Benchmark benchmark = null;
            String driver = null;
            String database = null;
            String username = null;
            String password = null;
            int runs = 5;
            int rows = 2000;
            int increment = 0;
            
            // Load the JDBC driver
            driver = commands.getOptionValue("j");
            logger.fine("Loading driver: " + driver);
            Class.forName(driver).newInstance();
            
            // create the benchmark
            String classname = commands.getOptionValue("b");
            logger.fine("Loading benchmark: " + classname);
            benchmark = (Benchmark) Class.forName(classname).newInstance();
            
            // set the various benchmark properties
            database = commands.getOptionValue("d");
            benchmark.setDb(database);
            username = commands.getOptionValue("u");
            benchmark.setUser(username);
            password = commands.getOptionValue("p");
            benchmark.setPassword(password);
            if(commands.hasOption("r"))
                rows = Integer.parseInt(commands.getOptionValue("r"));
            benchmark.setRows(rows);
            
            if(commands.hasOption("n"))
                runs = Integer.parseInt(commands.getOptionValue("n"));
            
            if(commands.hasOption("I"))
                increment = Integer.parseInt(commands.getOptionValue("I"));
            
            OutputStreamWriter output = null;
            if(commands.hasOption("o")){
                File file = new File(commands.getOptionValue("o"));
                // delete the previous file
                if(file.exists())
                    file.delete();
                // create the file
                file.createNewFile();
                output = new OutputStreamWriter(new FileOutputStream(file));
                output.write("Run,Create,Insert,Select,Rows\n");
            }
            
            // execute the benchmark a number of times
            for(int i=0; i<runs; i++){
                benchmark.execute();
                if(output != null){
                    output.write(Integer.toString(i));
                    output.write(",");
                    output.write(Long.toString(benchmark.getCreateTime()));
                    output.write(",");
                    output.write(Long.toString(benchmark.getInsertTime()));
                    output.write(",");
                    output.write(Long.toString(benchmark.getSelectTime()));
                    output.write(",");
                    output.write(Integer.toString(rows));
                    output.write('\n');
                }
                if(increment != 0){
                    rows += increment;
                    benchmark.setRows(rows);
                }
            }
            output.close();
        } catch (Exception e) {
            logger.log(Level.SEVERE, e.getMessage(), e);
            // print out the usage instructions
            HelpFormatter formatter = new HelpFormatter();
            formatter.printHelp("java com.ideasasylum.dbbenchmark.Benchmark", options);
        }
    }
    
    public String getDb() {
        return db;
    }
    
    public void setDb(String db) {
        this.db = db;
    }
    
    public String getUser() {
        return user;
    }
    
    public void setUser(String user) {
        this.user = user;
    }
    
    public String getPassword() {
        return password;
    }
    
    public void setPassword(String password) {
        this.password = password;
    }
    
    public int getRows() {
        return rows;
    }
    
    public void setRows(int rows) {
        this.rows = rows;
    }
    
    public long getCreateTime() {
        return createTime;
    }
    
    public void setCreateTime(long createTime) {
        this.createTime = createTime;
    }
    
    public long getInsertTime() {
        return insertTime;
    }
    
    public void setInsertTime(long insertTime) {
        this.insertTime = insertTime;
    }
    
    public long getSelectTime() {
        return selectTime;
    }
    
    public void setSelectTime(long selectTime) {
        this.selectTime = selectTime;
    }
    
}