1 |
torben |
228 |
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 |
|
|
}
|