android编程中SQLite数据库的增、删、改、查功能实现

SQLite数据库是android系统自带的数据库，小巧使用方便。

最常见的就是电话簿。电话簿数据库存在的位置位于（如图）：

可以用SQLiteManager打开。

在android中使用SQLiteDatabase就能直接对数据库操作。

下面用一个demo实现数据库的创建、表的创建和删除、表中数据的增删改查。

架构如下：

布局文件main实现简单的功能：

 1 <?xml version="1.0" encoding="utf-8"?>
 2 
 6 
 7     
12 
13     
18     
23     
28     
33     
38     
45     
46     
53     
54     
61     
62         
69     
76 

工具类DBUtil.java是实现数据库的创建连接、断接、增删改查等操作。

package com.db.util;

import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.EditText;

public class DBUtil
{
    static SQLiteDatabase sld;
    public static void createOrOpenDatabase() throws Exception
    {
        sld=SQLiteDatabase.openDatabase
        (
                "/data/data/com.db/dbtest", //数据库所在路径
                null,                                 //CursorFactory
                SQLiteDatabase.OPEN_READWRITE|SQLiteDatabase.CREATE_IF_NECESSARY //读写、若不存在则创建
        );            
        String sql0="create table if not exists user(username varchar2(20),password varchar2(20))";
        sld.execSQL(sql0); 
    }
    
    public static void closeDatabase() throws Exception
    {
       try
       {
           sld.close(); 
       }
       catch(Exception e)
       {
           e.printStackTrace();
       }
    }
   
    /*=====================================begin==========================================================*/
    //获取用户信息-winxiang
    public static List searchuser(String username){
        List list=new ArrayList();
        try
        {
            createOrOpenDatabase();
            String sql="select * from user where username='"+username+"'";
            Cursor cur=sld.rawQuery(sql, new String[]{});
            while(cur.moveToNext())
            {
                list.add(cur.getString(0));  //username
                list.add(cur.getString(1));  //password 
            }
            cur.close();
            closeDatabase();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return list;        
    }
  //获取所有用户信息-winxiang
    public static List searchalluser(){
        List list=new ArrayList();
        try
        {
            createOrOpenDatabase();
            String sql="select * from user";
            Cursor cur=sld.rawQuery(sql, new String[]{});
            while(cur.moveToNext())
            {
                list.add(cur.getString(0));  //username
                list.add(cur.getString(1));  //password 
            }
            cur.close();
            closeDatabase();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return list;        
    }
        
    public static void updatetable(String sql)
    {
        try
        {
            createOrOpenDatabase();            
            sld.execSQL(sql);            
            closeDatabase();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
   
    //舍弃user表
    public static void droptable(){
        try
        {
            String sql="drop table user";
            createOrOpenDatabase();            
            sld.execSQL(sql);            
            closeDatabase();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        Log.d("DB","had deleted table: user->");  
    }
    /*=====================================end==========================================================*/
}

DBTestactivity：

 1 package com.db;
 2 
 3 
 4 import java.util.List;
 5 
 6 import com.db.util.DBUtil;
 7 import android.app.Activity;
 8 import android.os.Bundle;
 9 import android.view.View;
10 import android.view.View.OnClickListener;
11 import android.widget.Button;
12 import android.widget.EditText;
13 import android.widget.TextView;
14 import android.widget.Toast;
15 
16 public class DBtestActivity extends Activity {
17     Button btn_createdb,btn_insert,btn_show,btn_update,btn_showall,deleteusertable;
18     EditText username,password;
19     TextView showsomething;
20     
21     @Override
22     public void onCreate(Bundle savedInstanceState) {
23         super.onCreate(savedInstanceState);
24         setContentView(R.layout.main);
25         init();
26     }
27     
28     public void init(){
29         username = (EditText) findViewById(R.id.username);
30         password = (EditText) findViewById(R.id.password);
31         btn_createdb = (Button) findViewById(R.id.btn_create);
32         btn_insert = (Button) findViewById(R.id.btn_insert);
33         btn_show = (Button) findViewById(R.id.btn_show);
34         btn_update = (Button) findViewById(R.id.btn_update);
35         deleteusertable = (Button) findViewById(R.id.btn_deleteusertable);
36         btn_showall= (Button) findViewById(R.id.btn_showall);
37         showsomething = (TextView) findViewById(R.id.showsomething);
38         
39         btn_createdb.setOnClickListener(listener);
40         btn_insert.setOnClickListener(listener);
41         btn_createdb.setOnClickListener(listener);
42         btn_show.setOnClickListener(listener);
43         btn_update.setOnClickListener(listener);
44         btn_showall.setOnClickListener(listener);
45         deleteusertable.setOnClickListener(listener);
46     }
47     
48     public OnClickListener listener = new OnClickListener() {
49         @Override
50         public void onClick(View v) {
51             Button button = (Button) v;
52             if(button.getId()==btn_createdb.getId()){
53                 try {
54                     DBUtil.createOrOpenDatabase(); 
55                 } catch (Exception e) {
56                     e.printStackTrace();
57                 }
58             }else if(button.getId()==btn_insert.getId()){
59                 String sql="insert into user values ('"+username.getText()+"','"+password.getText()+"')";
60                 DBUtil.updatetable(sql);
61             }else if(button.getId()==btn_show.getId()){
62                 Listuser = DBUtil.searchuser(username.getText().toString());
63                 showsomething.setText(user.toString());
64             }else if(button.getId()==btn_update.getId()){
65                 String sql="update user set username='"+username.getText()+"',password='"+password.getText()+"' where username = '"+username.getText()+"'";
66                 System.out.println(sql);
67                 DBUtil.updatetable(sql);
68             }else if(button.getId()==btn_showall.getId()){
69                 Listusers = DBUtil.searchalluser();
70                 showsomething.setText(users.toString());
71             }else if(button.getId()==deleteusertable.getId()){
72                 DBUtil.droptable();
73                 Toast.makeText(getApplicationContext(), "用户表删除成功", Toast.LENGTH_SHORT).show();
74             }
75         }
76     };
77 }

效果图如下：