· 9 years ago · Nov 14, 2016, 10:54 PM
1import java.sql.*;
2import java.io.BufferedReader;
3import java.io.FileReader;
4import java.io.IOException;
5import java.sql.Connection;
6import java.sql.DriverManager;
7import java.sql.PreparedStatement;
8import java.sql.ResultSet;
9import java.sql.SQLException;
10import java.sql.Statement;
11import java.util.LinkedList;
12import java.util.List;
13import java.util.Scanner;
14
15public class wyniki_lotto
16{
17 public static void main( String args[] )
18 {
19 Connection c = null;
20 try {
21 Class.forName("org.sqlite.JDBC");
22 c = DriverManager.getConnection("jdbc:sqlite:test.db");
23 } catch ( Exception e ) {
24 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
25 System.exit(0);
26 }
27 System.out.println("Baza otwarta");
28 }
29
30public void dropTable() {
31 try {
32 stmt.execute("DROP TABLE IF EXISTS lotto;");
33 } catch (SQLException e) {
34 System.err.println("brak tabeli");
35 e.printStackTrace();
36 }
37 }
38public void createTable(){
39 {
40stmt = c.createStatement();
41 String sql = "CREATE TABLE wyniki_lotto " +
42 "(ID INT PRIMARY KEY NOT NULL," +
43 " Numer VARCHAR(4) NOT NULL, " +
44 " DZIEN VARCHAR(2) NOT NULL, " +
45 " MIESIAC VARCHAR(2) NOT NULL, " +
46 " ROK VARCHAR(4) NOT NULL, " +
47 " L1 VARCHAR(2) NOT NULL, " +
48 " L2 VARCHAR(2) NOT NULL, " +
49 " L3 VARCHAR(2) NOT NULL, " +
50 " L4 VARCHAR(2) NOT NULL, " +
51 " L5 VARCHAR(2) NOT NULL, " +
52 " L6 VARCHAR(2) NOT NULL, " +
53 )";
54 stmt.executeUpdate(sql);
55 stmt.close();
56 c.close();
57 } catch ( Exception e ) {
58 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
59 System.exit(0);
60 }
61 System.out.println("stworzono tabele");
62}
63
64/*public void readFile(String filePatch = "wyniki-lotto-sortowane.csv") throws IOException {
65 FileReader fileReader = new FileReader(filePath);
66 BufferedReader bufferedReader = new BufferedReader(fileReader);
67
68 String textLine = bufferedReader.readLine();
69 do {
70 System.out.println(textLine);
71
72 textLine = bufferedReader.readLine();
73 } while(textLine != null);
74
75 bufferedReader.close();
76}*/
77
78public void readFile () {
79 String fileName = "wyniki-lotto-sortowane.csv";
80 String line = null;
81 try {
82 FileReader fileReader =
83 new FileReader(fileName);
84 BufferedReader bufferedReader =
85 new BufferedReader(fileReader);
86
87 while((line = bufferedReader.readLine()) != null) {
88 String[] parts = line.split(";");
89 String Numer = parts[0];
90 String Dzien = parts[1];
91 String Miesiac = parts[2];
92 String Rok = parts[3];
93 String L1 = parts[4];
94 String L2 = parts[5];
95 String L3 = parts[6];
96 String L4 = parts[7];
97 String L5 = parts[8];
98 String L6 = parts[9];
99 String insertQuery = "INSERT INTO (NUMER, DZIEN, MIESIAC, ROK, L1, L2, L3, L4, L5, L6) VALUES ('"+Numer+"','"+Dzien+"','"+Miesiac+"','"Rok"',"+L1+","+L2+","+L3+","+L4+","+L5+","+L6+");";
100 try {
101 stmt.execute(insertQuery);
102 } catch (SQLException e) {
103 System.err.println("Blad przy dodawaniu wiersza");
104 e.printStackTrace();
105 }
106
107 }
108 bufferedReader.close();
109 }
110 catch(FileNotFoundException ex) {
111 System.out.println(
112 "Blad przy otwieraniu pliku '" +
113 fileName + "'");
114 }
115 catch(IOException ex) {
116 System.out.println(
117 "Blad przy wczytywaniu pliku '"
118 + fileName + "'");
119 }
120
121
122public void wyswietlwszystkielosowania(){
123 try {
124 ResultSet result = stmt.executeQuery("SELECT * FROM wyniki_lotto");
125 String NUMER, DZIEN, MIESIAC, ROK, L1, L2, L3, L4, L5, L6;
126
127 while(result.next()) {
128 Numer = result.getString("NUMER");
129 Dzien = result.getString("DZIEN");
130 Miesiac = result.getString("MIESIAC");
131 Rok = result.getString("ROK");
132 L1 = result.getInt("L1");
133 L2 = result.getInt("L2");
134 L3 = result.getInt("L3");
135 L4 = result.getInt("L4");
136 L5 = result.getInt("L5");
137 L6 = result.getInt("L6");
138 System.out.println("NUMER: "+NUMER+", DZIEN: "+DZIEN+", MIESIAC: "+MIESIAC+", ROK: "+ROK+", L1: "+L1+", L2: "+L2+", L3: "+L3+", L4: "+L4+", L5: "+L5+", L6: "+L6);
139 }
140 } catch (SQLException e) {
141 System.err.println("Blad przy wykonywaniu polecenia");
142 e.printStackTrace();
143 }
144}
145
146public void liczba(String liczba){
147 try {
148 ResultSet result = stmt.executeQuery("SELECT COUNT(id_wyniki_lotto) AS wynik FROM wyniki_lotto WHERE L1="+liczba+" OR L2="+liczba+" OR L3="+liczba+" OR L4="+liczba+" OR L5="+liczba+" OR L6="+liczba
149);
150 int wynik = result.getInt("wynik");
151 System.out.println("Liczba "+liczba+" pojawiła się: "+wynik+" razy");
152 } catch (SQLException e) {
153 System.err.println("Blad przy wykonywaniu polecenia");
154 e.printStackTrace();
155 }
156}
157
158public void L1 () {
159 try {
160 ResultSet result = stmt.executeQuery("SELECT L1 FROM wyniki_lotto");
161 String L1;
162
163 while(result.next()) {
164 L1 = result.getString("L1");
165
166 System.out.println("L1: "+L1);
167 }
168 }
169 catch (SQLException e) {
170 System.err.println("Blad przy wypisywaniu spisu firm");
171 e.printStackTrace();
172 }
173}
174
175
176
177public void L2 () {
178 try {
179 ResultSet result = stmt.executeQuery("SELECT L2 FROM wyniki_lotto");
180 String L2;
181
182 while(result.next()) {
183 L2 = result.getString("L2");
184
185 System.out.println("L2: "+L2);
186 }
187 }
188 catch (SQLException e) {
189 System.err.println("Blad przy wypisywaniu spisu firm");
190 e.printStackTrace();
191 }
192}
193
194
195public void L3 () {
196 try {
197 ResultSet result = stmt.executeQuery("SELECT L3 FROM wyniki_lotto");
198 String L3;
199
200 while(result.next()) {
201 L3 = result.getString("L3");
202
203 System.out.println("L3: "+L3);
204 }
205 }
206 catch (SQLException e) {
207 System.err.println("Blad przy wypisywaniu spisu firm");
208 e.printStackTrace();
209 }
210}
211
212
213public void L4 () {
214 try {
215 ResultSet result = stmt.executeQuery("SELECT L4 FROM wyniki_lotto");
216 String L4;
217
218 while(result.next()) {
219 L4 = result.getString("L4");
220
221 System.out.println("L4: "+L4);
222 }
223 }
224 catch (SQLException e) {
225 System.err.println("Blad przy wypisywaniu spisu firm");
226 e.printStackTrace();
227 }
228}
229
230public void L5 () {
231 try {
232 ResultSet result = stmt.executeQuery("SELECT L5 FROM wyniki_lotto");
233 String L5;
234
235 while(result.next()) {
236 L5 = result.getString("L5");
237
238 System.out.println("L5: "+L5);
239 }
240 }
241 catch (SQLException e) {
242 System.err.println("Blad przy wypisywaniu spisu firm");
243 e.printStackTrace();
244 }
245}
246
247public void L6 () {
248 try {
249 ResultSet result = stmt.executeQuery("SELECT L6 FROM wyniki_lotto");
250 String L6;
251
252 while(result.next()) {
253 L6 = result.getString("L6");
254
255 System.out.println("L6: "+L6);
256 }
257 }
258 catch (SQLException e) {
259 System.err.println("Blad przy wypisywaniu spisu firm");
260 e.printStackTrace();
261 }
262}
263
264public void rok(String rok){
265 try {
266 ResultSet result = stmt.executeQuery("SELECT * FROM tabela WHERE ROK="+rok);
267 while(result.next())
268 {
269 Numer = result.getString("NUMER");
270 Dzien = result.getString("DZIEN");
271 Miesiac = result.getString("MIESIAC");
272 Rok = result.getString("ROK");
273 L1 = result.getInt("L1");
274 L2 = result.getInt("L2");
275 L3 = result.getInt("L3");
276 L4 = result.getInt("L4");
277 L5 = result.getInt("L5");
278 L6 = result.getInt("L6");
279 System.out.println("NUMER: "+NUMER+", DZIEN: "+DZIEN+", MIESIAC: "+MIESIAC+", ROK: "+ROK+", L1: "+L1+", L2: "+L2+", L3: "+L3+", L4: "+L4+", L5: "+L5+", L6: "+L6);
280 }
281
282 } catch (SQLException e) {
283 System.err.println("Blad przy wykonywaniu polecenia");
284 e.printStackTrace();
285 }
286}
287}
288}