· 6 years ago · Apr 10, 2019, 12:30 AM
1import java.sql.*;
2import java.util.ArrayList;
3import java.util.Scanner;
4
5public class Main {
6
7 private static Scanner sc;
8
9 public static Person selectPerson(Connection conn, String firstName, String lastName) {
10 String sqlGetInfo = "SELECT firstName, lastName, age, ssn, creditCard FROM records";
11
12 try {
13 Statement stmt = conn.createStatement();
14 ResultSet rs = stmt.executeQuery(sqlGetInfo);
15
16 // loop through the result set
17 while (rs.next()) {
18 if (rs.getString("firstName").equals(firstName) && rs.getString("lastName").equals(lastName)) {
19 Person person = new Person(rs.getString("firstName"), rs.getString("lastName"), rs.getInt("age"), rs.getLong("ssn"), rs.getLong("creditCard"));
20 return person;
21 }
22 }
23 } catch (SQLException e) {
24 System.out.println(e.getMessage());
25 }
26 return null;
27 }
28
29 public static void printSpecific(Connection conn, String firstName, String lastName) {
30 String sqlGetInfo = "SELECT firstName, lastName, age, ssn, creditCard FROM records";
31
32 try {
33 Statement stmt = conn.createStatement();
34 ResultSet rs = stmt.executeQuery(sqlGetInfo);
35
36 // loop through the result set
37 while (rs.next()) {
38 if (rs.getString("firstName").equals(firstName) && rs.getString("lastName").equals(lastName)) {
39 System.out.println(rs.getString("firstName") + "\t\t" +
40 rs.getString("lastName") + "\t\t" +
41 rs.getInt("age") + "\t\t" +
42 rs.getLong("ssn") + "\t\t" +
43 rs.getLong("creditCard"));
44 }
45 }
46 } catch (SQLException e) {
47 System.out.println(e.getMessage());
48 }
49 }
50
51 public static void createNewDatabase(String fileName) {
52 String url = "jdbc:sqlite:C:/Users/The Clone Trooper/Dropbox/School/Java 2/" + fileName;
53
54 try(Connection conn = DriverManager.getConnection(url)){
55 if(conn != null) {
56 DatabaseMetaData meta = conn.getMetaData();
57 System.out.println("The Driver name is " + meta.getDriverName());
58 System.out.println("A new database has been created called: " + fileName);
59 }
60 } catch(SQLException e){
61 System.out.println(e.getMessage());
62 }
63
64 }
65
66 public static void printTable(Connection conn) {
67
68 String sqlGetInfo = "SELECT firstName, lastName, age, ssn, creditCard FROM records";
69 try {
70 Statement stmt = conn.createStatement();
71 ResultSet rs = stmt.executeQuery(sqlGetInfo);
72
73 while(rs.next()) {
74 System.out.println(rs.getString("firstName") + "\t\t" +
75 rs.getString("lastName") + "\t\t" +
76 rs.getInt("age") + "\t\t" +
77 rs.getLong("ssn") + "\t\t" +
78 rs.getLong("creditCard"));
79 }
80 } catch (SQLException e) {
81 e.printStackTrace();
82 }
83 }
84
85 public static ArrayList<Person> findAllPeople(Connection conn) {
86
87 ArrayList<Person> allPeople = new ArrayList<Person>();
88
89 String sqlGetInfo = "SELECT firstName, lastName, age, ssn, creditCard FROM records";
90 try {
91 Statement stmt = conn.createStatement();
92 ResultSet rs = stmt.executeQuery(sqlGetInfo);
93 Person person;
94 while(rs.next()) {
95 //System.out.println(rs.getString("firstName") + "\t\t" +
96 //rs.getString("lastName") + "\t\t" +
97 //rs.getInt("age") + "\t\t" +
98 //rs.getLong("ssn") + "\t\t" +
99 //rs.getLong("creditCard"));
100 person = new Person(rs.getString("firstName"), rs.getString("lastName"), rs.getInt("age"), rs.getLong("ssn"), rs.getLong("creditCard"));
101 allPeople.add(person);
102 }
103 } catch (SQLException e) {
104 e.printStackTrace();
105 }
106 return allPeople;
107 }
108
109 public static void insertPerson(Person person, Connection conn) {
110 try {
111 String sqlInsert = "INSERT INTO records(firstName,lastName,age,ssn,creditCard) VALUES(?,?,?,?,?)";
112 PreparedStatement pstmt = conn.prepareStatement(sqlInsert);
113 pstmt.setString(1, person.getFirst());
114 pstmt.setString(2, person.getLast());
115 pstmt.setInt(3, person.getAge());
116 pstmt.setLong(4, person.getSSN());
117 pstmt.setLong(5, person.getCreditCard());
118 pstmt.executeUpdate();
119 } catch (SQLException e) {
120 e.printStackTrace();
121 }
122 }
123
124 public static Connection connect(Connection conn) {
125 try {
126 String url = "jdbc:sqlite:C:/Users/The Clone Trooper/Dropbox/School/Java 2/myDatabase.db";
127 conn = DriverManager.getConnection(url);
128 System.out.println("Connection established!");
129
130 String sql = "CREATE TABLE IF NOT EXISTS records (\n" +
131 " firstName text NOT NULL,\n" +
132 " lastName text NOT NULL,\n" +
133 " age integer NOT NULL,\n" +
134 " ssn long NOT NULL,\n" +
135 " creditCard long NOT NULL\n" +
136 ");";
137 Statement stmt = conn.createStatement();
138 stmt.executeUpdate(sql);
139
140 //printTable(conn, stmt);
141
142 //stmt.close();
143 //conn.close();
144
145 } catch ( Exception e ) {
146 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
147 }
148 return conn;
149 }
150
151 public static void closeDatabase(Connection conn) {
152 try {
153 if (conn != null) {
154 conn.close();
155 System.out.println("Database closed!");
156 }
157 } catch (SQLException ex) {
158 System.out.println(ex.getMessage());
159 }
160 }
161
162 public static int deletePerson(Connection conn, String firstName, String lastName) {
163 String sqlGetInfo = "DELETE FROM records WHERE firstName = ? AND lastName = ?";
164
165 try {
166 PreparedStatement pstmt = conn.prepareStatement(sqlGetInfo);
167 pstmt.setString(1, firstName);
168 pstmt.setString(2, lastName);
169 pstmt.executeUpdate();
170 return 1;
171 } catch (SQLException e) {
172 return 0;
173 }
174 }
175
176 public static void main(String[] args) {
177 Connection conn = null;
178 boolean keepGoing = true;
179 sc = new Scanner(System.in);
180
181 //createNewDatabase("myDatabase.db");
182 conn = connect(conn);
183
184 System.out.println("---Insertion---");
185 do {
186 System.out.println("Enter the first name to add to the database: ");
187 String firstName = sc.next();
188 System.out.println("Enter the last name: ");
189 String lastName = sc.next();
190 System.out.println("Enter the age: ");
191 int age = sc.nextInt();
192 System.out.println("Enter the ssn: ");
193 long ssn = sc.nextInt();
194 System.out.println("Enter the credit card: ");
195 long creditCard = sc.nextInt();
196
197 Person person = new Person(firstName, lastName, age, ssn, creditCard);
198 insertPerson(person, conn);
199
200 System.out.println("Would you like to keep adding to the database? (true or false): ");
201 keepGoing = sc.nextBoolean();
202
203 } while(keepGoing);
204 ////////////////////////////////////////////////////////////////////////////
205 System.out.println("---Retrieval---");
206 printSpecific(conn, "Tomm", "Jerry");
207 ////////////////////////////////////////////////////////////////////////////
208 System.out.println("---slectPerson---");
209 Person person;
210 System.out.println("Enter the first name of the person that you would like to find: ");
211 String firstName = sc.next();
212 System.out.println("Enter the last name: ");
213 String lastName = sc.next();
214
215 if ((person = selectPerson(conn, firstName, lastName)) != null)
216 System.out.println("firstName: " + person.getFirst() + "\nlastName: " + person.getLast() + "\nAge: " + person.getAge() + "\nSSN: " + person.getSSN() + "\nCredit Card: " + person.getCreditCard());
217 else
218 System.out.println("Person not found!");
219 ////////////////////////////////////////////////////////////////////////////
220 System.out.println("---findAllPeople---");
221 ArrayList<Person> allPeople = findAllPeople(conn);
222 for(Person personBuffer: allPeople) {
223 System.out.println("firstName: " + personBuffer.getFirst() + "\t\t\tlastName: " + personBuffer.getLast() + "\t\t\tAge: " +
224 personBuffer.getAge() + "\t\t\tSSN: " + personBuffer.getSSN() + "\t\t\tCredit Card: " + personBuffer.getCreditCard());
225 System.out.println("---");
226 }
227 ////////////////////////////////////////////////////////////////////////////
228 System.out.println("---deletePerson---");
229 System.out.println("Enter the first name of the person that you would like to delete: ");
230 firstName = sc.next();
231 System.out.println("Enter the last name: ");
232 lastName = sc.next();
233
234 if(deletePerson(conn, firstName, lastName) == 0) {
235 System.out.println("Person not found!");
236 } else {
237 allPeople = findAllPeople(conn);
238 for(Person personBuffer: allPeople) {
239 System.out.println("firstName: " + personBuffer.getFirst() + "\t\t\tlastName: " + personBuffer.getLast() + "\t\t\tAge: " +
240 personBuffer.getAge() + "\t\t\tSSN: " + personBuffer.getSSN() + "\t\t\tCredit Card: " + personBuffer.getCreditCard());
241 System.out.println("---");
242 }
243 }
244
245 ////////////////////////////////////////////////////////////////////////////
246 System.out.println("---Closure---");
247 closeDatabase(conn);
248
249 }
250
251}