博主前些天发现了一个巨牛的人工智能学习网站,通俗易懂,风趣幽默,忍不住也分享一下给大家,
👉点击跳转到网站
SQLite建表注意事项
SQLite相关的API如下
1.创建一个类MyUserlogDBHelper去继承SQLiteOpenHelper,在该类里面创建表。
public class MyUserlogDBHelper extends SQLiteOpenHelper {
public MyUserlogDBHelper db;
//对外提供参数,单例模式
private static SQLiteOpenHelper mHelper;
public static synchronized SQLiteOpenHelper getInstance(Context context){
if (mHelper==null){
mHelper=new MyUserlogDBHelper(context,"user_log",null,1);
}
return mHelper;
}
private MyUserlogDBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
//创建表
String sql="create table user_log (_id integer primary key autoincrement,name text)";
//拿到db执行sql语句即可
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2.在Activity中写查询,增加,修改,删除数据的操作。
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
public void create(View view) {
SQLiteOpenHelper helper = MyUserlogDBHelper.getInstance(this);
SQLiteDatabase readableDatabase = helper.getReadableDatabase();
}
//查询语句
public void query(View view) {
SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
SQLiteDatabase db = instance.getReadableDatabase();
if (db.isOpen()){
//查询语句
String sql="select * from user_log";
//获取游标
Cursor cursor = db.rawQuery(sql, null);
while(cursor.moveToNext()){
//不规范的写法
/*int _id=cursor.getColumnIndex("0");
int name=cursor.getColumnIndex("1");
*/
//规范的写法
int _id=cursor.getInt(cursor.getColumnIndex("_id"));
String name=cursor.getString(cursor.getColumnIndex("name"));
Log.d("数据库", "_id: "+_id+",name:"+name);
}
//关闭游标
cursor.close();
//关闭数据库
db.close();
}
}
//插入语句
public void insert(View view) {
SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
SQLiteDatabase db = instance.getWritableDatabase();
//判断数据库是否开启
if (db.isOpen()){
String sql="insert into user_log(name) values('路宇')";
db.execSQL(sql);
}
db.close();
}
//修改
public void update(View view) {
SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
SQLiteDatabase db = instance.getWritableDatabase();
if (db.isOpen()){
String sql="update user_log set name=? where _id=?";
db.execSQL(sql,new Object[]{"成龙","5"});
}
//关闭数据库
db.close();
}
//删除数据
public void delete(View view) {
SQLiteOpenHelper instance = MyUserlogDBHelper.getInstance(this);
SQLiteDatabase db = instance.getWritableDatabase();
//判断数据是否是打开状态
if (db.isOpen()){
String sql="delete from user_log where _id=?";
db.execSQL(sql,new Object[]{6});
}
db.close();//关闭数据库操作
}
}
3.布局文件。
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity"
android:orientation="vertical"
>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="创建表"
android:onClick="create" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="查询"
android:onClick="query" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入"
android:onClick="insert" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改"
android:onClick="update" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除"
android:onClick="delete" />
</LinearLayout>
以上就是SQLite数据库中,最简单的增删改查操作!
下面看第二个实例如下
1、创建DBTestHelper类
public class DBTestHelper extends SQLiteOpenHelper {
private static DBTestHelper dbTestHelper = null;
public DBTestHelper(@Nullable Context context, int version) {
super(context, "ly.db", null, version);
}
/**
* 什么时候才会创建数据库文件?
* 1.数据库文件不存在
* 2.连接数据库
* <p>
* 什么时候调用该方法?
* 当数据库文件创建时调用
*
* @param db
*/
@Override
public void onCreate(SQLiteDatabase db) {
Log.i("TAG", "DBTestHelper onCreate() ");
String sql = "CREATE TABLE person(" +
" _id INTEGER PRIMARY KEY AUTOINCREMENT," +
" name VARCHAR," +
" age INT" +
");";
db.execSQL(sql);
//插入一些初始化的值
db.execSQL("INSERT INTO person(name,age) VALUES('Tom1',11)");
db.execSQL("INSERT INTO person(name,age) VALUES('Tom2',13)");
db.execSQL("INSERT INTO person(name,age) VALUES('Tom3',15)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i("TAG", "DBTestHelper onUpgrade() ");
}
}
2、布局效果如下
3、DataBasesActivity类具体的实现方法
public class DataBasesActivity extends AppCompatActivity {
private Button btn_create;
private Button btn_update;
private Button btn_delete;
private Button btn_insert;
private Button btn_query;
private Button btn_update_record;
private Button btn_test_transaction;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_data_bases);
btn_create = findViewById(R.id.btn_create);
btn_update = findViewById(R.id.btn_update);
btn_delete = findViewById(R.id.btn_delete);
btn_insert = findViewById(R.id.btn_insert);
btn_query = findViewById(R.id.btn_query);
btn_update_record = findViewById(R.id.btn_update_record);
btn_test_transaction = findViewById(R.id.btn_test_transaction);
btn_create.setOnClickListener(onClickListener);
btn_update.setOnClickListener(onClickListener);
btn_delete.setOnClickListener(onClickListener);
btn_insert.setOnClickListener(onClickListener);
btn_query.setOnClickListener(onClickListener);
btn_update_record.setOnClickListener(onClickListener);
btn_test_transaction.setOnClickListener(onClickListener);
}
private final View.OnClickListener onClickListener = new View.OnClickListener() {
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.btn_create://创建库
DBTestHelper dbTestHelper = new DBTestHelper(DataBasesActivity.this, 1);
//当执行完该方法后,才会执行DBTestHelper类中的 onCreate()方法:进行数据库表的创建与初始化
SQLiteDatabase database = dbTestHelper.getReadableDatabase();
Toast.makeText(DataBasesActivity.this, "创建数据库!", Toast.LENGTH_SHORT).show();
break;
case R.id.btn_update: //更新库
DBTestHelper dbTestHelper1 = new DBTestHelper(DataBasesActivity.this, 2);
//执行该方法后,会执行数据库用于版本更新的方法onUpgrade()
SQLiteDatabase database1 = dbTestHelper1.getReadableDatabase();
Toast.makeText(DataBasesActivity.this, "更新数据库!", Toast.LENGTH_SHORT).show();
break;
case R.id.btn_delete: //删除记录
deleteRecord();
break;
case R.id.btn_insert://添加记录
insertRecord();
break;
case R.id.btn_query: //查询记录
queryRecord();
break;
case R.id.btn_update_record: //更新记录
updateRecord();
break;
case R.id.btn_test_transaction: //测试事务
testTransaction();
break;
default:
break;
}
}
};
/**
* 测试事务处理
* UPDATE age=16 FROM person WHERE _id = 1;
* UPDATE age=17 FROM person WHERE _id = 3;
* <p>
* 一个功能中对数据库进行的多个操作:要么都成功,要么都失败!
* <p>
* 事务处理的3步:
* 1.开启事务(获取连接后)
* 2.设置事务成功(再全部正常执行)
* 3.结束事务(finally中)
*/
private void testTransaction() {
SQLiteDatabase database = null;
try {
DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
//得到连接
database = dbTestHelper3.getReadableDatabase();
//1.开启事务(获取连接后)
database.beginTransaction();
//执行update UPDATE person SET name='Jack',age=21 WHERE id=4;
ContentValues contentValues = new ContentValues();
contentValues.put("age", 16);
int updateCount = database.update("person", contentValues, "_id=?", new String[]{"1"});
Log.i("TAG", "updateCount: " + updateCount);
//出了异常
boolean flag = true;
if (flag) {
throw new RuntimeException("出异常了!");
}
contentValues = new ContentValues();
contentValues.put("age", 17);
int updateCount2 = database.update("person", contentValues, "_id=?", new String[]{"3"});
Log.i("TAG", "updateCount2: " + updateCount2);
// 2.设置事务成功(再全部正常执行)
database.setTransactionSuccessful();
} catch (RuntimeException e) {
e.printStackTrace();
} finally {
if (database != null) {
//3.结束事务(finally中)
database.endTransaction();
database.close();
}
}
}
//查询记录
private void queryRecord() {
DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
//得到连接
SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
// Cursor cursor = database.query("person", null, "_id=?", new String[]{"3"}, null, null, null); //查询id为3的
//查询所有的记录
Cursor cursor = database.query("person", null, null, null, null, null, null);
int count = cursor.getCount();
while (cursor.moveToNext()) {
int rowid = cursor.getInt(cursor.getColumnIndex("rowid"));
int id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(2);
Log.i("TAG", "id-" + id + "name-" + name + "age-" + age + "rowid: " + rowid);
}
cursor.close();
database.close();
Toast.makeText(this, "count=" + count, Toast.LENGTH_SHORT).show();
}
//删除记录
private void deleteRecord() {
DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
//得到连接
SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
//执行delete DELETE FROM person WHERE _id=2
int deleteCount = database.delete("person", "_id=?", new String[]{"2"});
database.close();
Toast.makeText(this, "deleteCount=" + deleteCount, Toast.LENGTH_SHORT).show();
}
//更新记录
private void updateRecord() {
DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
//得到连接
SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
//执行update UPDATE person SET name='Jack',age=21 WHERE id=4;
ContentValues contentValues = new ContentValues();
contentValues.put("name", "Jack");
contentValues.put("age", 13);
int updateCount = database.update("person", contentValues, "_id=?", new String[]{"3"});
database.close();
Toast.makeText(this, "updateCount=" + updateCount, Toast.LENGTH_SHORT).show();
}
//添加记录
private void insertRecord() {
DBTestHelper dbTestHelper3 = new DBTestHelper(DataBasesActivity.this, 2);
//得到连接
SQLiteDatabase database = dbTestHelper3.getReadableDatabase();
//执行insert INSERT INTO person(name,age) VALUES('Tom',12);
ContentValues contentValues = new ContentValues();
contentValues.put("name", "Tom");
contentValues.put("age", 12);
long id = database.insert("person", null, contentValues);
//关闭
database.close();
//提示
Toast.makeText(this, "id=" + id, Toast.LENGTH_SHORT).show();
}
}