sqldict.java

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

package org.minjey.cjsjk.dao;

public class SQLDict {
	//public static String USERNAME = "admin";
	//public static String PASSWORD = "admin";
	public static String DDB = "Cjsjk";
	public static String TADMIN = 
		"CREATE TABLE admin(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+ "\n" + "\t" +
			"username VARCHAR(16) NOT NULL," + "\n" + "\t" +
			"password VARCHAR(255) NOT NULL" + "\n" +
		")";
	public static String TCHECK = 
		"CREATE TABLE tcheck(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+ "\n" + "\t" +
			"checkcode VARCHAR(255) NOT NULL," + "\n" + "\t" +
			"updatetime DATE NOT NULL," + "\n" + "\t" +
			"bz VARCHAR(255)" + "\n" +
		")";
	public static String TSTACTION = 
		"CREATE TABLE station(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"+ "\n" + "\t" +
			"name VARCHAR(30) NOT NULL," + "\n" + "\t" +
			"sadmin VARCHAR(20)," + "\n" + "\t" +
			"address VARCHAR(50)," + "\n" + "\t" +
			"postcode VARCHAR(6)," + "\n" + "\t" +
			"telephone VARCHAR(16)," + "\n" + "\t" +
			"bz VARCHAR(255)" + "\n" +
		")";
	public static String TENROLLTIME = 
		"CREATE TABLE enrolltime(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"etime DATE NOT NULL" + "\n" +
		")";
	public static String TMAJORRECORD =
		"CREATE TABLE majorrecord(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"name VARCHAR(10) NOT NULL" + "\n" +
		")";
	public static String TMAJORTYPE =
		"CREATE TABLE majortype(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"name VARCHAR(10) NOT NULL" + "\n" +
		")";
	public static String TMAJORLENGTH =
		"CREATE TABLE majorlength(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"length INT NOT NULL" + "\n" + "\t" +
		")";
	public static String TMAJOR =
		"CREATE TABLE major(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"name VARCHAR(30) NOT NULL," + "\n" + "\t" +
			"code VARCHAR(15)," + "\n" + "\t" +
			"intime DATE NOT NULL," + "\n" + "\t" +
			"outtime DATE," + "\n" + "\t" +
			"bz VARCHAR(255)," + "\n" + "\t" +
			"station_id INT NOT NULL," + "\n" + "\t" +
			"enrolltime_id INT NOT NULL," + "\n" + "\t" +
			"majorrecord_id INT NOT NULL," + "\n" + "\t" +
			"majortype_id INT NOT NULL," + "\n" + "\t" +
			"majorlength_id INT NOT NULL," + "\n" + "\t" +
			"FOREIGN KEY (station_id) REFERENCES station(id) ON DELETE CASCADE," + "\n" + "\t" +
			"FOREIGN KEY (enrolltime_id) REFERENCES enrolltime(id)," + "\n" + "\t" +
			"FOREIGN KEY (majorrecord_id) REFERENCES majorrecord(id)," + "\n" + "\t" +
			"FOREIGN KEY (majortype_id) REFERENCES majortype(id)," + "\n" + "\t" +
			"FOREIGN KEY (majorlength_id) REFERENCES majorlength(id)" + "\n" +
		")";
	public static String TEXAMTYPE =
		"CREATE TABLE examtype(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"name VARCHAR(10) NOT NULL" + "\n" +
		")";
	public static String TCOURSE =
		"CREATE TABLE course(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"corder INT NOT NULL," + "\n" + "\t" +
			"name VARCHAR(30) NOT NULL," + "\n" + "\t" +
			"length INT NOT NULL," + "\n" + "\t" +
			"point FLOAT NOT NULL," + "\n" + "\t" +
			"semester INT NOT NULL," + "\n" + "\t" +
			"teacher VARCHAR(20)," + "\n" + "\t" +
			"book VARCHAR(50)," + "\n" + "\t" +
			"price FLOAT," + "\n" + "\t" +
			"publisher VARCHAR(50)," + "\n" + "\t" +
			"bz VARCHAR(255)," + "\n" + "\t" +
			"major_id INT NOT NULL," + "\n" + "\t" +
			"examtype_id INT NOT NULL," + "\n" + "\t" +
			"FOREIGN KEY (major_id) REFERENCES major(id) ON DELETE CASCADE," + "\n" + "\t" +
			"FOREIGN KEY (examtype_id) REFERENCES examtype(id)" + "\n" +
		")";
	public static String TSTUDENTSTATUE =
		"CREATE TABLE studentstatue(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"name VARCHAR(10) NOT NULL" + "\n" +
		")";
	public static String TSTUDENT =
		"CREATE TABLE student(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"sorder INT NOT NULL," + "\n" + "\t" +
			"stuno VARCHAR(20)," + "\n" + "\t" +
			"stuid VARCHAR(18)," + "\n" + "\t" +
			"name VARCHAR(20) NOT NULL," + "\n" + "\t" +
			"age INT," + "\n" + "\t" +
			"sex VARCHAR(2)," + "\n" + "\t" +
			"bz VARCHAR(255)," + "\n" + "\t" +
			"major_id INT NOT NULL," + "\n" + "\t" +
			"studentstatue_id INT NOT NULL," + "\n" + "\t" +
			"FOREIGN KEY (major_id) REFERENCES major(id) ON DELETE CASCADE," + "\n" + "\t" +
			"FOREIGN KEY (studentstatue_id) REFERENCES studentstatue(id)" + "\n" +
		")";
	public static String TSTUDENTCOURSE =
		"CREATE TABLE studentcourse(" + "\n" + "\t" +
			"id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + "\n" + "\t" +
			"student_id INT NOT NULL," + "\n" + "\t" +
			"course_id INT NOT NULL," + "\n" + "\t" +
			"grade VARCHAR(5)," + "\n" + "\t" +
			//"UNIQUE (student_id, course_id)," + "\n" + "\t" +
			"FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE," + "\n" + "\t" +
			"FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE" + "\n" +
		")";
	public static String IMAJORSTATION =
		"CREATE INDEX majorstation " + "\n" +
		"ON major(station_id)";
	public static String IMAJORINROLLTIME =
		"CREATE INDEX majorenrolltime " + "\n" +
		"ON major(enrolltime_id)";
	public static String IMAJORMAJORRECORD =
		"CREATE INDEX majormajorrecord " + "\n" +
		"ON major(majorrecord_id)";
	public static String IMAJORMAJORTYPE =
		"CREATE INDEX majormajortype " + "\n" +
		"ON major(majortype_id)";
	/*	
	public static String IMAJORMAJORLENGTH =
		"CREATE INDEX majormajorlength " + "\n" +
		"ON major(majorlength_id)";
	*/
	public static String ICOURSEMAJOR =
		"CREATE INDEX coursemajor " + "\n" +
		"ON course(major_id)";
	/*
	public static String ICOURSEEXAMTYPE =
		"CREATE INDEX courseexamtype " +
		"ON course(examtype_id)";
	*/
	public static String ISTUDENTMAJOR =
		"CREATE INDEX studentmajor " + "\n" +
		"ON student(major_id)";
	/*
	public static String ISTUDENTSTATUE =
		"CREATE INDEX studentstatue " + "\n" +
		"ON student(studentstatue_id)";
	*/
}