package com.example.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBCreate extends SQLiteOpenHelper {
/**
* 2 是版本号
*
* @param context
*/
public DBCreate(Context context) {
super(context, "bing.db", null, 2);
// TODO 自动生成的构造函数存根
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO 自动生成的方法存根
System.out.println("aaaaaaaaaaaaaaaaa onCreate");
db.execSQL("CREATE TABLE person(id integer primary key autoincrement,name VARCHAR(20))");
}
/**
* onUpgrade()方法在数据库的版本发生变化时会被调用，一般在软件升级时才需改变版本号
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO 自动生成的方法存根
System.out.println("aaaaaaaaaaaaaaa onUpgrate");
db.execSQL("ALTER TABLE person ADD address VARCHAR(12) NULL ");
}
}
package dao;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.example.db.DBCreate;
import damain.Person;
public class PersonDao {
private DBCreate dbOpenHelper;
public PersonDao(Context context) {
dbOpenHelper = new DBCreate(context);
}
/**
* 添加
* @param name
* @param address
*/
public void add(String name,String address){
boolean result = find(name);
if(result)
return ;
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if(db.isOpen()){
db.execSQL("insert into person (name,address) values (?,?)",new Object[]{name,address});
db.close();
}
}
/**
*
* @param name
*/
public boolean find(String name){
boolean result;
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
if(db.isOpen()){
Cursor cursor = db.rawQuery("select * from person where name = ?",new String[]{name});
if(cursor.moveToFirst()){
int index = cursor.getColumnIndex("address");
String address = cursor.getString(index);
Log.i("chaoyongbing", "address="+address);
result = true;
}
cursor.close();
result = false;
db.close();
}
result = false;
return result;
}
public void delete(String name){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if(db.isOpen()){
db.execSQL("delete from person where name = ?",new Object[]{name});
db.close();
}
}
public void update(String name,String newname,String newaddress){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if(db.isOpen()){
db.execSQL("update person set name = ? , address = ? where name =?",new Object[]{newname,newaddress,name});
db.close();
}
}
public List<Person> getAllPerson(){
List<Person> persons = null;
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if(db.isOpen()){
persons = new ArrayList<Person>();
Cursor cursor = db.rawQuery("select * from person",null);
while(cursor.moveToNext()){
Person person = new Person();
int index1 = cursor.getColumnIndex("name");
int index2 = cursor.getColumnIndex("address");
String name = cursor.getString(index1);
String address = cursor.getString(index2);
person.setName(name);
person.setAddress(address);
persons.add(person);
}
cursor.close();
db.close();
}
return persons;
}
}
package com.example.db;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import damain.Person;
import dao.PersonDBDao;
import dao.PersonDao;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v4.widget.CursorAdapter;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity implements OnClickListener {
private ListView mListView;
private List<Person> persons;
private LayoutInflater inflater;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
inflater = (LayoutInflater) this
.getSystemService(LAYOUT_INFLATER_SERVICE);
setContentView(R.layout.main);
mListView = (ListView) this.findViewById(R.id.lv_all_person);
PersonDBDao person = new PersonDBDao(this);
persons = person.findAll();
// mListView.setAdapter(new MyAdapter());
List<Map<String, String>> date = new ArrayList<Map<String, String>>();
for (Person person1 : persons) {
HashMap<String, String> map = new HashMap<String, String>();
map.put("name", person1.getName());
map.put("address", person1.getAddress());
date.add(map);
}
/*
* mListView.setAdapter(new SimpleAdapter(getBaseContext(), date,
* R.layout.item, new String[] { "name", "address" }, new int[] {
* R.id.tv_name1, R.id.tv_name2 }));
*/
String[] personarray = new String[persons.size()];
for (int i = 0; i < persons.size(); i++) {
personarray[i] = persons.get(i).getName();
}
/*
* mListView.setAdapter(new ArrayAdapter<String>(getBaseContext(),
* R.layout.item,R.id.tv_name1, personarray));
*/
mListView
.setAdapter(new SimpleCursorAdapter(getBaseContext(),
R.layout.item, person.findAllbyCursor(), new String[] {
"name", "address" }, new int[] { R.id.tv_name1,
R.id.tv_name2 }));
mListView.setOnItemClickListener(new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view,
int position, long id) {
// TODO 自动生成的方法存根
String name = persons.get(position).getName();
Toast.makeText(getBaseContext(), name, Toast.LENGTH_SHORT)
.show();
}
});
}
public class MyAdapter extends BaseAdapter {
/***
* 返回当前listview有多少个条目
*/
@Override
public int getCount() {
// TODO 自动生成的方法存根
return persons.size();
}
/**
* 返回当前位置对应条目的object对象
*/
@Override
public Object getItem(int position) {
// TODO 自动生成的方法存根
return persons.get(position);
}
/***
* 返回当前位置条目的id
*/
@Override
public long getItemId(int position) {
// TODO 自动生成的方法存根
return position;
}
/***
* 返回每一个条目的具体内容
*/
@Override
public View getView(int position, View convertView, ViewGroup parent) {
// TODO 自动生成的方法存根
/*
* TextView tv = new TextView(MainActivity.this); Person person =
* persons.get(position);
* tv.setText(person.getName()+" | "+person.getAddress());
* //System.out.println("我被点用了"+position); return tv;
*/
View view = inflater.inflate(R.layout.item, null);
Person person = persons.get(position);
TextView tv_name = (TextView) view.findViewById(R.id.tv_name1);
TextView tv_address = (TextView) view.findViewById(R.id.tv_name2);
tv_name.setText("name:" + person.getName());
tv_address.setText("address:" + person.getAddress());
return view;
}
}
@Override
public void onClick(View v) {
// TODO 自动生成的方法存根
}
}PersonDBDao.java:package dao;
import java.util.ArrayList;
import java.util.List;
import com.example.db.DBCreate;
import com.example.db.MyDBHelper;
import damain.Person;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class PersonDBDao {
private Context context;
private MyDBHelper dbOpenHelper;
public PersonDBDao(Context context) {
this.context = context;
dbOpenHelper = new MyDBHelper(context);
}
/*
* 添加记录
*/
public void add(String name, String address) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if (db.isOpen()) {
ContentValues values = new ContentValues();
values.put("name", name);
values.put("address", address);
db.insert("person", null, values);
db.close();
}
}
/**
* 删除记录
*
* @param name
*/
public void delete(String name) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if (db.isOpen()) {
db.delete("person", "name=?", new String[] { name });
db.close();
}
}
/**
* 改记录
*
* @param newname
* @param newaddress
* @param name
*/
public void update(String newname, String newaddress, String name) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if (db.isOpen()) {
ContentValues values = new ContentValues();
values.put("name", newname);
values.put("address", newaddress);
db.update("person", values, "name=?", new String[] { name });
db.close();
}
}
/****
* 查找记录
*/
public boolean find(String name) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
// select name,address from person where name = ‘chao‘
// 排序
if (db.isOpen()) {
Cursor cursor = db.query("person", null, "name=?",
new String[] { name }, null, null, null);
if (cursor.moveToFirst()) {
cursor.close();
return true;
}
cursor.close();
db.close();
}
return false;
}
public List<Person> findAll() {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
List<Person> persons = null;
if (db.isOpen()) {
Cursor cursor = db.query("person", null, null, null, null, null,
null);
persons = new ArrayList<Person>();
while (cursor.moveToNext()) {
Person person = new Person();
String name = cursor.getString(cursor.getColumnIndex("name"));
String address = cursor.getString(cursor
.getColumnIndex("address"));
person.setName(name);
person.setAddress(address);
persons.add(person);
}
cursor.close();
db.close();
}
return persons;
}
public Cursor findAllbyCursor() {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
List<Person> persons = null;
if (db.isOpen()) {
/*
* Cursor cursor = db.query("person", null, null, null, null, null,
* null);
*/
Cursor cursor = db.rawQuery(
"select id as _id,name,address from person", null);
return cursor;
}
return null;
}
/***
* 银行转账模拟
*/
public void transaction() {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
if (db.isOpen()) {
try {
db.beginTransaction();
// chaoyongbing0 1000快
db.execSQL("update person set money=? where name = ?",
new Object[] { 1000, "chaoyongbing0" });
// chaoyongbing0 - 200
db.execSQL("update person set money=money-? where name = ?",
new Object[] { 200, "chaoyongbing0" });
// chaoyongbing1 0快
db.execSQL("update person set money=? where name = ?",
new Object[] { 0, "chaoyongbing1" });
// chaoyongbing1 +200
db.execSQL("update person set money=money+? where name = ?",
new Object[] { 200, "chaoyongbing1" });
// 必须要有下面的语句，否则会默认回滚
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
}
}Person.java:package damain;
public class Person {
String name;
String address;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}android SQLite数据库操作,布布扣,bubuko.com
原文：http://blog.csdn.net/yongbingchao/article/details/22505837