Android学习篇章23-SQLite数据库操作：SQLiteOpenHelper-单例模式

数据库操作类：

单例模式

public class MySqliteDbHelper extends  SQLiteOpenHelper{public static final String  DB_NAME="book.db";public static final String  TAB_BOOK="bookinfo"; public static final int  VISION=1;private static MySqliteDbHelper instance=null;//name 数据库名//factory游标工厂//version版本号public MySqliteDbHelper(Context context, String name,CursorFactory factory, int version) {super(context, name, factory, version);}public static MySqliteDbHelper getInstance(Context context){if(instance==null)   instance=new MySqliteDbHelper(context,DB_NAME,null,VISION);return instance; }//这个方法会在数据库第一次访问时创建//一般我们在这个方法中完成建库建表以及加入默认数据的操作@Overridepublic void onCreate(SQLiteDatabase db) {String createBookTab="create table  "+TAB_BOOK+"(_id integer primary key autoincrement," +"bookname ,author,price)";db.execSQL(createBookTab);}//这个方法是在有版本更新   新版本的版本号大于老版本的版本号时执行@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        //把原来老的表删除  创建新的数据库一般来说要先备份老的数据 再重新创建数据库db.execSQL("drop table "+TAB_BOOK);onCreate(db);}}

DAO:

public class BookDao {MySqliteDbHelper  dbhelper=null;String db_path="book.db";Context context=null;public  BookDao(Context context){this.context=context;dbhelper=MySqliteDbHelper.getInstance(context);}public  void closeDB(){if(dbhelper!=null){dbhelper.close();dbhelper=null;}}public int  updateBook(Book book){return 0;}public  Cursor  queryAllBooksByCursor(){return null;}public void delBookById(String  bookid){SQLiteDatabase db=dbhelper.getWritableDatabase();db.delete(dbhelper.TAB_BOOK, "_id="+bookid, null);}public  List<Book>  queryAllBooks(){List<Book>  bookList=new ArrayList<Book>();        SQLiteDatabase db=dbhelper.getWritableDatabase();Cursor c=db.query(dbhelper.TAB_BOOK, null, null, null, null, null, null);    if(c.moveToFirst())    {    do{    Book b=new Book();    b.bookId=c.getInt(c.getColumnIndex("_id"));    b.bookname=c.getString(c.getColumnIndex("bookname"));    b.author=c.getString(c.getColumnIndex("author"));    b.price=c.getFloat(c.getColumnIndex("price"));    bookList.add(b);    }while(c.moveToNext());    }    c.close();    return bookList;}public  Book  queryBookById(String id){return null;}public  void  insertBook(Book book){SQLiteDatabase db=dbhelper.getWritableDatabase();ContentValues values=new ContentValues();values.put("bookname", book.bookname);values.put("author", book.author);values.put("price", book.price);db.insert(dbhelper.TAB_BOOK,"author", values);}}

MainActivity

public class MainActivity extends Activity {EditText  bookIdTxt=null;EditText  bookTxt=null;EditText  authorTxt=null;EditText  priceTxt=null;BookDao  db=null;ListView  listview1=null;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);init();}@Overrideprotected void onPause() {// TODO Auto-generated method stubif(db!=null)db.closeDB();super.onPause();}@Overrideprotected void onResume() {db=new BookDao(this);// TODO Auto-generated method stubsuper.onResume();}@Overrideprotected void onStop() {//关闭数据库db.closeDB();super.onStop();}public  void  clickBtn(View view){  int id=view.getId();  if(id==R.id.btn1)  {  //增加图书信息  addBook();  }else if(id==R.id.btn2)  {  delBookById();  findAllBook2();  }else if(id==R.id.btn3)  {  updateBookById();  }else if(id==R.id.btn4)  {  findBookById();  }else if(id==R.id.btn5)  {  //findAllBookByCursor();  findAllBook2();  }}public void  delBookById(){String bookId=""+bookIdTxt.getText();db.delBookById(bookId);}public void findAllBookByCursor(){}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 findAllBook2(){List<Book> bookList= db.queryAllBooks();mybookadapter adapter=(mybookadapter)listview1.getAdapter();        if(adapter==null)        {  adapter=new mybookadapter(this);  listview1.setAdapter(adapter);        }adapter.setData(bookList);//要通知数据集发生变动adapter.notifyDataSetChanged();}public  void findBookById(){}public void addBook(){String name=""+bookTxt.getText();String author=""+authorTxt.getText();float price=Float.parseFloat(""+priceTxt.getText());Book book=new Book();book.bookname=name;book.author=author;book.price=price;db.insertBook(book);}public  void updateBookById(){}public class mybookadapter extends  BaseAdapter{List<Book> list=new ArrayList<Book>();LayoutInflater inflater=null;Context context;public  mybookadapter(Context context){this.context=context;inflater=LayoutInflater.from(context);}public void setData(List<Book>  list){this.list=list;}@Overridepublic int getCount() {return list.size();}@Overridepublic Object getItem(int position) {return list.get(position);}@Overridepublic long getItemId(int position) {return position;}@Overridepublic View getView(int position, View convertView, ViewGroup parent) {Book book=list.get(position);if(convertView==null){convertView=inflater.inflate(R.layout.line,null);CompnentHolder ch=new CompnentHolder();ch.idTxt=(TextView)convertView.findViewById(R.id.bookIdTxt);    ch.nameTxt=(TextView)convertView.findViewById(R.id.bookTxt);    ch.authorTxt=(TextView)convertView.findViewById(R.id.authorTxt);    ch.priceTxt=(TextView)convertView.findViewById(R.id.priceTxt);                convertView.setTag(ch);    }CompnentHolder ch=(CompnentHolder)convertView.getTag();ch.idTxt.setText(""+book.bookId);ch.nameTxt.setText(book.bookname);ch.authorTxt.setText(book.author);ch.priceTxt.setText(""+book.price);if(position%2==0){  convertView.setBackgroundColor(Color.RED);  ch.idTxt.setTextColor(Color.WHITE);  ch.nameTxt.setTextColor(Color.WHITE);  ch.authorTxt.setTextColor(Color.WHITE);  ch.priceTxt.setTextColor(Color.WHITE);}else{  convertView.setBackgroundColor(Color.WHITE);  ch.idTxt.setTextColor(Color.BLACK);  ch.nameTxt.setTextColor(Color.BLACK);  ch.authorTxt.setTextColor(Color.BLACK);  ch.priceTxt.setTextColor(Color.BLACK);  }return convertView;}}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);}public class   CompnentHolder{public  TextView idTxt;public  TextView nameTxt;public  TextView authorTxt;public  TextView priceTxt;}@Overridepublic 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/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:id="@+id/linelayout"    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="35sp"        android:textColor="#f00"        android:layout_marginRight="5dp"        />    <TextView android:id="@+id/bookTxt"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:textSize="35sp"        android:textColor="#000"        android:layout_marginRight="5dp"        />    <TextView android:id="@+id/authorTxt"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:textSize="35sp"        android:textColor="#f00"        android:layout_marginRight="5dp"        />    <TextView android:id="@+id/priceTxt"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:textSize="35sp"        android:textColor="#fff"        android:layout_marginRight="5dp"        /></LinearLayout>