文章转载自:http://www.cnblogs.com/Codenewbie/archive/2013/03/21/2973252.html
android平台下数据库的一些概念
Sqlite是android平台的一款轻量级的、嵌入式的、关系型数据库产品,以下是一些主要概念。
1.Sqlite通过文件保存数据库,创建的数据库文件默认存在在/data/data/<package>/databases/的文件
夹下,一个文件就是一个数据库。
2.一个数据库包含多个表格,一个表格包含不同的字段,字段类型等等,这与SQL数据库相似。
3.Sqlite记录没有顺序的概念,不存在第一第二类的概念,通过查询获取满足条件的记录。
android平台下操作数据库的的相关类
(1.)SQLiteOpenHelper 抽象类,用于创建和管理数据库以及版本,需要创建一个子类继承,该类封装
了对数据库操作的基本方法,使用方便。
(2.)SQLiteDatabase 数据库访问了,主要是对数据库的增删改查等常用操作,功能比
SQLiteOpenHelper丰富,比如事务管理,分页等等。
(3.)Cursor游标类,主要用来返回返回查询记录结果
SQLiteOpenHelper 类使用方法比较少,网上介绍也比较多,本程序主要采用SQLiteDatabase类操作数
据库,介绍android 平台下Sqlite的多种使用方法。主要功能如图-1界面所示。
图-1 Sqlite数据库界面的操作及其功能
图-2 创建的数据库截图
源码下载:Sqlite.zip 1.对数据库的操作
(1.)创建一个数据库
view plain
copy to clipboard
print
?
-
-
- openOrCreateDatabase("students.db",SQLiteDatabase.CREATE_IF_NECESSARY,null);
-
(2.)删除一个数据库
view plain
copy to clipboard
print
?
-
- deleteDatabase("students.db");
-
(3.)获取应用程序所有数据库的名称
view plain
copy to clipboard
print
?
-
-
- databaseList()
-
(4.)重命名一个数据库
view plain
copy to clipboard
print
?
-
- Filef=getDatabasePath("data.db");
- FilerenameFile=getDatabasePath("renamedata.db");
- f.renameTo(renameFile);
-
2.对表的操作
(1.)创建一个表(sql语句中创建)
view plain
copy to clipboard
print
?
- mDb.execSQL("createtableifnotexists"+table+
- "(idintegerprimarykeyautoincrement,"
- +"usernametextnotnull,infotextnotnull);");
-
(2.)删除一个表
view plain
copy to clipboard
print
?
- mDb.execSQL("droptableifexists"+table);
-
(3.)修改表
Sqlite是不能修改一个表的字段,不能删除一个表的字段,但是可以重命名一个表的名称,可以添加一
个字段及其属性
1.)重命名表
view plain
copy to clipboard
print
?
- mDb.execSQL("altertable"+oldTable+"renameto"+newTableName+";");
-
2.)添加一列
view plain
copy to clipboard
print
?
- mDb.execSQL("altertable"+table+"addcolumn"+column+type+";");
-
(4.)获取一个数据库的所有表的名称
view plain
copy to clipboard
print
?
- mDb.rawQuery("selectnamefromsqlite_masterwheretype='table'orderbyname",null);
-
3.对数据库记录的操作
(1.)添加一条记录
view plain
copy to clipboard
print
?
- ContentValuesvalues=newContentValues();
- values.put("username",user.getUsername());
- values.put("info",user.getInfo());
- mDb.insert(table,null,values);
-
(2.)删除一条记录
view plain
copy to clipboard
print
?
- StringwhereClause="id=?";
- String[]whereArgs=newString[]{String.valueOf(id)};
- mDb.delete(table,whereClause,whereArgs);
-
(3.)修改一条记录
values.put("info",info);
StringwhereClause="id=?";
String[]whereArgs=newString[]{String.valueOf(id)};
mDb.update(table,values,whereArgs);
(4.)查询一条数据
view plain
copy to clipboard
print
?
- String[]columns=newString[]{"id","username","info"};
- Stringselection="id=?";
- String[]selectionArgs={String.valueOf(id)};
- StringgroupBy=null;
- Stringhaving=null;
- StringorderBy=null;
- mDb.query(table,columns,selection,selectionArgs,groupBy,having,orderBy);
-
(5.)查询所有记录
view plain
copy to clipboard
print
?
- mDb.rawQuery("select*from"+table,null);
-
SqliteActivity源码类如下(注意:在点击对数据记录的增删改查时要先create table ,否则会因为无法找到mytable表而报错)
SqliteActivity.java
view plain
copy to clipboard
print
?
- packagecom.sqlite.main;
-
- importjava.io.File;
- importandroid.app.Activity;
- importandroid.content.ContentValues;
- importandroid.database.Cursor;
- importandroid.database.SQLException;
- importandroid.database.sqlite.SQLiteDatabase;
- importandroid.os.Bundle;
- importandroid.text.Html;
- importandroid.view.View;
- importandroid.view.View.OnClickListener;
- importandroid.widget.Button;
- importandroid.widget.TextView;
- importandroid.widget.Toast;
-
- publicclassSqliteActivityextendsActivity{
-
-
-
- SQLiteDatabasemDb;
- SQLiteDatabaseDaodao;
-
- TextViewshow;
-
- @Override
- publicvoidonCreate(BundlesavedInstanceState){
- super.onCreate(savedInstanceState);
- setContentView(R.layout.main);
-
- dao=newSQLiteDatabaseDao();
-
-
- ButtoncreateDatabase=(Button)findViewById(R.id.createdatabase);
- createDatabase.setOnClickListener(createDatabaseClick);
-
- ButtongetDatabasesList=(Button)findViewById(R.id.getdatabaseslist);
- getDatabasesList.setOnClickListener(getDatabaseListClick);
-
- ButtonrenameDatabase=(Button)findViewById(R.id.renamedatabase);
- renameDatabase.setOnClickListener(renameDatabaseClick);
-
- ButtonremoveDatabase=(Button)findViewById(R.id.removedatabase);
- removeDatabase.setOnClickListener(removeDatabaseClick);
-
- ButtoncreateTable=(Button)findViewById(R.id.createtable);
- createTable.setOnClickListener(createTableClick);
-
- ButtongetTablesList=(Button)findViewById(R.id.gettableslist);
- getTablesList.setOnClickListener(getTablesListClick);
-
- ButtonrenameTable=(Button)findViewById(R.id.renametable);
- renameTable.setOnClickListener(renameTableClick);
-
- ButtondropTable=(Button)findViewById(R.id.droptable);
- dropTable.setOnClickListener(dropTableClick);
-
- ButtonaddTableColumn=(Button)findViewById(R.id.addtablecolumn);
- addTableColumn.setOnClickListener(addTableColumnClick);
-
- ButtongetTableColumnsList=(Button)findViewById(R.id.gettablecolumnslist);
- getTableColumnsList.setOnClickListener(getTableColumnsListClick);
-
- ButtoninsertTable=(Button)findViewById(R.id.inserttable);
- insertTable.setOnClickListener(insertTableClick);
-
- ButtonqueryTable=(Button)findViewById(R.id.querytable);
- queryTable.setOnClickListener(queryTableClick);
-
- ButtonupdateTable=(Button)findViewById(R.id.updatetable);
- updateTable.setOnClickListener(updateTableClick);
-
- Buttondelete=(Button)findViewById(R.id.delete);
- delete.setOnClickListener(deleteClick);
-
- show=(TextView)findViewById(R.id.showresult);
-
- }
-
-
-
-
- OnClickListenercreateDatabaseClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- openOrCreateDatabase("students.db",
- SQLiteDatabase.CREATE_IF_NECESSARY,null);
- show.setText("创建的数据库路径为n"
- +getDatabasePath("students.db"));
-
- }
- };
-
-
- OnClickListenergetDatabaseListClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
- String[]dblist=dao.getDatabasesList();
- Stringrs="";
- for(Strings:dblist){
- rs+=s+"n";
- }
- show.setText("数据库名称为:n"+rs);
-
- }
- };
-
-
- OnClickListenerrenameDatabaseClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- openOrCreateDatabase("data.db",
- SQLiteDatabase.CREATE_IF_NECESSARY,null);
- Filef=getDatabasePath("data.db");
- FilerenameFile=getDatabasePath("renamedata.db");
- booleanb=f.renameTo(renameFile);
- if(b)
- show.setText("data.db已经重命名为renamedata.db");
- else
-
- show.setText("无法重命名");
- }
- };
-
-
- OnClickListenerremoveDatabaseClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- dao.dropDatabase("students.db");
-
- String[]dblist=dao.getDatabasesList();
- Stringrs="";
- for(Strings:dblist){
- rs+=s+"n";
- }
- show.setText("数据库students.db已经删除n现在数据库的名称为:n"+rs);
- }
- };
-
-
-
- OnClickListenercreateTableClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- dao.createTable(mDb,"mytable");
- show.setText("数据库students.db已经创建mytable表n");
-
- }
- };
-
-
-
- OnClickListenergetTablesListClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
-
- StringtableNames=dao.getTablesList(mDb);
- show.setText(tableNames);
-
- }
- };
-
-
-
- OnClickListenerrenameTableClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- dao.createTable(mDb,"testtable");
-
- booleanb=dao.alterTableRenameTable(mDb,"testtable","newtable");
- if(b)show.setText("testtable已经重命名为nnewtable表n");
- elseshow.setText("newtable已经存在n请删除(droptable)后重试");
- }
- };
-
-
-
- OnClickListenerdropTableClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- dao.dropTable(mDb,"newtable");
-
- StringtableNames=dao.getTablesList(mDb);
- show.setText("newtable已经删除n现在表名称为:n"+tableNames);
- }
- };
-
-
-
- OnClickListeneraddTableColumnClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- booleanb=dao.alterTableAddColumn(mDb,"mytable","password","varchar(30)");
- if(b)show.setText("已经添加password字段n字符类型为:varcharn长度为:30");
- elseshow.setText("mytable表中password字段已经存在");
- }
- };
-
-
-
- OnClickListenergetTableColumnsListClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- Stringstr=dao.getTableColumns(mDb);
- show.setText("mytable表的列名:n"+str);
- }
- };
-
-
-
- OnClickListenerinsertTableClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
- Useruser=newUser();
- user.setUsername("Mr.Young");
- user.setInfo("好学生");
- dao.insert(mDb,"mytable",user);
-
- Cursorc=dao.getAllData(mDb,"mytable");
- if(c.moveToLast()){
- Stringid=c.getString(0);
- Stringusername=c.getString(1);
- Stringinfo=c.getString(2);
-
- show.setText("最新添加的一条数据:n"+"id:"+id+"nusername:"+username+"ninfo:"+info);
- }
-
- }
- };
-
-
-
- OnClickListenerqueryTableClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
-
-
- Cursorc=dao.getAllData(mDb,"mytable");
- Strings="";
- intcolumnsSize=c.getColumnCount();
- String[]columns=c.getColumnNames();
- StringcolumnsName="";
-
- for(Stringcol:columns){
-
- columnsName+=col+"u0020u0020";
- }
-
- while(c.moveToNext()){
-
- for(inti=0;i<columnsSize;i++){
- s+=c.getString(i)+"u0020u0020";
- }
- s+="<br>";
- }
- show.setText(Html.fromHtml("<h5>"+columnsName+"</h5>"+s));
- }
- };
-
-
-
- OnClickListenerupdateTableClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
- Cursorc=dao.getAllData(mDb,"mytable");
- if(c.moveToFirst()){
-
- intfirst=Integer.valueOf(c.getString(0));
-
-
- dao.update(mDb,first,"YongMing","学习成绩优异");
- Cursoru=dao.queryById(mDb,first);
- u.moveToFirst();
- show.setText("id为:"+first+"的记录已经修改:nid:"+first+"nusername:"+u.getString(1)+"ninfo:"+u.getString(2));
-
- }else
-
- show.setText("没有要更新的记录!请添加数据后再作修改");
- }
- };
-
-
-
- OnClickListenerdeleteClick=newOnClickListener(){
-
- @Override
- publicvoidonClick(Viewv){
-
- Cursorc=dao.getAllData(mDb,"mytable");
- if(c.moveToLast()){
- intlast=Integer.valueOf(c.getString(0));
-
-
- booleanb=dao.delete(mDb,last);
- if(b)
- show.setText("成功删除id为:n"+last+"的记录!");
- }
- else
- show.setText("没有要删除的记录!");
- }
- };
-
-
- @Override
- publicvoidfinish(){
-
- super.finish();
- mDb.close();
- }
-
-
-
-
-
-
-
-
-
-
-
- classSQLiteDatabaseDao{
-
- publicSQLiteDatabaseDao(){
- mDb=openOrCreateDatabase("users.db",null);
- }
-
-
-
-
- publicString[]getDatabasesList(){
-
- returndatabaseList();
- }
-
-
- publicvoidcreateDatabase(Stringdb){
-
- openOrCreateDatabase(db,null);
- }
-
-
- publicvoiddropDatabase(Stringdb){
-
- try{
- deleteDatabase(db);
-
- }catch(SQLExceptione){
- Toast.makeText(getApplicationContext(),"删除数据库失败",
- Toast.LENGTH_LONG).show();
-
- }
- }
-
-
-
-
- publicStringgetTablesList(SQLiteDatabasemDb){
-
- Cursorc=mDb
- .rawQuery(
- "selectnamefromsqlite_masterwheretype='table'orderbyname",
- null);
- Stringstr="";
- while(c.moveToNext()){
- str+=c.getString(0)+"n";
-
- }
- return"表的名称为:n"+str;
- }
-
-
- publicvoidcreateTable(SQLiteDatabasemDb,Stringtable){
- try{
- mDb.execSQL("createtableifnotexists"+table+
- "(idintegerprimarykeyautoincrement,infotextnotnull);");
- }catch(SQLExceptione){
- Toast.makeText(getApplicationContext(),"数据表创建失败",
- Toast.LENGTH_LONG).show();
- }
- }
-
-
- publicvoiddropTable(SQLiteDatabasemDb,Stringtable){
-
- try{
- mDb.execSQL("droptableifexists"+table);
-
- }catch(SQLExceptione){
- Toast.makeText(getApplicationContext(),"数据表删除失败",
- Toast.LENGTH_LONG).show();
- }
-
- }
-
-
- publicbooleanalterTableRenameTable(SQLiteDatabasemDb,StringoldTable,
- StringnewTableName){
- try{
- mDb.execSQL("altertable"+oldTable+"renameto"
- +newTableName+";");
-
- }catch(SQLExceptione){
- Toast.makeText(getApplicationContext(),"数据表重命名失败",
- Toast.LENGTH_LONG).show();
- returnfalse;
- }
-
- returntrue;
- }
-
-
-
-
-
- publicbooleanalterTableAddColumn(SQLiteDatabasemDb,Stringtable,
- Stringcolumn,Stringtype){
- try{
- mDb.execSQL("altertable"+table+"addcolumn"+column
- +type+";");
-
- }catch(SQLExceptione){
- Toast.makeText(getApplicationContext(),"数据表添加失败",
- Toast.LENGTH_LONG).show();
- returnfalse;
- }
-
- returntrue;
- }
-
-
- publicStringgetTableColumns(SQLiteDatabasemDb){
-
- Cursorc=dao.getAllData(mDb,"mytable");;
- String[]columns=c.getColumnNames();
- Stringstr="";
- for(Strings:columns){
-
- str+=s+"n";
-
- }
-
- returnstr;
- }
-
-
-
-
- publiclonginsert(SQLiteDatabasemDb,Useruser){
-
- ContentValuesvalues=newContentValues();
- values.put("username",user.getUsername());
- values.put("info",user.getInfo());
- returnmDb.insert(table,null,values);
- }
-
-
-
-
-
- publicbooleandelete(SQLiteDatabasemDb,intid){
-
- StringwhereClause="id=?";
- String[]whereArgs=newString[]{String.valueOf(id)};
- try{
- mDb.delete(table,whereArgs);
- }catch(SQLExceptione){
- Toast.makeText(getApplicationContext(),
- Toast.LENGTH_LONG).show();
- returnfalse;
- }
- returntrue;
- }
-
-
-
-
-
- publicvoidupdate(SQLiteDatabasemDb,intid,Stringusername,Stringinfo)
- {
-
- ContentValuesvalues=newContentValues();
- values.put("username",whereArgs);
- }
-
- publicCursorqueryById(SQLiteDatabasemDb,intid){
-
-
-
-
-
-
-
-
- String[]columns=newString[]{"id","info"};
- Stringselection="id=?";
- String[]selectionArgs={String.valueOf(id)};
- StringgroupBy=null;
- Stringhaving=null;
- StringorderBy=null;
- returnmDb.query(table,
- selectionArgs,orderBy);
- }
-
- publicCursorgetAllData(SQLiteDatabasemDb,Stringtable){
-
-
- returnmDb.rawQuery("select*from"+table,null);
-
-
-
-
-
-
-
-
-
- }
-
- }
-
- }
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|