package com.example.a94168.exp6;
import android.app.Activity;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.R.integer;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Adapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity implements View.OnClickListener {
private DBAdapter dbAdapter;
private EditText addSnoEditText,addSnameEditText,addClassEditText,dleteBySnoEditText,deleteByIdEditText,findBySnoEditText,findOneStudentByIdEditText,updateByIdEditText;
private Button btnAdd,btnFindAll,btnClearAllData,btnClearShowView,btnDeleteBySno,btnDeleteById,btnFindBySno,btnFindOneStudentById,btnUpdateById;
private TextView labelShow;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// DB db = new DB(this);
// SQLiteDatabase dbWrite = db.getWritableDatabase();
// //构造一条数据
// ContentValues cv = new ContentValues();
// cv.put("name", "小张");
// cv.put("sex", "男");
// dbWrite.insert("user", null, cv);
// cv = new ContentValues();
// cv.put("name", "小李");
// cv.put("sex", "女");
// dbWrite.insert("user", null, cv);
// dbWrite.close();
// SQLiteDatabase dbRead = db.getReadableDatabase();
//将所有的数据都返回,所以columns这一咧指定为空,代表查询到所有的数据
//selection代表查询的条件
//selectionArgs代表查询条件的参数
// groupBy用于指定分组
// having用于指定条件
// orderBy用于指定排序
// dbRead.query("user", null, "name=?", new String[]{"小张"}, groupBy, having, orderBy)
//返回值为游标类型的
// Cursor cursor = dbRead.query("user", null, null, null, null, null, null);
// while (cursor.moveToNext()) {
// String name = cursor.getString(cursor.getColumnIndex("name"));
// String sex = cursor.getString(cursor.getColumnIndex("sex"));
// System.out.println(name +"\n" + sex+"\n");
// }
btnAdd = (Button)findViewById(R.id.btnAdd);
btnFindAll = (Button)findViewById(R.id.btnFindAll);
addSnoEditText = (EditText)findViewById(R.id.addSno);
addSnameEditText = (EditText)findViewById(R.id.addSname);
addClassEditText = (EditText)findViewById(R.id.addClass);
labelShow = (TextView)findViewById(R.id.labelShow);
btnClearShowView = (Button)findViewById(R.id.btnClearShowView);
btnClearAllData = (Button)findViewById(R.id.btnClearAllData);
btnDeleteById = (Button)findViewById(R.id.btnDeleteById);
btnDeleteBySno = (Button)findViewById(R.id.btnDeleteBySno);
dleteBySnoEditText = (EditText)findViewById(R.id.dleteBySnoEditText);
deleteByIdEditText= (EditText)findViewById(R.id.deleteByIdEditText);
findBySnoEditText = (EditText)findViewById(R.id.findBySnoEditText);
updateByIdEditText = (EditText)findViewById(R.id.updateByIdEditText);
btnUpdateById = (Button)findViewById(R.id.btnUpdateById);
findOneStudentByIdEditText = (EditText)findViewById(R.id.findOneStudentByIdEditText);
btnFindBySno = (Button)findViewById(R.id.btnFindBySno);
btnFindOneStudentById = (Button)findViewById(R.id.btnFindOneStudentById);
btnAdd.setOnClickListener(this);
btnFindAll.setOnClickListener(this);
btnClearShowView.setOnClickListener(this);
btnClearAllData.setOnClickListener(this);
btnDeleteById.setOnClickListener(this);
btnDeleteBySno.setOnClickListener(this);
btnFindBySno.setOnClickListener(this);
btnFindOneStudentById .setOnClickListener(this);
btnUpdateById.setOnClickListener(this);
dbAdapter = new DBAdapter(this);
dbAdapter.open();
}
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
switch (v.getId()) {
//添加数据
case R.id.btnAdd:
{
Student ss = new Student();
String sno = addSnoEditText.getText().toString();
String sname = addSnameEditText.getText().toString();
String classes = addClassEditText.getText().toString();
ss.setSno(sno);
ss.setSname(sname);
ss.setClasses(classes);
dbAdapter.insert(ss);
Toast.makeText(this, "添加成功",Toast.LENGTH_LONG).show();
//清空上一次的文本框输入的数据
addSnoEditText.setText("");
addSnameEditText.setText("");
addClassEditText.setText("");
//跟新添加数据后的显示频
Student[] s =dbAdapter.findAllStudent();
//输出测试
// for (int i = 0; i < s.length; i++) {
// System.out.println(s[i].getSname()+"\n"+s[i].getClasses()+"\n"+s[i].getSno()+"\n");
// }
if(s==null)
{
labelShow.setText("数据库中不存在数据");
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
return ;
}
else
{
String msg="";
for (int i = 0; i < s.length; i++)
{
msg += s[i].toString()+"\n";
}
labelShow.setText(msg);
}
break;
}
//查询到数据库中所有的数据
case R.id.btnFindAll:
{
Student[] s =dbAdapter.findAllStudent();
//输出测试
// for (int i = 0; i < s.length; i++) {
// System.out.println(s[i].getSname()+"\n"+s[i].getClasses()+"\n"+s[i].getSno()+"\n");
// }
if(s==null)
{
labelShow.setText("数据库中不存在数据");
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
return ;
}
else
{
String msg="";
for (int i = 0; i < s.length; i++)
{
msg += s[i].toString()+"\n";
}
labelShow.setText(msg);
}
break;
}
//删除数据库中的所有数据操作
case R.id.btnClearAllData:
{
dbAdapter.deleteAllStudent();
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
labelShow.setText("数据全部删除");
break;
}
//清除显示屏幕上的所有数据
case R.id.btnClearShowView:
{
labelShow.setText("");
Toast.makeText(this, "显示屏清除数据成功", Toast.LENGTH_LONG).show();
break;
}
//按照id来及行删除
case R.id.btnDeleteById:
{
String _id = deleteByIdEditText.getText().toString();
if ("".equals(_id)) {
Toast.makeText(this, "ID不能为空", Toast.LENGTH_LONG).show();
return ;
}
int id = Integer.parseInt(_id);
labelShow.setText("");
dbAdapter.deleteBYId(id);
Student[] s =dbAdapter.findAllStudent();
if(s==null)
{
labelShow.setText("数据库中不存在数据");
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
return ;
}
else
{
String msg="";
for (int i = 0; i < s.length; i++)
{
msg += s[i].toString()+"\n";
}
labelShow.setText(msg);
}
Toast.makeText(this, "删除成功", Toast.LENGTH_LONG).show();
break;
}
//按照学号老进行删除
case R.id.btnDeleteBySno:
{
String sno = dleteBySnoEditText.getText().toString();
if ("".equals(sno)) {
Toast.makeText(this, "学号不能为空", Toast.LENGTH_LONG).show();
return ;
}
dbAdapter.deleteBySno(sno);
labelShow.setText("");
Student[] s =dbAdapter.findAllStudent();
if(s==null)
{
labelShow.setText("数据库中不存在数据");
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
return ;
}
else
{
String msg="";
for (int i = 0; i < s.length; i++)
{
msg += s[i].toString()+"\n";
}
labelShow.setText(msg);
}
Toast.makeText(this, "删除成功", Toast.LENGTH_LONG).show();
break;
}
case R.id.btnFindBySno:
{
String sno = findBySnoEditText.getText().toString();
if ("".equals(sno)) {
Toast.makeText(this, "学号不能为空", Toast.LENGTH_LONG).show();
return ;
}
findBySnoEditText.setText("");
Student []s =dbAdapter.findStudentsBySno(sno);
labelShow.setText("");
if(s==null)
{
labelShow.setText("数据库中不存在数据");
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
return ;
}
else
{
String msg="";
for (int i = 0; i < s.length; i++)
{
msg += s[i].toString()+"\n";
}
labelShow.setText(msg);
}
break;
}
case R.id.btnFindOneStudentById:
{
String _id =findOneStudentByIdEditText.getText().toString();
if ("".equals(_id)) {
Toast.makeText(this, "ID不能为空", Toast.LENGTH_LONG).show();
return ;
}
int id = Integer.parseInt(_id);
labelShow.setText("");
Student[] s = dbAdapter.findOneStudentByID(id);
if(s==null)
{
labelShow.setText("数据库中不存在数据");
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
return ;
}
else
{
String msg="";
for (int i = 0; i < s.length; i++)
{
msg += s[i].toString()+"\n";
}
labelShow.setText(msg);
}
break;
}
case R.id.btnUpdateById:
{
String _id = updateByIdEditText.getText().toString();
if ("".equals(_id)) {
Toast.makeText(this, "ID不能为空", Toast.LENGTH_LONG).show();
return ;
}
int id = Integer.parseInt(_id);
String sno = addSnoEditText.getText().toString();
String sname = addSnameEditText.getText().toString();
String classes = addClassEditText.getText().toString();
Student ss = new Student();
ss.setId(id);
ss.setSno(sno);
ss.setSname(sname);
ss.setClasses(classes);
dbAdapter.updateOneStudentById(id, ss);
//查询跟新的数据 显示出来
Student[] s =dbAdapter.findAllStudent();
//输出测试
// for (int i = 0; i < s.length; i++) {
// System.out.println(s[i].getSname()+"\n"+s[i].getClasses()+"\n"+s[i].getSno()+"\n");
// }
if(s==null)
{
labelShow.setText("数据库中不存在数据");
Toast.makeText(this, "数据库中不存在数据", Toast.LENGTH_LONG).show();
return ;
}
else
{
String msg="";
for (int i = 0; i < s.length; i++)
{
msg += s[i].toString()+"\n";
}
labelShow.setText(msg);
}
break;
}
default:
break;
}
}
}
package com.example.a94168.exp6;
import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBAdapter {
private final Context context;
private SQLiteDatabase db;
private DBHelper dbHelper;
private final static String DB_TABLE="studentinfo";
private final static String DB_NAME="student.db";
private final static int DB_VERSION=1;
private final static String KEY_ID="id";
private final static String KEY_SNO="sno";
private final static String KEY_SNAME="sname";
private final static String KEY_CLASSES="classes";
public DBAdapter(Context _cContext) {
// TODO Auto-generated constructor stub
context = _cContext;
}
//关闭数据库的连接
public void close()
{
if(db!=null)
{
db.close();
db=null;
}
}
public void open() throws SQLiteException
{
dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
try{
db = dbHelper.getWritableDatabase();
}catch(Exception e)
{
db = dbHelper.getReadableDatabase();
}
}
//插入数据
public long insert(Student s)
{
ContentValues cv = new ContentValues();
cv.put(KEY_SNO,s.getSno() );
cv.put(KEY_SNAME, s.getSname());
cv.put(KEY_CLASSES, s.getClasses());
return db.insert(DB_TABLE, null, cv);
}
//查询所有的数据
public Student[] findAllStudent()
{
Cursor c = db.query(DB_TABLE, null, null, null, null,null, null);
return convertToStudent(c);
}
public long deleteAllStudent()
{
return db.delete(DB_TABLE, null, null);
}
//这种方发比较好能够防止sql注入
public long deleteBySno(String sno)
{
return db.delete(DB_TABLE, "sno=?", new String[]{sno});
}
//这种方式其实不太好存在sql注入的问题,但是一般的情况下是可以使用的
public long deleteBYId(int id)
{
return db.delete(DB_TABLE, "id="+id, null);
}
public Student[] findStudentsBySno(String sno)
{
Cursor c = db.query(DB_TABLE, null, "sno=?", new String[]{sno}, null, null, null);
return convertToStudent(c);
}
public long updateOneStudentById(int id,Student s)
{
ContentValues cv = new ContentValues();
cv.put(KEY_SNO, s.getSno());
cv.put(KEY_SNAME, s.getSname());
cv.put(KEY_CLASSES, s.getClasses());
return db.update(DB_TABLE, cv, "id="+id, null);
}
public Student[] findOneStudentByID(int ID)
{
Cursor c = db.query(DB_TABLE, null, "id="+ID, null, null, null, null);
return convertToStudent(c);
}
//转换函数
public Student[] convertToStudent(Cursor c)
{
int resultsCount = c.getCount();
if(resultsCount==0||!c.moveToFirst())
{
return null;
}
Student []stu = new Student[resultsCount];
for (int i = 0; i < stu.length; i++) {
stu[i] = new Student();
String sno = c.getString(c.getColumnIndex("sno"));
String sname = c.getString(c.getColumnIndex("sname"));
String classes = c.getString(c.getColumnIndex("classes"));
stu[i].id=c.getInt(0);
stu[i].setSno(sno);
stu[i].setSname(sname);
stu[i].setClasses(classes);
//切记不可少了这一句,这个是循环的条件
c.moveToNext();
}
return stu;
}
//内部的静态类用于创建数据库和建立数据库链接
private static class DBHelper extends SQLiteOpenHelper{
public DBHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
private static final String SQL="CREATE TABLE studentinfo ("
+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "sno TEXT DEFAULT NONE,"
+ "sname TEXT DEFAULT NONE,"
+ "classes TEXT DEFAULT NONE"
+ ")";
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(SQL);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS"+DB_TABLE);
onCreate(db);
}
}
}
package com.example.a94168.exp6;
import java.io.Serializable;
public class Student implements Serializable{
public int id=-1;
public String sno;
public String sname;
public String classes;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getSno() {
return sno;
}
public void setSno(String sno) {
this.sno = sno;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getClasses() {
return classes;
}
public void setClasses(String classes) {
this.classes = classes;
}
@Override
public String toString() {
return "id锛�" + id +" " + "瀛﹀彿锛�" + sno +" "+ "濮撳悕锛�" + sname +" "+ "鐝骇锛�" + classes ;
}
}
.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="10dp"
android:paddingLeft="10dp"
android:paddingRight="10dp"
android:paddingTop="10dp"
tools:context=".MainActivity"
android:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical" >
<TableLayout
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<TableRow
android:id="@+id/tableRow1"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<TextView
android:id="@+id/viewSno"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="学号" />
<EditText
android:id="@+id/addSno"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10" >
<requestFocus />
</EditText>
</TableRow>
<TableRow
android:id="@+id/tableRow2"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<TextView
android:id="@+id/viewSname"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="姓名" />
<EditText
android:id="@+id/addSname"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10" />
</TableRow>
<TableRow
android:id="@+id/tableRow3"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<TextView
android:id="@+id/viewClass"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="班级" />
<EditText
android:id="@+id/addClass"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10" />
</TableRow>
<LinearLayout android:layout_height="wrap_content" >
<TableRow
android:id="@+id/tableRow4"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btnAdd"
android:layout_width="220px"
android:layout_height="wrap_content"
android:text="添加" />
<Button
android:id="@+id/btnUpdateById"
android:layout_width="380px"
android:layout_height="wrap_content"
android:text="更新根据ID" />
<EditText
android:id="@+id/updateByIdEditText"
android:layout_width="420px"
android:layout_height="wrap_content"
android:ems="10" />
</TableRow>
</LinearLayout>
</TableLayout>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical" >
<TableLayout
android:layout_width="match_parent"
android:layout_height="wrap_content" >
<TableRow
android:id="@+id/tableRow5"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btnFindAll"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="查询所有" />
<Button
android:id="@+id/btnFindBySno"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="学号查询" />
<EditText
android:id="@+id/findBySnoEditText"
android:layout_width="420px"
android:layout_height="wrap_content"
android:ems="10" />
</TableRow>
<TableRow
android:id="@+id/tableRow6"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btnClearShowView"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="清除显示" />
<Button
android:id="@+id/btnDeleteBySno"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="学号删除" />
<EditText
android:id="@+id/dleteBySnoEditText"
android:layout_width="420px"
android:layout_height="wrap_content"
android:ems="10" />
</TableRow>
<TableRow
android:id="@+id/tableRow7"
android:layout_width="wrap_content"
android:layout_height="wrap_content" >
<Button
android:id="@+id/btnClearAllData"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除所有" />
<Button
android:id="@+id/btnDeleteById"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="ID删除" />
<EditText
android:id="@+id/deleteByIdEditText"
android:layout_width="420px"
android:layout_height="wrap_content"
android:ems="10" />
</TableRow>
</TableLayout>
</LinearLayout>
<RelativeLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
>
<Button
android:id="@+id/btnFindOneStudentById"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
android:text="ID查询" />
<EditText
android:id="@+id/findOneStudentByIdEditText"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/findOneStudentById"
android:layout_alignBottom="@+id/findOneStudentById"
android:layout_alignParentRight="true"
android:ems="10"
android:layout_toRightOf="@+id/btnFindOneStudentById"
android:layout_toEndOf="@+id/btnFindOneStudentById" />
</RelativeLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical" >
<TextView
android:id="@+id/labelShow"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="" />
</LinearLayout>
</LinearLayout>