Android 开发笔记_SQLite基本用法

SQLiteOpenHelper

  1 package com.example.alimjan.hello_world.dataBase;
  2 
  3 import android.content.ContentValues;
  4 import android.content.Context;
  5 import android.database.Cursor;
  6 import android.database.sqlite.SQLiteDatabase;
  7 import android.database.sqlite.SQLiteOpenHelper;
  8 import android.util.Log;
  9 
 10 import com.example.alimjan.hello_world.bean.UserInfo;
 11 
 12 import java.util.ArrayList;
 13 
 14 public class UserDBHelper extends SQLiteOpenHelper {
 15     private static final String TAG = "UserDBHelper";
 16     private static final String DB_NAME = "user.db";
 17     private static final int DB_VERSION = 1;
 18     private static UserDBHelper mHelper = null;
 19     private SQLiteDatabase mDB = null;
 20     private static final String TABLE_NAME = "user_info";
 21 
 22     private UserDBHelper(Context context) {
 23         super(context, DB_NAME, null, DB_VERSION);
 24     }
 25 
 26     private UserDBHelper(Context context, int version) {
 27         super(context, DB_NAME, null, version);
 28     }
 29 
 30     public static UserDBHelper getInstance(Context context, int version) {
 31         if (version > 0 && mHelper == null) {
 32             mHelper = new UserDBHelper(context, version);
 33         } else if (mHelper == null) {
 34             mHelper = new UserDBHelper(context);
 35         }
 36         return mHelper;
 37     }
 38 
 39     public SQLiteDatabase openReadLink() {
 40         if (mDB == null || mDB.isOpen() != true) {
 41             mDB = mHelper.getReadableDatabase();
 42         }
 43         return mDB;
 44     }
 45 
 46     public SQLiteDatabase openWriteLink() {
 47         if (mDB == null || mDB.isOpen() != true) {
 48             mDB = mHelper.getWritableDatabase();
 49         }
 50         return mDB;
 51     }
 52 
 53     public void closeLink() {
 54         if (mDB != null && mDB.isOpen() == true) {
 55             mDB.close();
 56             mDB = null;
 57         }
 58     }
 59     
 60     public String getDBName() {
 61         if (mHelper != null) {
 62             return mHelper.getDatabaseName();
 63         } else {
 64             return DB_NAME;
 65         }
 66     }
 67     
 68     @Override
 69     public void onCreate(SQLiteDatabase db) {
 70         Log.d(TAG, "onCreate");
 71         String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
 72         Log.d(TAG, "drop_sql:" + drop_sql);
 73         db.execSQL(drop_sql);
 74         String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
 75                 + "_id INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL,"
 76                 + "name VARCHAR NOT NULL," + "age INTEGER NOT NULL,"
 77                 + "height LONG NOT NULL," + "weight FLOAT NOT NULL,"
 78                 + "married INTEGER NOT NULL," + "update_time VARCHAR NOT NULL"
 79                 //演示数据库升级时要先把下面这行注释
 80                 + ",phone VARCHAR" + ",password VARCHAR"
 81                 + ");";
 82         Log.d(TAG, "create_sql:" + create_sql);
 83         db.execSQL(create_sql);
 84     }
 85 
 86     @Override
 87     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 88         Log.d(TAG, "onUpgrade oldVersion="+oldVersion+", newVersion="+newVersion);
 89         if (newVersion > 1) {
 90             //Android的ALTER命令不支持一次添加多列，只能分多次添加
 91             String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "phone VARCHAR;";
 92             Log.d(TAG, "alter_sql:" + alter_sql);
 93             db.execSQL(alter_sql);
 94             alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "password VARCHAR;";
 95             Log.d(TAG, "alter_sql:" + alter_sql);
 96             db.execSQL(alter_sql);
 97         }
 98     }
 99 
100     public int delete(String condition) {
101         int count = mDB.delete(TABLE_NAME, condition, null);
102         return count;
103     }
104 
105     public int deleteAll() {
106         int count = mDB.delete(TABLE_NAME, "1=1", null);
107         return count;
108     }
109 
110     public long insert(UserInfo info) {
111         ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
112         infoArray.add(info);
113         return insert(infoArray);
114     }
115     
116     public long insert(ArrayList<UserInfo> infoArray) {
117         long result = -1;
118         for (int i = 0; i < infoArray.size(); i++) {
119             UserInfo info = infoArray.get(i);
120             ArrayList<UserInfo> tempArray = new ArrayList<UserInfo>();
121             // 如果存在同名记录，则更新记录
122             // 注意条件语句的等号后面要用单引号括起来
123             if (info.name!=null && info.name.length()>0) {
124                 String condition = String.format("name='%s'", info.name);
125                 tempArray = query(condition);
126                 if (tempArray.size() > 0) {
127                     update(info, condition);
128                     result = tempArray.get(0).rowid;
129                     continue;
130                 }
131             }
132             // 如果存在同样的手机号码，则更新记录
133             if (info.phone!=null && info.phone.length()>0) {
134                 String condition = String.format("phone='%s'", info.phone);
135                 tempArray = query(condition);
136                 if (tempArray.size() > 0) {
137                     update(info, condition);
138                     result = tempArray.get(0).rowid;
139                     continue;
140                 }
141             }
142             // 不存在唯一性重复的记录，则插入新记录
143             ContentValues cv = new ContentValues();
144             cv.put("name", info.name);
145             cv.put("age", info.age);
146             cv.put("height", info.height);
147             cv.put("weight", info.weight);
148             cv.put("married", info.married);
149             cv.put("update_time", info.update_time);
150             cv.put("phone", info.phone);
151             cv.put("password", info.password);
152             result = mDB.insert(TABLE_NAME, "", cv);
153             // 添加成功后返回行号，失败后返回-1
154             if (result == -1) {
155                 return result;
156             }
157         }
158         return result;
159     }
160 
161     public int update(UserInfo info, String condition) {
162         ContentValues cv = new ContentValues();
163         cv.put("name", info.name);
164         cv.put("age", info.age);
165         cv.put("height", info.height);
166         cv.put("weight", info.weight);
167         cv.put("married", info.married);
168         cv.put("update_time", info.update_time);
169         cv.put("phone", info.phone);
170         cv.put("password", info.password);
171         int count = mDB.update(TABLE_NAME, cv, condition, null);
172         return count;
173     }
174 
175     public int update(UserInfo info) {
176         return update(info, "rowid="+info.rowid);
177     }
178 
179     public ArrayList<UserInfo> query(String condition) {
180         String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time," +
181                 "phone,password from %s where %s;", TABLE_NAME, condition);
182         Log.d(TAG, "query sql: "+sql);
183         ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
184         Cursor cursor = mDB.rawQuery(sql, null);
185         if (cursor.moveToFirst()) {
186             for (;; cursor.moveToNext()) {
187                 UserInfo info = new UserInfo();
188                 info.rowid = cursor.getLong(0);
189                 info.xuhao = cursor.getInt(1);
190                 info.name = cursor.getString(2);
191                 info.age = cursor.getInt(3);
192                 info.height = cursor.getLong(4);
193                 info.weight = cursor.getFloat(5);
194                 //SQLite没有布尔型，用0表示false，用1表示true
195                 info.married = (cursor.getInt(6)==0)?false:true;
196                 info.update_time = cursor.getString(7);
197                 info.phone = cursor.getString(8);
198                 info.password = cursor.getString(9);
199                 infoArray.add(info);
200                 if (cursor.isLast() == true) {
201                     break;
202                 }
203             }
204         }
205         cursor.close();
206         return infoArray;
207     }
208     
209     public UserInfo queryByPhone(String phone) {
210         UserInfo info = null;
211         ArrayList<UserInfo> infoArray = query(String.format("phone='%s'", phone));
212         if (infoArray.size() > 0) {
213             info = infoArray.get(0);
214         }
215         return info;
216     }
217     
218 }

 1 package com.example.alimjan.hello_world.bean;
 2 
 3 public class UserInfo {
 4     public long rowid;
 5     public int xuhao;
 6     public String name;
 7     public int age;
 8     public long height;
 9     public float weight;
10     public boolean married;
11     public String update_time;
12     public String phone;
13     public String password;
14     
15     public UserInfo() {
16         rowid = 0l;
17         xuhao = 0;
18         name = "";
19         age = 0;
20         height = 0l;
21         weight = 0.0f;
22         married = false;
23         update_time = "";
24         phone = "";
25         password = "";
26     }
27 }

write

  1 package com.example.alimjan.hello_world;
  2 
  3 /**
  4  * Created by alimjan on 7/4/2017.
  5  */
  6 
  7         import android.content.Context;
  8         import android.content.Intent;
  9         import android.os.Bundle;
 10         import android.support.v7.app.AppCompatActivity;
 11         import android.view.View;
 12         import android.view.View.OnClickListener;
 13         import android.widget.AdapterView;
 14         import android.widget.ArrayAdapter;
 15         import android.widget.EditText;
 16         import android.widget.Spinner;
 17         import android.widget.Toast;
 18         import android.widget.AdapterView.OnItemSelectedListener;
 19 
 20         import com.example.alimjan.hello_world.bean.UserInfo;
 21         import com.example.alimjan.hello_world.dataBase.UserDBHelper;
 22         import com.example.alimjan.hello_world.Utils.DateUtil;
 23 
 24 public class class_4_2_2_1_write extends AppCompatActivity implements OnClickListener {
 25 
 26     private UserDBHelper mHelper;
 27     private EditText et_name;
 28     private EditText et_age;
 29     private EditText et_height;
 30     private EditText et_weight;
 31     private boolean bMarried = false;
 32 
 33     @Override
 34     protected void onCreate(Bundle savedInstanceState) {
 35         super.onCreate(savedInstanceState);
 36         setContentView(R.layout.code_4_2_2_1);
 37         et_name = (EditText) findViewById(R.id.et_name);
 38         et_age = (EditText) findViewById(R.id.et_age);
 39         et_height = (EditText) findViewById(R.id.et_height);
 40         et_weight = (EditText) findViewById(R.id.et_weight);
 41         findViewById(R.id.btn_save).setOnClickListener(this);
 42 
 43         ArrayAdapter<String> typeAdapter = new ArrayAdapter<String>(this,
 44                 R.layout.item_select, typeArray);
 45         typeAdapter.setDropDownViewResource(R.layout.item_dropdown);
 46         Spinner sp_married = (Spinner) findViewById(R.id.sp_married);
 47         sp_married.setPrompt("请选择婚姻状况");
 48         sp_married.setAdapter(typeAdapter);
 49         sp_married.setSelection(0);
 50         sp_married.setOnItemSelectedListener(new TypeSelectedListener());
 51 
 52     }
 53 
 54     private String[] typeArray = {"未婚", "已婚"};
 55     class TypeSelectedListener implements OnItemSelectedListener {
 56         public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
 57             bMarried = (arg2==0)?false:true;
 58         }
 59 
 60         public void onNothingSelected(AdapterView<?> arg0) {
 61         }
 62     }
 63 
 64     @Override
 65     protected void onStart() {
 66         super.onStart();
 67         mHelper = UserDBHelper.getInstance(this, 2);
 68         mHelper.openWriteLink();
 69     }
 70 
 71     @Override
 72     protected void onStop() {
 73         super.onStop();
 74         mHelper.closeLink();
 75     }
 76 
 77     @Override
 78     public void onClick(View v) {
 79         if (v.getId() == R.id.btn_save) {
 80             String name = et_name.getText().toString();
 81             String age = et_age.getText().toString();
 82             String height = et_height.getText().toString();
 83             String weight = et_weight.getText().toString();
 84             if (name==null || name.length()<=0) {
 85                 showToast("请先填写姓名");
 86                 return;
 87             }
 88             if (age==null || age.length()<=0) {
 89                 showToast("请先填写年龄");
 90                 return;
 91             }
 92             if (height==null || height.length()<=0) {
 93                 showToast("请先填写身高");
 94                 return;
 95             }
 96             if (weight==null || weight.length()<=0) {
 97                 showToast("请先填写体重");
 98                 return;
 99             }
100 
101             UserInfo info = new UserInfo();
102             info.name = name;
103             info.age = Integer.parseInt(age);
104             info.height = Long.parseLong(height);
105             info.weight = Float.parseFloat(weight);
106             info.married = bMarried;
107             info.update_time = DateUtil.getCurDateStr("yyyy-MM-dd HH:mm:ss");
108             mHelper.insert(info);
109             showToast("数据已写入SQLite数据库");
110         }
111     }
112 
113     private void showToast(String desc) {
114         Toast.makeText(this, desc, Toast.LENGTH_SHORT).show();
115     }
116 
117     public static void startHome(Context mContext) {
118         Intent intent = new Intent(mContext, class_4_2_2_1_write.class);
119         mContext.startActivity(intent);
120     }
121 
122 }

  1 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  2     android:layout_width="match_parent"
  3     android:layout_height="match_parent"
  4     android:focusable="true"
  5     android:focusableInTouchMode="true"
  6     android:orientation="vertical"
  7     android:padding="10dp" >
  8 
  9     <RelativeLayout
 10         android:layout_width="match_parent"
 11         android:layout_height="50dp" >
 12 
 13         <TextView
 14             android:id="@+id/tv_name"
 15             android:layout_width="wrap_content"
 16             android:layout_height="match_parent"
 17             android:layout_alignParentLeft="true"
 18             android:gravity="center"
 19             android:text="姓名："
 20             android:textColor="@color/black"
 21             android:textSize="17sp" />
 22 
 23         <EditText
 24             android:id="@+id/et_name"
 25             android:layout_width="match_parent"
 26             android:layout_height="match_parent"
 27             android:layout_marginBottom="5dp"
 28             android:layout_marginTop="5dp"
 29             android:layout_toRightOf="@+id/tv_name"
 30             android:background="@drawable/editext_selector"
 31             android:gravity="left|center"
 32             android:hint="请输入姓名"
 33             android:inputType="text"
 34             android:maxLength="12"
 35             android:textColor="@color/black"
 36             android:textColorHint="@color/grey"
 37             android:textCursorDrawable="@drawable/text_cursor"
 38             android:textSize="17sp" />
 39     </RelativeLayout>
 40 
 41     <RelativeLayout
 42         android:layout_width="match_parent"
 43         android:layout_height="50dp" >
 44 
 45         <TextView
 46             android:id="@+id/tv_age"
 47             android:layout_width="wrap_content"
 48             android:layout_height="match_parent"
 49             android:layout_alignParentLeft="true"
 50             android:gravity="center"
 51             android:text="年龄："
 52             android:textColor="@color/black"
 53             android:textSize="17sp" />
 54 
 55         <EditText
 56             android:id="@+id/et_age"
 57             android:layout_width="match_parent"
 58             android:layout_height="match_parent"
 59             android:layout_marginBottom="5dp"
 60             android:layout_marginTop="5dp"
 61             android:layout_toRightOf="@+id/tv_age"
 62             android:background="@drawable/editext_selector"
 63             android:gravity="left|center"
 64             android:hint="请输入年龄"
 65             android:inputType="number"
 66             android:maxLength="2"
 67             android:textColor="@color/black"
 68             android:textColorHint="@color/grey"
 69             android:textCursorDrawable="@drawable/text_cursor"
 70             android:textSize="17sp" />
 71     </RelativeLayout>
 72 
 73     <RelativeLayout
 74         android:layout_width="match_parent"
 75         android:layout_height="50dp" >
 76 
 77         <TextView
 78             android:id="@+id/tv_height"
 79             android:layout_width="wrap_content"
 80             android:layout_height="match_parent"
 81             android:layout_alignParentLeft="true"
 82             android:gravity="center"
 83             android:text="身高："
 84             android:textColor="@color/black"
 85             android:textSize="17sp" />
 86 
 87         <EditText
 88             android:id="@+id/et_height"
 89             android:layout_width="match_parent"
 90             android:layout_height="match_parent"
 91             android:layout_marginBottom="5dp"
 92             android:layout_marginTop="5dp"
 93             android:layout_toRightOf="@+id/tv_height"
 94             android:background="@drawable/editext_selector"
 95             android:gravity="left|center"
 96             android:hint="请输入身高"
 97             android:inputType="number"
 98             android:maxLength="3"
 99             android:textColor="@color/black"
100             android:textColorHint="@color/grey"
101             android:textCursorDrawable="@drawable/text_cursor"
102             android:textSize="17sp" />
103     </RelativeLayout>
104 
105     <RelativeLayout
106         android:layout_width="match_parent"
107         android:layout_height="50dp" >
108 
109         <TextView
110             android:id="@+id/tv_weight"
111             android:layout_width="wrap_content"
112             android:layout_height="match_parent"
113             android:layout_alignParentLeft="true"
114             android:gravity="center"
115             android:text="体重："
116             android:textColor="@color/black"
117             android:textSize="17sp" />
118 
119         <EditText
120             android:id="@+id/et_weight"
121             android:layout_width="match_parent"
122             android:layout_height="match_parent"
123             android:layout_marginBottom="5dp"
124             android:layout_marginTop="5dp"
125             android:layout_toRightOf="@+id/tv_weight"
126             android:background="@drawable/editext_selector"
127             android:gravity="left|center"
128             android:hint="请输入体重"
129             android:inputType="numberDecimal"
130             android:maxLength="5"
131             android:textColor="@color/black"
132             android:textColorHint="@color/grey"
133             android:textCursorDrawable="@drawable/text_cursor"
134             android:textSize="17sp" />
135     </RelativeLayout>
136 
137     <RelativeLayout
138         android:layout_width="match_parent"
139         android:layout_height="50dp" >
140 
141         <TextView
142             android:id="@+id/tv_married"
143             android:layout_width="wrap_content"
144             android:layout_height="match_parent"
145             android:layout_alignParentLeft="true"
146             android:gravity="center"
147             android:text="婚否："
148             android:textColor="@color/black"
149             android:textSize="17sp" />
150 
151         <Spinner
152             android:id="@+id/sp_married"
153             android:layout_width="match_parent"
154             android:layout_height="match_parent"
155             android:layout_toRightOf="@+id/tv_married"
156             android:gravity="left|center"
157             android:spinnerMode="dialog" />
158     </RelativeLayout>
159 
160     <Button
161         android:id="@+id/btn_save"
162         android:layout_width="match_parent"
163         android:layout_height="wrap_content"
164         android:text="保存到数据库"
165         android:textColor="@color/black"
166         android:textSize="20sp" />
167 
168 </LinearLayout>

Read

 1 package com.example.alimjan.hello_world;
 2 
 3 /**
 4  * Created by alimjan on 7/4/2017.
 5  */
 6 
 7         import java.util.ArrayList;
 8 
 9         import com.example.alimjan.hello_world.bean.UserInfo;
10         import com.example.alimjan.hello_world.dataBase.UserDBHelper;
11 
12         import android.content.Context;
13         import android.content.Intent;
14         import android.os.Bundle;
15         import android.support.v7.app.AppCompatActivity;
16         import android.view.View;
17         import android.view.View.OnClickListener;
18         import android.widget.TextView;
19         import android.widget.Toast;
20 
21 public class class_4_2_2 extends AppCompatActivity implements OnClickListener {
22 
23     private UserDBHelper mHelper;
24     private TextView tv_sqlite;
25 
26     @Override
27     protected void onCreate(Bundle savedInstanceState) {
28         super.onCreate(savedInstanceState);
29         setContentView(R.layout.code_4_2_2);
30         tv_sqlite = (TextView) findViewById(R.id.tv_sqlite);
31         findViewById(R.id.btn_delete).setOnClickListener(this);
32     }
33 
34     private void readSQLite() {
35         if (mHelper == null) {
36             showToast("数据库连接为空");
37             return;
38         }
39         ArrayList<UserInfo> userArray = mHelper.query("1=1");
40         String desc = String.format("数据库查询到%d条记录，详情如下：", userArray.size());
41         for (int i=0; i<userArray.size(); i++) {
42             UserInfo info = userArray.get(i);
43             desc = String.format("%s\n第%d条记录信息如下：", desc, i+1);
44             desc = String.format("%s\n　姓名为%s", desc, info.name);
45             desc = String.format("%s\n　年龄为%d", desc, info.age);
46             desc = String.format("%s\n　身高为%d", desc, info.height);
47             desc = String.format("%s\n　体重为%f", desc, info.weight);
48             desc = String.format("%s\n　婚否为%b", desc, info.married);
49             desc = String.format("%s\n　更新时间为%s", desc, info.update_time);
50         }
51         if (userArray==null || userArray.size()<=0) {
52             desc = "数据库查询到的记录为空";
53         }
54         tv_sqlite.setText(desc);
55     }
56 
57     @Override
58     protected void onStart() {
59         super.onStart();
60         mHelper = UserDBHelper.getInstance(this, 2);
61         mHelper.openReadLink();
62         readSQLite();
63     }
64 
65     @Override
66     protected void onStop() {
67         super.onStop();
68         mHelper.closeLink();
69     }
70 
71     @Override
72     public void onClick(View v) {
73         if (v.getId() == R.id.btn_delete) {
74             //删除所有记录
75             mHelper.closeLink();
76             mHelper.openWriteLink();
77             mHelper.deleteAll();
78             //重新读取数据库
79             mHelper.closeLink();
80             mHelper.openReadLink();
81             readSQLite();
82         }
83     }
84 
85     private void showToast(String desc) {
86         Toast.makeText(this, desc, Toast.LENGTH_SHORT).show();
87     }
88 
89     public static void startHome(Context mContext) {
90         Intent intent = new Intent(mContext, class_4_2_2.class);
91         mContext.startActivity(intent);
92     }
93 
94 }

 1 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
 2     android:layout_width="match_parent"
 3     android:layout_height="match_parent"
 4     android:focusable="true"
 5     android:focusableInTouchMode="true"
 6     android:orientation="vertical"
 7     android:padding="10dp" >
 8 
 9     <Button
10         android:id="@+id/btn_delete"
11         android:layout_width="match_parent"
12         android:layout_height="wrap_content"
13         android:text="删除所有记录"
14         android:textColor="@color/black"
15         android:textSize="20sp" />
16 
17     <TextView
18         android:id="@+id/tv_sqlite"
19         android:layout_width="match_parent"
20         android:layout_height="wrap_content"
21         android:paddingTop="10dp"
22         android:textColor="@color/black"
23         android:textSize="17sp" />
24 
25 </LinearLayout>

posted @ 2017-07-05 10:51 alm 阅读(420) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

alm

Android 开发笔记___SQLite__基本用法

公告

Android 开发笔记_SQLite基本用法