常见的SQLite语句

来源：互联网发布：投资理财app源码编辑：程序博客网时间：2024/04/29 06:47
写SQL语句代码常见的错误,缺少空格,逗号,还有字符串必须用 ' String' 包裹起来
/** * Created by gaomin on 2016/4/28. */public class SQLiteDao {    private Context mcontext;    private SQLiteDBHelper sqLiteDBHelper;    private SQLiteDatabase sqLiteDatabase;    private static final String TABLE_NAME = "PERSON";    private static final String COLUMN_ID = "_id";    private static final String COLUMN_NAME = "name";    private static final String COLUMN_AGE = "age";    class SQLiteDBHelper extends SQLiteOpenHelper{        private static final String BD_NAME = "person.db";        private static final int VERSION = 1;        //创建数据库        public SQLiteDBHelper(Context context) {            super(context, BD_NAME, null, VERSION);        }        //创建表        @Override        public void onCreate(SQLiteDatabase db) {        /*    sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS "+             TABLE_NAME+"("+COLUMN_ID+" INTEGER PRIMARY KEY"+","                     +COLUMN_AGE+" TEXT"+","                     +COLUMN_NAME+" INTEGER"+")");*/            db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + COLUMN_ID                    + " INTEGER PRIMARY KEY,name TEXT,age INTEGER)");        }        @Override        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            Log.i("onUpgrade","onUpgrade");            db.execSQL("ALTER TABLE "+TABLE_NAME+" RENAME TO PERSON_TEMP");            db.execSQL("CREATE TABLE "+TABLE_NAME                    +"(_id integer primary key,name varchar(20),age integer,sex varchar(2)");            db.execSQL("insert into "+TABLE_NAME+"(_id,name,sex,age )" +                    "SELECT _id,name,age,'女' FROM PERSON_TEMP");        }    }    //构造方法中初始化context SQLiteDatabase SQLiteDBHelper    public SQLiteDao(Context c){        mcontext = c;        sqLiteDBHelper = new SQLiteDBHelper(mcontext);        sqLiteDatabase = sqLiteDBHelper.getWritableDatabase();    }   //两种插入数据的操作,前者为原生操作,效率自然高点    public void insertPerson(Person person){        String INSERT_SQL = "INSERT INTO "+TABLE_NAME                +"("+COLUMN_NAME+","+COLUMN_AGE+")"+"VALUES("                +"'"+person.getName()+"'"+","+person.getAge()+")";        sqLiteDatabase.execSQL(INSERT_SQL);     /*   ContentValues contentValues = new ContentValues();        contentValues.put(COLUMN_NAME,person.getName());        contentValues.put(COLUMN_AGE,person.getAge());        sqLiteDatabase.insert(TABLE_NAME,null,contentValues);*/    }    //修改数据    public void updatePerson(Person person) {        String UPDATE_SQL = "UPDATE "+TABLE_NAME+                " SET "+COLUMN_NAME+" = "+"'"+person.getName()+"'"                +" WHERE "+COLUMN_ID+" = "+person.getId();        sqLiteDatabase.execSQL(UPDATE_SQL);       /* ContentValues values = new ContentValues();        values.put(COLUMN_NAME,person.getName());        sqLiteDatabase.update(TABLE_NAME,values,COLUMN_ID                +" = ?",new String[]{String.valueOf(person.getId())});*/    }    public void deletePerson(int id) {        String DELETE_SQL = "DELETE FROM "+TABLE_NAME                +" WHERE "+COLUMN_ID+" = "+id;        sqLiteDatabase.execSQL(DELETE_SQL);/*        sqLiteDatabase.delete(TABLE_NAME,COLUMN_ID                +" = ?",new String[]{String.valueOf(id)});*/    }    //查询所有数据    public ArrayList<Person> listPerson() {        //new 一个集合对象用来保存查询的数据对象        ArrayList<Person> personArrayList = new ArrayList<Person>();        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM "+TABLE_NAME                       +" WHERE "+COLUMN_ID+" IS NOT ?",new String[]{"NULL"});        //遍历        while (cursor.moveToNext()){            Person person = new Person();            person.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));            person.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));            person.setAge(cursor.getInt(cursor.getColumnIndex(COLUMN_AGE)));            personArrayList.add(person);        }        cursor.close();        return personArrayList;    }    //根据id查询单个Person    public ArrayList<Person> findPerson(int sid) {        ArrayList<Person> onePerson = new ArrayList<Person>();        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE "                +COLUMN_ID+" = ?",new String[]{String.valueOf(sid)});        while (cursor.moveToNext()){            Person person = new Person();            person.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));            person.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));            person.setAge(cursor.getInt(cursor.getColumnIndex(COLUMN_AGE)));            onePerson.add(person);        }        cursor.close();        return onePerson;    }    //查询指定数据LIMIT m,n从低m-1条开始查询,一共查询n条数据    public ArrayList<Person> queryPageData(Integer offset, Integer maxResult){        ArrayList<Person> arrayList = new ArrayList<Person>();        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM "+               TABLE_NAME+" LIMIT ?,?",                new String[]{String.valueOf(offset),String.valueOf(maxResult)});        while (cursor.moveToNext()){            Person person = new Person();            person.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));            person.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));            person.setAge(cursor.getInt(cursor.getColumnIndex(COLUMN_AGE)));            arrayList.add(person);        }        cursor.close();        return arrayList;    }    //统计数据    public long getCount() {        Cursor cursor = sqLiteDatabase.rawQuery("SELECT COUNT(*) FROM "+TABLE_NAME+" WHERE "                               +COLUMN_ID+" IS NOT ?",new String[]{"NULL"});        cursor.moveToNext();        long count = cursor.getLong(0);        cursor.close();        return count;    }}
1 0