· 9 years ago · Nov 14, 2016, 09:06 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(){
39stmt = c.createStatement();
40 String sql = "CREATE TABLE wyniki_lotto " +
41 "(ID INT PRIMARY KEY NOT NULL," +
42 " Numer VARCHAR(4) NOT NULL, " +
43 " DZIEN VARCHAR(2) NOT NULL, " +
44 " MIESIAC VARCHAR(2) NOT NULL, " +
45 " ROK VARCHAR(4) NOT NULL, " +
46 " L1 VARCHAR(2) NOT NULL, " +
47 " L2 VARCHAR(2) NOT NULL, " +
48 " L3 VARCHAR(2) NOT NULL, " +
49 " L4 VARCHAR(2) NOT NULL, " +
50 " L5 VARCHAR(2) NOT NULL, " +
51 " L6 VARCHAR(2) NOT NULL, " +
52 )";
53 stmt.executeUpdate(sql);
54 stmt.close();
55 c.close();
56 } catch ( Exception e ) {
57 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
58 System.exit(0);
59 }
60 System.out.println("stworzono tabele");
61 }
62}
63/*public void readFile(String filePatch = "wyniki-lotto-sortowane.csv") throws IOException {
64 FileReader fileReader = new FileReader(filePath);
65 BufferedReader bufferedReader = new BufferedReader(fileReader);
66
67 String textLine = bufferedReader.readLine();
68 do {
69 System.out.println(textLine);
70
71 textLine = bufferedReader.readLine();
72 } while(textLine != null);
73
74 bufferedReader.close();
75}*/
76
77public void readFile () {
78 String fileName = "wyniki-lotto-sortowane.csv";
79 String line = null;
80 try {
81 FileReader fileReader =
82 new FileReader(fileName);
83 BufferedReader bufferedReader =
84 new BufferedReader(fileReader);
85
86 while((line = bufferedReader.readLine()) != null) {
87 String[] parts = line.split(";");
88 String Numer = parts[0];
89 String Dzien = parts[1];
90 String Miesiac = parts[2];
91 String Rok = parts[3];
92 String L1 = parts[4];
93 String L2 = parts[5];
94 String L3 = parts[6];
95 String L4 = parts[7];
96 String L5 = parts[8];
97 String L6 = parts[9];
98 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+");";
99 try {
100 stmt.execute(insertQuery);
101 } catch (SQLException e) {
102 System.err.println("Blad przy dodawaniu wiersza");
103 e.printStackTrace();
104 }
105
106 }
107 bufferedReader.close();
108 }
109 catch(FileNotFoundException ex) {
110 System.out.println(
111 "Blad przy otwieraniu pliku '" +
112 fileName + "'");
113 }
114 catch(IOException ex) {
115 System.out.println(
116 "Blad przy wczytywaniu pliku '"
117 + fileName + "'");
118 }
119}
120
121public void wyswietlwszystkielosowania(){
122 try {
123 ResultSet result = stmt.executeQuery("SELECT * FROM wyniki_lotto");
124 String NUMER, DZIEN, MIESIAC, ROK, L1, L2, L3, L4, L5, L6;
125
126 while(result.next()) {
127 Numer = result.getString("NUMER");
128 Dzien = result.getString("DZIEN");
129 Miesiac = result.getString("MIESIAC");
130 Rok = result.getString("ROK");
131 L1 = result.getInt("L1");
132 L2 = result.getInt("L2");
133 L3 = result.getInt("L3");
134 L4 = result.getInt("L4");
135 L5 = result.getInt("L5");
136 L6 = result.getInt("L6");
137 System.out.println("NUMER: "+NUMER+", DZIEN: "+DZIEN+", MIESIAC: "+MIESIAC+", ROK: "+ROK+", L1: "+L1+", L2: "+L2+", L3: "+L3+", L4: "+L4+", L5: "+L5+", L6: "+L6);
138 }
139 } catch (SQLException e) {
140 System.err.println("Blad przy wykonywaniu polecenia");
141 e.printStackTrace();
142 }
143}
144
145public void liczba(String liczba){
146 try {
147 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
148);
149 int wynik = result.getInt("wynik");
150 System.out.println("Liczba "+liczba+" pojawiła się: "+wynik+" razy");
151 } catch (SQLException e) {
152 System.err.println("Blad przy wykonywaniu polecenia");
153 e.printStackTrace();
154 }
155}
156public void pierwsza () {
157 try {
158 ResultSet result = stmt.executeQuery("SELECT L1 FROM wyniki_lotto");
159 String L1;
160
161 while(result.next()) {
162 L1 = result.getString("L1");
163
164 System.out.println("L1: "+L1);
165 }
166 }
167 } catch (SQLException e) {
168 System.err.println("Blad przy wypisywaniu spisu firm");
169 e.printStackTrace();
170 }
171}
172
173}