网站的差异推广网络营销案例

1、sqlliet简单介绍及相关技术要点

　　A、可以保存任何类型的数据不限长度   但整形主键的话必须为整数
　　只能编写标准sql语句
　　分页和mysql一样
   　imit 5 offset 3  
　　limit 3,5  跳过前面3条

　　B、写一个类继承SQLiteOpenHelper
　　数据库文件所在路径为：应用的包下面的databases

　　最好是少建类  可以少占内存
　   SQLiteDatabase database= openHelp.getWritableDatabase();//对数据要更改,该方法以读和写的方式打开数据库
　　SQLiteDatabase database2=openHelp.getReadableDatabase();//先用可以写的方式打开数据库，如果打开失败再以只读的方式打开数据库

　　C、SqliteDev工具下载地址http://download.csdn.net/source/3302231

package com.tjp.model;

public class Person {

private int personId;
private String name;
public Person() {
super ();
}
public Person( int personId, String name) {
super ();
this .personId = personId;
this .name = name;
}
public int getPersonId() {
return personId;
}
public void setPersonId( int personId) {
this .personId = personId;
}
public String getName() {
return name;
}
public void setName(String name) {
this .name = name;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return " name= " + name + " personId= " + personId;
}
}

package com.tjp.service;

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

public class DBOpenHelp extends SQLiteOpenHelper {

private static final String DBNAME = " tjp.db " ; // 数据库名称
private static final int DBVERSION = 1 ; // 数据版本
public DBOpenHelp(Context context, String name, CursorFactory factory,
int version) {
super (context, name, factory, version); // context 上下文 ，name 数据库名称以db为后缀名 ， factory 游标工厂 version数据库版本号
// TODO Auto-generated constructor stub
}

public DBOpenHelp(Context context) {
super (context, DBNAME, null , DBVERSION);
}

/**
* 数据库第一次创建的时候被调用
*/
@Override
public void onCreate(SQLiteDatabase db) {
String sql = " create table person(personid integer primary key autoincrement,name varchar(20)) " ;
db.execSQL(sql); // 执行有更新行为的sql语句

}

/**
* 在软件升级的时候，当数据库的版本发送改变的时候
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub

}

}

package com.tjp.service;

import java.util.ArrayList;
import java.util.List;

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

import com.tjp.model.Person;

public class PersonService {

private DBOpenHelp openHelp = null ;
private Context context;


public PersonService(Context context) {
this .context = context;
openHelp = new DBOpenHelp(context);
}

public void save(Person person){
SQLiteDatabase database = openHelp.getWritableDatabase(); // 对数据要更改,该方法以读和写的方式打开数据库,有缓存功能
database.execSQL( " insert into person(name) values(?) " , new Object[]{person.getName()});
}

public void update(Person person){
SQLiteDatabase database = openHelp.getWritableDatabase();
database.execSQL( " update person set name=? where personid=? " , new Object[]{person.getName(),person.getPersonId()});
}

public void delete( int personId){
SQLiteDatabase database = openHelp.getWritableDatabase();
database.execSQL( " delete from person where personid=? " , new Object[]{personId});
}

public Person find( int personId){
SQLiteDatabase database = openHelp.getWritableDatabase();
Person person = null ;
Cursor cursor = database.rawQuery( " select * from person where personid=? " , new String[]{String.valueOf(personId).toString()});
if (cursor.moveToFirst()){ // 如果移动成功了表示存在
int personIda = cursor.getInt(cursor.getColumnIndex( " personid " ));
String name = cursor.getString(cursor.getColumnIndex( " name " ));
person = new Person(personIda,name);
}
cursor.close();
return person;
}

public List < Person > getScrollDate( int offerset, int maxResult){
List < Person > persons = new ArrayList < Person > ();
SQLiteDatabase database = openHelp.getWritableDatabase();
String sql = " select * from person limit ?,? " ;
Cursor cursor = database.rawQuery(sql, new String[]{String.valueOf(offerset),String.valueOf(maxResult)});
while (cursor.moveToNext()){
int personIda = cursor.getInt(cursor.getColumnIndex( " personid " ));
String name = cursor.getString(cursor.getColumnIndex( " name " ));
Person person = new Person(personIda,name);
persons.add(person);
}
return persons;
}

public long getCount(){
SQLiteDatabase database = openHelp.getWritableDatabase();
Cursor cursor = database.rawQuery( " select count(*) from person " , null );
cursor.moveToFirst();
int count = cursor.getInt( 0 );
cursor.close();
return count;
}
}

package com.tjp.db;

import java.util.List;

import com.tjp.model.Person;
import com.tjp.service.DBOpenHelp;
import com.tjp.service.PersonService;

import android.test.AndroidTestCase;
import android.util.Log;

public class PersonServiceTest extends AndroidTestCase {

private static final String TAG = " PersonServiceTest " ;
public void testCreateDb() throws Exception{
DBOpenHelp help = new DBOpenHelp( this .getContext());
help.getWritableDatabase(); // 第一次调用该方法创建数据库
}

public void testsave() throws Exception{
PersonService personService = new PersonService( this .getContext());
Person person = new Person( 1 , " 谭建平 " );
personService.save(person);

Person person1 = new Person( 1 , " 谭建平1 " );
personService.save(person1);

Person person2 = new Person( 1 , " 谭建平2 " );
personService.save(person2);

Person person3 = new Person( 1 , " 谭建平3 " );
personService.save(person3);

Person person4 = new Person( 1 , " 谭建平4 " );
personService.save(person4);

Person person5 = new Person( 1 , " 谭建平5 " );
personService.save(person5);

Person person6 = new Person( 1 , " 谭建平6 " );
personService.save(person6);
}
public void testupdate() throws Exception{
PersonService personService = new PersonService( this .getContext());
Person person = new Person( 1 , " 老李 " );
personService.update(person);
}
public void testdelete() throws Exception{
PersonService personService = new PersonService( this .getContext());
personService.delete( 1 );
}
public void tesfind() throws Exception{
PersonService personService = new PersonService( this .getContext());
Person person = personService.find( 1 );
Log.i(TAG, person.getName());
}
public void testscorll() throws Exception{
PersonService personService = new PersonService( this .getContext());
List < Person > persons = personService.getScrollDate( 0 , 2 );
for (Person person : persons){
Log.i(TAG, person.toString());

}
}

public void testcount() throws Exception{
PersonService personService = new PersonService( this .getContext());
Log.i(TAG, personService.getCount() + "" );
}
}