· 6 years ago · Nov 02, 2019, 03:32 PM
1public void criarBanco() {
2
3 Connection conn = null;
4 Statement stmt = null;
5 try {
6 //STEP 2: Register JDBC driver
7 Class.forName("com.mysql.jdbc.Driver");
8
9 //STEP 3: Open a connection
10 System.out.println("Connecting to database...");
11 conn = DriverManager.getConnection(DB_URL, USER, PASS);
12
13 //STEP 4: Execute a query
14 System.out.println("Creating database...");
15 stmt = conn.createStatement();
16
17 String sql = "-- MySQL Workbench Forward Engineering\n" +
18 "\n" +
19 "SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;\n" +
20 "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n" +
21 "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';\n" +
22 "\n" +
23 "-- -----------------------------------------------------\n" +
24 "-- Schema dbScoutTcc\n" +
25 "-- -----------------------------------------------------\n" +
26 "-- Esse é o banco de dados do TCC Scout Profissional. Desenvolvido por Keven, Gustavo, Thaís Medeiros e Wesley. \n" +
27 "\n" +
28 "-- -----------------------------------------------------\n" +
29 "-- Schema dbScoutTcc\n" +
30 "--\n" +
31 "-- Esse é o banco de dados do TCC Scout Profissional. Desenvolvido por Keven, Gustavo, Thaís Medeiros e Wesley. \n" +
32 "-- -----------------------------------------------------\n" +
33 "CREATE SCHEMA IF NOT EXISTS `dbScoutTcc` DEFAULT CHARACTER SET utf8 ;\n" +
34 "USE `dbScoutTcc` ;\n" +
35 "\n" +
36 "-- -----------------------------------------------------\n" +
37 "-- Table `dbScoutTcc`.`Responsaveis`\n" +
38 "-- -----------------------------------------------------\n" +
39 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`Responsaveis` (\n" +
40 " `resCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
41 " `resNome` VARCHAR(60) NOT NULL,\n" +
42 " `resCPF` VARCHAR(14) NOT NULL,\n" +
43 " `resRG` VARCHAR(12) NOT NULL,\n" +
44 " `resEmail` VARCHAR(45) NOT NULL,\n" +
45 " `resTelefone` VARCHAR(13) NULL,\n" +
46 " `resSenha` VARCHAR(10) NOT NULL,\n" +
47 " `resFoto` BLOB NULL,\n" +
48 " `resObservacao` TEXT NULL,\n" +
49 " `resPerm` TINYINT(3) NOT NULL,\n" +
50 " PRIMARY KEY (`resCodigo`))\n" +
51 "ENGINE = InnoDB\n" +
52 "DEFAULT CHARACTER SET = utf8\n" +
53 "COMMENT = 'Aqui estão armazenadas todas as informações necessárias para a tabela Responsáveis. \\n\\n\\n';\n" +
54 "\n" +
55 "\n" +
56 "-- -----------------------------------------------------\n" +
57 "-- Table `dbScoutTcc`.`Jogadores`\n" +
58 "-- -----------------------------------------------------\n" +
59 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`Jogadores` (\n" +
60 " `jogCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
61 " `jogNome` VARCHAR(60) NOT NULL,\n" +
62 " `jogAltura` FLOAT NOT NULL,\n" +
63 " `jogSexo` CHAR(1) NULL,\n" +
64 " `jogDataNascimento` DATE NOT NULL,\n" +
65 " `jogCPF` VARCHAR(14) NOT NULL,\n" +
66 " `jogRG` VARCHAR(12) NOT NULL,\n" +
67 " `jogEmail` VARCHAR(45) NOT NULL,\n" +
68 " `jogTelefone` VARCHAR(13) NULL,\n" +
69 " `jogFoto` BLOB NULL,\n" +
70 " `jogResponsavelFK` INT NULL,\n" +
71 " `jogObservacao` TEXT NULL,\n" +
72 " PRIMARY KEY (`jogCodigo`),\n" +
73 " INDEX `fk_Jogadores_Responsaveis1_idx` (`jogResponsavelFK` ASC),\n" +
74 " CONSTRAINT `fk_Jogadores_Responsaveis1`\n" +
75 " FOREIGN KEY (`jogResponsavelFK`)\n" +
76 " REFERENCES `dbScoutTcc`.`Responsaveis` (`resCodigo`)\n" +
77 " ON DELETE NO ACTION\n" +
78 " ON UPDATE NO ACTION)\n" +
79 "ENGINE = InnoDB\n" +
80 "DEFAULT CHARACTER SET = utf8\n" +
81 "COMMENT = 'Nesta tabela estão armazenadas todas as informações de Jogadores. \\n\\n\\n\\n\\n';\n" +
82 "\n" +
83 "\n" +
84 "-- -----------------------------------------------------\n" +
85 "-- Table `dbScoutTcc`.`Times`\n" +
86 "-- -----------------------------------------------------\n" +
87 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`Times` (\n" +
88 " `timCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
89 " `timNome` VARCHAR(25) NOT NULL,\n" +
90 " `timModalidade` VARCHAR(30) NOT NULL,\n" +
91 " `timEscola` VARCHAR(35) NOT NULL,\n" +
92 " `timMunicipio` VARCHAR(30) NOT NULL,\n" +
93 " `timEstado` CHAR(2) NULL,\n" +
94 " PRIMARY KEY (`timCodigo`))\n" +
95 "ENGINE = InnoDB\n" +
96 "DEFAULT CHARACTER SET = utf8\n" +
97 "COMMENT = 'Nesta tabela estão todas as informações dos Times. ';\n" +
98 "\n" +
99 "\n" +
100 "-- -----------------------------------------------------\n" +
101 "-- Table `dbScoutTcc`.`JogTimes`\n" +
102 "-- -----------------------------------------------------\n" +
103 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`JogTimes` (\n" +
104 " `jogtCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
105 " `jogtJogadoresFK` INT NOT NULL,\n" +
106 " `jogtTimesFK` INT NOT NULL,\n" +
107 " `jogtDataSaida` DATE NULL,\n" +
108 " `jogtDataEntrada` DATE NOT NULL,\n" +
109 " `jogtTitular` TINYINT(1) NOT NULL,\n" +
110 " `jogtLider` TINYINT(1) NOT NULL,\n" +
111 " `jogtNumeroCamisa` CHAR(2) NOT NULL,\n" +
112 " `jogtPosicao` VARCHAR(25) NULL,\n" +
113 " PRIMARY KEY (`jogtCodigo`),\n" +
114 " INDEX `fk_JogTimes_Jogadores_idx` (`jogtJogadoresFK` ASC),\n" +
115 " INDEX `fk_JogTimes_Times1_idx` (`jogtTimesFK` ASC),\n" +
116 " CONSTRAINT `fk_JogTimes_Jogadores`\n" +
117 " FOREIGN KEY (`jogtJogadoresFK`)\n" +
118 " REFERENCES `dbScoutTcc`.`Jogadores` (`jogCodigo`)\n" +
119 " ON DELETE NO ACTION\n" +
120 " ON UPDATE NO ACTION,\n" +
121 " CONSTRAINT `fk_JogTimes_Times1`\n" +
122 " FOREIGN KEY (`jogtTimesFK`)\n" +
123 " REFERENCES `dbScoutTcc`.`Times` (`timCodigo`)\n" +
124 " ON DELETE NO ACTION\n" +
125 " ON UPDATE NO ACTION)\n" +
126 "ENGINE = InnoDB\n" +
127 "DEFAULT CHARACTER SET = utf8\n" +
128 "COMMENT = 'Nesta tabela está o resultado entre a relação das tabelas Jogadores e Times. ';\n" +
129 "\n" +
130 "\n" +
131 "-- -----------------------------------------------------\n" +
132 "-- Table `dbScoutTcc`.`TimesRes`\n" +
133 "-- -----------------------------------------------------\n" +
134 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`TimesRes` (\n" +
135 " `timrCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
136 " `timrResponsaveisFK` INT NOT NULL,\n" +
137 " `timrTimesFK` INT NOT NULL,\n" +
138 " `timrPresidente` TINYINT(1) NOT NULL,\n" +
139 " PRIMARY KEY (`timrCodigo`),\n" +
140 " INDEX `fk_TimesRes_Responsaveis1_idx` (`timrResponsaveisFK` ASC),\n" +
141 " INDEX `fk_TimesRes_Times1_idx` (`timrTimesFK` ASC),\n" +
142 " CONSTRAINT `fk_TimesRes_Responsaveis1`\n" +
143 " FOREIGN KEY (`timrResponsaveisFK`)\n" +
144 " REFERENCES `dbScoutTcc`.`Responsaveis` (`resCodigo`)\n" +
145 " ON DELETE NO ACTION\n" +
146 " ON UPDATE NO ACTION,\n" +
147 " CONSTRAINT `fk_TimesRes_Times1`\n" +
148 " FOREIGN KEY (`timrTimesFK`)\n" +
149 " REFERENCES `dbScoutTcc`.`Times` (`timCodigo`)\n" +
150 " ON DELETE NO ACTION\n" +
151 " ON UPDATE NO ACTION)\n" +
152 "ENGINE = InnoDB\n" +
153 "DEFAULT CHARACTER SET = utf8\n" +
154 "COMMENT = 'Nesta tabela está a relação entre as tabelas Responsáveis e Time. \\n';\n" +
155 "\n" +
156 "\n" +
157 "-- -----------------------------------------------------\n" +
158 "-- Table `dbScoutTcc`.`Partidas`\n" +
159 "-- -----------------------------------------------------\n" +
160 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`Partidas` (\n" +
161 " `parCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
162 " `parAcrescimos` TIME NULL,\n" +
163 " `parDuracao1Tempo` TIME NOT NULL,\n" +
164 " `parDuracao2Tempo` TIME NOT NULL,\n" +
165 " `parData` DATETIME NOT NULL,\n" +
166 " `parTimeCasaFK` INT NOT NULL,\n" +
167 " `parResultado` VARCHAR(30) NOT NULL,\n" +
168 " `parTimeVisitanteFK` INT NOT NULL,\n" +
169 " PRIMARY KEY (`parCodigo`),\n" +
170 " INDEX `fk_Partidas_Times1_idx` (`parTimeCasaFK` ASC),\n" +
171 " INDEX `fk_Partidas_Times2_idx` (`parTimeVisitanteFK` ASC),\n" +
172 " CONSTRAINT `fk_Partidas_Times1`\n" +
173 " FOREIGN KEY (`parTimeCasaFK`)\n" +
174 " REFERENCES `dbScoutTcc`.`Times` (`timCodigo`)\n" +
175 " ON DELETE NO ACTION\n" +
176 " ON UPDATE NO ACTION,\n" +
177 " CONSTRAINT `fk_Partidas_Times2`\n" +
178 " FOREIGN KEY (`parTimeVisitanteFK`)\n" +
179 " REFERENCES `dbScoutTcc`.`Times` (`timCodigo`)\n" +
180 " ON DELETE NO ACTION\n" +
181 " ON UPDATE NO ACTION)\n" +
182 "ENGINE = InnoDB\n" +
183 "DEFAULT CHARACTER SET = utf8\n" +
184 "COMMENT = 'Nesta tabela estão armazenadas as informações da Partida. ';\n" +
185 "\n" +
186 "\n" +
187 "-- -----------------------------------------------------\n" +
188 "-- Table `dbScoutTcc`.`Fundamentos`\n" +
189 "-- -----------------------------------------------------\n" +
190 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`Fundamentos` (\n" +
191 " `funCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
192 " `funTiroDeMeta` INT NULL,\n" +
193 " `funPontos` INT NULL,\n" +
194 " `funDesarmes` INT NULL,\n" +
195 " `funEscanteios` INT NULL,\n" +
196 " `funFaltascometidas` INT NULL,\n" +
197 " `funPassesErrados` INT NULL,\n" +
198 " `funPosseDeBola` INT NULL,\n" +
199 " `funPassesCertos` INT NULL,\n" +
200 " `funEntrou` TIME NULL,\n" +
201 " `funSaiu` TIME NULL,\n" +
202 " `funErrouGol` VARCHAR(45) NULL,\n" +
203 " `funCartaoAmarelo` VARCHAR(45) NULL,\n" +
204 " `funCartaoVermelho` VARCHAR(45) NULL,\n" +
205 " PRIMARY KEY (`funCodigo`))\n" +
206 "ENGINE = InnoDB\n" +
207 "DEFAULT CHARACTER SET = utf8\n" +
208 "COMMENT = 'Nesta tabela estão as informações necessárias para os Fudamentos.';\n" +
209 "\n" +
210 "\n" +
211 "-- -----------------------------------------------------\n" +
212 "-- Table `dbScoutTcc`.`JogPartiFundas`\n" +
213 "-- -----------------------------------------------------\n" +
214 "CREATE TABLE IF NOT EXISTS `dbScoutTcc`.`JogPartiFundas` (\n" +
215 " `jogpfCodigo` INT NOT NULL AUTO_INCREMENT,\n" +
216 " `jogpfFundamentosFK` INT NOT NULL,\n" +
217 " `jogpfJogadoresFK` INT NOT NULL,\n" +
218 " `jogpfPartidasFK` INT NOT NULL,\n" +
219 " `jogpfFundamento2FK` INT NULL,\n" +
220 " PRIMARY KEY (`jogpfCodigo`),\n" +
221 " INDEX `fk_JogPartiFundas_Fundamentos1_idx` (`jogpfFundamentosFK` ASC),\n" +
222 " INDEX `fk_JogPartiFundas_Jogadores1_idx` (`jogpfJogadoresFK` ASC),\n" +
223 " INDEX `fk_JogPartiFundas_Partidas1_idx` (`jogpfPartidasFK` ASC),\n" +
224 " INDEX `fk_JogPartiFundas_Fundamentos2_idx` (`jogpfFundamento2FK` ASC),\n" +
225 " CONSTRAINT `fk_JogPartiFundas_Fundamentos1`\n" +
226 " FOREIGN KEY (`jogpfFundamentosFK`)\n" +
227 " REFERENCES `dbScoutTcc`.`Fundamentos` (`funCodigo`)\n" +
228 " ON DELETE NO ACTION\n" +
229 " ON UPDATE NO ACTION,\n" +
230 " CONSTRAINT `fk_JogPartiFundas_Jogadores1`\n" +
231 " FOREIGN KEY (`jogpfJogadoresFK`)\n" +
232 " REFERENCES `dbScoutTcc`.`Jogadores` (`jogCodigo`)\n" +
233 " ON DELETE NO ACTION\n" +
234 " ON UPDATE NO ACTION,\n" +
235 " CONSTRAINT `fk_JogPartiFundas_Partidas1`\n" +
236 " FOREIGN KEY (`jogpfPartidasFK`)\n" +
237 " REFERENCES `dbScoutTcc`.`Partidas` (`parCodigo`)\n" +
238 " ON DELETE NO ACTION\n" +
239 " ON UPDATE NO ACTION,\n" +
240 " CONSTRAINT `fk_JogPartiFundas_Fundamentos2`\n" +
241 " FOREIGN KEY (`jogpfFundamento2FK`)\n" +
242 " REFERENCES `dbScoutTcc`.`Fundamentos` (`funCodigo`)\n" +
243 " ON DELETE NO ACTION\n" +
244 " ON UPDATE NO ACTION)\n" +
245 "ENGINE = InnoDB\n" +
246 "DEFAULT CHARACTER SET = utf8\n" +
247 "COMMENT = 'Nesta tabela está a relação entre as tabelas Jogadores, Fundamentos e Times. ';\n" +
248 "\n" +
249 "\n" +
250 "SET SQL_MODE=@OLD_SQL_MODE;\n" +
251 "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\n" +
252 "SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;\n";
253 stmt.executeUpdate(sql);
254 System.out.println("Database created successfully...");
255 } catch (SQLException se) {
256 //Handle errors for JDBC
257 se.printStackTrace();
258 } catch (Exception e) {
259 //Handle errors for Class.forName
260 e.printStackTrace();
261 } finally {
262 //finally block used to close resources
263 try {
264 if (stmt != null)
265 stmt.close();
266 } catch (SQLException se2) {
267 }// nothing we can do
268 try {
269 if (conn != null)
270 conn.close();
271 } catch (SQLException se) {
272 se.printStackTrace();
273 }//end finally try
274 }//end try
275 System.out.println("Goodbye!");
276 }