sqlite入门

1、db链接类

 1 package com.example.mydbtest;
 2 
 3 import android.content.Context;
 4 import android.database.sqlite.SQLiteDatabase;
 5 import android.database.sqlite.SQLiteDatabase.CursorFactory;
 6 import android.database.sqlite.SQLiteOpenHelper;
 7 
 8 public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {
 9 
10     public PersonSQLiteOpenHelper(Context context) {
11         super(context, "person.db", null, 2);
12         // TODO Auto-generated constructor stub
13     }
14 
15     @Override
16     public void onCreate(SQLiteDatabase db) {
17         // TODO Auto-generated method stub
18         //String sql = "create table persion(id integer autoincrement primary key, name varchar(20), number varchar(20))";
19         db.execSQL("create table person(id integer primary key autoincrement, name varchar(20), number varchar(20))");
20     }
21 
22     /*
23      * 数据库的版本发生变化时，该方法被调用
24      * 
25      */
26     @Override
27     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
28         // TODO Auto-generated method stub
29         db.execSQL("alter table person add account varchar(20)");
30 
31     }
32 
33 }

2、直接sql类

 1 package com.example.mydbtest.dao;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 import android.content.Context;
 7 import android.database.Cursor;
 8 import android.database.sqlite.SQLiteDatabase;
 9 
10 import com.example.mydbtest.PersonSQLiteOpenHelper;
11 import com.example.mydbtest.domain.Person;
12 
13 public class PersonDao {
14 
15     private PersonSQLiteOpenHelper helper;
16     public PersonDao(Context context){
17         helper = new PersonSQLiteOpenHelper(context);
18     }
19     
20     public void add(String name,String number){
21         SQLiteDatabase db = helper.getWritableDatabase();
22         db.execSQL("insert into person(name,number) values(?,?)", new Object[]{name,number});
23         db.close();
24     }
25     
26     public boolean find(String name){
27         SQLiteDatabase db = helper.getWritableDatabase();
28         Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
29         boolean result = cursor.moveToNext();
30         cursor.close();
31         db.close();
32         
33         return result;
34     }
35     
36     public void update(String name, String newnumber){
37         SQLiteDatabase db = helper.getWritableDatabase();
38         db.execSQL("update person set number=? where name=?", new Object[]{newnumber,name});
39         db.close();
40     }
41     
42     public void delete(String name){
43         SQLiteDatabase db = helper.getWritableDatabase();
44         db.execSQL("delete from person where name=?", new Object[]{name});
45         db.close();
46     }
47     
48     public List<Person> findAll(){
49         SQLiteDatabase db = helper.getWritableDatabase();
50         Cursor cursor = db.rawQuery("select * from person", null);
51         List<Person> persons = new ArrayList<Person>();
52         while(cursor.moveToNext()){
53             int id = cursor.getInt(cursor.getColumnIndex("id"));
54             String name = cursor.getString(cursor.getColumnIndex("name"));
55             String number = cursor.getString(cursor.getColumnIndex("number"));
56             Person person = new Person(id,name,number);
57             persons.add(person);
58         }
59         cursor.close();
60         db.close();
61         return persons;
62     }
63 }

3、非直接sql类

 1 package com.example.mydbtest.dao;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 import android.content.ContentValues;
 7 import android.content.Context;
 8 import android.database.Cursor;
 9 import android.database.sqlite.SQLiteDatabase;
10 
11 import com.example.mydbtest.PersonSQLiteOpenHelper;
12 import com.example.mydbtest.domain.Person;
13 
14 public class PersonDao2 {
15 
16     private PersonSQLiteOpenHelper helper;
17     public PersonDao2(Context context){
18         helper = new PersonSQLiteOpenHelper(context);
19     }
20     
21     public long add(String name,String number,int account){
22         SQLiteDatabase db = helper.getWritableDatabase();
23         ContentValues values = new ContentValues();
24         values.put("name", name);
25         values.put("number", number);
26         values.put("account", account);
27         long id = db.insert("person", null, values);
28         db.close();
29         return id;
30     }
31     
32     public boolean find(String name){
33         SQLiteDatabase db = helper.getWritableDatabase();
34         //Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
35         Cursor cursor = db.query("person", null, "name=?", new String[]{name}, null, null, null);
36         boolean result = cursor.moveToNext();
37         cursor.close();
38         db.close();
39         
40         return result;
41     }
42     
43     public int update(String name, String newnumber){
44         SQLiteDatabase db = helper.getWritableDatabase();
45         //db.execSQL("update person set number=? where name=?", new Object[]{newnumber,name});
46         ContentValues values = new ContentValues();
47         values.put("number", newnumber);
48         int id = db.update("person", values, "name=?", new String[]{name});
49         db.close();
50         return id;
51     }
52     
53     public int delete(String name){
54         SQLiteDatabase db = helper.getWritableDatabase();
55         //db.execSQL("delete from person where name=?", new Object[]{name});
56         int rows = db.delete("person", "name=?", new String[]{name});
57         db.close();
58         return rows;
59     }
60     
61     public List<Person> findAll(){
62         SQLiteDatabase db = helper.getWritableDatabase();
63         //Cursor cursor = db.rawQuery("select * from person", null);
64         Cursor cursor = db.query("person", new String[]{"id","name","number"}, null, null, null, null, null);
65         List<Person> persons = new ArrayList<Person>();
66         while(cursor.moveToNext()){
67             int id = cursor.getInt(cursor.getColumnIndex("id"));
68             String name = cursor.getString(cursor.getColumnIndex("name"));
69             String number = cursor.getString(cursor.getColumnIndex("number"));
70             Person person = new Person(id,name,number);
71             persons.add(person);
72         }
73         cursor.close();
74         db.close();
75         return persons;
76     }
77 
78 }

4、单元测试类

 1 package com.example.mydbtest.test;
 2 
 3 import java.util.List;
 4 
 5 import com.example.mydbtest.PersonSQLiteOpenHelper;
 6 import com.example.mydbtest.dao.PersonDao2;
 7 import com.example.mydbtest.domain.Person;
 8 
 9 import android.database.sqlite.SQLiteDatabase;
10 import android.test.AndroidTestCase;
11 
12 public class TestPersonDB extends AndroidTestCase {
13 
14     public void testCreateDb() throws Exception{
15         PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());
16         helper.getWritableDatabase();
17     }
18     
19     public void testAdd() throws Exception{
20         PersonDao2 dao = new PersonDao2(getContext());
21         dao.add("wangwu", "123",5000);
22         //dao.add("zhangsan", "456", 2000);
23     }
24     
25     public void testFind() throws Exception{
26         PersonDao2 dao = new PersonDao2(getContext());
27         boolean result = dao.find("wangwu");
28         assertEquals(true,result);
29     }
30     
31     public void testUpdate() throws Exception{
32         PersonDao2 dao = new PersonDao2(getContext());
33         dao.update("wangwu", "321");
34     }
35     
36     public void testDelete() throws Exception{
37         PersonDao2 dao = new PersonDao2(getContext());
38         dao.delete("wangwu");
39     }
40     
41     public void testFindAll() throws Exception{
42         PersonDao2 dao = new PersonDao2(getContext());
43         List<Person> persons = dao.findAll();
44         for(Person p : persons){
45             System.out.println(p.toString());
46         }
47     }
48     
49     public void testTransaction() throws Exception{
50         PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());
51         SQLiteDatabase db = helper.getWritableDatabase();
52         db.beginTransaction();
53            try {
54              db.execSQL("update person set account=account+1000 where name=?", new Object[]{"wangwu"});
55              db.execSQL("update person set account=account-1000 where name=?", new Object[]{"zhangsan"});
56              db.setTransactionSuccessful();
57            }catch(Exception e){
58                e.printStackTrace();
59            } finally {
60              db.endTransaction();
61              db.close();
62            }
63 
64     }
65 }

5、domain类

 1 package com.example.mydbtest.domain;
 2 
 3 public class Person {
 4 
 5     private int id;
 6     private String name;
 7     private String number;
 8     
 9     public Person() {
10         
11     }
12     public Person(int id, String name, String number) {
13         this.id = id;
14         this.name = name;
15         this.number = number;
16     }
17     
18     
19     @Override
20     public String toString() {
21         return "Person [id=" + id + ", name=" + name + ", number=" + number
22                 + "]";
23     }
24     
25     public int getId() {
26         return id;
27     }
28     public void setId(int id) {
29         this.id = id;
30     }
31     public String getName() {
32         return name;
33     }
34     public void setName(String name) {
35         this.name = name;
36     }
37     public String getNumber() {
38         return number;
39     }
40     public void setNumber(String number) {
41         this.number = number;
42     }
43 }

 

posted @ 2016-04-14 08:09  zhongyinghe  阅读(150)  评论(0)    收藏  举报