SQLite的增删改查

个人编程习惯，通常需要5个类：

1.实体类：Person.java

2.助手类：DBOpenHelper.java(继承SQLiteOpenHelper)

3.抽象类:SQLOperate.java(封装了对数据库的操作)

4.实现类:SQLOperateImpl.java(实现抽象类SQLOperate.java)

5.测试类：Test.java(继承AndroidTestCase)

 

1.实体类People.java

package com.domain;

public class People {
private  String name;
private int birthday;
private int stuid;
public int getStuid() {
    return stuid;
}
public void setStuid(int stuid) {
    this.stuid = stuid;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public int getBirthday() {
    return birthday;
}
public void setBirthday(int birthday) {
    this.birthday = birthday;
}
}

2.助手类

package com.example.sqlitedao;

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 DBOpenHelper extends SQLiteOpenHelper{
    private static  String DATABASENAME="grove.db";
    
    
    private static final int VERSION=1;
    private static final String TABLENAME="student";
    public static final String FLAG="MySQLiteOpenHelper";
    
Context context;
    public DBOpenHelper(Context context) {
        super(context, DATABASENAME, null, VERSION);
        this.context=context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql="create table "+TABLENAME+"(stuid integer primary key,name varchar(20) not null,birthday date not null)";
        db.execSQL(sql);
        Log.i(FLAG,"表格创建成功！");
        
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        
    }

}

3.抽象类

package com.example.sqlitedao;

import java.util.List;
import java.util.Map;

import com.domain.People;

/**
 * 
 * @author 增删改查
 *
 */

public interface SQLOperate {
    
public    void insert(People p);
public   void update(People p);
public   void delete(int stuid);
//public   void query(People p);
public  void query(List<People> list);

}

4.实现类

package com.example.sqlitedao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.domain.People;

public class SQLOperateImpl implements SQLOperate{
    private static final String TABLENAME="student";
    private SQLiteDatabase db=null;
    
    public SQLOperateImpl(SQLiteDatabase db) {
        this.db=db;
        
    }
    @Override
    public void insert(People p) {
        Object []args={p.getName(),p.getBirthday(),p.getStuid()};
        String sql="insert into "+TABLENAME+" (name,birthday,stuid) values(?,?,?)";
        this.db.execSQL(sql,args);
        this.db.close();
        
    }

    @Override
    public void update(People p) {
        Object []args={p.getName(),p.getBirthday(),p.getStuid()};
        String sql="update "+TABLENAME+" set name=?,birthday=? where stuid=?";
        this.db.execSQL(sql,args);
        this.db.close();
        
    }



    @Override
    public void query(List<People> list) {
        Cursor c=db.rawQuery("select * from student", null);
        
        while(c.moveToNext()){
//            Map<String,Object> map=new HashMap<String,Object>();
//            map.put("stuid",c.getInt(c.getColumnIndex("stuid")));
//            map.put("name",c.getString(c.getColumnIndex("name")));//根据列名获取内容
//            map.put("birthday",c.getString(c.getColumnIndex("birthday")));
            People p=new People();
            p.setStuid(c.getInt(c.getColumnIndex("stuid")));
            p.setName(c.getString(c.getColumnIndex("name")));
            p.setBirthday(c.getInt(c.getColumnIndex("birthday")));
            list.add(p);
            
        
    }
        
    }
    @Override
    public void delete(int stuid ) {
        Object []args={stuid};
        String sql="delete from "+TABLENAME+" where stuid=?";
        this.db.execSQL(sql,args);
        this.db.close();
        
    }


}

5.测试类

当需要创建或打开一个数据库并获得数据库对象时，首先根据指定的文件名创建一个辅助对象，然后调用该对象的getWritableDatabase 或 getReadableDatabase方法 获得SQLiteDatabase 对象。

SQLiteDatabase db = myHelper.getWritableDatabase(); 
SQLiteDatabase db = myHelper.getReadableDatabase();

一般情况下两者返回情况都是相同的，唯一的区别是：在数据库仅开放只读权限或磁盘已满时，getReadableDatabase只会返回一个只读的数据库对象。

package com.example.sqlitetest;

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

import com.domain.People;
import com.example.sqlitedao.DBOpenHelper;
import com.example.sqlitedao.SQLOperateImpl;

import android.os.Bundle;
import android.app.Activity;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

public class MainActivity extends Activity {
	
	SQLOperateImpl sqlOperateImpl=null;
	DBOpenHelper dbopenhelper=new DBOpenHelper(this);
private	 List< People> list=null;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		
	Button	query_btn=(Button) findViewById(R.id.query_btn);
	Button	insert_btn=(Button) findViewById(R.id.insert_btn);
		
		
		
		 
		
	//查询
		 query_btn.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				sqlOperateImpl=new SQLOperateImpl(dbopenhelper.getWritableDatabase());
				
				List<People> list = new ArrayList<People>();
				sqlOperateImpl.query(list);
				
				for(int i=0;i<list.size();i++){
				
					System.err.println(list.get(i).getName()+list.get(i).getBirthday()+list.get(i).getStuid());	
				}
				
				//Log.i("name", p.getName());
				
				
			}
		});
 //插入数据
		insert_btn.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				sqlOperateImpl=new SQLOperateImpl(dbopenhelper.getWritableDatabase());
				People p=new People();
				p.setName("wgf1");p.setBirthday(1992);p.setStuid(1);
				sqlOperateImpl.insert(p);
				Log.i("insert", "插入成功");
				
			}
		});
		
	}
	//删除
	public void	drop_btn(View v){
		
		try {
			sqlOperateImpl.delete(1);
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		
	}
	public void	update_btn(View v){
		People p=new People();
		p.setName("更新后的wgf1");p.setBirthday(1992);p.setStuid(1);
		sqlOperateImpl.update(p);
		
		//Log.i("name", p.getName());
		
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

}

　　6.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity" >

    <Button
        android:id="@+id/insert_btn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="插入"
        android:onClick="insert_btn" />
    <Button
        android:id="@+id/query_btn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询"
        android:onClick="query_btn" />
    <Button
        android:id="@+id/drop_btn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除"
        android:onClick="drop_btn" />
    <Button
        android:id="@+id/drop_btn"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="更新"
        android:onClick="update_btn" />

</LinearLayout>