· 10 years ago · May 02, 2016, 12:03 PM
1
2import java.sql.*;
3import java.io.*;
4
5public class CreateSampleTables {
6 public static void main(String args[]) {
7 Statement stmt = null;
8 try {
9 Class.forName("com.mysql.jdbc.Driver").newInstance();
10 System.out.println("Loaded Driver");
11
12 String url = "jdbc:mysql://cs.stcc.edu/silvestri?allowMultiQueries=true";
13 String user = "readonly";
14 String password = "readonly";
15 Connection con = DriverManager.getConnection(url, user, password);
16 System.out.println("Connected to database");
17
18 stmt = con.createStatement();
19
20 System.out.println("Dropping Existing Tables");
21 stmt.executeUpdate(""
22 + "drop table if exists Department;"
23 + "drop table if exists Enrollment;"
24 + "drop table if exists TaughtBy;");
25
26 System.out.println("Creating Tables");
27 stmt.executeUpdate(""
28 + "create table Department ("
29 + " deptId char(4) not null, "
30 + " name varchar(25) unique, /* works in MYSQL */ "
31 + " chairId varchar(9), " + " collegeId varchar(4), "
32 + " constraint pkDepartment primary key (deptId));"
33 + "create table Enrollment (" + " ssn char(9) not null,"
34 + " courseId char(5) not null," + " dateRegistered date,"
35 + " grade char(1),"
36 + " constraint pkEnrollment primary key (ssn, courseId));"
37 + "create table TaughtBy (" + " courseId char(5),"
38 + " ssn char(9));");
39
40 System.out.println("Loading Tables");
41 stmt.executeUpdate(""
42 + "insert into Department values ('CS', 'Computer Science', '111221115', 'SC');"
43 + "insert into Department values ('MATH', 'Mathematics', '111221116', 'SC');"
44 + "insert into Department values ('CHEM', 'Chemistry', '111225555', 'SC');"
45 + "insert into Department values ('EDUC', 'Education', '333114444', 'EDUC');"
46 + "insert into Department values ('ACCT', 'Accounting', '333115555', 'BUSS');"
47 + "insert into Department values ('BIOL', 'Biology', '111225555', 'SC');"
48 + "insert into Enrollment values ('444111110', '11111', now(), 'A');"
49 + "insert into Enrollment values ('444111110', '11112', now(), 'B');"
50 + "insert into Enrollment values ('444111110', '11113', now(), 'C');"
51 + "insert into Enrollment values ('444111111', '11111', now(), 'D');"
52 + "insert into Enrollment values ('444111111', '11112', now(), 'F');"
53 + "insert into Enrollment values ('444111111', '11113', now(), 'A');"
54 + "insert into Enrollment values ('444111112', '11114', now(), 'B');"
55 + "insert into Enrollment values ('444111112', '11115', now(), 'C');"
56 + "insert into Enrollment values ('444111112', '11116', now(), null);"
57 + "insert into Enrollment values ('444111113', '11111', now(), null);"
58 + "insert into Enrollment values ('444111113', '11113', now(), null);"
59 + "insert into Enrollment values ('444111114', '11115', now(), null);"
60 + "insert into Enrollment values ('444111115', '11115', now(), null);"
61 + "insert into Enrollment values ('444111115', '11116', now(), null);"
62 + "insert into Enrollment values ('444111116', '11111', now(), null);"
63 + "insert into Enrollment values ('444111117', '11111', now(), null);"
64 + "insert into Enrollment values ('444111118', '11111', now(), null);"
65 + "insert into Enrollment values ('444111118', '11112', now(), null);"
66 + "insert into Enrollment values ('444111118', '11113', now(), null);"
67 + "insert into TaughtBy values ('11111', '111221111');"
68 + "insert into TaughtBy values ('11112', '111221111');"
69 + "insert into TaughtBy values ('11113', '111221111');"
70 + "insert into TaughtBy values ('11114', '111221115');"
71 + "insert into TaughtBy values ('11115', '111221110');"
72 + "insert into TaughtBy values ('11116', '111221115');"
73 + "insert into TaughtBy values ('11117', '111221116');"
74 + "insert into TaughtBy values ('11118', '111221112');"
75 );
76
77 System.out.println("Done.");
78
79 } catch (SQLException e) {
80 System.out.println("SQL Error! " + e.getMessage());
81 } catch (Exception e) {
82 e.printStackTrace();
83 } finally {
84 try {
85 stmt.close();
86 } catch (Exception e) {
87 }
88 }
89 }
90}