8qqajBGN

1package j3l2databases;
2
3import java.io.IOException;
4import java.nio.file.Files;
5import java.nio.file.Paths;
6import java.util.logging.Level;
7import java.util.logging.Logger;
8
9/**
10 * Имя входного файла передается в виде параметра. Имя базы данных фиксированно,
11 * база данных создается в текущем каталоге.
12 * 
13 * @author efremov
14 */
15public class J3L2Databases {
16
17    /**
18     * @param args the command line arguments
19     */
20    public static void main(String[] args) {
21        String fileName = args[0];
22        System.out.println("Parsing file: " + fileName);
23        
24        // Parsing the input file and storing into database the updated items:
25        try (ProductStorage stor = ProductStorage.connectByPath("products.db") ) {
26            Files.lines(Paths.get(fileName))
27                    .skip(1)  // Skipping the header
28                    .map(Product::fromString)
29                    .filter(Product::isValid) // Skipping objects 
30                    .forEach(stor::putOrUpdateIfChanged);
31        } catch (IOException ex) {
32            LOG.log(Level.SEVERE, "Error when reading the input file: " + fileName, ex);
33            System.exit(1);
34        } catch (Exception ex) {
35            LOG.log(Level.SEVERE, null, ex);
36        }
37        
38    }
39    
40    private static final Logger LOG = Logger.getLogger(J3L2Databases.class.getName());
41    
42}
43
44//=======================
45package j3l2databases;
46
47import java.math.BigDecimal;
48import java.text.MessageFormat;
49import java.util.logging.Logger;
50
51/**
52 * Класс a la entity, в котором объединены поля, читаемые из файла. 
53 * Считается, что у объектов есть идентификаторы 
54 * (@see #id), в данной реализации они берутся из поля 
55 * @see #refNumber (которое соответствует столбцу Код в исходном файле).
56 * Преобразование из строки в объект класса делается статическим методом
57 * @see #fromString(java.lang.String), однако при неудачном разборе строки
58 * ввода никакох исключений не возбуждается, а возвращается "пустой" экземпляр
59 * класса, помеченный флагом недействительности (проверяется методом 
60 * @see #isValid() ), это сделано чтобы упростить обработку в потоке.
61 * 
62 * @author efremov
63 */
64public class Product {
65
66    private final int id;
67    private final String group1;
68    private final String group2;
69    private final String group3;
70    private final String group4;
71    private final String group5;
72    private final String shortName;
73    private final int refNumber;
74    private final String catNumber;
75    private final String fullName;
76    private final BigDecimal price;
77    private final boolean isValid;
78
79    public Product(int id, String group1, String group2, String group3,
80            String group4, String group5, String shortName, int refNumber, 
81            String catNumber, String fullName, BigDecimal price) {
82        this.id = id;
83        this.group1 = (group1 == null) ? "" : group1.trim();
84        this.group2 = (group2 == null) ? "" : group2.trim();
85        this.group3 = (group3 == null) ? "" : group3.trim();
86        this.group4 = (group4 == null) ? "" : group4.trim();
87        this.group5 = (group5 == null) ? "" : group5.trim();
88        this.shortName = (shortName == null) ? "" : shortName.trim();
89        this.refNumber = refNumber;
90        this.catNumber = (catNumber == null) ? "" : catNumber.trim();
91        this.fullName = (fullName == null) ? "" : fullName.trim();
92        this.price = price;
93        isValid = true;
94    }
95
96    public Product() {
97        this.id = 0;
98        this.group1 = "";
99        this.group2 = "";
100        this.group3 = "";
101        this.group4 = "";
102        this.group5 = "";
103        this.shortName = "";
104        this.refNumber = 0;
105        this.catNumber = "";
106        this.fullName = "";
107        this.price = BigDecimal.ZERO;
108        isValid = false;
109    }
110
111    public boolean isValid() {
112        return isValid;
113    }
114
115    public int getId() {
116        return id;
117    }
118
119    public String getGroup1() {
120        return group1;
121    }
122
123    public String getGroup2() {
124        return group2;
125    }
126
127    public String getGroup3() {
128        return group3;
129    }
130
131    public String getGroup4() {
132        return group4;
133    }
134    
135    public String getGroup5() {
136        return group5;
137    }
138    
139    public String getShortName() {
140        return shortName;
141    }
142
143    public String getFullName() {
144        return fullName;
145    }
146
147    public BigDecimal getPrice() {
148        return price;
149    }
150
151    public boolean isGroupsOrPriceDifferent(Product other) {
152        if(other == null)
153            return true;
154        
155        boolean res =  !group1.equals(other.group1) || 
156                !group2.equals(other.group2) ||
157                !group3.equals(other.group3) ||
158                !group4.equals(other.group4) ||
159                !group5.equals(other.group5) ||
160                !price.equals(other.price);
161        return res;
162    }
163    
164    @Override
165    public String toString() {
166        return "Product{" + "id=" + id + ", group1=" + group1 + ", group2=" 
167                + group2 + ", group3=" + group3 + ", group4=" + group4 
168                + ", group5=" + group5 + ", shortName=" + shortName 
169                + ", refNumber=" + refNumber + ", catNumber=" 
170                + catNumber + ", fullName=" + fullName + ", price=" + price 
171                + ", isValid=" + isValid + '}';
172    }
173
174    public static Product fromString(String sIn) {
175        // Parse a tab-separated string
176        try {
177            String[] field = sIn.split("\\t");
178            if (field.length != 10) {
179                throw new RuntimeException("Input string cannot be split into 10 fields");
180            }
181            String group1 = field[0].trim();
182            String group2 = field[1].trim();
183            String group3 = field[2].trim();
184            String group4 = field[3].trim();
185            String group5 = field[4].trim();
186            String shortName = field[5].trim();
187            int refNumber = Integer.parseInt(field[6]);
188            String catNumber = field[7].trim();
189            String fullName = field[8].trim();
190            BigDecimal price = new BigDecimal(field[9]);
191            return new Product(refNumber, group1, group2, group3, group4, 
192                    group5, shortName, refNumber, catNumber, fullName, price);
193        } catch (Exception ex) {
194            LOG.warning(ex.toString());
195            LOG.warning(MessageFormat.format("Invalid input string: {0}", sIn));
196            return new Product();
197        }
198    }
199
200    private static final Logger LOG = Logger.getLogger(Product.class.getName());
201
202}
203
204// =====================
205package j3l2databases;
206
207import java.sql.Connection;
208import java.sql.DriverManager;
209import java.sql.PreparedStatement;
210import java.sql.ResultSet;
211import java.sql.SQLException;
212import java.sql.Statement;
213import java.util.logging.Level;
214import java.util.logging.Logger;
215
216/**
217 * Главный класс для работы с базой данных.
218 * 
219 * Для создания экземпляра класс используется статический метод 
220 * @see #connectByPath(java.lang.String).
221 * Основной (единственный) метод, который используется для обновления - это
222 * @see #putOrUpdateIfChanged(j3l2databases.Product). Этот метод не возбуждает
223 * исключений чтобы соответствовать интерфейсу Consumer.
224 * Драйвер SQLite подгружается
225 * в статическом блоке. При первом получении экземпляра класса делается попытка создать 
226 * таблицу products - это кривовато, но удобно для тестирования.
227 * 
228 * @author efremov
229 */
230public class ProductStorage implements AutoCloseable {
231
232
233    public static ProductStorage connectByPath(String dbPath) throws SQLException {
234        Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
235        if(!databaseInitialized) {
236            inititalizeSQLiteDB(conn);
237            databaseInitialized = true;
238        }
239        return new ProductStorage(conn);
240    }
241
242
243    public Product getById(int id) throws SQLException {
244        find_by_id_stmt.setInt(1, id);
245        ResultSet rs = find_by_id_stmt.executeQuery();
246        if (rs.next()) {
247            Product p = new Product(rs.getInt("id"),
248                    rs.getString("group1"),
249                    rs.getString("group2"),
250                    rs.getString("group3"),
251                    rs.getString("group4"),
252                    rs.getString("group5"),
253                    rs.getString("short_name"),
254                    rs.getInt("id"),
255                    rs.getString("cat_number"),
256                    rs.getString("full_name"),
257                    rs.getBigDecimal("price"));
258            return p;
259        }
260        return null;
261    }
262
263    public boolean updateItemById(Product p) throws SQLException {
264        update_tmpl.setString(1, p.getGroup1());
265        update_tmpl.setString(2, p.getGroup2());
266        update_tmpl.setString(3, p.getGroup3());
267        update_tmpl.setString(4, p.getGroup4());
268        update_tmpl.setString(5, p.getGroup5());
269        update_tmpl.setBigDecimal(6, p.getPrice());
270        update_tmpl.setInt(7, p.getId());
271        boolean res = update_tmpl.execute();
272        conn.commit();
273        return update_tmpl.getUpdateCount() > 0;
274    }
275
276    public void insert(Product p) throws SQLException {
277        insert_stmt.setInt(1, p.getId());
278        insert_stmt.setString(2, p.getGroup1());
279        insert_stmt.setString(3, p.getGroup2());
280        insert_stmt.setString(4, p.getGroup3());
281        insert_stmt.setString(5, p.getGroup4());
282        insert_stmt.setString(6, p.getGroup5());
283        insert_stmt.setString(7, p.getShortName());
284        insert_stmt.setString(8, p.getFullName());
285        insert_stmt.setBigDecimal(9, p.getPrice());
286        insert_stmt.execute();
287        conn.commit();
288    }
289
290    public void putOrUpdateIfChanged(Product p) {
291        try {
292            Product pWas = getById(p.getId());
293            if (pWas == null) {
294                insert(p);
295                System.out.println(p.toString() + " put into the db");
296            } else if (p.isGroupsOrPriceDifferent(pWas)) {
297                System.out.print("Updating: " + p.getId());
298                boolean res = updateItemById(p);
299                System.out.println("   Result: " + res);
300            } else {
301                System.out.println("Id " + p.getId() + " has not changed");
302            }
303        } catch (SQLException ex) {
304            LOG.log(Level.SEVERE, "Error while putting or updating a product", ex);
305        }
306    }
307
308    @Override
309    public void close() throws Exception {
310        if (conn != null) {
311            conn.close();
312        }
313    }
314    
315
316    private ProductStorage(Connection conn) throws SQLException {
317        this.conn = conn;
318        insert_stmt = conn.prepareStatement(INSERT_TMPL);
319        find_by_id_stmt = conn.prepareStatement(FIND_BY_ID_TMPL);
320        update_tmpl = conn.prepareStatement(UPDATE_TMPL);
321        this.conn.setAutoCommit(false);
322    }
323
324    private static void inititalizeSQLiteDB(Connection conn) throws SQLException {
325        System.out.println("Initialization of DB...");
326        String sql = "CREATE TABLE IF NOT EXISTS products (\n"
327                + "    id         INTEGER         PRIMARY KEY\n"
328                + "                               NOT NULL\n"
329                + "                               UNIQUE,\n"
330                + "    group1     STRING,\n"
331                + "    group2     STRING,\n"
332                + "    group3     STRING,\n"
333                + "    group4     STRING,\n"
334                + "    group5     STRING,\n"
335                + "    short_name STRING          NOT NULL,\n"
336                + "    cat_number STRING,\n"
337                + "    full_name  STRING          NOT NULL,\n"
338                + "    price      DECIMAL (15, 2) NOT NULL\n"
339                + ");";
340        Statement stmt = conn.createStatement();
341        boolean n = stmt.execute(sql);
342        System.out.println("Result of inititalization: " + n);
343    }
344    
345    private final Connection conn;
346    private final String INSERT_TMPL = "INSERT INTO products (id, group1, group2, group3, group4, group5, short_name, full_name, price) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
347    private final String UPDATE_TMPL = "UPDATE products SET group1=?, group2=?, group3=?, group4=?, group5=?, price=? WHERE id=?";
348    private final String FIND_BY_ID_TMPL = "SELECT id, group1, group2, group3, group4, group5, short_name, cat_number, full_name, price FROM products WHERE id=?";
349    private final PreparedStatement insert_stmt;
350    private final PreparedStatement find_by_id_stmt;
351    private final PreparedStatement update_tmpl;
352    
353    private static final Logger LOG = Logger.getLogger(ProductStorage.class.getName());
354    private static boolean databaseInitialized = false;
355
356    static {
357        try {
358            Class.forName("org.sqlite.JDBC");
359        } catch (ClassNotFoundException ex) {
360            LOG.log(Level.SEVERE, "Failed to load SQLite driver", ex);
361            throw new RuntimeException("Cannot find org.sqlite.JDBC", ex);
362        }
363    }
364
365}