testsqlite.java
来自「主要对各种数据库性能进行测试」· Java 代码 · 共 582 行 · 第 1/2 页
JAVA
582 行
package sqlite;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Random;
public class TestSqlite {
String sGet;
WriteFile2 file = new WriteFile2();
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
PreparedStatement st = null;
void test(){
long lStart=0,lEnd=0,lFee=0;
int insertnum=10000,deletenum=100,selectnum=100,updatenum=100;
int inscommit=1000,delcommit=100,updcommit=100,updatetime=100;
int selecttime=100,deletetime=100,ordertime=100;
int grouptime=100,groupnum=10;
String sql = "",driver="",in="",dbtype="",showprint="";
Configure config = new Configure();
config.configfile = "../config2.ini";
int isharddisk = Integer.parseInt(config.getProperty("isharddisk"));
insertnum=Integer.parseInt(config.getProperty("insertnum"));
deletenum=Integer.parseInt(config.getProperty("deletenum"));
selectnum=Integer.parseInt(config.getProperty("selectnum"));
updatenum=Integer.parseInt(config.getProperty("updatenum"));
selecttime = Integer.parseInt(config.getProperty("selecttime"));
updatetime = Integer.parseInt(config.getProperty("updatetime"));
ordertime = Integer.parseInt(config.getProperty("ordertime"));
deletetime = Integer.parseInt(config.getProperty("deletetime"));
grouptime = Integer.parseInt(config.getProperty("grouptime"));
groupnum = Integer.parseInt(config.getProperty("groupnum"));
inscommit=Integer.parseInt(config.getProperty("inscommit"));
delcommit=Integer.parseInt(config.getProperty("delcommit"));
updcommit=Integer.parseInt(config.getProperty("updcommit"));
showprint = config.getProperty("showprint");
String oracle_driver = config.getProperty("oracle_driver");
String oracle_user = config.getProperty("oracle_user");
String oracle_password = config.getProperty("oracle_password");
String mysql_driver = config.getProperty("mysql_driver");
String mysql_user = config.getProperty("mysql_user");
String mysql_password = config.getProperty("mysql_password");
String kingbase_driver = config.getProperty("kingbase_driver");
String kingbase_user = config.getProperty("kingbase_user");
String kingbase_password = config.getProperty("kingbase_password");
String isdrop = config.getProperty("isdrop");
String sqlite_driver = config.getProperty("sqlite_driver");
String hsql_driver = config.getProperty("hsql_driver");
System.out.println("starting...");
try {
if(this.sGet.equals("1")||this.sGet.equalsIgnoreCase("sqlite")){
Class.forName("org.sqlite.JDBC");
if(isharddisk==0){ //内存
driver = "jdbc:sqlite:";
in = driver+",数据库在内存中。。。";
System.out.println(in);
file.aLine(in);
}else{
driver = sqlite_driver;
in = driver+",数据库在硬盘上。。。";
System.out.println(in);
file.aLine(in);
}
dbtype = "sqlite";
} else if(this.sGet.equals("2")||this.sGet.equalsIgnoreCase("hsql")){
Class.forName("org.hsqldb.jdbcDriver");
if(isharddisk==0){ //内存
driver = "jdbc:hsqldb:mem:hsql";
in = driver+",数据库在内存中。。。";
System.out.println(in);
file.aLine(in);
}else{
driver = hsql_driver;
in = driver+",数据库在硬盘上。。。";
System.out.println(in);
file.aLine(in);
}
dbtype = "hsql";
}else if(this.sGet.equals("3")||this.sGet.equalsIgnoreCase("mysql")){
Class.forName("com.mysql.jdbc.Driver");
driver = mysql_driver;
System.out.println(driver);
file.aLine(driver);
dbtype = "mysql";
}else if(this.sGet.equals("4")||this.sGet.equalsIgnoreCase("oracle")){
Class.forName("oracle.jdbc.driver.OracleDriver");
driver = oracle_driver;
System.out.println(driver);
file.aLine(driver);
dbtype = "oracle";
}else if(this.sGet.equals("5")||this.sGet.equalsIgnoreCase("kingbase")){
Class.forName("com.kingbase.Driver");
driver = kingbase_driver;
System.out.println(driver);
file.aLine(driver);
dbtype = "kingbase";
}
String date=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
in = "============="+driver+"，测试时间："+date+"=============";
System.out.println(in);
file.aLine(in);
try{
if(dbtype.equalsIgnoreCase("oracle")){
System.out.println(oracle_user+","+oracle_password);
conn = DriverManager.getConnection(driver,oracle_user,oracle_password);
}else if(dbtype.equalsIgnoreCase("mysql")){
conn = DriverManager.getConnection(driver,mysql_user,mysql_password);
}else if(dbtype.equalsIgnoreCase("kingbase")){
conn = DriverManager.getConnection(driver,kingbase_user,kingbase_password);
}else{
conn = DriverManager.getConnection(driver);
}
}catch(SQLException se){
System.out.println("error:"+se.getMessage());
}
conn.setAutoCommit(false);
stmt = conn.createStatement();
/*===============================删除表========================*/
if(isdrop.equals("1")){
try{
stmt.executeUpdate("drop table person");
System.out.println("删除表person成功!");
}catch(Exception e){
System.out.println("表不存在！");
}
}
/*===============================建表===========================*/
lStart = System.currentTimeMillis();
try{
if(dbtype.equalsIgnoreCase("sqlite")){
sql = "create table person(id integer primary key, name varchar(32),name3 varchar(32),name4 varchar(32),name5 varchar(32),name6 varchar(32),name7 varchar(32),name8 varchar(32),name9 varchar(32),name10 int)";
}else if(dbtype.equalsIgnoreCase("hsql")){
sql = "create table person(id int generated by default as identity(start with 1,increment by 1) not null primary key, name varchar(32),name3 varchar(32),name4 varchar(32),name5 varchar(32),name6 varchar(32),name7 varchar(32),name8 varchar(32),name9 varchar(32),name10 int)";
}else if(dbtype.equalsIgnoreCase("mysql")){
sql = "create table person(id integer auto_increment, name varchar(32),name3 varchar(32),name4 varchar(32),name5 varchar(32),name6 varchar(32),name7 varchar(32),name8 varchar(32),name9 varchar(32),name10 int,primary key(id))";
}else if(dbtype.equalsIgnoreCase("oracle") ){
sql = "create table person(id int , name varchar(32),name3 varchar(32),name4 varchar(32),name5 varchar(32),name6 varchar(32),name7 varchar(32),name8 varchar(32),name9 varchar(32),name10 int,constraint pk_person primary key(id))";
}else if ( dbtype.equalsIgnoreCase("kingbase")){
sql = "create table person(id int identity, name varchar(32),name3 varchar(32),name4 varchar(32),name5 varchar(32),name6 varchar(32),name7 varchar(32),name8 varchar(32),name9 varchar(32),name10 int,constraint pk_person primary key(id))";
}
try{
stmt.executeUpdate(sql);
}catch(Exception e){
}
/*
* oracle需要创建序列和触发器，orcle和mysql会自动在主键上创建索引，而sqlite和hsql需要手工创建
*/
if(dbtype.equalsIgnoreCase("oracle") ){
try{
sql="select count(*) from user_objects where object_name = 'SEQ_PERSON'" ;
int iCount = 0;
rset = stmt.executeQuery(sql);
if (rset.next()){
iCount = rset.getInt(1);
}
if (rset!=null){
rset.close();
rset = null;
}
if(iCount == 0 ){
if(isdrop.equals("1")){
sql = "drop sequence seq_person";
stmt.executeUpdate(sql);
}
sql = "create sequence seq_person";
stmt.executeUpdate(sql);
System.out.println("建序列成功!");
file.aLine(sql+",建序列成功!");
}
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
sql = "create or replace trigger trg_person" +
" before insert on person for each row " +
" declare " +
" begin " +
" select seq_person.nextval into :new.id from dual; " +
" end;";
try{
stmt.executeUpdate(sql);
}catch(SQLException se){
se.printStackTrace();
}
System.out.println(sql+",建触发器成功!");
file.aLine(sql+",建触发器成功!");
}else if(dbtype.equalsIgnoreCase("sqlite")||dbtype.equalsIgnoreCase("hsql")){
lStart = System.currentTimeMillis();
sql = "CREATE INDEX person_idx on person(id)";
try{
stmt.executeUpdate(sql);
}catch(SQLException se){
}
lEnd = System.currentTimeMillis();
lFee = lEnd - lStart;
in = "sql="+sql + "创建索引成功，耗时："+lFee+"ms";
System.out.println(in);
file.aLine(in);
}
/*
//在name上创建索引，暂时不用
lStart = System.currentTimeMillis();
sql = "CREATE INDEX person_idx2 on person(name)";
try{
stmt.executeUpdate(sql);
}catch(Exception e){
}
lEnd = System.currentTimeMillis();
lFee = lEnd - lStart;
in += "创建索引成功，耗时："+lFee+"ms";
System.out.println(in);
file.aLine(in);*/
}catch(Exception e){
e.printStackTrace();
}
System.out.println("正在查询总记录数...");
/*===============================总记录数===============================*/
lStart = System.currentTimeMillis();
sql="select count(*) from person";
rset = stmt.executeQuery(sql);
while (rset.next()){
in = "插入前总记录数:"+rset.getInt(1);
System.out.println(in);
file.aLine(in);
}
if (rset!=null){
rset.close();
rset = null;
}
lEnd = System.currentTimeMillis();
in = "查询总记录数，花费："+(lEnd - lStart)+"ms.";
System.out.println(in);
file.aLine(in);
/*===============================插入数据===============================*/
Random rand = new Random();
lStart = System.currentTimeMillis();
sql = "INSERT INTO person(name,name3,name4,name5,name6,name7,name8,name9,name10) VALUES(?,?,?,?,?,?,?,?,?)";
st = conn.prepareStatement(sql);
long lstart2=System.currentTimeMillis();
for (int i=0; i<insertnum; i++) {
String name="";
name="namename"+rand.nextInt(insertnum);
st.setString(1, name);
st.setInt(2, i*101);
st.setString(3, "abcdefghijklmnopqrst"+i);
st.setString(4, "abcdefghijklmnopqrst"+i);
st.setString(5, "abcdefghijklmnopqrst"+i);
st.setString(6, "abcdefghijklmnopqrst"+i);
st.setString(7, "abcdefghijklmnopqrst"+i);
st.setString(8, "abcdefghijklmnopqrst"+i);
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?