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个字节的整数)