SQLite数据库

1. 加载驱动.

2. 连接数据库.

3. 操作数据库.

创建表:

create table person(

_id integer primary key,

name varchar(20),

age integer

);

添加: insert into person(name, age) values('lisi', 19);

删除: delete from person where _id = 1;

修改: update person set name = '李四' where name = '王五';

查询所有: select * from person;

查询单条: select * from person where name = 'zhangsan';

在person表中添加一个余额(balance)列balance: alter table person add balance integer;

事务:

update person set balance = balance - 50 where name = 'lisi';

update person set balance = balance + 50 where name = 'zhangsan';

// 开启事务

db.beginTransaction();

// 标记事务成功

db.setTransactionSuccessful();

// 停止事务

db.endTransaction();

案例代码:

package:com\gzy\android\sqlitedemo2\entities\Person.java

1 public class Person {
2     private int id;
3     private String name;
4     private int age;
5     //......
6 }

　　package:com\gzy\android\sqlitedemo2\db\PersonSQLiteOpenHelper.java

 1 /**
 2  * @author starMan 数据库帮助类, 用于创建和管理数据库的.
 3  * extends SQLiteOpenHelper
 4  */
 5 public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {
 6 
 7     private static final String TAG = "PersonSQLiteOpenHelper";
 8 
 9     /**
10      * 数据库的构造函数
11      *
12      * @param context
13      * @param name    数据库名称
14      * @param factory 游标工程
15      * @param version 数据库的版本号 不可以小于1
16      */
17     public PersonSQLiteOpenHelper(Context context) {
18 //        super(context, "starframing_person.db", null, 1);
19         super(context, "starframing_person.db", null, 2);   //更新版本号时调用 onUpgrade() 方法
20     }
21 
22     /**
23      * 数据库第一次创建时回调此方法. 初始化一些表
24      */
25     @Override
26     public void onCreate(SQLiteDatabase db) {
27         // 操作数据库
28         String sql = "create table person(_id integer primary key, name varchar(20), age integer);";
29         db.execSQL(sql);    // 创建person表
30     }
31 
32     /**
33      * 数据库的版本号更新时回调此方法,
34      * 更新数据库的内容(删除表, 添加表, 修改表)
35      */
36     @Override
37     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
38         if(oldVersion == 1 && newVersion == 2) {
39             Log.i(TAG, "数据库更新啦...");
40             // 在person表中添加一个余额列balance
41             db.execSQL("alter table person add balance integer;");
42         }
43 //        else if(oldVersion == 2 && newVersion == 3) {}
44     }
45 }

　　package:com\gzy\android\sqlitedemo2\dao\PersonDao.java

  1 public class PersonDao {
  2     private PersonSQLiteOpenHelper mOpenHelper; // 数据库的帮助类对象
  3 
  4     public PersonDao(Context context) {
  5         mOpenHelper = new PersonSQLiteOpenHelper(context);
  6     }
  7 
  8     /**
  9      * 添加到person表一条数据
 10      *
 11      * @param person
 12      */
 13     public void insert(Person person) {
 14         SQLiteDatabase db = mOpenHelper.getWritableDatabase(); // 打开可读写数据库对象
 15         if (db.isOpen()) { // 如果数据库打开, 执行添加操作
 16 
 17             // 执行添加到数据库的操作
 18             // db.execSQL("insert into person(name, age) values('新垣结衣', 27)");
 19             db.execSQL("insert into person(name, age) values(?, ?);",
 20                     new Object[]{person.getName(), person.getAge()});
 21             db.close(); // 关闭数据库
 22         }
 23     }
 24 
 25     /**
 26      * 通过id删除
 27      *
 28      * @param id
 29      */
 30     public void delete(int id) {
 31         SQLiteDatabase db = mOpenHelper.getWritableDatabase();
 32         if (db.isOpen()) {
 33             db.execSQL("delete from person where _id = ?;", new Integer[]{id});
 34             db.close();
 35         }
 36     }
 37 
 38     /**
 39      * 通过_id更新
 40      * @param _id
 41      * @param person
 42      */
 43     public void update(int _id, Person person) {
 44         SQLiteDatabase db = mOpenHelper.getWritableDatabase();
 45         if (db.isOpen()) {
 46             db.execSQL("update person set name = ?, age = ? where _id = ?;",
 47                     new Object[]{person.getName(), person.getAge(), _id});
 48             db.close();
 49         }
 50     }
 51 
 52     /**
 53      * 查询全部
 54      */
 55     public List<Person> queryAll() {
 56         SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 打开只读数据库对象
 57         if (db.isOpen()) {
 58             Cursor cursor = db.rawQuery("select * from person;", null);
 59             if (null != cursor && cursor.getCount() > 0) {
 60                 List<Person> list = new ArrayList<Person>();
 61                 int id;
 62                 String name;
 63                 int age;
 64                 while (cursor.moveToNext()) {
 65                     id = cursor.getInt(0);
 66                     name = cursor.getString(1);
 67                     age = cursor.getInt(2);
 68                     list.add(new Person(id, name, age));
 69                 }
 70                 cursor.close();//关闭游标
 71                 db.close();
 72                 return list;
 73             }
 74             db.close();
 75         }
 76         return null;
 77     }
 78 
 79     /**
 80      * 根据id查询单个
 81      * @param id
 82      * @return
 83      */
 84     public Person queryItem(int id) {
 85         SQLiteDatabase db = mOpenHelper.getReadableDatabase();    //打开只读数据库对象
 86         if (db.isOpen()) {
 87             Cursor cursor = db.rawQuery("select * from person where _id = ?;",
 88                     new String[]{String.valueOf(id)});
 89 //            if(cursor.getCount() > 0);
 90             if (null != cursor && cursor.moveToFirst()) {
 91                 Person person = new Person(id, cursor.getString(1), cursor.getInt(2));
 92                 cursor.close();//关闭游标
 93                 db.close();
 94                 return new Person(id, cursor.getString(1), cursor.getInt(2));
 95             }
 96             db.close();
 97         }
 98         return null;
 99     }
100 }

　　 package:com\gzy\android\sqlitedemo2\ApplicationTest.java

 1 public class ApplicationTest extends ApplicationTestCase<Application> {
 2     public ApplicationTest() {
 3         super(Application.class);
 4     }
 5 
 6     private static final String TAG = "ApplicationTest";
 7 
 8     public void test(){
 9         //什么时候创建数据库
10         PersonSQLiteOpenHelper mOpenHelper = new PersonSQLiteOpenHelper(getContext());
11 
12         // 第一次连接数据库时创建数据库文件. onCreate会被调用
13         mOpenHelper.getReadableDatabase();
14     }
15 
16     public void testInsert() {
17         PersonDao dao = new PersonDao(getContext());
18         dao.insert(new Person("户田惠梨香", 27));
19     }
20 
21     public void testDelete() {
22         PersonDao dao = new PersonDao(getContext());
23         dao.delete(1);
24     }
25 
26     public void testUptate() {
27         PersonDao dao = new PersonDao(getContext());
28         dao.update(2, new Person("崛北真希", 26));
29     }
30 
31     public void testQueryAll() {
32         PersonDao dao = new PersonDao(getContext());
33         List<Person> list = dao.queryAll();
34         for (Person person : list) {
35             Log.i(TAG, person.toString());
36         }
37     }
38 
39     public void testQueryItem() {
40         PersonDao dao = new PersonDao(getContext());
41         Person person = dao.queryItem(3);
42         Log.i(TAG, person.toString());
43     }
44 }

=============================使用Android自带API操作database===========================

package:com\gzy\android\sqlitedemo2\dao\PersonDao2.java

  1 public class PersonDao2 {
  2     private static final String TAG = "PersonDao2";
  3     
  4     private PersonSQLiteOpenHelper mOpenHelper; // 数据库的帮助类对象
  5 
  6     public PersonDao2(Context context) {
  7         mOpenHelper = new PersonSQLiteOpenHelper(context);
  8     }
  9 
 10     /**
 11      * 添加到person表一条数据
 12      *
 13      * @param person
 14      */
 15     public void insert(Person person) {
 16         SQLiteDatabase db = mOpenHelper.getWritableDatabase(); // 打开可读写数据库对象
 17         if (db.isOpen()) { // 如果数据库打开, 执行添加操作
 18 
 19             ContentValues values = new ContentValues();
 20             values.put("name" ,person.getName());   // key作为要存储的列名, value对象列的值
 21             values.put("age", person.getAge());
 22             long id = db.insert("person", null, values);
 23             Log.i(TAG, "id: " + id);
 24 
 25             db.close(); // 关闭数据库
 26         }
 27     }
 28 
 29     /**
 30      * 通过id删除
 31      *
 32      * @param id
 33      */
 34     public void delete(int id) {
 35         SQLiteDatabase db = mOpenHelper.getWritableDatabase();
 36         if (db.isOpen()) {
 37             String whereClause = "_id = ?";
 38             String[] whereArgs = {String.valueOf(id)};
 39             int count = db.delete("person", whereClause, whereArgs);
 40             Log.i(TAG, "删除了 " + count + " 行");
 41 
 42             db.close();
 43         }
 44     }
 45 
 46     /**
 47      * 通过_id更新
 48      * @param _id
 49      * @param person
 50      */
 51     public void update(int _id, Person person) {
 52         SQLiteDatabase db = mOpenHelper.getWritableDatabase();
 53         if (db.isOpen()) {
 54 //            String table, ContentValues values, String whereClause, String[] whereArgs
 55 
 56             ContentValues values = new ContentValues();
 57             values.put("name", person.getName());
 58             values.put("age", person.getAge());
 59 
 60             String whereClause = "_id = ?";
 61             String[] whereArgs = {String.valueOf(_id)};
 62             int count = db.update("person", values, whereClause, whereArgs);
 63             Log.i(TAG, "修改了 " + count + "行");
 64             db.close();
 65         }
 66     }
 67 
 68     /**
 69      * 查询全部
 70      */
 71     public List<Person> queryAll() {
 72         SQLiteDatabase db = mOpenHelper.getReadableDatabase(); // 打开只读数据库对象
 73         if (db.isOpen()) {
 74 
 75             String[] columns = {"_id", "name", "age"};//需要的列
 76             String selection = null;                    //选择条件, 给null查询所有
 77             String[] selectionArgs = null;              // 选择条件的参数, 会把选择条件中的? 替换成数据中的值
 78             String groupBy = null;                      // 分组语句  group by name
 79             String having = null;                       // 过滤语句
 80             String orderBy = "_id desc";               // 排序
 81             Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
 82             if(null != cursor && cursor.getCount() > 0) {
 83                 List<Person> list = new ArrayList<Person>();
 84                 int id;
 85                 String name;
 86                 int age;
 87                 while (cursor.moveToNext()) {
 88                     id = cursor.getInt(0);
 89                     name = cursor.getString(1);
 90                     age = cursor.getInt(2);
 91                     list.add(new Person(id, name, age));
 92                 }
 93                 cursor.close();
 94                 db.close();
 95                 return list;
 96             }
 97             db.close();
 98         }
 99         return null;
100     }
101 
102     /**
103      * 根据id查询单个
104      * @param id
105      * @return
106      */
107     public Person queryItem(int id) {
108         SQLiteDatabase db = mOpenHelper.getReadableDatabase();    //打开只读数据库对象
109         if (db.isOpen()) {
110 
111             String[] columns = null;
112             String selection = "_id = ?";
113             String[] selectionArgs = new String[] {String.valueOf(id)};
114             String groupBy = null;
115             String having = null;
116             String orderBy = null;
117             Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
118             if(null != cursor && cursor.moveToFirst()) {
119                 Person person = new Person(cursor.getInt(0), cursor.getString(1), cursor.getInt(2));
120                 cursor.close();
121                 db.close();
122                 return person;
123             }
124             db.close();
125         }
126         return null;
127     }
128 }

　　package:com\gzy\android\sqlitedemo2\ApplicationTest2.java

  1 public class ApplicationTest2 extends ApplicationTestCase<Application> {
  2     public ApplicationTest2() {
  3         super(Application.class);
  4     }
  5 
  6     private static final String TAG = "ApplicationTest";
  7 
  8     public void test(){
  9         //什么时候创建数据库
 10         PersonSQLiteOpenHelper mOpenHelper = new PersonSQLiteOpenHelper(getContext());
 11 
 12         // 第一次连接数据库时创建数据库文件. onCreate会被调用
 13         mOpenHelper.getReadableDatabase();
 14     }
 15 
 16     public void testInsert() {
 17         PersonDao2 dao = new PersonDao2(getContext());
 18         dao.insert(new Person("新垣结衣", 28));
 19     }
 20 
 21     public void testDelete() {
 22         PersonDao2 dao = new PersonDao2(getContext());
 23         dao.delete(1);
 24     }
 25 
 26     public void testUptate() {
 27         PersonDao2 dao = new PersonDao2(getContext());
 28         dao.update(2, new Person("崛北真希", 26));
 29     }
 30 
 31     public void testQueryAll() {
 32         PersonDao2 dao = new PersonDao2(getContext());
 33         List<Person> list = dao.queryAll();
 34         for (Person person : list) {
 35             Log.i(TAG, person.toString());
 36         }
 37     }
 38 
 39     public void testQueryItem() {
 40         PersonDao2 dao = new PersonDao2(getContext());
 41         Person person = dao.queryItem(3);
 42         Log.i(TAG, person.toString());
 43     }
 44 
 45     //测试事务 transaction
 46     public void testTransaction() {
 47         PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
 48         SQLiteDatabase db = openHelper.getWritableDatabase();
 49 
 50         if(db.isOpen()) {
 51             try {
 52                 //开启事务
 53                 db.beginTransaction();
 54                 //1.从张三账户中扣50块钱
 55                 db.execSQL("update person set balance = balance - 50 where name = 'zhangsan'");
 56                 //ATM机挂掉了
 57 //                int result = 10 / 0;
 58 
 59                 // 2. 向李四账户中加1000块钱
 60                 db.execSQL("update person set balance = balance + 50 where name = 'lisi';");
 61                 // 标记事务成功
 62                 db.setTransactionSuccessful();
 63             } finally {
 64                 // 停止事务
 65                 db.endTransaction();
 66             }
 67             db.close();
 68         }
 69     }
 70 
 71 
 72     /**
 73      * 使用事务transaction 节约数据库插入时间为以前的1/10
 74      */
 75     public void testTransactionInsert() {
 76         PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
 77         SQLiteDatabase db = openHelper.getWritableDatabase();
 78 
 79         if(db.isOpen()) {
 80 
 81             // 1. 记住当前的时间
 82             long start = System.currentTimeMillis();
 83 
 84             // 2. 开始添加数据
 85             try {
 86                 db.beginTransaction();
 87                 for (int i = 0; i < 10000; i++) {
 88                     db.execSQL("insert into person(name, age, balance) values('wang" + i + "', " + (10 + i) + ", " + (10000 + i) + ")");
 89                 }
 90                 db.setTransactionSuccessful();
 91             } finally {
 92                 db.endTransaction();
 93             }
 94 
 95             // 3. 记住结束时间, 计算耗时时间
 96             long end = System.currentTimeMillis();
 97 
 98             long diff = end - start;
 99             Log.i(TAG, "耗时: " + diff + "毫秒");
100 
101             db.close();
102         }
103     }
104 }

posted on 2015-12-23 18:57 starFarming 阅读(208) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

starMan_GZY

SQLite数据库

导航

公告