android之SQLite简单应用-CSDN博客

本文链接：https://blog.csdn.net/qa962839575/article/details/42001649

主显示布局以及代码：

activity_main.xml：

java代码:（注意：这里execSQL(String sql)该方法只可支持增删改的操作）

package com.example.day05_01;

import java.util.ArrayList;
import java.util.List;

import android.support.v7.app.ActionBarActivity;
import android.text.TextUtils;
import android.util.Log;
import android.content.ContentValues;
import android.content.Context;
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.EditText;
import android.widget.TextView;

public class MainActivity extends ActionBarActivity {
//设置数据库名，表名，列名 -----strat
private final String DB_NAME = "users.db";
private final String TABLE_NAME = "users";
private final String NAME= "name";
private final String HOBBY = "hobby";
private final String ID = "id";
private final String HEIGHT = "height";
//设置数据库名，表名，列名 -----end
private EditText etId,etUserName,etHobby,etHeight;//填写信息的组件
private TextView tvResult;//显示信息的组件
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
createDatabase();
init();
setListener();
}

private void init() {
// TODO Auto-generated method stub
etId = (EditText) findViewById(R.id.etId);
etUserName = (EditText) findViewById(R.id.etUserName);
etHobby = (EditText) findViewById(R.id.etHobby);
etHeight = (EditText) findViewById(R.id.etHeight);
tvResult = (TextView) findViewById(R.id.tvResult);
}

private void setListener() {
// TODO Auto-generated method stub
setInsert();
setQuery();
setDelete();
setUpdate();
}

private void setUpdate() {
findViewById(R.id.btUpdate).setOnClickListener(new OnClickListener() {

@Override
public void onClick(View view) {
// TODO Auto-generated method stub
SQLiteDatabase db = openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);
String id = etId.getText().toString();
if(TextUtils.isEmpty(id)){
etId.setError("id必填");
return;
}
String name = etUserName.getText().toString();
if(TextUtils.isEmpty(name)){
etUserName.setError("姓名必填");
return;
}
String hobby = etHobby.getText().toString();
if(TextUtils.isEmpty(hobby)){
etHobby.setError("爱好必填");
return;
}
String height = etHeight.getText().toString();
if(TextUtils.isEmpty(height)){
etHeight.setError("身高必填");
return;
}
ContentValues values = new ContentValues();
values.put("name", name);
values.put("hobby", hobby);
values.put("height", height);
db.update(TABLE_NAME, values, ID+"=?",new String[]{id});
}
});
}

private void setDelete() {
findViewById(R.id.btDelete).setOnClickListener(new OnClickListener() {

@Override
public void onClick(View view) {
// TODO Auto-generated method stub
String id = etId.getText().toString();
if(TextUtils.isEmpty(id)){
etId.setError("id必填");
return;
}
SQLiteDatabase db = openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);
String deleteSql = "delete from "+TABLE_NAME +" where "+ID+" = "+id;
Log.i("main", "deleteSql :"+deleteSql);
db.execSQL(deleteSql);
}
});
}

private void setQuery() {
findViewById(R.id.btQuery).setOnClickListener(new OnClickListener() {

@Override
public void onClick(View view) {
// TODO Auto-generated method stub
SQLiteDatabase db = openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);
String name = etUserName.getText().toString();
String querySql = "select * from "+TABLE_NAME+" where "+NAME+" like ? ";
String[] params = new String[]{"%"+name+"%"};
Log.i("main", querySql);
List<UserBean> queryResult = new ArrayList<UserBean>();
Cursor cursor = db.rawQuery(querySql, params);
while(cursor.moveToNext()){
int id = cursor.getInt(cursor.getColumnIndex(ID));
String username = cursor.getString(cursor.getColumnIndex(NAME));
String hobby = cursor.getString(cursor.getColumnIndex(HOBBY));
long height = cursor.getLong(cursor.getColumnIndex(HEIGHT));
UserBean userBean = new UserBean(id,username,hobby,height+"");
queryResult.add(userBean);
}
String viewString = "";
for (int i=0;i<queryResult.size();i++){
UserBean userBean = queryResult.get(i);
viewString += userBean.toString();
}
tvResult.setText(viewString);
}
});
}

private void setInsert() {
findViewById(R.id.btInsert).setOnClickListener(new OnClickListener() {
@Override
public void onClick(View view) {
// TODO Auto-generated method stub
SQLiteDatabase db = openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);
String name = etUserName.getText().toString();
if(TextUtils.isEmpty(name)){
etUserName.setError("姓名必填");
return;
}
String hobby = etHobby.getText().toString();
if(TextUtils.isEmpty(hobby)){
etHobby.setError("爱好必填");
return;
}
String height = etHeight.getText().toString();
if(TextUtils.isEmpty(height)){
etUserName.setError("身高必填");
return;
}
String insertSql = "insert into "+TABLE_NAME +"(" +
NAME+","+HOBBY+","+HEIGHT+")"
+"values('"+name+"','"+hobby+"',"+height+")";
Log.i("main", "insertSql :"+insertSql);
db.execSQL(insertSql);
}
});
}

private void createDatabase() {
// TODO Auto-generated method stub
SQLiteDatabase db = openOrCreateDatabase(DB_NAME, MODE_PRIVATE,null);

//如果存在就打开数据库，不存在创建数据库
String createTable = "create table if not exists "+TABLE_NAME+" ("
+ID+" integer primary key autoincrement,"//注意这里要加上autoincrement，自增
+NAME+" char(50),"
+HOBBY+" char(50),"
+HEIGHT+" real"
+")"
;
Log.i("main", "createTableSql :"+createTable);
db.execSQL(createTable);
}

}

entity类：

package com.example.day05_01;

public class UserBean {
private int id;
private String name;
private String hobby;
private String height;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public String getHeight() {
return height;
}
public void setHeight(String height) {
this.height = height;
}
public UserBean(int id, String name, String hobby, String height) {
super();
this.id = id;
this.name = name;
this.hobby = hobby;
this.height = height;
}
@Override
public String toString() {
return "UserBean [id=" + id + ", name=" + name + ", hobby=" + hobby
+ ", height=" + height + "]";
}

}