android 44 SQLiteOpenHelper
java
package com.sxt.day06_10; import java.util.ArrayList; import com.sxt.day06_10.entity.StudentBean; import android.os.Bundle; import android.app.Activity; import android.content.Context; import android.view.Menu; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.ListView; import android.widget.TextView; public class MainActivity extends Activity { StudentDBHelper mDao; ListView mlvStudent; ArrayList<StudentBean> mStudents; StudentAdapter mAdapter; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); mDao=new StudentDBHelper(this); initData(); initView(); } private void initView() { mlvStudent=(ListView) findViewById(R.id.lvStudent); mAdapter=new StudentAdapter(mStudents, this, mDao); mlvStudent.setAdapter(mAdapter); } private void initData() { mStudents=mDao.queryAll(); } class StudentAdapter extends BaseAdapter{ ArrayList<StudentBean> students; Context context; StudentDBHelper dao; public StudentAdapter(ArrayList<StudentBean> students, Context context,//Context是Activity的父类 StudentDBHelper dao) { super(); this.students = students; this.context = context; this.dao = dao; } public void remove(int position){//列表的删除 dao.deleteRecord(students.get(position).getId()); students.remove(position); notifyDataSetChanged(); } public void add(StudentBean bean){//列表的增加 students.add(bean); notifyDataSetChanged(); dao.insertRecored(bean); } public void update(int position,StudentBean bean){ students.set(position, bean); notifyDataSetChanged(); dao.updateRecord(bean); } @Override public int getCount() { return students.size(); } @Override public Object getItem(int position) { // TODO Auto-generated method stub return null; } @Override public long getItemId(int position) { // TODO Auto-generated method stub return 0; } @Override public View getView(int position, View convertView, ViewGroup parent) { ViewHolder holder=null; if(convertView==null){ //缓存第一屏的所有convertView（比如10个） convertView=View.inflate(context, R.layout.item_student, null); holder=new ViewHolder(); holder.tvName=(TextView) convertView.findViewById(R.id.tvName); holder.tvSex=(TextView) convertView.findViewById(R.id.tvSex); holder.tvBirthday=(TextView) convertView.findViewById(R.id.tvBirthday); holder.tvHeight=(TextView) convertView.findViewById(R.id.tvHeight); convertView.setTag(holder); }else{ //滚屏的时候获取缓存的所有convertView（比如10个） holder=(ViewHolder) convertView.getTag(); } //修改成新的 StudentBean bean=students.get(position); holder.tvName.setText(bean.getName()); holder.tvSex.setText(bean.getSex()); holder.tvBirthday.setText(bean.getBirthday()); holder.tvHeight.setText(bean.getHeight()+""); return convertView; } class ViewHolder{ TextView tvName,tvSex,tvBirthday,tvHeight; } } }
package com.sxt.day06_10; import java.util.ArrayList; import com.sxt.day06_10.entity.StudentBean; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; public class StudentDBHelper extends SQLiteOpenHelper { private static final String DB_NAME="students.db"; static final String TABLE_NAME="student"; static final String ID="_id"; static final String NAME="name"; static final String SEX="sex"; static final String BIRTHDAY="birthday"; static final String HEIGHT="height"; public StudentDBHelper(Context context) { super(context, DB_NAME, null, 1); } @Override public void onCreate(SQLiteDatabase db) { String sql="create table if not exists "+TABLE_NAME+"(" +ID+" integer primary key autoincrement," +NAME+" varchar(50)," +SEX+" varchar(2)," +BIRTHDAY+" datetext," +HEIGHT+" real)"; db.execSQL(sql); insertRecords(db); } private void insertRecords(SQLiteDatabase db) { ContentValues values=new ContentValues(); values.put(NAME, "张飞"); values.put(SEX, "男"); values.put(BIRTHDAY, "1990-5-5"); values.put(HEIGHT, 1.99); db.insert(TABLE_NAME, null, values); values=new ContentValues(); values.put(NAME, "王菲"); values.put(SEX, "女"); values.put(BIRTHDAY, "1990-8-5"); values.put(HEIGHT, 1.69); db.insert(TABLE_NAME, null, values); values=new ContentValues(); values.put(NAME, "刘亦菲"); values.put(SEX, "女"); values.put(BIRTHDAY, "1991-5-5"); values.put(HEIGHT, 1.7); db.insert(TABLE_NAME, null, values); values=new ContentValues(); values.put(NAME, "李菲"); values.put(SEX, "女"); values.put(BIRTHDAY, "1992-5-5"); values.put(HEIGHT, 1.72); db.insert(TABLE_NAME, null, values); values=new ContentValues(); values.put(NAME, "田菲"); values.put(SEX, "男"); values.put(BIRTHDAY, "1993-5-5"); values.put(HEIGHT, 1.78); db.insert(TABLE_NAME, null, values); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } /** * 向表中增加一条记录 * @param bean * @return */ public int insertRecored(StudentBean bean){ ContentValues values=new ContentValues(); values.put(NAME, bean.getName()); values.put(SEX, bean.getSex()); values.put(BIRTHDAY, bean.getBirthday()); values.put(HEIGHT, bean.getHeight()); SQLiteDatabase db = getWritableDatabase();//getWritableDatabase()是SQLiteOpenHelper的方法 long count = db.insert(TABLE_NAME, null, values); return (int) count;//插入的行数 } /** * 删除指定id的记录 * @param id * @return */ public int deleteRecord(int id){ SQLiteDatabase db = getWritableDatabase(); int count = db.delete(TABLE_NAME, ID+"=?", new String[]{""+id}); return count;//删除的行号 } public int updateRecord(StudentBean bean){ ContentValues values=new ContentValues(); values.put(NAME, bean.getName()); values.put(SEX, bean.getSex()); values.put(BIRTHDAY, bean.getBirthday()); values.put(HEIGHT, bean.getHeight()); SQLiteDatabase db = getWritableDatabase(); int count = db.update(TABLE_NAME, values, ID+"=?", new String[]{bean.getId()+""});//ID+"=?"是条件，new String[]{bean.getId()+""是填充占位符? return count; } public ArrayList<StudentBean> queryAll(){ SQLiteDatabase db = getReadableDatabase(); Cursor c = db.query(TABLE_NAME, null, null, null, null, null, null);//全查询，没有条件 ArrayList<StudentBean> students=new ArrayList<StudentBean>(); while(c.moveToNext()){ int id=c.getInt(0); String name=c.getString(c.getColumnIndex(NAME)); String sex=c.getString(c.getColumnIndex(SEX)); String birthday=c.getString(c.getColumnIndex(BIRTHDAY)); double height=c.getDouble(c.getColumnIndex(HEIGHT)); StudentBean bean=new StudentBean(id, name, sex, birthday, height); students.add(bean); } return students; } public StudentBean queryRecord(int id){ SQLiteDatabase db = getReadableDatabase(); Cursor c = db.query(TABLE_NAME, null, ID+"=?", new String[]{""+id}, null,null,null); if(c.moveToNext()){ String name=c.getString(c.getColumnIndex(NAME)); String sex=c.getString(c.getColumnIndex(SEX)); String birthday=c.getString(c.getColumnIndex(BIRTHDAY)); double height=c.getDouble(c.getColumnIndex(HEIGHT)); StudentBean bean=new StudentBean(id, name, sex, birthday, height); return bean; } return null; } }
main.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent"> <ListView android:id="@+id/lvStudent" android:layout_width="match_parent" android:layout_height="match_parent" android:dividerHeight="5dp"/> </RelativeLayout>
item_student.xml
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="wrap_content" > <TextView android:id="@+id/tvName" android:layout_width="wrap_content" android:layout_height="wrap_content" android:textSize="18sp" android:text="张飞" android:layout_marginLeft="5dp"/> <TextView android:id="@+id/tvSex" android:layout_alignParentRight="true" android:layout_width="wrap_content" android:layout_height="wrap_content" android:textSize="18sp" android:text="男" android:layout_marginLeft="5dp"/> <TextView android:id="@+id/tvBirthday" android:layout_below="@id/tvName" android:layout_alignParentLeft="true" android:layout_width="wrap_content" android:layout_height="wrap_content" android:textSize="18sp" android:text="1990-5-5" android:layout_marginLeft="5dp"/> <TextView android:id="@+id/tvHeight" android:layout_below="@id/tvSex" android:layout_alignParentRight="true" android:layout_width="wrap_content" android:layout_height="wrap_content" android:textSize="18sp" android:text="1.99米" android:layout_marginLeft="5dp"/> </RelativeLayout>
