SQLite数据库——增删改查

Activity
package com.zdsoft.sqlite1207.activity;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.support.v7.app.AppCompatActivity;import android.os.Bundle;import android.view.View;import android.widget.Button;import android.widget.EditText;import android.widget.ListView;import android.widget.Toast;import com.zdsoft.sqlite1207.adapter.MainListViewAdapter;import com.zdsoft.sqlite1207.db.DBHelper;import com.zdsoft.sqlite1207.R;import com.zdsoft.sqlite1207.entity.Users;import java.util.ArrayList;import java.util.List;public class MainActivity extends AppCompatActivity implements View.OnClickListener {    private EditText et_user, et_password, et_id;    private Button bt_add, bt_delete, bt_select, bt_update;    public DBHelper dbHelper;    private List<Users> list;    private ListView lv_show;    MainListViewAdapter adapter;    SQLiteDatabase sqLiteDatabase;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        initView();        listener();        initData();        sqLiteDatabase.close();    }    private void initView() {        et_user = (EditText) findViewById(R.id.et_user);        et_password = (EditText) findViewById(R.id.et_password);        bt_add = (Button) findViewById(R.id.bt_add);        bt_delete = (Button) findViewById(R.id.bt_delete);        bt_select = (Button) findViewById(R.id.bt_select);        bt_update = (Button) findViewById(R.id.bt_update);        lv_show = (ListView) findViewById(R.id.lv_show);        et_id = (EditText) findViewById(R.id.et_id);        dbHelper = new DBHelper(this);    }    private void listener() {        bt_add.setOnClickListener(this);        bt_delete.setOnClickListener(this);        bt_select.setOnClickListener(this);        bt_update.setOnClickListener(this);    }    private void initData() {        adapter = new MainListViewAdapter(this, getList());        lv_show.setAdapter(adapter);    }    private List<Users> getList() {        // 查询全部        list = new ArrayList<Users>();        sqLiteDatabase = dbHelper.getReadableDatabase();        Cursor cursor = sqLiteDatabase.rawQuery("select * from users", new String[]{});        while (cursor.moveToNext()) {            Users users = new Users();            int id = cursor.getInt(cursor.getColumnIndex("id"));            String username = cursor.getString(cursor.getColumnIndex("username"));            String password = cursor.getString(cursor.getColumnIndex("password"));            users.setId(id);            users.setUsername(username);            users.setPassword(password);            list.add(users);        }        return list;    }    private void refresh() {        list.clear();        list.addAll(getList());        initData();    }    @Override    public void onClick(View v) {        switch (v.getId()) {            case R.id.bt_add:                // 添加                sqLiteDatabase = dbHelper.getWritableDatabase();                sqLiteDatabase.execSQL("insert into users (username,password) values (?,?)", new String[]{et_user.getText().toString(), et_password.getText().toString()});                Toast.makeText(MainActivity.this, "添加成功", Toast.LENGTH_SHORT).show();                et_user.setText("");                et_password.setText("");                refresh();                break;            case R.id.bt_delete:                // 删除                sqLiteDatabase = dbHelper.getWritableDatabase();                sqLiteDatabase.execSQL("delete from users where id > " + et_id.getText().toString() + "");                Toast.makeText(MainActivity.this, "删除成功", Toast.LENGTH_SHORT).show();                et_id.setText("");                refresh();                break;            case R.id.bt_select:                // 查询单个                sqLiteDatabase = dbHelper.getReadableDatabase();                Cursor cursor = sqLiteDatabase.rawQuery("select * from users where id = " + et_id.getText().toString() + "", new String[]{});                if (cursor.moveToFirst()) {                    Users users = new Users();                    int id = cursor.getInt(cursor.getColumnIndex("id"));                    String username = cursor.getString(cursor.getColumnIndex("username"));                    String password = cursor.getString(cursor.getColumnIndex("password"));                    users.setId(id);                    users.setUsername(username);                    users.setPassword(password);                    Toast.makeText(MainActivity.this, users.getUsername(), Toast.LENGTH_SHORT).show();                }                et_id.setText("");                break;            case R.id.bt_update:                // 修改                sqLiteDatabase = dbHelper.getWritableDatabase();                String str = "update users set username = '" + et_user.getText().toString() + "' , password = '" + et_password.getText().toString() + " ' where id = " + et_id.getText().toString() + "";                sqLiteDatabase.execSQL(str);                Toast.makeText(MainActivity.this, "修改成功", Toast.LENGTH_SHORT).show();                et_id.setText("");                et_user.setText("");                et_password.setText("");                refresh();                break;            default:                break;        }    }}

Adapter

package com.zdsoft.sqlite1207.adapter;import android.content.Context;import android.view.LayoutInflater;import android.view.View;import android.view.ViewGroup;import android.widget.BaseAdapter;import android.widget.TextView;import com.zdsoft.sqlite1207.R;import com.zdsoft.sqlite1207.entity.Users;import java.util.ArrayList;import java.util.List;/** * Created by Administrator on 2016/12/7. */public class MainListViewAdapter extends BaseAdapter {    private Context context;    private List<Users> list;    public MainListViewAdapter(Context context, List<Users> list) {        this.context = context;        this.list = list;    }    @Override    public int getCount() {        return list.size();    }    @Override    public Object getItem(int position) {        return null;    }    @Override    public long getItemId(int position) {        return 0;    }    @Override    public View getView(int position, View convertView, ViewGroup parent) {        ViewHolder viewHolder = null;        if (convertView == null) {            viewHolder = new ViewHolder();            convertView = LayoutInflater.from(context).inflate(R.layout.listview_item, null);            viewHolder.tv_id_it = (TextView) convertView.findViewById(R.id.tv_id_it);            viewHolder.tv_user_it = (TextView) convertView.findViewById(R.id.tv_user_it);            viewHolder.tv_password_it = (TextView) convertView.findViewById(R.id.tv_password_it);            convertView.setTag(viewHolder);        } else {            viewHolder = (ViewHolder) convertView.getTag();        }        viewHolder.tv_id_it.setText(String.valueOf(list.get(position).getId()));        viewHolder.tv_user_it.setText(list.get(position).getUsername());        viewHolder.tv_password_it.setText(list.get(position).getPassword());        return convertView;    }    private class ViewHolder {        TextView tv_id_it;        TextView tv_user_it;        TextView tv_password_it;    }}

App

package com.zdsoft.sqlite1207.app;import android.app.Application;import android.database.sqlite.SQLiteDatabase;import com.zdsoft.sqlite1207.db.DBHelper;/** * * Created by Administrator on 2016/12/7. */public class App extends Application {    public DBHelper dbHelper;    @Override    public void onCreate() {        super.onCreate();        //程序启动，完成数据库的初始化工作。        dbHelper = new DBHelper(this);        SQLiteDatabase sb = dbHelper.getReadableDatabase();        sb.close();    }}

DB

package com.zdsoft.sqlite1207.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by Administrator on 2016/12/7. */public class DBHelper extends SQLiteOpenHelper {    /**     * 数据库名称     */    private final static String DB_NAME = "User.db";    /**     * 写构造方法建库     * @param context     */    public DBHelper(Context context) {        super(context, DB_NAME, null, 1);    }    /**     * 建表     * @param db     */    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL("create table users ( id Integer primary key autoincrement , username varchar (100) , password varchar(100))");    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    }}

实体类

package com.zdsoft.sqlite1207.entity;/** * 实体类 * Created by Administrator on 2016/12/7. */public class Users {    private int id;    private String username;    private String password;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }}

layout

<?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_show"        android:layout_width="match_parent"        android:layout_height="0dp"        android:layout_weight="1" />    <LinearLayout        android:layout_width="match_parent"        android:layout_height="wrap_content">        <TextView            android:layout_width="50dp"            android:layout_height="wrap_content"            android:gravity="center_horizontal"            android:text="id:" />        <EditText            android:id="@+id/et_id"            android:layout_width="match_parent"            android:layout_height="wrap_content"            android:hint="请输入id" />    </LinearLayout>    <LinearLayout        android:layout_width="match_parent"        android:layout_height="wrap_content">        <TextView            android:layout_width="50dp"            android:layout_height="wrap_content"            android:gravity="center_horizontal"            android:text="账号：" />        <EditText            android:id="@+id/et_user"            android:layout_width="match_parent"            android:layout_height="wrap_content"            android:hint="请输入用户名"            android:inputType="textPersonName" />    </LinearLayout>    <LinearLayout        android:layout_width="match_parent"        android:layout_height="wrap_content">        <TextView            android:layout_width="50dp"            android:layout_height="wrap_content"            android:gravity="center_horizontal"            android:text="密码：" />        <EditText            android:id="@+id/et_password"            android:layout_width="match_parent"            android:layout_height="wrap_content"            android:hint="请输入密码"            android:inputType="textPassword" />    </LinearLayout>    <LinearLayout        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:orientation="horizontal">        <Button            android:id="@+id/bt_add"            android:layout_width="0dp"            android:layout_height="wrap_content"            android:layout_weight="1"            android:text="添加" />        <Button            android:id="@+id/bt_delete"            android:layout_width="0dp"            android:layout_height="wrap_content"            android:layout_weight="1"            android:text="删除" />        <Button            android:id="@+id/bt_select"            android:layout_width="0dp"            android:layout_height="wrap_content"            android:layout_weight="1"            android:text="查询" />        <Button            android:id="@+id/bt_update"            android:layout_width="0dp"            android:layout_height="wrap_content"            android:layout_weight="1"            android:text="修改" />    </LinearLayout></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="horizontal">    <TextView        android:id="@+id/tv_id_it"        android:layout_width="0dp"        android:layout_height="wrap_content"        android:layout_weight="1" />    <TextView        android:id="@+id/tv_user_it"        android:layout_width="0dp"        android:layout_height="wrap_content"        android:layout_weight="1" />    <TextView        android:id="@+id/tv_password_it"        android:layout_width="0dp"        android:layout_height="wrap_content"        android:layout_weight="1" /></LinearLayout>