SQLite使用基础

关注

原创最新推荐文章于 2023-09-04 20:28:01 发布 516 阅读 AI 写同款· GEO 优化 ›

Sqlite3学习笔记 1、触发器 Trigger 说明： a)sqlite3触发器只支持FOR EACH ROW,不支持FOR EACH STATMENT,即只能对行的增删改进行监听，不能对单条SQL语句进行监听 b)sqlite3触发器触发的执行结果只能执行DML操作，无法执行DDL操作基本用法：阅读详情

数据库的增删改查操作，实现对封装了bean的操作。

建表：

@Override
public void onCreate(SQLiteDatabase db) {
	String sql = "CREATE TABLE " + TABLE_NAME + "(" + _ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + _USERNAME
			+ " TEXT NOT NULL," + _PASSWORD + " TEXT)";
	Log.i(TAG, sql);
	db.execSQL(sql);
}

增加数据：

①利用sql语句

public void add(User u) {
	String sql = "INSERT INTO " + TABLE_NAME + "(" + _USERNAME + ", "
			+ _PASSWORD + ") VALUES(?,?)";
	Log.i(TAG, sql);
	db.execSQL(sql, new Object[] { u.getUserName(), u.getPassWord() });
}

②高级做法

public void add_(User u) {
	ContentValues values = new ContentValues();
 
	values.put(_USERNAME, u.getUserName());
	values.put(_PASSWORD, u.getPassWord());
	db.insert(TABLE_NAME, null, values);
}

db.insert(TABLE_NAME, null, values);

①参数一：表名

②参数二：可选的，一般为空，当values参数为空或者里面没有内容的时候，insert是会失败的(底层数据库不允许插入一个空行)，为了防止这种情况，我们要在这里设置一个列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null,然后再向数据库中插入。

删除数据：

①利用sql语句

public void del(int id) {
	String sql = "DELETE FROM " + TABLE_NAME + " WHERE " + _ID + "= ?";
	Log.i(TAG, sql);
	db.execSQL(sql, new Object[] { id });
}

②高级做法

public void del_(int id) {
	String whereClause = _ID + " = ?";
	String[] whereArgs = { id + "" };
	db.delete(TABLE_NAME, whereClause, whereArgs);
}

db.delete(TABLE_NAME, whereClause, whereArgs)

参数一：表名

参数二：where后面添加的条件，看好格式

参数三：条件中？参数的值的数组

更改数据：

①利用sql

public void update(User u) {
	String sql = "UPDATE " + TABLE_NAME + " SET " + _USERNAME + " = ?,"+ _PASSWORD + "= ?" + " WHERE " + _ID + "=?";
	Log.i(TAG, sql);
	db.execSQL(sql,	new Object[] { u.getUserName(), u.getPassWord(), u.getId() });
}

②推荐做法

public void update_(User u) {
 
	ContentValues values = new ContentValues();
	values.put(_USERNAME, u.getUserName());
	values.put(_PASSWORD, u.getPassWord());
 
	String whereClause = _ID + "=?";   //可以有多个条件的
	String[] whereArgs = { u.getId() + "" };
	db.update(TABLE_NAME, values, whereClause, whereArgs);
}

db.update(TABLE_NAME, values, whereClause, whereArgs) ：荐做法

参数一：表名

参数二：更改之后的ContentValues 类型的值

参数三：条件语句，格式

参数四：条件中?号所对应的值

查询数据：

①sql语句做法

public List<User> queryAll() {
	List<User> list = new ArrayList<User>();
	String sql = "SELECT * FROM " + TABLE_NAME;
	Log.i(TAG, sql);
	Cursor cursor = db.rawQuery(sql, null);
 
	while (cursor.moveToNext()) {
		User u = new User();
		u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));
		u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));
		u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));
		list.add(u);
	}
 
	return list;
}

②高级用法

public List<User> queryAll_() {
	List<User> list = new ArrayList<User>();
 
	Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null,
			null);
	while (cursor.moveToNext()) {
		User u = new User();
		u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));
		u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));
		u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));
		list.add(u);
	}
	return list;
}

db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy) 上面都给空，代表就是select * from user;

参数一：表名

参数二：需要查询的字段

参数三：筛选的语句，类似于where中的条件

参数四：筛选条件中的值

参数四：为分组增加条件

参数五：分组之后的条件

参数六：排序

还有三个构造方法。看名称就能看懂。

实现分页的功能的查询：

public Cursor query(int currentPage,int pageSize){
	int start=(currentPage-1)*pageSize;
	String limit=start+","+pageSize;  //和sql语句基本一样
	Cursor cursor=db.query(TABLE_NAME, null, null, null, null, null, null, limit);
	return cursor;
}

整体的代码：

import java.util.ArrayList;
import java.util.List;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
import com.mixm.bean.User;
 
public class UserDBHelper {
	private static final String TAG = "UserDBHelper";
	private static final String DATABASE_NAME = "user.db";
	private static final int VERSISON = 1;
	private static final String TABLE_NAME = "user";
	private static final String _ID = "id";
	private static final String _USERNAME = "username";
	private static final String _PASSWORD = "password";
 
	private UserHelper userHelper;
	private SQLiteDatabase db;
 
	// 内部类
	class UserHelper extends SQLiteOpenHelper {
 
		public UserHelper(Context context) {
			super(context, DATABASE_NAME, null, VERSISON);
		}
 
		@Override
		public void onCreate(SQLiteDatabase db) {
			String sql = "CREATE TABLE " + TABLE_NAME + "(" + _ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + _USERNAME
					+ " TEXT NOT NULL," + _PASSWORD + " TEXT)";
			Log.i(TAG, sql);
			db.execSQL(sql);
		}
 
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
		}
 
	}
 
	public UserDBHelper(Context context) {
		userHelper = new UserHelper(context);
		db = userHelper.getWritableDatabase();
	}
 
	// 普通
	public void add(User u) {
		String sql = "INSERT INTO " + TABLE_NAME + "(" + _USERNAME + ", "
				+ _PASSWORD + ") VALUES(?,?)";
		Log.i(TAG, sql);
		db.execSQL(sql, new Object[] { u.getUserName(), u.getPassWord() });
	}
 
	// 高级 一般做法
	public void add_(User u) {
		ContentValues values = new ContentValues();
 
		values.put(_USERNAME, u.getUserName());
		values.put(_PASSWORD, u.getPassWord());
		db.insert(TABLE_NAME, null, values);
	}
 
	public void del(int id) {
		String sql = "DELETE FROM " + TABLE_NAME + " WHERE " + _ID + "= ?";
		Log.i(TAG, sql);
		db.execSQL(sql, new Object[] { id });
	}
 
	public void del_(int id) {
		String whereClause = _ID + " = ?";
		String[] whereArgs = { id + "" };
		db.delete(TABLE_NAME, whereClause, whereArgs);
	}
 
	public void update(User u) {
		String sql = "UPDATE " + TABLE_NAME + " SET " + _USERNAME + " = ?,"
				+ _PASSWORD + "= ?" + " WHERE " + _ID + "=?";
		Log.i(TAG, sql);
		db.execSQL(sql,
				new Object[] { u.getUserName(), u.getPassWord(), u.getId() });
	}
 
	public void update_(User u) {
 
		ContentValues values = new ContentValues();
		values.put(_USERNAME, u.getUserName());
		values.put(_PASSWORD, u.getPassWord());
 
		String whereClause = _ID + "=?";
		String[] whereArgs = { u.getId() + "" };
		db.update(TABLE_NAME, values, whereClause, whereArgs);
	}
 
	public List<User> queryAll() {
		List<User> list = new ArrayList<User>();
		String sql = "SELECT * FROM " + TABLE_NAME;
		Log.i(TAG, sql);
		Cursor cursor = db.rawQuery(sql, null);
 
		while (cursor.moveToNext()) {
			User u = new User();
			u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));
			u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));
			u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));
			list.add(u);
		}
 
		return list;
	}
 
	public List<User> queryAll_() {
		List<User> list = new ArrayList<User>();
		Cursor cursor = db
				.query(TABLE_NAME, null, null, null, null, null, null);
		while (cursor.moveToNext()) {
			User u = new User();
			u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));
			u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));
			u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));
			list.add(u);
		}
		return list;
	}
}