1200字范文 > Android SQLite封装sql语句查看数据库

Android SQLite封装sql语句查看数据库

时间：2020-06-15 01:36:27

目标效果：

点击不同的按钮，可分别对数据库进行不同的操作并输出Toast提示和LogCat日志信息。

1.activity_main.xml页面定义所有的按钮控件。

activity_main.xml页面：

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;}@Overridepublic String toString() {// TODO Auto-generated method stubreturn "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);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_BOOK);}/*升级数据库*//*** 1、第一次创建数据库的时候，这个方法不会走* 2、清除数据后再次运行(相当于第一次创建)这个方法不会走* 3、数据库已经存在，而且版本升高的时候，这个方法才会调用*/@Overridepublic 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);}/*添加信息*/@Overridepublic 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();}/*根据页码数目删除信息*/@Overridepublic void delete(int pages) {SQLiteDatabase sqLiteDatabases=myDatabaseHelper.getWritableDatabase();sqLiteDatabases.delete(MyDatabaseHelper.TABLE_BOOK,MyDatabaseHelper.BOOK_PAGES + ">?",new String[]{String.valueOf(pages)});}/*根据价格修改信息*/@Overridepublic 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返回*/@Overridepublic List<Book> query() {List<Book> books=null;SQLiteDatabase sqLiteDatabases=myDatabaseHelper.getReadableDatabase();//查询是Readable，其余是WritableCursor 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;}@Overridepublic 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;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);/*创建*/btCreate=(Button) findViewById(R.id.btCreate);btCreate.setOnClickListener(new OnClickListener() {@Overridepublic 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() {@Overridepublic 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() {@Overridepublic 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() {@Overridepublic 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() {@Overridepublic 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() {@Overridepublic 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环境中，可进行查看数据库内容。

————————————————

原文链接：/hester_hester/article/details/51447189

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。