總網頁瀏覽量

關於我自己

我的相片
人生的必修課是接受無常，人生的選修課是放下執著。

2012年1月3日 星期二

SQLiteOpenHelper ~ 旅遊匯率隨時查(一)















Activity_ExchangeRate.java
package com.tsots.ExchangeRate;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.KeyEvent;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;

public class Activity_ExchangeRate extends Activity 
{
 Context context = Activity_ExchangeRate.this;
 String Tag = "Activity_ExchangeRate.java";
 TextView tv_updatedate;
 Spinner spinner_cash;
 ListView lv_exchange;
 EditText et_cash;
 Button bt_exchange;
 
 ArrayAdapter adapter_spinner_country;
 Adapter_ListView_Exchange adapter_listview_exchange;
 java.text.SimpleDateFormat sdf;
 
 String tables[] = {"table_value", "table_listData", "table_exchangerate"};
 String fieldNames[][] =
 {
  { "fieldname_date", "fieldname_country", "fieldname_cash"},
  { "fieldname_id", "fieldname_column_country", "fieldname_column_cash", "fieldname_column_unit"},
  { "fieldname_country1_exchangerate", "fieldname_country2_exchangerate", "fieldname_country3_exchangerate", "fieldname_country4_exchangerate"}
 };
 String fieldTypes[][] =
 {
  { "text", "text", "text"},
  { "INTEGER PRIMARY KEY AUTOINCREMENT", "text", "text", "text"},
  { "text", "text", "text", "text"},
 };
 int version = 1;
 private SQLiteOpenHelper_ExchangeRate dbHelper = new SQLiteOpenHelper_ExchangeRate 
 (
   this,
   "SQLite_ExchangeRate.db",
   null,
   version,    
   tables,
   fieldNames,
   fieldTypes
 );
 
 String selected_country;
 int int_selected_country;
 String[] array_country;
 String[] array_cash;
 String[] array_unit;
 String[] array_deafault_exchangerate;
 List allarray_country = new ArrayList();
 List allarray_cash;
 List final_allarray_cash;
 List allarray_unit = new ArrayList();
 List all_id;
 DecimalFormat nf = new DecimalFormat("0.000");
 
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.layout_exchangerate);
        bt_exchange = (Button) findViewById (R.id.bt_exchange);
        et_cash = (EditText) findViewById (R.id.et_cash);
     array_country = getResources().getStringArray(R.array.array_country);
     array_cash = getResources().getStringArray(R.array.array_cash);
     array_unit = getResources().getStringArray(R.array.array_unit);
     array_deafault_exchangerate = getResources().getStringArray(R.array.array_cash);
        tv_updatedate = (TextView) findViewById (R.id.tv_updatedate);
        spinner_cash = (Spinner) findViewById (R.id.spinner_cash);
        lv_exchange = (ListView) findViewById (R.id.lv_exchange);
        
     sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

     default_sqlite_table_listdata();
     update_spinner();
     update_listview();
     
        bt_exchange.setOnClickListener(new OnClickListener()
        {
   public void onClick(View v) 
   {
    Log.i(Tag, "匯率換算ing...");
    //tv_updatedate.setText(sdf.format(new java.util.Date()));
    //擷取EditText的值, 更新allarray_cash
    System.out.println("104 et_cash.getText().toString() = "+et_cash.getText().toString());
    System.out.println("105 allarray_cash = "+ allarray_cash); 
    if(!et_cash.getText().toString().equals(""))
    {
        save_sqlite_table_value();    
     save_sqlite_table_listData();
     //save_sqlite_table_exchangerate();
     Log.i(Tag, "更新畫面資料");
     update_spinner();
     update_listview();
    }
    else
    {
     Toast.makeText(context, "請輸入正確數字", Toast.LENGTH_SHORT).show();
     allarray_cash = new ArrayList();
     for (int i=0; i<4 ; i++)
     {
      allarray_cash.add("");
     }
     save_sqlite_table_listData();
     update_listview();
    }
   }         
        });
    }

    //AP關閉前執行
 @Override
 protected void onPause() 
 {
  save_sqlite_table_value();
        save_sqlite_table_listData();
  super.onPause();
 }
 
 /*
  *  更新tables[0]
  *    TextView更新日期   Spinner所選貨幣   EditText兌換金額
  *  {"fieldname_date", "fieldname_country", "fieldname_cash"}
  */
    public void save_sqlite_table_value()
    {
     Log.i(Tag, "儲存tables[0]資料ing...");
     Cursor cursor_sqlite_table_value = null;
  try
  { 
   cursor_sqlite_table_value = dbHelper.select(tables[0], null, null, null, null, null, null);
   cursor_sqlite_table_value.moveToFirst();  
   String[] updateFields = {"fieldname_date", "fieldname_country", "fieldname_cash"};
   String[] updateValues = {tv_updatedate.getText().toString(), String.valueOf(int_selected_country), et_cash.getText().toString()};
      dbHelper.update(tables[0], updateFields, updateValues, null, null);      
  }
  catch(Exception e)
  {
   System.out.println("158 Exception : save_sqlite_table_value()");
  }
  finally
  {
   cursor_sqlite_table_value.close();
  }
    }

 /*
  *  更新tables[1]
  *   換算後各國幣值List
  *  {"fieldname_column_cash"}
  */
    public void save_sqlite_table_listData()
    {
     Log.i(Tag, "儲存tables[1]資料ing...");
     Cursor cursor_sqlite_table_listdata = null;
  try
  {   
   cursor_sqlite_table_listdata = dbHelper.select(tables[1], null, null, null, null, null, null);
      cursor_sqlite_table_listdata.moveToFirst();
      //更新資料庫
   String[] updateFields = {"fieldname_column_cash"};
   String where = "fieldname_id=?"; 
   int position = 1;
   do
   {
    if(position <= cursor_sqlite_table_listdata.getCount())
    {
     String[] updateValues = {allarray_cash.get(position-1)};
     String[] whereValue = {String.valueOf(position)};
     dbHelper.update(tables[1], updateFields, updateValues, where, whereValue);
     position++;
    }
   }while(cursor_sqlite_table_listdata.moveToNext());
  }
  catch(Exception e)
  {
   System.out.println("196 Exception : save_sqlite_table_listData()");
  }
  finally
  {
   cursor_sqlite_table_listdata.close();
  }
    }    

 /*
  *  更新tables[2]
  *      台幣        菲律賓幣        澳幣        美金 
  *  { "fieldname_country1_exchangerate", "fieldname_country2_exchangerate", "fieldname_country3_exchangerate", "fieldname_country4_exchangerate"}
  */
    public void save_sqlite_table_exchangerate()
    {
     Log.i(Tag, "儲存tables[2]資料ing...");
     Cursor cursor_sqlite_table_listdata = null;
  try
  {   
   cursor_sqlite_table_listdata = dbHelper.select(tables[2], null, null, null, null, null, null);
      cursor_sqlite_table_listdata.moveToFirst();
      //更新資料庫
   String[] updateFields = { "fieldname_country1_exchangerate", "fieldname_country2_exchangerate", "fieldname_country3_exchangerate", "fieldname_country4_exchangerate"};
   String[] updateValues = {allarray_cash.get(0), allarray_cash.get(1), allarray_cash.get(2), allarray_cash.get(3)};
   dbHelper.update(tables[2], updateFields, updateValues, null, null);
  }
  catch(Exception e)
  {
   System.out.println("224 Exception : save_sqlite_table_listData()");
  }
  finally
  {
   cursor_sqlite_table_listdata.close();
  }
    }
    
    /*
  *  更新Spinner畫面資料 
  */
    public void update_spinner()
    {
     Log.i(Tag, "設定Spinner選項");
     Cursor cursor_sqlite_table_value = null;
     //try
     //{
      cursor_sqlite_table_value = dbHelper.select(tables[0], null, null, null, null, null, null);
      cursor_sqlite_table_value.moveToFirst();
      tv_updatedate.setText(cursor_sqlite_table_value.getString(0));
      et_cash.setText(cursor_sqlite_table_value.getString(2));     
     //}
     //catch(Exception e)
     //{     
      adapter_spinner_country = new ArrayAdapter(this, android.R.layout.simple_spinner_item, array_country);
      adapter_spinner_country.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
      spinner_cash.setAdapter(adapter_spinner_country);
      //從SQLite中擷取資料, 避免spinner item又跳回預設值0
      spinner_cash.setSelection(Integer.valueOf(cursor_sqlite_table_value.getString(1)));
      spinner_cash.setOnItemSelectedListener(new Spinner.OnItemSelectedListener()
      {      
       public void onItemSelected(AdapterView arg0, View arg1, int arg2, long arg3)
       {
        int_selected_country = arg2;
        //依Spinner的選項更改, 置換ListView的幣值換算結果
        change_selected_country(int_selected_country);
        save_sqlite_table_value();
        save_sqlite_table_listData();
        //save_sqlite_table_exchangerate();
        update_listview();
       } 
    public void onNothingSelected(AdapterView arg0) 
    {
  
    }
      });   
     //}
     //finally
     //{
      cursor_sqlite_table_value.close();
     //}     
    }
    
    /*
  *  更新ListView畫面資料 
  */
    public void update_listview()
    {
     Log.i(Tag, "設定ListView內容");
     Cursor cursor_sqlite_table_listdata = null;
     try
     {
      //在此必須初使化allarray_cash, 否則ListView資料無法更新
      final_allarray_cash = new ArrayList();
      cursor_sqlite_table_listdata = dbHelper.select(tables[1], null, null, null, null, null, null);
      cursor_sqlite_table_listdata.moveToFirst();
      do
      {
       allarray_country.add(cursor_sqlite_table_listdata.getString(1));
       final_allarray_cash.add(cursor_sqlite_table_listdata.getString(2));
       allarray_unit.add(cursor_sqlite_table_listdata.getString(3));
      }while(cursor_sqlite_table_listdata.moveToNext());
     }
     catch(Exception e)
     {
      System.out.println("299 Exception : update_listview()");
     }
     finally
     {
      cursor_sqlite_table_listdata.close();
     }
     
     adapter_listview_exchange = new Adapter_ListView_Exchange
     (
      this, 
      android.R.layout.simple_list_item_1, 
      allarray_country, 
      final_allarray_cash,
      allarray_unit
     );
     lv_exchange.setAdapter(adapter_listview_exchange);    
    }
    
    /*
     *  先將SQLite欄位的預設值填好, 避免之後select時產生Exception
     */
 public void default_sqlite_table_listdata()
 {
  Log.i(Tag, "載入SQLite預設值");
  //insert tables[0]
  Cursor cursor_sqlite_table_value = null;
  try
  {
   cursor_sqlite_table_value = dbHelper.select(tables[0], null, null, null, null, null, null);
   cursor_sqlite_table_value.moveToFirst();
   String[] updateFields = {"fieldname_date", "fieldname_country", "fieldname_cash"};
   if(cursor_sqlite_table_value.getCount() == 0)
   {        
     String[] updateValues = {"2011-12", "0", "1"};
     dbHelper.insert(tables[0], updateFields, updateValues);
   }   
  }
     catch(Exception e)
     {
      System.out.println("338 Exception : default_sqlite_table_listdata()");
     }
     finally
     {
      cursor_sqlite_table_value.close();
     }
 
  //insert tables[1]
  Cursor cursor_sqlite_table_listdata = null;
  try
  {
   cursor_sqlite_table_listdata = dbHelper.select(tables[1], null, null, null, null, null, null);
   cursor_sqlite_table_listdata.moveToFirst();
   String[] updateFields = {"fieldname_column_country", "fieldname_column_cash", "fieldname_column_unit"};
   if(cursor_sqlite_table_listdata.getCount() == 0)
   {        
    for(int i=0 ; i();
   cursor_sqlite_table_exchangerate = dbHelper.select(tables[2], null, null, null, null, null, null);
   cursor_sqlite_table_exchangerate.moveToFirst();

   System.out.println("407 int_selected_country = "+int_selected_country);
      if(int_selected_country == 0)
      {
       allarray_cash.add(et_cash.getText().toString());
       allarray_cash.add(String.valueOf(nf.format(Double.valueOf(cursor_sqlite_table_exchangerate.getString(1))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(String.valueOf(nf.format(Double.valueOf(cursor_sqlite_table_exchangerate.getString(2))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(String.valueOf(nf.format(Double.valueOf(cursor_sqlite_table_exchangerate.getString(3))*Double.valueOf(et_cash.getText().toString()))));
       System.out.println("414 "+allarray_cash);       
      }
      else if(int_selected_country == 1)
      {
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(0))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(1))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(et_cash.getText().toString());
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(2))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(1))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(3))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(1))*Double.valueOf(et_cash.getText().toString()))));
       System.out.println("421 "+allarray_cash);
      }
      else if(int_selected_country == 2)
      {
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(0))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(2))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(1))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(2))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(et_cash.getText().toString());
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(3))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(2))*Double.valueOf(et_cash.getText().toString()))));
       System.out.println("430 "+allarray_cash);
      }
      else
      {
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(0))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(3))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(1))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(3))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(String.valueOf(nf.format(Double.parseDouble(cursor_sqlite_table_exchangerate.getString(2))/Double.parseDouble(cursor_sqlite_table_exchangerate.getString(3))*Double.valueOf(et_cash.getText().toString()))));
       allarray_cash.add(et_cash.getText().toString());
       System.out.println("438 "+allarray_cash);
      }
  }
  catch(Exception e)
  {
   System.out.println("443 Exception : change_selected_country(int int_selected_country)");
  }
  finally
  {
   cursor_sqlite_table_exchangerate.close();
  }
    }
}
////


Adapter_ListView_Exchange.java
package com.tsots.ExchangeRate;

import java.util.List;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;

public class Adapter_ListView_Exchange extends BaseAdapter
{
 private LayoutInflater mInflater;
 List adapter_allarray_country;
 List adapter_allarray_cash;
 List adapter_allarray_unit;
 TextView column_country;
 TextView column_cash;
 TextView column_unit;

 public Adapter_ListView_Exchange
 (
   Context context,
   int simple_list_item_single_choice, 
   List allarray_country,
   List allarray_cash, 
   List allarray_unit
 ) 
 {
  mInflater = LayoutInflater.from(context);
  adapter_allarray_country = allarray_country;
  adapter_allarray_cash = allarray_cash;
  adapter_allarray_unit = allarray_unit;
 }

 public int getCount()
 {
  return adapter_allarray_cash.size();
 }

 public Object getItem(int position)
 {
  return adapter_allarray_cash.get(position);
 }
  
 public long getItemId(int position)
 {
  return position;
 }
  
 public View getView(int position,View convertView,ViewGroup parent)
 {
     convertView = mInflater.inflate(R.layout.layout_adapter_listview_exchange,null);
     column_country = (TextView)convertView.findViewById(R.id.column_country);
     column_cash = (TextView)convertView.findViewById(R.id.column_cash);
     column_unit = (TextView)convertView.findViewById(R.id.column_unit);
     column_country.setText(adapter_allarray_country.get(position).toString());
     column_cash.setText(adapter_allarray_cash.get(position).toString());
     column_unit.setText(adapter_allarray_unit.get(position).toString());
     return convertView;
 }
}


SQLiteOpenHelper_ExchangeRate.java
package com.tsots.ExchangeRate;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class SQLiteOpenHelper_ExchangeRate extends SQLiteOpenHelper

{

  public String TableNames[];

  public String FieldNames[][];

  public String FieldTypes[][];

  public static String NO_CREATE_TABLES = "no tables";

  private String message = "";

  

  public SQLiteOpenHelper_ExchangeRate(Context context, String dbname, CursorFactory factory, int version, String tableNames[], String fieldNames[][], String fieldTypes[][])

  {

    super(context, dbname, factory, version);

    TableNames = tableNames;

    FieldNames = fieldNames;

    FieldTypes = fieldTypes;

  }



  @Override

  public void onCreate(SQLiteDatabase db)

  {

    if (TableNames == null)

    {

      message = NO_CREATE_TABLES;

      return;

    }



    for (int i = 0; i < TableNames.length; i++)

    {

      String sql = "CREATE TABLE " + TableNames[i] + " (";

      for (int j = 0; j < FieldNames[i].length; j++)

      {

        sql += FieldNames[i][j] + " " + FieldTypes[i][j] + ",";

      }

      sql = sql.substring(0, sql.length() - 1);

      sql += ")";

      db.execSQL(sql);

    }

  }



  @Override

  public void onUpgrade(SQLiteDatabase db, int arg1, int arg2)

  {

    for (int i = 0; i < TableNames[i].length(); i++)

    {

      String sql = "DROP TABLE IF EXISTS " + TableNames[i];

      db.execSQL(sql);

    }

    onCreate(db);

  }



  public void execSQL(String sql) throws java.sql.SQLException

  {

    SQLiteDatabase db = this.getWritableDatabase();

    db.execSQL(sql);

  }



  public Cursor select(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

  {

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);

    return cursor;

  }



  public long insert(String table, String fields[], String values[])

  {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues cv = new ContentValues();

    for (int i = 0; i < fields.length; i++)

    {

      cv.put(fields[i], values[i]);

    }

    return db.insert(table, null, cv);

  }



  public int delete(String table, String where, String[] whereValue)

  {

    SQLiteDatabase db = this.getWritableDatabase();



    return db.delete(table, where, whereValue);

  }



  public int update(String table, String updateFields[],

      String updateValues[], String where, String[] whereValue)

  {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues cv = new ContentValues();

    for (int i = 0; i < updateFields.length; i++)

    {

      cv.put(updateFields[i], updateValues[i]);

    }

    return db.update(table, cv, where, whereValue);

  }



  public String getMessage()

  {

    return message;

  }



  @Override

  public synchronized void close()

  {

    // TODO Auto-generated method stub

    super.close();

  }

}
張貼者： 於
標籤： , ,

3 則留言:

  1. Unknown2016年6月19日 凌晨12:10

    你好
    最近在學習APP程式
    有看到你的BLOG

    這篇SQLiteOpenHelper ~ 旅遊匯率隨時查(一)

    但是遇到了一些問題 可以請問?

    回覆刪除
  2. skytear-tsots2016年6月19日 晚上7:10

    遇到甚麼問題？可以問喔~

    回覆刪除

訂閱： 張貼留言 (Atom)