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
|
/*
@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;
import java.sql.Timestamp;
/**
* 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 SampleCalendarDatabase {
public static final int ENTRYCOUNT = 100;
public static final String DB_TABLE_NAME = "calendar";
public static final String PROPERTY_ID_START = "EVENTSTART";
public static final String PROPERTY_ID_END = "EVENTEND";
public static final String PROPERTY_ID_TITLE = "TITLE";
public static final String PROPERTY_ID_NOTIME = "NOTIME";
private Connection connection = null;
private static final String[] titles = new String[] { "Meeting", "Dentist",
"Haircut", "Bank", "Birthday", "Library", "Rent", "Acme test",
"Party" };
/**
* Create temporary database.
*
*/
public SampleCalendarDatabase() {
// 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 SampleCalendarDatabase(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) {
System.out.println("SampleDatabase 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 "
+ DB_TABLE_NAME
+ " ( ID INTEGER IDENTITY, TITLE VARCHAR(100), "
+ "EVENTSTART DATETIME, EVENTEND DATETIME, NOTIME BOOLEAN )";
update(stmt);
for (int j = 0; j < ENTRYCOUNT; j++) {
final Timestamp start = new Timestamp(new java.util.Date()
.getTime());
start.setDate((int) ((Math.random() - 0.4) * 200));
start.setMinutes(0);
start.setHours(8 + (int) Math.random() * 12);
final Timestamp end = new Timestamp(start.getTime());
if (Math.random() < 0.7) {
long t = end.getTime();
final long hour = 60 * 60 * 1000;
t = t + hour + (Math.round(Math.random() * 3 * hour));
end.setTime(t);
}
stmt = "INSERT INTO "
+ DB_TABLE_NAME
+ "(TITLE, EVENTSTART, EVENTEND, NOTIME) VALUES ("
+ "'"
+ titles[(int) (Math.round(Math.random()
* (titles.length - 1)))] + "','" + start
+ "','" + end + "'," + (Math.random() > 0.7) + ")";
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 "
+ DB_TABLE_NAME);
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;
}
}
|
