SQLite数据库创建及增删改查
插入语句:insert into person (name,number) values(‘zhangsan’,20) 查询语句: (1)select * from person (2)select * from person wherename=’zhangsan’ 更新语句:update person set number='200' where name='zhangsan' 删除语句:delete from person where name='zhangsan'
使用数据库 一共要有四个类 (1)PersonSQLiteOpenHelper(数据库类) (2)PersonDB(操作数据库的类) (3)PersonDao(对数据库进行增删改查的操作工具类) (4)Person对象类 PersonSQLiteOpenHelper代码: package com.example.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class PersonSQLiteOpenHelper extends SQLiteOpenHelper { /* * 数据库的构造方法,用来定义数据库的名称,数据库查询的结果集,数据库的版本 * */ public PersonSQLiteOpenHelper(Context context) { super(context,"persondb",null,1); // TODO Auto-generated constructor stub } /* * 数据库第一次被创建调用的方法 * */ @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub //初始化数据库的表结构 db.execSQL("create table person (id integer primary key autoincrement,name varchar(20),number varchar(20))"); } @Override public void onUpgrade(SQLiteDatabase arg0,int arg1,int arg2) { // TODO Auto-generated method stub } }
PersonDB代码: package com.example.test; import java.util.List; import android.database.sqlite.SQLiteDatabase; import android.test.AndroidTestCase; import com.example.db.PersonSQLiteOpenHelper; import com.example.db.dao.PersonDao; import com.example.db.dao.domain.Person; public class TestPersonDB extends AndroidTestCase { public void testCreateDB() throws Exception{ PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext()); SQLiteDatabase db = helper.getWritableDatabase(); } public void testAdd() throws Exception{ PersonDao dao = new PersonDao(getContext()); dao.add("jim","0181"); dao.add("peter","0007"); } public void testfind() throws Exception{ PersonDao dao = new PersonDao(getContext()); boolean result = dao.find("jim"); } public void testDel() throws Exception{ PersonDao dao = new PersonDao(getContext()); dao.delete("jim"); } public void testUpdate() throws Exception{ PersonDao dao = new PersonDao(getContext()); dao.update("jim","9999"); } public void findAll() throws Exception{ PersonDao dao = new PersonDao(getContext()); List<Person> persons = dao.findAll(); for(Person per:persons){ System.out.println(per.toString()); } } }
PersonDao代码: package com.example.db.dao; //用于操作数据库的一个类 import java.util.ArrayList; import java.util.List; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import com.example.db.PersonSQLiteOpenHelper; import com.example.db.dao.domain.Person; public class PersonDao { private PersonSQLiteOpenHelper helper; //在构造函数中进行初始化 public PersonDao(Context context) { helper = new PersonSQLiteOpenHelper(context); } /* * 添加一条数据到数据库 * name,number * * */ public void add(String name,String number){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("insert into person (name,number) values (?,?)",new Object[]{"zhangsan",50}); db.close(); } //查询记录是否存在 //返回boolean public boolean find(String name){ SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.rawQuery("select * from person where name=?",new String[]{name}); boolean result = cursor.moveToNext(); cursor.close(); db.close(); return result; } //更新数据 public void update(String name,String newnumber){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("update person set number=? where name=?",new Object[]{name,newnumber}); db.close(); } //删除数据 public void delete(String name){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from person where name=?",new String[]{name}); db.close(); } //查询所有的数据 public List<Person> findAll(){ SQLiteDatabase db = helper.getWritableDatabase(); List<Person> persons = new ArrayList<Person>(); Cursor cursor = db.rawQuery("select * from person",null); //Cursor cursor = db.rawQuery("select id,name,number from person",null);//第二种写法 while(cursor.moveToNext()){ int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); String number = cursor.getString(cursor.getColumnIndex("number")); //创建一个对象,并初始化 Person per = new Person(id,number); //把对象添加进集合中 persons.add(per); } //关闭数据库的连接 db.close(); return persons; } }
附:PersonDao代码中的db.execSQL()直接执行SQL语句可能会引起某些不可预知的错误,并且大部分人不熟SQL语句,所以google工程师把增删改查的四个语句封装成了API,供直接使用,说以上面的红色部分的语句可改如下 public long add(String name,String number){ SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("insert into person (name,50}); ContentValues values = new ContentValues(); values.put("name","zhangsan"); values.put("number",50); long num = db.insert("person",values); db.close(); return num; } //查询记录是否存在 //返回boolean public boolean find(String name){ SQLiteDatabase db = helper.getWritableDatabase(); //Cursor cursor = db.rawQuery("select * from person where name=?",new String[]{name}); Cursor cursor = db.query("person","nume=?",new String[]{name},null); boolean result = cursor.moveToNext(); cursor.close(); db.close(); return result; } //更新数据 public int update(String name,String newnumber){ SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("update person set number=? where name=?",newnumber}); ContentValues values = new ContentValues(); values.put("number",newnumber); int number = db.update("person",values,"name=?",new String[]{name}); db.close(); return number; } //删除数据 public int delete(String name){ SQLiteDatabase db = helper.getWritableDatabase(); //db.execSQL("delete from person where name=?",new String[]{name}); int number = db.delete("person",new String[]{name}); db.close(); return number; } Person代码: package com.example.db.dao.domain; public class Person { private int id; private String name; private String number; public Person(){} public Person(int id,String name,String number) { super(); this.id = id; this.name = name; this.number = number; } 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 getNumber() { return number; } public void setNumber(String number) { this.number = number; } public String toString(){ return this.id+" "+this.name+" "+this.number; } } (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |