Android 数据库SQL增、删、查、改

 

根据写的小程序来记录下关于SQL的简单使用，部分代码直接引用了程序里的代码

首先创建表：

String sql="create table stu_table(id int,sName verChar(30),sSex verChar(10),sAge int)";
db.execSQL(sql);

ID的属性是各种信息！

 

 

SQL增删查改对应的语句如下：

增加数据（两种方法）：

1、

1 private void insert(SQLiteDatabase db) {  
2    
3     //实例化常量值  
4     ContentValues cV = new ContentValues();  
5     cV.put("name","zhangsan");  
6     cV.put("age","21");  
7     db.insert("stu_table",null,cV);    //调用insert()方法插入数据  
8 }

2、

1  db.execSQL("insert into stu_table(id,sName,sSex,sAge)values(?,?,?,?)",
2                             new Object[]{
3                             numText.getText().toString(), 
4                             nameText.getText().toString(),
5                             sexText.getText().toString(), 
6                             ageText.getText().toString()}
7 
8                           //接受四个用户输入的数据，直接用SQL实现增加数据
9 );

 

 

 

删除数据（两种方法）：

1、

 1 private void delete(SQLiteDatabase db) {  
 2    
 3    //删除条件  
 4    String whereClause = "_id=?";  
 5    
 6    //删除条件所在的位置
 7    String[] whereArgs = {String.valueOf(2)};  
 8    
 9    //执行删除  
10    db.delete("stu_table",whereClause,whereArgs);   
11 }

2、

1  StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1);
2  SQLiteDatabase db = my_db.getWritableDatabase();
3  db.delete("stu_table", "id=?", new String[{numText.getText().toString()});

 

 

修改数据（两种方法）：

1、

  StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1);
            SQLiteDatabase db = my_db.getWritableDatabase();
            //得到用户输入的数据
            String[] sss = new String[4];
            sss[0] = numText.getText().toString();
            sss[1] = nameText.getText().toString();
            sss[2] = sexText.getText().toString();
            sss[3] = ageText.getText().toString();
  db.execSQL("update stu_table set sName=?,sSex=?,sAge=? where id=?", new Object[]{
                            sss[1], sss[2], sss[3], sss[0]
                    });

2、

private void update(SQLiteDatabase db) {  
   
    //实例化内容值  
    ContentValues values = new ContentValues();  
   
    //在values中添加内容   
    values.put("name","lisi");  
   
    //修改条件   
    String whereClause = "id=?";  
   
    //修改添加参数  
    String[] whereArgs={String.valuesOf(1)};  
   
    //修改  
    db.update("usertable",values,whereClause,whereArgs);   
}

 

 

查询数据：

查询数据是通过Cursor查询，就像一个游标。

函数如下：

public Cursor query(String table,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit);

table:表名

columns:列名称数组

selection:相当于where，条件

selectionArgs:条件字句

groupBy:分组列

having：分组条件

orderBy：按照指定的排序列

limit:分页查询限制

Cursor:返回值，相当于结果集ResultSet

 

关于Cursor的各种方法：

 

方法名称

方法描述

getCount()

获得总的数据项数

isFirst()

判断是否第一条记录

isLast()

判断是否最后一条记录

moveToFirst()

移动到第一条记录

moveToLast()

移动到最后一条记录

move(int offset)

移动到指定记录

moveToNext()

移动到下一条记录

moveToPrevious()

移动到上一条记录

getColumnIndexOrThrow(String columnName)

根据列名称获得列索引

getInt(int columnIndex)

获得指定列索引的int类型值

getString(int columnIndex)

获得指定列缩影的String类型值

 

 1 StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1);    SQLiteDatabase db = my_db.getReadableDatabase();
 2 String strings = numText.getText().toString();
 3 Cursor cursor = db.query("stu_table", new String[]{"id", "sName", "sSex","sAge"}, "id=?", new String[]{strings}, null, null, null, null);//查询数据
 4  while (cursor1.moveToNext()) {
 5                     String no = cursor.getString(cursor.getColumnIndex("id"));
 6                     String name cursor.getString(cursor.getColumnIndex("sName"));
 7                     String sex = cursor.getString(cursor.getColumnIndex("sSex"));
 8                     String age = cursor.getString(cursor.getColumnIndex("sAge"));
 9 
10                     String sss = "   " + no + "    " + name + "    " + sex + "    " + age + "    ";
11                     textView.setTextSize(30);
12                     textView.setText(sss); //把查询到的数据显示到textview
13                 }

 

 

下面是全部代码，

MainActivity.java代码如下：

  1 package com.example.lenovo.stu_imfo;
  2 
  3 import android.app.AlertDialog;
  4 import android.content.DialogInterface;
  5 import android.database.Cursor;
  6 import android.database.sqlite.SQLiteDatabase;
  7 import android.support.v7.app.AppCompatActivity;
  8 import android.os.Bundle;
  9 import android.util.Log;
 10 import android.view.View;
 11 import android.widget.Button;
 12 import android.widget.EditText;
 13 import android.widget.TextView;
 14 import android.widget.Toast;
 15 
 16 public class MainActivity extends AppCompatActivity {
 17 
 18     private Button queBut;
 19     private Button addBut;
 20     private Button delBut;
 21     private Button updBut;
 22     private EditText numText;
 23     private EditText nameText;
 24     private EditText sexText;
 25     private EditText ageText;
 26     private TextView textView;
 27 
 28     @Override
 29     protected void onCreate(Bundle savedInstanceState) {
 30         super.onCreate(savedInstanceState);
 31         setContentView(R.layout.activity_main);
 32 
 33         setView();
 34         setListener();
 35     }
 36 
 37     //建立各种view
 38     private void setView() {
 39         queBut = (Button) findViewById(R.id.queBut);
 40         addBut = (Button) findViewById(R.id.addBut);
 41         delBut = (Button) findViewById(R.id.delBut);
 42         updBut = (Button) findViewById(R.id.updBut);
 43         numText = (EditText) findViewById(R.id.numText);
 44         nameText = (EditText) findViewById(R.id.nameText);
 45         ageText = (EditText) findViewById(R.id.ageText);
 46         sexText = (EditText) findViewById(R.id.sexText);
 47         textView = (TextView) findViewById(R.id.info);
 48     }
 49 
 50     //全部的监听事件
 51     public void setListener() {
 52         queBut.setOnClickListener(new queryListener());
 53         addBut.setOnClickListener(new InsertListener());
 54         delBut.setOnClickListener(new DeleteListener());
 55         updBut.setOnClickListener(new ModifyListener());
 56     }
 57 
 58 
 59     //添加数据
 60     class InsertListener implements View.OnClickListener {
 61 
 62         @Override
 63         public void onClick(View v) {
 64             Boolean flag = false;
 65             StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1);
 66             SQLiteDatabase db = my_db.getWritableDatabase();
 67             final String[] ss = new String[4];
 68             ss[0] = numText.getText().toString();
 69             ss[1] = nameText.getText().toString();
 70             ss[2] = sexText.getText().toString();
 71             ss[3] = ageText.getText().toString();         //得到用户输入的数据
 72 
 73             if (!ss[0].equals("") && !ss[1].equals("") && !ss[2].equals("") && !ss[3].equals("")) {
 74                 Cursor cursor = db.query("stu_table", new String[]{"id"}, "id=?", new String[]{ss[0]}, null, null, null);
 75                 while (cursor.moveToNext()) {
 76                     String No = cursor.getString(cursor.getColumnIndex("id"));
 77                     if (!No.equals(ss[0]))
 78                         flag = false;
 79                     else {
 80                         flag = true;
 81                         break;
 82                     }
 83                 }
 84                 if (flag == false) {
 85                     db.execSQL("insert into stu_table(id,sName,sSex,sAge)values(?,?,?,?)",
 86                             new Object[]{numText.getText().toString(), nameText.getText().toString(), sexText.getText().toString(), ageText.getText().toString()});
 87                     Log.i("test", "——————————插入数据OK！——————————");
 88                     Toast.makeText(MainActivity.this, "添加成功！", Toast.LENGTH_SHORT).show();
 89 
 90                 } else {
 91                     Toast.makeText(MainActivity.this, "该学号已存在", Toast.LENGTH_SHORT).show();
 92                     new AlertDialog.Builder(MainActivity.this)
 93                             .setTitle("提示")
 94                             .setMessage("该学号已存在，请重新填写信息")
 95                             .setIcon(R.drawable.ic)
 96                             .setPositiveButton("好的", new DialogInterface.OnClickListener() {
 97                                 @Override
 98                                 public void onClick(DialogInterface dialog, int which) {
 99                                     setResult(RESULT_OK);
100                                 }
101                             }).show();
102                 }
103 
104             } else if (ss[1].length() == 0 || ss[2].length() == 0 || ss[3].length() == 0) {
105                 new AlertDialog.Builder(MainActivity.this)
106                         .setTitle("提示")
107                         .setMessage("您填的信息不完整")
108                         .setIcon(R.drawable.ic)
109                         .setPositiveButton("我知道了", new DialogInterface.OnClickListener() {
110                             @Override
111                             public void onClick(DialogInterface dialog, int which) {
112                                 setResult(RESULT_OK);
113                             }
114                         }).show();
115             }
116 
117             db.close();
118 
119         }
120     }
121 
122     //查询个人信息
123     class queryListener implements View.OnClickListener {
124 
125         @Override
126         public void onClick(View v) {
127             StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1);
128             SQLiteDatabase db = my_db.getReadableDatabase();
129             String strings = numText.getText().toString();
130             Cursor cursor = db.query("stu_table", new String[]{"id", "sName", "sSex", "sAge"}, "id=?", new String[]{strings}, null, null, null, null);
131             if (!cursor.moveToNext()) {
132                 new AlertDialog.Builder(MainActivity.this)
133                         .setTitle("提示")
134                         .setMessage("该学生不存在，查询失败")
135                         .setIcon(R.drawable.ic)
136                         .setPositiveButton("确定", new DialogInterface.OnClickListener() {
137                             @Override
138                             public void onClick(DialogInterface dialog, int which) {
139                                 setResult(RESULT_OK);
140                             }
141                         }).show();
142             } else {
143                 Cursor cursor1 = db.query("stu_table", new String[]{"id", "sName", "sSex", "sAge"}, "id=?", new String[]{strings}, null, null, null, null);
144                 while (cursor1.moveToNext()) {
145                     String no = cursor.getString(cursor.getColumnIndex("id"));
146                     String name = cursor.getString(cursor.getColumnIndex("sName"));
147                     String sex = cursor.getString(cursor.getColumnIndex("sSex"));
148                     String age = cursor.getString(cursor.getColumnIndex("sAge"));
149                     String sss = "   " + no + "    " + name + "    " + sex + "    " + age + "    ";
150                     textView.setTextSize(30);
151                     textView.setText(sss);
152                 }
153                 Log.i("test", "——————————查询数据OK！——————————");
154                 my_db.close();
155             }
156 
157         }
158     }
159     
160     //删除信息
161 
162     class DeleteListener implements View.OnClickListener {
163 
164 
165         @Override
166         public void onClick(View v) {
167             new AlertDialog.Builder(MainActivity.this)
168                     .setTitle("标题")
169                     .setMessage("您确定要删除该学号的学生信息吗？")
170                     .setIcon(R.drawable.ic)
171                     .setPositiveButton("YES", new DialogInterface.OnClickListener() {
172                         @Override
173                         public void onClick(DialogInterface dialog, int which) {
174                             setResult(RESULT_OK);
175                             StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1);
176                             SQLiteDatabase db = my_db.getWritableDatabase();
177                             db.delete("stu_table", "id=?", new String[]{numText.getText().toString()});
178                             db.close();
179                             Toast.makeText(MainActivity.this, "删除成功！", Toast.LENGTH_SHORT).show();
180 
181                         }
182 
183                     }).setNegativeButton("No", new DialogInterface.OnClickListener() {
184 
185 
186                 @Override
187                 public void onClick(DialogInterface dialog, int which) {
188 
189                 }
190             }).show();
191 
192         }
193     }
194 
195     
196     //修改信息
197     class ModifyListener implements View.OnClickListener {
198 
199         @Override
200         public void onClick(View v) {
201 
202 
203             StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1);
204             SQLiteDatabase db = my_db.getWritableDatabase();
205             String[] sss = new String[4];
206             sss[0] = numText.getText().toString();
207             sss[1] = nameText.getText().toString();
208             sss[2] = sexText.getText().toString();
209             sss[3] = ageText.getText().toString();
210             if (sss[0].equals("") || sss[1].equals("") || sss[2].equals("") || sss[3].equals("")) {
211                 new AlertDialog.Builder(MainActivity.this)
212                         .setTitle("提示")
213                         .setIcon(R.drawable.ic)
214                         .setMessage("填的信息不完整！")
215                         .setPositiveButton("确定", new DialogInterface.OnClickListener() {
216                             @Override
217                             public void onClick(DialogInterface dialog, int which) {
218                                 setResult(RESULT_OK);
219                             }
220                         }).show();
221             }
222             if (sss[0].length() != 0 && sss[1].length() != 0 && sss[2].length() != 0 && sss[3].length() != 0) {
223                 boolean flag = false;
224                 Cursor cursor = db.query("stu_table", new String[]{"id"}, "id=?", new String[]{sss[0]}, null, null, null);
225                 while (cursor.moveToNext()) {
226                     String No = cursor.getString(cursor.getColumnIndex("id"));
227                     if (No.equals(sss[0])) {
228                         flag = true;
229                         break;
230                     }
231                 }
232                 if (flag == true) {
233                     db.execSQL("update stu_table set sName=?,sSex=?,sAge=? where id=?", new Object[]{
234                             sss[1], sss[2], sss[3], sss[0]
235                     });
236                     Toast.makeText(MainActivity.this, "修改成功！", Toast.LENGTH_SHORT).show();
237                 }
238                 if (flag == false) {
239                     new AlertDialog.Builder(MainActivity.this)
240                             .setTitle("提示")
241                             .setIcon(R.drawable.ic)
242                             .setMessage("该学号不存在！")
243                             .setPositiveButton("确定", new DialogInterface.OnClickListener() {
244                                 @Override
245                                 public void onClick(DialogInterface dialog, int which) {
246                                     setResult(RESULT_OK);
247                                 }
248                             }).show();
249                 }
250 
251             }
252             db.close();
253         }
254     }
255 }
View Code

StuDBHelper.java代码如下：

 1 package com.example.lenovo.stu_imfo;
 2 
 3 import android.content.Context;
 4 import android.database.sqlite.SQLiteDatabase;
 5 import android.database.sqlite.SQLiteOpenHelper;
 6 import android.util.Log;
 7 
 8 public class StuDBHelper extends SQLiteOpenHelper {
 9     public static final String TAG="TestSQL";
10 
11     public StuDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
12         super(context, name, factory, version);
13     }
14 
15     @Override
16     public void onCreate(SQLiteDatabase db) {
17 
18         String sql="create table stu_table(id int,sName verChar(30),sSex verChar(10),sAge int)";
19         Log.i(TAG,"——————————创建数据库——————————");
20         db.execSQL(sql);
21     }
22 
23     @Override
24     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
25         Log.i(TAG, "——————————数据库更新——————————");
26 
27     }
28 
29 
30 }
View Code

布局：

  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:layout_marginTop="30dp"
  5     android:orientation="vertical">
  6 
  7     <LinearLayout
  8         android:layout_width="wrap_content"
  9         android:layout_height="wrap_content"
 10         android:orientation="horizontal">
 11 
 12         <TextView
 13             android:layout_width="wrap_content"
 14             android:layout_height="wrap_content"
 15             android:layout_marginLeft="30dp"
 16             android:text="学号" />
 17 
 18         <EditText
 19             android:id="@+id/numText"
 20             android:layout_width="100dp"
 21             android:layout_height="wrap_content"
 22             android:singleLine="true" />
 23 
 24         <TextView
 25             android:layout_width="wrap_content"
 26             android:layout_height="wrap_content"
 27             android:layout_marginLeft="50dp"
 28             android:text="姓名" />
 29 
 30         <EditText
 31             android:id="@+id/nameText"
 32             android:layout_width="100dp"
 33             android:layout_height="wrap_content"
 34             android:singleLine="true" />
 35     </LinearLayout>
 36 
 37     <LinearLayout
 38         android:layout_width="wrap_content"
 39         android:layout_height="wrap_content"
 40         android:layout_marginTop="20dp"
 41         android:orientation="horizontal">
 42 
 43         <TextView
 44             android:layout_width="wrap_content"
 45             android:layout_height="wrap_content"
 46             android:layout_marginLeft="30dp"
 47             android:singleLine="true"
 48             android:text="性别" />
 49 
 50         <EditText
 51             android:id="@+id/sexText"
 52             android:layout_width="100dp"
 53             android:layout_height="wrap_content"
 54             android:singleLine="true" />
 55 
 56         <TextView
 57             android:layout_width="wrap_content"
 58             android:layout_height="wrap_content"
 59             android:layout_marginLeft="50dp"
 60             android:singleLine="true"
 61             android:text="年龄" />
 62 
 63         <EditText
 64             android:id="@+id/ageText"
 65             android:layout_width="100dp"
 66             android:layout_height="wrap_content"
 67             android:singleLine="true" />
 68     </LinearLayout>
 69 
 70 
 71     <Button
 72         android:id="@+id/queBut"
 73         android:layout_width="fill_parent"
 74         android:layout_height="wrap_content"
 75         android:layout_marginTop="50dp"
 76         android:text="个人查询" />
 77 
 78     <Button
 79         android:id="@+id/addBut"
 80         android:layout_width="fill_parent"
 81         android:layout_height="wrap_content"
 82         android:layout_marginTop="10dp"
 83         android:text="添加学生信息" />
 84 
 85     <Button
 86         android:id="@+id/updBut"
 87         android:layout_width="fill_parent"
 88         android:layout_height="wrap_content"
 89         android:layout_marginTop="10dp"
 90         android:text="修改学生信息" />
 91 
 92     <Button
 93         android:id="@+id/delBut"
 94         android:layout_width="fill_parent"
 95         android:layout_height="wrap_content"
 96         android:layout_marginTop="10dp"
 97         android:text="删除学生信息" />
 98 
 99     <TextView
100         android:id="@+id/info"
101         android:layout_width="wrap_content"
102         android:layout_height="wrap_content"
103         android:layout_marginLeft="30dp"
104         android:layout_marginTop="20dp"
105         android:singleLine="true" />
106 
107 </LinearLayout>
View Code

 

显示如下：

posted @ 2015-12-06 18:07  白一  阅读(2104)  评论(1)  编辑  收藏  举报