screatetable.java

来自「主要对各种数据库性能进行测试」· Java 代码 · 共 251 行

package sqlite; 
 
import java.sql.ResultSet; 
import java.sql.SQLException;

public class SCreateTable extends ServerFactory { 
	int newval = 0;
	static int ist,thenum = 0; 
	static long max=0,min=0,total=0;
	long avg=0; 
	String table = "users",showprint,dbtype = "";
	
	ExecSql  exec = new ExecSql();
	WriteFile file = new WriteFile(); 
	Configure config = new Configure(); 
		
	public SCreateTable() { 
		String dbtype = config.getProperty("dbtype");
		showprint = config.getProperty("showprint"); 
		this.dbtype = dbtype; 
		exec = new ExecSql();
		this.init();
		
	}  

	@Override
	public int Exec() {
		ResultSet rs = null;
		thenum ++;
		newval = thenum; 
		String sql ;
		int count = 0;
		try {  
			/*
			 * mysql,oracle 会自动在主键上创建索引
			 */
			if(dbtype.equalsIgnoreCase("sqlite")){  
				sql = "select count(*) from sqlite_master where type='table' and name='"+table+"'";
				System.out.println(sql);
				rs = exec.select(conn, sql); 
				if(rs.next()){ 
			    	count = rs.getInt(1); 
			    }
				System.out.println(rs);
				exec.closeResult(rs);  
				System.out.println(count);
				if(count == 0){ 
					sql = "create table "+table+" (userid integer primary key ,column2 varchar(30),column3 varchar(30),column4 varchar(30),column5 varchar(30),column6 varchar(30),column7 varchar(30),column8 varchar(30),column9 varchar(30),column10 varchar(30))";
					exec.exec(conn, sql);
					System.out.println(sql+"建表成功!");  
					file.aLine(sql+",建表成功!"); 
					sql = "create index idx_"+table+" on "+table+"(userid)";
					exec.exec(conn, sql);
					System.out.println(sql+"建索引成功!"); 
					file.aLine(sql+",建索引成功!");
				}//
			}else if(dbtype.equalsIgnoreCase("hsql")){  
			 
				sql = "create table "+table+" (userid int generated by default as identity(start with 1,increment by 1) not null primary key,column2 varchar(30),column3 varchar(30),column4 varchar(30),column5 varchar(30),column6 varchar(30),column7 varchar(30),column8 varchar(30),column9 varchar(30),column10 varchar(30))";
				System.out.println(sql);
				int result = exec.exec(conn, sql);
				if(result==-1){
					System.out.println("建表失败，该表已经存在!"); 
					file.aLine(sql+",建表失败，该表已经存在!"); 
				}else{
					System.out.println("建表成功!"); 
					file.aLine(sql+",建表成功!"); 
					sql = "create index idx_"+table+" on "+table+"(userid)";
			 		System.out.println(sql);
					exec.exec(conn, sql);
					System.out.println("建索引成功!"); 
					file.aLine(sql+",建索引成功!"); 
				} 
			}else if(dbtype.equalsIgnoreCase("oracle")){  
	 
				sql = "select count(1) from user_tables where lower(table_name) = '" + table + "'";
				
				rs = exec.select(conn, sql); 
				if(rs.next()){ 
			    	count = rs.getInt(1); 
			    } 
				exec.closeResult(rs);  
				//System.out.println(count);
				if(count < 1){  
 					sql = "create table "+table+" (userid int not null primary key,column2 varchar(30),column3 varchar(30),column4 varchar(30),column5 varchar(30),column6 varchar(30),column7 varchar(30),column8 varchar(30),column9 varchar(30),column10 varchar(30))";
					System.out.println(sql);
					
					int result = exec.exec(conn, sql); 
					if (result == 1){
						System.out.println("建表成功!"); 
						file.aLine(sql+",建表成功!"); 
					}else{
						System.out.println("建表失败，该表已经存在!"); 
						file.aLine(sql+",建表失败，该表已经存在!");  
						return 0;
					}
					
					count = 0;
					sql = "select count(1) from user_objects a where a.object_type = 'SEQUENCE' and a.object_name = 'SEQ_"+table.toUpperCase()+"'";
					rs = exec.select(conn, sql); 
					if(rs.next()){ 
				    	count = rs.getInt(1); 
				    } 
					exec.closeResult(rs);  
					//System.out.println(count);
					if(count==0 ){
						sql = "create sequence seq_"+table;
						System.out.println(sql);
						try{
							result = exec.exec(conn, sql); 
							System.out.println("建序列成功!"); 
							file.aLine(sql+",建序列成功!");
						}catch( Exception e){
							
						}
					}
					sql = "create or replace trigger trg_" + table +
							" before insert on users for each row " +
							" declare " +
							" begin " +
								" select seq_users.nextval into :new.userid from dual; " +
							" end;";
					System.out.println(sql);
					result = exec.exec(conn, sql); 
					
					System.out.println("建触发器成功!"+result); 
					file.aLine(sql+",建触发器成功!"); 
					 
				} 
			}else if(dbtype.equalsIgnoreCase("mysql")){  
				sql = "create table if not exists "+table+" (userid integer auto_increment,column2 varchar(30),column3 varchar(30),column4 varchar(30),column5 varchar(30),column6 varchar(30),column7 varchar(30),column8 varchar(30),column9 varchar(30),column10 varchar(30),primary key(userid))";
				System.out.println(sql);
				
				int result = exec.exec(conn, sql); 
				if (result == 1){
					System.out.println("建表成功!"); 
					file.aLine(sql+",建表成功!"); 
				}else{
					System.out.println("建表失败，该表已经存在!"); 
					file.aLine(sql+",建表失败，该表已经存在!");  
				} 
			}else if(dbtype.equalsIgnoreCase("enterprisedb")){  
	 
				sql = "select count(1) from user_tables where table_name = '" + table.toUpperCase() + "'";
				
				rs = exec.select(conn, sql); 
				if(rs.next()){ 
			    	count = rs.getInt(1); 
			    } 
				exec.closeResult(rs);  
				//System.out.println(count);
				if(count < 1){  
 					sql = "create table "+table+" (userid int not null primary key,column2 varchar(30),column3 varchar(30),column4 varchar(30),column5 varchar(30),column6 varchar(30),column7 varchar(30),column8 varchar(30),column9 varchar(30),column10 varchar(30))";
					System.out.println(sql);
					
					int result = exec.exec(conn, sql); 
					if (result == 1){
						System.out.println("建表成功!"); 
						file.aLine(sql+",建表成功!"); 
					}else{
						System.out.println("建表失败，该表已经存在!"); 
						file.aLine(sql+",建表失败，该表已经存在!");  
						return 0;
					}
					
					count = 0;
					sql = "select count(1) from user_objects a where a.object_type = 'SEQUENCE' and a.object_name = 'SEQ_"+table.toUpperCase()+"'";
					rs = exec.select(conn, sql); 
					if(rs.next()){ 
				    	count = rs.getInt(1); 
				    } 
					exec.closeResult(rs);  
					System.out.println(count);
					if(count==0 ){
						sql = "create sequence seq_"+table;
						System.out.println(sql);
						try{
							result = exec.exec(conn, sql); 
							System.out.println("建序列成功!"); 
							file.aLine(sql+",建序列成功!");
						}catch( Exception e){
							
						}
					}
					sql = "create or replace trigger trg_" + table +
							" before insert on users for each row " +
							" declare " +
							" begin " +
								" select seq_users.nextval into :new.userid from dual; " +
							" end;";
					System.out.println(sql);
					result = exec.exec(conn, sql); 
					
					System.out.println("建触发器成功!"+result); 
					file.aLine(sql+",建触发器成功!"); 
					 
				} 
			}else if(dbtype.equalsIgnoreCase("kingbase")){  
			 
				sql = "create table "+table+" (userid int identity,column2 varchar(30),column3 varchar(30),column4 varchar(30),column5 varchar(30),column6 varchar(30),column7 varchar(30),column8 varchar(30),column9 varchar(30),column10 varchar(30),constraint pk_users primary key(userid))";
				System.out.println(sql);
				int result = exec.exec(conn, sql);
				if(result==-1){
					System.out.println("建表失败，该表已经存在!"); 
					file.aLine(sql+",建表失败，该表已经存在!"); 
				}else{
					System.out.println("建表成功!"); 
					file.aLine(sql+",建表成功!"); 
					sql = "create index idx_"+table+" on "+table+"(userid)";
			 		System.out.println(sql);
					exec.exec(conn, sql);
					System.out.println("建索引成功!"); 
					file.aLine(sql+",建索引成功!"); 
			}	}  
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("SCreateTable.class");
		}finally{ 
			freeConnection(this.dbtype);
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		return 1;
	}
		// TODO Auto-generated method stub  
		
		@Override
		public int init() {
			// TODO Auto-generated method stub
			this.dbtype = config.getProperty("dbtype");
			
			if(!initConnection(this.dbtype)) {
				System.out.println("create database connection failed!");
				return -1;
			}
			return 0;
		}
		public static void main(String[] args) {
			SCreateTable as = new SCreateTable();
			as.Exec();
		}
	}