source: winedb/src/utils/WineImporter.java@ 9b6a069

Last change on this file since 9b6a069 was 9b6a069, checked in by dportnoy <devnull@…>, 13 years ago

Initial commit
  • Property mode set to 100644
File size: 6.3 KB
Line 
1package utils;
2
3import java.io.*;
4import java.sql.*;
5
6public class WineImporter {
7 static Connection conn;
8
9 //if the string doesn't exist in the table, it gets added
10 public static int getId(String name, String table) {
11 Statement stmt = null;
12 ResultSet rs = null;
13 int id = 0;
14
15 if(name.equals(""))
16 return 0;
17 name = name.replace("'", "\\'");
18
19 try {
20 stmt = conn.createStatement();
21 rs = stmt.executeQuery("SELECT id FROM "+table+" WHERE name='"+name+"'");
22
23 if(!rs.next()) {
24 stmt = conn.createStatement();
25 stmt.executeUpdate("INSERT INTO "+table+" (name) VALUES ('"+name+"')");
26 stmt = conn.createStatement();
27 rs = stmt.executeQuery("SELECT id FROM "+table+" WHERE name='"+name+"'");
28 rs.next();
29 }
30 id = rs.getInt("id");
31 }catch(SQLException ex) {
32 // handle any errors
33 System.out.println("SQLException: " + ex.getMessage());
34 System.out.println("SQLState: " + ex.getSQLState());
35 System.out.println("VendorError: " + ex.getErrorCode());
36 }
37
38 return id;
39 }
40
41 public static void main(String[] args) {
42 BufferedReader in;
43
44 try {
45 conn = DriverManager.getConnection("jdbc:mysql://localhost/wines", "root", "");
46
47 in = Utils.loadTextFile("wineCSV.csv");
48
49 String line, name, winery, vintage, country, region, variety, location, date_added, date_drank, size, rating, price, image;
50 int loc_num, rack, section, column, row, winery_id, country_id, region_id, variety_id;
51
52 in.readLine(); //ignore column headers
53 while(in.ready()) {
54 line = in.readLine();
55
56 name = line.substring(0, line.indexOf(","));
57 line = line.substring(line.indexOf(",")+1);
58 name = name.replace("'", "\\'");
59
60 if(line.substring(0, 1).equals("\"")) {
61 winery = line.substring(0, line.indexOf(",", line.lastIndexOf("\"")));
62 line = line.substring(line.indexOf(",", line.lastIndexOf("\""))+1);
63 winery = winery.substring(1, winery.length()-1);
64 }else {
65 winery = line.substring(0, line.indexOf(","));
66 line = line.substring(line.indexOf(",")+1);
67 winery = winery.trim();
68 }
69 winery_id = getId(winery, "wineries");
70
71 vintage = line.substring(0, line.indexOf(","));
72 line = line.substring(line.indexOf(",")+1);
73 if(vintage.equals(""))
74 vintage = "0";
75
76 country = line.substring(0, line.indexOf(","));
77 line = line.substring(line.indexOf(",")+1);
78 if(country.equals("U.S.A."))
79 country = "USA";
80 country_id = getId(country, "countries");
81
82 if(line.substring(0, 1).equals("\"")) {
83 region = line.substring(0, line.indexOf(",", line.lastIndexOf("\"")));
84 line = line.substring(line.indexOf(",", line.lastIndexOf("\""))+1);
85 region = region.substring(1, region.length()-1);
86 }else {
87 region = line.substring(0, line.indexOf(","));
88 line = line.substring(line.indexOf(",")+1);
89 region = region.trim();
90 }
91 region_id = getId(region, "regions");
92
93 variety = line.substring(0, line.indexOf(","));
94 line = line.substring(line.indexOf(",")+1);
95 variety_id = getId(variety, "varieties");
96
97 location = line.substring(0, line.indexOf(","));
98 line = line.substring(line.indexOf(",")+1);
99
100 if(location.length() == 4) {
101 rack = Integer.parseInt(location.substring(0, 1));
102 section = Integer.parseInt(location.substring(1, 2));
103 column = Integer.parseInt(location.substring(2, 3));
104 row = Integer.parseInt(location.substring(3, 4));
105
106 loc_num = (rack << 24) + (section << 16) + (column << 8) + row;
107 }else
108 loc_num = 0;
109
110 date_added = line.substring(0, line.indexOf(","));
111 line = line.substring(line.indexOf(",")+1);
112 date_added = date_added.replace('/', '-');
113 date_added = date_added.substring(6, 10) + "-" + date_added.substring(0, 5);
114
115 rating = line.substring(0, line.indexOf(","));
116 line = line.substring(line.indexOf(",")+1);
117
118 date_drank = line.substring(0, line.indexOf(","));
119 line = line.substring(line.indexOf(",")+1);
120 date_drank = date_drank.replace('/', '-');
121 if(date_drank.equals(""))
122 date_drank = "0000-00-00";
123 else
124 date_drank = date_drank.substring(6, 10) + "-" + date_drank.substring(0, 5);
125
126 size = line.substring(0, line.indexOf(","));
127 line = line.substring(line.indexOf(",")+1);
128
129 price = line.substring(0, line.indexOf(","));
130 line = line.substring(line.indexOf(",")+1); //
131
132 image = line;
133 if(!image.equals(""))
134 image += ".png";
135
136 System.out.println(name+","+winery_id+","+vintage+","+country_id+","+region_id+","+variety_id+","+loc_num+","+date_added+","+date_drank+","+size+","+image);
137
138 Statement stmt = null;
139
140 String winery_id_string, country_id_string, region_id_string, variety_id_string;
141
142 if(winery_id == 0)
143 winery_id_string = "NULL";
144 else
145 winery_id_string = "'"+winery_id+"'";
146
147 if(country_id == 0)
148 country_id_string = "NULL";
149 else
150 country_id_string = "'"+country_id+"'";
151
152 if(region_id == 0)
153 region_id_string = "NULL";
154 else
155 region_id_string = "'"+region_id+"'";
156
157 if(variety_id == 0)
158 variety_id_string = "NULL";
159 else
160 variety_id_string = "'"+variety_id+"'";
161
162 try {
163 stmt = conn.createStatement();
164 stmt.executeUpdate("INSERT INTO wines (name, winery_id, vintage, country_id, region_id, variety_id, date_added, date_drank, size, image, cellar_location) VALUES ('"+name+"', "+winery_id_string+", '"+vintage+"', "+country_id_string+", "+region_id_string+", "+variety_id_string+", '"+date_added+"', '"+date_drank+"', '"+size+"', '"+image+"', '"+loc_num+"')");
165 }catch(SQLException ex) {
166 // handle any errors
167 System.out.println("SQLException: " + ex.getMessage());
168 System.out.println("SQLState: " + ex.getSQLState());
169 System.out.println("VendorError: " + ex.getErrorCode());
170 }
171 }
172
173 conn.close();
174 }catch(IOException ioe) {
175 ioe.printStackTrace();
176 }catch(SQLException ex) {
177 // handle any errors
178 System.out.println("SQLException: " + ex.getMessage());
179 System.out.println("SQLState: " + ex.getSQLState());
180 System.out.println("VendorError: " + ex.getErrorCode());
181 }
182 }
183}
Note: See TracBrowser for help on using the repository browser.