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
|
/*
* Copyright (C) 2007 The Android Open Source Project
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package tests.sql;
import dalvik.annotation.TestTargetClass;
import junit.framework.TestCase;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@TestTargetClass(Statement.class)
public class SQLTest extends TestCase {
static Connection conn;
@Override
public void setUp() throws Exception {
getSQLiteConnection();
createZoo();
}
protected File dbFile;
protected void getSQLiteConnection() throws Exception {
String tmp = System.getProperty("java.io.tmpdir");
assertEquals(tmp, System.getProperty("java.io.tmpdir"));
File tmpDir = new File(tmp);
if (tmpDir.isDirectory()) {
dbFile = File.createTempFile("sqliteTest", ".db", tmpDir);
dbFile.deleteOnExit();
} else {
System.err.println("java.io.tmpdir does not exist");
}
Class.forName("SQLite.JDBCDriver").newInstance();
conn = DriverManager.getConnection("jdbc:sqlite:/" + dbFile.getPath());
assertNotNull("Connection created ", conn);
}
@Override
public void tearDown() {
Statement st = null;
try {
if (! conn.isClosed()) {
st = conn.createStatement();
st.execute("drop table if exists zoo");
}
} catch (SQLException e) {
fail("Couldn't drop table: " + e.getMessage());
} finally {
try {
if (st != null) {
st.close();
conn.close();
}
} catch(SQLException ee) {
//ignore
}
}
}
public void createZoo() {
String[] queries = {
"create table zoo(id smallint, name varchar(10), family varchar(10))",
"insert into zoo values (1, 'Kesha', 'parrot')",
"insert into zoo values (2, 'Yasha', 'sparrow')" };
Statement st = null;
try {
st = conn.createStatement();
for (int i = 0; i < queries.length; i++) {
st.execute(queries[i]);
}
} catch (SQLException e) {
e.printStackTrace();
fail("Unexpected exception: " + e.getMessage());
} finally {
try {
if (st != null) {
st.close();
}
} catch (SQLException ee) {}
}
}
public void createProcedure() {
String proc = "CREATE PROCEDURE welcomeAnimal (IN parameter1 integer, IN parameter2 char(20), IN parameter3 char(20)) "
+ " BEGIN "
+ " INSERT INTO zoo(id, name, family) VALUES (parameter1, parameter2, parameter3); "
+ "SELECT * FROM zoo;" + " END;";
Statement st = null;
try {
st = conn.createStatement();
st.execute("DROP PROCEDURE IF EXISTS welcomeAnimal");
st.execute(proc);
} catch (SQLException e) {
fail("Unexpected exception: " + e.getMessage());
} finally {
try {
st.close();
} catch (SQLException ee) {}
}
}
public int getCount(ResultSet rs) {
int count = 0;
try {
while (rs.next()) {
count++;
}
} catch (SQLException e) {
fail("SQLException is thrown");
}
return count;
}
}
|
