基本功能
- 添加:输入信息,点击“添加”按钮添加进数据表中
- 修改:选定要修改的信息,输入要修改的数据,点击“修改”按钮保存修改
- 删除:选定要删除的信息,点击“删除”按钮删除该信息
- 查询:呈现表中所有信息到页面上
主要代码
DbHelper.java
SQLite数据库打开助手DbHelper作为抽象类SQLiteOpenHelper的子类需要重写2个抽象方法onCreate()和onUpgrade()
public class DbHelper extends SQLiteOpenHelper {
public static final String TB_NAME = "student";
public DbHelper(Context context, String dbname, SQLiteDatabase.CursorFactory factory, int version) {
super(context, dbname, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS " +
TB_NAME + "( _id integer primary key autoincrement," +
"name varchar," + "age integer"+ ")");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TB_NAME);
onCreate(db);
}
}
MyDAO.java
添加增删查改基本操作
public class MyDAO {
private SQLiteDatabase myDb;
private DbHelper dbHelper;
public MyDAO(Context context) {
dbHelper = new DbHelper(context,"test.db",null,4);
}
public Cursor allQuery(){ //查询所有
myDb = dbHelper.getReadableDatabase();
return myDb.rawQuery("select * from student",null);
}
public int getRecordsNumber(){
myDb = dbHelper.getReadableDatabase();
Cursor cursor= myDb.rawQuery("select * from student",null);
return cursor.getCount();
}
public void insertInfo(String name,int age){ //插入
myDb = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", name);
values.put("age", age);
long rowid=myDb.insert(DbHelper.TB_NAME, null, values);
if(rowid==-1)
Log.i("myDbDemo", "数据插入失败!");
else
Log.i("myDbDemo", "数据插入成功!"+rowid);
}
public void deleteInfo(String selId){ //删除
String where = "_id=" + selId;
int i = myDb.delete(DbHelper.TB_NAME, where, null);
if (i > 0)
Log.i("myDbDemo", "数据删除成功!");
else
Log.i("myDbDemo", "数据未删除!");
}
public void updateInfo(String name,int age,String selId){ //修改记录
ContentValues values = new ContentValues();
values.put("name", name);
values.put("age", age);
String where="_id="+selId;
int i=myDb.update(DbHelper.TB_NAME, values, where, null);
if(i>0)
Log.i("myDbDemo","数据更新成功!");
else
Log.i("myDbDemo","数据未更新!");
}
}
MainActivity.java
使用了MyDAO类的相关方法,首次运行时,增加3条记录并使用ListView控件显示出来
public class MainActivity extends Activity implements View.OnClickListener{
private MyDAO myDAO;
private ListView listView;
private List<Map<String,Object>> listData;
private Map<String,Object> listItem;
private SimpleAdapter listAdapter;
private EditText name;
private EditText age;
private String selId=null; //选择项id
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Button bt_add= (Button) findViewById(R.id.add);bt_add.setOnClickListener(this);
Button bt_modify=(Button)findViewById(R.id.modify);bt_modify.setOnClickListener(this);
Button bt_del=(Button)findViewById(R.id.del);bt_del.setOnClickListener(this);
name=(EditText)findViewById(R.id.name);
age=(EditText)findViewById(R.id.age);
myDAO = new MyDAO(this);
if(myDAO.getRecordsNumber()==0) {
myDAO.insertInfo("lier", 26);
myDAO.insertInfo("wangwu", 45);
myDAO.insertInfo("zhangsan", 80);
}
displayRecords();
}
public void displayRecords(){
listView = (ListView)findViewById(R.id.listView);
listData = new ArrayList<Map<String,Object>>();
Cursor cursor = myDAO.allQuery();
while (cursor.moveToNext()){
int id=cursor.getInt(0);
String name=cursor.getString(1);
//int age=cursor.getInt(2);
int age=cursor.getInt(cursor.getColumnIndex("age"));
listItem=new HashMap<String,Object>();
listItem.put("_id", id);
listItem.put("name", name);
listItem.put("age", age);
listData.add(listItem);
}
listAdapter = new SimpleAdapter(this, listData, R.layout.item, new String[]{"_id","name","age"}, new int[]{R.id.id2,R.id.name2,R.id.age2});
listView.setAdapter(listAdapter); //应用适配器
listView.setOnItemClickListener(new AdapterView.OnItemClickListener() { //列表项监听
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
Map<String,Object> rec= (Map<String, Object>) listAdapter.getItem(position);
name.setText(rec.get("name").toString());
age.setText(rec.get("age").toString());
Log.i("ly",rec.get("_id").toString());
selId=rec.get("_id").toString();
}
});
}
@Override
public void onClick(View v) {
if(selId!=null) {
String p1 = name.getText().toString().trim();
int p2 = Integer.parseInt(age.getText().toString());
switch (v.getId()){
case R.id.add:
myDAO.insertInfo(p1,p2);
break;
case R.id.modify:
myDAO.updateInfo(p1,p2,selId);
Toast.makeText(getApplicationContext(),"更新成功!",Toast.LENGTH_SHORT).show();
break;
case R.id.del:
myDAO.deleteInfo(selId);
Toast.makeText(getApplicationContext(),"删除成功!",Toast.LENGTH_SHORT).show();
name.setText(null);age.setText(null); selId=null;
}
}else{
if(v.getId()==R.id.add) {
String p1 = name.getText().toString();
String p2=age.getText().toString();
if(p1.equals("")||p2.equals("")){
Toast.makeText(getApplicationContext(),"姓名和年龄都不能空!",Toast.LENGTH_SHORT).show();
}else{
myDAO.insertInfo(p1, Integer.parseInt(p2));
}
} else{
Toast.makeText(getApplicationContext(),"请先选择记录!",Toast.LENGTH_SHORT).show();
}
}
displayRecords();
}
}
效果演示
完整项目源码
https://gitee.com/GAIALscariot/SQLite