Android之Sqlite数据库
数据库访问完毕后,游标必须也记得关闭
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import com.huangzhong.love_power_model.UserInfoDto; import java.util.ArrayList; import java.util.List; import android.app.AlertDialog; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.util.Log; public class UserInfoDao{ private static final int MODE_PRIVATE = 0x0; private static final String DATABASE = "lovepower"; private static SQLiteDatabase db; public static void insert(Context context,UserInfoDto model) { //Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate Cursor cursor; int count = 0; try { db = context.openOrCreateDatabase(DATABASE, MODE_PRIVATE, null); //db.execSQL("drop table UserInfo ;"); String sqlCheckExsit = "select count(*) as c from Sqlite_master where type ='table' and name ='UserInfo' "; cursor = db.rawQuery(sqlCheckExsit, null); if(cursor.moveToNext()){ count = cursor.getInt(0); if(count <= 0){ db.execSQL("Create table UserInfo (Id text not null,UserName text,Birthday text,PhoneNumber text,QQ text,Address text,CreateDate text);"); Log.e("insertSql", "2222"); } } String insertSql = "insert into UserInfo (Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate) " + "values('" + model.getId() + "', '" + model.getUserName()+"','" + model.getBirthday()+"','" + model.getPhoneNumber()+"','" + model.getQQ()+"','" + model.getAddress()+"','" + model.getCreateDate() + "');"; db.execSQL(insertSql); cursor.close(); Log.e("insertSql", "ok"); } finally { db.close(); } } /** * 查询 * @param context 上下文 * @param sql SQl查询语句 * @param fields 字段名集合 * @return 查询结果集 List<String>类型 */ public static List<UserInfoDto> query(Context context, String sql) { Log.e("UserInfoDto", "取数据"); List<UserInfoDto> dataList = new ArrayList<UserInfoDto>(); Cursor cursor;//Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate try { db = context.openOrCreateDatabase(DATABASE, MODE_PRIVATE, null); cursor = db.rawQuery(sql, null); if (cursor != null) { while (cursor.moveToNext()) { UserInfoDto model =new UserInfoDto(); model.setId(cursor.getString(cursor.getColumnIndex("Id"))); model.setUserName(cursor.getString(cursor.getColumnIndex("UserName"))); model.setBirthday(cursor.getString(cursor.getColumnIndex("Birthday"))); model.setPhoneNumber(cursor.getString(cursor.getColumnIndex("PhoneNumber"))); model.setQQ(cursor.getString(cursor.getColumnIndex("QQ"))); model.setAddress(cursor.getString(cursor.getColumnIndex("Address"))); model.setCreateDate(cursor.getString(cursor.getColumnIndex("CreateDate"))); dataList.add(model); } cursor.close();//游标必须关闭 } }catch(Exception e) { new AlertDialog.Builder(context).setIcon(android.R.drawable.ic_dialog_alert).setTitle("数据库连接错误:") .setMessage("数据访问异常。").show(); } finally { db.close(); } return dataList; } /** * 数据记录总条数 * @param context 上下文 * @param sql SQL查询语句 * @return 记录条数 */ public static int getCount(Context context, String sql) { int totalCounty = 0; try { db = context.openOrCreateDatabase(DATABASE, MODE_PRIVATE, null); Cursor cursor = db.rawQuery(sql, null); cursor.moveToFirst(); totalCounty = cursor.getInt(0); cursor.close();//游标必须关闭 } catch (Exception e) { new AlertDialog.Builder(context) .setIcon(android.R.drawable.ic_dialog_alert) .setTitle("数据库连接错误:").setMessage("数据访问异常。").show(); } finally { db.close(); } return totalCounty; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class UserInfoDto { //Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate private String Id; private String UserName; private String Birthday; private String PhoneNumber; private String QQ; private String Address; private String CreateDate; public String getId() { return Id; } public void setId(String id) { Id = id; } public String getUserName() { return UserName; } public void setUserName(String userName) { UserName = userName; } public String getBirthday() { return Birthday; } public void setBirthday(String birthday) { Birthday = birthday; } public String getPhoneNumber() { return PhoneNumber; } public void setPhoneNumber(String phoneNumber) { PhoneNumber = phoneNumber; } public String getQQ() { return QQ; } public void setQQ(String qQ) { QQ = qQ; } public String getAddress() { return Address; } public void setAddress(String address) { Address = address; } public String getCreateDate() { return CreateDate; } public void setCreateDate(String createDate) { CreateDate = createDate; } }