· 10 years ago · Jun 24, 2016, 10:42 AM
1package com.mycompany.lab_2_magazyn;
2
3/*
4 * To change this license header, choose License Headers in Project Properties.
5 * To change this template file, choose Tools | Templates
6 * and open the template in the editor.
7 */
8
9import java.awt.BorderLayout;
10import java.awt.HeadlessException;
11import java.sql.Connection;
12import java.sql.DatabaseMetaData;
13import java.sql.DriverManager;
14import java.sql.PreparedStatement;
15import java.sql.ResultSet;
16import java.sql.ResultSetMetaData;
17import java.sql.SQLException;
18import java.sql.Statement;
19import java.util.Vector;
20import java.util.logging.Level;
21import java.util.logging.Logger;
22import javax.swing.JFrame;
23import javax.swing.JOptionPane;
24import javax.swing.JPanel;
25import javax.swing.JScrollPane;
26import javax.swing.JTable;
27import javax.swing.table.DefaultTableModel;
28
29/**
30 *
31 * @author Michu
32 */
33public class Baza {
34
35 private static Connection con = null;
36
37 public Baza() throws SQLException {
38 try {
39 // Laczenie z baza
40 Class.forName("oracle.jdbc.OracleDriver");
41 con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hr", "hr");
42 } catch (ClassNotFoundException e) {
43 JOptionPane.showMessageDialog(null, e);
44 }
45 }
46
47
48
49
50 public boolean Sprawdz_tabele() throws SQLException {
51 DatabaseMetaData dbm = con.getMetaData();
52 Statement stmt = con.createStatement();
53 ResultSet rs = dbm.getTables(null, null, "TEST", null);
54 if (rs.next()) {
55 System.out.println("Table exists");
56 con.close();
57 return true;
58 } else {
59 System.out.println("Table does not exist");
60 con.close();
61 return false;
62 }
63 }
64
65 public void Tworz_tabele() throws SQLException {
66 try {
67 boolean sprawdz = Sprawdz_tabele();
68 if (sprawdz == true) {
69 System.out.println("tabela istnieje");
70 } else {
71 PreparedStatement tworz = con.prepareStatement("CREATE TABLE TEST (NR_MAG INTEGER,"
72 + "NRKARTY CHAR(50),"
73 + "NR_ODPADU INTEGER,"
74 + "NR_KLIENTA INTEGER,"
75 + "FIRMA INTEGER,"
76 + "JEDN CHAR(10),"
77 + "MASA FLOAT,"
78 + "DATAD CHAR(30),"
79 + "PRIMARY KEY (NR_MAG))");
80
81 tworz.executeUpdate();
82 con.close();
83 }
84 } catch (HeadlessException e) {
85 JOptionPane.showMessageDialog(null, e);
86 }
87 }
88
89 public void Wstaw_dane_do_tabeli(int NR_MAG,String NRKARTY,int NR_ODPADU,int NR_KLIENTA,int FIRMA,String JEDN,double MASA,String DATAD) throws SQLException {
90
91 try {
92 PreparedStatement wstaw = con.prepareStatement("INSERT INTO test (NR_MAG, NRKARTY, NR_ODPADU, NR_KLIENTA, FIRMA, JEDN, MASA, DATAD) VALUES (?,?,?,?,?,?,?,?)");
93 wstaw.setInt(1, NR_MAG);
94 wstaw.setString(2, NRKARTY);
95 wstaw.setInt(3, NR_ODPADU); // typy jak w create
96 wstaw.setInt(4, NR_KLIENTA);
97 wstaw.setInt(5, FIRMA);
98 wstaw.setString(6, JEDN);
99 wstaw.setDouble(7, MASA);
100 wstaw.setString(8, DATAD);
101
102
103 wstaw.executeUpdate();
104 con.close();
105 } catch (HeadlessException e) {
106 JOptionPane.showMessageDialog(null, e);
107 }
108 }
109
110 public void Odczytaj_z_tabeli(String tytul) throws SQLException {
111 try (Statement stmt = con.createStatement();
112 ResultSet rs = stmt.executeQuery("SELECT * FROM test")) {
113
114 ResultSetMetaData rsmt = rs.getMetaData();
115 int c = rsmt.getColumnCount();
116 Vector column = new Vector(c);
117 for (int i = 1; i <= c; i++) {
118 column.add(rsmt.getColumnName(i));
119 }
120 Vector data = new Vector();
121 Vector row = new Vector();
122 while (rs.next()) {
123 row = new Vector(c);
124 for (int i = 1; i <= c; i++) {
125 row.add(rs.getString(i));
126 }
127 data.add(row);
128 }
129
130 JTable table = new JTable(data, column);
131 JFrame frame = new JFrame(tytul);
132 frame.setSize(500, 200);
133 frame.setLocationRelativeTo(null);
134 //frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
135 JPanel panel = new JPanel();
136 JTable table1 = new JTable(data, column);
137 JScrollPane jsp = new JScrollPane(table1);
138 panel.setLayout(new BorderLayout());
139 panel.add(jsp, BorderLayout.CENTER);
140 frame.setContentPane(panel);
141 frame.setVisible(true);
142 con.close();
143 }
144 }
145
146 public void Usun_dane_z_tabeli(int NR_MAG) throws SQLException {
147 try {
148 PreparedStatement wstaw = con.prepareStatement("DELETE FROM test WHERE NR_MAG = ?");
149 wstaw.setInt(1, NR_MAG);
150
151 wstaw.executeUpdate();
152 con.close();
153 } catch (HeadlessException e) {
154 JOptionPane.showMessageDialog(null, e);
155 }
156 }
157
158 public void Aktualizuj_dane_z_tabeli(int NR_MAG,String NRKARTY,int NR_ODPADU,int NR_KLIENTA,int FIRMA,String JEDN,double MASA,String DATAD) throws SQLException {
159 con.setAutoCommit(false);//commit trasaction manually
160 try {
161 PreparedStatement wstaw = con.prepareStatement("UPDATE test SET NRKARTY = ?, NR_ODPADU = ?, NR_KLIENTA = ?, FIRMA = ?, JEDN = ?, MASA = ?, DATAD = ? WHERE NR_MAG = ?"); ;
162
163 wstaw.setString(1, NRKARTY);
164 wstaw.setInt(2, NR_ODPADU); // typy jak w create
165 wstaw.setInt(3, NR_KLIENTA);
166 wstaw.setInt(4, FIRMA);
167 wstaw.setString(5, JEDN);
168 wstaw.setDouble(6, MASA);
169 wstaw.setString(7, DATAD);
170 wstaw.setInt(8, NR_MAG);
171 wstaw.addBatch();
172 wstaw.executeBatch();
173 con.commit();
174 con.close();
175
176 //wstaw.executeUpdate();
177 } catch (HeadlessException e) {
178 JOptionPane.showMessageDialog(null, e);
179 }
180 }
181}