CREATETABLE person (personid integer primary key autoincrement, name varchar(20))
SQLite可以解析大部分标准SQL语句,如:
查询语句:select * from 表名where 条件子句group by 分组字句having ... order by 排序子句
如:select * from person
select * from person order by id desc
select name from person group by name having count(*)>1
分页SQL与mysql类似,下面SQL语句获取5条记录,跳过前面3条记录
select * from Account limit 5 offset 3 或者 select * from Account limit 3,5
插入语句:insert into 表名(字段列表) values(值列表)。如:insert into person(name, age) values(‘lz’,3)
更新语句:update 表名set 字段名=值where 条件子句。如:update person set name=‘lz‘where id=10
删除语句:delete from 表名where 条件子句。如:delete from person where id=10
package com.example.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.domain.Person;
public class PersonDao {
private PersonSQLiteOpenHelper helper;
public PersonDao(Context context){
helper=new PersonSQLiteOpenHelper(context);
}
/**
* 添加一条数据到数据库
* @param name 姓名
* @param number 电话
*/
public void add(String name,String number){
SQLiteDatabase db=helper.getWritableDatabase();
db.execSQL("insert into person(name,number) values(?,?)", new Object[]{name,number});
db.close();
}
/**
* 查询记录是否存在
* @param name 姓名
* @return true 存在 false 不存在
*/
public boolean find(String name){
SQLiteDatabase db=helper.getReadableDatabase();
Cursor cursor= db.rawQuery("select * from person where name=?", new String[]{name});
boolean result= cursor.moveToNext();
cursor.close();
db.close();
return result;
}
/**
* 修改一条记录
* @param name 要修改的人的名字
* @param number 新号码
*/
public void update(String name,String number){
SQLiteDatabase db=helper.getWritableDatabase();
db.execSQL("update person set number=? where name=?", new Object[]{number,name});
db.close();
}
/**
* 删除一条记录
* @param name 需要删除的姓名
*/
public void delete (String name){
SQLiteDatabase db=helper.getWritableDatabase();
db.execSQL("delete from person where name=?", new Object[]{name});
db.close();
}
/**
* 返回全部数据
* @return
*/
public List<Person> findAll(){
SQLiteDatabase db=helper.getReadableDatabase();
List<Person> persons=new ArrayList<Person>();
Cursor cursor= db.rawQuery("select * 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 p=new Person(id, name, number);
persons.add(p);
}
cursor.close();
db.close();
return persons;
}
}
/数据库/src/com/example/dao/PersonDao2.java
package com.example.dao;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.db.PersonSQLiteOpenHelper;
import com.example.domain.Person;
public class PersonDao2 {
private PersonSQLiteOpenHelper helper;
public PersonDao2(Context context){
helper=new PersonSQLiteOpenHelper(context);
}
/**
* 添加一条数据到数据库
* @param name 姓名
* @param number 电话
*/
public long add(String name,String number,int money){
SQLiteDatabase db=helper.getWritableDatabase();
// db.execSQL("insert into person(name,number) values(?,?)", new Object[]{name,number});
ContentValues values=new ContentValues();
values.put("name", name);
values.put("number", number);
values.put("account", money);
long id=db.insert("person", null, values);
db.close();
return id;
}
/**
* 查询记录是否存在
* @param name 姓名
* @return true 存在 false 不存在
*/
public boolean find(String name){
SQLiteDatabase db=helper.getReadableDatabase();
//Cursor cursor= db.rawQuery("select * from person where name=?", new String[]{name});
Cursor cursor=db.query("person", null, "name=?", new String[]{name}, null, null, null);
boolean result= cursor.moveToNext();
cursor.close();
db.close();
return result;
}
/**
* 修改一条记录
* @param name 要修改的人的名字
* @param number 新号码
*/
public int update(String name,String number){
SQLiteDatabase db=helper.getWritableDatabase();
//db.execSQL("update person set number=? where name=?", new Object[]{number,name});
ContentValues values=new ContentValues();
values.put("number", number);
int rows= db.update("person", values, "name=?",new String[]{name});
db.close();
return rows;
}
/**
* 删除一条记录
* @param name 需要删除的姓名
*/
public int delete (String name){
SQLiteDatabase db=helper.getWritableDatabase();
//db.execSQL("delete from person where name=?", new Object[]{name});
int rows= db.delete("person", "name=?", new String[]{name});
db.close();
return rows;
}
/**
* 返回全部数据
* @return
*/
public List<Person> findAll(){
SQLiteDatabase db=helper.getReadableDatabase();
List<Person> persons=new ArrayList<Person>();
//Cursor cursor= db.rawQuery("select * from person", null);
Cursor cursor=db.query("person", new String[]{"id","name","number"}, null, null, null, null, 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 p=new Person(id, name, number);
persons.add(p);
}
cursor.close();
db.close();
return persons;
}
}
/数据库/src/com/example/db/MainActivity.java
package com.example.db;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
public class MainActivity extends ActionBarActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
PersonSQLiteOpenHelper helper=new PersonSQLiteOpenHelper(this);
helper.getWritableDatabase();
}
}
/数据库/src/com/example/db/PersonSQLiteOpenHelper.java
package com.example.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {
private static final String TAG = "PersonSQLiteOpenHelper";
/**
* 数据库的构造方法 用来定义数据库的名称 数据库查询的结果集 数据库的版本
* @param context
*/
public PersonSQLiteOpenHelper(Context context) {
super(context, "person.db", null, 2);
}
/**
* 数据库第一次被创建的时候调用的方法
* @param db 被创建的数据库
*/
@Override
public void onCreate(SQLiteDatabase db) {
//初始化数据库的表结构
db.execSQL("create table person (id integer primary key autoincrement, name varchar(20),number varchar(20))");
}
/**
* 当数据库的版本号发生变化的时候(增加的时候)调用。
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i(TAG, "数据库的版本变化了!");
db.execSQL("alter table person add account varchar(20)");
}
}
/数据库/src/com/example/db/test/TestPersonDB.java
package com.example.db.test;
import java.util.List;
import com.example.dao.PersonDao;
import com.example.dao.PersonDao2;
import com.example.db.PersonSQLiteOpenHelper;
import com.example.domain.Person;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
public class TestPersonDB extends AndroidTestCase {
public void testCreateDB() throws Exception{
PersonSQLiteOpenHelper helper=new PersonSQLiteOpenHelper(getContext());
SQLiteDatabase db=helper.getWritableDatabase();
db.close();
}
public void testAdd() throws Exception{
PersonDao2 dao=new PersonDao2(getContext());
dao.add("lz", "187",1000);
dao.add("ly", "186",2000);
}
public void testFind() throws Exception{
PersonDao2 dao=new PersonDao2(getContext());
boolean result=dao.find("lz");
assertEquals(true, result);
}
public void testUpdate() throws Exception{
PersonDao2 dao=new PersonDao2(getContext());
dao.update("lz", "110");
}
public void testDelte() throws Exception{
PersonDao2 dao=new PersonDao2(getContext());
dao.delete("lz");
}
public void testFindAll() throws Exception{
PersonDao2 dao=new PersonDao2(getContext());
List<Person> persons= dao.findAll();
for(Person p:persons){
System.out.println(p.toString());
}
}
/**
* 数据库事务的操作
* @throws Exception
*/
public void testTransaction()throws Exception{
PersonSQLiteOpenHelper helper=new PersonSQLiteOpenHelper(getContext());
SQLiteDatabase db=helper.getWritableDatabase();
//开启数据库事务
db.beginTransaction();
try {
db.execSQL("update person set account= account-100 where name=? ",new Object[]{"lz"});
db.execSQL("update person set account= account+100 where name=? ",new Object[]{"ly"});
//标记数据库事务执行成功
db.setTransactionSuccessful();
} finally {
db.endTransaction();
db.close();
}
}
}
/数据库/src/com/example/domain/Person.java
package com.example.domain;
public class Person {
private int id;
private String name;
private String number;
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", number=" + number
+ "]";
}
public Person() {
}
public Person(int id, String name, String number) {
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;
}
}