1 package com.itheima.mysqlite;
2
3 import android.content.Context;
4 import android.database.sqlite.SQLiteDatabase;
5 import android.database.sqlite.SQLiteOpenHelper;
6
7 public class DBOpenHelper extends SQLiteOpenHelper {
8
9 /**
10 * 用来打开数据库的一个工具，其中有创建和升级的方法
11 * @param context
12 * @param name
13 * @param factory
14 * @param version
15 */
16 public DBOpenHelper(Context context) {
17 super(context, "itheima", null, 3);
18 /*
19 * 参数1：上下文环境的对象，用来确定数据库存储位置
20 * 参数2：数据库文件的名字
21 * 参数3：用来创建结果集的工厂，null为使用默认的工厂
22 * 参数4：数据库的版本，从1开始
23 */
24 }
25
26 @Override
27 public void onCreate(SQLiteDatabase db) {
28 System.out.println("onCreate");
29 db.execSQL("CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))");
30 }
31
32 @Override
33 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
34 System.out.println("onUpgrade");
35 db.execSQL("ALTER TABLE person ADD balance INTEGER");
36 }
37 }
1 package com.itheima.mysqlite;
2
3 public class Person {
4 private Integer id;
5 private String name;
6 private Integer balance;
7
8 public Person() {
9 super();
10 }
11
12 public Person(String name, Integer balance) {
13 super();
14 this.name = name;
15 this.balance = balance;
16 }
17
18 public Person(Integer id, String name, Integer balance) {
19 super();
20 this.id = id;
21 this.name = name;
22 this.balance = balance;
23 }
24
25 public Integer getId() {
26 return id;
27 }
28
29 public void setId(Integer id) {
30 this.id = id;
31 }
32
33 public String getName() {
34 return name;
35 }
36
37 public void setName(String name) {
38 this.name = name;
39 }
40
41 public Integer getBalance() {
42 return balance;
43 }
44
45 public void setBalance(Integer balance) {
46 this.balance = balance;
47 }
48
49 @Override
50 public String toString() {
51 return "Person [id=" + id + ", name=" + name + ", balance=" + balance + "]";
52 }
53
54 }
1 //SQL语句操作方法
2
3 package com.itheima.mysqlite;
4
5 import java.util.ArrayList;
6 import java.util.List;
7
8 import android.content.Context;
9 import android.database.Cursor;
10 import android.database.SQLException;
11 import android.database.sqlite.SQLiteDatabase;
12
13 public class ClassicPersonDao {
14 private Context context;
15
16 public ClassicPersonDao(Context context) {
17 this.context = context;
18 }
19
20 public void insert(Person p){
21 DBOpenHelper helper = new DBOpenHelper(context);
22 SQLiteDatabase db = helper.getWritableDatabase();
23 db.execSQL("INSERT INTO person(name, balance) VALUES(?, ?)", new Object[] { p.getName(), p.getBalance() });
24 db.close();
25 }
26 public void delete(int id){
27 DBOpenHelper helper = new DBOpenHelper(context);
28 SQLiteDatabase db = helper.getWritableDatabase();
29 db.execSQL("DELETE FROM person WHERE id=?", new Object[]{id});
30 db.close();
31 }
32 public void update(Person p){
33 DBOpenHelper helper = new DBOpenHelper(context);
34 SQLiteDatabase db = helper.getWritableDatabase();
35 db.execSQL("UPDATE person SET name=?, balance=? WHERE id=?", new Object[] {p.getName(), p.getBalance(), p.getId()});
36 db.close();
37 }
38 public Person query(int id){
39 DBOpenHelper helper = new DBOpenHelper(context);
40 SQLiteDatabase db = helper.getReadableDatabase();//如果数据库不可写，getReadableDatabase()方法可以执行
41 Cursor c = db.rawQuery("SELECT * FROM person WHERE id=? ", new String[]{ id + ""});
42
43 Person p = null;
44 if(c.moveToNext()){ //将结果集向后移动，并且返回是否成功
45 String name = c.getString(c.getColumnIndex("name"));
46 int balance = c.getInt(c.getColumnIndex("balance"));
47 p = new Person(name, balance);
48 }
49 return p;
50 }
51
52 public List<Person> queryAll(){
53 DBOpenHelper helper = new DBOpenHelper(context);
54 SQLiteDatabase db = helper.getReadableDatabase();//如果数据库不可写，getReadableDatabase()方法可以执行
55 Cursor c = db.rawQuery("SELECT id, name, balance FROM person",null);
56 List<Person> persons = new ArrayList<Person>();
57 while (c.moveToNext()){
58 persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));
59 }
60 c.close();
61 db.close();
62 return persons;
63 }
64
65 /**
66 * 分页查询 指定页数和每页容量
67 * @param pageNum 页码
68 * @param capacity 每页大小
69 * @return 指定页上的Person 集合
70 */
71 public List<Person> queryPage(int pageNum, int capacity){
72 DBOpenHelper helper = new DBOpenHelper(context);
73 SQLiteDatabase db = helper.getReadableDatabase();
74 String offset = (pageNum - 1) * capacity + "";
75 String limit = capacity + "";
76 Cursor c = db.rawQuery("SELECT id, name, balance FROM person LIMIT ?,?",new String[]{offset , limit});
77 List<Person> persons = new ArrayList<Person>();
78 while (c.moveToNext()){
79 persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));
80 }
81 c.close();
82 db.close();
83 return persons;
84 }
85 /**
86 * 查询数据库中有多少条记录
87 * @return
88 */
89 public int queryCount(){
90 DBOpenHelper helper = new DBOpenHelper(context);
91 SQLiteDatabase db = helper.getReadableDatabase();//如果数据库不可写，getReadableDatabase()方法可以执行
92 Cursor c = db.rawQuery("SELECT COUNT(*) FROM person",null);
93 c.moveToNext();
94 int count = c.getInt(0);
95 c.close();
96 db.close();
97 return count;
98 }
99 //事务操作
100 public void remit(int from, int to, int amount){
101 DBOpenHelper helper = new DBOpenHelper(context);
102 SQLiteDatabase db = helper.getReadableDatabase();
103 try {
104 db.beginTransaction(); //开启事物
105 db.execSQL("UPDATE person SET balance=balance-? WHERE id=?", new Object[]{amount, from});
106 db.execSQL("UPDATE person SET balance=balance+? WHERE id=?", new Object[]{amount, to});
107 db.setTransactionSuccessful(); //设置事物成功，设置成功之前的操作在事物结束的时候会被提交
108 } finally {
109 db.endTransaction(); //结束事物，如果不结束其会超时自动结束
110 db.close();
111 }
112 }
113 }
1 //另一种操作方法
2 package com.itheima.mysqlite;
3
4 import java.util.ArrayList;
5 import java.util.List;
6
7 import android.content.ContentValues;
8 import android.content.Context;
9 import android.database.Cursor;
10 import android.database.sqlite.SQLiteDatabase;
11
12 public class PersonDao {
13 private Context context;
14
15 public PersonDao(Context context) {
16 this.context = context;
17 }
18
19 public long insert(Person p){
20 DBOpenHelper helper = new DBOpenHelper(context);
21 SQLiteDatabase db = helper.getWritableDatabase();
22 ContentValues values = new ContentValues(); //创建一个Map集合，用来装载要插入的数据
23 values.put("name", p.getName());
24 values.put("balance", p.getBalance());
25 long id = db.insert("person", "name", values);//第2个参数随便写一个列名就可以，在插入空记录时用来拼装SQL语句
26 db.close();
27 return id;
28 }
29 public void delete(int id){
30 DBOpenHelper helper = new DBOpenHelper(context);
31 SQLiteDatabase db = helper.getWritableDatabase();
32 int count = db.delete("person", "id=?", new String[]{ id + ""}); //删除，并且返回影响的记录数
33 db.close();
34 }
35 public int update(Person p){
36 DBOpenHelper helper = new DBOpenHelper(context);
37 SQLiteDatabase db = helper.getWritableDatabase();
38 ContentValues values = new ContentValues();
39 values.put("name", p.getName());
40 values.put("balance", p.getBalance());
41 int count = db.update("person", values, "id=?", new String[]{p.getId()+""});
42
43 db.close();
44 return count;
45 }
46 public Person query(int id){
47 DBOpenHelper helper = new DBOpenHelper(context);
48 SQLiteDatabase db = helper.getReadableDatabase();//如果数据库不可写，getReadableDatabase()方法可以执行
49 Cursor c = db.query("person", new String[]{"name", "balance"}, "id=?", new String[]{id+""}, null, null, null);
50 Person p = null;
51 if(c.moveToNext()){ //将结果集向后移动，并且返回是否成功
52 String name = c.getString(c.getColumnIndex("name"));
53 int balance = c.getInt(c.getColumnIndex("balance"));
54 p = new Person(id, name, balance);
55 }
56 return p;
57 }
58
59 public List<Person> queryAll(){
60 DBOpenHelper helper = new DBOpenHelper(context);
61 SQLiteDatabase db = helper.getReadableDatabase();//如果数据库不可写，getReadableDatabase()方法可以执行
62 Cursor c = db.query("person", null, null, null, null, null, "balance DESC"); //递减查询所有
63 List<Person> persons = new ArrayList<Person>();
64 while (c.moveToNext()){
65 persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));
66 }
67 c.close();
68 db.close();
69 return persons;
70 }
71
72 /**
73 * 分页查询 指定页数和每页容量
74 * @param pageNum 页码
75 * @param capacity 每页大小
76 * @return 指定页上的Person 集合
77 */
78 public List<Person> queryPage(int pageNum, int capacity){
79 DBOpenHelper helper = new DBOpenHelper(context);
80 SQLiteDatabase db = helper.getReadableDatabase();
81 String offset = (pageNum - 1) * capacity + "";
82 String limit = capacity + "";
83 Cursor c = db.query("person", null, null, null, null, null, null, offset + "," + limit);
84 List<Person> persons = new ArrayList<Person>();
85 while (c.moveToNext()){
86 persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));
87 }
88 c.close();
89 db.close();
90 return persons;
91 }
92 /**
93 * 查询数据库中有多少条记录
94 * @return
95 */
96 public int queryCount(){
97 DBOpenHelper helper = new DBOpenHelper(context);
98 SQLiteDatabase db = helper.getReadableDatabase();//如果数据库不可写，getReadableDatabase()方法可以执行
99 Cursor c = db.query("person", new String[]{"COUNT(*)"}, null, null, null, null, null);
100 c.moveToNext();
101 int count = c.getInt(0);
102 c.close();
103 db.close();
104 return count;
105 }
106 //事务操作
107 public void remit(int from, int to, int amount){
108 DBOpenHelper helper = new DBOpenHelper(context);
109 SQLiteDatabase db = helper.getReadableDatabase();
110 try {
111 db.beginTransaction(); //开启事物
112 db.execSQL("UPDATE person SET balance=balance-? WHERE id=?", new Object[]{amount, from});
113 db.execSQL("UPDATE person SET balance=balance+? WHERE id=?", new Object[]{amount, to});
114 db.setTransactionSuccessful(); //设置事物成功，设置成功之前的操作在事物结束的时候会被提交
115 } finally {
116 db.endTransaction(); //结束事物，如果不结束其会超时自动结束
117 db.close();
118 }
119 }
120 }
1 package com.itheima.mysqlite;
2
3 import java.util.List;
4 import java.util.Random;
5
6 import android.test.AndroidTestCase;
7
8 public class DBTest extends AndroidTestCase {
9 public void testCreateDatabase(){
10 DBOpenHelper helper = new DBOpenHelper(getContext());
11 helper.getWritableDatabase(); // 获取数据库连接
12 /*
13 * 数据库不存在：创建数据库，执行onCreate()方法
14 * 数据库存在，版本没变：不执行任何方法
15 * 数据库存在，版本提升：执行onUpgrade()方法
16 */
17 }
18 public void testInsert(){
19 PersonDao dao = new PersonDao(getContext());
20 dao.insert(new Person("insert", 9999));
21 }
22
23 public void testUpdate(){
24 PersonDao dao = new PersonDao(getContext());
25 System.out.println(dao.update(new Person(209,"李四",321)));
26 }
27
28 public void testDelete(){
29 PersonDao dao = new PersonDao(getContext());
30 dao.delete(208);
31 }
32
33 public void testQuery(){
34 PersonDao dao = new PersonDao(getContext());
35 System.out.println(dao.query(234));
36 System.out.println(dao.query(2));
37 System.out.println(dao.query(302));
38 }
39
40 public void testQueryAll(){
41 PersonDao dao = new PersonDao(getContext());
42 List<Person> persons = dao.queryAll();
43 for(Person p: persons){
44 System.out.println(p);
45 }
46 }
47
48 public void testQueryPage(){
49 PersonDao dao = new PersonDao(getContext());
50 List<Person> persons = dao.queryPage(2,20);
51 for(Person p: persons){
52 System.out.println(p);
53 }
54 }
55
56 public void testQueryCount(){
57 PersonDao dao = new PersonDao(getContext());
58 System.out.println(dao.queryCount());
59 }
60
61 public void testRemit(){
62 PersonDao dao = new PersonDao(getContext());
63 dao.remit(209,208,100);
64 }
65 }
