sample code:Tutorial_DB_SQLiteActivity
databse on
assets\powenbko.sqlite
package com.powenko;
import java.io.IOException;
import java.util.List;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
public class Tutorial_DB_SQLiteActivity extends Activity {
private ListView myListView;
private DBClass mDBClass;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
myListView = (ListView) this.findViewById(R.id.myListView);
Fun_open();
}
public void Fun_open(){
// Save data to DB
mDBClass = new DBClass(this);
try {
mDBClass.createDataBase();
mDBClass.insert("title","info");
Cursor tCursor=mDBClass.select();
tCursor=mDBClass.doSQL("select * from powenkotable where title='powenko' ");
List<News> t_List=mDBClass.FunGetNewsInfo(tCursor);
int tsize=t_List.size(); //t_List.size();
// tsize=tsize;
DBClassMyAdapter t_DBClassMyAdapter=new DBClassMyAdapter(this,t_List );
/////////
/* new SimpleCursorAdapter並將myCursor傳入，顯示資料的欄位為todo_text */
/*
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.list,
tCursor, new String[]
{ "info","title" }, new int[]
{ R.id.listTextView1,R.id.listTextView2 });
*/
myListView.setAdapter(t_DBClassMyAdapter);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.powenko;
public class News
{
public String _title="";
public String _info="";
}
package com.powenko;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
//public class exchangeRateDB extends SQLiteOpenHelper
public class DBClass extends SQLiteOpenHelper
{
private List<News> li=new ArrayList<News>();
private final static String DATABASE_NAME = "powenko.sqlite";
//////////////////////////////////////////
//The Android's default system path of your application database.
private static String DB_PATH = "/data/data/com.powenko/databases/";
private static String DB_NAME = DATABASE_NAME; //"myDBName";
private SQLiteDatabase myDataBase;
private Context myContext;
/////////////////////////////////////////
private final static int DATABASE_VERSION = 1;
//private final static String TABLE_NAME = "exchangeRate_table";
public String TABLE_NAME = "powenkotable";
public DBClass(Context context) {
//super(context);
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
this.myContext = context;
}
@Override
public void onCreate(SQLiteDatabase db)
{
/*
String sql = "CREATE TABLE " + TABLE_NAME + " (" + FIELD_id
+ " INTEGER primary key autoincrement, " + " " +
FIELD_TEXT + " text, " + " " +
FIELD_lastBuildDate + " text , " +FIELD_sort + " INTEGER "+
")";
db.execSQL(sql);
*/
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
db.execSQL(sql);
onCreate(db);
}
public Cursor doSQL(String i_sql)
{
SQLiteDatabase sqliteDB = this.getReadableDatabase();
Cursor cursor = sqliteDB.rawQuery(i_sql, null);
return cursor;
/*
SQLiteDatabase db = this.getReadableDatabase();
// Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, FIELD_sort);
Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
return cursor;
*/
}
public Cursor select()
{
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
return cursor;
}
/*
public void delete(int id)
{
SQLiteDatabase db = this.getWritableDatabase();
String where = FIELD_id + " = ?";
String[] whereValue =
{ Integer.toString(id) };
db.delete(TABLE_NAME, where, whereValue);
}
*/
/*
public void update(int id,
String text,
String textdynamicRate,
String textlastBuildDate,
int intFIELD_sort)
{
SQLiteDatabase db = this.getWritableDatabase();
String where = FIELD_id + " = ?";
String[] whereValue =
{ Integer.toString(id) };
ContentValues cv = new ContentValues();
cv.put(FIELD_TEXT, text);
cv.put(FIELD_dynamicRate , textdynamicRate);
cv.put(FIELD_sort, intFIELD_sort);
db.update(TABLE_NAME, cv, where, whereValue);
}*/
public void onDropTable()
{
SQLiteDatabase db = this.getWritableDatabase();
String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
db.execSQL(sql);
onCreate(db);
}
public void setTableName(String iTableName)
{
//super(context, DATABASE_NAME, null, DATABASE_VERSION);
TABLE_NAME=iTableName;
}
/////////////////////////////////////////////////////
public void createDataBase() throws IOException{
boolean dbExist = checkDataBase();
if(dbExist){
//do nothing - database already exist
}else{
this.getReadableDatabase();
try {
copyDataBase();
dbExist = checkDataBase();
} catch (IOException e)
{
throw new Error("Error copying database");
}
}
}
/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn't
*/
private boolean checkDataBase(){
SQLiteDatabase checkDB = null;
try{
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}catch(SQLiteException e){
//database does't exist yet.
}
if(checkDB != null){
checkDB.close();
}
return checkDB != null ? true : false;
}
/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException{
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException{
//Open the database
String myPath = DB_PATH + DB_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
//SQLiteDatabase.OPEN_READONLY);
}
@Override
public synchronized void close() {
if(myDataBase != null)
myDataBase.close();
super.close();
}
public long insert(String title,String info)
{
SQLiteDatabase db = this.getWritableDatabase();
/* 將新增的值放入ContentValues
願景派
來臨 海凌
*/
ContentValues cv = new ContentValues();
cv.put("info", info);
cv.put("title", title);
long row = db.insert(TABLE_NAME, null, cv);
return row;
}
public List<News> FunGetNewsInfo(Cursor myCursor)
{
List<News> data2=new ArrayList<News>();
String a1="";
if (myCursor.moveToFirst())
{
data2 = new ArrayList<News>();
do
{
News news=new News();
news._title=myCursor.getString(myCursor.getColumnIndex("title"));
news._info=myCursor.getString(myCursor.getColumnIndex("info"));
data2.add(news);
}while(myCursor.moveToNext() );
}
myCursor.close();
return data2;
}
}
package com.powenko;
import java.util.List;
import android.content.Context;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ImageView;
import android.widget.TextView;
/* �۩w�q��Adapter�A�~��android.widget.BaseAdapter */
public class DBClassMyAdapter extends BaseAdapter
{
/* �ܼƫŧi */
public LayoutInflater mInflater;
public List<News> items;
/* MyAdapter���غc�l�A�ǤJ��ӰѼ� */
public DBClassMyAdapter(Context context,List<News> it)
{
/* �Ѽƪ�l�� */
mInflater = LayoutInflater.from(context);
items = it;
}
/* ÔøΩ]ÔøΩ~ÔøΩÔøΩBaseAdapterÔøΩAÔøΩÔøΩÔøΩ–ºgÔøΩHÔøΩUmethod */
// @Override
@Override
public int getCount()
{
return items.size();
}
// @Override
@Override
public Object getItem(int position)
{
return items.get(position);
}
// @Override
@Override
public long getItemId(int position)
{
return position;
}
// @Override
@Override
public View getView(int position,View convertView,ViewGroup par)
{
ViewHolder holder;
String a1="";
if(convertView == null)
{
/* �ϥΦ۩w�q��news_row�@��Layout */
convertView = mInflater.inflate(R.layout.list, null);
/* ÔøΩÔøΩlÔøΩÔøΩholderÔøΩÔøΩtextÔøΩPicon */
holder = new ViewHolder();
holder.text = (TextView) convertView.findViewById(R.id.listTextView1);
holder.text2 = (TextView) convertView.findViewById(R.id.listTextView2);
convertView.setTag(holder);
}
else
{
holder = (ViewHolder) convertView.getTag();
}
News tmpN=items.get(position);
holder.text.setText(tmpN._title ); //"港幣(HKD) Hong Kong Dollars");//tmpN.getTitle());
holder.text2.setText(tmpN._info );// getdynamicRate());
return convertView;
}
/* class ViewHolder */
private class ViewHolder
{
TextView text;
TextView text2;
TextView text3;
TextView text4;
TextView text5;
TextView text6;
TextView text7;
TextView text8;
ImageView icon;
}
}