数据库的创建和操作
数据库 操作 创建
2023-09-11 14:15:38 时间
初步学习，一步步按照传智播客的视频操作的。
AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="maureen.database"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="18" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name="maureen.database.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<uses-library android:name="android.test.runner"/>"
</application>
<instrumentation android:name="android.test.InstrumentationTestRunner"
android:targetPackage="maureen.database" android:label="Tests for my app"/>"
</manifest>
MainActivity.java
package maureen.database;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
@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;
}
}
Person.java
package maureen.domain;
import android.R.integer;
public class Person {
private Integer id;
private String name;
private String phone;
public Person() {
}
public Person(String name, String phone) {
this.name = name;
this.phone = phone;
}
public Person(Integer id, String name, String phone) {
this.id = id;
this.name = name;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]";
}
}
DBOpenHelper.java
package maureen.service;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.AlteredCharSequence;
public class DBOpenHelper extends SQLiteOpenHelper {
public DBOpenHelper(Context context) {
/*
* super(context, name, factory, version);name--要创建的数据库的名称，factory-对查询后的
* 结果集进行随机访问（游标），传入null代表使用系统默认的游标工厂；version--数据库文件的版本号 不能为0
*/
super(context, "maureendb.db", null, 2);// 创建的数据库默认保存在<包>/databases/
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {// 数据库第一次被创建的时候调用
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE person(personid integer primary key autoincrement,name varchar(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 数据库文件版本号发生变更的时候调用,1变成2的时候就会调用此函数,且只调用此函数
// TODO Auto-generated method stub
db.execSQL("ALTER TABLE person ADD phone varchar(12) null");// 往表中增加一列
}
}
PersonService.java
package maureen.service;
import java.util.ArrayList;
import java.util.List;
import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import maureen.domain.Person;
public class PersonService {// 增、删、改、查
private DBOpenHelper dbOpenHelper;
public PersonService(Context context) {
// super();
this.dbOpenHelper = new DBOpenHelper(context);
}
/*
* 添加记录
*/
public void save(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();// 取得数据库操作实例
db.execSQL("insert into person(name,phone) values('"
+ person.getName() + "','" + person.getPhone() + "')");// 或者用如下的方式赋值
/*
* db.execSQL("insert into person(name,phone) values(?,?)", new
* Object[]{person.getName(),person.getPhone()});
*/
// db.close();可以不关，不关可以提高性能，不用频繁打开数据库
}
/*
* 删除记录
*/
public void delete(Integer id) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?)", new Object[] { id });
}
/*
* 修改记录
*/
public void update(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL(
"update person set name=?,phone=? where personid=?",
new Object[] { person.getName(), person.getPhone(),
person.getId() });
}
/*
*
* 查找记录
*/
public Person find(Integer id) {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// 数据库磁盘空间没满的时候返回的操作实例就是getWritableDatabase与它一样，而如满了返回的就是只读的操作实例
Cursor cursor = db.rawQuery("select * from person where personid=?",
new String[] { id.toString() });// cursor对查询返回的结果集进行随机访问
if (cursor.moveToFirst()) {
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid, name, phone);
}
cursor.close();
return null;
}
/*
* 分页获取记录 param offset 跳过前面多少条记录 param maxResult 每页多少条记录
*/
public List<Person> getScrollData(int offset, int maxResult) {// 分页查询
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// 数据库磁盘空间没满的时候返回的操作实例就是getWritableDatabase与它一样，而如满了返回的就是只读的操作实例
Cursor cursor = db.rawQuery(
"select * from person order by personid asc limit ?,?",
new String[] { String.valueOf(offset),
String.valueOf(maxResult) });
while (cursor.moveToNext()) {// 迭代
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personid, name, phone));
}
cursor.close();
return persons;
}
/*
*
* 获取记录总数
*/
public long getCount() {// 取得记录总数
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person ", null);
cursor.moveToFirst();// 因为一定有一条记录，所以不用判断
long result = cursor.getLong(0);// 只有一个字段，所以设为0
cursor.close();
return 0;
}
}
package maureen.service;
import java.util.ArrayList;
import java.util.List;
import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import maureen.domain.Person;
public class PersonService {// 增、删、改、查
private DBOpenHelper dbOpenHelper;
public PersonService(Context context) {
// super();
this.dbOpenHelper = new DBOpenHelper(context);
}
/*
* 添加记录
*/
public void save(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();// 取得数据库操作实例
db.execSQL("insert into person(name,phone) values('"
+ person.getName() + "','" + person.getPhone() + "')");// 或者用如下的方式赋值
/*
* db.execSQL("insert into person(name,phone) values(?,?)", new
* Object[]{person.getName(),person.getPhone()});
*/
// db.close();可以不关，不关可以提高性能，不用频繁打开数据库
}
/*
* 删除记录
*/
public void delete(Integer id) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?)", new Object[] { id });
}
/*
* 修改记录
*/
public void update(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL(
"update person set name=?,phone=? where personid=?",
new Object[] { person.getName(), person.getPhone(),
person.getId() });
}
/*
*
* 查找记录
*/
public Person find(Integer id) {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// 数据库磁盘空间没满的时候返回的操作实例就是getWritableDatabase与它一样，而如满了返回的就是只读的操作实例
Cursor cursor = db.rawQuery("select * from person where personid=?",
new String[] { id.toString() });// cursor对查询返回的结果集进行随机访问
if (cursor.moveToFirst()) {
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid, name, phone);
}
cursor.close();
return null;
}
/*
* 分页获取记录 param offset 跳过前面多少条记录 param maxResult 每页多少条记录
*/
public List<Person> getScrollData(int offset, int maxResult) {// 分页查询
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// 数据库磁盘空间没满的时候返回的操作实例就是getWritableDatabase与它一样，而如满了返回的就是只读的操作实例
Cursor cursor = db.rawQuery(
"select * from person order by personid asc limit ?,?",
new String[] { String.valueOf(offset),
String.valueOf(maxResult) });
while (cursor.moveToNext()) {// 迭代
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personid, name, phone));
}
cursor.close();
return persons;
}
/*
*
* 获取记录总数
*/
public long getCount() {// 取得记录总数
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person ", null);
cursor.moveToFirst();// 因为一定有一条记录，所以不用判断
long result = cursor.getLong(0);// 只有一个字段，所以设为0
cursor.close();
return 0;
}
}
OtherPersonService.java
package maureen.service;
import java.util.ArrayList;
import java.util.List;
import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import maureen.domain.Person;
public class OtherPersonService {// 增、删、改、查
private DBOpenHelper dbOpenHelper;
public OtherPersonService(Context context) {
// super();
this.dbOpenHelper = new DBOpenHelper(context);
}
/*
* 添加记录
*/
public void save(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();// 取得数据库操作实例
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());// key-字段名称，要和表中的名称一致
db.insert("person", null, values);// null值字段，如果第三个参数传的是null值或者空集合就需要用第二个参数，第三个字段不为空的时候，就不需要指定第二个参数，直接为null就可以
// db.execSQL("insert into person(name,phone) values('"+person.getName()+"','"+person.getPhone()+"')");//或者用如下的方式赋值
/*
* db.execSQL("insert into person(name,phone) values(?,?)", new
* Object[]{person.getName(),person.getPhone()});
*/
// db.close();可以不关，不关可以提高性能，不用频繁打开数据库
}
/*
* 删除记录
*/
public void delete(Integer id) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.delete("person", "personid=?", new String[] { id.toString() });
// db.execSQL("delete from person where personid=?)",new Object[]{id});
}
/*
* 修改记录
*/
public void update(Person person) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
db.update("person", values, "personid=?", new String[] { person.getId()
.toString() });
// db.execSQL("update person set name=?,phone=? where personid=?",new
// Object[]{person.getName(),person.getPhone(),person.getId()});
}
/*
*
* 查找记录
*/
public Person find(Integer id) {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// 数据库磁盘空间没满的时候返回的操作实例就是getWritableDatabase与它一样，而如满了返回的就是只读的操作实例
// db.query(table, columns, selection, selectionArgs, groupBy, having,
// orderBy)--column是要查询的字段，传入null就会返回所有字段
Cursor cursor = db.query("person", new String[] { "personid", "name",
"phone" }, "personid=?", new String[] { id.toString() }, null,
null, null);
// Cursor cursor=db.rawQuery("select * from person where personid=?",
// new String[]{id.toString()});//cursor对查询返回的结果集进行随机访问
if (cursor.moveToFirst()) {
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid, name, phone);
}
cursor.close();
return null;
}
/*
* 分页获取记录 param offset 跳过前面多少条记录 param maxResult 每页多少条记录
*/
public List<Person> getScrollData(int offset, int maxResult) {// 分页查询
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();// 数据库磁盘空间没满的时候返回的操作实例就是getWritableDatabase与它一样，而如满了返回的就是只读的操作实
Cursor cursor = db.query("person", null, null, null, null, null,
"personid asc", offset + "," + maxResult);
/*
* Cursor
* cursor=db.rawQuery("select * from person order by personid asc limit ?,?"
* , new String[]{String.valueOf(offset),String.valueOf(maxResult)});
*/
while (cursor.moveToNext()) {// 迭代
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personid, name, phone));
}
cursor.close();
return persons;
}
/*
*
* 获取记录总数
*/
public long getCount() {// 取得记录总数
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", new String[] { "count(*)" }, null,
null, null, null, null);
// Cursor cursor=db.rawQuery("select count(*) from person ", null);
cursor.moveToFirst();// 因为一定有一条记录，所以不用判断
long result = cursor.getLong(0);// 只有一个字段，所以设为0
cursor.close();
return 0;
}
}
PersonServiceTest.java
package maureen.test;
import java.util.List;
import android.test.AndroidTestCase;
import android.util.Log;
import maureen.domain.Person;
import maureen.service.*;
public class PersonServiceTest extends AndroidTestCase {
private static final String TAG = "PersonServiceTest";
public void testCreateDB() throws Exception {
DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());
dbOpenHelper.getWritableDatabase();// 第一次调用getWritableDatabase函数就能自动创建数据库
}
public void testSave() throws Exception {
PersonService service = new PersonService(this.getContext());
for (int i = 0; i < 20; i++) {
Person person = new Person("君君桑" + i, "15671684862" + i);
service.save(person);
}
}
public void testDelete() throws Exception {
PersonService service = new PersonService(this.getContext());
service.delete(21);
}
public void testUpdate() throws Exception {
PersonService service = new PersonService(this.getContext());
Person person = service.find(1);
person.setName("君君");
service.update(person);
}
public void testFind() throws Exception {// 测试的时候在Logcat里面的filter中放入PersonServiceTest再进行单元测试，在logcat中如果找到刚刚插入的记录，则测试通过
PersonService service = new PersonService(this.getContext());
Person person = service.find(1);
Log.i(TAG, person.toString());
}
public void testScrollData() throws Exception {
PersonService service = new PersonService(this.getContext());
List<Person> persons = service.getScrollData(10, 5);
for (Person person : persons) {
Log.i(TAG, person.toString());
}
}
public void testCount() throws Exception {
PersonService service = new PersonService(this.getContext());
long result = service.getCount();
Log.i(TAG, result + "");
}
}OtherPersonServiceTest.java--与PersonServiceTest功能相同是为了测试OtherPersonService
package maureen.test;
import java.util.List;
import android.test.AndroidTestCase;
import android.util.Log;
import maureen.domain.Person;
import maureen.service.*;
public class OtherPersonServiceTest extends AndroidTestCase {
private static final String TAG = "PersonServiceTest";
public void testSave() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
Person person = new Person("xiaaosha", "15671684800");
service.save(person);
}
public void testDelete() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
service.delete(21);
}
public void testUpdate() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
Person person = service.find(1);
person.setName("赵君君");
service.update(person);
}
public void testFind() throws Exception {// 测试的时候在Logcat里面的filter中放入PersonServiceTest再进行单元测试，在logcat中如果找到刚刚插入的记录，则测试通过
OtherPersonService service = new OtherPersonService(this.getContext());
Person person = service.find(1);
Log.i(TAG, person.toString());
}
public void testScrollData() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
List<Person> persons = service.getScrollData(0, 5);
for (Person person : persons) {
Log.i(TAG, person.toString());
}
}
public void testCount() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
long result = service.getCount();
Log.i(TAG, result + "");
}
}
package maureen.test;
import java.util.List;
import android.test.AndroidTestCase;
import android.util.Log;
import maureen.domain.Person;
import maureen.service.*;
public class OtherPersonServiceTest extends AndroidTestCase {
private static final String TAG = "PersonServiceTest";
public void testSave() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
Person person = new Person("xiaaosha", "15671684800");
service.save(person);
}
public void testDelete() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
service.delete(21);
}
public void testUpdate() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
Person person = service.find(1);
person.setName("赵君君");
service.update(person);
}
public void testFind() throws Exception {// 测试的时候在Logcat里面的filter中放入PersonServiceTest再进行单元测试，在logcat中如果找到刚刚插入的记录，则测试通过
OtherPersonService service = new OtherPersonService(this.getContext());
Person person = service.find(1);
Log.i(TAG, person.toString());
}
public void testScrollData() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
List<Person> persons = service.getScrollData(0, 5);
for (Person person : persons) {
Log.i(TAG, person.toString());
}
}
public void testCount() throws Exception {
OtherPersonService service = new OtherPersonService(this.getContext());
long result = service.getCount();
Log.i(TAG, result + "");
}
}
相关文章
- [Android] SQLite数据库之增删改查基础操作
- Jedis（java操作redis数据库技术）
- 数据库其它操作
- Myeclipse 操作数据库
- 第二百八十三节，MySQL数据库-MySQL存储过程
- Python操作SQLite数据库的方法详解
- android 对sqlite数据库的增删改查等各种操作
- 【MYSQL数据库开发之二】MYSQL 基础语句的书写与一些数据库操作（创建使用数据库、表）！
- 【转】django的ORM操作数据库样例
- Navicat工具获取操作数据库和表的SQL语句
- MySQL数据库的备份和还原
- 更新数据库中某一列的值，让其在原数的基础上加N
- oracle数据库维护常用操作
- 使用 ABAP 代码动态创建 Netweaver 数据库表
- 使用JPA + Eclipselink操作PostgreSQL数据库
- 【SpringBoot笔记28】SpringBoot集成ES数据库之操作doc文档（创建、更新、删除、查询）
- Qt操作SQLite数据库
- 【JavaEE基础与高级 第42章】C3P0连接数据库操作顺序
- Python编程：peewee的pwiz将已有数据库转为Model
- ODBC数据库
- SQLServer 创建自己的数据库
- [1] 数据库字段的规范化设计和冗余化设计
- 十一、MYSQL数据库备份还原
- LabVIEW与Access数据库通信时，如何创建DSN文件
- Python接口自动化核心模块 - 数据库操作和日志
- Django（三） ORM 数据库操作
- ASP.NET Core使用EF Core操作MySql数据库
- Django ORM 数据库常用操作
- 【Python】MongoDB数据库安装和Pymongo操作学习