FCRcc1Ph

1/*
2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
5 */
6package com.mycompany.databaseconnection;
7
8/**
9 *
10 * @author eeu69c
11 */
12import java.sql.*;
13import java.io.*;
14
15public class mySqLTester {
16
17    public static void main(String[] args) throws IOException,
18            ClassNotFoundException, SQLException {
19
20        //Runs off the database properties file which is stored in resources
21        InputStream stream = mySqLTester.class.getResourceAsStream("/database.properties");
22        SimpleDataSource.init(stream);
23
24        Connection conn = SimpleDataSource.getConnection();
25        Statement stat = conn.createStatement();
26
27        if (conn != null) {
28            System.out.println("connection made");
29        }
30
31        try {
32
33            String studentTable = "CREATE TABLE IF NOT EXISTS student"
34                    + "(student_id CHAR(7), "
35                    + "student_name VARCHAR(20), "
36                    + "degree_scheme VARCHAR(27), " + "PRIMARY KEY (student_id))"
37                    + "ENGINE = InnoDB";
38            stat.execute(studentTable);
39
40            String staffTable = "CREATE TABLE IF NOT EXISTS staff"
41                    + "(staff_id CHAR(7), "
42                    + "staff_name VARCHAR(20), "
43                    + "grade VARCHAR(20), " + "PRIMARY KEY (staff_id))"
44                    + "ENGINE = InnoDB";
45            stat.execute(staffTable);
46
47            String moduleTable = "CREATE TABLE IF NOT EXISTS module"
48                    + "(module_id CHAR(7), "
49                    + "module_name VARCHAR(20), "
50                    + "credits CHAR(3), " + "PRIMARY KEY (module_id))"
51                    + "ENGINE = InnoDB";
52            stat.execute(moduleTable);
53
54
55            String registeredTable = "CREATE TABLE IF NOT EXISTS registered"
56                    + "(student_id CHAR(7),"
57                    + "module_id CHAR(7),"
58                    + "PRIMARY KEY (student_id, module_id), INDEX(student_id), INDEX(module_id),"
59                    + "FOREIGN KEY (student_id) REFERENCES student (student_id),"
60                    + "FOREIGN KEY (module_id) REFERENCES module (module_id))"
61                    + "ENGINE = InnoDB";
62            stat.execute(registeredTable);
63
64            String teachesTable = "CREATE TABLE IF NOT EXISTS teaches"
65                    + "(staff_id CHAR(7)," 
66                    + "module_id CHAR(7),"
67                    + "PRIMARY KEY (staff_id, module_id), INDEX(staff_id), INDEX(module_id),"
68                    + "FOREIGN KEY(staff_id) REFERENCES staff (staff_id),"
69                    + "FOREIGN KEY(module_id) REFERENCES module (module_id))"
70                    + "ENGINE = InnoDB";
71            stat.execute(teachesTable);
72            
73            
74
75
76stat.execute("INSERT INTO student VALUES('S10345','John Smith','BSc Computer Science'),"
77 		                     + "('S10346','Sian Evans', 'BSc Computer Science'),"
78                                     + "('S10347','Sean Crossan', 'BSc Electronic Engineering'),"
79                                     + "('S10348','Jamie McDonald', 'BSc Mathematics')");
80
81
82
83            //stat.execute("DROP TABLE IF EXISTS student, staff, module, registered, teaches");
84   
85        } finally {
86            conn.close();
87        }
88    }
89}