SQLite数据库的创建与增删改查

1.主界面布局设计（ layout .xml )

主界面布局中放置3个按钮，分别是“查询／删除”，“修改”，“添加”，单击按钮后分别出现对应的界面。

<?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"
    xmlns:tools="http://schemas.android.com/tools"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/search_delete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:text="查询/删除" />

    <Button
        android:id="@+id/update"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:text="修改" />

    <Button
        android:id="@+id/add"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:text="添加" />
</LinearLayout>

2.数据库创建以及操作方法类（ user database )

数据库的创建类 user _ database 包含了数据库创建的 oncreate (）方法、版本更新的 onUpgradeO 方法、数据添加的 adddata (）方法、数据删除的 delete (）方法以及数据更新的 update (）方法，

public class user_database extends SQLiteOpenHelper {
    public user_database(@Nullable Context context ) {
        super(context,"user",null,1);//数据库名user
    }
//数据库第一次创建是调用该方法
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String sql="create table user(id integer primary key autoincrement," +
                "username varchar(20),paswd varchar(20),sex varchar(20),age integer)";
        sqLiteDatabase.execSQL(sql);
    }
//数据库版本号更新是调用
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
//添加
    public void adddata(SQLiteDatabase sqLiteDatabase,String username,String paswd,String sex,int age){
        ContentValues values=new ContentValues();
        values.put("username",username);
        values.put("paswd",paswd);
        values.put("sex",sex);
        values.put("age",age);
        sqLiteDatabase.insert("user",null,values);
        sqLiteDatabase.close();
    }
//删除
    public void delete(SQLiteDatabase sqLiteDatabase,int id){
        sqLiteDatabase.delete("user","id=?",new String[]{id+""});
        sqLiteDatabase.close();
    }
//更新
    public void update(SQLiteDatabase sqLiteDatabase,int id,String username,String paswd,String sex,int age){
        ContentValues values=new ContentValues();
        values.put("username",username);
        values.put("paswd",paswd);
        values.put("sex",sex);
        values.put("age",age);
        sqLiteDatabase.update("user",values,"id=?",new String[]{id+""});
        sqLiteDatabase.close();
    }
//查询
    public List<userInfo> querydata(SQLiteDatabase sqLiteDatabase){
        Cursor cursor=sqLiteDatabase.query("user",null,null,
                null,null,null,"id ASC");
        List<userInfo>list=new ArrayList<userInfo>();
        while (cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String username=cursor.getString(1);
            String paswd=cursor.getString(2);
            String sex=cursor.getString(3);
            int age=cursor.getInt(cursor.getColumnIndex("age"));
            list.add(new userInfo(id,username,paswd,sex,age));
        }
        cursor.close();
        sqLiteDatabase.close();
        return list;
    }

}

3.创建一个 userInfo 的 JavaBean 类

在操作数据库时，把数据存放在一个 JavaBean 对象中操作起来会相对简单一点。因此创建一个 userInfo 类。

public class userInfo {
    public int id;
    public String username;
    public String paswd;
    public String sex;
    public int age;

    public userInfo(int id, String username, String paswd, String sex, int age) {
        this.id=id;
        this.username=username;
        this.paswd=paswd;
        this.sex=sex;
        this.age=age;
    }

    public void setId(int id){this.id=id; }
    public void setUsername(String username){ this.username=username; }
    public void setPaswd(String paswd){ this.paswd=paswd; }
    public void setSex(String sex){ this.sex=sex; }
    public void setAge(int age){  this.age=age; }
    public int getId(){  return id;  }
    public String getUsername(){ return username; }
    public String getPaswd(){ return paswd; }
    public String getSex(){ return sex; }
    public int getAge(){ return age;  }
    @Override
    public String toString(){
        return "userInfo{"+"id+"+id+",username"+username+'\''+
                ",paswd"+paswd+'\''+",sex"+sex+'\''+",age"+age+'}';
    }
}

4.主界面功能实现类（ MainActivity ) 

MainActivity 负责数据库的创建以及对布局中的3个按钮添加监听，然后跳转到不同的功能界面中去。

public class MainActivity extends Activity implements View.OnClickListener {
    public user_database user;
    public SQLiteDatabase sqL_read;
    private Button s_d_btn,i_btn,u_btn;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.layout);
        user_database user=new user_database(MainActivity.this);
        sqL_read=user.getReadableDatabase();
        init();
    }

    private void init() {
        s_d_btn=(Button)findViewById(R.id.search_delete);
        i_btn=(Button)findViewById(R.id.add);
        u_btn=(Button)findViewById(R.id.update);
        s_d_btn.setOnClickListener(this);
        i_btn.setOnClickListener(this);
        u_btn.setOnClickListener(this);
    }
    @Override
    public void onClick(View v) {
        switch (v.getId()){
            case R.id.search_delete:
                Intent intent1=new Intent(MainActivity.this,Sea_deluser_Activity.class);
                startActivity(intent1);
                break;
            case R.id.add:
                Intent intent2=new Intent(MainActivity.this,Insertuser_Activity.class);
                startActivity(intent2);
                break;
            case R.id.update:
                Intent intent3=new Intent(MainActivity.this,Updareuser_Activity.class);
                startActivity(intent3);
                break;
            default:
                break;
        }
    }
}

5.用户信息查询＆删除界面设计（ ser_del.xml )

考虑到用户信息的删除，需要先查询到用户信息，然后再根据 id 删除数据，所以把用户的信息查询与删除放在一个界面里实现。在显示用户的信息时，单击用户名弹出一个对话框，提示用户是否删除。 ser_del. 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">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="ID" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="用户名" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="密码" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="性别" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:text="年龄"
            android:textSize="20sp"
            />
    </LinearLayout>

    <ListView
        android:id="@+id/mes"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:dividerHeight="2dp"
        android:padding="5dip">
    </ListView>
</LinearLayout>

6.删除查询类（Sea_deluser_Activity） 

public class Sea_deluser_Activity extends Activity {
    public ListView user_list;
    private List<userInfo>list;
    private SQLiteDatabase sqLiteDatabase;
    private String[] user_mes;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        requestWindowFeature(Window.FEATURE_NO_TITLE);
        setContentView(R.layout.ser_del);
        user_list=findViewById(R.id.mes);
        user_database users=new user_database(Sea_deluser_Activity.this);
        sqLiteDatabase=users.getReadableDatabase();
//获取从数据库查询到的数据
        list=users.querydata(sqLiteDatabase);
//把获取到的信息添加到用户数组中
        user_mes=new String[list.size()];
        for (int i = 0; i<list.size(); i++){
            user_mes[i]=list.get(i).getId()+"\t\t\t\t\t\t\t\t"+list.get(i).getUsername()+"\t\t\t\t\t\t\t\t"+
                    list.get(i).getPaswd()+"\t\t\t\t\t\t\t\t"+list.get(i).getSex()+"\t\t\t\t\t\t\t\t"
                    +list.get(i).getAge();
        }
//把用户名显示在ListVeiw上
        final ArrayAdapter<String>adapter=new ArrayAdapter<String>
                (Sea_deluser_Activity.this, android.R.layout.simple_list_item_1,user_mes);
        user_list.setAdapter(adapter);
//为ListView每个元素添加单击元素
        user_list.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
                final int id=list.get(i).getId();
//弹出一个对话框
                new AlertDialog.Builder(Sea_deluser_Activity.this).setTitle("系统提示")
                        .setMessage("确定删除吗").setPositiveButton("确定", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialog, int which) {
//删除数据操作，首先获得到ID
                        user_database user_database=new user_database(Sea_deluser_Activity.this);
                        SQLiteDatabase sqLiteDatabase=user_database.getWritableDatabase();
                        user_database.delete(sqLiteDatabase,id);
                        refresh();
                        Toast.makeText(Sea_deluser_Activity.this,"删除成功",Toast.LENGTH_SHORT).show();
                    }
                }).setNegativeButton("取消", new DialogInterface.OnClickListener() {
                    @Override
                    public void onClick(DialogInterface dialog, int which) {
                    }
                }).show();
            }
        });
    }
//刷新页面
    private void refresh() {
        finish();
        Intent intent=new Intent(Sea_deluser_Activity.this,Sea_deluser_Activity.class);
        startActivity(intent);

    }
}

7.用户信息添加界面设计（ user insert . xml )

用户信息添加界面主要包含3个 EditText 组件，分别对应数库中的 username 、 paswd 、 age 。另外还包括一个按钮和一个 Spinner 组件： Spinner 组件负责让用户选择性别：按钮负责把数据添加到数据库当中。

<?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">
    <EditText
        android:id="@+id/insert_name"
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:hint="请输入用户名"/>
    <EditText
        android:id="@+id/insert_paswd"
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:inputType="numberPassword"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:hint="请输入密码"/>
    <Spinner
        android:id="@+id/insert_sex"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:entries="@array/sex"/>
    <EditText
        android:id="@+id/insert_age"
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:hint="请输入年纪"/>
    <Button
        android:id="@+id/save_usermes"
        android:layout_width="match_parent"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:textColor="#FFFFFF"
        android:background="#4169E1"
        android:text="添加该用户信息"
        android:layout_height="50dp"/>
</LinearLayout>

8.用户信息添加功能实现类（ Insertuser _ Activity ) 

Insertuser _ Activity 首先需要获取各个组件，然后为按钮添加监听事件，为 Spinner 组件添加选择事件。按钮的监听事件负责把 EditText 中输人的信息和 Spinner 中选择的性别添加到数据库中，然后跳转到用户数据查询页面，查看插人的信息，添加的事件通过调用数据库创建类的数据插人方法实现。

public class Insertuser_Activity extends Activity {
    private EditText name_edit,paswd_edit,age_edit;
    private Spinner spinner;
    private Button save_btn;
    private String select_sex="男";
    @Override
    protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        requestWindowFeature(Window.FEATURE_NO_TITLE);
        setContentView(R.layout.insert);
        init();
    }
    public void init() {
        name_edit = (EditText) findViewById(R.id.insert_name);
        paswd_edit = (EditText) findViewById(R.id.insert_paswd);
        spinner = (Spinner) findViewById(R.id.insert_sex);
        spinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> adapterView, View view, int i, long l) {
                select_sex = Insertuser_Activity.this.getResources().getStringArray(R.array.sex)[i];
            }

            @Override
            public void onNothingSelected(AdapterView<?> adapterView) {
            }
        });
        age_edit = (EditText) findViewById(R.id.insert_age);
        save_btn = (Button) findViewById(R.id.save_usermes);
        save_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String name_str = name_edit.getText().toString();
                Toast.makeText(Insertuser_Activity.this,name_str,Toast.LENGTH_SHORT).show();
                String paswd_str = paswd_edit.getText().toString();
                int age = Integer.parseInt(age_edit.getText().toString());
//调用数据库操作类的插入方法
                user_database us_db = new user_database(Insertuser_Activity.this);
                SQLiteDatabase sqLiteDatabase = us_db.getWritableDatabase();
                us_db.adddata(sqLiteDatabase, name_str, paswd_str, select_sex, age);
                Intent intent = new Intent(Insertuser_Activity.this, Sea_deluser_Activity.class);
                startActivity(intent);
            }
        });
    }
}

9.数据库数据更新界面（update.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">

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="60dp"
        android:gravity="center">

        <EditText
            android:id="@+id/user_id"
            android:layout_width="200dp"
            android:layout_height="match_parent"
            android:gravity="center"
            android:hint="请输入用户ID"
            android:textSize="20sp" />

        <Button
            android:id="@+id/chaxun"
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_alignParentRight="true"
            android:layout_marginRight="71dp"
            android:text="查询" />
    </RelativeLayout>

    <EditText
        android:id="@+id/update_name"
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:hint="请输入用户名"/>
    <EditText
        android:id="@+id/update_paswd"
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:inputType="numberPassword"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:hint="请输入密码"/>
    <Spinner
        android:id="@+id/update_sex"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:entries="@array/sex"/>
    <EditText
        android:id="@+id/update_age"
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:gravity="center"
        android:layout_marginTop="10dp"
        android:hint="请输入年纪"/>
    <Button
        android:id="@+id/update_usermes"
        android:layout_width="match_parent"
        android:layout_marginTop="20dp"
        android:textSize="17sp"
        android:textColor="#FFFFFF"
        android:background="#4169E1"
        android:text="更新该用户信息"
        android:layout_height="50dp"/>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:orientation="horizontal">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="ID" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="用户名" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="密码" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:textSize="20sp"
            android:text="性别" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:text="年龄"
            android:textSize="20sp"   />

    </LinearLayout>

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

10.数据更新类（Updareuser_Activity）

public class Updareuser_Activity extends Activity {
    private EditText id_edit,name_edit,paswd_edit,age_edit;
    private Spinner spinner;
    private Button update_btn,chaxun_btn;
    private String select_sex="男";
    public ListView user_list;
    private List<userInfo> list;
    SQLiteDatabase sqLiteDatabase;
    private String[] user_mes;
    @Override
    protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        requestWindowFeature(Window.FEATURE_NO_TITLE);
        setContentView(R.layout.update);
        init();
    }

    public void init() {
        id_edit=(EditText) findViewById(R.id.user_id) ;
        name_edit = (EditText) findViewById(R.id.update_name);
        paswd_edit = (EditText) findViewById(R.id.update_paswd);
        spinner = (Spinner) findViewById(R.id.update_sex);
        spinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> adapterView, View view, int i, long l) {
                select_sex = Updareuser_Activity.this.getResources().getStringArray(R.array.sex)[i];
            }
            @Override
            public void onNothingSelected(AdapterView<?> adapterView) {
            }
        });
        age_edit = (EditText) findViewById(R.id.update_age);
        update_btn = (Button) findViewById(R.id.update_usermes);
        update_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                int id=Integer.parseInt(id_edit.getText().toString());
                String name_str = name_edit.getText().toString();
                String paswd_str = paswd_edit.getText().toString();
                int age = Integer.parseInt(age_edit.getText().toString());
                Toast.makeText(Updareuser_Activity.this,"修改成功！",Toast.LENGTH_SHORT).show();
                user_database us_db = new user_database(Updareuser_Activity.this);
                SQLiteDatabase sqLiteDatabase = us_db.getWritableDatabase();
                us_db.update(sqLiteDatabase,id, name_str, paswd_str, select_sex, age);
                Intent intent = new Intent(Updareuser_Activity.this, Sea_deluser_Activity.class);
                startActivity(intent);
            }
        });
        chaxun_btn=findViewById(R.id.chaxun);
        chaxun_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                    user_list=findViewById(R.id.user_cx);
                    user_database users=new user_database(Updareuser_Activity.this);
                    sqLiteDatabase=users.getReadableDatabase();
                    list=users.querydata(sqLiteDatabase);
                    user_mes=new String[list.size()];
                    for (int i = 0; i < list.size(); i++) {
                        user_mes[i] = list.get(i).getId()+"\t\t\t\t\t\t\t\t"+list.get(i).getUsername() + "\t\t\t\t\t\t\t\t" +
                                list.get(i).getPaswd() + "\t\t\t\t\t\t\t\t" + list.get(i).getSex() + "\t\t\t\t\t\t\t\t"
                                + list.get(i).getAge();
                    }
                    final ArrayAdapter<String> adapter = new ArrayAdapter<String>
                            (Updareuser_Activity.this, android.R.layout.simple_list_item_1, user_mes);
                    user_list.setAdapter(adapter);
            }
        });
    }
}

AndroidManifest.xml添加

        <activity android:name=".Insertuser_Activity"/>
        <activity android:name=".Sea_deluser_Activity"/>
        <activity android:name=".Updareuser_Activity"/>

结果展示：

查询、删除：

 查询、更新：

添加：

 

 写完之后，感觉有点复杂 (Ｔ▽Ｔ)，不过问题不大，友友根据实际情况修改。最后面的结果展示排版没弄好，建议自己动手操作一遍，便于理解。