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);
}
}