· 6 years ago · Dec 07, 2019, 02:42 PM
1package com.company;
2
3import java.io.IOException;
4import java.sql.Connection;
5import java.sql.PreparedStatement;
6import java.sql.SQLException;
7import java.util.Scanner;
8
9
10public class Main {
11 private static final Scanner SCANNER = new Scanner(System.in);
12 public static final String OP1 = "Get all names and phone numbers by char.";
13 public static final String OP2 = "Get contact info by specific name.";
14 public static final String OP3 = "Add contact.";
15 public static final String OP4 = "Remove contact.";
16 public static final String EXIT = "Exit";
17
18 //to create a connection to our Data Base
19 private static Connection myDataBase;
20 //will convert our SQL request to a statement that the DB will recognize
21 private static PreparedStatement statement;
22 //address of our mysql
23 final static String URL = "jdbc:mysql://localhost/";
24 //init time zone UTC
25 final static String HOLD_JDBC = "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
26 //name of the data base
27 final static String DB_NAME = "phone_book";
28 //name of user (in our case administrator)
29 final static String DB_USER = "root";
30 //administrator password
31 final static String DB_PASS = "";
32
33 public static void main(String[] args) throws IOException, SQLException {
34 Contact_SQL contact_sql = new Contact_SQL(myDataBase, statement, URL, DB_NAME, HOLD_JDBC, DB_USER, DB_PASS);
35 phoneBook_SQL(contact_sql);
36 }
37
38 static boolean phoneBook_SQL(Contact_SQL contact_sql) throws IOException, SQLException {
39 String[] options = {OP1, OP2, OP3, OP4, EXIT};
40 switch (Utils.menu(options)) {
41 case 1:
42 if (!(initSearchCharRequest(contact_sql)))
43 return phoneBook_SQL(contact_sql);
44 break;
45 case 2:
46 if (!(initSearchNameRequest(contact_sql)))
47 return phoneBook_SQL(contact_sql);
48 break;
49 case 3:
50 if (!(insertContactToTable(Utils.getNewContactInformation())))
51 return phoneBook_SQL(contact_sql);
52 break;
53 case 4:
54 initRemoveContactByName(contact_sql);
55 return phoneBook_SQL(contact_sql);
56 }
57 return false;
58 }
59
60 private static boolean initRemoveContactByName(Contact_SQL contact_sql) throws IOException, SQLException {
61 System.out.println("Type contact name to remove : ");
62 String name = SCANNER.nextLine();
63 contact_sql.removeContactByName(name);
64 return true;
65 }
66
67 private static boolean insertContactToTable(String[] info) throws SQLException {
68 Contact_SQL contactSql = new Contact_SQL(myDataBase, statement, URL, DB_NAME, HOLD_JDBC, DB_USER, DB_PASS, info[0], info[1], Integer.parseInt(info[2]), info[3], info[4]);
69 contactSql.saferInsert(contactSql);
70 return false;
71 }
72
73 private static boolean initSearchNameRequest(Contact_SQL contact_sql) throws IOException, SQLException {
74 System.out.print("Type Contact name : ");
75 String userName = SCANNER.nextLine();
76 if (userName.equals("back")) {
77 return false;
78 }
79 if (contact_sql.getByName(userName)) {
80 return true;
81 }
82 return initSearchNameRequest(contact_sql);
83 }
84
85 static boolean initSearchCharRequest(Contact_SQL contact_sql) throws SQLException, IOException {
86 System.out.print("Type your char : ");
87 String uChar = SCANNER.nextLine();
88 if (Utils.isChar(uChar)) {
89 contact_sql.getDataByChar(uChar);
90 return initSearchCharRequest(contact_sql);
91 } else if (uChar.equals("back")) {
92 return false;
93 } else
94 throw new IOException("Please NOTE : you chose to search by CHAR found : STRING");
95 }
96
97}
98
99=======================================================================================================================
100
101package com.company;
102
103import java.io.IOException;
104import java.sql.*;
105import java.util.Vector;
106
107public abstract class SQL_Handler {
108 //to create a connection to our Data Base
109 private Connection myDataBase;
110 //will convert our SQL request to a statement that the DB will recognize
111 private PreparedStatement statement;
112 //my localHost url
113 private String URL;
114 //init time zone UTC
115 private String HOLD_JDBC;
116 //name of the data base
117 private String DB_NAME;
118 //name of user (in our case administrator)
119 private String DB_USER;
120 //administrator password
121 private String DB_PASS;
122 private static final String SQL_CREATE_TABLE_COMMAND = "CREATE TABLE IF NOT EXISTS contacts_information " +
123 "(char_Id TEXT (16) NOT NULL, " +
124 "contact_name TEXT (16) NOT NULL, " +
125 "phone_number INT PRIMARY KEY AUTO_INCREMENT, " +
126 "city TEXT (16) NOT NULL, " +
127 "address TEXT (16) NOT NULL)";
128 public static final String SQL_INSERT_STATEMENT = "INSERT INTO contacts_information (char_Id,contact_name,phone_number,city,address) VALUES (?,?,?,?,?)";
129
130 public SQL_Handler(Connection myDataBase, PreparedStatement statement, String URL, String DB_NAME, String HOLD_JDBC, String DB_USER, String DB_PASS) throws SQLException {
131 this.myDataBase = myDataBase;
132 this.statement = statement;
133 this.URL = URL;
134 this.DB_NAME = DB_NAME;
135 this.HOLD_JDBC = HOLD_JDBC;
136 this.DB_USER = DB_USER;
137 this.DB_PASS = DB_PASS;
138 this.myDataBase = DriverManager.getConnection(URL + DB_NAME + HOLD_JDBC, DB_USER, DB_PASS);
139 initContactsTable();
140 }
141
142 void initContactsTable() throws SQLException {
143 statement = myDataBase.prepareStatement(SQL_CREATE_TABLE_COMMAND);
144 statement.execute();
145 }
146
147 boolean getDataByChar(String charId) throws SQLException {
148 String sql = "SELECT * FROM contacts_information WHERE char_ID='" + charId + "'";
149 ResultSet result = myDataBase.prepareStatement(sql).executeQuery();
150 Vector<Contact_SQL> contact_sqls = new Vector<>();
151 if (!result.next()) {
152 System.out.println("No results for that char");
153 return false;
154 } else {
155 for (result.first(); !result.isAfterLast(); result.next()) {
156 Contact_SQL contactSql = new Contact_SQL(this.myDataBase, this.statement, this.URL, this.DB_NAME, this.HOLD_JDBC, this.DB_USER, this.DB_PASS, result.getString("char_Id"),
157 result.getString("contact_name"), result.getInt("phone_number"), result.getString("city"), result.getNString("address"));
158 contact_sqls.add(contactSql);
159 }
160 for (Contact_SQL contact_sql : contact_sqls) {
161 System.out.println(contact_sql);
162 }
163 }
164 return true;
165 }
166
167 void saferInsert(Contact_SQL contact_sql) throws SQLException {
168 statement = myDataBase.prepareStatement(SQL_INSERT_STATEMENT);
169 statement.setString(1, contact_sql.getCharId());
170 statement.setString(2, contact_sql.getName());
171 statement.setInt(3, contact_sql.getPhone_number());
172 statement.setString(4, contact_sql.getCity());
173 statement.setString(5, contact_sql.getAddress());
174 statement.execute();
175 System.out.println("contact was add ! ");
176 }
177
178 boolean getByName(String name) throws IOException, SQLException {
179 String sql = "SELECT * FROM contacts_information WHERE contact_name='" + name + "'";
180 ResultSet result = myDataBase.prepareStatement(sql).executeQuery();
181 Vector<Contact_SQL> contact_sqls = new Vector<>();
182 if (!result.next()) {
183 System.out.println("No results for that name");
184 return false;
185 } else {
186 for (result.first(); !result.isAfterLast(); result.next()) {
187 Contact_SQL contactSql = new Contact_SQL(this.myDataBase, this.statement, this.URL, this.DB_NAME, this.HOLD_JDBC, this.DB_USER, this.DB_PASS, result.getString("char_Id"),
188 result.getString("contact_name"), result.getInt("phone_number"), result.getString("city"), result.getNString("address"));
189 contact_sqls.add(contactSql);
190 }
191 for (Contact_SQL contact_sql : contact_sqls) {
192 System.out.println(contact_sql);
193 }
194 return true;
195 }
196 }
197
198 void removeContactByName(String name) throws SQLException, IOException {
199 if (getByName(name)){
200 String sql = "DELETE FROM contacts_information WHERE contact_name='" +name + "'";
201 statement = myDataBase.prepareStatement(sql);
202 statement.execute();
203 System.out.println("^^ Contact was deleted ^^");
204 }
205 }
206}
207
208===========================================================================================================================
209
210package com.company;
211
212import java.sql.Connection;
213import java.sql.PreparedStatement;
214import java.sql.SQLException;
215
216public class Contact_SQL extends SQL_Handler {
217 private String charId;
218 private String name;
219 private int phone_number;
220 private String city;
221 private String address;
222
223 public Contact_SQL(Connection myDataBase, PreparedStatement statement, String URL, String DB_NAME, String HOLD_JDBC, String DB_USER, String DB_PASS) throws SQLException {
224 super(myDataBase,statement,URL,DB_NAME,HOLD_JDBC,DB_USER,DB_PASS);
225 }
226 public Contact_SQL(Connection myDataBase, PreparedStatement statement, String URL, String DB_NAME, String HOLD_JDBC, String DB_USER, String DB_PASS, String charId, String name, int phone_number, String city, String address) throws SQLException {
227 super(myDataBase, statement, URL, DB_NAME, HOLD_JDBC, DB_USER, DB_PASS);
228 this.charId = charId;
229 this.name = name;
230 this.phone_number = phone_number;
231 this.city = city;
232 this.address = address;
233 }
234
235 @Override
236 public String toString() {
237 return "Contact_SQL{" + "charId='" + charId + '\'' + ", name='" + name + '\'' + ", phone_number=" + phone_number + ", city='" + city + '\'' + ", address='" + address + '\'' + '}';
238 }
239
240 public String getCharId() {
241 return charId;
242 }
243
244 public String getName() {
245 return name;
246 }
247
248 public int getPhone_number() {
249 return phone_number;
250 }
251
252 public String getCity() {
253 return city;
254 }
255
256 public String getAddress() {
257 return address;
258 }
259
260}
261
262===========================================================================================================================
263
264package com.company;
265
266import java.io.IOException;
267import java.util.Scanner;
268
269public class Utils {
270 static final Scanner SCANNER = new Scanner(System.in);
271 static final int CHAR_ZERO_VALUE = 48;
272 static final int CHAR_NINE_VALUE = 57;
273
274 public static int menu(String[] options) throws IOException {
275 System.out.println("* MENU *");
276 for (int i = 0; i < options.length; i++) {
277 System.out.println((i + 1) + ". " + options[i]);
278 }
279 System.out.print("Type your choice : ");
280 String choice = SCANNER.nextLine();
281 if (!isNumber(choice.charAt(0)))
282 throw new IOException("Must type only digits between " + 1 + " AND " + options.length);
283 if (Integer.parseInt(choice) < 1 || Integer.parseInt(choice) > options.length) {
284 throw new IOException("You choose Index OUT OF BOUND");
285 } else
286 return Integer.parseInt(choice);
287 }
288
289 static boolean isNumber(char userChar) {
290 return userChar >= CHAR_ZERO_VALUE && userChar <= CHAR_NINE_VALUE;
291 }
292 static boolean isChar(String s){
293 return s.length()==1;
294 }
295 static String[] getNewContactInformation() {
296 String[] result = new String[5];
297 System.out.print("Type name : ");
298 result[1] = SCANNER.nextLine();
299 result[0] = String.valueOf(result[1].charAt(0));
300 System.out.print("Type phone number : ");
301 result[2] = SCANNER.nextLine();
302 System.out.print("Type city : ");
303 result[3] = SCANNER.nextLine();
304 System.out.print("Type address : ");
305 result[4] = SCANNER.nextLine();
306 return result;
307 }
308}