很久没做笔记了,其实做笔记好处还是很多的,过段时间如果忘记怎么使用了可以翻一下笔记,而且写笔记的时候就相当于又复习了一遍,加深印象,我是菜鸟,写的笔记可以都看不懂,见谅啊!!!
SQLite是是一个软件库,它的优点是能自给自足,不需要配置,而且也不依赖服务器,而且SQLite支持的平台很多,可以在UNIX(在Linux,Mac OS-X,Android,IOS)和Windows(Win32中,WINCE,WinRT的)中运行。
我们来做一个学生管理系统,使用到的功能就是“增”“删”“改”“查”。就是增加数据,删除数据,更改数据和查询数据,其实这几种办法都是差不多的,等下再说,在写这四个功能前还得需要建立一个数据表:
建立数据表
//创建一个MySqliteOpenHelper 类,继承SQLiteOpenHelper ,这是SQLiteOpen的一个帮助类
public class MySqliteOpenHelper extends SQLiteOpenHelper {
public MySqliteOpenHelper(Context context ) {
super(context, information.SqlName,null,information.SqlVersion);
}
//创建一个表,名字叫"student",里面的属性有学生的学号"_id", 名字"name",年龄"age",为了防止有重名的情况,把"_id"设置为主键,并且让它以自增的模式添加,每加一个学生信息,id就回自己加一,名字设置长度最多20,名字和年龄不能为空;
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table "+ "student" +" (_id integer primary key autoincrement not null, name varchar(20) not null, age integer not null )";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
查询列表
//列表这里用到了ListView来对学生信息表来进行排序。创建一个类继承BaseActivity.然后重写里面的方法:
public class SelectSqlActivity extends BaseActivity {
private ListView listView;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_select_sql);
findView();
getSqlData();
}
private void getSqlData(){
MySqliteOpenHelper msoh = new MySqliteOpenHelper(SelectSqlActivity.this);
SQLiteDatabase db = msoh.getWritableDatabase();
String sql = "select name from student where name = ?";
String[] args = {"小明"};
Cursor cursor = db.rawQuery(sql, args);
int num = cursor.getCount();
Log.d(Constant.Tag,"num:"+num);
boolean isLast = true;
while(isLast){
try{
isLast = cursor.moveToNext();
Log.d(Constant.Tag,"name:"+cursor.getString(0));
}catch (Exception e){
}
}
}
@Override
public void findView() {
listView = fView(R.id.listView);
}
}
public class SqlActivity extends CursorAdapter {
public SqlActivity(Context context, Cursor c) {
super(context, c, true);
}
@Override
public View newView(Context context, Cursor cursor, ViewGroup parent) {
return LayoutInflater.from(context).inflate(R.layout.adapter_listview_item, null);
}
//设置表的显示格式,如下,id显示在第0列,名字显示在第一列,年龄显示在第二列;
@Override
public void bindView(View view, Context context, Cursor cursor) {
TextView textView = (TextView) view.findViewById(R.id.textView);
StringBuffer str = new StringBuffer();
str.append("_id");
str.append(cursor.getString(0));
str.append("name");
str.append(cursor.getString(1));
str.append("age");
str.append(cursor.getString(2));
textView.setText(str.toString());
}
}
启动方法
public class SqliteActivity extends AppCompatActivity {
MySqliteOpenHelper mySqliteOpenHelper = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sqlite);
}
public void btnOnclick(View view){
switch (view.getId()){
case R.id.createBtn://创建数据库和表
mySqliteOpenHelper = new MySqliteOpenHelper(SqliteActivity.this);
break;
case R.id.insertBtn://插入数据
mySqliteOpenHelper = new MySqliteOpenHelper(SqliteActivity.this);
SQLiteDatabase db = mySqliteOpenHelper.getWritableDatabase();
//方法1:
String insertStr1 = "insert into student (name,age) values ('小明',12)";
db.execSQL(insertStr1);
// //方法2:
String insertStr2 = "insert into student (name,age) values (?,?)";
Object args[] = {"测试数据", 999};
db.execSQL(insertStr2, args);
//方法3:
//String table, String nullColumnHack, ContentValues values
ContentValues cv = new ContentValues();
cv.put("name","小花");
cv.put("age",56);
db.insert("student", null, cv);
//insert into student (name) values (NULL)
db.close();//数据库用完一定要关闭,数据库用完一定要关闭,数据库用完一定要关闭。重要的话说三遍!
break;
case R.id.selBtn://查询数据
Intent intent = new Intent(SqliteActivity.this,SelectSqlActivity.class);
startActivity(intent);
break;
case R.id.updateBtn://更新数据
mySqliteOpenHelper = new MySqliteOpenHelper(SqliteActivity.this);
SQLiteDatabase db1 = mySqliteOpenHelper.getWritableDatabase();
// //方法1
String updateSql = "UPDATE student SET name = '更新了数据6' WHERE _id = 6";
db1.execSQL(updateSql);
//方法2
String updateSqlArgs = "UPDATE " + Constant.TableName + " SET name = ?,age=? WHERE _id = ?";
Object[] bindArgs1 = {"ddd", 23, 58};
db1.execSQL(updateSqlArgs,bindArgs1);
// //方法3
// String table, ContentValues values, String whereClause, String[] whereArgs
ContentValues updateCv = new ContentValues();
updateCv.put("name","更新了数据12");
updateCv.put("age",11111);
String whereClause = "_id = 12";
db1.update("student",updateCv,whereClause,null);
db1.close();
break;
case R.id.deleteBtn://删除数据
mySqliteOpenHelper = new MySqliteOpenHelper(SqliteActivity.this);
SQLiteDatabase sdDel = mySqliteOpenHelper.getWritableDatabase();
//方法1
String delStr = "DELETE FROM student WHERE name = ?";
Object[] bindArgs = {"yyd"};
sdDel.execSQL(delStr,bindArgs);
//方法2
String delStr1 = "DELETE FROM student WHERE name = '小花'";
sdDel.execSQL(delStr1);
//方法3
//String table, String whereClause, String[] whereArgs
int num = sdDel.delete("student","name = '小明'",null);
Log.d(Constant.Tag,"num:"+num);
break;
case R.id.shiWuBtn:
insertData();
break;
}
}
大家也看到了,操作方法有三种,至于用哪种办法就看大家的使用习惯了,大家有什么想说的可以在下面留言,新手写博客,写的不好勿怪