PowenKo > Android > DB > SQLite sample

February 25, 2012   ·   0 Comments

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;
  }
}
shareShare on FacebookShare on Google+Share on LinkedInEmail this to someonePrint this pageBuffer this pageDigg thisFlattr the authorShare on RedditPin on PinterestShare on StumbleUponShare on TumblrTweet about this on Twitter

By


Readers Comments (0)


You must be logged in to post a comment.

PowenKo’s Online Store

powenko_arduinoBook
Android7

Amazon

adsbygoogle

Recent Posts

Categories

Amazon