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