Android的使用SQLite

package com.example.dell.testsqlite; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.util.Log; import android.view.View; import android.widget.Button; import java.util.ArrayList; import java.util.List; public class MainActivity extends AppCompatActivity implements View.OnClickListener { private Button insert; private Button update; private Button delete; private Button queryAll; private Button queryCondition; private MyDatabaseHelper mydbHelper; private SQLiteDatabase writdb; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); insert = (Button) findViewById(R.id.insert); update = (Button) findViewById(R.id.update); delete = (Button) findViewById(R.id.delete); queryAll = (Button) findViewById(R.id.query_all); queryCondition = (Button) findViewById(R.id.query_condition); insert.setOnClickListener(this); update.setOnClickListener(this); delete.setOnClickListener(this); queryAll.setOnClickListener(this); queryCondition.setOnClickListener(this); } @Override public void onClick(View v) { switch (v.getId()) { case R.id.insert: mydbHelper = new MyDatabaseHelper(this, "school", null, 1); writdb = mydbHelper.getWritableDatabase(); Student insertStu = new Student(); insertStu.setNumber("1238409"); insertStu.setName("小明"); insertStu.setAge(21); //获取数据 ContentValues cv = new ContentValues(); cv.put("number", insertStu.getNumber()); cv.put("name", insertStu.getName()); cv.put("age", insertStu.getAge()); //向数据库插入数据 writdb.insert("student", null, cv); /*writdb.execSQL("insert into student(number,name,age) values(?,?,?)", new String[]{insertStu.getNumber(), insertStu.getName(), String.valueOf(insertStu.getAge())});*/ writdb.close(); mydbHelper.close(); break; case R.id.update: mydbHelper = new MyDatabaseHelper(this, "school", null, 1); writdb = mydbHelper.getWritableDatabase(); Student updatetStu = new Student(); updatetStu.setNumber("222222"); updatetStu.setAge(43); //获取要更新的数据 ContentValues cv1 = new ContentValues(); cv1.put("number", updatetStu.getNumber()); cv1.put("age", updatetStu.getAge()); //参数说明：①表名，②更新的数据，③条件，④条件值 writdb.update("student", cv1, "id > ?", new String[]{"1"}); /*writdb.execSQL("update student set number = ?,age = ? where id > ?", new String[]{updatetStu.getNumber(), String.valueOf(updatetStu.getAge()),"1"});*/ writdb.close(); mydbHelper.close(); break; case R.id.delete: mydbHelper = new MyDatabaseHelper(this, "school", null, 1); writdb = mydbHelper.getWritableDatabase(); writdb.delete("student", "id = ?", new String[]{"2"}); //writdb.execSQL("delete from student where id = ?",new String[]{"2"}); break; case R.id.query_all: mydbHelper = new MyDatabaseHelper(this, "school", null, 1); writdb = mydbHelper.getWritableDatabase(); //创建一个集合用于存放查询到的数据 List<Student> students2 = new ArrayList<>(); //参数说明：①表名（使用全局常量，避免写错），②要查找的列名，③约束条件，④约束条件的值，⑤分组，⑥分组后的约束，⑦排序 Cursor cursor = writdb.query("student", null, null, null, null, null, null); //Cursor cursor1 = writdb.rawQuery("SELECT * FROM student",null); if (cursor != null) { while (cursor.moveToNext()) { Student stu = new Student(); //获取一行中的数据 stu.setId(cursor.getInt(cursor.getColumnIndex("id"))); stu.setNumber(cursor.getString(cursor.getColumnIndex("number"))); stu.setName(cursor.getString(cursor.getColumnIndex("name"))); stu.setAge(cursor.getInt(cursor.getColumnIndex("age"))); //添加到集合里 students2.add(stu); } //记得关闭 cursor.close(); } for (Student student2 : students2) { Log.e("查询结果", student2.toString()); } break; case R.id.query_condition: mydbHelper = new MyDatabaseHelper(this, "school", null, 1); writdb = mydbHelper.getWritableDatabase(); List<Student> students3 = new ArrayList<>(); //查询student表的id，number，name的列，条件是id > 7，根据id降序 Cursor cursor1 = writdb.query("student", new String[]{"id", "number", "name"}, "id > ?", new String[]{"7"}, null, null, "id desc"); /*Cursor cursor1 = writdb.rawQuery("SELECT id, number, name FROM student WHERE id > ? ORDER BY id desc", new String[]{"7"});*/ if (cursor1 != null) { while (cursor1.moveToNext()) { Student stu = new Student(); stu.setId(cursor1.getInt(cursor1.getColumnIndex("id"))); stu.setNumber(cursor1.getString(cursor1.getColumnIndex("number"))); stu.setName(cursor1.getString(cursor1.getColumnIndex("name"))); students3.add(stu); } cursor1.close(); } writdb.close(); mydbHelper.close(); for (Student student2 : students3) { Log.e("查询结果", student2.toString()); } break; } } }