Android学习篇章22-SQLite数据库操作：SQLiteDatabase

最新推荐文章于 2022-11-22 00:21:07 发布

辛言

最新推荐文章于 2022-11-22 00:21:07 发布

阅读量756

点赞数

分类专栏： Android 基础文章标签： android

本文链接：https://blog.csdn.net/naibbian/article/details/14046823

版权

Android 基础专栏收录该内容

61 篇文章 0 订阅

订阅专栏

DAO:

public class BookDao {
	
	SQLiteDatabase  db=null;
	String db_path="book.db";
	Context context=null;
	public  BookDao(Context context)
	{
		this.context=context;
	}
	public  void   createDB()
	{
		  if(db==null)
		  {
			  //设置数据库的地址  以及读写方式 
			  try {
				db=context.openOrCreateDatabase(db_path, SQLiteDatabase.OPEN_READWRITE, null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//   db=SQLiteDatabase.openOrCreateDatabase(db_path, null);
		  }
	}
	
	public  void closeDB()
	{
		  if(db!=null)
		  {
			  db.close();
			  db=null;
		  }
	}
	
	
	public void create()
	{
			 Log.i("test", "创建数据库");
	//		 String createSql="create table bookinfo (_id integer primary key autoincrement," +
	//		 		" bookname varchar(100), author varchar(30), price float )";
		//Sqlite数据库中  所有的列都是弱数据类型的  它不会对你插入这个列的数据做数据类型检查
		//它会自动的将这个数据转换为对这一列最合适的数据类型
			 String createSql="create table bookinfo (_id integer primary key autoincrement," +
				 		" bookname , author , price )";
			 try {
				db.execSQL(createSql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	public int  updateBook(Book book)
	{
		//update bookinfo  set(bookname =? , author=?,price=?)
		// where _id=?
		ContentValues cv=new ContentValues();
		cv.put("bookname", book.bookname);
		cv.put("author", book.author);
		cv.put("price", book.price);
//		db.update("bookinfo", cv, "_id="+book.bookId, null);
		int result=db.update("bookinfo", cv, "_id=?", new String[]{""+book.bookId});
		return result;
	}
	public  Cursor  queryAllBooksByCursor()
	{
		return db.query("bookinfo",null,null,null,null,null,null);
	}
	public  List<Book>  queryAllBooks()
	{
		List<Book>  bookList=new ArrayList<Book>();
		Cursor c=db.query("bookinfo",null,null,null,null,null,null);
	    if(c.moveToFirst())
	    {
	    	do
	    	{
	    		Book book=new Book();
				book.bookId=c.getInt(c.getColumnIndex("_id"));
				book.bookname=c.getString(c.getColumnIndex("bookname"));
				book.author=c.getString(c.getColumnIndex("author"));
				book.price=c.getFloat(c.getColumnIndex("price"));
	    		bookList.add(book);
	    	}while(c.moveToNext());
	    	c.close();
	    }
	    
	    return bookList;
	}
	
	public  Book  queryBookById(String id)
	{
		String sql="select * from bookinfo where _id=?";
		Cursor c=db.rawQuery(sql, new String[]{id});
		if(c.moveToFirst())
		{
			Book book=new Book();
			book.bookId=c.getInt(c.getColumnIndex("_id"));
			book.bookname=c.getString(c.getColumnIndex("bookname"));
			book.author=c.getString(c.getColumnIndex("author"));
			book.price=c.getFloat(c.getColumnIndex("price"));
			c.close();
			return book;
			
		}
		return null;
	}
	
	public  void  insertBook(Book book)
	{
		if(db!=null)
		{
			String sql="insert into bookinfo  (author) " +
					"values (null)";
			ContentValues  cv=new ContentValues();
			cv.put("bookname", book.bookname);
			cv.put("author", book.author);
			cv.put("price",book.price);
			//第2个参数允许为空的列的列名
			try {
				db.insert("bookinfo", "author", cv);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				Log.i("test", "开始创建数据库");
				create();
				db.insert("bookinfo", "author", cv);
				e.printStackTrace();
			}
		}
	}
}

MainActivity:

public class MainActivity extends Activity {
	EditText  bookIdTxt=null;
	EditText  bookTxt=null;
	EditText  authorTxt=null;
	EditText  priceTxt=null;
	BookDao  db=null;
	ListView  listview1=null;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		init();
	}
	@Override
	protected void onPause() {
		// TODO Auto-generated method stub
		if(db!=null)
			db.closeDB();
		super.onPause();
	}
	@Override
	protected void onResume() {
		db=new BookDao(this);
		db.createDB();
		super.onResume();
	}
	public  void  clickBtn(View view)
	{
		  int id=view.getId();
		  if(id==R.id.btn0)
		  {
			  //创建数据库
			  db.create();
		  }else if(id==R.id.btn1)
		  {
			  //增加图书信息
			  addBook();
		  }else if(id==R.id.btn2)
		  {
			  
		  }else if(id==R.id.btn3)
		  {
			  updateBookById();
		  }else if(id==R.id.btn4)
		  {
			  findBookById();
		  }else if(id==R.id.btn5)
		  {
			  //findAllBookByCursor();
			  findAllBook();
		  }
		
	}
	
	public void findAllBookByCursor()
	{
		  Cursor c= db.queryAllBooksByCursor();
		  
		  String[] from={"_id","bookname","author","price"};
		  int[]   to={R.id.bookIdTxt,R.id.bookTxt,R.id.authorTxt,R.id.priceTxt};
		  SimpleCursorAdapter  adapter=
				  new SimpleCursorAdapter(this, R.layout.line, c, from, to);
	      listview1.setAdapter(adapter);
	    //  c.close();
	
	}
	
	public void findAllBook()
	{
		List<Book> bookList= db.queryAllBooks();
		List  list=new ArrayList<String>();
		for(int i=0;i<bookList.size();i++)
		{
			list.add(""+bookList.get(i));
		}
		ArrayAdapter<String>  adapter=
				new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,list);
		listview1.setAdapter(adapter);
		
	}
	
	public  void findBookById()
	{
		    String id=""+bookIdTxt.getText();
		    Book book=db.queryBookById(id);
		    if(book!=null)
		    {
		    	  bookTxt.setText(book.bookname);
		    	  authorTxt.setText(book.author);
		    	  priceTxt.setText(""+book.price);
		    }else
		    {
		    	//Builder模式
		    	new AlertDialog.Builder(this)
		    	    .setTitle("查询")
		    	    .setMessage("没有查到信息")
		    	    .setCancelable(false)
		    	    .setPositiveButton("确定", new DialogInterface.OnClickListener() {
						
						@Override
						public void onClick(DialogInterface dialog, int which) {
							// TODO Auto-generated method stub
							dialog.dismiss();
						}
					})
		    	    .show();
		    }
	}
	
	public void addBook()
	{
		Book book=new Book();
		book.bookname=""+ bookTxt.getText();
		book.author=""+authorTxt.getText();
		book.price=Float.parseFloat(""+priceTxt.getText());
		db.insertBook(book);
	}

	public  void updateBookById()
	{
		Book book=new Book();
		book.bookId=Integer.parseInt(""+bookIdTxt.getText());
		book.bookname=""+ bookTxt.getText();
		book.author=""+authorTxt.getText();
		book.price=Float.parseFloat(""+priceTxt.getText());
		int result=db.updateBook(book);
		if(result>0)
		{
			new AlertDialog.Builder(this)
			  .setTitle("更新成功")
			  .setMessage("图书信息已更新")
			  .setPositiveButton("确定", new DialogInterface.OnClickListener() {
				
				@Override
				public void onClick(DialogInterface dialog, int which) {
					// TODO Auto-generated method stub
					findBookById();
				}
			})
			  .show();
			  
		}else
		{
			Toast.makeText(this, "数据更新失败", Toast.LENGTH_LONG).show();
		}
	}
	
	public void init()
	{
		  bookIdTxt=(EditText)findViewById(R.id.bookIdTxt);
		  bookTxt=(EditText)findViewById(R.id.bookTxt);
		  authorTxt=(EditText)findViewById(R.id.authorTxt);
		  priceTxt=(EditText)findViewById(R.id.priceTxt);
		  listview1=(ListView)findViewById(R.id.listview1);
	}
	@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;
	}

}

实体类：

public class Book {
	
	public int bookId;
	public String bookname;
	public String author;
	public float  price;
	public String toString()
	{
		return  "id:"+bookId+" 书名:"+bookname
				+" 作者:"+author+" 价格:"+price;
	}
}

mainxml:

<LinearLayout 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"
    android:orientation="vertical"
    tools:context=".MainActivity" >

     <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="图书编号" />
    <EditText  android:id="@+id/bookIdTxt"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        />
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="书名" />
    <EditText  android:id="@+id/bookTxt"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        />
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="作者" />
    <EditText  android:id="@+id/authorTxt"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        />
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="价格" />
    <EditText  android:id="@+id/priceTxt"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        />
    
    <Button android:id="@+id/btn0"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="clickBtn"
        android:text="创建数据库"
        />    
    <Button android:id="@+id/btn1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="clickBtn"
        android:text="增加"
        />
    <Button android:id="@+id/btn2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="clickBtn"
        android:text="删除"
        />
    <Button android:id="@+id/btn3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="clickBtn"
        android:text="修改"
        />
    <Button android:id="@+id/btn4"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="clickBtn"
        android:text="根据ID查询"
        />
    <Button android:id="@+id/btn5"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="clickBtn"
        android:text="查询所有"
        />
    <ListView android:id="@+id/listview1"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        />
</LinearLayout>

line.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="horizontal" >
    <TextView android:id="@+id/bookIdTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="25sp"
        android:textColor="#f00"
        android:layout_marginRight="5dp"
        />

    <TextView android:id="@+id/bookTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="25sp"
        android:textColor="#000"
        android:layout_marginRight="5dp"
        />
    <TextView android:id="@+id/authorTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="25sp"
        android:textColor="#f00"
        android:layout_marginRight="5dp"
        />
    <TextView android:id="@+id/priceTxt"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="25sp"
        android:textColor="#fff"
        android:layout_marginRight="5dp"
        />
</LinearLayout>

辛言

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Android学习篇章22-SQLite数据库操作：SQLiteDatabase

DAO:public class BookDao { SQLiteDatabase db=null; String db_path="book.db"; Context context=null; public BookDao(Context context) { this.context=context; } public void createDB() {
复制链接

扫一扫