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(); } }
