Android利用listview控件操作SQLite数据库实例

在本实例中，首先我们利用sqliteOpenHelper类建立一个数据库，并写好增、删、查等方法，通过SimpleCursorAdapter连接listview实现数据库的增加、查询以及长按删除的功能。

首先，我们先认识一下什么是SQLiteOpenHelper类。

Android为了操作SQlite数据库，提供了SQLiteDatabase类，其内封装了insert 、delete、update 、query 、执行SQL命令等操作。同时又为SQLiteDatabase提供了一个辅助类，SQLiteOpenHelper。它提供了两个重要的方法，分别是：

onCreate(SQLiteDatabase db)：用户初次使用软件时生成数据库，一旦数据库存在则不会调用此方法。函数是在第一次创建数据库的时候执行的，仅仅生成DataBaseHelper对（SQLiteOpenHelper类型）的时候是不会调用该函数的，而只有当调用DataBaseHelper对象的getReadableDataBase时或者是调用了getWritableDataBase时，如果是第一次创建数据库，那么就一定会调用onCreate()函数。

onUpgrade(SQLiteDatabase db，int oldVersion，int vewVersion)：用于升级软件时更新数据库表结构，如增加表、列字段等操作。

实现了这两个方法，就可以用它的getWritableDatabase()和getReadableDatabase()来获得数据库（SQLiteDatabase 对象）。

如果用户需要升级数据库表结构，需要主动调用onUpgrade(SQLiteDatabase db，int oldVersion，int vewVersion),传入一个新的版本的号。

建立一个新数据库的代码如下：

package com.example.listview_sqlite_xu;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class NewsSearchDatabaseHelper extends SQLiteOpenHelper {
final String SQL_CREATE_TABLE = "create table news_table (" +
"_id integer primary key autoincrement, " +
"news_tittle varchar(50), " +
"news_content varchar(5000))";
public NewsSearchDatabaseHelper(Context context, String name, int version) {
    super(context, name, null, version);
  }
  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(SQL_CREATE_TABLE);
  }
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    System.out.println("call update");
  }   
}

接下来我们建立MainActivity：

package com.example.listview_sqlite_xu;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.View;
import android.view.View.OnCreateContextMenuListener;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;
public class MainActivity extends Activity {
  private NewsSearchDatabaseHelper helper;  //数据库帮助类
  private EditText et_tittle;          //输入新闻标题
  private EditText et_content;        //输入新闻内容
  private ListView listView;                  //显示新闻列表
  ArrayList<HashMap<String, Object>> listData; //  key-value
  @Override
  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    helper = new NewsSearchDatabaseHelper(getApplicationContext(), "news", 1);       //创建一个名为“news”的数据库
    //初始化控件
    et_tittle = (EditText) findViewById(R.id.et_news_tittle);
    et_content = (EditText) findViewById(R.id.et_news_content);
    listView = (ListView) findViewById(R.id.lv_news);
    listView.setOnCreateContextMenuListener(listviewLongPress); 
      // 设置长按事件 
  }
  
  public void onClick(View view) {
    int id = view.getId();
    if(id==R.id.bt_add) 
     insertNews();
    else if(id== R.id.bt_query)
        queryNews();
    }
  
  private void inflateListView(Cursor cursor) {
  SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter(getApplicationContext(), R.layout.item, cursor, new String[]{"news_tittle", "news_content"}, new int[]{R.id.tittle, R.id.content},1);
    listView.setAdapter(cursorAdapter);
  }
  
  private void insertNews() {
    String tittle = et_tittle.getText().toString();
    String content = et_content.getText().toString();
    helper.getReadableDatabase().execSQL("insert into news_table values(null, ?, ?)", new String[]{tittle, content});
    Cursor cursor = helper.getReadableDatabase().rawQuery("select * from news_table", null);
    inflateListView(cursor);      //刷新listview
  }
  
  private boolean deleteNews(int _id) {
     String whereClause = "_id=?"; 
     String[] whereArgs = new String[] { String.valueOf(_id) }; 
     try{
     helper.getReadableDatabase().delete("news_table", whereClause,whereArgs);
     Cursor cursor = helper.getWritableDatabase().rawQuery("select * from news_table", null);
     inflateListView(cursor);
     }catch (SQLException e) { 
       Toast.makeText(getApplicationContext(), "删除数据库失败", 
           Toast.LENGTH_LONG).show(); 
       return false; 
     } 
     return true; 
   } 
  //长按listview删除item   
  OnCreateContextMenuListener listviewLongPress = new OnCreateContextMenuListener() { 
     public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) { 
        final AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo) menuInfo; 
        new AlertDialog.Builder(MainActivity.this) 
            // 弹出窗口的最上头文字  
            .setTitle("删除当前数据") 
             //设置弹出窗口的图式  
            .setIcon(android.R.drawable.ic_dialog_info) 
            // 设置弹出窗口的信息  
            .setMessage("确定删除当前记录") 
            .setPositiveButton("是", 
             new DialogInterface.OnClickListener() { 
             public void onClick( DialogInterface dialoginterface, int i) { 
                    // 获取位置索引 
            int mListPos = info.position; 
                    // 将listview中所有的数据都传入hashmap-listData中
            Cursor c = helper.getReadableDatabase().rawQuery("select * from news_table", null);
            int columnsSize = c.getColumnCount();
            listData = new ArrayList<HashMap<String, Object>>();
             while (c.moveToNext()) { 
            HashMap<String, Object> map = new HashMap<String, Object>(); 
             for (int j = 0; j < columnsSize; j++) { 
             map.put("_id", c.getString(0)); 
             map.put("news_tittle", c.getString(1)); 
             map.put("news_content", c.getString(2)); 
             } 
             listData.add(map); 
             } 
              HashMap<String, Object> map = listData .get(mListPos); 
              // 获取id 
              int id = Integer.valueOf((map.get("_id").toString())); 
             deleteNews(id); 
               // 移除数据   
             } 
             } 
           ) 
        .setNegativeButton("否", 
          new DialogInterface.OnClickListener() { 
           public void onClick( 
             DialogInterface dialoginterface, int i) { 
                    // 什么也没做 
                  } 
                }).show(); 
      } 
    };
  
  private void queryNews() {
    String tittle = et_tittle.getText().toString();
    String content = et_content.getText().toString();
    Cursor cursor = helper.getReadableDatabase().rawQuery(
        "select * from news_table where news_tittle like ? and news_content like ?", 
        new String[]{"%" + tittle + "%", "%" + content + "%"});
    Bundle bundle = new Bundle();
    bundle.putSerializable("news", cursor2list(cursor));
    Intent intent = new Intent(this, SearchResultActivity.class);
    intent.putExtras(bundle);
    startActivity(intent);  
  }
  
  private ArrayList<Map<String, String>> cursor2list(Cursor cursor) {
    ArrayList<Map<String, String>> list = new ArrayList<Map<String,String>>();
    //遍历Cursor
    while(cursor.moveToNext()){
      Map<String, String> map = new HashMap<String, String>();
      map.put("tittle", cursor.getString(1));
      map.put("content", cursor.getString(2));
      list.add(map);
    }
    return list;
  }
  @Override
  protected void onDestroy() {
    super.onDestroy();
    //释放数据库资源
    if(helper !=null)
      helper.close();
  }
}

新建一个Activity用来显示查询的结果：

package com.example.listview_sqlite_xu;
import java.util.List;
import java.util.Map;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.SimpleAdapter;
public class SearchResultActivity extends Activity {
  private ListView listView;
  @Override
  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    //设置布局文件
    setContentView(R.layout.news_search_result);
    //初始化组件
    listView = (ListView) findViewById(R.id.lv_search_result);
    //获取跳转到该Activity的intent对象
    Intent intent = getIntent();
    //获取Intent对象所携带的数据
    Bundle bundle = intent.getExtras();
    //从Bundle中取出List<Map<String,String>>数据
    @SuppressWarnings("unchecked")
    List<Map<String, String>> list = (List<Map<String, String>>)bundle.getSerializable("news");
    SimpleAdapter adapter = new SimpleAdapter(
        getApplicationContext(),   //上下文对象
        list,             //数据源
        R.layout.item,         //List显示布局
        new String[]{"tittle", "content"}, //List中map的键值
        new int[]{R.id.tittle, R.id.content});  //填充到的布局文件
    listView.setAdapter(adapter);
  }
}

main_activity的布局文件

<LinearLayout xmlns:android="Http://schemas.android.com/apk/res/android"
  xmlns:tools="http://schemas.android.com/tools"
  android:layout_width="match_parent"
  android:layout_height="match_parent"
  android:paddingBottom="@dimen/activity_vertical_margin"
  android:paddingLeft="@dimen/activity_horizontal_margin"
  android:paddingRight="@dimen/activity_horizontal_margin"
  android:paddingTop="@dimen/activity_vertical_margin"
  tools:context=".MainActivity" 
  android:orientation="vertical">
  <TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="新闻标题" />
  <EditText 
    android:id="@+id/et_news_tittle"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:singleLine="true"
    android:hint="点击此处输入新闻标题"/>
  <TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="新闻内容" />
  <EditText 
    android:id="@+id/et_news_content"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:lines="2"
    android:hint="点击此处输入新闻内容"/>
  <LinearLayout
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:orientation="horizontal"
    android:layout_gravity="center_horizontal">
    <Button
      android:id="@+id/bt_add"
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:onClick="onClick"
      android:text="添加新闻" />
    <Button
      android:id="@+id/bt_query"
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:onClick="onClick"
      android:text="查找新闻" />
  </LinearLayout>
  <ListView 
    android:id="@+id/lv_news"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"/>
</LinearLayout>
listview的布局文件：
<?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/tittle"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textSize="20dp"
    android:textColor="#CC0000"
    />
  <TextView 
    android:id="@+id/content"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:textSize="10dp"
    android:textColor="#00FF00"/>
</LinearLayout>

查询结果页面布局文件：

<?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" >
  <ListView 
    android:id="@+id/lv_search_result"
    android:layout_height="wrap_content"
    android:layout_width="wrap_content"/>
</LinearLayout>

您可能感兴趣的文章:Android ListView 条目多样式展示实例详解Android ListView用EditText实现搜索功能效果Android ListView隐藏右侧滚动条功能android listview实现新闻列表展示效果Android 中RecycleView实现item的点击事件Android recycleView的应用和点击事件实例详解Android ListView与RecycleView的对比使用解析