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
|
/*
@ITMillApache2LicenseForJavaFiles@
*/
package com.itmill.toolkit.demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Creates temporary database named toolkit with sample table named employee and
* populates it with data. By default we use HSQLDB. Ensure that you have
* hsqldb.jar under WEB-INF/lib directory. Database is will be created into
* memory.
*
* @author IT Mill Ltd.
*
*/
public class SampleDatabase {
public static final int ROWCOUNT = 1000;
private Connection connection = null;
private static final String[] firstnames = new String[] { "Amanda",
"Andrew", "Bill", "Frank", "Matt", "Xavier", "John", "Mary", "Joe",
"Gloria", "Marcus", "Belinda", "David", "Anthony", "Julian",
"Paul", "Carrie", "Susan", "Gregg", "Michael", "William", "Ethan",
"Thomas", "Oscar", "Norman", "Roy", "Sarah", "Jeff", "Jane",
"Peter", "Marc", "Josie", "Linus" };
private static final String[] lastnames = new String[] { "Torvalds",
"Smith", "Jones", "Beck", "Burton", "Bell", "Davis", "Burke",
"Bernard", "Hood", "Scott", "Smith", "Carter", "Roller", "Conrad",
"Martin", "Fisher", "Martell", "Freeman", "Hackman", "Jones",
"Harper", "Russek", "Johnson", "Sheridan", "Hill", "Parker",
"Foster", "Moss", "Fielding" };
private static final String[] titles = new String[] { "Project Manager",
"Marketing Manager", "Sales Manager", "Sales", "Trainer",
"Technical Support", "Account Manager", "Customer Support",
"Testing Engineer", "Software Designer", "Programmer", "Consultant" };
private static final String[] units = new String[] { "Tokyo",
"Mexico City", "Seoul", "New York", "Sao Paulo", "Bombay", "Delhi",
"Shanghai", "Los Angeles", "London", "Shanghai", "Sydney",
"Bangalore", "Hong Kong", "Madrid", "Milano", "Beijing", "Paris",
"Moscow", "Berlin", "Helsinki" };
/**
* Create temporary database.
*
*/
public SampleDatabase() {
// connect to SQL database
connect();
// initialize SQL database
createTables();
// test by executing sample JDBC query
testDatabase();
}
/**
* Creates sample table named employee and populates it with data.Use the
* specified database connection.
*
* @param connection
*/
public SampleDatabase(Connection connection) {
// initialize SQL database
createTables();
// test by executing sample JDBC query
testDatabase();
}
/**
* Connect to SQL database. In this sample we use HSQLDB and an toolkit
* named database in implicitly created into system memory.
*
*/
private void connect() {
// use memory-Only Database
final String url = "jdbc:hsqldb:mem:toolkit";
try {
Class.forName("org.hsqldb.jdbcDriver").newInstance();
connection = DriverManager.getConnection(url, "sa", "");
} catch (final Exception e) {
throw new RuntimeException(e);
}
}
/**
* use for SQL commands CREATE, DROP, INSERT and UPDATE
*
* @param expression
* @throws SQLException
*/
public void update(String expression) throws SQLException {
Statement st = null;
st = connection.createStatement();
final int i = st.executeUpdate(expression);
if (i == -1) {
throw new SQLException("Database error : " + expression);
}
st.close();
}
/**
* Create test table and few rows. Issue note: using capitalized column
* names as HSQLDB returns column names in capitalized form with this demo.
*
*/
private void createTables() {
try {
String stmt = null;
stmt = "CREATE TABLE employee ( ID INTEGER IDENTITY, FIRSTNAME VARCHAR(100), "
+ "LASTNAME VARCHAR(100), TITLE VARCHAR(100), UNIT VARCHAR(100) )";
update(stmt);
for (int j = 0; j < ROWCOUNT; j++) {
stmt = "INSERT INTO employee(FIRSTNAME, LASTNAME, TITLE, UNIT) VALUES ("
+ "'"
+ firstnames[(int) (Math.random() * (firstnames.length - 1))]
+ "',"
+ "'"
+ lastnames[(int) (Math.random() * (lastnames.length - 1))]
+ "',"
+ "'"
+ titles[(int) (Math.random() * (titles.length - 1))]
+ "',"
+ "'"
+ units[(int) (Math.random() * (units.length - 1))]
+ "'" + ")";
update(stmt);
}
} catch (final SQLException e) {
if (e.toString().indexOf("Table already exists") == -1) {
throw new RuntimeException(e);
}
}
}
/**
* Test database connection with simple SELECT command.
*
*/
private String testDatabase() {
String result = null;
try {
final Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
final ResultSet rs = stmt
.executeQuery("SELECT COUNT(*) FROM employee");
rs.next();
result = "rowcount for table test is " + rs.getObject(1).toString();
stmt.close();
} catch (final SQLException e) {
throw new RuntimeException(e);
}
return result;
}
public Connection getConnection() {
return connection;
}
}
|
