7Xa5ZRrR

1/*
2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
5 */
6package db;
7
8import com.mysql.jdbc.Connection;
9import com.mysql.jdbc.Statement;
10import java.sql.DriverManager;
11import java.sql.ResultSet;
12import java.sql.SQLException;
13import java.util.logging.Level;
14import java.util.logging.Logger;
15import javax.swing.UIManager;
16
17/**
18 *
19 * @author qasko
20 */
21public final class MyFrame extends javax.swing.JFrame {
22
23    String driver = "com.mysql.jdbc.Driver";
24    Connection con = null;
25    Statement stmt = null;
26    ResultSet rs = null;
27    String userName = "root";
28    String password = "";
29    String URL = "jdbc:mysql://localhost/mydb";
30    int pocetRiadkov = 0;
31    String createString = "CREATE TABLE IF NOT EXISTS zoznam " + "(ID INT UNSIGNED NOT NULL " + "AUTO_INCREMENT, " + "PRIMARY KEY (id), " + "meno VARCHAR (10), " + "priezvisko VARCHAR (10), " + "plat INTEGER)";
32    String demoData = "INSERT INTO Zoznam (Meno,Priezvisko,Plat) VALUES ('Jano','Mrkva',550)";
33    String avgPlat;
34    String pocet;
35
36    /**
37     * Creates new form MyFrame
38     */
39    public MyFrame() {
40        if (PripojenieKuMySQL()) {
41            initComponents();
42            if (NastavResultSet("Select * from zoznam")) {
43                System.out.println("ok");
44            }
45        } else {
46            System.out.println("error");
47        }
48    }
49
50    public Boolean PripojenieKuMySQL() {
51        try {
52            Class.forName(driver);
53            con = (Connection) DriverManager.getConnection(URL, userName, password);
54        } catch (ClassNotFoundException | SQLException e) {
55            System.out.println(e);
56            return false; // nastala chyba
57        }
58        return true; // vsetko je ok
59    }
60
61    public Boolean NastavResultSet(String Prikaz) {
62        try {
63            stmt = (Statement) con.createStatement();
64            rs = stmt.executeQuery(Prikaz);
65            // skoc na koniec, vrat poradove cislo riadku
66            rs.last();
67            pocetRiadkov = rs.getRow();
68            // nastav zase na zaciatok
69            rs.beforeFirst();
70            NacitajDalsiZaznam();
71        } catch (SQLException e) {
72            System.out.println(e);
73            return false; // vynimka => chyba
74        }
75        return true;
76    }
77
78    public void NacitajDalsiZaznam() {
79        try {
80            if (!rs.isLast()) { // ak nie je koniec
81                rs.next(); // posun sa
82                NaplnPolia(); // zobraz aktualny
83            }
84        } catch (SQLException e) {
85            System.out.println(e);
86        }
87    }
88
89    public void NacitajPredoslyZaznam() {
90        try {
91            if (!rs.isFirst()) { // ak nie je prvý
92                rs.previous(); // posun sa dozadu
93                NaplnPolia(); // zobraz aktualny
94            }
95        } catch (SQLException e) {
96            System.out.println(e);
97        }
98    }
99
100    public void NaplnPolia() throws SQLException {
101        // nacitanie z resultsetu do poli
102        idLabel.setText(rs.getString("ID"));
103        menoField.setText(rs.getString("meno"));
104        priezviskoField.setText(rs.getString("priezvisko"));
105        platField.setText(rs.getString("plat"));
106
107        // aktualny zaznam
108        jTextField.setText(Integer.toString(rs.getRow()));
109        // zapamataby pocet zaznamov
110        jLabel.setText("/" + pocetRiadkov);
111    }
112
113    public void VykonajDotaz(String prikazSQL) {
114        try {
115            stmt.executeUpdate(prikazSQL);
116        } catch (SQLException e) {
117            System.out.println(e);
118        }
119    }
120
121    /**
122     * This method is called from within the constructor to initialize the form.
123     * WARNING: Do NOT modify this code. The content of this method is always
124     * regenerated by the Form Editor.
125     */
126    @SuppressWarnings("unchecked")
127    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
128    private void initComponents() {
129
130        jTextField3 = new javax.swing.JTextField();
131        jLabel1 = new javax.swing.JLabel();
132        jLabel2 = new javax.swing.JLabel();
133        jLabel3 = new javax.swing.JLabel();
134        jLabel4 = new javax.swing.JLabel();
135        priezviskoField = new javax.swing.JTextField();
136        platField = new javax.swing.JTextField();
137        menoField = new javax.swing.JTextField();
138        idLabel = new javax.swing.JLabel();
139        jTextField = new javax.swing.JTextField();
140        jLabel = new javax.swing.JLabel();
141        jButton1 = new javax.swing.JButton();
142        jButton2 = new javax.swing.JButton();
143        avgPlatLabel = new javax.swing.JLabel();
144        jButton3 = new javax.swing.JButton();
145        jButton4 = new javax.swing.JButton();
146        jButton5 = new javax.swing.JButton();
147        platVacsiField = new javax.swing.JTextField();
148        jLabel5 = new javax.swing.JLabel();
149        jButton6 = new javax.swing.JButton();
150        pocetLabel = new javax.swing.JLabel();
151        jButton7 = new javax.swing.JButton();
152        jButton8 = new javax.swing.JButton();
153
154        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
155
156        jLabel1.setText("ID:");
157
158        jLabel2.setText("meno:");
159
160        jLabel3.setText("priezvisko:");
161
162        jLabel4.setText("plat:");
163
164        jLabel.setText("jLabel5");
165
166        jButton1.setText("UPDATE");
167        jButton1.addActionListener(new java.awt.event.ActionListener() {
168            public void actionPerformed(java.awt.event.ActionEvent evt) {
169                jButton1ActionPerformed(evt);
170            }
171        });
172
173        jButton2.setText("AVG");
174        jButton2.addActionListener(new java.awt.event.ActionListener() {
175            public void actionPerformed(java.awt.event.ActionEvent evt) {
176                jButton2ActionPerformed(evt);
177            }
178        });
179
180        avgPlatLabel.setText("avg");
181
182        jButton3.setText(">");
183        jButton3.addActionListener(new java.awt.event.ActionListener() {
184            public void actionPerformed(java.awt.event.ActionEvent evt) {
185                jButton3ActionPerformed(evt);
186            }
187        });
188
189        jButton4.setText("<");
190        jButton4.addActionListener(new java.awt.event.ActionListener() {
191            public void actionPerformed(java.awt.event.ActionEvent evt) {
192                jButton4ActionPerformed(evt);
193            }
194        });
195
196        jButton5.setText("NEW");
197        jButton5.addActionListener(new java.awt.event.ActionListener() {
198            public void actionPerformed(java.awt.event.ActionEvent evt) {
199                jButton5ActionPerformed(evt);
200            }
201        });
202
203        jLabel5.setText("Plat > ako");
204
205        jButton6.setText("OK");
206        jButton6.addActionListener(new java.awt.event.ActionListener() {
207            public void actionPerformed(java.awt.event.ActionEvent evt) {
208                jButton6ActionPerformed(evt);
209            }
210        });
211
212        pocetLabel.setText("pocet");
213
214        jButton7.setText("DELETE THIS");
215        jButton7.addActionListener(new java.awt.event.ActionListener() {
216            public void actionPerformed(java.awt.event.ActionEvent evt) {
217                jButton7ActionPerformed(evt);
218            }
219        });
220
221        jButton8.setText("HLADAT");
222        jButton8.addActionListener(new java.awt.event.ActionListener() {
223            public void actionPerformed(java.awt.event.ActionEvent evt) {
224                jButton8ActionPerformed(evt);
225            }
226        });
227
228        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
229        getContentPane().setLayout(layout);
230        layout.setHorizontalGroup(
231            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
232            .addGroup(layout.createSequentialGroup()
233                .addContainerGap()
234                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
235                    .addGroup(layout.createSequentialGroup()
236                        .addComponent(jButton4)
237                        .addGap(14, 14, 14)
238                        .addComponent(jTextField, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE)
239                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
240                        .addComponent(jLabel))
241                    .addGroup(layout.createSequentialGroup()
242                        .addComponent(jLabel1)
243                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
244                        .addComponent(idLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 100, javax.swing.GroupLayout.PREFERRED_SIZE))
245                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
246                        .addComponent(jButton3)
247                        .addGroup(layout.createSequentialGroup()
248                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
249                                .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
250                                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
251                                        .addComponent(jLabel3)
252                                        .addComponent(jLabel2))
253                                    .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED))
254                                .addGroup(layout.createSequentialGroup()
255                                    .addComponent(jLabel4)
256                                    .addGap(33, 33, 33)))
257                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
258                                .addComponent(menoField, javax.swing.GroupLayout.DEFAULT_SIZE, 127, Short.MAX_VALUE)
259                                .addComponent(priezviskoField)
260                                .addComponent(platField))))
261                    .addGroup(layout.createSequentialGroup()
262                        .addComponent(jButton1)
263                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
264                        .addComponent(jButton5, javax.swing.GroupLayout.PREFERRED_SIZE, 71, javax.swing.GroupLayout.PREFERRED_SIZE)
265                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
266                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
267                            .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 71, javax.swing.GroupLayout.PREFERRED_SIZE)
268                            .addComponent(avgPlatLabel))
269                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
270                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
271                            .addComponent(jButton8)
272                            .addComponent(jButton7)))
273                    .addGroup(layout.createSequentialGroup()
274                        .addComponent(jLabel5)
275                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
276                        .addComponent(platVacsiField, javax.swing.GroupLayout.PREFERRED_SIZE, 50, javax.swing.GroupLayout.PREFERRED_SIZE)
277                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
278                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
279                            .addComponent(pocetLabel)
280                            .addComponent(jButton6))))
281                .addContainerGap(34, Short.MAX_VALUE))
282        );
283        layout.setVerticalGroup(
284            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
285            .addGroup(layout.createSequentialGroup()
286                .addContainerGap()
287                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
288                    .addComponent(jTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
289                    .addComponent(jLabel)
290                    .addComponent(jButton3)
291                    .addComponent(jButton4))
292                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
293                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
294                    .addComponent(jLabel1)
295                    .addComponent(idLabel, javax.swing.GroupLayout.PREFERRED_SIZE, 14, javax.swing.GroupLayout.PREFERRED_SIZE))
296                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
297                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
298                    .addComponent(jLabel2)
299                    .addComponent(menoField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
300                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
301                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
302                    .addComponent(jLabel3)
303                    .addComponent(priezviskoField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
304                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
305                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
306                    .addComponent(jLabel4)
307                    .addComponent(platField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
308                .addGap(18, 18, 18)
309                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
310                    .addComponent(jButton1)
311                    .addComponent(jButton5)
312                    .addComponent(jButton2)
313                    .addComponent(jButton7))
314                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
315                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
316                    .addComponent(avgPlatLabel)
317                    .addComponent(jButton8))
318                .addGap(4, 4, 4)
319                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
320                    .addComponent(jLabel5)
321                    .addComponent(platVacsiField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
322                    .addComponent(jButton6))
323                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
324                .addComponent(pocetLabel)
325                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
326        );
327
328        pack();
329    }// </editor-fold>                        
330
331    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
332        String dotaz = "Update zoznam set "
333                + "meno ='" + menoField.getText() + "', "
334                + "priezvisko ='" + priezviskoField.getText() + "', "
335                + "plat ='" + platField.getText() + "' "
336                + "where ID = " + idLabel.getText();
337        VykonajDotaz(dotaz);
338        NastavResultSet("Select * from zoznam");
339        System.out.println("UPDATED");
340    }                                        
341
342    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
343        try {
344            rs = stmt.executeQuery("SELECT AVG(plat) as plat FROM zoznam");
345            rs.next();
346            avgPlat = rs.getString("plat");
347
348            avgPlatLabel.setText(avgPlat);
349            NastavResultSet("Select * from zoznam");
350        } catch (SQLException ex) {
351            Logger.getLogger(MyFrame.class.getName()).log(Level.SEVERE, null, ex);
352        }
353    }                                        
354
355    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                         
356        NacitajDalsiZaznam();
357    }                                        
358
359    private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {                                         
360        NacitajPredoslyZaznam();
361    }                                        
362
363    private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {                                         
364        String dotaz = "Insert into zoznam (meno,priezvisko,plat) values ("
365                + "'" + menoField.getText() + "', "
366                + "'" + priezviskoField.getText() + "', "
367                + "'" + platField.getText() + "')";
368        VykonajDotaz(dotaz);
369        NastavResultSet("Select * from Zoznam");
370        System.out.println("inserted");
371    }                                        
372
373    private void jButton6ActionPerformed(java.awt.event.ActionEvent evt) {                                         
374        try {
375            rs = stmt.executeQuery("SELECT COUNT(ID) as pocet FROM zoznam WHERE plat > " + platVacsiField.getText());
376            rs.next();
377            pocet = rs.getString("pocet");
378
379            pocetLabel.setText(pocet);
380            NastavResultSet("Select * from zoznam");
381        } catch (SQLException ex) {
382            Logger.getLogger(MyFrame.class.getName()).log(Level.SEVERE, null, ex);
383        }
384    }                                        
385
386    private void jButton7ActionPerformed(java.awt.event.ActionEvent evt) {                                         
387        String dotaz = "DELETE FROM zoznam WHERE id = " + idLabel.getText();
388        VykonajDotaz(dotaz);
389        NastavResultSet("Select * from Zoznam");
390        System.out.println("deleted");
391    }                                        
392
393    private void jButton8ActionPerformed(java.awt.event.ActionEvent evt) {                                         
394        try {
395            rs = stmt.executeQuery("SELECT * FROM zoznam WHERE meno = " + menoField.getText() + " OR priezvisko = " + priezviskoField.getText());
396            rs.next();
397            pocet = rs.getString("pocet");
398
399            pocetLabel.setText(pocet);
400            NastavResultSet("Select * from zoznam");
401        } catch (SQLException ex) {
402            Logger.getLogger(MyFrame.class.getName()).log(Level.SEVERE, null, ex);
403        }
404    }                                        
405
406    /**
407     * @param args the command line arguments
408     */
409    public static void main(String args[]) {
410        /* Set the Nimbus look and feel */
411        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
412        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
413         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
414         */
415        try {
416            UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
417        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) {
418            java.util.logging.Logger.getLogger(MyFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
419        }
420        //</editor-fold>
421
422        //</editor-fold>
423
424        /* Create and display the form */
425        java.awt.EventQueue.invokeLater(() -> {
426            new MyFrame().setVisible(true);
427        });
428    }
429
430    // Variables declaration - do not modify                     
431    private javax.swing.JLabel avgPlatLabel;
432    private javax.swing.JLabel idLabel;
433    private javax.swing.JButton jButton1;
434    private javax.swing.JButton jButton2;
435    private javax.swing.JButton jButton3;
436    private javax.swing.JButton jButton4;
437    private javax.swing.JButton jButton5;
438    private javax.swing.JButton jButton6;
439    private javax.swing.JButton jButton7;
440    private javax.swing.JButton jButton8;
441    private javax.swing.JLabel jLabel;
442    private javax.swing.JLabel jLabel1;
443    private javax.swing.JLabel jLabel2;
444    private javax.swing.JLabel jLabel3;
445    private javax.swing.JLabel jLabel4;
446    private javax.swing.JLabel jLabel5;
447    private javax.swing.JTextField jTextField;
448    private javax.swing.JTextField jTextField3;
449    private javax.swing.JTextField menoField;
450    private javax.swing.JTextField platField;
451    private javax.swing.JTextField platVacsiField;
452    private javax.swing.JLabel pocetLabel;
453    private javax.swing.JTextField priezviskoField;
454    // End of variables declaration                   
455}