你的位置:首页 > 操作系统

[操作系统]sqlite的基本使用


一:基本操作
1继承SQLiteOpenHelper 
public class UserSqliteOpenHelper extends SQLiteOpenHelper {   private static final int versionNo = 1;   public UserSqliteOpenHelper(Context context) {      super(context, "user.db", null, versionNo);   }   @Override   public void onCreate(SQLiteDatabase db) {      Log. i("" , "sqlite onCreate" );      db.execSQL( "create table user (id Integer primary key autoincrement, name varchar(20))");   }   @Override   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {      Log. i("" , "sqlite onUpgrade" );      db.execSQL( "insert into user(name) values(?)", new Object[]{"version"+versionNo });   } }

 


 
从代码中我们可以看出,UserSqliteOpenHelper重写了SQLiteOpenHelper的onCreate和onUpgrade两个方法
说明:
onCreate:数据库被建立的时候调用,一般执行创建数据库语句
onUpgrade:当版本号(versionNo)增加时执行此方法,一般用于对表结构的更改、添加或删除
*第一次创建数据库时执行onCreate,不执行onUpgrade;更改版本号之后,执行onUpgrade,不执行onCreate
操作说明:
当 private static final int versionNo = 1 时结果如下

 
 
当 private static final int versionNo = 2 时结果如下

 

 
2.创建dao文件对sqlite数据库进行操作
public class UserDao {   private UserSqliteOpenHelper tsoh;     public UserDao(Context context) {      tsoh = new UserSqliteOpenHelper(context);   }     public void insert(String name){      SQLiteDatabase db = tsoh.getWritableDatabase();      db. execSQL("insert into user(name) values(?)", new Object[]{name});      db.close();   }     public boolean find(String name){      SQLiteDatabase db = tsoh.getReadableDatabase();      Cursor cursor = db.rawQuery( "select * from user where name = ?", new String[]{name});      boolean result = cursor.moveToNext();      cursor.close();      db.close();      return result;   }     public void update(String name, String newName){      SQLiteDatabase db = tsoh.getWritableDatabase();      db. execSQL("update user set name = ? where name = ?", new Object[]{newName, name});      db.close();   }     public void delete(String name){      SQLiteDatabase db = tsoh.getWritableDatabase();      db. execSQL("delete from user where name = ?", new Object[]{name});      db.close();   }     public List<User> findAll(){      List<User> users = new ArrayList<User>();      SQLiteDatabase db = tsoh.getReadableDatabase();      Cursor cursor = db.rawQuery( "select * from user", null);      while(cursor.moveToNext()){         int id = cursor.getInt(cursor.getColumnIndex("id" ));        String name = cursor.getString(cursor.getColumnIndex("name" ));        User user = new User(id, name);        users.add(user);      }      cursor.close();      db.close();      return users;   } }

 



 
user类的定义
public class User {   private int id;   private String name;     public User() {      super();   }     public User(int id, String name) {      super();      this. id = id;      this. name = name;   }     public int getId() {      return id;   }   public void setId(int id) {      this. id = id;   }   public String getName() {      return name;   }   public void setName(String name) {      this. name = name;   }   @Override   public String toString() {      return "info:"+ id+ ","+ name;   }}

 


代码很简单,不做过多解释。需要注意的是,每次在操作数据库的时候记得将cursor和database关闭,释放资源。项目级的代码应该使用try catch finally来操作,这里为了演示简单操作。这里还是沿用了拼接字符串的方式执行sql语句,其实android还为我们提供封装好了的API(最终还是将参数拼接成字符串,不过对于调用者来说新的API简单明了,用户不用考虑单引号双引号的麻烦了),这里不做过多介绍。
 
3.调用代码
    
@Override  protected void onCreate(Bundle savedInstanceState) {    super.onCreate(savedInstanceState);    setContentView(R.layout. activity_main);        UserDao testDao = new UserDao( this);    testDao.insert( "name1");    testDao.insert( "name2");    testDao.insert( "name3");        testDao.update( "name1", "name4");        boolean result = testDao.find("name2" );        testDao.delete( "name3");        List<User> users = testDao.findAll();    for(User user:users){      Log. i("MainActivity" , user.toString());    }  }

 




基本操作介绍完毕。
 
二:推荐sqlite界面工具:sqlite expert professional
前两天有个同事问我,sqlite中Integer能存储最大的数值是多少,当时我也不知道,于是就当场测试了一下,测试的结果:-9223372036854775808 ~ 9223372036854775807 也就是-2^63到2^63-1
这就是我通过 sqlite工具实验的结果


 
 

INTEGER: 用来存储一个整数,根据大小可以使用1,2,3,4,6,8个字节来存储(网络抄录,从我实验的结果上来看上限是8个字节的整数)