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.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) { }
                        }
                }
        }

        private void loadData(String data)
        {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                db.beginTransaction();
                db.execSQL("DELETE FROM people");
                StringTokenizer lines = new StringTokenizer( data, "|" );

                SQLiteStatement stmt = db.compileStatement("INSERT INTO people (name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?);");
                while (lines.hasMoreTokens())
                {  
                        String line = lines.nextToken(); 

                        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);
                        }

                }
                stmt.close();
                db.setTransactionSuccessful();
                db.endTransaction();
                db.close();

        }

        public void loadData() throws Exception
        {
                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);
                StringBuilder sb = new StringBuilder(1024000);
                String line;
                
                while ( (line = input.readLine()) != null) {
                        sb.append(line);
                        sb.append("\n");
                }
                
                is.close();
                Log.i("PeopleDatabase", "Parsing data");

                loadData(sb.toString());

                Log.i("PeopleDatabase", "Parsing finished");
        }
        
        public void close()
        {
                dbHelper.close();
        }
        

}
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	}