android操作sqlite数据库及心得

写这篇文章主要是网上的对sqlite的操作太多且太杂，很多时候都不能很好的运用到自己的项目中，结构不清晰，我自己写了一篇适合刚刚接触的人看的操作方法。

近来用android时要将一些数据保存起来，一开始用的是preferences，后来要保存的东西多了，发现用preferences明显不能满足要求了，而且发现用这个的话代码就变得有点乱了，所以才开始学习使用sqlite数据库，一开始以为不就是个数据库么，和平时的mysql啊或者是sqlserver都一样，都很简单的，但后来真正在用的时候才发现困难一个接着一个，但还是在不断的摸索中一步一步的不断解决困难，后来发现学习安卓的话当自己实在找不到思路时看看网上的一些教学视频也是个不错的选择。这里推荐一个视频不错，我就是照这个视频学的。http://www.tudou.com/programs/view/2qH5Am_3MsM/

写一下android操作数据库中的一些准备。

首先，配一下adb的环境变量，因为每次都要到adb的目录下去启动实在太麻烦了，下面是具体步骤，当然也可以该其他文件，我习惯改这个，可以改完后可以source一下使它生效。

1、sudo gedit /etc/profile
2、将下面的两句加到上面打开的文件里 
export ANDROID_HOME=/home/sdk文件路径
export PATH=$PATH:$ANDROID_HOME/platform-tools

3、重启电脑，大功告成！！

adb配好以后，我们最好还要给手机里的数据库访问的权限，一般在/data/data/包名/database 里面，用adb shell进入后su获得手机root权限，然后给权限chmod。

要读数据库文件的话就用命令 sqlite3 数据库文件 ，其中的数据库可以直接在adb shell中运行sqlite3，但我按照网上弄的就是不能在adb shell中打开sqlite3数据库，说命令没有找到，我该传的文件都传了，没办法，只有在eclipse里的ddms的file explore里把数据库文件到处然后在linux终端里运行sqlite3数据库来看了。

还有要注意的是写sql语句时一定要注意"select * from " +TABLE_NAME 中的from和引号要留有空格，不然的话就连在一起了。 

下面的有一个知识要讲一下，sqlite的增加，删除等操作都挺简单的，麻烦的就是查询操作，一般都借用Cursor来保存查询数据，一开始我没怎么注意这是一个指针类型，指向数据库里的数据，而我一开始写的时候把数据库的关闭操作写在了Cursor操作的前面，也就是说先把数据库关闭了再对Cursor对象进行操作，这样的话就造成了Cursor的空指针，也就注定杯具了好久。。。

下面就贴一些关于sqlite数据库操作的事例，这样对一些还在困惑中的人有一些帮助，同时有助于以后自己回顾。

SQLiteHelper.java（数据库辅助类）

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

publicclass SQLiteHelper extendsSQLiteOpenHelper{

     

    privatestatic final String DATABASE_NAME="fpp.db";

    privatestatic final int  DATABASE_VERSION=17;//更改版本后数据库将重新创建

    privatestatic final String TABLE_NAME="test";

     

    /**

     * 在SQLiteOpenHelper的子类当中，必须有这个构造函数

     * @param context     当前的Activity

     * @param name        表的名字（而不是数据库的名字，这个类是用来操作数据库的）

     * @param factory      用来在查询数据库的时候返回Cursor的子类，传空值

     * @param version      当前的数据库的版本，整数且为递增的数

     */

    publicSQLitedata(Context context)

    {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);//继承父类

        // TODO Auto-generated constructor stub

    }

/**

     * 该函数是在第一次创建数据库时执行，只有当其调用getreadabledatebase()

     * 或者getwrittleabledatebase()而且是第一创建数据库是才会执行该函数

     */

 

    publicvoid onCreate(SQLiteDatabase db)

    {

      

        // TODO Auto-generated method stub

        String sql = "CREATE TABLE " + TABLE_NAME + "("

                +"id INTEGER,"

                +"nid VARCHAR(11),"

                +"sid CHAR(1),"

                +"type INTEGER,"

                +"stime DATETIME,"

                +"locate_main VARCHAR(45),"

                +"locate_detail VARCHAR(45),"

                +"state INTEGER"

                +")";

        db.execSQL(sql);

        Log.e("create","数据库创建成功");

    }

/**

*数据库更新函数，当数据库更新时会执行此函数

*/

 

    publicvoid onUpgrade(SQLiteDatabase db, intoldVersion, intnewVersion)

    {

        String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;

        db.execSQL(sql);

        this.onCreate(db);

        // TODO Auto-generated method stub

        System.out.println("数据库已经更新");

        /**

         * 在此添加更新数据库是要执行的操作

         */

    }

   

}

MyOperator.java （数据库操作类）

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

publicclass MyOperator {

     

    privatestatic final String TABLE_NAME = "test";//要操作的数据表的名称

    privateSQLiteDatabase db=null;//数据库操作

     

    //构造函数

    publicMyOperator(SQLiteDatabase db)

    {

        this.db=db;

    }

     

//  //插入操作

//  public void insert(int id,String nid,String sid,int type,

//          String stime,String etime,String desc,String locate_main,String locate_detail,int state)

//  {

//      String sql = "INSERT INTO " + TABLE_NAME + " (id,nid,sid,type,stime,etime,desc,locate_main,locate_detail,state)"

//              + " VALUES(?,?,?,?,?,?,?,?,?,?)";

//      Object args[]=new Object[]{id,nid,sid,type,stime,etime,desc,locate_main,locate_detail,state};

//      this.db.execSQL(sql, args);

//      this.db.close();

//  }

    //插入重载操作

    publicvoid insert(intid,intstate)

    {

        String sql = "INSERT INTO " + TABLE_NAME + " (id,state)" +" VALUES(?,?)";

        Object args[]=newObject[]{id,state};

        this.db.execSQL(sql, args);

        this.db.close();

    }

     

     

    //更新操作

    publicvoid update(intid,intstate)

    {

        String sql = "UPDATE " + TABLE_NAME + " SET state=? WHERE id=?";

        Object args[]=newObject[]{state,id};

        this.db.execSQL(sql, args);

        this.db.close();

    }

     

    //删除操作,删除

    publicvoid delete(intid)

    {

        String sql = "DELETE FROM " + TABLE_NAME +" WHERE id=?";

        Object args[]=newObject[]{id};

        this.db.execSQL(sql, args);

        this.db.close();

    }

     

    //查询操作,查询表中所有的记录返回列表

    publicList<string> find()

    {

        List<string> all = newArrayList<string>(); //此时只是String

        String sql = "SELECT * FROM " + TABLE_NAME;

        Cursor result = this.db.rawQuery(sql,null);   //执行查询语句

        for(result.moveToFirst();!result.isAfterLast();result.moveToNext()  )   //采用循环的方式查询数据

        {

            all.add(result.getInt(0)+","+result.getString(1)+","+result.getString(2)+","+result.getInt(3)+","

                    +result.getString(4)+","+result.getString(5)+","+result.getString(6)+","+result.getString(7)+","

                    +result.getString(8));

        }

        this.db.close();

        returnall;

    }

     

    //查询操作虫重载函数，返回指定ID的列表

    publicint getstatebyID(intid)

    {

        intnum=-1;//错误状态-1

        List<string> all = newArrayList<string>(); //此时只是String

        String sql = "SELECT state FROM " + TABLE_NAME + " where id=?" ;

        String args[] = newString[]{String.valueOf(id)};

        Cursor result = this.db.rawQuery(sql, args);

        for(result.moveToFirst();!result.isAfterLast();result.moveToNext()  )

        {

            num=result.getInt(0);

        }

         

        Log.e("database","图片状态state"+ String.valueOf(num));

        this.db.close();

        returnnum;

    }

     

    //判断插入数据的ID是否已经存在数据库中。

    publicboolean check_same(intid)

    {

        String sql="SELECT id from " + TABLE_NAME + " where id = ?";

        String args[] =newString[]{String.valueOf(id)};

        Cursor result=this.db.rawQuery(sql,args);

        Log.e("database","the sql has been excuate");

         

        Log.e("database","the hang count" + String.valueOf(result.getCount()));

         

        if(result.getCount()==0)//判断得到的返回数据是否为空

        {

            Log.e("database","return false and not exist the same result" + String.valueOf(result.getCount()));

            this.db.close();

            returnfalse;

        }

        else

        {

            Log.e("database","return true and exist the same result"+ String.valueOf(result.getCount()));

            this.db.close();

            returntrue;

        }

    }

}</string></string></string></string></string>

随便的一个项目中的activity（activity 类，操作打开数据库已经在里面）

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

publicclass MainActivity extendsActivity {

    privatestatic LinkedList<map<string, object="">> mListItems;

    privatePullToRefreshListView pullToRefreshListView;

    privateProblemController problemController = ProblemController.getInstance();

     

    privateSQLiteOpenHelper helper =null;

    privateMyOperator mytab=null;

     

    /** Called when the activity is first created. */

    @Override

    publicvoid onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

        this.helper=newSQLitedata(this);//数据库操作辅助类

        setPullToRefreshView();

    }

 

     

    @Override

    publicboolean onCreateOptionsMenu(Menu _menu){

        super.onCreateOptionsMenu(_menu);

        _menu.add(0,0,0,"设置");

        returntrue;

    }

     

    @Override

    publicboolean onOptionsItemSelected(MenuItem _item){

        switch(_item.getItemId()) {

            case0:{

                Intent intent = newIntent(getApplicationContext(), SettingsActivity.class);

                startActivity(intent);

                break;

            }

        }

        returntrue;

    }

     

    privateclass GetDataTask extendsAsyncTask<void,void,=""string[]=""> {

 

        @Override

        protectedString[] doInBackground(Void... params) {

            if(listToShowData(problemController.getNewProblems())){

                 

            }else{

                Message message = newMessage();

                handler.sendMessage(message);

            }

            returnmStrings;

        }

 

        @Override

        protectedvoid onPostExecute(String[] result) {

            pullToRefreshListView.onRefreshComplete();

            super.onPostExecute(result);

        }

    }

 

    privateString[] mStrings = {};

 

    /**

     * @param _newslist 需要显示的消息列表

     */

    privateboolean listToShowData(LinkedList<problem> _problems) {

        if(_problems != null){

            mListItems.clear();

            for(Problem news : _problems) {

                //将数据插入数据库进行初始化

                //这里需要一个判断重复的操作，如果数据的id已经在数据库中就不需要进行插入数据

                 

                 

                mytab = newMyOperator(helper.getWritableDatabase());

                Log.e("database","start check if id exists and insert the id,the id is "+String.valueOf(news.getID()));

                if(!mytab.check_same(news.getID()))

                {

                    Log.e("database","the id is not exist,insert the new id now.....");

                    mytab = newMyOperator(helper.getWritableDatabase());

                    mytab.insert(news.getID(),1);

                    Log.e("database","insert finished");

                }

                 

                 

                Map<string, object=""> tmpMap = newHashMap<string, object="">();  //用来储存日志名称的Map

                tmpMap.put("id", news.getID());

                tmpMap.put("describe","模块:"+ news.getSid() + "出现故障!");

                tmpMap.put("time", news.getsTime());

                tmpMap.put("img", R.drawable.icon_important);

                 

                Log.e("database","start read database");

                 

                //读取数据库判断这个事件的状态显示对应的图标，1表示默认状态问号，2表示已察看，3表示事件已经完成勾

                mytab = newMyOperator(helper.getWritableDatabase());

                intstate = mytab.getstatebyID(news.getID());

                switch(state)

                {

                case1:tmpMap.put("state_img", R.drawable.icon_question);break;

                case2:tmpMap.put("state_img", R.drawable.icon_process);break;

                case3:tmpMap.put("state_img", R.drawable.icon_correct);break;

                default:tmpMap.put("state_img", R.drawable.icon_correct);

                }

                mListItems.add(tmpMap);

                Log.e(news.toString(), news.toString());

            }

            returntrue;

        }else{

            returnfalse;

        }

    }

     

    /**

     * @param 对下拉刷新控件进行设置

     */

    privatevoid setPullToRefreshView(){

        mListItems = newLinkedList<map<string,object>>();

        pullToRefreshListView = (PullToRefreshListView)findViewById(R.id.pullToRefreshListView1);

        pullToRefreshListView.setOnRefreshListener(newOnRefreshListener() {

            publicvoid onRefresh() {

                newGetDataTask().execute();    //拉数据的线程开启

            }

        });

        pullToRefreshListView.setOnItemClickListener(newOnItemClickListener() {

            publicvoid onItemClick(AdapterView<!--?--> arg0, View arg1, finalint arg2,

                    longarg3) {

                Log.e("Pull", String.valueOf(arg2));

                ShareData.showProblem = problemController.getOldProblems().get(arg2 - 1);

                Intent intent = newIntent(getApplicationContext(), DetailsActivity.class);

                 

                 

                //设置新的图片,现在用数据库的方法，所以这个操作就不需要了，到时候统一读取图片

//              ImageView tempImage=(ImageView)arg1.findViewById(R.id.imageView2);

//              tempImage.setImageResource(R.drawable.icon_correct);

                //把状态存入数据库,判断图片状态，如果为1则说明没有被访问过，改变为2

                mytab = newMyOperator(helper.getWritableDatabase());

                if(mytab.getstatebyID(ShareData.showProblem.getID())==1)

                {

                    mytab = newMyOperator(helper.getWritableDatabase());

                    mytab.update(ShareData.showProblem.getID(),2);

                }

                 

//将故障ID传入到选项界面，以便与判断哪个页面选了哪些选项。            

                intid=ShareData.showProblem.getID();

                Bundle bd=newBundle();

                bd.putInt("id", id);

                intent.putExtra("ID", bd);

                 

                 

                startActivity(intent);

//传入到了另一个界面

            }

        });

        pullToRefreshListView.setOnItemLongClickListener(newOnItemLongClickListener() {

            publicboolean onItemLongClick(AdapterView<!--?--> arg0, View arg1,

                    intarg2, longarg3) {

                Log.e("PullLong", String.valueOf(arg2));

                returntrue;

            }

        });

        SimpleAdapter adapter = newSimpleAdapter(getApplicationContext(), mListItems, R.layout.layout_listitem,

                newString[]{"id","img","describe","time","state_img"},

                newint[]{R.id.title_TV, R.id.imageView1, R.id.content_TV, R.id.date_TV, R.id.imageView2});

        pullToRefreshListView.setAdapter(adapter);

         

    }

     

    privateHandler handler = newHandler(){

        @Override

        publicvoid handleMessage(Message message) {

            Toast.makeText(getApplicationContext(),"网络状况出现问题!", Toast.LENGTH_LONG).show();

        }

    };

}</map<string,object></string,></string,></problem></void,></map<string,>