Pbs1egRA

1import java.sql.ResultSet;
2import java.sql.SQLException;
3import java.sql.Statement;
4import java.sql.Connection;
5
6public class DB_Statements {
7//    Declare a Statement
8    private static Statement stmt = null;
9
10//    Declare & create a connection
11    private static Connection con = DB_Connector.connect();
12
13//    Declare a result set
14    private static ResultSet rs = null;
15
16//    Method to create a new Database
17    public void createDB() {
18
19//    SQL statement
20        String query = "create database ThisDatabase";
21        try {
22
23//    create statement
24            stmt = con.createStatement();
25
26//    execute statement
27            stmt.executeUpdate(query);
28            System.out.println("\n---Database was created---");
29        }
30
31//    handle sql exceptions
32        catch (SQLException ex) {
33            System.out.println("\n---Statement did not execute---");
34            ex.printStackTrace();
35        }
36    }
37
38//    method to use a database
39    public void useDB() {
40
41//    SQL statement
42    String query = "use ThisDatabase";
43    try {
44
45//    create statement
46        stmt = con.createStatement();
47
48//    execute statement
49        stmt.executeUpdate(query);
50        System.out.println("\n---Using ThisDatabase---");
51    }
52
53//    handle sql exceptions
54        catch(SQLException ex) {
55            System.out.println("\n---Statement did not execute---");
56            ex.printStackTrace();
57        }
58    }
59
60//    method to create a table
61    public void createTable () {
62
63//    SQL statement
64        String query = "create table if not exists ThisTable(" +
65            "id int NOT NULL AUTO_INCREMENT," +
66            "name varchar(28)," +
67            "address varchar(28), " +
68            "PRIMARY KEY(id)" +
69            ")";
70        try {
71
72//    create statement
73            stmt = con.createStatement();
74
75//    execute statement
76            stmt.executeUpdate(query);
77            System.out.println("\n---Table was created---");
78        }
79//    handle sql exceptions
80        catch (SQLException ex) {
81            System.out.println("\n---Statement did not execute---");
82            ex.printStackTrace();
83        }
84    }
85
86//    method to insert data
87    public void insertData () {
88
89//    SQL statement
90        String query = "INSERT INTO ThisTable " +
91                "(name, address) " +
92                "VALUES ('Douglas', 'Home'), " +
93                "('John', 'At Home'), " +
94                "('Bobby', 'His Home')";
95        try {
96
97//    create statement
98            stmt = con.createStatement();
99
100//    execute statement
101            stmt.executeUpdate(query);
102            System.out.println("\n---Data inserted---");
103        }
104
105//    handle sql exceptions
106        catch (SQLException ex) {
107            System.out.println("\n---Statement did not execute---");
108            ex.printStackTrace();
109        }
110    }
111
112//    method to read data from table
113    public void queryThisTable () {
114
115//    SQL statement
116        String query = "SELECT * FROM ThisTable";
117
118        try {
119
120//    create statement
121            stmt = con.createStatement();
122
123//    execute statement
124            rs = stmt.executeQuery(query);
125            System.out.println("\nid\t\tname\t\taddress\n_________________________________");
126
127//    return next_row in the table while true
128            while (rs.next()) {
129                int id = rs.getInt(1);
130                String name = rs.getString("name");
131                String address = rs.getString("address");
132                System.out.println(id + "\t\t" + name + "\t\t" + address);
133            }
134            System.out.println("\n---Select query was executed---");
135        }
136
137//    handle sql exceptions
138        catch (SQLException ex) {
139            System.out.println("\n---Statement did not execute---");
140            ex.printStackTrace();
141        }
142    }
143
144//    method to check for user credentials
145    public boolean checkLogin(String username, String password) {
146        boolean check = false;
147
148//    SQL statement
149        String query = "select * from ThisDatabase.users where username = '" + username + "' and password = '" + password + "'";
150
151        try {
152//    create statement
153            stmt = con.createStatement();
154
155//    create result set
156            rs = stmt.executeQuery(query);
157
158//    return next row in the table while true
159            while (rs.next()) {
160                check = true;
161                System.out.println("\n---YooHoo!!!---");
162            }
163            if (check == false) {
164                System.out.println("\n---No good---");
165            }
166        }
167//    handle sql exceptions
168        catch (Exception e) {
169            e.printStackTrace();
170        }
171        return check;
172
173    }
174}