一个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.iteye.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