android数据库sqlite使用实例

最新推荐文章于 2021-05-27 12:02:08 发布

转载最新推荐文章于 2021-05-27 12:02:08 发布 · 645 阅读

资源操作同时被 2 个专栏收录

79 篇文章

订阅专栏

课程

28 篇文章

订阅专栏

本文介绍了使用Android Studio创建应用并实现与SQLite数据库交互的方法，包括数据库表创建、数据插入、查询、更新和删除操作。

效果图：

javaBean:

public class UserModel {
	private int id;
	private String name;
	private int age;

	public UserModel() {
		super();
	}

	public UserModel(String name, int age) {
		super();
		this.name = name;
		this.age = age;
	}

	public UserModel(int id, String name, int age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}

	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 int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

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

}

数据库帮助类：

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

@SuppressLint("NewApi")
public class MySqliteOpenHelper extends SQLiteOpenHelper {

	public MySqliteOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	/**
	 * 数据库第一次创建的时候调用该方法，并且呢onCreate这个方法不会被重复调用
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		// 创建student表
		String sql = "create table t_student(id INTEGER primary key autoincrement,name text not null,age INTEGER not null)";
		db.execSQL(sql);

		// 给student表插入数据
		sql = "insert into t_student(name,age) values('" + "班长同学" + "',16)";
		db.execSQL(sql);

		sql = "insert into t_student(name,age) values('" + "同学越来越厉害，工作越来越顺利..."
				+ "',16)";
		db.execSQL(sql);
	}

	/**
	 * 数据库升级（其实数据库升级是根据app的版本号来更新）
	 * 更新操作－－－新加入一张表、更新字段名称、表新增字段、删除表字段等等......
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql = "create table t_user(id INTEGER primary key autoincrement,name text not null,age INTEGER not null)";
		db.execSQL(sql);
	}

	/**
	 * 在开发过程中有可能数据库升级的时候出了问题， 我们希望数据库能够还原到之前的一个版本
	 */
	@Override
	public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		super.onDowngrade(db, oldVersion, newVersion);
	}

}

activity主布局：

activity_main.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:orientation="vertical"
    tools:context="${relativePackage}.${activityClass}" >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="60dip"
        android:orientation="horizontal" 
        android:gravity="center">

        <Button
            android:id="@+id/bt_add"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="新增" />

        <Button
            android:id="@+id/bt_delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="删除" />

        <Button
            android:id="@+id/bt_query"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="查询" />

        <Button
            android:id="@+id/bt_update"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="修改" />
    </LinearLayout>

    <ListView
        android:id="@+id/lv_show"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:cacheColorHint="@android:color/transparent" />

</LinearLayout>

listview item布局：

item_layout.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="40dip"
    android:orientation="horizontal" >

    <TextView
        android:id="@+id/tv_id"
        android:layout_width="0dip"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:gravity="center_vertical" />

    <TextView
        android:id="@+id/tv_name"
        android:layout_width="0dip"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:gravity="center_vertical" />

    <TextView
        android:id="@+id/tv_age"
        android:layout_width="0dip"
        android:layout_height="match_parent"
        android:layout_weight="1"
        android:gravity="center_vertical" />

</LinearLayout>

activity java文件：

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;

public class MainActivity extends Activity implements OnClickListener {

	private static final String DB_NAME = "User_DB";

	private List<UserModel> models = new ArrayList<UserModel>();
	private MyAdapter myAdapter;

	private SQLiteDatabase sqLiteDatabase;

	private MySqliteOpenHelper openHelper;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);

		findViewById(R.id.bt_add).setOnClickListener(this);
		findViewById(R.id.bt_delete).setOnClickListener(this);
		findViewById(R.id.bt_query).setOnClickListener(this);
		findViewById(R.id.bt_update).setOnClickListener(this);
		ListView lv_show = (ListView) findViewById(R.id.lv_show);
		myAdapter = new MyAdapter();
		lv_show.setAdapter(myAdapter);

		createTable();
	}

	private void createTable() {
		// 第一种方式： 1、打开或者创建数据库 有就打开 没有就创建
		// sqLiteDatabase = openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE,
		// null);
		//
		// // 表重复创建－－－－
		// File file = new File("/data/data/" + getPackageName() + "/databases/"
		// + DB_NAME);
		// if (!file.exists()) {
		// // 2、创建表
		// // sqlite数据字段类型（integer，real浮点型，text文本类型，varchar，long，blod二进制数据等等）
		// // sqlite数据字段类型其实是一种弱类型（当你查询的时候，一般我们使用integer、real、text类型，
		// // 但是我们可以直接使用text类型来查询）
		// String sql =
		// "create table t_user(id INTEGER primary key autoincrement,name text not null,age INTEGER not null)";
		// sqLiteDatabase.execSQL(sql);
		// }

		openHelper = new MySqliteOpenHelper(this, DB_NAME, null, 2);
		// 其实这两个方法没有太大的区别
		// sqLiteDatabase = openHelper.getReadableDatabase();
		sqLiteDatabase = openHelper.getWritableDatabase();
	}

	@Override
	public void onClick(View v) {
		switch (v.getId()) {
		case R.id.bt_add:
			addUser();
			break;

		case R.id.bt_delete:
			deleteUser();
			break;
		case R.id.bt_query:
			queryUser();
			break;
		case R.id.bt_update:
			updateUser();
			break;

		default:
			break;
		}
	}

	private void updateUser() {
		String sql = "update t_student set name = '数据更新' where id = 2";
		sqLiteDatabase.execSQL(sql);
		queryUser();
	}

	private void deleteUser() {
		// String sql = "delete from t_user where id = 2";
		// sqLiteDatabase.execSQL(sql);

		String sql = "delete from t_student where id = ?";
		sqLiteDatabase.execSQL(sql, new String[] { String.valueOf(3) });
		queryUser();
	}

	private void addUser() {
		UserModel userModel = new UserModel("大家永远年轻", new Random().nextInt(30));
		// String sql = "insert into t_user(name,age) values('"
		// + userModel.getName() + "'," + userModel.getAge() + ")";
		// sqLiteDatabase.execSQL(sql);

		ContentValues values = new ContentValues();
		values.put("name", userModel.getName());
		values.put("age", userModel.getAge()); 
		//这样的好处在于我们再也不用写sql语句
		sqLiteDatabase.insert("t_student",// table, 表名
				"id",// nullColumnHack, 不为空的字段
				values);// values);//插入的值

		queryUser();
	}

	private void queryUser() {
		models.clear();

		// String sql = "select * from t_user";
		// cursor 游标 相当于ResultSet
		// Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
		Cursor cursor = sqLiteDatabase.query("t_student",// table, 表名
				null,// columns,查询的字段数组
				null,// selection, 查询条件字段数组
				null,// selectionArgs, 查询条件字段的对应的值数组
				null,// groupBy,分组
				null,// having,分组后的条件
				null);// orderBy);排序

		UserModel userModel = null;
		// 注意：游标默认是放在第0条数据上面的，需要往下移动游标
		while (cursor.moveToNext()) {
			// 尽量不用使用下标表示字段
			// cursor.getInt(0);
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int age = cursor.getInt(cursor.getColumnIndex("age"));
			userModel = new UserModel(id, name, age);
			models.add(userModel);
		}

		myAdapter.notifyDataSetChanged();
	}

	public class MyAdapter extends BaseAdapter {

		@Override
		public int getCount() {
			return models.size();
		}

		@Override
		public Object getItem(int position) {
			return models.get(position);
		}

		@Override
		public long getItemId(int position) {
			return position;
		}

		@Override
		public View getView(int position, View convertView, ViewGroup parent) {
			ViewHolder holder = null;
			if (convertView == null) {
				convertView = getLayoutInflater().inflate(R.layout.item_layout,
						parent, false);
				holder = new ViewHolder();
				holder.tv_id = (TextView) convertView.findViewById(R.id.tv_id);
				holder.tv_name = (TextView) convertView
						.findViewById(R.id.tv_name);
				holder.tv_age = (TextView) convertView
						.findViewById(R.id.tv_age);
				convertView.setTag(holder);
			} else {
				holder = (ViewHolder) convertView.getTag();
			}
			UserModel userModel = models.get(position);
			holder.tv_id.setText(String.valueOf(userModel.getId()));
			holder.tv_name.setText(userModel.getName());
			holder.tv_age.setText(String.valueOf(userModel.getAge()));

			return convertView;
		}

		class ViewHolder {
			TextView tv_id;
			TextView tv_name;
			TextView tv_age;
		}

	}

}

整理自教程