Androidsqllite数据库

最新推荐文章于 2024-03-30 21:15:42 发布

lvzhanshen

最新推荐文章于 2024-03-30 21:15:42 发布

阅读量383

点赞数 1

分类专栏： Android开发

Android开发专栏收录该内容

40 篇文章 0 订阅

订阅专栏

整体创建数据库的步骤：创建一个数据库(定义好数据库的名称)

PersonSQLiteOpenHelper类：

package com.example.shujuku;

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, 3);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// 创建一个Person表,初始化数据库的表结构
		String sql = "create table person (id integer primary key autoincrement,name varchar(20),number varchar(20))";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// 更新数据库，数据库版本号变化，数据库发生变化
		Log.i(TAG, "数据库版本变化了。。。。");
		db.execSQL("alter table person add account varchar(20)");
	}

}

返回参数的类Person：

package com.example.shujuku.domin;

public class Person {

	private int id;
	private String name;
	private String number;

	public Person() {

	}

	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + ", number=" + number
				+ "]";
	}

	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;
	}

}

增删查改的方法1：

package com.example.shujuku.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.shujuku.PersonSQLiteOpenHelper;
import com.example.shujuku.domin.Person;

public class PersonDao {

	private PersonSQLiteOpenHelper helper;

	// 在构造方法里面完成helper的初始化
	public PersonDao(Context context) {
		helper = new PersonSQLiteOpenHelper(context);
	}

	/**
	 * 添加一条记录到数据库
	 */
	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
	 */
	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
	 * @return
	 */
	public void update(String name, String newnumber) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("update person set number = ? where name = ?", new Object[] {
				newnumber, name });
		db.close();
	}

	/**
	 * 删除一条记录
	 */
	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 name,id,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 p = new Person(id, name, number);
			persons.add(p);
		}
		cursor.close();
		db.close();
		return persons;
	}
}

增删查改的方法2：

package com.example.shujuku.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.shujuku.PersonSQLiteOpenHelper;
import com.example.shujuku.domin.Person;

public class PersonDao2 {

	private PersonSQLiteOpenHelper helper;

	// 在构造方法里面完成helper的初始化
	public PersonDao2(Context context) {
		helper = new PersonSQLiteOpenHelper(context);
	}

	/**
	 * 添加一条记录到数据库
	 */
	public long add(String name, String number) {
		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);
		long id = db.insert("person", null, values);
		db.close();
		return id;
	}

	/**
	 * 查询记录是否存在
	 * 
	 * @param name
	 * @return
	 */
	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
	 * @return
	 */
	public int update(String name, String newnumber) {
		SQLiteDatabase db = helper.getWritableDatabase();
		// db.execSQL("update person set number = ? where name = ?", new
		// Object[] {
		// newnumber, name });
		ContentValues values = new ContentValues();
		values.put("number", newnumber);
		int number = db.update("person", values, "name = ?",
				new String[] { newnumber });
		db.close();
		return number;
	}

	/**
	 * 删除一条记录
	 */
	public int delete(String name) {
		SQLiteDatabase db = helper.getWritableDatabase();
		// db.execSQL("delete from person where name = ?", new Object[] { name
		// });
		int number = db.delete("person", "name = ?", new String[] { name });
		db.close();
		return number;
	}

	/**
	 * 返回全部的信息
	 * 
	 * @return
	 */
	public List<Person> findAll() {
		SQLiteDatabase db = helper.getReadableDatabase();
		List<Person> persons = new ArrayList<Person>();
		// Cursor cursor = db.rawQuery("select name,id,number from person",
		// null);
		Cursor cursor = db.query("person", new String[] { "name", "id",
				"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;
	}
}

测试方法：添加

<uses-library android:name="android.test.runner"到application之后

到AndroidManifest中

package com.example.shujuku.test;

import java.util.List;

import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

import com.example.shujuku.PersonSQLiteOpenHelper;
import com.example.shujuku.dao.PersonDao2;
import com.example.shujuku.domin.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 {
		PersonDao2 dao = new PersonDao2(getContext());
		// dao.add("wangwu", "123");
		long number = 88590;
		for (int i = 0; i < 50; i++) {
			dao.add("wangwu" + i, Long.toString(number + i));
		}
	}

	public void testFind() throws Exception {
		PersonDao2 dao = new PersonDao2(getContext());
		boolean result = dao.find("wangwu");
		assertEquals(true, result);
	}

	public void testUpdate() throws Exception {
		PersonDao2 dao = new PersonDao2(getContext());
		dao.update("wangwu", "321");
	}

	public void testDelete() throws Exception {
		PersonDao2 dao = new PersonDao2(getContext());
		dao.delete("wangwu");
	}

	public void findAll() throws Exception {
		PersonDao2 dao = new PersonDao2(getContext());
		List<Person> persons = dao.findAll();
		for (Person p : persons) {
			System.out.println(p.toString());
		}
	}
	public void testTransfer() throws Exception{
		
	}
}

布局文件：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/ll_root"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    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" >

    <ListView
        android:id="@+id/lv"
        android:layout_width="match_parent"
        android:layout_height="match_parent" >
    </ListView>

</LinearLayout>

主类文件：

package com.example.shujuku;

import java.util.List;

import android.app.Activity;
import android.graphics.Color;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.TextView;

import com.example.shujuku.dao.PersonDao;
import com.example.shujuku.domin.Person;

public class MainActivity extends Activity {

	private ListView lv;
	private List<Person> persons;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
//		LinearLayout ll_root = (LinearLayout) findViewById(R.id.ll_root);
		PersonDao dao = new PersonDao(this);
		persons = dao.findAll();// 得到所有数据的集合
		lv = (ListView)this.findViewById(R.id.lv);
		lv.setAdapter(new MyAdapter());
	}

	private class MyAdapter extends BaseAdapter {
		private static final String TAG = "MyAdapter";

		// 控制listview里面有多少个条目
		@Override
		public int getCount() {
			// TODO Auto-generated method stub
			return persons.size();
		}

		@Override
		public Object getItem(int arg0) {
			// TODO Auto-generated method stub
			return null;
		}

		@Override
		public long getItemId(int arg0) {
			// TODO Auto-generated method stub
			return 0;
		}

		@Override
		public View getView(int position, View convertView, ViewGroup parent) {
			Log.i(TAG, "位置"+position);
			TextView tv = new TextView(getApplicationContext());
			tv.setTextSize(20);
			tv.setTextColor(Color.BLACK);
			// 得到某個位置的person對象
			Person person = persons.get(position);
			tv.setText(person.toString());
			return tv;
		}

	}
}