forked from mark-watson/Java-AI-Book-Code
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathCreateSampleDatabases.java
More file actions
executable file
·167 lines (152 loc) · 6.41 KB
/
CreateSampleDatabases.java
File metadata and controls
executable file
·167 lines (152 loc) · 6.41 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
package database;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.w3c.dom.*;
import org.w3c.dom.Document;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;
public class CreateSampleDatabases {
public static void main(String argv[]) {
try {
// open a database connection:
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection conn = DriverManager.getConnection("jdbc:derby:test_database;create=true");
System.out.println("conn: " + conn);
Statement s = conn.createStatement();
// US STATES
try { s.execute("drop table usstates"); } catch (Exception ignore) { }
String sql2 = "create table usstates (name varchar(20), abbreviation char(2), industry varchar(200), agriculture varchar(200), population bigint)";
System.out.println(sql2);
s.execute(sql2);
System.out.println("Created table usstates");
DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
Document doc = docBuilder.parse(new File("test_data/xml/USstates.xml"));
// normalize text representation
doc.getDocumentElement().normalize();
NodeList listOfStates = doc.getElementsByTagName("state");
int totalStates = listOfStates.getLength();
System.out.println("Total no of states : " + totalStates);
for (int stateIndex = 0; stateIndex < totalStates; stateIndex++) {
Node firstStateNode = listOfStates.item(stateIndex);
if (firstStateNode.getNodeType() == Node.ELEMENT_NODE) {
Element firstStateElement = (Element) firstStateNode;
String name = firstStateElement.getAttribute("name").replaceAll("'", "");
String abbreviation = firstStateElement.getAttribute("abbrev");
String industry = firstStateElement.getAttribute("industry").replaceAll("'", "");
String agriculture = firstStateElement.getAttribute("agriculture").replaceAll("'", "");
long population = parseLong(firstStateElement
.getAttribute("population"));
String sql = "insert into usstates values ('"+name+"','"+abbreviation+"','"+industry+"','"+agriculture+"',"+population+")";
System.out.println("\n"+sql+"\n");
s.execute(sql);
}
}
// FACTBOOK
try { s.execute("drop table factbook"); } catch (Exception ignore) { }
sql2 = "create table factbook (name varchar(60), location varchar(150), export bigint, import bigint, debt bigint, aid bigint, unemployment_percent int, inflation_percent int)";
System.out.println(sql2);
s.execute(sql2);
System.out.println("Created table factbook");
docBuilderFactory = DocumentBuilderFactory
.newInstance();
docBuilder = docBuilderFactory.newDocumentBuilder();
doc = docBuilder.parse(new File(
"test_data/xml/FactBook.xml"));
// normalize text representation
doc.getDocumentElement().normalize();
System.out.println("Root element of XML document: "
+ doc.getDocumentElement().getNodeName());
NodeList listOfCountrys = doc.getElementsByTagName("country");
int totalCountrys = listOfCountrys.getLength();
System.out.println("Total no of countries : " + totalCountrys);
for (int countryIndex = 0; countryIndex < totalCountrys; countryIndex++) {
Node firstCountryNode = listOfCountrys.item(countryIndex);
if (firstCountryNode.getNodeType() == Node.ELEMENT_NODE) {
Element firstCountryElement = (Element) firstCountryNode;
String name = firstCountryElement.getAttribute("name").replaceAll("'", "");
String location = firstCountryElement
.getAttribute("location").replaceAll("'", "");
if (location.length() > 150) location = location.substring(0,149);
long population = parseLong(firstCountryElement
.getAttribute("population"));
long exports = (long)parseFloat(firstCountryElement
.getAttribute("exports"));
long imports = (long)parseFloat(firstCountryElement
.getAttribute("imports"));
long debt = (long)parseFloat(firstCountryElement
.getAttribute("debt"));
long aid = (long)parseFloat(firstCountryElement
.getAttribute("aid"));
long unemployment = (long)parseFloat(firstCountryElement
.getAttribute("unemployment"));
long inflation = (long)parseFloat(firstCountryElement
.getAttribute("inflation"));
System.out.println(name);
System.out.println(" location: " + location);
System.out.println(" population: " + population);
System.out.println(" exports: " + exports);
System.out.println(" imports: " + imports);
System.out.println(" debt: " + debt);
System.out.println(" aid: " + aid);
System.out.println(" unemployment: " + unemployment);
System.out.println(" inflation: " + inflation);
String sql = "insert into factbook values ('"+name+"','"+location+"',"+exports+","+imports+","+debt+","+aid+","+unemployment+","+inflation+")";
System.out.println("\n"+sql+"\n");
s.execute(sql);
}
}
} catch (Exception ex) {
System.out.println("Error" + ex);
ex.printStackTrace();
}
}
private static float parseFloat(String s) {
String orig = s;
float ret = -1f;
float scale = 1f;
try {
if (s.indexOf("million") > -1) scale = 1000000f;
if (s.indexOf("billion") > -1) scale = 1000000000f;
s = s.replaceAll("\\$", "");
int index = s.indexOf("%");
if (index > -1)
s = s.substring(0, index);
index = s.indexOf(" ");
if (index > -1)
s = s.substring(0, index);
s = s.replaceAll(",", "");
ret = Float.parseFloat(s);
} catch (Exception ex) {
//System.err.println("Error in parseFloat: " + ex + " for input: "
// + orig + " current s: " + s);
}
return ret * scale;
}
private static long parseLong(String s) {
String orig = s;
long ret = -1;
long scale = 1;
try {
if (s.indexOf("million") > -1) scale = 1000000;
if (s.indexOf("billion") > -1) scale = 1000000000;
s = s.replaceAll("\\$", "");
int index = s.indexOf("%");
if (index > -1)
s = s.substring(0, index);
index = s.indexOf(" ");
if (index > -1)
s = s.substring(0, index);
s = s.replaceAll(",", "");
ret = Integer.parseInt(s);
} catch (Exception ex) {
//System.err.println("Error in parseInt: " + ex + " for input: "
// + orig + " current s: " + s);
}
return ret * scale;
}
}