如何往数据库中存储mp3,image等文件

講義摘錄之25: 使用SQLite的Blob儲存*.mp3檔案

 

這是一個Android範例，茲說明如下：

 

Step-1: 首先將.mp3檔案放入Project的/res/raw/裡，如下：

 

      

 

       程式一開始執行，建立一個資料庫，含有BLOB欄位，如下之指令：

 

     sql = "create table mySong("

            + "song_no text not null, "

            + "song_mp3 blob );";

         try {

            db.execSQL(sql);

         } catch (SQLException e) {

            Log.e("ERROR", e.toString());

            return;

        }

 

Step-2: 從Project的/res/raw/讀取*.mp3歌曲，然後分段儲存到SQLite的BLOB裡，如下之指令：

 

  InputStream is = getResources().openRawResource(rid);

  int bufSize = 63*1024;

  byte[] buffer  = new byte[bufSize];

  try {

          int size = is.read(buffer);

          while(size >= 0){

     ByteArrayOutputStream out = new ByteArrayOutputStream(size);

               out.write(buffer, 0, size);

               out.flush();

          out.close();

          cv.put("song_mp3", out.toByteArray());

          db.insert("mySong", null, cv);

          size = is.read(buffer);

             }

          } catch (IOException e) {

                     Log.e("ERROR", e.toString());

          }

 

Step-3: 從SQLite的BLOB裡，讀取歌曲並存入

/data/data/com.misoo.SQ01/files/song.mp3，

如下之指令：

 

 

  FileOutputStream os = null;

  try{

           os = openFileOutput("song.mp3", MODE_WORLD_READABLE);

  } catch(FileNotFoundException e){

                     Log.e("ERROR", e.toString());

  }

  byte[] red_buf;                      

  //----------------------------------------

  mOpenHelper = new DatabaseHelper(this);

  SQLiteDatabase db = mOpenHelper.getReadableDatabase();

  String col[] = {"song_no", "song_mp3" };

  cur = db.query("mySong", col, cond, null, null, null, null);

  int k =0;

  cur.moveToFirst();

  try{

             while(!cur.isAfterLast()){

                               red_buf = cur.getBlob(1);

                               os.write(red_buf);

                                 k++;

                               cur.moveToNext();

            }

            os.flush();

            os.close();

            }catch(Exception e){

                      Log.e("ERROR", e.toString());

                 return;

            }

 

Step-4: 使用MediaPlayer將

        /data/data/com.misoo.SQ01/files/song.mp3，

       播放出來，如下之指令：

 

    String path = "/data/data/com.misoo.SQ01/files/song.mp3";

    mPlayer = new MediaPlayer();

    try {

                     mPlayer.setDataSource(path);

                     mPlayer.prepare();

    } catch (IOException e) {

                     e.printStackTrace();

           }

    mPlayer.start();

}

 

其實，BLOB欄位可儲存很大的資料量，在本範例裡，刻意將歌曲切成許多段，逐一存入資料庫裏。其目的只是為了舉例而已。

 

package com.misoo.SQ01;

import java.io.ByteArrayOutputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

 

import android.app.Activity;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.media.MediaPlayer;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.LinearLayout;

 

public class ac01 extends Activity implements OnClickListener{

        private static final String DB_NAME = "mp3Song.db";

           private static final int DB_VERSION = 2;

           private Button btn, btn2, btn3;

           private Cursor cur;

           private MediaPlayer mPlayer;

 

           private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {

            super(context, DB_NAME, null, DB_VERSION);

        }

        @Override

        public void onCreate(SQLiteDatabase db) {

        }

        @Override

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

         }

    }

               

    @Override

    public void onCreate(Bundle icicle) {

        super.onCreate(icicle);

                   LinearLayout layout = new LinearLayout(this);

                     layout.setOrientation(LinearLayout.VERTICAL);

 

                     btn = new Button(this);

                     btn.setId(101);

                     btn.setText("play");

                     btn.setBackgroundResource(R.drawable.heart);

                     btn.setOnClickListener(this);

                     LinearLayout.LayoutParams param

                         = new LinearLayout.LayoutParams(80, 50);

                     param.topMargin = 10;

                     layout.addView(btn, param);

 

                     btn2 = new Button(this);

                     btn2.setId(102);

                     btn2.setText("stop");

                     btn2.setBackgroundResource(R.drawable.heart);

                     btn2.setOnClickListener(this);

                     layout.addView(btn2, param);

 

                     btn3 = new Button(this);

                     btn3.setId(103);

                     btn3.setText("exit");

                     btn3.setBackgroundResource(R.drawable.heart);

                     btn3.setOnClickListener(this);

                     layout.addView(btn3, param);

                     setContentView(layout);

                     setTitle("Saving into SQliteDB...");

        //---------------------------------

        init();

        setTitle("Saved in SQliteDB.");

    }

    private DatabaseHelper mOpenHelper;

    public void init(){

        mOpenHelper = new DatabaseHelper(this);

         SQLiteDatabase db = mOpenHelper.getWritableDatabase();

         //-----------------------------------

         String sql = "drop table mySong";

         try {

           db.execSQL(sql);

         } catch (SQLException e) {

             Log.e("ERROR", e.toString());

         }

         //-----------------------------------

        sql = "create table mySong("

            + "song_no text not null, "

            + "song_mp3 blob );";

         try {

            db.execSQL(sql);

         } catch (SQLException e) {

            Log.e("ERROR", e.toString());

            return;

        }

        //---------------------------------

        SaveOneSong(db,"s01", R.raw.den_li_guing);

       }    

    public void SaveOneSong(SQLiteDatabase db, String key, int rid){

        ContentValues cv = new ContentValues();

        cv.put("song_no", key);

                      

         InputStream is = getResources().openRawResource(rid);

          int bufSize = 63*1024;

          byte[] buffer  = new byte[bufSize];

          try {

                               int size = is.read(buffer);

                               while(size >= 0){

                                          ByteArrayOutputStream out = new ByteArrayOutputStream(size);

                            out.write(buffer, 0, size);

                            out.flush();

                 out.close();

                 cv.put("song_mp3", out.toByteArray());

                 db.insert("mySong", null, cv);

                 size = is.read(buffer);

                               }

                    } catch (IOException e) {

                                Log.e("ERROR", e.toString());

                    }

    }

 

public void play(String cond){

            FileOutputStream os = null;

                      try{

                                 os = openFileOutput("song.mp3", MODE_WORLD_READABLE);

                      } catch(FileNotFoundException e){

                                 Log.e("ERROR", e.toString());

                      }

                byte[] red_buf;                

                      //----------------------------------------

                      mOpenHelper = new DatabaseHelper(this);

         SQLiteDatabase db = mOpenHelper.getReadableDatabase();

                      String col[] = {"song_no", "song_mp3" };

                      cur = db.query("mySong", col, cond, null, null, null, null);

                      int k =0;

                      cur.moveToFirst();

                      try{

                         while(!cur.isAfterLast()){

                               red_buf = cur.getBlob(1);

                               os.write(red_buf);

                                 k++;

                               cur.moveToNext();

                       }

                         os.flush();

                         os.close();

          

                      }catch(Exception e){

                                 Log.e("ERROR", e.toString());

                                 return;

                      }

 

    String path = "/data/data/com.misoo.SQ01/files/song.mp3";

    mPlayer = new MediaPlayer();

    try {

                     mPlayer.setDataSource(path);

                     mPlayer.prepare();

    } catch (IOException e) {

                     e.printStackTrace();

           }

    mPlayer.start();

    }

  public void onClick(View v) {

                     switch (v.getId()) {

                     case 101:

                                String cond = "song_no='s01'";

                                play(cond);

                                break;

                     case 102:

                                stop();

                         break;

                     case 103:

                                stop();

                                finish();

                                break;

                     }

           }

  public void stop() {

             if (mPlayer != null) {

                  mPlayer.stop();

                                mPlayer.release();

                                mPlayer = null;

                     }

  }

}

 

 

 

原文： http://www.android1.net/Topic.aspx?BoardID=11&TopicID=588