实现一个使用SQLite增删改查数据的小例子,点击按钮执行相关操作。
MySqlieHelper是创建数据库的类:
package com.engineer.shizhibin.sqlitedemo;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by 13922 on 2018/6/10.
*/
public class MySqliteHelper extends SQLiteOpenHelper {
public MySqliteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
public MySqliteHelper(Context context) {
super(context,Constant.DB_NAME,null, Constant.DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table student (id integer primary key,name varchar(10),age integer)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
Constant是保存常量的类:
package com.engineer.shizhibin.sqlitedemo;
/**
* Created by 13922 on 2018/6/10.
*/
public class Constant {
public static final String DB_NAME = "db_info";
public static final int DB_VERSION = 1;
public static final String TB_NAME= "user";
}
为了解耦,不在MainActivity中直接操作数据库。使用DbManager类进行相关操作:
package com.engineer.shizhibin.sqlitedemo;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
/**
* Created by 13922 on 2018/6/10.
*/
public class DbManager {
private static MySqliteHelper helper;
public static MySqliteHelper getInstance(Context context) {
if (helper == null) {
helper = new MySqliteHelper(context);
}
return helper;
}
public static void execSql(SQLiteDatabase db,String sql) {
if(db != null && !"".equals(sql)) {
db.execSQL(sql);
}
}
public static Cursor query(SQLiteDatabase db,String sql) {
Cursor c = db.rawQuery(sql,null);
return c;
}
}
MainActivity类:
package com.engineer.shizhibin.sqlitedemo;
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.TextView;
public class MainActivity extends AppCompatActivity {
private MySqliteHelper helper;
private TextView tv_result;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper = DbManager.getInstance(this);
tv_result = this.findViewById(R.id.result);
}
public void createDb(View view) {
SQLiteDatabase db = helper.getReadableDatabase();
}
public void click(View view) {
StringBuffer result = new StringBuffer("");
switch (view.getId()) {
case R.id.insert:
SQLiteDatabase db = helper.getReadableDatabase();
String sql = "insert into student values(1,'Tom',21)";
String sql2 = "insert into student values(2,'Bob',20)";
DbManager.execSql(db,sql);
DbManager.execSql(db,sql2);
db.close();
break;
case R.id.update:
db = helper.getReadableDatabase();
String update = "update student set name ='Jack' where id = 2";
DbManager.execSql(db,update);
result.delete(0,result.length());
db.close();
break;
case R.id.delete:
db = helper.getReadableDatabase();
String delete = "delete from student where id = 2";
DbManager.execSql(db,delete);
result.delete(0,result.length());
db.close();
break;
case R.id.query:
db = helper.getReadableDatabase();
String query = "select * from student";
Cursor c = DbManager.query(db,query);
while(c.moveToNext()) {
int id = c.getInt(0);
String name = c.getString(1);
int age = c.getInt(2);
result.append(id).append(name).append(age);
}
tv_result.setText(result);
db.close();
break;
}
}
}
.xml文件:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context="com.engineer.shizhibin.sqlitedemo.MainActivity">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="创建数据库"
android:onClick="createDb"
android:background="#ff0"/>
<Button
android:id="@+id/insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="插入数据"
android:onClick="click"
android:background="#ff0"/>
<Button
android:id="@+id/update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="修改数据"
android:onClick="click"
android:background="#ff0"/>
<Button
android:id="@+id/delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="删除数据"
android:onClick="click"
android:background="#ff0"/>
<Button
android:id="@+id/query"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="查询数据"
android:onClick="click"
android:background="#ff0"/>
<TextView
android:id="@+id/result"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="40dp"
android:textSize="40dp"
android:text="展示查询结果"/>
</LinearLayout>
程序效果:
1.点击创建数据库,并点击插入数据,然后查询数据:
有两个学生信息,1号 Tom 21岁 2号 Bob 20岁
2.点击修改,查询 Bob被改成了Jack:
3.点击删除,查询只剩下了1号同学: