Android中SQLite数据库操作（1）——使用SQL语句操作SQLite数据库

下面是最原始的方法，用SQL语句操作数据库。后面的“Android中SQLite数据库操作（2）——SQLiteOpenHelper类”将介绍一种常用的android封装操作SQLite的工具类。

MainActivity.java

package com.example.sqlitetest;

import android.app.Activity;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.widget.CursorAdapter;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;

public class MainActivity extends Activity {
	SQLiteDatabase db;
	Button bn = null;
	ListView listView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        //创建或打开数据库
        //如果my.db3存在则打开该数据库，如果不存在先创建my.db3文件再打开
        /* 
 	      SQLite数据库只是一个文件
  	               从本质上来看，SQLite的数据库操作方式是对文件的操作。
 	    */
        db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().toString() + "my.db3", null);
        listView = (ListView) findViewById(R.id.show);
        bn = (Button) findViewById(R.id.ok);
        bn.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View source) {
				//获取用户输入
				EditText titleEdit = (EditText) findViewById(R.id.title);
				EditText contentEdit = (EditText) findViewById(R.id.content);
				String title = titleEdit.getText().toString();
				String content = contentEdit.getText().toString();
				
				try {
					insertData(db, title, content);
					
					Cursor cursor = db.rawQuery("select * from news_inf", null);
					inflateList(cursor);
				} catch (SQLException se) {
					//执行DDL创建数据表
					db.execSQL("create table news_inf(_id integer" +
					" primary key autoincrement," +
					" news_title varchar(50)," +
					" news_content varchar(255))");
					//执行insert语句插入数据
					insertData(db, title, content);
					//执行查询
					Cursor cursor = db.rawQuery("select * from news_inf", null);
					inflateList(cursor);
				}
			}
		});
    }
    
    private void insertData(SQLiteDatabase db, String title, String content){
    	//执行插入语句
    	db.execSQL("insert into news_inf values(null, ?, ?)", new String[]{title, content});
    }
    
    private void inflateList(Cursor cursor){
    	//填充SimpleCursorAdapter
    	SimpleCursorAdapter adapter = new SimpleCursorAdapter(MainActivity.this, 
    			R.layout.line, cursor, new String[]{"news_title", "news_content"},
    			new int[]{R.id.my_title, R.id.my_content}, 
    			CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
    	listView.setAdapter(adapter);
    }
    
    @Override
    protected void onDestroy() {
    	super.onDestroy();
    	//退出程序时关闭SQLiteDatabase
    	if(db != null && db.isOpen()){
    		db.close();
    	}
    }
}

string.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
	<string name="hello">Hello World, DBTest!</string>
	<string name="app_name">数据库访问测试</string>
	<string name="insert">插入</string>
</resources>

main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:orientation="vertical"
	android:layout_width="fill_parent"
	android:layout_height="fill_parent"
	>
<EditText 
	android:id="@+id/title"
	android:layout_width="fill_parent" 
	android:layout_height="wrap_content" 
	/>
<EditText  
	android:id="@+id/content"
	android:layout_width="fill_parent" 
	android:layout_height="wrap_content" 
	android:lines="2"
	/>	
<Button  
	android:id="@+id/ok"
	android:layout_width="wrap_content" 
	android:layout_height="wrap_content" 
	android:text="@string/insert"
	/>
<ListView  
	android:id="@+id/show"
	android:layout_width="fill_parent" 
	android:layout_height="fill_parent" 
	/>			
</LinearLayout>

line.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:orientation="horizontal"
	android:layout_width="fill_parent"
	android:layout_height="fill_parent"
	>
<EditText 
	android:id="@+id/my_title"
	android:layout_width="wrap_content" 
	android:layout_height="wrap_content" 
	android:width="120px"
	/>
<EditText  
	android:id="@+id/my_content"
	android:layout_width="fill_parent" 
	android:layout_height="wrap_content" 
	/>	
</LinearLayout>

运行中出现了错误

有错误日志可以看到是创建表时有问题，发现是字段之间忘了空格。

最后的运行结果

有关SQLite部分知识可以参考：http://blog.csdn.net/dawanganban/article/details/9832883

源代码下载：http://download.csdn.net/detail/lxq_xsyu/5890745