Android：数据库增删改查、SQLite、ORM、Cursor
1.继承类SQLiteOpenHelper： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 public class MySQLiteHelper extends SQLiteOpenHelper { //重写构造方法 public MySQLiteHelper(Context context, String name, CursorFactory factory, int version) { super (context, name, factory, version); } //创建表 @Override public void onCreate(SQLiteDatabase db) { db.execSQL( "CREATE TABLE person(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),phone VARCHAR(20))" ); db.execSQL( "CREATE TABLE student(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),phone VARCHAR(20))" ); } //升级表（当Database的Version低于当前new里的Version，直接执行下面方法） @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { int v = newVersion - oldVersion; switch (v) { case 3 : db.execSQL( "ALTER TABLE person ADD salary3 VARCHAR(20)" ); case 2 : db.execSQL( "ALTER TABLE person ADD salary2 VARCHAR(20)" ); case 1 : db.execSQL( "ALTER TABLE person ADD salary1 VARCHAR(20)" ); default : break ; } } } 2.实现增删改查： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 /** * CREATE TABLE person ( personid INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), phone VARCHAR(20) ) DROP TABLE person //删除表 INSERT INTO person(name,phone) VALUES ("rongqin","1598088588") //插入 UPDATE person SET name="Andfeel" WHERE personid=2 //改 ALTER TABLE person ADD salary //增加列 DELETE FROM person WHERE name="Andfeel" //删除 SELECT * FROM person WHERE name="Andfeel" SELECT phone,name FROM person SELECT name,phone FROM person ORDER BY name ASC"//ASC升序 DESC降序 * */ public class MainActivity extends Activity implements OnClickListener { private TextView textView; private MySQLiteHelper mDBHelper; private MySQLiteHelper bHelper; @Override protected void onCreate(Bundle savedInstanceState) { super .onCreate(savedInstanceState); setContentView(R.layout.activity_main); findViewById(R.id.btn__create_table).setOnClickListener( this ); findViewById(R.id.btn_delete_table).setOnClickListener( this ); findViewById(R.id.btn_alter_table).setOnClickListener( this ); findViewById(R.id.btn_insert).setOnClickListener( this ); findViewById(R.id.btn_delete).setOnClickListener( this ); findViewById(R.id.btn_update).setOnClickListener( this ); findViewById(R.id.btn_select).setOnClickListener( this ); textView = (TextView) findViewById(R.id.textView1); //创建数据库 mDBHelper = new MySQLiteHelper( this , "one.db" , null , 3 ); } @Override public boolean onCreateOptionsMenu(Menu menu) { getMenuInflater().inflate(R.menu.activity_main, menu); return true ; } @Override public void onClick(View v) { switch (v.getId()) { case R.id.btn__create_table: btnCreateTable(); break ; case R.id.btn_delete_table: btnDeleteTable(); break ; case R.id.btn_alter_table: btnAlterTable(); break ; case R.id.btn_insert: btnInsert(); break ; case R.id.btn_delete: btnDelete(); break ; case R.id.btn_update: btnUpdate(); break ; case R.id.btn_select: btnSelect(); break ; default : break ; } } private void btnSelect() //查 { SQLiteDatabase db = mDBHelper.getWritableDatabase(); // Cursor cursor = db.rawQuery("SELECT * FROM person WHERE name = ?", new String[]{"rongqin"}); // cursor.moveToFirst(); Cursor cursor = db.query( "person" , //表名 new String[]{ "phone" , "name" }, //查询的列名 "name=?" , //查询条件 new String[]{ "huangxx" }, //查询参数 null , null , "phone DESC" ); //排序，升序为ASC，降序为DESC boolean hasdata = cursor.moveToFirst(); //游标移到第一行 StringBuilder sb = new StringBuilder(); while (hasdata) //是否有数据 { int columnIndex = cursor.getColumnIndex( "name" ); //得到name的列数 String name = cursor.getString(columnIndex); //得到name这一列的值 int columnIndex2 = cursor.getColumnIndex( "phone" ); String phone = cursor.getString(columnIndex2); sb.append( "name:" ).append(name).append( " phone:" ).append(phone).append( "--" ); hasdata = cursor.moveToNext(); //游标移动到下一行，判断是否有值 } textView.setText(sb.toString()); } private void btnUpdate() //改 { SQLiteDatabase db = mDBHelper.getWritableDatabase(); // db.execSQL("UPDATE person SET phone='0592xxxxxx' WHERE personid=5"); ContentValues values = new ContentValues(); values.put( "name" , "haoyouduo" ); values.put( "phone" , "666666" ); db.update( "person" , values , "personid=?" , new String[]{ "8" }); } private void btnDelete() //删 { SQLiteDatabase db = mDBHelper.getWritableDatabase(); // db.execSQL("DELETE FROM person WHERE name='Andfeel'"); db.delete( "person" , "name = ? and personid = ?" , new String[]{ "haoyouduo" , "5" }); } private void btnInsert() //增 { SQLiteDatabase db = mDBHelper.getWritableDatabase(); // db.execSQL("INSERT INTO person(name,phone) VALUES ('rongqin',1598088588)"); ContentValues values = new ContentValues(); values.put( "name" , "huangxx" ); values.put( "phone" , "158888" ); db.insert( "person" , null , values ); } private void btnAlterTable() //扩展表 { } private void btnDeleteTable() //删除表 { } private void btnCreateTable() //创建表 { } } 其他总结: 1.关闭数据源 1 2 3 4 5 6 7 8 9 10 /** * 关闭数据源 */ public void closeConnection() { if (mDb != null && mDb.isOpen()) mDb.close(); if (mDbHelper != null ) mDbHelper.close(); } 本文转自 glblong 51CTO博客，原文链接：http://blog.51cto.com/glblong/1216877，如需转载请自行联系原作者
