· 9 years ago · Nov 30, 2016, 01:36 PM
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 databasefun2;
7
8import java.sql.Connection;
9import java.sql.ResultSet;
10import java.sql.SQLException;
11import java.sql.Statement;
12import java.util.ArrayList;
13import java.util.Collections;
14import java.util.logging.Level;
15import java.util.logging.Logger;
16import javax.swing.JOptionPane;
17import javax.swing.JTable;
18import javax.swing.table.DefaultTableModel;
19
20/**
21 *
22 * @author douglas
23 */
24public class DB_Statements {
25
26// declare the Statement
27 private static Statement stmt = null;
28// declare the ResultSet
29 private static ResultSet rs = null;
30 private DB_Connectio_nonSingleton db = new DB_Connectio_nonSingleton();
31 // declare a connection
32 private static Connection con = DB_Connectio_nonSingleton.connect();
33
34// DB_Connectio_nonSingleton myCon = new DB_Connectio_nonSingleton();
35 public void createDB() {
36// con = DB_Connectio_nonSingleton.connect(user, psswrd); //get the connection through DB_Connection
37 String query = "select * from person"; //create an SQL query
38 try {
39 stmt = con.createStatement(); //create a statement
40 rs = stmt.executeQuery(query);
41 } catch (SQLException ex) {
42 Logger.getLogger(DB_Statements.class.getName()).log(Level.SEVERE, null, ex);
43 }
44 }
45
46 public void queryPerson() {
47
48// con = DB_Connectio_nonSingleton.connect(); //get the connection through DB_Connection
49 String query = "select * from person"; //create an SQL query
50 try {
51 stmt = con.createStatement(); //create a statement
52 rs = stmt.executeQuery(query); //return(execute) the SQL query
53
54 System.out.println("\nname\t\taddress\t\ttelephone\n_________________________________________");
55
56// return next row in the table while true
57 while (rs.next()) {
58 String name = rs.getString("name"); //return names
59 String address = rs.getString("address"); //return addresses
60 String telefon = rs.getString("telefon"); //return phone #'s
61 System.out.println(name + "\t\t" + address + "\t" + telefon);
62 }
63 } catch (SQLException ex) {
64 ex.printStackTrace();
65 }
66 closeResultSet();
67// myCon.closeConnection(); //close connection
68
69 }
70
71 public void createOrderTable() {
72
73// DB_Connection.connect(); //get the connection through DB_Connection
74 String table = "order1";
75 String query = "create table if not exists " + table + " (O_Id serial, OrderNo integer);"; //create an SQL query
76 try {
77
78 stmt = con.createStatement(); //create a statement
79 stmt.executeUpdate(query); //execute the SQL query
80 System.out.println("\n--Table " + table + " has been created--");
81 } catch (SQLException ex) {
82 ex.printStackTrace();
83 }
84// DB_Connection.closeConnection();
85 }
86
87 public void fillTable(JTable table) {
88 try {
89 DB_Connectio_nonSingleton.connect();
90 String query = "select * from order1;";
91 stmt = con.createStatement();
92 rs = stmt.executeQuery(query);
93
94 ArrayList<Object[]> dataList = new ArrayList<>();
95 String[] columnNames = {"id", "order no"};
96
97 while (rs.next()) {
98 String O_Id = rs.getString("O_Id"); //return ordr id
99 String OrderNo = rs.getString("OrderNo"); //return order number
100 System.out.println("o_id -> " + O_Id + ", orderNo -> " + OrderNo);
101 dataList.add(new Object[]{O_Id, OrderNo});
102 }
103
104 Object[][] data = new Object[dataList.size()][2];
105 int i = 0;
106 for (Object[] r : dataList) {
107 data[i] = r;
108 i++;
109 }
110 table.setModel(new DefaultTableModel(data, columnNames));
111 table.setAutoCreateRowSorter(true);
112// con.close();
113
114 } catch (SQLException e) {
115 System.err.println("Smth went worng");
116 System.err.println(e.getMessage());
117 }
118
119 }
120
121 public void insertIntoOrderTable(String ordrNo) {
122
123 String table = "order1";
124 String sql = "insert into " + table + " (orderno) values(" + ordrNo + ");";
125 try {
126 DB_Connectio_nonSingleton.connect();
127 stmt = con.createStatement();
128 stmt.executeUpdate(sql);
129 JOptionPane.showMessageDialog(null, "Data inserted into " + table + " table");
130 System.out.println("\n--Data inserted into " + table + " table--");
131 } catch (SQLException ex) {
132 System.err.println(ex.getMessage());
133 }
134// DB_Connection.closeConnection();
135 }
136
137 public void queryOrder1(JTable table) {
138
139// con = DB_Connection.connect(); //get the connection through DB_Connection
140 String query = "select * from order1 order by o_id asc"; //create an SQL query
141 try {
142 DB_Connectio_nonSingleton.connect();
143 stmt = con.createStatement(); //create a statement
144 rs = stmt.executeQuery(query); //return(execute) the SQL query
145 System.out.println("\nO_Id\t\tOrderNo\n----------------------");
146
147 ArrayList<Object[]> dataList = new ArrayList<>();
148 String[] columnNames = {"id", "order no"};
149
150// return next row in the table while true
151 while (rs.next()) {
152 String O_Id = rs.getString("O_Id"); //return names
153 String OrderNo = rs.getString("OrderNo"); //return addresses
154 System.out.println(O_Id + "\t|\t" + OrderNo);
155 dataList.add(new Object[]{O_Id, OrderNo});
156 }
157
158 Object[][] data = new Object[dataList.size()][2];
159 int i = 0;
160 for (Object[] r : dataList) {
161 data[i] = r;
162 i++;
163 }
164 table.setModel(new DefaultTableModel(data, columnNames));
165// table.setAutoCreateRowSorter(true);
166// con.close();
167
168 } catch (SQLException ex) {
169 ex.printStackTrace();
170 }
171// closeResultSet();
172// DB_Connection.closeConnection(); //close connection
173
174 }
175
176 public void updateOrderNo(String newOrderNo, String orderID) {
177 String table = "order1";
178 String sql = "update order1 set orderno = " + newOrderNo + " where o_id = " + orderID + ";";
179
180 try {
181 DB_Connectio_nonSingleton.connect();
182 stmt = con.createStatement();
183 stmt.executeUpdate(sql);
184 JOptionPane.showMessageDialog(null, "Data updated in " + table + " table");
185 System.out.println("\n--Data updated in " + table + " table--");
186 } catch (SQLException ex) {
187 System.err.println(ex.getMessage());
188 }
189// DB_Connection.closeConnection();
190 }
191
192 public void deleteRow(String orderID) {
193
194 String table = "order1";
195 String sql = "delete from " + table + " where o_id = " + orderID + ";";
196
197 try {
198 DB_Connectio_nonSingleton.connect();
199 stmt = con.createStatement();
200 stmt.executeUpdate(sql);
201 JOptionPane.showMessageDialog(null, "Row deleted from " + table + " table");
202 System.out.println("\n--Row deleted from " + table + " table--");
203 } catch (SQLException ex) {
204 System.err.println(ex.getMessage());
205 }
206
207 }
208
209
210
211// close the ResultSet
212 public void closeResultSet() {
213
214 try {
215 if (rs != null) {
216 rs.close();
217 System.out.println("\nThe ResultSet is now closed");
218 }
219
220 } catch (SQLException ex) {
221 ex.printStackTrace();
222 }
223
224 }
225
226}