一、android系统的集成轻量级的数据库,SQLite只是一个嵌入式的数据引擎,专门适用于(手机,PDA)上的适量数据存储。SQLite数据库实际上是文件。
二、android提供了SQLiteDatebase代表一个数据库(底层就是一个数据库文件),一旦程序获得了代表指定的SQLiteDatebase对象,接下来就可以通过SQLiteDatebase来管理数据。
(1).SQLiteDatabase有以下的静态方法来打开一个文件对应的数据库:
-
static SQLiteDatabase openDatabase(String path,SQLiteDatabase.CursorFactory factory,int flags):打开地址为path的数据库文件。
-
static SQLiteDatabase openOrCreateDatabase(File file,SQLiteDatabase.CursorFactory factory):打开或创建file所代表的SQLite数据库文件
-
static SQLiteDatabase openOrCreateDatabase(String path,SQLiteDatabase.CursorFactory factory):打开或创建path所代表的SQLite数据库文件
(2)对数据的操作的两个方法。
第一个方法:使用sql语句
execSQL(String sql,Object[] bindArgs) 执行带占位符的SQL语句
execSQL(String sql):执行sql语句
第二个方法:SQLiteDatebase有专门的增删改查的方式:
- insert(String table,String nullColumnHack,ContentValues values):向table表中插入数据
- update(String table,ContentValues values,String whereClause,String[] whereArgs):更新table表中的特定数据
- delete(String table,String whereClause,String[] whereArgs):删除table表中的特定数据
- Cursor query(String table,String columns,String selections,String[] selectionArgs,String groupBy,String having,String orderBy):对table表执行查询
- Cursor query(String table,String columns,String selections,String[] selectionArgs,String groupBy,String having,String orderBy,String limit):对table表执行查询,limit控制至多查询几条记录
- Cursor query(boolean distinct,String table,String columns,String selections,String[] selectionArgs,String groupBy,String having,String orderBy,String limit):对table表执行查询,limit控制至多查询几条记录,distinct控制是否去除重复值
- rawQuery(String sql,String[] selectionArgs):执行带占位符的SQL查询
- beginTransaction():开始事务
- endTransaction():结束事务
对于查方法都会返回一个Cursor对象,android中提供的Cursor对象类似于jdbc中的ResultSet,对于Cursor同样提供了如下的方法来移动查询结果的指针。
move(int offset):将记录指针向下或者向上移动指定行数。
boolean moveToFirst():将记录指针移动到第一行,如何成功返回true
boolean moveToLast():将记录指针移动到最后一行,如果成功返回true
boolean moveToNext():将记录指针一档到下一行,成功返回true
boolean moveToPosition(int position):将记录指针移动到指定行,如果成功返回true
boolean moveToPrevious():将记录指针移动到上一行,成功返回true
一旦移动到其中一行,就可以用,getXxx()方法或者该行的数据
实例:
1、xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical"> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/e_title"/> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/e_content"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:id="@+id/sqlite_insert" android:text="insert"/> <ListView android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/sqlite_list"/> </LinearLayout>
2、java代码
public class SqLiteActivity extends AppCompatActivity { SQLiteDatabase db; ListView sq_list; protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.sqlite_activity); final TextView e_title=findViewById(R.id.e_title); final TextView e_content=findViewById(R.id.e_content); //创建数据库,此处使用绝对路径 db=SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().toString()+"/my.db3",null); Toast.makeText(SqLiteActivity.this,"数据库创建成功",Toast.LENGTH_SHORT).show(); sq_list=findViewById(R.id.sqlite_list); Button sqlite_insert=findViewById(R.id.sqlite_insert); sqlite_insert.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { String tite=e_title.getText().toString(); String content=e_content.getText().toString(); try{ insertDate(db,tite,content);Cursor cursor=db.rawQuery("select * from news_inf",null); inflateList(cursor); }catch (SQLiteException e){ db.execSQL("drop table if exists news_info"); db.execSQL("create table news_info(" +"news_id integer primary key autoincrement," +"news_title varchar(50)," +"news_content varchar(255))"); Toast.makeText(SqLiteActivity.this,"表创建成功",Toast.LENGTH_SHORT).show(); //执行insert insertDate(db,tite,content); //执行查询,查询所有记录 Cursor cursor=db.rawQuery("select * from news_info",null); inflateList(cursor); } } }); } private void insertDate(SQLiteDatabase db,String title,String content){ //执行插入,使用含有占位符的方法 db.execSQL("insert into news_info values(null,?,?)",new String[]{title,content}); Toast.makeText(SqLiteActivity.this,"插入成功",Toast.LENGTH_SHORT).show(); } private void inflateList(Cursor cursor){ //使用SimpleCursorAdapter来填充ListView SimpleCursorAdapter adapter=new SimpleCursorAdapter( SqLiteActivity.this, R.layout.sqlite_list_item, cursor, new String[]{"list_title","list_content"}, new int[]{R.id.list_title,R.id.list_content}, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER); //显示数据 sq_list.setAdapter(adapter); } }
三、SQLIteOpenHelper类
在实际项目中通常是继承SQLiteOpenHelper开发子类,并通过子类的getReadableDatebase(),getWritebleDatebase()的方法代开数据库。
(1)开发者只需要重写SQLIteOpenHelper子类的两个方法,加一个构造方法
构造方法用于创建数据库 :例如
public MyDatabaseHelper(Context context, String name,SQLiteDatabase.CursorFactory factory, int version) { super(context, name,factory,version); this.context=context; }
abstract void onCreate(SQLiteDatebase db):此方法初始使用软降用于生成表。
abstract void onUpgrade(SQLiteDatebase db,int oldVesion,int newVersion):用于升级软件更新数据库的表结构
当调用,SQLIteOpenHelper子类的getReadableDatebase(),getWritebleDatebase()方法时获取到SQLiteDatebase的实例,如果数据库不存在,就调用onCreate()方法,可生成表,onCreate()方法只有在初次生成数据库时才会被调用。
实例:英语生词本
1、sqllitehelper_activity.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:layout_margin="10dp" android:orientation="vertical"> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="word" android:id="@+id/word"/> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="detail" android:id="@+id/detail"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_gravity="center" android:text="添加生词" android:id="@+id/add_word"/> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="key" android:id="@+id/key"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="查询" android:layout_gravity="center" android:id="@+id/search"/> </LinearLayout>
2、reslut_activity.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:layout_margin="10dp" android:orientation="vertical"> <ListView android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/result_show"/> </LinearLayout>
3、result_show_item.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="10dp" android:orientation="vertical"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/result_word" android:text="aaa" android:textSize="24sp"/> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/result_detail" android:text="aaaaaaaaa" android:textSize="20sp"/> </LinearLayout>
4、SqLiteOpenHelperActivity
public class SqLiteOpenHelperActivity extends AppCompatActivity implements View.OnClickListener { private EditText word; private EditText detail; private EditText key; private Button add_word; private Button search; MyDatabaseHelper myDatabaseHelper; protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.sqllitehelper_activity); word=findViewById(R.id.word); detail=findViewById(R.id.detail); key=findViewById(R.id.key); add_word=findViewById(R.id.add_word); search=findViewById(R.id.search); add_word.setOnClickListener(this); search.setOnClickListener(this); myDatabaseHelper=new MyDatabaseHelper(this,"bookstore",null,1); } @Override public void onClick(View view) { switch (view.getId()){ case R.id.add_word:{ String word1=word.getText().toString(); String detail1=detail.getText().toString(); // Toast.makeText(SqLiteOpenHelperActivity.this,word1+" "+detail1,Toast.LENGTH_SHORT).show(); word.setText(""); detail.setText(""); insertDate(myDatabaseHelper.getReadableDatabase(),word1,detail1); break; } case R.id.search:{ String key1=key.getText().toString(); Cursor cursor=myDatabaseHelper.getReadableDatabase().rawQuery("select * from book where word like ? or detail like ?",new String[]{"%"+key1+"%","%"+key1+"%"}); key.setText(""); Bundle data=new Bundle(); data.putSerializable("data",converCursorToList(cursor)); Intent intent=new Intent(SqLiteOpenHelperActivity.this,ResultActivity.class); intent.putExtras(data); startActivity(intent); break; } default: break; } } private void insertDate(SQLiteDatabase db,String word,String detail){ db.execSQL("insert into book values(null,?,?)",new String[]{word,detail} ); Toast.makeText(SqLiteOpenHelperActivity.this,"添加生词成功",Toast.LENGTH_SHORT).show(); } private ArrayList<Map<String,String>> converCursorToList(Cursor cursor){ ArrayList<Map<String,String>> result=new ArrayList<>(); StringBuilder builder=new StringBuilder(); while (cursor.moveToNext()){ Map<String,String> map=new HashMap<>(); map.put("word","word:"+cursor.getString(1)); map.put("detail","detail:"+cursor.getString(2)); builder.append(cursor.getString(1)+" "+cursor.getString(2)+"\n"); result.add(map); } Toast.makeText(SqLiteOpenHelperActivity.this,builder.toString(),Toast.LENGTH_SHORT).show(); return result; } protected void onDestroy(){ super.onDestroy(); if(myDatabaseHelper!=null){ myDatabaseHelper.close(); } } }
5、ResultActivity.class
public class ResultActivity extends AppCompatActivity { private ListView listView; protected void onCreate(Bundle saveInstanceState) { super.onCreate(saveInstanceState); setContentView(R.layout.result_activity); listView=findViewById(R.id.result_show); Bundle bundle=getIntent().getExtras(); List<Map<String,String>> data=(ArrayList<Map<String,String>>)bundle.getSerializable("data"); SimpleAdapter adapter=new SimpleAdapter(ResultActivity.this,data,R.layout.rsult_show_item, new String[]{"word","detail"},new int[]{R.id.result_word,R.id.result_detail}); listView.setAdapter(adapter); } }