DB_APP/Model.java at master · maxprihodko8/DB_APP · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
import java.sql.*;

public class Model {
    static final String jDriver = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/buy";
    static final String dbUser = "root";
    static final String dbPass = "";
    Connection dbConnection;
    Statement statement;
    String [][] tableData;


     private String  s1 = "CREATE TABLE IF NOT EXISTS Buyers (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY," +
            "firstname VARCHAR(30) NOT NULL," +
            "lastname VARCHAR(30) NOT NULL," +
            "adress VARCHAR(30) NOT NULL," +
            "credit_card_number VARCHAR(30) NOT NULL," +
            "bank_card_number VARCHAR(30) NOT NULL" +
            ");";

    private String s2 = "INSERT INTO Buyers VALUES " +
            "(1,'Vasilis','Ivanov','Sumy','3242','423252')," +
            "(2,'Igor','Petrov','Kiev','3453','342342')," +
            "(3,'Vladislav','Shepotin','Sumy','3242','423252')," +
            "(4,'Kolya','Ivanov','Sumy','3242','423252')," +
            "(5,'Emil','Babaev','Sumy','3242','423252')," +
            "(6,'Sasha','Sergeev','Sumy','3242','423252')" +
            ";";

    Model(){
        dbConnection = getDBConnection();
        try {
            statement = dbConnection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection getDBConnection() {
        try {
            Class.forName(jDriver);
            dbConnection = DriverManager.getConnection(DB_URL, dbUser, dbPass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dbConnection;
    }

    public void makeFirstTable(){
        try {

            statement.execute(s1);
            //statement.execute(s2);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public String[][] getNamesSurnamesSorted(){
        String res = "";

        try{
            statement = dbConnection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * from Buyers order by firstname;");
            rs.last();
            int size = rs.getRow();
            rs.beforeFirst();
            tableData = new String[size][6];
            int i=0,j=0;
            while(rs.next()){
                tableData[i][j] = rs.getString("id"); j++;
                tableData[i][j] = rs.getString("firstname"); j++;
                tableData[i][j] = rs.getString("lastname"); j++;
                tableData[i][j] = rs.getString("adress"); j++;
                tableData[i][j] = rs.getString("credit_card_number"); j++;
                tableData[i][j] = rs.getString("bank_card_number"); j++;
                i++; j=0;
            }

        } catch (SQLException e){
            e.printStackTrace();}
        return tableData;
    }

    public String[][] getNamesSurnamesCreditInterval(int range1, int range2){
        if(range1 == (int)range1 && range2 == (int)range2)
        try{
            dbConnection = getDBConnection();
            statement = dbConnection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * from Buyers WHERE credit_card_number BETWEEN " + range1 + " AND " + range2 + ";");
            rs.last();
            int size = rs.getRow();
            rs.beforeFirst();
            tableData = new String[size][6];
            int i=0,j=0;
            while(rs.next()){
                tableData[i][j] = rs.getString("id"); j++;
                tableData[i][j] = rs.getString("firstname"); j++;
                tableData[i][j] = rs.getString("lastname"); j++;
                tableData[i][j] = rs.getString("adress"); j++;
                tableData[i][j] = rs.getString("credit_card_number"); j++;
                tableData[i][j] = rs.getString("bank_card_number"); j++;
                i++; j=0;
            }

        } catch (SQLException e){
            e.printStackTrace();}
        return tableData;
    }

    public String[][] getSelectAll() {
            try {
                dbConnection = getDBConnection();
                statement = dbConnection.createStatement();
                ResultSet rs = statement.executeQuery("SELECT * FROM Buyers;");
                rs.last();
                int size = rs.getRow();
                rs.beforeFirst();
                tableData = new String[size][6];
                int i=0,j=0;
                while(rs.next()){
                    tableData[i][j] = rs.getString("id"); j++;
                    tableData[i][j] = rs.getString("firstname"); j++;
                    tableData[i][j] = rs.getString("lastname"); j++;
                    tableData[i][j] = rs.getString("adress"); j++;
                    tableData[i][j] = rs.getString("credit_card_number"); j++;
                    tableData[i][j] = rs.getString("bank_card_number"); j++;
                    i++; j=0;
                }
            } catch (SQLException e) {
            }
        return tableData;
    }

    public void queryMake(String s) {
        if (s != null)
            try {
                statement.execute(s);
            } catch (SQLException e) {
                e.printStackTrace();}
    }


    void closeConn() {
        try {
            dbConnection = getDBConnection();
            dbConnection.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}