一个android sqlite CRUD代码生成小工具

恒晖瑶

把之前的文章合并到这里，添加bulk批量操作函数.
android sqlite也无非是CRUD，所以通常是Ctrl+C、Ctrl+V,不过拷贝的代码容易出错浪费不少苦逼的时间。android sqlite的ORM的开源项目现在也有不少,不过对于一个嵌入式程序除了性能也需要考虑包的大小，所以ORM在嵌入式来说起码现在还不适合时机。
  这个小工具功能很简单，不添加任何外部引用，使用方式直接运行按提示做即可^_^。
  具体功能：通过输入create table语句生成表CRUD类、实体类(可选)。
  由于sqlite解析器没有解析check约束，所以create table语句也不支持check约束。



    sqlite和java类型映射关系(需要注意的是sqlite实际只支持5中数据类型，详情见链接)

sqlite	java
int、integer	int
short、byte、boolean、bool	short
long、number	long
text、varchar、char 、nvarchar 、string	String
float	float
real、double	double
blob	byte[]

 


　　现在控制带输入sqlite create table语句就可以了

生成的CRUD代码大概是这样的

import java.util.List;
import java.util.ArrayList;
import android.text.TextUtils;
import android.database.Cursor;
import android.content.ContentValues;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/*
* generated by sqliteDaoGen.jar
* http://obacow.iyunv.com/
*/
public class PersonDao{
public static final String TABLENAME = "Person";
public static final Object SYNC= new Object();
private final SQLiteOpenHelper mOpenHelper;
public PersonDao(SQLiteOpenHelper openHelper){
mOpenHelper=openHelper;
}
//
//query
//
public Cursor query(String whereClause, String []whereArgs){
final String sql = "SELECT "
+ COLUMNS.SEX + ","
+ COLUMNS.NAME + ","
+ COLUMNS.AGE + ","
+ COLUMNS.ID
+ " FROM " + (TextUtils.isEmpty(whereClause)? TABLENAME : TABLENAME+" WHERE "+whereClause);
return mOpenHelper.getReadableDatabase().rawQuery(sql, whereArgs);
}
public List<Person> queryToList(String whereClause, String []whereArgs){
Cursor cursor=null;
try{
synchronized(SYNC){
if ( (cursor = query(whereClause, whereArgs) )==null || cursor.getCount()<1)return null;
final List<Person> list = new ArrayList<Person>(cursor.getCount());
while (cursor.moveToNext()){
final Person entity=new Person();
entity.setSex( cursor.isNull(COLUMNINDEXS.SEX)? 1:cursor.getShort(COLUMNINDEXS.SEX) );
entity.setName( cursor.getString(COLUMNINDEXS.NAME) );
entity.setAge( cursor.isNull(COLUMNINDEXS.AGE)? 20:cursor.getInt(COLUMNINDEXS.AGE) );
entity.setId( cursor.getInt(COLUMNINDEXS.ID) );
list.add(entity);
}
cursor.close();
return list;
}
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (cursor!= null) cursor.close();
}
return null;
}
public Person queryFirst(String whereClause, String []whereArgs){
final List<Person> list=queryToList(whereClause,whereArgs);
return list!=null&& list.size()>0? list.get(0):null;
}
public int queryTotalRows(String whereClause, String []whereArgs){
final String sql = "SELECT COUNT(*) FROM "  +(TextUtils.isEmpty(whereClause)? TABLENAME : TABLENAME+" WHERE "+whereClause);
Cursor cursor=null;
SQLiteDatabase db=null;
try{
synchronized(SYNC){
cursor=(db=mOpenHelper.getReadableDatabase()).rawQuery(sql, whereArgs);
if (cursor!=null&&cursor.moveToFirst() ) return cursor.getInt(0);
}
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (cursor!=null) cursor.close();
if (db!=null) db.close();
}
return 0;
}
//
//inset, update, delete
//
public boolean insert(Person entity){
SQLiteDatabase db=null;
try{
return insert0(db=mOpenHelper.getWritableDatabase(), entity);
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (db!=null) db.close();
}
return false;
}
public boolean update(Person entity){
SQLiteDatabase db=null;
try{
return update0(db=mOpenHelper.getWritableDatabase(), entity, COLUMNS.ID+"=?", new String[]{String.valueOf(entity.getId())} );
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (db!=null) db.close();
}
return false;
}
public boolean delete(Person entity){
SQLiteDatabase db=null;
try{
return delete0(db=mOpenHelper.getWritableDatabase(), COLUMNS.ID+"=?", new String[]{String.valueOf(entity.getId())} );
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (db!=null) db.close();
}
return false;
}
private static final boolean insert0(SQLiteDatabase db, Person entity){
return db.insert(TABLENAME, null, tranEntity2CV(entity, true) ) >0;
}
private static final boolean update0(SQLiteDatabase db, Person entity, String whereClause, String []whereArgs){
return db.update(TABLENAME, tranEntity2CV(entity, false), whereClause, whereArgs) >0;
}
private static final boolean delete0(SQLiteDatabase db, String whereClause, String []whereArgs){
return db.delete(TABLENAME, whereClause, whereArgs) >0;
}
private static final ContentValues tranEntity2CV(Person entity, boolean forInsert){
final ContentValues cv=new ContentValues(3);
cv.put(COLUMNS.SEX, entity.getSex());
cv.put(COLUMNS.NAME, entity.getName());
cv.put(COLUMNS.AGE, entity.getAge());
return cv;
}
//
//bulkInsert, bulkUpdate, bulkDelete
//
public boolean bulkInsert(List<Person> list){
SQLiteDatabase db=null;
try{
(db=mOpenHelper.getWritableDatabase()).beginTransaction();
for (int i=0, nlen=list.size(); i<nlen; i++){
if(!insert0(db, list.get(i) ) ) return false;
}
db.setTransactionSuccessful();
return true;
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (db!=null){
db.endTransaction();
db.close();
}
}
return false;
}
public boolean bulkUpdate(List<Person> list){
SQLiteDatabase db=null;
try{
(db=mOpenHelper.getWritableDatabase()).beginTransaction();
final String []array=new String[1];
for (int i=0, nlen=list.size(); i<nlen; i++){
Person entity=list.get(i);
array[0]=String.valueOf(entity.getId());
if(!update0(db, entity, COLUMNS.ID+"=?",array) ) return false;
}
db.setTransactionSuccessful();
return true;
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (db!=null){
db.endTransaction();
db.close();
}
}
return false;
}
public boolean bulkDelete(List<Person> list){
SQLiteDatabase db=null;
try{
final StringBuffer sb = new StringBuffer().append(COLUMNS.ID).append(" IN(");
for (int i=0, nlen=list.size()-1; i<=nlen; i++){
sb.append(list.get(i).getId() ).append(i==nlen? " )" : ", ");
}
return delete0(db=mOpenHelper.getWritableDatabase(), sb.toString(), null);
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if (db!=null) db.close();
}
return false;
}
//
//stuff
//
public static final class COLUMNS{
public static final String SEX="[Sex]", NAME="[Name]", AGE="[Age]", ID="[_Id]";
}
public static final class COLUMNINDEXS{
public static final int SEX=0, NAME=1, AGE=2, ID=3;
}
static final void dropTable(SQLiteDatabase db){
db.execSQL("DROP TABLE IF EXISTS " + TABLENAME + ";");
}
static final void createTable(SQLiteDatabase db){
db.execSQL( "CREATE TABLE IF NOT EXISTS " + TABLENAME + "("
+ COLUMNS.SEX + " INTEGER DEFAULT(1),"
+ COLUMNS.NAME + " TEXT(3,15) UNIQUE NOT NULL,"
+ COLUMNS.AGE + " INTEGER DEFAULT(20),"
+ COLUMNS.ID + " INTEGER PRIMARY KEY AUTOINCREMENT);" );
}
}//end class PersonDao