Android sqlite数据库简单使用(创建和插入,查询数据)

       不能不说,数据库对我们软件有多重要了,在APP上我们Android 也提供了一个轻便型数据库,只有简单了解一下sql数据库的,应该都可以很方便上手。

         第一步,我们新建

         声明一个MediaSQLiteOpenHelper类去继承SQLiteOpenHelper类,我们在里面用到两个方法。
              onCreate()和onUpgrade()方法,上码:
public class MediaSQLiteOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = "MediaSQLiteOpenHelper";
    private Context mcontext;
    public static final String photo = "create table media_photo("
            + "id integer primary key autoincrement,"
            + "phtoto_upurl text,"
            + "photo_name text,"
            + "photo_uptime text)";
    public static final String video = "create table media_video("
            + "id integer primary key autoincrement,"
            + "video_upurl text,"
            + "video_name text,"
            + "video_uptime text)";

    public MediaSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        this.mcontext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(photo);
        db.execSQL(video);
        Log.i(TAG, "创建成功");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

 

我这边新建了两张表(图片和视频),id自动增长和主键。主要存放他们的一些名称信息和地址等

新建一个类,bean,这是图片的数据类型信息,视频的一样,只是命名不一样。

public class ItemPhotoBean {

    private String tv_photo_name;//照片名
    private String tv_photo_time;//上传时间
    private String upload_path;//原图地址
    private String thumbnail_path;//压缩图

    public ItemPhotoBean() {
    }

    public ItemPhotoBean(String tv_photo_name, String tv_photo_time, String upload_path, String thumbnail_path) {
        this.tv_photo_name = tv_photo_name;
        this.tv_photo_time = tv_photo_time;
        this.upload_path = upload_path;
        this.thumbnail_path = thumbnail_path;
    }

    public String getTv_photo_name() {
        return tv_photo_name;
    }

    public void setTv_photo_name(String tv_photo_name) {
        this.tv_photo_name = tv_photo_name;
    }

    public String getTv_photo_time() {
        return tv_photo_time;
    }

    public void setTv_photo_time(String tv_photo_time) {
        this.tv_photo_time = tv_photo_time;
    }

    public String getUpload_path() {
        return upload_path;
    }

    public void setUpload_path(String upload_path) {
        this.upload_path = upload_path;
    }

    public String getThumbnail_path() {
        return thumbnail_path;
    }

    public void setThumbnail_path(String thumbnail_path) {
        this.thumbnail_path = thumbnail_path;
    }
}

 

//定义

private String phtoaddress, photoname, times;

private MediaSQLiteOpenHelper mediaSQLiteOpenHelper;

private SQLiteDatabase db;

//创建自己数据库

mediaSQLiteOpenHelper = new MediaSQLiteOpenHelper(AddPhotoActivity.this, "数据库名称.adb", null, 3);
db = mediaSQLiteOpenHelper.getWritableDatabase();
//添加到图片列表
ContentValues cv = new ContentValues();
cv.put("phtoto_upurl", phtoaddress);  //手机获取的照片返回路径
cv.put("photo_name", photoname);      //从手机返回路径提取,图片照片名称
cv.put("photo_uptime", times);       //获取设备时间
//插入
db.insert("media_photo", null, cv);
db.close();

 

下面提供一下从手机返回路径提取,图片照片名称的方式(两种方式,一种是后缀名,一种不带后缀名的),具体情况看自己的需求调用。

 

public class GetFileName {
    public String getFileName(String pathandname) {
        /**
         * 仅保留文件名不保留后缀
         */
        int start = pathandname.lastIndexOf("/");
        int end = pathandname.lastIndexOf(".");
        if (start != -1 && end != -1) {
            return pathandname.substring(start + 1, end);
        } else {
            return null;
        }
    }

    /**
     * 保留文件名及后缀
     */
    public String getFileNameWithSuffix(String pathandname) {
        int start = pathandname.lastIndexOf("/");
        if (start != -1) {
            return pathandname.substring(start + 1);
        } else {
            return null;
        }
    }
}

 

我这是带后缀的

//获取图片名
GetFileName getFileName = new GetFileName();
//phtoaddress参数是图片文件路径,最后定义一个photoname 字符串接收输出。
String photoname = getFileName.getFileNameWithSuffix(phtoaddress);

 

获取设备时间的方式获取:

//获取图片添加时间,现在的手机时间,定义一个times字符串接收输出
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");// HH:mm:ss
Date date = new Date(System.currentTimeMillis());
String times = simpleDateFormat.format(date);

有了这些就可以添加一张图片到插入到sqlite数据库了,我这边是没有用sql语句插入,但是ContentValues,这个类帮我们做了很多东西,才看起来简单。

 

最后这是我的一个效果图。

 

我这边插入完成之后,用了一个listview展示出来

        新建一个activity

private MyAdapterItemPhoto adapter = null;
private List list = new ArrayList<>();
MediaSQLiteOpenHelper mediaSQLiteOpenHelper;
private SQLiteDatabase db;
//创建数据库
mediaSQLiteOpenHelper = new MediaSQLiteOpenHelper(PhotoItemActivity.this, "uad360media.db", null, 3);
db = mediaSQLiteOpenHelper.getReadableDatabase();

Cursor cursor = db.rawQuery("select id,phtoto_upurl,photo_name,photo_uptime from media_photo ORDER BY id DESC LIMIT 100", null);
if (cursor != null && cursor.getCount() > 0) {
    while (cursor.moveToNext()) {
        String id = cursor.getString(cursor.getColumnIndex("id"));
        String phtoto_upurl = cursor.getString(cursor.getColumnIndex("phtoto_upurl"));
        String photo_name = cursor.getString(cursor.getColumnIndex("photo_name"));
        String photo_uptime = cursor.getString(cursor.getColumnIndex("photo_uptime"));
        list.add(new ItemPhotoBean(phtoto_upurl, photo_uptime, photo_name, id));
    }

}
cursor.close();
db.close();

查找出来,放到listview的适配器展示

在适配器getview的方法里

final ItemPhotoBean itemphotoBean = list.get(position);
String url = itemphotoBean.getTv_photo_name();
String times = itemphotoBean.getTv_photo_time();
String photoname = itemphotoBean.getUpload_path();
viewHoudler.tv_photo_name.setText(photoname);
viewHoudler.tv_photo_time.setText(times);
//图片加载框架
Glide.with(context).load(url).into(viewHoudler.imageView);


       这是lisview单项的布局页面

最终的效果图:

Android sqlite数据库简单使用(创建和插入,查询数据)_第1张图片

 

 

listview这边主要是提供数据库查询出来,把存进去的照片信息获取出来,

Glide的框架加载图片到imageview,用了一条查询语句,就是

Cursor cursor = db.rawQuery("select id,phtoto_upurl,photo_name,photo_uptime from media_photo ORDER BY id DESC LIMIT 100", null);

                 把数据库表的id最大排序,前100条展示出来,由于时间,毕竟急,很多完整的部分没有放出来,实际的核心代码就是这些了,插入和查询,以后会学习更多sqlite的实用技巧,慢慢分享出来,大家一起交流学习,以上有存在不足的,欢迎评论区,指出来,一起交流一下。谢谢

 

 

你可能感兴趣的