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

目标效果:
在这里插入图片描述
在这里插入图片描述

点击不同的按钮,可分别对数据库进行不同的操作并输出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

一个简单的基于AndroidSqlite数据库的操作封装,它有如下的好处:便捷地创建表和增添表字段灵活的数据类型处理通过操作对象来insert或者update表记录支持多种查询方式,支持多表自定义的复杂查询,支持分页查询支持事务快速开始:    1. 设计表:@Table(name="t_user") public class UserModel {     @Table.Column(name="user_id",type=Column.TYPE_INTEGER,isPrimaryKey=true)     public Integer userId;     @Table.Column(name="user_name",type=Column.TYPE_STRING,isNull=false)     public String userName;     @Table.Column(name="born_date",type=Column.TYPE_TIMESTAMP)     public Date bornDate;     @Table.Column(name="pictrue",type=Column.TYPE_BLOB)     public byte[] pictrue;     @Table.Column(name="is_login",type=Column.TYPE_BOOLEAN)     public Boolean isLogin;     @Table.Column(name="weight",type=Column.TYPE_DOUBLE)     public Double weight; }2. 初始化对象:SQLiteDatabase db = context.openOrCreateDatabase("test.db", Context.MODE_PRIVATE, null); DbSqlite dbSqlite = new DbSqlite(db); IBaseDao userDAO = DaoFactory.createGenericDao(dbSqlite, UserModel.class);3. 创建表:userDAO.createTable(); 4. Insert 记录:UserModel user = new UserModel(); user.userName = "darcy"; user.isLogin = true; user.weight = 60.5; user.bornDate = new Date(); byte[] picture = {0x1,0x2,0x3,0x4}; user.pictrue = picture; userDAO.insert(user);5. Update 记录:UserModel user = new UserModel(); user.weight = 88.0; userDAO.update(user, "user_name=?", "darcy");6. 查询://单条结果查询 UserModel user = userDAO.queryFirstRecord("user_name=?", "darcy"); //一般查询 List userList = userDAO.query("user_name=? and weight > ?", "darcy" , "60"); //分页查询 PagingList pagingList = userDAO.pagingQuery(null, null, 1, 3);7. 事务支持:DBTransaction.transact(mDb, new DBTransaction.DBTransactionInterface() {         @Override         public void onTransact() {             // to do                 } };8. 更新表(目前只支持添加字段)@Table(name="t_user" , version=2) //修改表版本 public class UserModel {     //members above...     //new columns     @Table.Column(name="new_column_1",type=Column.TYPE_INTEGER)     public Integer newColumn;     @Table.Column(name="new_column_2",type=Column.TYPE_INTEGER)     public Integer newColumn2; } userDAO.updateTable();缺点和不足:还没支持多对一或者一多的关系没支持联合主键没支持表的外键设计其他...实例:SqliteLookup(Android查看Sqlite数据库利器): https://github.com/YeDaxia/SqliteLookup 标签:SQLiteUtils
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值