目标效果:
点击不同的按钮,可分别对数据库进行不同的操作并输出Toast提示和LogCat日志信息。
1.activity_main.xml页面定义所有的按钮控件。
activity_main.xml页面:
<RelativeLayout 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"
tools:context=".MainActivity" >
<Button
android:id="@+id/btCreate"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentTop="true"
android:layout_centerHorizontal="true"
android:layout_marginTop="30dp"
android:text="Create database" />
<Button
android:id="@+id/btAdd"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_below="@+id/btCreate"
android:layout_marginTop="30dp"
android:text="Add data" />
<Button
android:id="@+id/btUpdate"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_below="@+id/btAdd"
android:layout_marginTop="20dp"
android:text="Update_data" />
<Button
android:id="@+id/btDelete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_below="@+id/btUpdate"
android:layout_marginTop="20dp"
android:text="Delete_Date" />
<Button
android:id="@+id/btReplace"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/btDelete"
android:layout_marginTop="20dp"
android:text="Replace_data" />
<Button
android:id="@+id/btQuery"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/btReplace"
android:layout_marginTop="20dp"
android:text="Query_data" />
</RelativeLayout>
2.定义Book.java实体类并重写toString方法,用于查询时数据的输出。
Book.java页面:
package com.example.entity;
public class Book {
private int id;
private String name;
private String author;
private int pages;
private double price;
public Book(String name, String author, int pages, double price) {
super();
this.name = name;
this.author = author;
this.pages = pages;
this.price = price;
}
public Book() {
super();
}
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 getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getPages() {
return pages;
}
public void setPages(int pages) {
this.pages = pages;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return "book id "+id+",name "+name+",author "+author+",pages "+pages+",price "+price;
}
}
3.定义BookDao.java接口页面用于定义父类方法。
BookDao.java页面:
package com.example.dao;
import java.util.List;
import com.example.entity.Book;
public interface BookDao {
public void add(Book book);
public void delete(int pages);
public void update(Book book);
public List<Book> query();
public void replace(Book book);
}
4.定义MyDatabaseHelper.java页面用于继承SQLiteOpenHelper类,进行创建数据库。
MyDatabaseHelper.java页面:
package com.example.newclass;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
public class MyDatabaseHelper extends SQLiteOpenHelper{
private static final String DB_NAME="books.db";//数据库名称
private static final int BOOK_VERSION=1;//版本号
public static final String TABLE_BOOK="book";//表名
public static final String BOOK_ID="id";
public static final String BOOK_AUTHOR="author";
public static final String BOOK_PRICE="price";
public static final String BOOK_PAGES="pages";
public static final String BOOK_NAME="name";
public static final String CREATE_BOOK="create table book ( id integer primary key autoincrement, author text, price real, pages integer, name text)";
public MyDatabaseHelper(Context context) {
super(context,DB_NAME,null,BOOK_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);
}
/*升级数据库*/
/**
* 1、第一次创建数据库的时候,这个方法不会走
* 2、清除数据后再次运行(相当于第一次创建)这个方法不会走
* 3、数据库已经存在,而且版本升高的时候,这个方法才会调用
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
5.定义BookDaoImpl.java页面用于实现BookDao.java接口,重写BookDao.java页面中的增删改查方法。
BookDaoImpl.java页面:
package com.example.dao;
import java.util.ArrayList;
import java.util.List;
import com.example.entity.Book;
import com.example.newclass.MyDatabaseHelper;
import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
public class BookDaoImpl implements BookDao{
MyDatabaseHelper myDatabaseHelper;
public BookDaoImpl(Context context){
myDatabaseHelper=new MyDatabaseHelper(context);
}
/*添加信息*/
@Override
public void add(Book book) {
SQLiteDatabase sqLiteDatabases=myDatabaseHelper.getWritableDatabase();
ContentValues values=new ContentValues();
//开始组装数据
values.put(MyDatabaseHelper.BOOK_NAME,book.getName());
values.put(MyDatabaseHelper.BOOK_AUTHOR,book.getAuthor());
values.put(MyDatabaseHelper.BOOK_PAGES,book.getPages());
values.put(MyDatabaseHelper.BOOK_PRICE,book.getPrice());
sqLiteDatabases.insert(MyDatabaseHelper.TABLE_BOOK,null,values);//插入数据
values.clear();
}
/*根据页码数目删除信息*/
@Override
public void delete(int pages) {
SQLiteDatabase sqLiteDatabases=myDatabaseHelper.getWritableDatabase();
sqLiteDatabases.delete(MyDatabaseHelper.TABLE_BOOK,MyDatabaseHelper.BOOK_PAGES + ">?",new String[]{String.valueOf(pages)});
}
/*根据价格修改信息*/
@Override
public void update(Book book) {
SQLiteDatabase sqLiteDatabases=myDatabaseHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(MyDatabaseHelper.BOOK_NAME,book.getName());
values.put(MyDatabaseHelper.BOOK_AUTHOR,book.getAuthor());
values.put(MyDatabaseHelper.BOOK_PAGES,book.getPages());
values.put(MyDatabaseHelper.BOOK_PRICE,book.getPrice());
sqLiteDatabases.update(MyDatabaseHelper.TABLE_BOOK,values,MyDatabaseHelper.BOOK_PRICE + "=?",new String[]{String.valueOf(book.getPrice())});
values.clear();
}
/*查询信息,使用List返回*/
@Override
public List<Book> query() {
List<Book> books=null;
SQLiteDatabase sqLiteDatabases=myDatabaseHelper.getReadableDatabase();//查询是Readable,其余是Writable
Cursor cursor=sqLiteDatabases.query(MyDatabaseHelper.TABLE_BOOK,null,null,null,null,null,null);
if(cursor!=null){
books=new ArrayList<Book>();
while(cursor.moveToNext()){
Book book=new Book();
int id=cursor.getInt(cursor.getColumnIndex(myDatabaseHelper.BOOK_ID));
String name=cursor.getString(cursor.getColumnIndex(MyDatabaseHelper.BOOK_NAME));
String author=cursor.getString(cursor.getColumnIndex(MyDatabaseHelper.BOOK_AUTHOR));
int pages=cursor.getInt(cursor.getColumnIndex(MyDatabaseHelper.BOOK_PAGES));
double price=cursor.getDouble(cursor.getColumnIndex(MyDatabaseHelper.BOOK_PRICE));
book.setId(id);
book.setName(name);
book.setAuthor(author);
book.setPages(pages);
book.setPrice(price);
books.add(book);
//Log.i("MainActivity","book id is "+id+" name is "+name+",author is "+author+",pages is "+pages+",price is "+price);
}
}
cursor.close();
return books;
}
@Override
public void replace(Book book) {
SQLiteDatabase sqLiteDatabases=myDatabaseHelper.getWritableDatabase();
sqLiteDatabases.beginTransaction();//开启事务
try {
sqLiteDatabases.delete(MyDatabaseHelper.TABLE_BOOK,null,null);
ContentValues values=new ContentValues();
values.put(MyDatabaseHelper.BOOK_NAME,book.getName());
values.put(MyDatabaseHelper.BOOK_AUTHOR,book.getAuthor());
values.put(MyDatabaseHelper.BOOK_PAGES,book.getPages());
values.put(MyDatabaseHelper.BOOK_PRICE,book.getPrice());
sqLiteDatabases.insert(MyDatabaseHelper.TABLE_BOOK,null,values);
sqLiteDatabases.setTransactionSuccessful();//事物已经执行成功
values.clear();
} catch (Exception e) {
e.printStackTrace();
}finally{
sqLiteDatabases.endTransaction();//结束事物
}
}
}
6.MainActivity.java页面用于将增删改查方法和按钮绑定。
MainActivity.java页面:
package com.example.sqlite;
import java.util.List;
import com.example.dao.BookDaoImpl;
import com.example.entity.Book;
import com.example.newclass.MyDatabaseHelper;
import android.os.Bundle;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;
public class MainActivity extends Activity {
private Button btCreate,btAdd,btUpdate,btDelete,btQuery,btReplace;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
/*创建*/
btCreate=(Button) findViewById(R.id.btCreate);
btCreate.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
MyDatabaseHelper myDatabaseHelper=new MyDatabaseHelper(MainActivity.this);
myDatabaseHelper.getWritableDatabase();
Toast.makeText(MainActivity.this, "Create succeeded",Toast.LENGTH_SHORT).show();
}
});
/*添加*/
btAdd=(Button) findViewById(R.id.btAdd);
btAdd.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
BookDaoImpl bookDaoImpl=new BookDaoImpl(MainActivity.this);
Book book1=new Book("The Da Vinci ode","Dan Brown",454,16.96);
Book book2=new Book("hello c","Tan",510,24.00);
bookDaoImpl.add(book1);
bookDaoImpl.add(book2);
Toast.makeText(MainActivity.this,"Add Succeed",Toast.LENGTH_SHORT).show();
}
});
/*修改*/
btUpdate=(Button) findViewById(R.id.btUpdate);
btUpdate.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v){
BookDaoImpl bookDaoImpl=new BookDaoImpl(MainActivity.this);
Book book=new Book("The Da Vinci ode","Dan Brown",540,24.00);
bookDaoImpl.update(book);
Toast.makeText(MainActivity.this,"Update Succeed",Toast.LENGTH_SHORT).show();
}
});
/*删除*/
btDelete=(Button) findViewById(R.id.btDelete);
btDelete.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
BookDaoImpl bookDaoImpl=new BookDaoImpl(MainActivity.this);
bookDaoImpl.delete(500);
Toast.makeText(MainActivity.this,"Delete Succeed",Toast.LENGTH_SHORT).show();
}
});
/*查询*/
btQuery=(Button) findViewById(R.id.btQuery);
btQuery.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
BookDaoImpl bookDaoImpl=new BookDaoImpl(MainActivity.this);
List<Book> books=bookDaoImpl.query();
for(Book book : books){
Log.i("MainActivity",book.toString());
}
Log.i("MainActivity","-----分割线-----");
Toast.makeText(MainActivity.this,"Query Succeed",Toast.LENGTH_SHORT).show();
}
});
/*替换*/
btReplace=(Button) findViewById(R.id.btReplace);
btReplace.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
BookDaoImpl bookDaoImpl=new BookDaoImpl(MainActivity.this);
Book book=new Book("java","Li Brown",320,14.50);
bookDaoImpl.replace(book);
Toast.makeText(MainActivity.this,"Replace Succeed",Toast.LENGTH_SHORT).show();
}
});
}
}
7.程序运行就可以显示目标效果了。
8.生成的数据库文件在/data/data/项目包名/databases/文件夹中,导出后,添加到Navicat Premium环境中,可进行查看数据库内容。
————————————————
版权声明:本文为CSDN博主「Vivinia_Vivinia」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/hester_hester/article/details/51447189