Android SQLite数据库简单实现增删改查

最新推荐文章于 2022-11-02 22:30:10 发布

爾雅

最新推荐文章于 2022-11-02 22:30:10 发布

阅读量759

点赞数

CC 4.0 BY-SA版权

文章标签：数据

本文链接：https://blog.youkuaiyun.com/ACG_Hzy/article/details/79135846

本文详细介绍了一个基于SQLite的数据库管理系统实现，包括表结构定义、数据库创建、数据增删改查等核心功能，并通过MainActivity实现了基本的数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、SQLiteColumn类(存储表名和字段名):

public class MySQLiteColumn implements Serializable{
	public static final String STUDENT_TABLE="student";
	
	public static final String NAME = "name";
	public static final String SEX = "sex";

}

二、MySQLiteOpenHelper类(建表):

public class MySQliteOpenHelper extends SQLiteOpenHelper{
	

	public static final int VEISION = 2;//数据库的版本号，即修改记录
	public static final String SQLITE_NAME = "student";//数据库的名称
	
	
	public MySQliteOpenHelper(Context context) {
		super(context, SQLITE_NAME, null, VEISION);
	}

	//创建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		createTables(db);
//		db.execSQL("");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		switch (oldVersion) {
		case 1://处理升级1版本的升级
//			createTeacherTable(db);
		//case 2://处理2版本的升级
		//case 3:
			break;
		default:
			break;
		}
	}
	

	//创建表
	private void createTables(SQLiteDatabase db) {
		//version1   //msdb.executeSQL(sql)
		db.execSQL("create table if not exists Student(_id INTEGER primary key autoincrement,name varchar(20) not null,sex varchar(2))");
//		db.execSQL("");
		//version2 新增教师表
//		createTeacherTable(db);
	}
	
	private void createTeacherTable(SQLiteDatabase db){
		db.execSQL("create table if not exists Teacher(_id INTEGER primary key autoincrement,name varchar(20) not null,sex varchar(2))");
	}
	
	//关闭游标
	public void closeCursor(Cursor cursor){
		if(cursor == null){
			return;
		}
		cursor.close();
	}
	
	//关闭db
	public void closeDataBase(SQLiteDatabase db){
		if(db == null){
			return ;
		}
		db.close();
	}
	
	//关闭游标和db
	public void closeDbAndCursor(SQLiteDatabase db,Cursor cursor){
		closeCursor(cursor);
		closeDataBase(db);
	}
}

三、MySQLiteDateBase类(操作表):

public class MySQLiteDateBase {
	
	private static MySQLiteDateBase mydb;
	private MySQliteOpenHelper openHelper;
	
	/**
	 * 构造方法私有化
	 * @param context
	 */
	private MySQLiteDateBase(Context context) {
		super();
		openHelper = new MySQliteOpenHelper(context);
	}
	
	/**
	 * 获取MySQLiteDateBase
	 * @param context
	 * @return
	 */
	public static synchronized MySQLiteDateBase getInstance(Context context){
		if(mydb == null){
			mydb = new MySQLiteDateBase(context);
		}
		return mydb;
	}
	
	
	/**
	 * db.execSQL(sql);
	 * 添加数据到student表中
	 * @param stu
	 */
	public void addStudent(Student stu){
		SQLiteDatabase db = null;
		try {
			db = openHelper.getReadableDatabase();
			ContentValues contentValues = new ContentValues();
			contentValues.put(MySQLiteColumn.NAME, stu.getName());
			contentValues.put(MySQLiteColumn.SEX, stu.getSex());
			//向表student插入一条数据
			db.insert(MySQLiteColumn.STUDENT_TABLE, null, contentValues);
//			db.execSQL("");
		} catch (Exception e) {
		}finally{
			openHelper.closeDataBase(db);
		}
		
	}
	
	
	
	
	/**
	 * 查询 Student表
	 * @param table 表名
	 * @param columns 列名的字符串数组
	 * @param selection 查询条件
	 * @param selectionArgs 查询条件字符串的占位符的值
	 * @param groupBy 分组
	 * @param having 分组之后的数据筛选
	 * @param orderBy 排序
	 * @param limit 分页
	 * 
	 * @return
	 */
	public ArrayList<Student> queryStudent(){
		ArrayList<Student> list = new ArrayList<Student>();
		Cursor cursor = null;
		SQLiteDatabase db = null;
		try {
			db = openHelper.getReadableDatabase();
			//查询方法
			cursor = db.query(MySQLiteColumn.STUDENT_TABLE, new String[]{MySQLiteColumn.NAME,MySQLiteColumn.SEX}, null, null, null, null, null, null);
//			cursor = db.execSQL("");
			int nameIndex = cursor.getColumnIndex(MySQLiteColumn.NAME);
			int sexIndex = cursor.getColumnIndex(MySQLiteColumn.SEX);
			while (cursor.moveToNext()) {
				String name = cursor.getString(nameIndex);
				String sex = cursor.getString(sexIndex);
				Student stu = new Student(name, sex);
				list.add(stu);
			}
		} catch (Exception e) {
		}finally{
			openHelper.closeDbAndCursor(db, cursor);
		}
		return list;
	}
	
	/**
	 * db.rawQuery(sql, args);
	 * 从学生表中查询name符合条件的数据
	 * @return 
	 * */
	
	public ArrayList<Student> queryStudent(String name){
		ArrayList<Student> list = new ArrayList<Student>();
		Cursor cursor= null;
		SQLiteDatabase db = null;
		try {			                                                                      // "   name = aaa ";
			db = openHelper.getReadableDatabase();   //id integer,title text,icon text,desc text,reviewcount integer
			cursor = db.query(MySQLiteColumn.STUDENT_TABLE, new String[]{MySQLiteColumn.NAME}, MySQLiteColumn.NAME+"= '"+name+"'", null, null, null, null);
//		第一个参数是sql语句，第二个参数是获取的字段
//			db.rawQuery(""，new String[]{} );
			int nameIndex = cursor.getColumnIndex(MySQLiteColumn.NAME);
			int sexInedex = cursor.getColumnIndex(MySQLiteColumn.SEX);
			while (cursor.moveToNext()) {
				String namestr = cursor.getString(nameIndex);
				String sexStr = cursor.getString(sexInedex);
				Student stu = new Student(namestr, sexStr);
				list.add(stu);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			openHelper.closeDbAndCursor(db, cursor);
		}
		return list;
		
	}
	
	/**
	 * 根据name删除数据 
	 * name  条件  
	 * @param name
	 */
	public boolean deletStudent(String name){
		
		SQLiteDatabase db = null;
		try {
			db = openHelper.getReadableDatabase();
			//删除一条数据
			int i = db.delete(MySQLiteColumn.STUDENT_TABLE, MySQLiteColumn.NAME+" = '"+name+"'", null);
//			db.execSQL("");
			if(i!=0){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			openHelper.closeDataBase(db);
		}
		return false;
	}
	
	
	/**
	 * 修改
	 * @param stu
	 * @return
	 */
	public boolean updateStudent(Student stu,String name){
		SQLiteDatabase db = null;
		try {
			db = openHelper.getReadableDatabase();
			ContentValues contentValues = new ContentValues();
			contentValues.put(MySQLiteColumn.NAME, stu.getName());
			contentValues.put(MySQLiteColumn.SEX, stu.getSex());
			
			int count = db.update(MySQLiteColumn.STUDENT_TABLE, contentValues, MySQLiteColumn.NAME+" = '"+ name +"'", null);
//			db.execSQL("");
			if(count > 0){
				return true;
			}
		} catch (Exception e) {
			
		}finally{
			openHelper.closeDataBase(db);
		}
		return false;
	}
}

四、Student实体类:

public class Student {
	
	private String name;
	private String sex;
	public Student(String name, String sex) {
		super();
		this.name = name;
		this.sex = sex;
	}
	public Student() {
		super();
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	@Override
	public String toString() {
		return "Student [name=" + name + ", sex=" + sex + "]";
	}
}

五、MainActivity类:

public class MainActivity extends Activity implements OnItemLongClickListener {
	private EditText et_name,et_sex;
	private ListView tv_msg;
	private MySQLiteDateBase db ;
	private ArrayAdapter<String> adapter;
	private ArrayList<String> list;
	private ArrayList<Student> listStu;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        initView();
    }

    //实例化所有控件
	private void initView() {
		et_name = (EditText) findViewById(R.id.et_name);
		et_sex = (EditText) findViewById(R.id.et_sex);
		tv_msg = (ListView) findViewById(R.id.tv_msg);
		
		db = MySQLiteDateBase.getInstance(this);
		list = new ArrayList<String>();
		adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, list);
		tv_msg.setAdapter(adapter);
		tv_msg.setOnItemLongClickListener(this);
	}
	
	
	
	public void clickBtn(View v){
		switch (v.getId()) {
		case R.id.btn_save://保存
			writeData();
			break;
		case R.id.btn_select://查询
			readData();
			break;
		
		case R.id.btn_update://修改
			upDate();
			break;
			

		default:
			break;
		}
	}

	//修改数据
	private void upDate() {
		boolean isupDate = db.updateStudent(new Student("网五网呜呜", "好好的"),"saa");
		if(isupDate){
			list.clear();
			ArrayList<Student> arrayList = db.queryStudent();
			for (int i = 0; i < arrayList.size(); i++) {
				list.add(arrayList.get(i).toString());
			}
		}
		adapter.notifyDataSetChanged();
	}

	private void readData() {
		if(listStu!=null){
			listStu.clear();
		}
		if(list!=null){
			list.clear();
		}
		listStu = db.queryStudent();
		for (int i = 0; i < listStu.size(); i++) {
			list.add(listStu.get(i).toString());
		}
		adapter.notifyDataSetChanged();
	}

	private void writeData() {
		if(!et_name.getText().equals("") && !et_sex.getText().equals("")){
			db.addStudent(new Student(et_name.getText().toString(),et_sex.getText().toString()));
			et_name.setText("");
			et_sex.setText("");
		}
	}
	
	

		@Override
		public boolean onItemLongClick(AdapterView<?> parent, View view,
				int position, long id) {
			boolean isSuccess = db.deletStudent(listStu.get(position).getName());
			if(isSuccess){
				list.remove(position);
				adapter.notifyDataSetChanged();
			}
			return true;
		}
}