1 |
package com.grundfos.android.people;
|
2 |
|
3 |
import java.io.BufferedReader;
|
4 |
import java.io.InputStream;
|
5 |
import java.io.InputStreamReader;
|
6 |
import java.net.URL;
|
7 |
import java.net.URLConnection;
|
8 |
import java.util.StringTokenizer;
|
9 |
|
10 |
import android.content.Context;
|
11 |
import android.database.Cursor;
|
12 |
import android.database.SQLException;
|
13 |
import android.database.sqlite.SQLiteDatabase;
|
14 |
import android.database.sqlite.SQLiteOpenHelper;
|
15 |
import android.database.sqlite.SQLiteStatement;
|
16 |
import android.util.Log;
|
17 |
|
18 |
public class PeopleDatabase {
|
19 |
|
20 |
class PeopleDatabaseHelper extends SQLiteOpenHelper
|
21 |
{
|
22 |
/** The name of the database file on the file system */
|
23 |
private static final String DATABASE_NAME = "People";
|
24 |
/** The version of the database that this class understands. */
|
25 |
private static final int DATABASE_VERSION = 5;
|
26 |
|
27 |
public PeopleDatabaseHelper(Context context)
|
28 |
{
|
29 |
super(context, DATABASE_NAME, null, DATABASE_VERSION);
|
30 |
}
|
31 |
|
32 |
@Override
|
33 |
public void onCreate(SQLiteDatabase db) {
|
34 |
db.beginTransaction();
|
35 |
try {
|
36 |
db.execSQL("CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, inits TEXT, title TEXT, dept TEXT, company TEXT, phone TEXT, cell TEXT, email TEXT)");
|
37 |
db.setTransactionSuccessful();
|
38 |
} catch (SQLException e) {
|
39 |
Log.e("Error creating tables and debug data", e.toString());
|
40 |
} finally {
|
41 |
db.endTransaction();
|
42 |
}
|
43 |
}
|
44 |
|
45 |
@Override
|
46 |
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
|
47 |
db.beginTransaction();
|
48 |
try {
|
49 |
db.execSQL("DROP TABLE people");
|
50 |
db.setTransactionSuccessful();
|
51 |
} catch (SQLException e) {
|
52 |
Log.e("Error creating tables and debug data", e.toString());
|
53 |
} finally {
|
54 |
db.endTransaction();
|
55 |
}
|
56 |
onCreate(db);
|
57 |
}
|
58 |
}
|
59 |
|
60 |
PeopleDatabaseHelper dbHelper;
|
61 |
|
62 |
public PeopleDatabase(Context context) {
|
63 |
dbHelper = new PeopleDatabaseHelper(context);
|
64 |
}
|
65 |
|
66 |
|
67 |
public Cursor getPeopleList(String search) {
|
68 |
search = search.replace(' ', '%');
|
69 |
search = "%" + search + "%";
|
70 |
|
71 |
String sql = "SELECT id AS _id, name, inits, company FROM people WHERE name LIKE ? ORDER BY name LIMIT 200";
|
72 |
SQLiteDatabase d = dbHelper.getReadableDatabase();
|
73 |
|
74 |
try {
|
75 |
String[] args = {search};
|
76 |
Cursor c = d.rawQuery(sql, args);
|
77 |
return c;
|
78 |
} catch (Exception e) {
|
79 |
Log.e("people", "-", e);
|
80 |
}
|
81 |
|
82 |
return null;
|
83 |
}
|
84 |
|
85 |
public PeopleBean getPeople(long id) {
|
86 |
String sql = "SELECT id, name, inits, title, dept, company,phone,cell,email FROM people WHERE id = " + id + "";
|
87 |
SQLiteDatabase d = dbHelper.getReadableDatabase();
|
88 |
|
89 |
try {
|
90 |
Cursor c = d.rawQuery(sql, null);
|
91 |
|
92 |
c.moveToNext();
|
93 |
PeopleBean people = new PeopleBean();
|
94 |
people.id = c.getInt(0);
|
95 |
people.name = c.getString(1);
|
96 |
people.inits = c.getString(2);
|
97 |
people.title = c.getString(3);
|
98 |
people.dept = c.getString(4);
|
99 |
people.company = c.getString(5);
|
100 |
people.phone = c.getString(6);
|
101 |
people.cell = c.getString(7);
|
102 |
people.email = c.getString(8);
|
103 |
|
104 |
c.close();
|
105 |
d.close();
|
106 |
return people;
|
107 |
} catch (Exception e) {
|
108 |
Log.e("people", "-", e);
|
109 |
}
|
110 |
|
111 |
return null;
|
112 |
}
|
113 |
|
114 |
/** Returns the number of Jobs */
|
115 |
public int getPeopleCount(){
|
116 |
|
117 |
Cursor c = null;
|
118 |
try {
|
119 |
SQLiteDatabase d = dbHelper.getReadableDatabase();
|
120 |
c = d.rawQuery("SELECT count(*) FROM people", null);
|
121 |
if (0 >= c.getCount()) { return 0; }
|
122 |
c.moveToFirst();
|
123 |
int count = c.getInt(0);
|
124 |
|
125 |
c.close();
|
126 |
d.close();
|
127 |
|
128 |
return count;
|
129 |
}
|
130 |
finally {
|
131 |
if (null != c) {
|
132 |
try { c.close(); }
|
133 |
catch (SQLException e) { }
|
134 |
}
|
135 |
}
|
136 |
}
|
137 |
|
138 |
private void loadData(String data)
|
139 |
{
|
140 |
SQLiteDatabase db = dbHelper.getWritableDatabase();
|
141 |
db.beginTransaction();
|
142 |
db.execSQL("DELETE FROM people");
|
143 |
StringTokenizer lines = new StringTokenizer( data, "|" );
|
144 |
|
145 |
SQLiteStatement stmt = db.compileStatement("INSERT INTO people (name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?);");
|
146 |
while (lines.hasMoreTokens())
|
147 |
{
|
148 |
String line = lines.nextToken();
|
149 |
|
150 |
try
|
151 |
{
|
152 |
StringTokenizer fields = new StringTokenizer(line, ";");
|
153 |
stmt.bindString(1, fields.nextToken().trim() );
|
154 |
stmt.bindString(2, fields.nextToken().trim() );
|
155 |
stmt.bindString(3, fields.nextToken().trim() );
|
156 |
stmt.bindString(4, fields.nextToken().trim() );
|
157 |
stmt.bindString(5, fields.nextToken().trim() );
|
158 |
stmt.bindString(6, fields.nextToken().trim() );
|
159 |
stmt.bindString(7, fields.nextToken().trim() );
|
160 |
stmt.bindString(8, fields.nextToken().trim() );
|
161 |
stmt.execute();
|
162 |
} catch (Exception e) {
|
163 |
Log.e("PeopleDataBase", "Token error: " + line, e);
|
164 |
}
|
165 |
|
166 |
}
|
167 |
stmt.close();
|
168 |
db.setTransactionSuccessful();
|
169 |
db.endTransaction();
|
170 |
db.close();
|
171 |
|
172 |
}
|
173 |
|
174 |
public void loadData() throws Exception
|
175 |
{
|
176 |
URL url = new URL("http://t-hoerup.dk/peopledata.txt");
|
177 |
|
178 |
URLConnection conn = url.openConnection();
|
179 |
conn.setConnectTimeout(1000);
|
180 |
InputStream is = conn.getInputStream();
|
181 |
|
182 |
BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768);
|
183 |
StringBuilder sb = new StringBuilder(1024000);
|
184 |
String line;
|
185 |
|
186 |
while ( (line = input.readLine()) != null) {
|
187 |
sb.append(line);
|
188 |
sb.append("\n");
|
189 |
}
|
190 |
|
191 |
is.close();
|
192 |
Log.i("PeopleDatabase", "Parsing data");
|
193 |
|
194 |
loadData(sb.toString());
|
195 |
|
196 |
Log.i("PeopleDatabase", "Parsing finished");
|
197 |
}
|
198 |
|
199 |
public void close()
|
200 |
{
|
201 |
dbHelper.close();
|
202 |
}
|
203 |
|
204 |
|
205 |
}
|