SQLiteDatabase 数据库使用

0 SQLiteDatabases数据库特点

一种切入式关系型数据库，支持事务，可使用SQL语言，独立，无需服务。程序内通过类名可访问数据库，程序外不可以访问。

SQLiteDatabases数据库使用方法

1 创建数据库及表，使用 SQLiteOpenHelper 类

 1 public class DBHelper extends SQLiteOpenHelper {
 2 
 3     private static final String DATABASE_NAME = "zc.db";//数据库名称
 4     public static final String TANLE_NAME = "car";//表名
 5     private static final int DATABASE_VERSION = 1;//数据库版本
 6 
 7     public DBHelper(Context context) {
 8         super(context, DATABASE_NAME, null, DATABASE_VERSION);
 9     }
10 
11     //数据库第一次被创建时执行此方法，创建表
12     @Override
13     public void onCreate(SQLiteDatabase db) {
14         db.execSQL("create table if not exists" + TANLE_NAME +
15                 "(_id integer primary key autoincrement ,brandName varchar ,brandId integer ,logo text)");
16     }
17 
18     //如果DATABASE_VERSION改为2，系统发现现有数据库版本不同,即会调用onUpgrade
19     @Override
20     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
21         db.execSQL("ALTER TABLE" + TANLE_NAME + " ADD COLUMN other text");
22     }
23 
24 }

2 操作SQLite数据库表，使用SQLiteDatabase类

SQLiteDatabase 增删改查方法

第一种：execSQL() , rawQuery() ,要求sql语法熟练

第二张：insert()，update()，deleter()，query()

 1 public class DBManager {
 2 
 3     private final DBHelper dbHelper;//DBHelper对象
 4     private final SQLiteDatabase db;//SQLiteDatabase对象
 5 
 6     public DBManager(Context context) {
 7         dbHelper = new DBHelper(context);
 8         db = dbHelper.getWritableDatabase();//真正创建或打开数据库，获取可读写SQLiteDatabase对象
 9     }
10 
11     //添加车辆列表
12     public void addCarList(List<Car> carList) {
13         db.beginTransaction();
14         try {
15             for (Car car : carList) {
16                 add(car);
17             }
18             db.setTransactionSuccessful();
19         } finally {
20             db.endTransaction();
21         }
22     }
23 
24     //新增
25     public void add(Car car) {
26         ContentValues cv = new ContentValues();
27         cv.put("brandId", car.getBrandId());
28         cv.put("brandName", car.getBrandName());
29         cv.put("logo", car.getLogo());
30         //rowid 返回新添记录的行号，与主键id无关
31         long rowid = db.insert(DBHelper.TANLE_NAME, null, cv);
32         db.close();
33     }
34 
35     //删除
36     public void delete(String brandId) {
37         db.delete(DBHelper.TANLE_NAME, "brandId = ? ", new String[]{brandId});
38         db.close();
39     }
40 
41     //修改
42     public void update(Car car) {
43         ContentValues cv = new ContentValues();
44         cv.put("brandName", car.getBrandName());
45         db.update(DBHelper.TANLE_NAME, cv, "brandId = ? ", new String[]{car.getBrandId()});
46         db.close();
47     }
48 
49     //查询
50     public List<Car> queryList() {
51         List<Car> list = new ArrayList<>();
52         Cursor cursor = db.rawQuery("select * from " + DBHelper.TANLE_NAME, null);
53         while (cursor.moveToNext()) {
54             Car car = new Car();
55             car.setBrandId(cursor.getString(cursor.getColumnIndex("brandId")));
56             car.setBrandName(cursor.getString(cursor.getColumnIndex("brandName")));
57             car.setLogo(cursor.getString(cursor.getColumnIndex("logo")));
58             list.add(car);
59         }
60         cursor.close();
61         db.close();
62         return list;
63     }
64 
65     public Car queryCar(String brandId) {
66         Car car = new Car();
67         Cursor cursor = db.rawQuery("select * from " + DBHelper.TANLE_NAME + " where brandId = ? ", new String[]{brandId});
68         while (cursor.moveToNext()) {
69             car.setBrandId(cursor.getString(cursor.getColumnIndex("brandId")));
70             car.setBrandName(cursor.getString(cursor.getColumnIndex("brandName")));
71             car.setLogo(cursor.getString(cursor.getColumnIndex("logo")));
72         }
73         cursor.close();
74         db.close();
75         return car;
76     }
77 
78 }