forked from biblelamp/JavaExercises
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathHW2Lesson.java
More file actions
175 lines (165 loc) · 6.37 KB
/
HW2Lesson.java
File metadata and controls
175 lines (165 loc) · 6.37 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
/**
* Java. Level 3. Lesson 2. Homework
*
* 1. Create a product table (id, title, cost) // -create
* 2. Clear the table and fill it with 1000 products // -init <quantity>
* 3. Get the price of the product by name // -getprice <name>
* 4. Change the price of product // -setprice <name> <price>
* 5. List of products in the given price range // -list <price1> <price2>
*
* @author Sergey Iryupin
* @version Jul 14, 2018
* @link https://github.com/biblelamp
*/
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
public class HW2Lesson {
static final String DRIVER_NAME = "org.sqlite.JDBC";
static final String DB_NAME = "jdbc:sqlite:goods.db";
final String TABLE_NAME = "products";
final String COL_ID = "id";
final String COL_TITLE = "title";
final String COL_PRICE = "price";
final String SQL_CREATE_TABLE =
"DROP TABLE IF EXISTS " + TABLE_NAME + ";" +
"CREATE TABLE " + TABLE_NAME + "(" +
COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
COL_TITLE + " TEXT," +
COL_PRICE + " REAL" +
");";
final String SQL_CLEAR_TABLE =
"DELETE FROM " + TABLE_NAME + ";" +
"DELETE FROM sqlite_sequence WHERE name='" + TABLE_NAME + "'"; // reset
final String SQL_INSERT = "INSERT INTO " + TABLE_NAME +
" (" + COL_TITLE + ", " + COL_PRICE + ") VALUES (?, ?);";
final String SQL_SELECT = "SELECT * FROM " + TABLE_NAME + " WHERE title=?;";
final String SQL_UPDATE =
"UPDATE " + TABLE_NAME + " SET price=? WHERE title=?;";
final String SQL_LIST_IN_RANGE =
"SELECT * FROM " + TABLE_NAME + " WHERE price>=? AND price<=?";
static final String CMD_CREATE = "-create";
static final String CMD_INIT = "-init";
static final String CMD_GETPRICE = "-getprice";
static final String CMD_SETPRICE = "-setprice";
static final String CMD_LIST = "-list";
static final String MSG_NOTFOUND = "Not found";
Connection connection = null;
public static void main(String[] args) {
HW2Lesson hw = new HW2Lesson(DRIVER_NAME, DB_NAME);
if (args.length > 0)
switch (args[0]) {
case CMD_CREATE:
hw.createTable();
break;
case CMD_INIT:
if (args.length > 1)
hw.initTable(Integer.parseInt(args[1]));
break;
case CMD_GETPRICE:
if (args.length > 1) {
float price = hw.getPriceByName(args[1]);
System.out.println((price < 0)? MSG_NOTFOUND : price);
}
break;
case CMD_SETPRICE:
if (args.length > 2)
hw.setPriceByName(args[1], Float.parseFloat(args[2]));
break;
case CMD_LIST:
if (args.length > 2) {
for (String item : hw.getListInRange(
Float.parseFloat(args[1]),
Float.parseFloat(args[2])))
System.out.println(item);
}
}
hw.close();
}
HW2Lesson(String driverName, String dbName) { // open connection
try {
Class.forName(driverName);
connection = DriverManager.getConnection(dbName);
} catch (ClassNotFoundException | SQLException ex) {
ex.printStackTrace();
}
}
void close() { // close connection
if (connection != null)
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
void createTable() { // stage 1. create table
try (Statement stmt = connection.createStatement()) {
stmt.executeUpdate(SQL_CREATE_TABLE);
} catch (SQLException ex) {
ex.printStackTrace();
}
}
void initTable(int quantity) { // stage 2. init & fill table
try (Statement stmt = connection.createStatement();
PreparedStatement pstmt =
connection.prepareStatement(SQL_INSERT)) {
stmt.executeUpdate(SQL_CLEAR_TABLE);
for (int i = 1; i <= quantity; i++) {
pstmt.setString(1, "product" + i);
pstmt.setFloat(2, i*10);
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
float getPriceByName(String name) { // stage 3. get price by name
float price = -1;
try (PreparedStatement pstmt =
connection.prepareStatement(SQL_SELECT)) {
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();
while (rs.next())
price = rs.getFloat(COL_PRICE);
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
return price;
}
// stage 4. set price by name
void setPriceByName(String name, float price) {
try (PreparedStatement pstmt =
connection.prepareStatement(SQL_UPDATE)) {
pstmt.setFloat(1, price);
pstmt.setString(2, name);
pstmt.executeUpdate();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
// stage 5. list in range
List<String> getListInRange(float priceFrom, float priceTo) {
List<String> list = new ArrayList<>();
try (PreparedStatement pstmt =
connection.prepareStatement(SQL_LIST_IN_RANGE)) {
pstmt.setFloat(1, priceFrom);
pstmt.setFloat(2, priceTo);
ResultSet rs = pstmt.executeQuery();
while (rs.next())
list.add(rs.getInt(COL_ID) + "\t" +
rs.getString(COL_TITLE) + "\t" +
rs.getFloat(COL_PRICE));
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
return list;
}
}