SQLite进阶：Android上的SQLite常用操作

昨天简单地做一了一个列表展示，现在将增加/修改/删除方法都给加上了，本次增加的内容比较多，比如常见按钮的事件，列表长按事件，Activity之间的跳转及传递参数，按键事件的监听等，希望对初学的朋友有些帮助，同时欢迎老鸟给我做Code review，需要改进的地方，还请指出，谢谢。

 

 

 

 

 

 

 

 

 

 

下面逐个介绍一个代码，数据库辅助类：DBOpenHelper

 

package com.van.sqlite.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库辅助类。
 * @author Van
 *
 */
public class DBOpenHelper extends SQLiteOpenHelper{
	
	/**数据库名称*/
	private static final String DATABASE_NAME="MyAppDB";
	/**数据库版本*/
	private static final int DATABASE_VERSION=1;
	/** 创建数据表语句*/
	private static final String DDL_CREATE_TABLE_APPINFO="CREATE TABLE IF NOT EXISTS AppInfo (appId integer primary key autoincrement,appName text,appDescription text, remark text)";

	/**
	 * 实例化数据库连接.
	 * @param context
	 */
	public DBOpenHelper(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		/**初始化数据表*/
		this.getWritableDatabase().execSQL(DDL_CREATE_TABLE_APPINFO);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}
}

 

 

针对AppInfo数据表的辅助类，内含增删查改方法：AppInfoHelper

 

 

package com.van.sqlite.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class AppInfoHelper {
	
	
	private DBOpenHelper helper;  
	
	/**
	 * 构造
	 * @param context
	 */
	public AppInfoHelper(Context context){
		this.helper=new DBOpenHelper(context);
	}
	
	
	
	/**
	 * 查询
	 * @param tableName
	 * @return
	 */
	public Cursor select(String sql) {  
		
		SQLiteDatabase db=helper.getWritableDatabase(); 
		 
        try{
        	Cursor cursor=db.rawQuery(sql,null);  
        	cursor.moveToFirst();
        	return cursor;
        	
        }catch(Exception ex){
        	ex.printStackTrace();
        }finally{
        	if(db.isOpen()){
        		db.close();
        	}
        }
        
        return null;
    }  
	
	
	
	
	
	
	/**
	 * 新增方法.
	 * @param appName
	 * @param appDescription
	 * @param remark
	 */
	public void addAppInfo(String appName,String appDescription,String remark){
		

        ContentValues values=new ContentValues(); 
        values.put("appName", appName);  
        values.put("appDescription", appDescription);  
        values.put("remark", remark);  
        
        SQLiteDatabase db=helper.getWritableDatabase();
        
        try{
        	db.insert("AppInfo", null, values);  
        	
        }catch(Exception ex){
        	ex.printStackTrace();
        }finally{
        	if(db.isOpen()){
        		db.close();
        	}
        }
	}
	
	/**
	 * 编辑应用信息.
	 * @param appId
	 * @param appName
	 * @param appDescription
	 * @param remark
	 */
	public void editAppInfo(int appId,String appName,String appDescription,String remark){
		
        ContentValues values=new ContentValues(); 
        values.put("appName", appName);  
        values.put("appDescription", appDescription);  
        values.put("remark", remark);  
        
        SQLiteDatabase db=helper.getWritableDatabase();

        //条件
        String[] whereValue ={ Integer.toString(appId) }; 

        try{
        	db.update("AppInfo", values, "appId=?", whereValue);  
        }catch(Exception ex){
        	ex.printStackTrace();
        }finally{
        	if(db.isOpen()){
        		db.close();
        	}
        }
        
	}
	
	
	/**
	 * 删除应用信息.
	 * @param appId
	 */
	public void deleteAppInfo(int appId){
    	
        SQLiteDatabase db=helper.getWritableDatabase();
        
        //条件
        String[] whereValue ={ Integer.toString(appId) }; 
        
        try{
       	 
        	db.delete("AppInfo", "appId=?", whereValue);
        }catch(Exception ex){
        	ex.printStackTrace();
        }finally{
        	
        	if(db.isOpen()){
        		db.close();
        	}
        }
   }
	
	
	
	
	
	
	

}

 

 

 

AppInfo数据适配器，用于列表显示的适配器：AppInfoListAdapter

 

package com.van.sqlite.adapter;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.widget.SimpleAdapter;

import com.van.sqlite.activity.R;
import com.van.sqlite.db.DBOpenHelper;

public class AppInfoListAdapter {
	
	
	
	private Context context;

	
	public AppInfoListAdapter(Context context){
		this.context=context;
	}

	 /** 
     * 查询AppInfo返回Map集合 
     * @return 
     */  
    private ArrayList<HashMap<String, Object>> fillList(){  
          
         //生成动态数组，并且转载数据    
        ArrayList<HashMap<String, Object>> dataList = new ArrayList<HashMap<String, Object>>();    
          
        DBOpenHelper helper=new DBOpenHelper(context);  
        SQLiteDatabase db=helper.getReadableDatabase();  
          
        try{  
            Cursor cursor=db.rawQuery("SELECT * FROM AppInfo", null);   
            cursor.moveToFirst();  
  
            if(cursor.moveToFirst()) {   
            	
            	while (!cursor.isAfterLast()) { 
                    Integer appId = cursor.getInt(cursor.getColumnIndex("appId"));    
                    String appName = cursor.getString(cursor.getColumnIndex("appName"));   
                    String appDescription = cursor.getString(cursor.getColumnIndex("appDescription"));   
                      
                    HashMap<String, Object> map = new HashMap<String, Object>();    
                    map.put("appId",appId);    
                    map.put("appName", appName);    
                    map.put("appDescription", appDescription);    
                    dataList.add(map);    
                    cursor.moveToNext();
            	}
                    
            }    
              
        }catch(Exception ex){  
            ex.printStackTrace();  
        }finally{  
              
            if(db.isOpen()){  
                db.close();  
            }  
        }  
        return dataList;  
    }  
	
	
	
	/** 
     * 填充数据，取得数据适配器. 
     * @param listData 
     * @return 
     */  
    public SimpleAdapter getAdapter(Context context){  
          
         //生成适配器，数组===》ListItem    
        SimpleAdapter adapter = new SimpleAdapter(context,  
        											fillList(),//数据来源     
                                                    R.layout.list_item,//ListItem的XML实现    
                                                    //动态数组与ListItem对应的子项            
                                                    new String[] {"appName", "appDescription"},     
                                                    //ListItem的XML文件里面的两个TextView ID    
                                                    new int[] {R.id.textView_appName,R.id.textView_appDescription});    
          
        return adapter;   
    }  
	
	
	
	
	
	

}

 

 

 

 

 

3个Activity，主界面显示/增加/修改

SQLiteDemoActivity

 

package com.van.sqlite.activity;  

import java.util.HashMap;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.AlertDialog.Builder;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.KeyEvent;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.View.OnCreateContextMenuListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;

import com.van.sqlite.adapter.AppInfoListAdapter;
import com.van.sqlite.db.AppInfoHelper;
  
public class SQLiteDemoActivity extends Activity {  
	
	
	private ListView listView;
	private AppInfoListAdapter adapter;
	
	
    @Override  
    public void onCreate(Bundle savedInstanceState) {  
        super.onCreate(savedInstanceState);  
        setContentView(R.layout.main);  
        
        listView=(ListView)findViewById(R.id.listView_appList);  
        
        //获取数据适配器
        adapter=new AppInfoListAdapter(this);
 
        //添加并且显示    
        listView.setAdapter(adapter.getAdapter(this));    
        
        
        //跳转到新增窗口
        Button addButton=(Button)findViewById(R.id.button_to_add);
        addButton.setOnClickListener(new OnClickListener(){
        	@Override
			public void onClick(View arg0) {

				//打开新的窗口
				Intent intent=new Intent();
				intent.setClass(SQLiteDemoActivity.this, AddAppInfoActivity.class);
				startActivity(intent);
				//结束当前
				SQLiteDemoActivity.this.finish();
			}
        });
        
        
        //数据列表点击操作
        listView.setOnItemClickListener(new OnItemClickListener() {

			@SuppressWarnings("unchecked")
			@Override
			public void onItemClick(AdapterView<?> adapterView, View view, int index,long arg3) {
				
				ListView listView = (ListView)adapterView;
				HashMap<String, Object> map = (HashMap<String, Object>) listView.getItemAtPosition(index);
				
				//打开新的窗口
				Intent intent=new Intent();
				intent.setClass(SQLiteDemoActivity.this, EditAppInfoActivity.class);
				//将appId传入编辑窗口
				intent.putExtra("appId", Integer.parseInt(map.get("appId").toString()));
				startActivity(intent);
				//结束当前
				SQLiteDemoActivity.this.finish();
			}
		});
        
        
        
        //添加长按点击   
        listView.setOnCreateContextMenuListener(new OnCreateContextMenuListener() {   
               
            @Override  
            public void onCreateContextMenu(ContextMenu menu, View v,ContextMenuInfo menuInfo) {
                menu.setHeaderTitle("列表操作");    
                menu.add(0, 0, 0, "删除应用信息");   
            }
        });  
        
        
    }  

    
    
    @Override
	 public boolean onKeyDown(int keyCode, KeyEvent event) {
		if(keyCode == KeyEvent.KEYCODE_BACK){//返回按钮
			
			AlertDialog.Builder builder = new Builder(SQLiteDemoActivity.this); 
	        builder.setMessage("确实要退出程序吗?"); 
	        builder.setTitle("提示"); 
	        builder.setPositiveButton("确认", 
	                new android.content.DialogInterface.OnClickListener() { 
	                    
	                    public void onClick(DialogInterface dialog, int which) { 
	                        dialog.dismiss(); 
	                        SQLiteDemoActivity.this.finish(); 
	                    } 
	                }); 
	        builder.setNegativeButton("取消", 
	                new android.content.DialogInterface.OnClickListener() { 
	                 
	                    public void onClick(DialogInterface dialog, int which) { 
	                        dialog.dismiss(); 
	                    } 
	                }); 
	        builder.create().show(); 

	        return true;
		}
		
		return false;
	}
    

    /**
     * 删除应用.
     * @param appId
     */
    public void delete(int appId){
    	 AppInfoHelper appInfoHelper=new AppInfoHelper(this);
         appInfoHelper.deleteAppInfo(appId);
         Toast.makeText(SQLiteDemoActivity.this, "应用删除成功！", Toast.LENGTH_SHORT).show();  
         //添加并且显示    
         listView.setAdapter(adapter.getAdapter(this));   
    }
    
    
    //长按菜单响应函数   
    @SuppressWarnings("unchecked")
	@Override  
    public boolean onContextItemSelected(MenuItem item) {   
    	
    	AdapterView.AdapterContextMenuInfo m = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo();
    	HashMap<String, Object> map = (HashMap<String, Object>) listView.getItemAtPosition(m.position);
    	int appId=Integer.parseInt(map.get("appId").toString());
    	//调用方法，删除
    	delete(appId);
        return super.onContextItemSelected(item);   
    }   
    
    
    
}  

 

 

 

AddAppInfoActivity

 

package com.van.sqlite.activity;


import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.KeyEvent;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.van.sqlite.db.AppInfoHelper;

public class AddAppInfoActivity extends Activity{
	
	
	@Override  
    public void onCreate(Bundle savedInstanceState) {  
        super.onCreate(savedInstanceState);  
        setContentView(R.layout.add);  
        
        Button saveButton=(Button)findViewById(R.id.button_save);
        saveButton.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				add();//添加数据
			}
		});
    }  

	/**
	 * 添加数据方法.
	 */
	public void add(){
		
        EditText et_appName=(EditText)findViewById(R.id.editText_appName);
        EditText et_appDescription=(EditText)findViewById(R.id.editText_appDescription);
        EditText et_remark=(EditText)findViewById(R.id.editText_remark);

        String appName=et_appName.getText().toString();  
        String appDescription=et_appDescription.getText().toString();  
        String remark=et_remark.getText().toString();  

        AppInfoHelper appInfoHelper=new AppInfoHelper(this);
        appInfoHelper.addAppInfo(appName, appDescription, remark);
        
        Toast.makeText(AddAppInfoActivity.this, "应用信息添加成功！", Toast.LENGTH_SHORT).show();  
    	Intent intent = new Intent(AddAppInfoActivity.this,SQLiteDemoActivity.class);
    	startActivity(intent);
    	AddAppInfoActivity.this.finish();
	}
	
	
	
	@Override
	 public boolean onKeyDown(int keyCode, KeyEvent event) {
		if(keyCode == KeyEvent.KEYCODE_BACK){//返回按钮
			Intent intent = new Intent();
			intent.setClass(AddAppInfoActivity.this, SQLiteDemoActivity.class);
			startActivity(intent);// 启动新的Activity
			AddAppInfoActivity.this.finish();// 结束就的Activity 
		}
		
		return false;
	}
	
	
	
	
}

 

EditAppInfoActivity

 

package com.van.sqlite.activity;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.KeyEvent;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.van.sqlite.db.AppInfoHelper;

public class EditAppInfoActivity extends Activity{
	
	
	private int paramAppId;
	
	@Override  
    public void onCreate(Bundle savedInstanceState) {  
        super.onCreate(savedInstanceState);  
        setContentView(R.layout.edit);  
        
        //绑定数据
        bindValue();
        
        //绑定事件
        Button editButton=(Button)findViewById(R.id.button_edit_save);
        editButton.setOnClickListener(new OnClickListener() {
			@Override
			public void onClick(View v) {
				edit();
			}
		});
      
    }
	
	/**
	 * 查询赋值.
	 */
	public void bindValue(){
		
		//取得编号
		paramAppId= this.getIntent().getExtras().getInt("appId");
		
		//查询数据
        AppInfoHelper appInfoHelper=new AppInfoHelper(this);

        Cursor cursor=appInfoHelper.select("SELECT * FROM AppInfo WHERE appId="+paramAppId);   
        
  
        if(cursor.moveToFirst()) {    
                String appName = cursor.getString(cursor.getColumnIndex("appName"));   
            String appDescription = cursor.getString(cursor.getColumnIndex("appDescription"));  
            String remark = cursor.getString(cursor.getColumnIndex("remark"));  
            
            EditText et_appName=(EditText)findViewById(R.id.editText_edit_appName);
            EditText et_appDescription=(EditText)findViewById(R.id.editText_edit_appDescription);
            EditText et_remark=(EditText)findViewById(R.id.editText_edit_remark);
            
            et_appName.setText(appName);
            et_appDescription.setText(appDescription);
            et_remark.setText(remark);
        }    
	}
	
	/**
	 * 编辑应用。
	 */
	private void edit(){
		
		 	EditText et_appName=(EditText)findViewById(R.id.editText_edit_appName);
	        EditText et_appDescription=(EditText)findViewById(R.id.editText_edit_appDescription);
	        EditText et_remark=(EditText)findViewById(R.id.editText_edit_remark);
	        

	        String appName=et_appName.getText().toString();  
	        String appDescription=et_appDescription.getText().toString();  
	        String remark=et_remark.getText().toString();  

	        AppInfoHelper appInfoHelper=new AppInfoHelper(this);
	        appInfoHelper.editAppInfo(paramAppId, appName, appDescription, remark);
	        
	    	Toast.makeText(EditAppInfoActivity.this, "应用信息编辑成功！", Toast.LENGTH_SHORT).show();  
        	Intent intent = new Intent(EditAppInfoActivity.this,SQLiteDemoActivity.class);
        	startActivity(intent);
        	EditAppInfoActivity.this.finish();
	}
	
	 @Override
	 public boolean onKeyDown(int keyCode, KeyEvent event) {
		if(keyCode == KeyEvent.KEYCODE_BACK){//返回按钮
			
			Intent intent = new Intent();
			intent.setClass(EditAppInfoActivity.this, SQLiteDemoActivity.class);
			startActivity(intent);// 启动新的Activity
			EditAppInfoActivity.this.finish();// 结束就的Activity 
		}
		
		return false;
	}
	
	
}

 

 

几个布局文件：
main.xml

 

<?xml version="1.0" encoding="utf-8"?>  
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
        android:layout_width="fill_parent"  
        android:layout_height="fill_parent"  
        android:orientation="vertical" >

     
          
         <Button
             android:id="@+id/button_to_add"
             android:layout_width="wrap_content"
             android:layout_height="wrap_content"
             android:text="@string/add" />

 



        <ListView
            android:id="@+id/listView_appList"
            android:layout_width="match_parent"
            android:layout_height="match_parent" >

        </ListView>
      
    </LinearLayout>  

 

list_item.xml

 

<?xml version="1.0" encoding="utf-8"?>  
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
        android:layout_width="match_parent"  
        android:layout_height="match_parent"  
        android:orientation="vertical" >  
      
        <TextView  
            android:id="@+id/textView_appName"  
            android:layout_width="wrap_content"  
            android:layout_height="wrap_content"  
            android:textAppearance="?android:attr/textAppearanceMedium" />  
      
        <TextView  
            android:id="@+id/textView_appDescription"  
            android:layout_width="wrap_content"  
            android:layout_height="wrap_content"  
            android:textAppearance="?android:attr/textAppearanceSmall" />
</LinearLayout> 

 

 

add.xml

 

<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent" >

    <TextView
        android:id="@+id/textView_appName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/appName"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/editText_appName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="textPostalAddress" />

    <TextView
        android:id="@+id/textView_appDescription"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/appDescription"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/editText_appDescription"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="text" />

    <TextView
        android:id="@+id/textView_remark"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/remark"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/editText_remark"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10" 
        android:inputType="text"/>

    <Button
        android:id="@+id/button_save"
        android:layout_width="100dp"
        android:layout_marginTop="15dp"
        android:layout_height="wrap_content"
        android:text="@string/save" />

</TableLayout>

 

 

edit.xml

 

<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent" >

    <TextView
        android:id="@+id/textView_edit_appName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/appName"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/editText_edit_appName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="textPostalAddress" />

    <TextView
        android:id="@+id/textView_edit_appDescription"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/appDescription"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/editText_edit_appDescription"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10"
        android:inputType="text" />

    <TextView
        android:id="@+id/textView_edit_remark"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/remark"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/editText_edit_remark"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:ems="10" 
        android:inputType="text"/>

    <Button
        android:id="@+id/button_edit_save"
        android:layout_width="100dp"
        android:layout_marginTop="15dp"
        android:layout_height="wrap_content"
        android:text="@string/edit" />

</TableLayout>

 

 

神马String变量就不贴了，看看效果先，主界面：

 

添加，点击主界面添加按钮进入添加页面：

 

编辑，点击列表直接跳转到编辑页面：

 

删除，长按类别项出现删除操作：