Sqlite

[android]代码库

package com.example.sqliteopenhelper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author 锋客
 * 安卓数据库构建
 * 
 */

public class SQLiteOpenHelperDemo extends SQLiteOpenHelper {
	
	private static final String DATABASESNAME="test.db";
	private static final String TABLENAME="test";
	private static final int VERSION=1;
	

	public SQLiteOpenHelperDemo(Context context) {
		super(context, DATABASESNAME, null, VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql="create table "+TABLENAME+"(id integer primary key,name char(12),sex char(2))";
		db.execSQL(sql);
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		String sql="drop table exists "+TABLENAME;
		db.execSQL(sql);
		this.onCreate(db);
		// TODO Auto-generated method stub

	}

	
}
==============================================================================
package com.example.dao;

/**
 * @author 锋客
 * 操作数据库的方法：增删改查
 * 形式：sql语句   SQLiteDatabases提供的方法
 * 查询：SimpleAdapter《——————————》List<Map<String, Object>>；
 *     Adapter《————————————》List<String>；
 */

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SqliteDao {

	private static final String TABLENAME = "test";
	private static SQLiteDatabase db;

	public SqliteDao(SQLiteDatabase db) {
		this.db = db;
		// TODO Auto-generated constructor stub
	}

	/**
	 * 添加
	 * 
	 * @param name
	 * @param sex
	 *            将值传入到方法中，使用ContentValues 存放数据 ContentValues类似于map
	 */
	public void add(String name, String sex) {
		
//		 sql
		 String sql="insert into "+TABLENAME+"(name,sex) values(?,?)";
		 System.out.println("已调用sql");
		 Object args[]=new Object[]{name,sex};
		 db.execSQL(sql,args);
		 db.close();
		 System.out.println("已添加");

//		ContentValues cvs = new ContentValues();
//		cvs.put("name", name);
//		cvs.put("sex", sex);
//		db.insert(TABLENAME, null, cvs);
//		db.close();

	}

	/**
	 * 修改
	 * 
	 * @param id
	 * @param name
	 * @param sex
	 *            将值传入到方法中，使用Sting【】 a【】={}，存放更新条件； contentvalues存放跟新内容；
	 *            update（表名，更新内容，条件，条件的值）
	 */
	public void update(int id, String name, String sex) {
		
		// sql
		// String sql="update "+TABLENAME+" set name=?,sex=? where id=?";
		// Object args[]=new Object[]{name,sex,id};
		// db.execSQL(sql,args);
		// db.close();
		
		ContentValues cvs = new ContentValues();
		cvs.put("name", name);
		cvs.put("sex", sex);
		String a[] = { String.valueOf(id) };
		db.update(TABLENAME, cvs, "id=?", a);
		db.close();

	}

	/**
	 * 删除
	 * 
	 * @param id
	 *            传入删除的id值，使用String【】 a【】={}；存放删除的条件值； delete（表名，条件，条件值）
	 * 
	 */

	public void delete(int id) {
		
		// sql
		// String sql="delete from test where id="+id+"";
		// db.execSQL(sql);
		// db.close();
		
		String a[] = { String.valueOf(id) };
		db.delete(TABLENAME, "id=?", a);
		db.close();

	}

	/**
	 * adapter查询
	 * 
	 * @return 游标，默认在最后的值 使用query方法进行查询 使用List<String> 储存结果
	 */
	public List<String> select() {
		
		// sql
		// String sql="select*from "+TABLENAME;
		// Cursor cursor=db.rawQuery(sql, null);

		List<String> list = new ArrayList<String>();
		String columns[] = new String[] { "id", "name", "sex" };
		Cursor cursor = db.query(TABLENAME, columns, null, null, null, null,
				null, null);
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			list.add("[" + cursor.getInt(0) + "]" + "" + cursor.getString(1)
					+ "" + cursor.getString(2));
			System.out.println("list已成功加入信息 " + "{" + cursor.getInt(0) + "}");
		}
		return list;
	}

	/**
	 * SimpleAdapter
	 * 
	 * @return 游标，默认在最后的值 使用query方法进行查询 使用List<Map<String, Object>> 储存结果;
	 *         注意类型的对应；
	 */

	public List<Map<String, Object>> selectSimpleAdapter() {
		
		// sql
		// String sql="select*from "+TABLENAME;
		// Cursor cursor=db.rawQuery(sql, null);
		
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String columns[] = new String[] { "id", "name", "sex" };
		Cursor cursor = db.query(TABLENAME, columns, null, null, null, null,
				null);
		//共有部分
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("id", String.valueOf(cursor.getInt(0)));
			map.put("name", String.valueOf(cursor.getString(1)));
			map.put("birthday", String.valueOf(cursor.getString(2)));
			list.add(map);
			System.out.println("list已成功加入信息 " + "{" + cursor.getInt(0) + "}");
		}
		return list;
	}

}
=====================================================================================
package com.example.sqlitetest;

/**
 * @author 锋客
 * 
 * 主控制class
 */

import com.example.dao.SqliteDao;
import com.example.sqliteopenhelper.SQLiteOpenHelperDemo;
import android.support.v7.app.ActionBarActivity;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Adapter;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.SimpleAdapter;

public class MainActivity extends ActionBarActivity {
	private SQLiteOpenHelper demo;
	private Button zengjia,shanchu,xiugai,chaxun1,chaxun2;
	private LinearLayout linearLayout;
	private ListView listView;
	

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
        listView=(ListView) findViewById(R.id.main_listview);
        linearLayout=(LinearLayout)findViewById(R.id.linearlayout_listview);
		demo = new SQLiteOpenHelperDemo(this);
		System.out.println(demo.hashCode());
		System.out.println("已创建");
		
		
	}

	public void addTest(View v) {
		System.out.println("已调用");
		SqliteDao dao = new SqliteDao(demo.getWritableDatabase());
		System.out.println("已调用11");
		dao.add("fengke", "男");

	}
	
	public void delete(View v){
		SqliteDao dao = new SqliteDao(demo.getWritableDatabase());
		dao.delete(6);
	}
	
	public void update(View v){
		SqliteDao dao = new SqliteDao(demo.getWritableDatabase());
		dao.update(8, "laosong", "女");
	}

	/**
	 * {@link ArrayAdapter}
	 *  查询的结果为List<String> 创建类的方法：上下文，风格，数据（查询结果）
	 */
	public void selectAll(View v) {
		SqliteDao dao = new SqliteDao(demo.getWritableDatabase());
		dao.select();
		ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
				android.R.layout.simple_list_item_activated_1, dao.select());
		 ListView listview =new  ListView(this);
    	 listview.setAdapter(adapter);
    	 linearLayout.addView(listview);

	}

	/**
	 * {@link SimpleAdapter}
	 * 
	 *  创建类的方法：上下文，数据，自定义布局，new String【】{字段名}，new
	 * int【】{自定义布局的id}
	 */
	public void selectAllSimple(View v) {
		SqliteDao dao = new SqliteDao(demo.getWritableDatabase());
		dao.selectSimpleAdapter();
		SimpleAdapter adapter = new SimpleAdapter(this,
				dao.selectSimpleAdapter(), R.layout.user, new String[] { "id",
						"name", "sex" }, new int[] { R.id.id_1, R.id.id_2,
						R.id.id_3 });
		listView.setAdapter(adapter);

	}

}