· 8 years ago · Dec 13, 2017, 08:50 AM
1/**
2 * @author Joshua Tepaß, Alexander Juckel & Larissa Brinkhus
3 * @version 1.0
4 */
5package aufg7;
6
7import java.util.*;
8import java.sql.*;
9
10public class Aufg7OPTIMUM
11{
12 static Connection conn;
13
14 public static void main(String[] args) throws SQLException
15 {
16 conn = connectToDB("joshua","Le3ugubU");
17 System.out.println("Verbindung hergestellt");
18
19 createTPSdb(10);
20 }
21/**
22* Connects to Database "aufgabe7" on the local SQL server
23* @throws SQLException
24* */
25 public static Connection connectToDB(String user, String password) throws SQLException
26 {
27 Connection testconnection = DriverManager.getConnection(
28 "jdbc:mysql://localhost:3307/aufgabe7",
29 user,
30 password);
31 return testconnection;
32 }
33
34/**
35 * Drops the tables if they already exists
36 * Creates tables
37 */
38 public static void createTables() throws SQLException
39 {
40 Statement stmt = conn.createStatement();
41
42 stmt.executeUpdate("USE aufgabe7");
43
44 stmt.executeUpdate("DROP TABLE IF EXISTS history");
45 stmt.executeUpdate("DROP TABLE IF EXISTS tellers");
46 stmt.executeUpdate("DROP TABLE IF EXISTS accounts");
47 stmt.executeUpdate("DROP TABLE IF EXISTS branches");
48
49 String branches = "CREATE TABLE IF NOT EXISTS branches("
50 + "branchid INT NOT NULL,"
51 + "branchname VARCHAR(20) NOT NULL,"
52 + "balance INT NOT NULL,"
53 + "address VARCHAR(68) NOT NULL,"
54 + "PRIMARY KEY (branchid))";
55
56 stmt.executeUpdate(branches);
57 System.out.println("Tabelle branches erstellt");
58
59
60 String accounts = "CREATE TABLE IF NOT EXISTS accounts("
61 + "accid INT NOT NULL,"
62 + "name VARCHAR(20) NOT NULL,"
63 + "balance INT NOT NULL,"
64 + "branchid INT NOT NULL,"
65 + "address VARCHAR(68) NOT NULL,"
66 + "PRIMARY KEY (accid),"
67 + "FOREIGN KEY (branchid) REFERENCES branches (branchid))";
68
69 stmt.executeUpdate(accounts);
70 System.out.println("Tabelle accounts erstellt");
71
72
73 String tellers = "CREATE TABLE IF NOT EXISTS tellers("
74 + "tellerid INT NOT NULL,"
75 + "tellername VARCHAR(20) NOT NULL,"
76 + "balance INT NOT NULL,"
77 + "branchid INT NOT NULL,"
78 + "address VARCHAR(68) NOT NULL,"
79 + "PRIMARY KEY (tellerid),"
80 + "FOREIGN KEY (branchid) REFERENCES branches (branchid))";
81
82 stmt.executeUpdate(tellers);
83 System.out.println("Tabelle tellers erstellt");
84
85
86 String history = "CREATE TABLE IF NOT EXISTS history("
87 + "accid INT NOT NULL,"
88 + "tellerid INT NOT NULL,"
89 + "delta INT NOT NULL,"
90 + "branchid INT NOT NULL,"
91 + "accbalance INT NOT NULL,"
92 + "cmmnt VARCHAR(30) NOT NULL,"
93 + "FOREIGN KEY (accid) REFERENCES accounts (accid),"
94 + "FOREIGN KEY (tellerid) REFERENCES tellers (tellerid),"
95 + "FOREIGN KEY (branchid) REFERENCES branches (branchid))";
96
97 stmt.executeUpdate(history);
98 System.out.println("Tabelle history erstellt");
99
100 }
101
102 static SplittableRandom random = new SplittableRandom();
103/*
104 * Fill tables
105 */
106 public static void fillTables(int n) throws SQLException
107 {
108 conn.setAutoCommit(false);
109
110 long startTime = System.currentTimeMillis();
111
112 String str68 = "12345678901234567890123456789012345678901234567890123456789012345678";
113 String str20 = "12345678901234567890";
114
115 PreparedStatement branches = conn.prepareStatement("INSERT INTO branches(branchid,branchname,balance,address) VALUES (?,?,?,?)");
116 PreparedStatement accounts = conn.prepareStatement("INSERT INTO accounts(accid,name,balance,branchid,address) VALUES (?,?,?,?,?)");
117 PreparedStatement tellers = conn.prepareStatement("INSERT INTO tellers(tellerid,tellername,balance,branchid,address) VALUES (?,?,?,?,?)");
118
119 for(int i=1;i<=n;i++)
120 {
121 branches.setInt(1, i);
122 branches.setString(2, str20);
123 branches.setInt(3, 0);
124 branches.setString(4, str68);
125 branches.executeUpdate();
126 }
127 System.out.println("Branches gefüllt");
128
129 for(int j=1;j<=n*100000;j++)
130 {
131 if(j%10000==0)
132 {
133 conn.commit();
134 }
135 accounts.setInt(1, j);
136 accounts.setString(2, str20);
137 accounts.setInt(3, 0);
138 accounts.setInt(4, (int)(random.nextDouble()*n+1));
139 accounts.setString(5, str68);
140 accounts.executeUpdate();
141 }
142 System.out.println("Accounts gefüllt");
143
144 for(int k=1;k<=n*10;k++)
145 {
146 tellers.setInt(1, k);
147 tellers.setString(2, str20);
148 tellers.setInt(3, 0);
149 tellers.setInt(4, (int)(random.nextDouble()*n+1));
150 tellers.setString(5, str68);
151 tellers.executeUpdate();
152 }
153 System.out.println("Tellers gefüllt");
154
155 long stopTime = System.currentTimeMillis();
156 double elapsedTime = (stopTime - startTime)/1000;
157 System.out.println(elapsedTime);
158
159 conn.commit();
160 }
161
162 public static void createTPSdb(int n) throws SQLException
163 {
164 createTables();
165 fillTables(n);
166 }
167}