android/people/PeopleDatabase.java

package com.grundfos.android.people;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.util.StringTokenizer;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.Handler;
import android.os.Message;
import android.util.Log;

public class PeopleDatabase  {

        class PeopleDatabaseHelper extends SQLiteOpenHelper
        {
                /** The name of the database file on the file system */
                private static final String DATABASE_NAME = "People";
                /** The version of the database that this class understands. */
                private static final int DATABASE_VERSION = 5;

                public PeopleDatabaseHelper(Context context)
                {
                        super(context, DATABASE_NAME, null, DATABASE_VERSION);  
                }

                @Override
                public void onCreate(SQLiteDatabase db) {
                        db.beginTransaction();
                        try {
                                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)");
                                db.setTransactionSuccessful();
                        } catch (SQLException e) {
                                Log.e("Error creating tables and debug data", e.toString());
                        } finally {
                                db.endTransaction();
                        }
                }

                @Override
                public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                        db.beginTransaction();
                        try {
                                db.execSQL("DROP TABLE people");
                                db.setTransactionSuccessful();
                        } catch (SQLException e) {
                                Log.e("Error creating tables and debug data", e.toString());
                        } finally {
                                db.endTransaction();
                        }
                        onCreate(db);
                }
        }

        PeopleDatabaseHelper dbHelper;

        public PeopleDatabase(Context context) {
                dbHelper = new PeopleDatabaseHelper(context);
        }


        public Cursor getPeopleList(String search) {
                search = search.replace(' ', '%');              
                search = "%" + search + "%";

                String sql = "SELECT id AS _id, name, inits, company FROM people WHERE name LIKE ? ORDER BY name LIMIT 200";
                SQLiteDatabase d = dbHelper.getReadableDatabase();

                try {
                        String[] args = {search};
                        Cursor c = d.rawQuery(sql, args);
                        return c;
                } catch (Exception e) {
                        Log.e("people", "-", e);
                }

                return null;
        }

        public PeopleBean getPeople(long  id) {
                String sql = "SELECT id, name, inits, title, dept, company,phone,cell,email FROM people WHERE id = " + id + "";
                SQLiteDatabase d = dbHelper.getReadableDatabase();

                try {
                        Cursor c = d.rawQuery(sql, null);

                        c.moveToNext();
                        PeopleBean people = new PeopleBean();
                        people.id = c.getInt(0);
                        people.name = c.getString(1);
                        people.inits = c.getString(2);
                        people.title = c.getString(3);
                        people.dept = c.getString(4);
                        people.company = c.getString(5);
                        people.phone = c.getString(6);
                        people.cell = c.getString(7);
                        people.email = c.getString(8);

                        c.close();
                        d.close();
                        return people;
                } catch (Exception e) {
                        Log.e("people", "-", e);
                }

                return null;
        }

        /** Returns the number of Jobs */
        public int getPeopleCount(){

                Cursor c = null;
                try {
                        SQLiteDatabase d = dbHelper.getReadableDatabase(); 
                        c = d.rawQuery("SELECT count(*) FROM people", null);
                        if (0 >= c.getCount()) { return 0; }
                        c.moveToFirst();
                        int count = c.getInt(0);

                        c.close();
                        d.close();

                        return count;
                }
                finally {
                        if (null != c) {
                                try { c.close(); }
                                catch (SQLException e) { }
                        }
                }
        }


        class DataLoader
        {
                SQLiteDatabase db; 
                Handler handler;
                int count = 0;
                int maxCount = 30000;
                SQLiteStatement stmt;

                DataLoader(Handler hndl) {
                        handler = hndl;
                }

                private void sendCount() {
                        Message msg = Message.obtain();
                        msg.what = 1;
                        msg.arg1 = count;
                        handler.sendMessage(msg);
                }               
                
                private void sendMaxCount()
                {
                        Message msg = Message.obtain();
                        msg.what = 0;
                        msg.arg1 = maxCount;
                        handler.sendMessage(msg);                       
                }

                private void insertLine(String line)
                {
                        try
                        {
                                StringTokenizer fields = new StringTokenizer(line, ";");
                                stmt.bindString(1, fields.nextToken().trim() );
                                stmt.bindString(2, fields.nextToken().trim() );
                                stmt.bindString(3, fields.nextToken().trim() );
                                stmt.bindString(4, fields.nextToken().trim() );
                                stmt.bindString(5, fields.nextToken().trim() );
                                stmt.bindString(6, fields.nextToken().trim() );
                                stmt.bindString(7, fields.nextToken().trim() );                         
                                stmt.bindString(8, fields.nextToken().trim() );
                                stmt.execute();
                        } catch (Exception e) {
                                Log.e("PeopleDataBase", "Token error: " + line, e);
                        }
                }

                public void loadData() throws Exception
                {
                        try
                        {
                                db = dbHelper.getWritableDatabase();
                                db.beginTransaction();
                                db.execSQL("DELETE FROM people");                       
                                stmt = db.compileStatement("INSERT INTO people (name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?);");

                                URL url = new URL("http://t-hoerup.dk/peopledata.txt");

                                URLConnection conn = url.openConnection();
                                conn.setConnectTimeout(1000);                   
                                InputStream is = conn.getInputStream();

                                BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768);
                                String line;


                                sendMaxCount();
                                while ( (line = input.readLine()) != null) {
                                        if (line.charAt(0) == '#') { //Lines starting with # are comments and should not be parsed by loaddata worker func.
                                                if (line.contains("count:")) {
                                                        maxCount = Integer.parseInt(line.substring(line.indexOf(':')+1).trim());
                                                        sendMaxCount();
                                                }
                                                Log.i("PeopleDB", "Count:"+maxCount);

                                        } else {
                                                count ++;
                                                if ((count % 100) == 0)
                                                        sendCount();
                                                insertLine(line);
                                        }
                                }                       
                                is.close();
                                db.setTransactionSuccessful();
                        } finally {
                                stmt.close();
                                db.endTransaction();
                                db.close();
                        }

                }                       
        }


        public void loadData(Handler hndl) throws Exception
        {
                DataLoader dl = new DataLoader(hndl);
                dl.loadData();
        }

        public void close()
        {
                dbHelper.close();
        }


}
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	torben	230	import android.os.Handler;
17			import android.os.Message;
18	torben	228	import android.util.Log;
19
20			public class PeopleDatabase {
21
22			class PeopleDatabaseHelper extends SQLiteOpenHelper
23			{
24			/** The name of the database file on the file system */
25			private static final String DATABASE_NAME = "People";
26			/** The version of the database that this class understands. */
27			private static final int DATABASE_VERSION = 5;
28
29			public PeopleDatabaseHelper(Context context)
30			{
31			super(context, DATABASE_NAME, null, DATABASE_VERSION);
32			}
33	torben	230
34	torben	228	@Override
35			public void onCreate(SQLiteDatabase db) {
36			db.beginTransaction();
37			try {
38			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)");
39			db.setTransactionSuccessful();
40			} catch (SQLException e) {
41			Log.e("Error creating tables and debug data", e.toString());
42			} finally {
43			db.endTransaction();
44			}
45			}
46
47			@Override
48			public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
49			db.beginTransaction();
50			try {
51			db.execSQL("DROP TABLE people");
52			db.setTransactionSuccessful();
53			} catch (SQLException e) {
54			Log.e("Error creating tables and debug data", e.toString());
55			} finally {
56			db.endTransaction();
57			}
58			onCreate(db);
59			}
60			}
61
62			PeopleDatabaseHelper dbHelper;
63	torben	230
64	torben	228	public PeopleDatabase(Context context) {
65			dbHelper = new PeopleDatabaseHelper(context);
66			}
67
68
69			public Cursor getPeopleList(String search) {
70			search = search.replace(' ', '%');
71			search = "%" + search + "%";
72
73			String sql = "SELECT id AS _id, name, inits, company FROM people WHERE name LIKE ? ORDER BY name LIMIT 200";
74			SQLiteDatabase d = dbHelper.getReadableDatabase();
75
76			try {
77			String[] args = {search};
78			Cursor c = d.rawQuery(sql, args);
79			return c;
80			} catch (Exception e) {
81			Log.e("people", "-", e);
82			}
83
84			return null;
85			}
86
87			public PeopleBean getPeople(long id) {
88			String sql = "SELECT id, name, inits, title, dept, company,phone,cell,email FROM people WHERE id = " + id + "";
89			SQLiteDatabase d = dbHelper.getReadableDatabase();
90
91			try {
92			Cursor c = d.rawQuery(sql, null);
93
94			c.moveToNext();
95			PeopleBean people = new PeopleBean();
96			people.id = c.getInt(0);
97			people.name = c.getString(1);
98			people.inits = c.getString(2);
99			people.title = c.getString(3);
100			people.dept = c.getString(4);
101			people.company = c.getString(5);
102			people.phone = c.getString(6);
103			people.cell = c.getString(7);
104			people.email = c.getString(8);
105
106			c.close();
107			d.close();
108			return people;
109			} catch (Exception e) {
110			Log.e("people", "-", e);
111			}
112
113			return null;
114			}
115
116			/** Returns the number of Jobs */
117			public int getPeopleCount(){
118
119			Cursor c = null;
120			try {
121			SQLiteDatabase d = dbHelper.getReadableDatabase();
122			c = d.rawQuery("SELECT count(*) FROM people", null);
123			if (0 >= c.getCount()) { return 0; }
124			c.moveToFirst();
125			int count = c.getInt(0);
126
127			c.close();
128			d.close();
129
130			return count;
131			}
132			finally {
133			if (null != c) {
134			try { c.close(); }
135			catch (SQLException e) { }
136			}
137			}
138			}
139
140	torben	230
141			class DataLoader
142	torben	228	{
143	torben	230	SQLiteDatabase db;
144			Handler handler;
145			int count = 0;
146			int maxCount = 30000;
147			SQLiteStatement stmt;
148	torben	228
149	torben	230	DataLoader(Handler hndl) {
150			handler = hndl;
151			}
152	torben	228
153	torben	230	private void sendCount() {
154			Message msg = Message.obtain();
155			msg.what = 1;
156			msg.arg1 = count;
157			handler.sendMessage(msg);
158			}
159
160			private void sendMaxCount()
161			{
162			Message msg = Message.obtain();
163			msg.what = 0;
164			msg.arg1 = maxCount;
165			handler.sendMessage(msg);
166			}
167
168			private void insertLine(String line)
169			{
170	torben	228	try
171			{
172			StringTokenizer fields = new StringTokenizer(line, ";");
173			stmt.bindString(1, fields.nextToken().trim() );
174			stmt.bindString(2, fields.nextToken().trim() );
175			stmt.bindString(3, fields.nextToken().trim() );
176			stmt.bindString(4, fields.nextToken().trim() );
177			stmt.bindString(5, fields.nextToken().trim() );
178			stmt.bindString(6, fields.nextToken().trim() );
179			stmt.bindString(7, fields.nextToken().trim() );
180			stmt.bindString(8, fields.nextToken().trim() );
181			stmt.execute();
182			} catch (Exception e) {
183			Log.e("PeopleDataBase", "Token error: " + line, e);
184			}
185			}
186
187	torben	230	public void loadData() throws Exception
188			{
189			try
190			{
191			db = dbHelper.getWritableDatabase();
192			db.beginTransaction();
193			db.execSQL("DELETE FROM people");
194			stmt = db.compileStatement("INSERT INTO people (name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?);");
195	torben	228
196	torben	230	URL url = new URL("http://t-hoerup.dk/peopledata.txt");
197	torben	228
198	torben	230	URLConnection conn = url.openConnection();
199			conn.setConnectTimeout(1000);
200			InputStream is = conn.getInputStream();
201	torben	228
202	torben	230	BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768);
203			String line;
204	torben	228
205
206	torben	230	sendMaxCount();
207			while ( (line = input.readLine()) != null) {
208			if (line.charAt(0) == '#') { //Lines starting with # are comments and should not be parsed by loaddata worker func.
209			if (line.contains("count:")) {
210			maxCount = Integer.parseInt(line.substring(line.indexOf(':')+1).trim());
211			sendMaxCount();
212			}
213			Log.i("PeopleDB", "Count:"+maxCount);
214
215			} else {
216			count ++;
217			if ((count % 100) == 0)
218			sendCount();
219			insertLine(line);
220			}
221			}
222			is.close();
223			db.setTransactionSuccessful();
224			} finally {
225			stmt.close();
226			db.endTransaction();
227			db.close();
228			}
229
230			}
231	torben	228	}
232	torben	230
233
234
235			public void loadData(Handler hndl) throws Exception
236			{
237			DataLoader dl = new DataLoader(hndl);
238			dl.loadData();
239			}
240
241	torben	228	public void close()
242			{
243			dbHelper.close();
244			}
245
246	torben	230
247	torben	228	}