· 4 years ago · May 11, 2021, 02:34 PM
1package telephoneBook.backEnd;
2
3import javafx.scene.control.Alert;
4
5import java.io.*;
6import java.sql.*;
7import java.util.List;
8
9public class Database {
10
11 private static final String driverName = "org.apache.derby.jdbc.EmbeddedDriver";
12 private static final String dbName = "jdbcContactsDB";
13 private static final String connectionURL = "jdbc:derby:" + dbName + ";create=true";
14
15 public void initializeDatabase() {
16 Connection connection;
17 try {
18 connection = DriverManager.getConnection(connectionURL);
19 if (!checkTableExisting(connection)) {
20 createDatabase(connection);
21 }
22 if (!connection.isClosed())
23 connection.close();
24 } catch (SQLException e) {
25 System.out.println(e.getMessage());
26 }
27 }
28
29 private void createDatabase(Connection connection) throws SQLException {
30
31 String createString = "CREATE TABLE CONTACTS"
32 + "(PERSON_ID INTEGER NOT NULL PRIMARY KEY"
33 + " GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
34 + " LASTNAME VARCHAR(32) NOT NULL,"
35 + " FIRSTNAME VARCHAR(32) NOT NULL,"
36 + " PATRONYMIC VARCHAR(32),"
37 + " COUNTRY_TITLE VARCHAR(64),"
38 + " TELEPHONE_NUMBER VARCHAR(20),"
39 + " HOME_TELEPHONE_NUMBER VARCHAR(20),"
40 + " ADDRESS VARCHAR(100),"
41 + " OFFICE VARCHAR(32),"
42 + " BIRTHDAY VARCHAR(10),"
43 + " COMMENT VARCHAR(100))";
44 Statement s = connection.createStatement();
45 s.execute(createString);
46 s.close();
47 connection.close();
48 }
49
50 public static boolean checkTableExisting(Connection conTst) throws SQLException {
51 try {
52 Statement s = conTst.createStatement();
53 s.execute("update CONTACTS set LASTNAME = 'Surname' where 1=3");
54 } catch (SQLException ex) {
55 String theError = (ex).getSQLState();
56 // If table exists will get - WARNING 02000: No row was found
57 if (theError.equals("42X05")) // Table does not exist
58 {
59 return false;
60 } else if (theError.equals("42X14") || theError.equals("42821")) {
61 System.out.println("Incorrect table definition. Drop table CONTACTS and rerun this program");
62 throw ex;
63 } else {
64 System.out.println("Unhandled SQLException");
65 throw ex;
66 }
67 }
68 return true;
69 }
70
71 public void addNewContactToDatabase(Contact newContact) {
72 Connection connection;
73 Statement s;
74 try {
75 connection = DriverManager.getConnection(connectionURL);
76 s = connection.createStatement();
77 /*s.execute("insert into CONTACTS(NEW_CONTACT) values (" +
78 "1, newContact.getLastName(), newContact.getFirstName(), " +
79 "newContact.getPatronymic(), newContact.getCountryTitle(), " +
80 "newContact.getTelephoneNumber(), newContact.getHomeTelephoneNumber(), " +
81 "newContact.getAddress(), newContact.getOffice()," +
82 "newContact.getBirthday(), newContact.getComment())");
83 */
84 s.execute("INSERT INTO CONTACTS(NEW_CONTACT) " +
85 "VALUES('" + 1 + "','" + newContact.getLastName() + "','" +
86 newContact.getFirstName() + "','" + newContact.getPatronymic() +
87 "','" + newContact.getCountryTitle() + "','" + newContact.getTelephoneNumber() +
88 "','" + newContact.getHomeTelephoneNumber() + "','" + newContact.getAddress() +
89 "','" + newContact.getOffice() + "','" + newContact.getBirthday() + "','" +
90 newContact.getComment() + "')");
91
92 /*s.execute("INSERT INTO CONTACTS(PERSON_ID, LASTNAME, FIRSTNAME, PATRONYMIC, COUNTRY_TITLE, TELEPHONE_NUMBER," +
93 "HOME_TELEPHONE_NUMBER, ADDRESS, OFFICE, BIRTHDAY, COMMENT) " +
94 "VALUES('" + 1 + "','" + newContact.getLastName() + "','" +
95 newContact.getFirstName() + "','" + newContact.getPatronymic() +
96 "','" + newContact.getCountryTitle() + "','" + newContact.getTelephoneNumber() +
97 "','" + newContact.getHomeTelephoneNumber() + "','" + newContact.getAddress() +
98 "','" + newContact.getOffice() + "','" + newContact.getBirthday() + "','" +
99 newContact.getComment() + "')");*/
100 /*
101 s.execute(String.format("insert into CONTACTS(NEW_CONTACT) values (%d, %s, %s, %s," +
102 " %s, %s, %s, %s, %s, %s, %s)", 1, newContact.getLastName(), newContact.getFirstName(),
103 newContact.getPatronymic(), newContact.getCountryTitle(), newContact.getTelephoneNumber(),
104 newContact.getHomeTelephoneNumber(), newContact.getAddress(), newContact.getOffice(),
105 newContact.getBirthday(), newContact.getComment()));*/
106
107 s.close();
108 connection.close();
109 System.out.println("Closed connection");
110
111 } catch (SQLException e) {
112 System.out.println("Can't get connection. Incorrect URL");
113 e.printStackTrace();
114 }
115 }
116
117 public void Load() {
118
119 Connection connection;
120 Statement s;
121 try {
122 connection = DriverManager.getConnection(connectionURL);
123 s = connection.createStatement();
124 ResultSet finalContacts;
125
126 // Select all records in the WISH_LIST table
127 finalContacts = s.executeQuery("select ENTRY_DATE, WISH_ITEM from CONTACTS");
128 while (finalContacts.next()) {
129 System.out.println("On " + finalContacts.getTimestamp(1) + " I wished for " + finalContacts.getString(2));
130 }
131 finalContacts.close();
132 } catch (SQLException e) {
133 System.out.println("Can't get connection. Incorrect URL");
134 e.printStackTrace();
135 }
136 }
137}