安卓实训第十二天（补充部分）---Sqlite数据库的创建和改变，以及使用SQLiteDataBase完成CRUD操作

本文详细介绍了一个基于SQLite的数据库创建过程，包括如何通过Java代码实现数据库的创建、表结构的更新，以及基本的增删改查操作。此外还提供了完整的代码示例和测试案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、数据库的创建

DatabaseHelper.java

[java]

package com.example.android_sqlite.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
/**
*
* @param context
* :上下文对象
* @param name
* ：数据库名称
* @param factory
* ：cursor工厂对象，可以设置null.采用默认的CursorFactory对象
* @param version
* ：数据库版本号
*/
public DatabaseHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
}
/**
* 当数据库第一次创建的时候会调用这个方法
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table users(userid integer primary key autoincrement,username varchar(20))");
}
/**
* 当数据库的版本升级的时候，自动调用这个方法（根据数据库的版本号）
*/
@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
}
}

package com.example.android_sqlite.database;

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

public class DatabaseHelper extends SQLiteOpenHelper {

	/**
	 * 
	 * @param context
	 *            :上下文对象
	 * @param name
	 *            ：数据库名称
	 * @param factory
	 *            ：cursor工厂对象，可以设置null.采用默认的CursorFactory对象
	 * @param version
	 *            ：数据库版本号
	 */
	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	/**
	 * 当数据库第一次创建的时候会调用这个方法
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table users(userid integer primary key autoincrement,username varchar(20))");
	}

	/**
	 * 当数据库的版本升级的时候，自动调用这个方法（根据数据库的版本号）
	 */
	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {

	}

}

测试类

[java]

package com.example.android_sqlite.test;
import com.example.android_sqlite.database.DatabaseHelper;
import android.test.AndroidTestCase;
public class DatabaseTese extends AndroidTestCase {
public void createDatabase() {
DatabaseHelper dh = new DatabaseHelper(getContext(), "csdn.db", null, 1);
dh.getWritableDatabase();
}
}

package com.example.android_sqlite.test;

import com.example.android_sqlite.database.DatabaseHelper;

import android.test.AndroidTestCase;

public class DatabaseTese extends AndroidTestCase {
	public void createDatabase() {
		DatabaseHelper dh = new DatabaseHelper(getContext(), "csdn.db", null, 1);
		dh.getWritableDatabase();
	}
}

输出结果：

二、用CMD控制台命令来完成对数据库的操作（复制来源于赵雅智的博客，因为她整理的很好）：

acb shell

挂到linux空间

打开data空间

打开数据库

查找表

查看创建表语句

插入表数据

查看表数据

修改id为3的名字为Devy

删除id=2的数据

查询前3条记录

查询含有Tom的数据

查询含有Tom的个数

三、修改表结构：

1.更改数据库版本号（每次更新都一定要进行版本的升级）

2.更新语句一条一条写，不能并列写

例如：

[java]

public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
db.execSQL("alter table users add userage integer;<span style="font-family: Arial, Helvetica, sans-serif;">alter table users add usersalary doouble</span>");
}

public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
	db.execSQL("alter table users add userage integer;<span style="font-family: Arial, Helvetica, sans-serif;">alter table users add usersalary doouble</span>");
}

上面语句只能执行“;”前的语句，后面的无法插入。

所以要将代码改为下式代码，才能两条语句都执行。

[java]

public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
db.execSQL("alter table users add userage integer");
db.execSQL("alter table users add usersalary doouble");
}

public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
	db.execSQL("alter table users add userage integer");
	db.execSQL("alter table users add usersalary doouble");
}

具体代码：

创建数据库代码：DatabaseHelper

[java]

package com.example.android_sqlite.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
// 数据库名称
private static final String DATABASENAME = "csdn.db";
// 数据库更新版本号
private static int databaseBersion = 2;
/**
*
* @param context
* :上下文对象
* @param name
* ：数据库名称
* @param factory
* ：cursor工厂对象，可以设置null.采用默认的CursorFactory对象
* @param version
* ：数据库版本号
*/
public DatabaseHelper(Context context) {
super(context, DATABASENAME, null, databaseBersion);
}
/**
* 当数据库第一次创建的时候会调用这个方法
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table users(userid integer primary key autoincrement,username varchar(20))");
}
/**
* 当数据库的版本升级的时候，自动调用这个方法（根据数据库的版本号）
*/
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
db.execSQL("alter table users add userage integer");
db.execSQL("alter table users add usersalary doouble");
System.out.println("-----------------升级了-----------------");
}
}

package com.example.android_sqlite.database;

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

public class DatabaseHelper extends SQLiteOpenHelper {
	// 数据库名称
	private static final String DATABASENAME = "csdn.db";
	// 数据库更新版本号
	private static int databaseBersion = 2;

	/**
	 * 
	 * @param context
	 *            :上下文对象
	 * @param name
	 *            ：数据库名称
	 * @param factory
	 *            ：cursor工厂对象，可以设置null.采用默认的CursorFactory对象
	 * @param version
	 *            ：数据库版本号
	 */
	public DatabaseHelper(Context context) {
		super(context, DATABASENAME, null, databaseBersion);
	}

	/**
	 * 当数据库第一次创建的时候会调用这个方法
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table users(userid integer primary key autoincrement,username varchar(20))");
	}

	/**
	 * 当数据库的版本升级的时候，自动调用这个方法（根据数据库的版本号）
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		db.execSQL("alter table users add userage integer");
		db.execSQL("alter table users add usersalary doouble");
		System.out.println("-----------------升级了-----------------");

	}

}

测试类：DatabaseTese.java

[java]

package com.example.android_sqlite.test;
import com.example.android_sqlite.database.DatabaseHelper;
import android.test.AndroidTestCase;
public class DatabaseTese extends AndroidTestCase {
public void createDatabase() {
DatabaseHelper dh = new DatabaseHelper(getContext());
dh.getWritableDatabase();
}
}

package com.example.android_sqlite.test;

import com.example.android_sqlite.database.DatabaseHelper;

import android.test.AndroidTestCase;

public class DatabaseTese extends AndroidTestCase {
	public void createDatabase() {
		DatabaseHelper dh = new DatabaseHelper(getContext());
		dh.getWritableDatabase();
	}
}

运行结果如下：

控制台输出：

cmd命令行输出：

更多 0

四、使用SQLiteDatabase操作SQLite数据库及事务

MainActivity.java

[java] view plain copy print ?

package com.example.android_sqlite;
import android.app.Activity;
import android.os.Bundle;
import com.example.android_sqlite.dao.impl.UserDaoImpls;
import com.example.android_sqlite.database.DatabaseHelper;
public class MainActivity extends Activity {
private UserDaoImpls userDaoImpls;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
DatabaseHelper dh = new DatabaseHelper(this);
userDaoImpls = new UserDaoImpls(dh);
}
}

package com.example.android_sqlite;

import android.app.Activity;
import android.os.Bundle;

import com.example.android_sqlite.dao.impl.UserDaoImpls;
import com.example.android_sqlite.database.DatabaseHelper;

public class MainActivity extends Activity {
	private UserDaoImpls userDaoImpls;

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

		// 创建数据库
		DatabaseHelper dh = new DatabaseHelper(this);
		userDaoImpls = new UserDaoImpls(dh);
	}

}

Users.java

[java] view plain copy print ?

package com.example.android_sqlite.domain;
import java.io.Serializable;
public class Users implements Serializable{
private static final long serialVersionUID = 1L;
private Integer userId;
private String userName;
private Integer userAge;
private Double userSalary;
public Users() {
super();
}
public Users(Integer userId, String userName, Integer userAge,
Double userSalary) {
super();
this.userId = userId;
this.userName = userName;
this.userAge = userAge;
this.userSalary = userSalary;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Integer getUserAge() {
return userAge;
}
public void setUserAge(Integer userAge) {
this.userAge = userAge;
}
public Double getUserSalary() {
return userSalary;
}
public void setUserSalary(Double userSalary) {
this.userSalary = userSalary;
}
@Override
public String toString() {
return "Users [userId=" + userId + ", userName=" + userName
+ ", userAge=" + userAge + ", userSalary=" + userSalary + "]";
}
}

package com.example.android_sqlite.domain;

import java.io.Serializable;

public class Users implements Serializable{
	private static final long serialVersionUID = 1L;
	private Integer userId;
	private String userName;
	private Integer userAge;
	private Double userSalary;

	public Users() {
		super();
	}
	public Users(Integer userId, String userName, Integer userAge,
			Double userSalary) {
		super();
		this.userId = userId;
		this.userName = userName;
		this.userAge = userAge;
		this.userSalary = userSalary;
	}

	public Integer getUserId() {
		return userId;
	}

	public void setUserId(Integer userId) {
		this.userId = userId;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public Integer getUserAge() {
		return userAge;
	}

	public void setUserAge(Integer userAge) {
		this.userAge = userAge;
	}

	public Double getUserSalary() {
		return userSalary;
	}

	public void setUserSalary(Double userSalary) {
		this.userSalary = userSalary;
	}

	@Override
	public String toString() {
		return "Users [userId=" + userId + ", userName=" + userName
				+ ", userAge=" + userAge + ", userSalary=" + userSalary + "]";
	}
	
}

BaseDao.java

[java] view plain copy print ?

package com.example.android_sqlite.dao;
import java.util.List;
public interface BaseDao<T, PK> {
/**
* 插入实体对象
*
* @param entity
* @return
*/
public boolean insert(T entity);
/**
* 更新实体对象
*
* @param entity
* @return
*/
public boolean update(T entity);
/**
* 删除实体对象
*
* @param entity
* @return
*/
public boolean delete(T entity);
/**
* 根据id删除对象
*
* @param id
* @return
*/
public boolean deleteByid(PK id);
/**
* 根据id查找对象
*
* @param id
* @return
*/
public T findByid(PK id);
/**
* 查找所有
*
* @return
*/
public List<T> findAll();
}

package com.example.android_sqlite.dao;

import java.util.List;

public interface BaseDao<T, PK> {
	/**
	 * 插入实体对象
	 * 
	 * @param entity
	 * @return
	 */
	public boolean insert(T entity);

	/**
	 * 更新实体对象
	 * 
	 * @param entity
	 * @return
	 */
	public boolean update(T entity);

	/**
	 * 删除实体对象
	 * 
	 * @param entity
	 * @return
	 */
	public boolean delete(T entity);

	/**
	 * 根据id删除对象
	 * 
	 * @param id
	 * @return
	 */
	public boolean deleteByid(PK id);

	/**
	 * 根据id查找对象
	 * 
	 * @param id
	 * @return
	 */
	public T findByid(PK id);

	/**
	 * 查找所有
	 * 
	 * @return
	 */
	public List<T> findAll();

}

UserDao.java

[java] view plain copy print ?

package com.example.android_sqlite.dao;
import com.example.android_sqlite.domain.Users;
public interface UserDao extends BaseDao<Users, Integer>{
}

package com.example.android_sqlite.dao;

import com.example.android_sqlite.domain.Users;


public interface UserDao extends BaseDao<Users, Integer>{

}

UserDaoImple.java

[java] view plain copy print ?

package com.example.android_sqlite.dao.impl;
import java.util.ArrayList;
import java.util.List;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.android_sqlite.dao.UserDao;
import com.example.android_sqlite.database.DatabaseHelper;
import com.example.android_sqlite.domain.Users;
public class UserDaoImpls implements UserDao {
// 依赖对象
private DatabaseHelper dh;
// 通过数据库的构造器实例化
public UserDaoImpls(DatabaseHelper databaseHelper) {
this.dh = databaseHelper;
}
@Override
public boolean insert(Users entity) {
// 得到数据库操作对象
try {
SQLiteDatabase db = dh.getWritableDatabase();
/*
* 方法一
* db.execSQL("insert into users(username,userage,usersalary) values('"
* + entity.getUserName() + "'," + entity.getUserAge() + "," +
* entity.getUserSalary() + ")");
*/
// 方法二
db.execSQL(
"insert into users(username,userage,usersalary) values(?,?,?)",
new Object[] { entity.getUserName(), entity.getUserAge(),
entity.getUserSalary() });
db.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
@Override
public boolean update(Users entity) {
try {
SQLiteDatabase db = dh.getWritableDatabase();
db.execSQL(
"update users set username=?,userage=?,usersalary=? where userid=?",
new Object[] { entity.getUserName(), entity.getUserAge(),
entity.getUserSalary(), entity.getUserId() });
db.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
@Override
public boolean delete(Users entity) {
return deleteByid(entity.getUserId());
}
@Override
public boolean deleteByid(Integer id) {
try {
SQLiteDatabase db = dh.getWritableDatabase();
db.execSQL("delete from users where userid=?", new Object[] { id });
db.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
@Override
public Users findByid(Integer id) {
Users entity = null;
SQLiteDatabase db = dh.getWritableDatabase();
Cursor c = db
.rawQuery(
"select userid,username,userage,usersalary from users where userid=?",
new String[] { id + "" });
if (c.moveToNext()) {
entity = new Users();
entity.setUserId(c.getInt(c.getColumnIndex("userid")));
entity.setUserName(c.getString(c.getColumnIndex("userid")));
entity.setUserAge(c.getInt(c.getColumnIndex("userage")));
entity.setUserSalary(c.getDouble(c.getColumnIndex("usersalary")));
}
return entity;
}
@Override
public List<Users> findAll() {
List<Users> entities = new ArrayList<Users>();
SQLiteDatabase db = dh.getWritableDatabase();
Cursor c = db
.rawQuery(
"select userid,username,userage,usersalary from users",
null);
while (c.moveToNext()) {
Users entity = new Users();
entity.setUserId(c.getInt(c.getColumnIndex("userid")));
entity.setUserName(c.getString(c.getColumnIndex("userid")));
entity.setUserAge(c.getInt(c.getColumnIndex("userage")));
entity.setUserSalary(c.getDouble(c.getColumnIndex("usersalary")));
entities.add(entity);
}
return entities;
}
}

package com.example.android_sqlite.dao.impl;

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

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

import com.example.android_sqlite.dao.UserDao;
import com.example.android_sqlite.database.DatabaseHelper;
import com.example.android_sqlite.domain.Users;

public class UserDaoImpls implements UserDao {
	// 依赖对象
	private DatabaseHelper dh;

	// 通过数据库的构造器实例化
	public UserDaoImpls(DatabaseHelper databaseHelper) {
		this.dh = databaseHelper;
	}

	@Override
	public boolean insert(Users entity) {
		// 得到数据库操作对象
		try {
			SQLiteDatabase db = dh.getWritableDatabase();
			/*
			 * 方法一
			 * db.execSQL("insert into users(username,userage,usersalary) values('"
			 * + entity.getUserName() + "'," + entity.getUserAge() + "," +
			 * entity.getUserSalary() + ")");
			 */

			// 方法二
			db.execSQL(
					"insert into users(username,userage,usersalary) values(?,?,?)",
					new Object[] { entity.getUserName(), entity.getUserAge(),
							entity.getUserSalary() });
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean update(Users entity) {
		try {
			SQLiteDatabase db = dh.getWritableDatabase();

			db.execSQL(
					"update users set username=?,userage=?,usersalary=? where userid=?",
					new Object[] { entity.getUserName(), entity.getUserAge(),
							entity.getUserSalary(), entity.getUserId() });
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean delete(Users entity) {

		return deleteByid(entity.getUserId());
	}

	@Override
	public boolean deleteByid(Integer id) {
		try {
			SQLiteDatabase db = dh.getWritableDatabase();

			db.execSQL("delete from users where userid=?", new Object[] { id });
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public Users findByid(Integer id) {
		Users entity = null;

		SQLiteDatabase db = dh.getWritableDatabase();
		Cursor c = db
				.rawQuery(
						"select userid,username,userage,usersalary from users where userid=?",
						new String[] { id + "" });
		if (c.moveToNext()) {

			entity = new Users();
			entity.setUserId(c.getInt(c.getColumnIndex("userid")));
			entity.setUserName(c.getString(c.getColumnIndex("userid")));
			entity.setUserAge(c.getInt(c.getColumnIndex("userage")));
			entity.setUserSalary(c.getDouble(c.getColumnIndex("usersalary")));
		}
		return entity;
	}

	@Override
	public List<Users> findAll() {
		List<Users> entities = new ArrayList<Users>();

		SQLiteDatabase db = dh.getWritableDatabase();
		Cursor c = db
				.rawQuery(
						"select userid,username,userage,usersalary from users",
						null);

		while (c.moveToNext()) {
			Users entity = new Users();
			entity.setUserId(c.getInt(c.getColumnIndex("userid")));
			entity.setUserName(c.getString(c.getColumnIndex("userid")));
			entity.setUserAge(c.getInt(c.getColumnIndex("userage")));
			entity.setUserSalary(c.getDouble(c.getColumnIndex("usersalary")));
			entities.add(entity);
		}

		return entities;
	}

}

DatabaseTese.java

[java] view plain copy print ?

package com.example.android_sqlite.test;
import java.util.List;
import com.example.android_sqlite.dao.UserDao;
import com.example.android_sqlite.dao.impl.UserDaoImpls;
import com.example.android_sqlite.database.DatabaseHelper;
import com.example.android_sqlite.domain.Users;
import android.test.AndroidTestCase;
public class DatabaseTeses extends AndroidTestCase {
public void createDatabase() {
DatabaseHelper dh = new DatabaseHelper(getContext());
dh.getWritableDatabase();
}
public void insert() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = new Users(null, "iii2", 20, 4000.0);
boolean flag = userDao.insert(entity);
assertEquals(true, flag);
}
public void update() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = new Users(7, "aaa", 21, 3000.0);
boolean flag = userDao.update(entity);
assertEquals(true, flag);
}
public void delete() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = new Users(1, "", null, null);
boolean flag = userDao.delete(entity);
assertEquals(true, flag);
}
public void findById() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = userDao.findByid(3);
if (entity != null) {
System.out.println(entity.toString());
} else {
System.out.println("no message");
}
}
public void findAll() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
List<Users> entities = userDao.findAll();
for (Users entity : entities) {
System.out.println(entity.toString());
}
}
}

package com.example.android_sqlite.test;

import java.util.List;

import com.example.android_sqlite.dao.UserDao;
import com.example.android_sqlite.dao.impl.UserDaoImpls;
import com.example.android_sqlite.database.DatabaseHelper;
import com.example.android_sqlite.domain.Users;

import android.test.AndroidTestCase;

public class DatabaseTeses extends AndroidTestCase {

	public void createDatabase() {
		DatabaseHelper dh = new DatabaseHelper(getContext());
		dh.getWritableDatabase();
	}

	public void insert() {
		DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
		UserDao userDao = new UserDaoImpls(databaseHelper);
		Users entity = new Users(null, "iii2", 20, 4000.0);
		boolean flag = userDao.insert(entity);
		assertEquals(true, flag);
	}

	public void update() {
		DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
		UserDao userDao = new UserDaoImpls(databaseHelper);
		Users entity = new Users(7, "aaa", 21, 3000.0);
		boolean flag = userDao.update(entity);
		assertEquals(true, flag);
	}

	public void delete() {
		DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
		UserDao userDao = new UserDaoImpls(databaseHelper);
		Users entity = new Users(1, "", null, null);
		boolean flag = userDao.delete(entity);
		assertEquals(true, flag);
	}

	public void findById() {
		DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
		UserDao userDao = new UserDaoImpls(databaseHelper);
		Users entity = userDao.findByid(3);
		if (entity != null) {
			System.out.println(entity.toString());
		} else {
			System.out.println("no message");
		}

	}

	public void findAll() {
		DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
		UserDao userDao = new UserDaoImpls(databaseHelper);
		List<Users> entities = userDao.findAll();
		for (Users entity : entities) {
			System.out.println(entity.toString());
		}
	}

}