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 personSELECT 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，如需转载请自行联系原作者