SQLite（一）基本操作

1 public class Constant {
2 public static String DB_NAME &＃61; "Text";
3
4 public static String TABLE_NAME &＃61; "person";
5 public static String ID &＃61; "_id";
6 public static String NAME &＃61; "name";
7 public static String AGE &＃61; "age";
8 }

1 public class MySQLiteOpenHelper extends SQLiteOpenHelper {
2
3 /*
4 * context: 上下文对象
5 * name : 数据库名字
6 * factory: 游标工厂&＃xff0c;游标Cursor相当于一个数据表&＃xff0c;用来保存数据
7 * version: 数据库版本 >&＃61;1
8 */
9 public MySQLiteOpenHelper(Context context, String name,
10 CursorFactory factory, int version) {
11 super(context, name, factory, version);
12 }
13 //这个构造方法是为了简便操作
14 public MySQLiteOpenHelper(Context context){
15 super(context, Constant.DB_NAME, null, 1);
16 }
17
18 //数据库创建时回调
19 public void onCreate(SQLiteDatabase arg0) {
20 String SQL &＃61; "create table "&＃43;Constant.TABLE_NAME&＃43;"("&＃43;Constant.ID&＃43;" int(4) not null primary key,"&＃43;Constant.AGE&＃43;" int(2) not null,"&＃43;Constant.NAME&＃43;" char(20))";
21 arg0.execSQL(SQL);
22 }
23
24 //数据库更新时回调
25 public void onUpgrade(SQLiteDatabase arg0, int oldVersion, int newVersion) {
26 }
27 //数据库打开时回调&＃xff0c;一般不重写
28 public void onOpen(SQLiteDatabase arg0){
29 }
30
31 }

1 public class DBoperation2 {
2 SQLiteDatabase DB &＃61; null;
3 public DBoperation2(SQLiteDatabase DB){
4 this.DB &＃61; DB;
5 }
6 //用sql语句实现增删改&＃xff0c;实际运用中适当的让某些变量拆解SQL语句&＃xff0c;让人机交互更和谐
7 public void SQL(SQLiteDatabase DB, String SQL){
8 if(DB !&＃61; null){
9 DB.execSQL(SQL);
10 }
11 }
12 //用sql语句实现查询
13 public Cursor SQLSEL(SQLiteDatabase DB, String SQL, String[] where){
14 Cursor cursor &＃61; null;
15 if(DB !&＃61; null){
16 cursor &＃61; DB.rawQuery(SQL, where);
17 }
18 return cursor;
19 }
20 }

1 public class DBoperation {
2 SQLiteDatabase DB &＃61; null;
3 public DBoperation(SQLiteDatabase DB){
4 this.DB &＃61; DB;
5 }
6 /*
7 * 增: insert(table, nullColumnHack, values)
8 * String table : 要插入的表的名字
9 * nullColumHack: 空列填充&＃xff0c;默认值
10 * ContentValues values: 要插入的数据&＃xff08;id&＃xff0c;name即插入的数据&＃xff09;
11 * 返回值为大于0的数时表示insert&＃xff08;&＃xff09;方法执行成功
12 */
13 public long Insert(int id, int age, String name){
14 ContentValues values &＃61; new ContentValues();
15 values.put(Constant.ID, id);
16 values.put(Constant.AGE, age);
17 values.put(Constant.NAME, name);
18 long i &＃61; DB.insert(Constant.TABLE_NAME, null, values);
19 return i;
20 }
21 /*
22 * 删: delete(table, whereClause, whereArgs)
23 * String table: 要删除表的名字
24 * String whereClause: 删除的条件
25 * String[] whereArgs: 删除条件的占位符
26 * 占位符解释: ①&＃xff1a;DB.delete(Constant.TABLE_NAME, Constant.ID&＃43;"&＃61;1", null);
27 * ②&＃xff1a;DB.delete(Constant.TABLE_NAME, Constant.ID&＃43;"&＃61;?",new String[1]);效果一致
28 * 返回值为大于0的数时表示Delete&＃xff08;&＃xff09;方法执行成功&＃xff0c;这里是根据ID来删除&＃xff0c;按实际情况更改
29 */
30 public long Delete(int age){
31 long i &＃61; DB.delete(Constant.TABLE_NAME, Constant.AGE&＃43;"&＃61;"&＃43;age, null);
32 return i;
33 }
34 /*
35 * 改: update(table, values, whereClause, whereArgs)
36 * String table: 要更新表的名字
37 * ContentValues values: 要更新的数据(name即更新的内容)
38 * String whereClause: 更新的条件
39 * String[] whereArgs: 更新条件的占位符
40 * 返回值是一个整形表示更新的行数&＃xff0c;这里是根据ID更新&＃xff0c;根据实际情况更改。
41 */
42 public int Updata(String name, int age){
43 ContentValues values &＃61; new ContentValues();
44 values.put(Constant.NAME, name);
45 int i &＃61; DB.update(Constant.TABLE_NAME, values, Constant.AGE&＃43;"&＃61;"&＃43;age, null);
46 return i;
47 }
48 /*
49 * 查询: query(table, columns, selection, selectionArgs, groupBy, having, orderBy)
50 * String table: 要查询的表名字
51 * String[] columns: 要查询显示的列
52 * String selection: 查询条件
53 * String[] selectionArgs: 查询条件的占位符
54 * String groupBy: String having: String orderBy
55 * 返回只是一个游标。
56 */
57 public Cursor Select(String[] columns, String selection, String[] selectionArgs){
58 Cursor cursor &＃61; DB.query(Constant.TABLE_NAME, columns, selection, selectionArgs, null, null, null);
59 return cursor;
60 }
61 }

1 public class MainActivity extends Activity {
2
3 private MySQLiteOpenHelper helper &＃61; null;
4 private DBoperation operation &＃61; null;
5 private Cursor cursor &＃61; null;
6 private ListView lv &＃61; null;
7 private int n &＃61; 1;
8 &＃64;Override
9 protected void onCreate(Bundle savedInstanceState) {
10 super.onCreate(savedInstanceState);
11 setContentView(R.layout.activity_main);
12 lv &＃61; (ListView)findViewById(R.id.lv);
13 if(helper &＃61;&＃61; null){
14 helper &＃61; new MySQLiteOpenHelper(this);
15 }
16 }
17 //这里的条件可以根据需要输入来操作&＃xff0c;我这里是为了方便
18 public void Click(View v){
19 SQLiteDatabase DB &＃61; helper.getWritableDatabase();
20 operation &＃61; new DBoperation(DB);
21 switch(v.getId()){
22 case R.id.insert :
23 long i &＃61; operation.Insert(n,1,"LWJ");
24 if(i > 0){
25 Toast toast &＃61; Toast.makeText(MainActivity.this, "插入成功", Toast.LENGTH_SHORT);
26 toast.show();
27 } else {
28 Toast toast &＃61; Toast.makeText(MainActivity.this, "插入失败", Toast.LENGTH_SHORT);
29 toast.show();
30 }
31 n&＃43;&＃43;;
32 Show2(DB);
33 DB.close();
34 break;
35 case R.id.delete :
36 long d &＃61; operation.Delete(1);
37 if( d >0){
38 Toast toast &＃61; Toast.makeText(MainActivity.this, "删除成功", Toast.LENGTH_SHORT);
39 toast.show();
40 } else {
41 Toast toast &＃61; Toast.makeText(MainActivity.this, "删除失败", Toast.LENGTH_SHORT);
42 toast.show();
43 }
44 Show2(DB);
45 DB.close();
46 break;
47 case R.id.updata :
48 int u &＃61; operation.Updata("lwj", 1);
49 if( u >0){
50 Toast toast &＃61; Toast.makeText(MainActivity.this, "更新成功", Toast.LENGTH_SHORT);
51 toast.show();
52 } else {
53 Toast toast &＃61; Toast.makeText(MainActivity.this, "更新失败", Toast.LENGTH_SHORT);
54 toast.show();
55 }
56 Show2(DB);
57 DB.close();
58 break;
59 case R.id.select :
60 Show(DB);
61 DB.close();
62 break;
63 }
64 }
65 }