Android SQLite数据库彻底掌握数据存储

SELEct * from person 
 
SELEct * from person order by id desc 
 
SELEct name from person group by name having count(*)>1 

public class DatabaseHelper extends sqliteOpenHelper { 
 
    //类没有实例化,是不能用作父类构造器的参数,必须声明为静态 
 
     private static final String name = "count"; //数据库名称 
 
     private static final int version = 1; //数据库版本 
 
     public DatabaseHelper(Context context) { 
 
       //第三个参数cursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类 
 
        super(context,name,null,version); 
 
     } 
 
    @Override 
    public void onCreate(sqliteDatabase db) { 
 
       db.execsql("create table IF NOT EXISTS person (personid INTEGER PRIMary key autoincrement,name varchar(20),age integer)");   
 
     } 
 
    @Override  
    public void onUpgrade(sqliteDatabase db,int newVersion) { 
 
        db.execsql("alter table person ADD phone VARCHAR(12)"); //往表中增加一列 
 
     } 
} 

sqliteDatabase db = ....; 
 
db.execsql("insert into person(name,agE) values('炸死特',4)"); 
 
db.close(); 

sqliteDatabase db = ....; 
 
db.execsql("insert into person(name,agE) values(?,?)",new Object[]{"炸死特",4});  
 
db.close(); 

sqliteDatabase db = ....; 
 
cursor cursor = db.rawQuery(“SELEct * from person”,null); 
 
while (cursor.moveToNext()) { 
 
 int personid = cursor.geTint(0); //获取第一列的值,第一列的索引从0开始 
 
 String name = cursor.getString(1);//获取第二列的值 
 
 int age = cursor.geTint(2);//获取第三列的值 
 
} 
 
cursor.close(); 
 
db.close(); 

sqliteDatabase db = databaseHelper.getWritableDatabase(); 
 
ContentValues values = new ContentValues(); 
 
values.put("name","炸死特"); 
 
values.put("age",4); 
 
long rowid = db.insert(“person”,values);//返回新添记录的行号，与主键id无关 

sqliteDatabase db = databaseHelper.getWritableDatabase(); 
 
db.delete("person","personid<?",new String[]{"2"}); 
 
db.close(); 

sqliteDatabase db = databaseHelper.getWritableDatabase(); 
 
ContentValues values = new ContentValues(); 
 
values.put(“name”,“炸死特”);//key为字段名，value为值 
 
db.update("person",values,"personid=?",new String[]{"1"});  
 
db.close(); 

sqliteDatabase db = databaseHelper.getWritableDatabase(); 
 
cursor cursor = db.query("person",new String[]{"personid,age"},"name like ?",new String[]{"%炸死特%"},"personid desc","1,2"); 
 
while (cursor.moveToNext()) { 
 
     int personid = cursor.geTint(0); //获取第一列的值,第一列的索引从0开始 
 
     String name = cursor.getString(1);//获取第二列的值 
 
     int age = cursor.geTint(2);//获取第三列的值 
 
} 
 
cursor.close(); 
 
db.close(); 

package jason.demo; 
 
import android.content.Context; 
import android.content.ContentValues; 
import android.database.cursor; 
import android.database.sqlException; 
import android.database.sqlite.sqliteOpenHelper; 
import android.database.sqlite.sqliteDatabase; 
import android.database.sqlite.sqliteQueryBuilder; 
 
class RestaurantHelper extends sqliteOpenHelper { 
  private static final String DATABASE_NAME="lunchlist.db";//数据库名称 
  private static final int scheR_333_11845@A_VERSION=2;//版本号,则是升级之后的,升级方法请看onUpgrade方法里面的判断 
   
  public RestaurantHelper(Context context) {//构造函数,接收上下文作为参数,直接调用的父类的构造函数 
    super(context,DATABASE_NAME,scheR_333_11845@A_VERSION); 
  } 
   
  @Override 
  public void onCreate(sqliteDatabase db) {//创建的是一个午餐订餐的列表,id,菜名,地址等等 
    db.execsql("create table restaurants (_id INTEGER PRIMARY KEY AUTOINCREMENT,@R_733_4687@T,address TEXT,type TEXT,notes TEXT,phone TEXT);"); 
  } 
 
  @Override 
  public void onUpgrade(sqliteDatabase db,int newVersion) { 
    if (oldVersion==1 && newVersion==2) {//升级判断,如果再升级就要再加两个判断,从1到3,从2到3 
      db.execsql("alter table restaurants ADD phone TEXT;"); 
    } 
  } 
 
  public cursor getAll(String where,String orderBy) {//返回表中的数据,where是调用时候传进来的搜索内容,orderby是设置中传进来的列表排序类型 
    StringBuilder buf=new StringBuilder("SELECT _id,address,type,notes,phone FROM restaurants"); 
     
    if (where!=null) { 
      buf.append(" WHERE "); 
      buf.append(wherE); 
    } 
     
    if (orderBy!=null) { 
      buf.append(" ORDER BY "); 
      buf.append(orderBy); 
    } 
     
    return(getReadableDatabase().rawQuery(buf.toString(),null)); 
  } 
   
  public cursor getById(String id) {//根据点击事件获取id,查询数据库 
    String[] args={iD}; 
 
    return(getReadableDatabase() 
            .rawQuery("SELECT _id,phone FROM restaurants WHERE _ID=?",args)); 
  } 
   
  public void insert(String name,String address,String type,String notes,String phonE) { 
    ContentValues cv=new ContentValues(); 
           
    cv.put("name",Name); 
    cv.put("address",address); 
    cv.put("type",typE); 
    cv.put("notes",notes); 
    cv.put("phone",phonE); 
     
    getWritableDatabase().insert("restaurants","name",cv); 
  } 
   
  public void update(String id,String name,String phonE) { 
    ContentValues cv=new ContentValues(); 
    String[] args={iD}; 
     
    cv.put("name",phonE); 
     
    getWritableDatabase().update("restaurants",cv,"_ID=?",args); 
  } 
   
  public String getName(cursor C) { 
    return(c.getString(1)); 
  } 
   
  public String getAddress(cursor C) { 
    return(c.getString(2)); 
  } 
   
  public String getType(cursor C) { 
    return(c.getString(3)); 
  } 
   
  public String getNotes(cursor C) { 
    return(c.getString(4)); 
  } 
   
  public String getPhone(cursor C) { 
    return(c.getString(5)); 
  } 
} 
 

public class LunchList extends ListActivity { 
 cursor model=null;//用来存储查询的数据 
 RestaurantAdapter adapter=null;//上面自定义的数据库帮助类 
 public void onCreate(Bundle savedInstanceStatE) { 
  super.onCreate(savedInstanceStatE); 
  setContentView(R.layout.main); 
 
  Helper=new RestaurantHelper(this);//参数是上下文 
 
  initList();//初始化listview列表,省略了很多,大体能明白使用数据库流程就行啦 
 
} 
 
private void initList() { 
 if (model!=null) { 
  stopManagingcursor(model); 
  model.close(); 
 } 
  
 String where=null; 
 
 if (Intent.ACTION_SEARCH.equals(geTintent().getAction())) {//如果有查询指令则根据查询内容从数据库获取表中所有满足条件数据 
  where="name LIKE \"%"+geTintent().getStringExtra(SearchManager.QUERY)+"%\""; 
 } 
 model=Helper.getAll(where,prefs.getString("sort_order","name"));//调用帮助类中的方法获取数据 
 startManagingcursor(model); 
 adapter=new RestaurantAdapter(model);//设置adapter 
 setlistadapter(adapter);//在列表中显示出来 
 }