·SQLite是安卓内嵌的数据库,它仅占用内存几百kb。
一、数据库的基本操作
·数据库的创建方法:
public class MyHelper extends SQLiteOpenHelper{ //创建一个类继承SQLiteOpenHelper
//构造方法,参数为上下文对象
public MyHelper(Context context){
super(context,"book.db",null,1);//参数1:上下文对象,参数2:数据库名,参数3:游标工厂,参数4:自定义的数据库版本号
}
//第一次创建时调用,所以把创建数据库的语句写在这里。
public void onCreat(SQLiteDatabase db){
db.execSQL("CREATE TABLE information (_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),price INTERGER)");
}
//当数据库的版本号增加时调用
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
//适合写更新数据库的语句
}
}
·添加(插入):
public void insert(String name,String price){ SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("name",name); values.put("price",price); long id = db.inseret("information",null,values); db.close(); }
·修改(更新):
public int update(String name,String price){ SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("price",price); //参数4:占位符的具体内容 int number = db.update("information",values,"name = ?",new String[]{name});//返回更新行数 db.close(); return number; }
·删除:
public int delete(long id){ SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); int number = db.delete("information","_id=?",new String[]{id+""});//根据id删除 db.close(); return number; }
·查询:
public boolean delete(long id){ SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); Cursor cursor = db.query("information",null,"_id=?",new String[]{id+""},null,null,null);//根据id查询,返回一个行数集合Cursor boolean result = cursor.moveToNext(); cursor.close(); db.close(); return result; }
*提示:除了查询以外,增删改还可以使用SQLiteDatabase的execSQL()来执行。比如:
//执行更新 SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("update book set price = 100 where name = ?",new String[]{"三毛"});
二、SQLite中的事务
·已转账为例子:
PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper (getContext()); SQLiteDatabase db = helper.getWritableDatabase(); db.beginTransaction();//开启事务 try(){ db.execSQL("update person set account = account-1000 where name = ?",new String[]{"wangwu"}); db.execSQL("update person set account = account+1000 where name = ?",new String[]{"zhaosi"}); //设置事务成功,当事务结束时才会提交事务。 db.setTransactionSuccessful(); }catch(Exception e){ Log.i("事务处理失败",e.toString()); }finally{ db.endTransaction();//关闭事务 db.close(); }
三、实例演示使用SQLite数据库
·使用数据库操作账号密码的流程如下:
1、简单写一个操作页面:
2、编写数据库的创建类
package com.sjzc.edu; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class MyHelper extends SQLiteOpenHelper {//继承SQLiteOpenHelper public MyHelper(Context context) { super(context, "itcast.db", null, 1);//参数3:游标工厂 } //创建数据库 @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE information (_id INTEGER PRIMARY KEY AUTOINCREMENT,username VARCHAR(20),password VARCHAR(20))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
3、在activity中进行增删改查的操作(重点)
package com.sjzc.edu; import android.content.ContentValues; import android.content.Context; 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.TextView; import android.widget.Toast; public class MainActivity extends AppCompatActivity implements View.OnClickListener { private MyHelper myHelper; private EditText et_username; private EditText et_password; private TextView showText; private Button bt_query; private Button bt_insert; private Button bt_update; private Button bt_delete; private String username; private String password; private SQLiteDatabase db; private ContentValues values; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); //创建数据库 myHelper= new MyHelper(this); initView();//初始化控件 } private void initView() { et_username = findViewById(R.id.username); et_password = findViewById(R.id.password); showText = findViewById(R.id.showText); bt_delete = findViewById(R.id.bt_delete); bt_insert = findViewById(R.id.bt_insert); bt_query = findViewById(R.id.bt_query); bt_update = findViewById(R.id.bt_update); bt_update.setOnClickListener(MainActivity.this); bt_delete.setOnClickListener(MainActivity.this); bt_query.setOnClickListener(MainActivity.this); bt_insert.setOnClickListener(MainActivity.this); } @Override public void onClick(View v) { switch (v.getId()){ case R.id.bt_insert://添加 username = et_username.getText().toString(); password = et_password.getText().toString(); db = myHelper.getReadableDatabase();//获取可读的数据库对象 values = new ContentValues(); values.put("username",username); values.put("password",password); db.insert("information",null,values); db.close(); Toast.makeText(MainActivity.this,"插入成功",Toast.LENGTH_LONG).show(); break; case R.id.bt_query://查询 db = myHelper.getReadableDatabase();//获取可读的数据库对象 Cursor cursor = db.query("information", null, null, null, null, null, null);//返回一个结果集 if (cursor.getCount()==0){ //没有查到数据 Toast.makeText(MainActivity.this,"没有查到数据",Toast.LENGTH_LONG).show(); showText.setText("没有查到数据"); }else{ cursor.moveToFirst(); Toast.makeText(MainActivity.this,cursor.getString(1),Toast.LENGTH_LONG).show(); showText.setText("Username:"+cursor.getString(1)+",Password:"+cursor.getString(2));//
每行数据有两列,分别为username和password
while (cursor.moveToNext()){ showText.append("\n"+"Username:"+cursor.getString(1)+",Password:"+cursor.getString(2)); } } db.close();//用完了要关闭 break; case R.id.bt_update://更新 username = et_username.getText().toString(); db = myHelper.getReadableDatabase();//获取可读的数据库对象 values = new ContentValues(); values.put("password","987654");//要更新的字段和内容 db.update("information",values,"username = ?",new String[]{username}); db.close(); break; case R.id.bt_delete://删除 db = myHelper.getReadableDatabase(); db.delete("information",null,null);//删除所有 Toast.makeText(MainActivity.this,"删除成功",Toast.LENGTH_LONG).show(); showText.setText("无数据"); db.close(); break; } } }
4、运行测试
相关推荐
0评论