Android_SQL增删改查

OpenHelper：

public class MyDbOpenHelper extends SQLiteOpenHelper{    private final static String dbName="Words.db";    private final static int version=1;    public MyDbOpenHelper(Context context){        super(context,dbName,null,version);    }    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL("create table if not exists word(_id integer primary key autoincrement,name varchar(20),mark varchar(100),time varchar(50))");    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        if(newVersion>oldVersion){//做数据库版本更新        }    }

MainActivity：

public class MainActivity extends AppCompatActivity {    private ListView lv;    SQLiteDatabase db;    MyDbOpenHelper helper;    EditText et_word;//单词编辑框    EditText et_mark;//备注编辑框    SimpleCursorAdapter adapetr;//专门用于数据库的适配器    int id=0;//选中项的id    AlertDialog dialog;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        lv = (ListView) findViewById(R.id.lv);        helper=new MyDbOpenHelper(this);//实例化数据库帮助类        db=helper.getWritableDatabase();//获取一个可读写权限的SQLiteDatabase        //页面进来就查询数据，并展示到ListView        Cursor cursor = db.query("word", null, null, null, null, null, null);        //参数1.上下文对象，2.加载的布局文件id,3.查询返回的结果集，4.取出列的值，5.把取出的值绑定到相对应的控件上，6.标记        adapetr=new SimpleCursorAdapter(this,R.layout.item_1,cursor,                new String[]{"name","time"},new int[]{R.id.tv_item_wordName,R.id.tv_item_lastTime},                CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);        lv.setAdapter(adapetr);        registerForContextMenu(lv);//给lv注册上下文菜单    }    //增加单词    public void btnAdd(View view){        View inflate = View.inflate(this, R.layout.cz_dialog, null);        et_word=(EditText)inflate.findViewById(R.id.et_word);        et_mark=(EditText)inflate.findViewById(R.id.et_mark);        Button btnok=(Button)inflate.findViewById(R.id.btn_com);        Button btncancel=(Button)inflate.findViewById(R.id.btn_can);        btnok.setOnClickListener(new Click1());        btncancel.setOnClickListener(new Click1());        dialog=new AlertDialog.Builder(this).setTitle("增加新单词").                setView(inflate).show();    }    class Click1 implements View.OnClickListener{        @Override        public void onClick(View v) {            switch (v.getId()){                case R.id.btn_com://确认                    String word=et_word.getText().toString();//获取输入的单词                    String mark=et_mark.getText().toString();//                    //获取当前时间                    Calendar cal=Calendar.getInstance();                    Date time = cal.getTime();                    //转换时间格式                    SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                    String date = format.format(time);                    //往数据库里更新的数据的集合                    ContentValues cv=new ContentValues();                    cv.put("name",word);                    cv.put("mark",mark);                    cv.put("time",date);                    long word1 = db.insert("word", null, cv);                    if(word1==1){//说明插入成功                        Toast.makeText(MainActivity.this,"插入数据成功",Toast.LENGTH_SHORT).show();                        Cursor cursor = db.query("word", null, null, null, null, null, null);                        adapetr.changeCursor(cursor);//把新查询到的数据通知adapter更新                    }                    dialog.dismiss();                    break;                case R.id.btn_can://取消                    break;            }        }    }    @Override    public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) {        super.onCreateContextMenu(menu, v, menuInfo);        getMenuInflater().inflate(R.menu.menu1,menu);    }    @Override    public boolean onContextItemSelected(MenuItem item) {        AdapterView.AdapterContextMenuInfo info=(AdapterView.AdapterContextMenuInfo)item.getMenuInfo();        int position=info.position;        switch (item.getItemId()){            case R.id.update://修改                View inflate = View.inflate(this, R.layout.cz_dialog, null);                et_word=(EditText)inflate.findViewById(R.id.et_word);                et_mark=(EditText)inflate.findViewById(R.id.et_mark);                Button btnok=(Button)inflate.findViewById(R.id.btn_com);                Button btncancel=(Button)inflate.findViewById(R.id.btn_can);                btnok.setOnClickListener(new ClickUpdate());                btncancel.setOnClickListener(new ClickUpdate());                //把当前选中项的信息取出并赋到相应的输入框                Cursor cursor3=(Cursor) adapetr.getItem(position);                et_word.setText(cursor3.getString(1));                et_mark.setText(cursor3.getString(2));                id=cursor3.getInt(0);                dialog=new AlertDialog.Builder(this).setTitle("增加新单词").                        setView(inflate).show();                break;            case R.id.delete://删除                //取出当前行对应的数据，强转成Cursor                Cursor cursor=(Cursor) adapetr.getItem(position);                db.delete("word","_id=?",new String[]{String.valueOf(cursor.getInt(0))});                Cursor cursor2 = db.query("word", null, null, null, null, null, null);                adapetr.changeCursor(cursor2);//把新查询到的数据通知adapter更新                break;        }        return super.onContextItemSelected(item);    }    class ClickUpdate implements View.OnClickListener{        @Override        public void onClick(View v) {            switch (v.getId()){                case R.id.btn_com://修改=更新                    ContentValues cv=new ContentValues();                    cv.put("name",et_word.getText().toString());                    cv.put("mark",et_mark.getText().toString());                    int result=db.update("word",cv,"_id=?",new String[]{String.valueOf(id)});                    if(result==1){//修改成功                        Cursor cursor2 = db.query("word", null, null, null, null, null, null);                        adapetr.changeCursor(cursor2);//把新查询到的数据通知adapter更新                    }                    dialog.dismiss();                    break;                case R.id.btn_can://取消                    dialog.dismiss();                    break;            }        }    }}

布局：

activity_main布局：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:orientation="vertical"    android:layout_width="match_parent"    android:layout_height="match_parent">    <Button        android:id="@+id/add"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:text="增加数据"        android:onClick="btnAdd"        />    <ListView        android:layout_width="match_parent"        android:layout_height="match_parent"        android:id="@+id/lv"/></LinearLayout>

dialog：

<?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:layout_width="match_parent"     android:layout_height="wrap_content"     android:id="@+id/tv_id"/>  <EditText     android:layout_width="match_parent"    android:layout_height="wrap_content"    android:id="@+id/et_word"    android:hint="请输入单词"/><EditText     android:layout_width="match_parent"    android:layout_height="wrap_content"    android:id="@+id/et_mark"    android:hint="请输入备注信息"/><LinearLayout     android:layout_width="match_parent"    android:layout_height="wrap_content"    android:orientation="horizontal">    <Button         android:layout_width="0dp"        android:layout_weight="1"        android:layout_height="wrap_content"        android:text="确认"        android:layout_marginRight="20dp"        android:layout_marginLeft="10dp"        android:id="@+id/btn_com"        android:onClick="click"/>    <Button         android:layout_width="0dp"        android:layout_weight="1"        android:layout_height="wrap_content"        android:text="取消"        android:layout_marginRight="10dp"        android:id="@+id/btn_can"        android:onClick="click"/></LinearLayout></LinearLayout>

item：

<?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:layout_width="match_parent"     android:layout_height="wrap_content"     android:id="@+id/tv_id"/>  <EditText     android:layout_width="match_parent"    android:layout_height="wrap_content"    android:id="@+id/et_word"    android:hint="请输入单词"/><EditText     android:layout_width="match_parent"    android:layout_height="wrap_content"    android:id="@+id/et_mark"    android:hint="请输入备注信息"/><LinearLayout     android:layout_width="match_parent"    android:layout_height="wrap_content"    android:orientation="horizontal">    <Button         android:layout_width="0dp"        android:layout_weight="1"        android:layout_height="wrap_content"        android:text="确认"        android:layout_marginRight="20dp"        android:layout_marginLeft="10dp"        android:id="@+id/btn_com"        android:onClick="click"/>    <Button         android:layout_width="0dp"        android:layout_weight="1"        android:layout_height="wrap_content"        android:text="取消"        android:layout_marginRight="10dp"        android:id="@+id/btn_can"        android:onClick="click"/></LinearLayout></LinearLayout>

menu：

<?xml version="1.0" encoding="utf-8"?><menu xmlns:android="http://schemas.android.com/apk/res/android">    <item        android:id="@+id/update"        android:title="修改"        />    <item        android:id="@+id/delete"        android:title="删除"        /></menu>