createtable.java

一个可以存储和打印成绩单的系统

package org.wuhang.transfer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.minjey.cjsjk.dao.SQLDict;

public class CreateTable {
	String station_sql;
	String enrolltime_sql;
	String majorrecord_sql;
	String majortype_sql;
	String majorlength_sql;
	String major_sql;
	String examtype_sql;
	String course_sql;
	String studentstatue_sql;
	String student_sql;
	String studentcourse_sql;
	String admin_sql;
	String check_sql;
	public CreateTable(String type) {
		this.createTable(type);
	}
	public CreateTable(Connection javadb_con,String type){
		this.createTable(javadb_con,type);
	}
	
	private Connection getConnection(){
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url1 = "jdbc:odbc:driver={microsoft access driver (*.mdb)};dbq=Cjsjk-copy.jar;pwd=12-343"; // ��λ
			Connection old_con = DriverManager.getConnection(url1);
			return old_con;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	private void createStation(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
				station_sql = SQLDict.TSTACTION;
			else
				station_sql = 
					"CREATE TABLE station(id counter(1,1) PRIMARY KEY,name VARCHAR(30) NOT NULL," +
					"sadmin VARCHAR(20),address VARCHAR(50),postcode VARCHAR(6),telephone " +
					"VARCHAR(16),bz VARCHAR(255))";
			 statement.execute(station_sql); 
			 statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	}
	
	private void createErolltime(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
				enrolltime_sql = SQLDict.TENROLLTIME;
		    else
			    enrolltime_sql = "CREATE TABLE enrolltime(id counter(1,1) PRIMARY KEY,etime DATE NOT NULL)";
			 statement.execute(enrolltime_sql); 
			 statement.close();
			 System.out.println("create table enrolltime");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	}
	
	private void createMajorrecord(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
				majorrecord_sql = SQLDict.TMAJORRECORD;
			else
			    majorrecord_sql = "CREATE TABLE majorrecord(id counter(1,1) PRIMARY KEY,name VARCHAR(10) NOT NULL)";
			 statement.execute(majorrecord_sql); 
			 statement.close();
			 System.out.println("create table majorrecord");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	}
	
	private void createMajorType(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
				majortype_sql = SQLDict.TMAJORTYPE;
			else
			    majortype_sql = "CREATE TABLE majortype(id counter(1,1) PRIMARY KEY,name VARCHAR(10) NOT NULL)";
			 statement.execute(majortype_sql); 
			 statement.close();
			 System.out.println("create table majortype");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	}
	
	private void createMajorLength(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
				majorlength_sql = SQLDict.TMAJORLENGTH;
			else
			    majorlength_sql = "CREATE TABLE majorlength(id counter(1,1) PRIMARY KEY,length INT NOT NULL)";
			 statement.execute(majorlength_sql); 
			 statement.close();
			 System.out.println("create table majorlength");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	}
	
	private void createMajor(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
				major_sql = SQLDict.TMAJOR;
			else
			    major_sql = "CREATE TABLE major(id counter(1,1) PRIMARY KEY,name VARCHAR(30) NOT NULL," +
					"code VARCHAR(15),intime DATE NOT NULL,outtime DATE,bz VARCHAR(255)," +
					"station_id INT NOT NULL,enrolltime_id INT NOT NULL,majorrecord_id " +
					"INT NOT NULL,majortype_id INT NOT NULL,majorlength_id INT NOT NULL," +
					"FOREIGN KEY (station_id) REFERENCES station(id)," +
					"FOREIGN KEY (enrolltime_id) REFERENCES enrolltime(id)," +
					"FOREIGN KEY (majorrecord_id) REFERENCES majorrecord(id)," +
					"FOREIGN KEY (majortype_id) REFERENCES majortype(id)," +
					"FOREIGN KEY (majorlength_id) REFERENCES majorlength(id))";
			 statement.execute(major_sql); 
			 statement.close();
			 System.out.println("create table major");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	}
	
	private void createExamType(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
			    examtype_sql = SQLDict.TEXAMTYPE;
			else
				examtype_sql = 	"CREATE TABLE examtype(id counter(1,1)PRIMARY KEY,name VARCHAR(10) NOT NULL)";
			 statement.execute(examtype_sql); 
			 statement.close();
			 System.out.println("create table examtype");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	private void createCourse(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
			    course_sql = SQLDict.TCOURSE;
			else
				course_sql = "CREATE TABLE course(id counter(1,1) PRIMARY KEY,corder INT NOT NULL," +
			"name VARCHAR(30) NOT NULL,length INT NOT NULL,point FLOAT NOT NULL," +
			"semester INT NOT NULL,teacher VARCHAR(20),book VARCHAR(50),price FLOAT," +
			"publisher VARCHAR(50),bz VARCHAR(255),major_id INT NOT NULL,examtype_id INT NOT NULL," +
			"FOREIGN KEY (major_id) REFERENCES major(id)," +
			"FOREIGN KEY (examtype_id) REFERENCES examtype(id))";
			 statement.execute(course_sql); 
			 statement.close();
			 System.out.println("create table course");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	private void createStudentStatue(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
			    studentstatue_sql = SQLDict.TSTUDENTSTATUE;
			else
				studentstatue_sql = "CREATE TABLE studentstatue(id counter(1,1) PRIMARY KEY,name VARCHAR(10) NOT NULL)";
			statement.execute(studentstatue_sql); 
			 statement.close();
			 System.out.println("create table studentstatue");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	private void createStudent(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
			        student_sql = SQLDict.TSTUDENT;
			else
				student_sql = "CREATE TABLE student(id counter(1,1) PRIMARY KEY," +
				"sorder INT NOT NULL," +
				"stuno VARCHAR(20)," +
				"stuid VARCHAR(18)," +
				"name VARCHAR(20) NOT NULL," +
				"age INT," +
				"sex VARCHAR(2)," +
				"bz VARCHAR(255)," +
				"major_id INT NOT NULL," +
				"studentstatue_id INT NOT NULL," +
				"FOREIGN KEY (major_id) REFERENCES major(id)," +
				"FOREIGN KEY (studentstatue_id) REFERENCES studentstatue(id))";
			 statement.execute(student_sql); 
			 statement.close();
			 System.out.println("create table student");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	}
	
	private void createStudentCourse(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
			     studentcourse_sql = SQLDict.TSTUDENTCOURSE;
			else
				 studentcourse_sql = "CREATE TABLE studentcourse(id counter(1,1) PRIMARY KEY," +
			 		"student_id INT NOT NULL," +
			 		"course_id INT NOT NULL," +
			 		"grade VARCHAR(5)," +
			 		"UNIQUE (student_id, course_id)," +
			 		"FOREIGN KEY (student_id) REFERENCES student(id)," +
			 		"FOREIGN KEY (course_id) REFERENCES course(id))"; 
			 statement.execute(studentcourse_sql); 
			 statement.close();
			 System.out.println("create table studentcourse");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	private void createAdmin(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
			    admin_sql = SQLDict.TADMIN;
			else
				admin_sql = "CREATE TABLE admin(id counter(1,1) PRIMARY KEY," +
				"username VARCHAR(16) NOT NULL," +
				"password VARCHAR(255) NOT NULL)";
			 statement.execute(admin_sql); 
			 statement.close();
			 System.out.println("create table admin");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}

	private void createCheck(Connection con,Statement statement,String type){
		try {
			statement = con.createStatement();
			if("javadb".equals(type))
			    check_sql = SQLDict.TCHECK;
			else
			    check_sql = "CREATE TABLE Tcheck(id counter(1,1) PRIMARY KEY," +
			"checkcode VARCHAR(255) NOT NULL," +
			"updatetime DATE NOT NULL,bz VARCHAR(255))";
			 statement.execute(check_sql); 
			 statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	private void createTable(String type){
		Connection con = getConnection();
		Statement statement = null;
		try {
			statement = con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		this.createErolltime(con, statement,type);
		this.createMajorrecord(con, statement,type);
		this.createMajorType(con, statement,type);
		this.createMajorLength(con, statement,type);
		this.createStation(con, statement,type);
		this.createMajor(con, statement,type);
		this.createExamType(con, statement,type);
		this.createCourse(con, statement,type);
		this.createStudentStatue(con, statement,type);
		this.createStudent(con, statement,type);
		this.createStudentCourse(con, statement,type);
		this.createAdmin(con, statement,type);
		this.createCheck(con, statement,type);
		try {
			con.commit();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
   
	private void createTable(Connection javadb_con,String type){
		Connection con = javadb_con;
		Statement statement = null;
		try {
			statement = con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.createErolltime(con, statement,type);
		this.createMajorrecord(con, statement,type);
		this.createMajorType(con, statement,type);
		this.createMajorLength(con, statement,type);
		this.createStation(con, statement,type);
		this.createMajor(con, statement,type);
		this.createExamType(con, statement,type);
		this.createCourse(con, statement,type);
		this.createStudentStatue(con, statement,type);
		this.createStudent(con, statement,type);
		this.createStudentCourse(con, statement,type);
		this.createAdmin(con, statement,type);
		try {
			con.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/*public static void main(String args[]){
		new CreateTable();
	}*/
}