android原生SQLite数据库的使用
16lz
2021-01-23
sqlite作为android内置的轻量级数据库 有诸多有点… 也有缺点…
1, 创建一个Openhelper
package com.diandou.demo39_androidsqlite.sql;import android.content.Context;import android.database.DatabaseErrorHandler;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;/** * Created by baiya on 2018/2/7. */public class DemoSQLiteOpenHelper extends SQLiteOpenHelper{ private static final String TAG = "DemoSQLiteOpenHelper"; public DemoSQLiteOpenHelper(Context context) { /** * 参数说明: * * 第一个参数: 上下文 * 第二个参数: 数据库文件名称 * 第三个参数: null代表默认的游标工厂 * 第四个参数: 数据库的版本号 (数据库版本号只增不减) * */ super(context, "demo.db", null, 2); Log.d(TAG,"DemoSQLiteOpenHelper"); } /** * 数据库创建时调用 * * 只有在app第一次安装时才会调用这个方法 * @param db */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL( "create table contactinfo (" + "id integer primary key autoincrement, " + "name varchar(20), " + "phone varchar(20))" ); Log.d(TAG,"onCreate"); } /** * 数据库升级时调用 * * 只有在上面super执行的时候才会执行这个方法 * * @param db * @param oldVersion * @param newVersion */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.d(TAG,"onUpgrade + oldVersion: " + oldVersion + "newVersion: " + newVersion); switch (oldVersion) { case 2: db.execSQL( "create table contactinfo2 (" + "id integer primary key autoincrement, " + "name varchar(20), " + "phone varchar(20))" ); // 判断版本号.如果是老版本, 走upgrade逻辑,创建新的表 break; case 3: db.execSQL("alter table contactinfo add category_id integer"); default: } }}2,创建一个dao
package com.diandou.demo39_androidsqlite.sql;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;/** * Created by baiya on 2018/2/7. */public class DemoDao { private DemoSQLiteOpenHelper helper; public DemoDao(Context context) { helper = new DemoSQLiteOpenHelper(context); } /** * 添加一条记录 * @param name 联系人姓名 * @param phone 联系人电话 * @return 返回的是添加后在数据库的行号 -1代表添加失败 */ public long add(String name, String phone, String category_id){ SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("insert into contactinfo (name,phone) values (?,?)", new Object[]{name,phone}); ContentValues values = new ContentValues(); values.put("name", name); values.put("phone", phone); values.put("category_id", category_id); //内部是组拼sql语句实现的. long rowid = db.insert("contactinfo", null, values); //记得释放数据库资源 db.close(); return rowid; } /** * 根据姓名删除一条记录 * @param name 要删除的联系人的姓名 * @return 返回0代表的是没有删除任何的记录 返回整数int值代表删除了几条数据 */ public int delete(String name){ //判断这个数据是否存在. SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("delete from contactinfo where name=?", new Object[]{name}); int rowcount = db.delete("contactinfo", "name=?", new String[]{name}); db.close(); //再从数据库里面查询一遍,看name是否还在 return rowcount; } /** * 修改联系人电话号码 * @param newphone 新的电话号码 * @param name 要修改的联系人姓名 * @return 0代表一行也没有更新成功, >0 整数代表的是更新了多少行记录 */ public int update(String newphone , String name){ //根据姓名修改电话号码 SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("update contactinfo set phone =? where name=?", new Object[]{newphone,name}); ContentValues values = new ContentValues(); values.put("phone", newphone); int rowcount = db.update("contactinfo", values, "name=?", new String[]{name}); db.close(); return rowcount; } /** * 查询联系人的电话号码 * @param name 要查询的联系人 * @return 电话号码 */ public String getPhoneNumber(String name){ String phone = null; SQLiteDatabase db = helper.getReadableDatabase(); //Cursor cursor = db.rawQuery("select phone from contactinfo where name=?", new String[]{name}); Cursor cursor = db.query("contactinfo", new String[]{"phone"}, "name=?", new String[]{name}, null, null, null); if(cursor.moveToNext()){//如果光标可以移动到下一位,代表就是查询到了数据 phone = cursor.getString(0); } cursor.close();//关闭掉游标,释放资源 db.close();//关闭数据库,释放资源 return phone; } /** * 获取所有数据 * @return */ public String getAll(){ String all = ""; SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.query("contactinfo", new String[]{"phone", "name", "category_id"}, null, null, null, null, null); while (cursor.moveToNext()){//如果光标可以移动到下一位,代表就是查询到了数据 all += cursor.getString(0)+"..."+cursor.getString(1)+"..."+cursor.getString(2)+"///"; } cursor.close();//关闭掉游标,释放资源 db.close();//关闭数据库,释放资源 return all; } /** * 查询所有表名 * @return */ public String updateField(){ String allTable = ""; SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select name from sqlite_master where type='table' order by name", null); while(cursor.moveToNext()){ //遍历出表名 String name = cursor.getString(0); allTable += name+"...."; } return allTable; }}3, App启动入口记得初始化一次,别忘了配置
package com.diandou.demo39_androidsqlite;import android.app.Application;import com.diandou.demo39_androidsqlite.sql.DemoSQLiteOpenHelper;/** * Created by baiya on 2018/2/22. */public class App extends Application { @Override public void onCreate() { super.onCreate(); initSqlite(); } /** * 初始化数据库 */ private void initSqlite() { DemoSQLiteOpenHelper openHelper = new DemoSQLiteOpenHelper(this); }}4,在activity中测试
package com.diandou.demo39_androidsqlite;import android.support.v7.app.AppCompatActivity;import android.os.Bundle;import android.text.TextUtils;import android.view.View;import android.widget.EditText;import android.widget.Toast;import com.diandou.demo39_androidsqlite.sql.DemoDao;public class MainActivity extends AppCompatActivity { private DemoDao demoDao; private EditText mEtName; private EditText mEtPhone; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); mEtName = (EditText) findViewById(R.id.mEtName); mEtPhone = (EditText) findViewById(R.id.mEtPhone); demoDao = new DemoDao(this); } public void add(View view){ String name = mEtName.getText().toString(); String phone = mEtPhone.getText().toString(); String category_id = "fdfdfd"; long add = demoDao.add(name, phone,category_id); if (add != -1){ Toast.makeText(this, "添加成功" + add, 0).show(); } } public void delete(View view){ String name = mEtName.getText().toString(); int delete = demoDao.delete(name); if (delete != 0){ Toast.makeText(this, "删除成功" + delete, 0).show(); } } public void update(View view){ String name = mEtName.getText().toString(); String phone = mEtPhone.getText().toString(); //根据phone更新name int update = demoDao.update(phone, name); if (update != 0){ Toast.makeText(this, "更新成功" + update, 0).show(); } } public void query(View view){// String name = mEtName.getText().toString();// String phoneNumber = demoDao.getPhoneNumber(name);// String phoneNumber_1 = demoDao.getPhoneNumber(name);// if (!TextUtils.isEmpty(phoneNumber_1)) {// Toast.makeText(this, "查询成功" + phoneNumber_1, 0).show();// } String all = demoDao.getAll(); if (!TextUtils.isEmpty(all)) { Toast.makeText(this, "查询成功--" + all, 0).show(); } } public void allTableName(View view) { String all = demoDao.updateField(); if (!TextUtils.isEmpty(all)) { Toast.makeText(this, "查询成功--" + all, 0).show(); } }}sqlite表的字段不可以更改, 但是可以添加
update方法中就是升级用的, onCreate方法只是在app第一次安装到手机上会调用一次, 创建完表之后就不会在走了
所以, 如果是在update中创建表, 或是增加字段的话, 记得在onCreate中也写一份