首先要用一个类来继承SQLiteOpenHelper,并必须实现
public DatabaseHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); }
具体类如下(关键地方我都加了注释):
package com.example.sqllitedemo; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; public class DatabaseHelper extends SQLiteOpenHelper { private static final int VERSION = 2; // SQLiteOpenHelper子类必须有该构造函数 // 第一次参数Activity对象,第二个表名,第三个暂时空值,第四个数据库的版本号 public DatabaseHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); // TODO Auto-generated constructor stub } public DatabaseHelper(Context context, String name, int version) { this(context, name, null, version); } public DatabaseHelper(Context context, String name) { this(context, name, VERSION); } // 该函数第一次创建数据库的时候执行,实际上是在第一次得到SqliteDatabase对象的时候执行 @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub System.out.println("create a database"); db.execSQL("create table user(id int,name verchar(20))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub System.out.println("update a database"); } }
调用代码如下:
package com.example.sqllitedemo; import android.app.Activity; import android.app.ActionBar; import android.app.Fragment; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.view.View.OnClickListener; import android.widget.ArrayAdapter; import android.widget.Button; import android.widget.AutoCompleteTextView; public class MainActivity extends Activity implements OnClickListener { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Button btnCreate = (Button) findViewById(R.id.btnCreateDatabase); btnCreate.setOnClickListener(this); Button btnUpdatDatabase = (Button) findViewById(R.id.btnUpdateDatabase); btnUpdatDatabase.setOnClickListener(this); Button btnInsert = (Button) findViewById(R.id.btnInsert); btnInsert.setOnClickListener(this); Button btnUpdate = (Button) findViewById(R.id.btnUpdate); btnUpdate.setOnClickListener(this); Button btnQuery = (Button) findViewById(R.id.btnQuery); btnQuery.setOnClickListener(this); } @Override public void onClick(View v) { // TODO Auto-generated method stub switch (v.getId()) { case R.id.btnCreateDatabase: DatabaseHelper helper = new DatabaseHelper(this, "School_db"); SQLiteDatabase db = helper.getReadableDatabase(); break; case R.id.btnUpdateDatabase: DatabaseHelper helper1 = new DatabaseHelper(this, "School_db", 2); SQLiteDatabase db1 = helper1.getReadableDatabase(); break; case R.id.btnInsert: // 用于存储数据的键值对,键为列名 ContentValues values = new ContentValues(); values.put("name", "张山"); values.put("id", 1); DatabaseHelper helper2=new DatabaseHelper(this,"School_db"); SQLiteDatabase db2=helper2.getWritableDatabase(); db2.insert("user", null, values); //方法二、直接使用ExecSQL db2.execSQL("insert into user(id,name) values(?,?)", new Object[]{2,"李四"}); break; case R.id.btnUpdate: DatabaseHelper helper3=new DatabaseHelper(this,"School_db"); SQLiteDatabase db3=helper3.getWritableDatabase(); ContentValues values1=new ContentValues(); values1.put("name", "zhangshan"); db3.update("user", values1, "id=?" , new String[]{"1"}); break; case R.id.btnQuery: DatabaseHelper helper4=new DatabaseHelper(this,"School_db"); SQLiteDatabase db4=helper4.getReadableDatabase(); Cursor cursor=db4.query("user",new String[]{"id","name"},"id=?",new String[]{"1"}, "","",""); while(cursor.moveToNext()) { String name=cursor.getString(cursor.getColumnIndex("name")); System.out.println(name); } break; default: break; } } }
除了上述的crud的方法,还可以使用下面的方式
DatabaseHelper myDBHelper=new DatabaseHelper(this,"School_db"); //增加 public void addPerson(String name) { SQLiteDatabase database = myDBHelper.getWritableDatabase(); //先判断数据库是否可用 if (database.isOpen()) { database.execSQL("insert into user(name) values(?,?)", new Object[]{name}); database.close(); } } //查找 public boolean findPerson(String name) { boolean result = false; SQLiteDatabase database = myDBHelper.getReadableDatabase(); if (database.isOpen()) { Cursor cursor = database.rawQuery("select * from user where name=?", new String[]{name}); if (cursor.moveToFirst()) {//游标是否移动到下一行,如果是,那说明有数据返回 Log.d(tag, "count:" + cursor.getColumnCount()); int nameIndex = cursor.getColumnIndex("name"); Log.d(tag, "name:" + cursor.getString(nameIndex)); cursor.close(); result = true; } else { result = false; } database.close(); } return result; } //删除一条数据 public void deletePerson(String name) { SQLiteDatabase database = myDBHelper.getWritableDatabase(); if (database.isOpen()) { database.execSQL("delete from user where name=?", new Object[]{name}); } database.close(); } //更新一条数据 public void updatePerson( String newName,String name) { SQLiteDatabase database = myDBHelper.getWritableDatabase(); if (database.isOpen()) { database.execSQL("update user set name=? where name=?", new Object[]{newName, name}); } database.close(); } //查找所有person public List<user> findAllPerson(){ List<user> userList = new ArrayList<user>(); SQLiteDatabase database = myDBHelper.getReadableDatabase(); if(database.isOpen()){ Cursor cursor = database.rawQuery("select * from user", null); while(cursor.moveToNext()){ int nameIndex = cursor.getColumnIndex("name"); Person person = new Person(name); personList.add(user); } } database.close(); return uerList; }