SQLite常见操作（增删改查）-白红宇

SQLite常见操作（增删改查）

阅读量：5080 次

发布时间：2019-06-12

本文共 13146 字，大约阅读时间需要 43 分钟。

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 context12      * @param name13      * @param factory14      * @param version15      */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     @Override27     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     @Override33     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     @Override50     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
     
       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
      
        persons = new ArrayList
       
        (); 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
        
          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
         
           persons = new ArrayList
          
           (); 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
     
       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
      
        persons = new ArrayList
       
        (); 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
        
          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
         
           persons = new ArrayList
          
           (); 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
     
       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
      
        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 }

转载于:https://www.cnblogs.com/zhangyuzunhh/archive/2013/03/26/2981751.html

你可能感兴趣的文章

JavaScript的迭代函数与迭代函数的实现