Android编程心得-在Assets文件夹中放入.sql文件实现创建SQlite表的操作

当我们在使用SQLiteOpenHelper时，经常使用db.execSQL(String sql)方法写入对应语句实现创建表的操作，这样的确可以实现业务逻辑。与此同时还有一种更灵活的方法，从assets文件夹下读取对应的.sql文件，然后创建表。

1.首先在工程的assets文件夹下，添加对应的.sql文件

2.配置一个Configuration类，用于保存固定路径变量

**[java]** [view plain](http://blog.csdn.net/yangqicong11/article/details/21396437#)[copy](http://blog.csdn.net/yangqicong11/article/details/21396437#)[![在CODE上查看代码片](https://code.csdn.net/assets/CODE_ico.png)](https://code.csdn.net/snippets/240525)[![派生到我的代码片](https://code.csdn.net/assets/ico_fork.svg)](https://code.csdn.net/snippets/240525/fork)

  <div>
    <embed id="ZeroClipboardMovie_1" src="http://static.blog.csdn.net/scripts/ZeroClipboard/ZeroClipboard.swf" type="application/x-shockwave-flash" width="18" height="18" align="middle" name="ZeroClipboardMovie_1">
    </embed>
  </div>
</div>

- <span class="keyword">public</span> <span class="keyword">class</span> Configuration {

- <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">final</span> String DB_PATH = <span class="string">&#8220;schema&#8221;</span>;

- <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">final</span> String DB_NAME = <span class="string">&#8220;test.db&#8221;</span>;

- <span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="keyword">int</span> DB_VERSION = <span class="number">1</span>;


- 
- }

3.逻辑实现类，executeAssetsSQL方法用于向Assets文件夹对应的路径读取SQL语句然后执行创建操作

  <div>
    <embed id="ZeroClipboardMovie_2" src="http://static.blog.csdn.net/scripts/ZeroClipboard/ZeroClipboard.swf" type="application/x-shockwave-flash" width="18" height="18" align="middle" name="ZeroClipboardMovie_2">
    </embed>
  </div>
</div>

- <span class="keyword">public</span> <span class="keyword">class</span> DBHelper <span class="keyword">extends</span> SQLiteOpenHelper {

- 
- <span class="keyword">private</span> Context mContext;

- 
- <span class="keyword">public</span> DBHelper(Context context, String databaseName,

- CursorFactory factory, <span class="keyword">int</span> version) {

- <span class="keyword">super</span>(context, databaseName, factory, version);

- mContext = context;

- }

- 
- <span class="comment">/**</span>

- <span class="comment">     * 数据库第一次创建时调用</span>

- <span class="comment">     * */</span>

- <span class="annotation">@Override</span>

- <span class="keyword">public</span> <span class="keyword">void</span> onCreate(SQLiteDatabase db) {

- executeAssetsSQL(db, <span class="string">&#8220;schema.sql&#8221;</span>);

- System.out.println(<span class="string">&#8220;创建表&#8221;</span>);

- }

- 
- <span class="comment">/**</span>

- <span class="comment">     * 数据库升级时调用</span>

- <span class="comment">     * */</span>

- <span class="annotation">@Override</span>

- <span class="keyword">public</span> <span class="keyword">void</span> onUpgrade(SQLiteDatabase db, <span class="keyword">int</span> oldVersion, <span class="keyword">int</span> newVersion) {

- <span class="comment">//数据库不升级</span>

- <span class="keyword">if</span> (newVersion <= oldVersion) {

- <span class="keyword">return</span>;

- }

- Configuration.oldVersion = oldVersion;

- 

- <span class="keyword">for</span> (<span class="keyword">int</span> i = <span class="number"></span>; i < changeCnt; i++) {

- <span class="comment">// 依次执行updatei_i+1文件      由1更新到2 [1-2]，2更新到3 [2-3]</span>

- String schemaName = <span class="string">&#8220;update&#8221;</span> + (oldVersion + i) + <span class="string">&#8220;_&#8221;</span>

- + (oldVersion + i + <span class="number">1</span>) + <span class="string">&#8220;.sql&#8221;</span>;

- executeAssetsSQL(db, schemaName);

- }

- }

- 
- <span class="comment">/**</span>

- <span class="comment">     * 读取数据库文件（.sql），并执行sql语句</span>

- <span class="comment">     * */</span>

- <span class="keyword">private</span> <span class="keyword">void</span> executeAssetsSQL(SQLiteDatabase db, String schemaName) {

- BufferedReader in = <span class="keyword">null</span>;

- <span class="keyword">try</span> {

- in = <span class="keyword">new</span> BufferedReader(<span class="keyword">new</span> InputStreamReader(mContext.getAssets()

- .open(Configuration.DB_PATH + <span class="string">&#8220;/&#8221;</span> + schemaName)));

- 
- System.out.println(<span class="string">&#8220;路径:&#8221;</span>+Configuration.DB_PATH + <span class="string">&#8220;/&#8221;</span> + schemaName);

- String line;

- String buffer = <span class="string">&#8220;&#8221;</span>;

- <span class="keyword">while</span> ((line = in.readLine()) != <span class="keyword">null</span>) {

- buffer += line;

- <span class="keyword">if</span> (line.trim().endsWith(<span class="string">&#8220;;&#8221;</span>)) {

- db.execSQL(buffer.replace(<span class="string">&#8220;;&#8221;</span>, <span class="string">&#8220;&#8221;</span>));

- buffer = <span class="string">&#8220;&#8221;</span>;

- }

- }

- } <span class="keyword">catch</span> (IOException e) {

- Log.e(<span class="string">&#8220;db-error&#8221;</span>, e.toString());

- } <span class="keyword">finally</span> {

- <span class="keyword">try</span> {

- <span class="keyword">if</span> (in != <span class="keyword">null</span>)

- in.close();

- } <span class="keyword">catch</span> (IOException e) {

- Log.e(<span class="string">&#8220;db-error&#8221;</span>, e.toString());

- }

- }

- }

- 
- }

💬 评论