Android项目笔记七：自定义的sqliteClass

短命女 2022-08-23 00:47 135阅读 0赞

1、查询表是否存在；

2、建立表；

3、懒得写了，自己看吧。。。。

public class MyDatabaseHelper extends SQLiteOpenHelper\{  
  
  
final String table\_slave\_config\_name ="SlaveConfig";  
final String table\_slave\_wifi\_name ="SlaveWifi";  
final String table\_note1\_user\_data\_name ="Note1UserData";  
final String table\_note2\_user\_data\_name ="Note2UserData";  
final String table\_note3\_user\_data\_name ="Note3UserData";  
final String table\_note4\_user\_data\_name ="Note4UserData";  
final String table\_note5\_user\_data\_name ="Note5UserData";  
final String table\_note6\_user\_data\_name ="Note6UserData";  
final String table\_note7\_user\_data\_name ="Note7UserData";  
final String table\_note8\_user\_data\_name ="Note8UserData";  
final String table\_name\_buffer\[\]=\{table\_note1\_user\_data\_name,table\_note2\_user\_data\_name,  
table\_note3\_user\_data\_name,table\_note4\_user\_data\_name,  
table\_note5\_user\_data\_name,table\_note6\_user\_data\_name,  
table\_note7\_user\_data\_name,table\_note8\_user\_data\_name\};  
final byte table\_note\_maxNub =8;  
final int table\_slave\_wifi\_maxNub =10;  
//创建客户端配置表，序号 服务器IP 服务器PORT  
final String create\_table\_slave\_config\_name\_aql=  
"create table "+table\_slave\_config\_name+" ( id integer primary key , "+  
"IP text not null , "+  
"PORT integer not null "+  
")";  
//创建客户端连接wifi表，序号 SSID 密码  
final String create\_table\_slave\_wifi\_name\_sql=  
"create table "+table\_slave\_wifi\_name+" ( id integer primary key , "+  
"SSID text not null ,"+  
"PASSWORD text default null , "+  
"unique (SSID) )";  
//创建数据保存表  
//节点一保存数据  
final String create\_table\_note1\_user\_data\_name\_sql=  
"create table "+table\_note1\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
//节点二保存数据  
final String create\_table\_note2\_user\_data\_name\_sql=  
"create table "+table\_note2\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
//节点三保存数据  
final String create\_table\_note3\_user\_data\_name\_sql=  
"create table "+table\_note3\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
//节点四保存数据  
final String create\_table\_note4\_user\_data\_name\_sql=  
"create table "+table\_note4\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
//节点五保存数据  
final String create\_table\_note5\_user\_data\_name\_sql=  
"create table "+table\_note5\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
//节点六保存数据  
final String create\_table\_note6\_user\_data\_name\_sql=  
"create table "+table\_note6\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
//节点七保存数据  
final String create\_table\_note7\_user\_data\_name\_sql=  
"create table "+table\_note7\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
//节点八保存数据  
final String create\_table\_note8\_user\_data\_name\_sql=  
"create table "+table\_note8\_user\_data\_name+" ( id integer primary key , "+  
"indate text not null , "+  
"data\_vale real default null , "+  
"temp\_vale real default null "+  
")";  
  
  
final String create\_table\_SQL\[\]=\{create\_table\_note1\_user\_data\_name\_sql,create\_table\_note2\_user\_data\_name\_sql,  
create\_table\_note3\_user\_data\_name\_sql,create\_table\_note4\_user\_data\_name\_sql,  
create\_table\_note5\_user\_data\_name\_sql,create\_table\_note6\_user\_data\_name\_sql,  
create\_table\_note7\_user\_data\_name\_sql,create\_table\_note8\_user\_data\_name\_sql\};  
  
  
public MyDatabaseHelper(Context context,String name,int version)\{  
super(context,name,null,version);  
\}  
  
  
@Override  
public void onCreate(SQLiteDatabase db)\{  
db.execSQL(create\_table\_slave\_config\_name\_aql);  
db.execSQL(create\_table\_slave\_wifi\_name\_sql);  
for(byte i=0;i<table\_note\_maxNub;i++)\{  
db.execSQL(create\_table\_SQL\[i\]);  
\}  
\}  
  
  
@Override  
public void onUpgrade(SQLiteDatabase db,int oldverion,int newverion)\{  
System.out.println("onUpgrade Called"+oldverion+"----->"+"newberion");  
\}  
  
  
public void my\_Init()\{  
  
  
if(!tabIsExist(table\_slave\_config\_name))\{  
my\_execSQL(create\_table\_slave\_config\_name\_aql);  
\}  
  
  
if(!tabIsExist(table\_slave\_wifi\_name))\{  
my\_execSQL(create\_table\_slave\_wifi\_name\_sql);  
\}  
  
  
for(byte i=0;i<table\_note\_maxNub;i++)\{  
if(!tabIsExist(table\_name\_buffer\[i\]))\{  
my\_execSQL(create\_table\_SQL\[i\]);  
\}  
\}  
\}  
  
  
public boolean tabIsExist(String tabName)\{  
boolean result = false;  
if(tabName == null)\{  
return result;  
\}  
SQLiteDatabase db = null;  
Cursor cursor = null;  
try \{  
db = this.getReadableDatabase();//此this是继承SQLiteOpenHelper类得到的  
String sql = "select count(\*) as c from sqlite\_master where type ='table' and name ='"+tabName.trim()+"'";  
cursor = db.rawQuery(sql, null);  
if(cursor.moveToNext())\{  
int count = cursor.getInt(0);  
if(count>0)\{  
result = true;  
\}  
\}  
cursor.close();  
db.close();  
\} catch (Exception e) \{  
// TODO: handle exception  
e.printStackTrace();  
\}  
return result;  
\}  
  
  
public void my\_execSQL(String sql)\{  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(sql);  
db.close();  
\}  
\}

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*SlaveConfigTable\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
public void my\_SQL\_ADD\_Column\_SlaveConfigTable(String ip,long port)\{  
  
  
if(null == ip)\{  
return;  
\}  
  
  
String insert\_table\_slave\_config\_name\_aql=  
"insert into "+  
table\_slave\_config\_name+  
" ( IP , PORT ) values ( "+  
"'"+ip.trim()+"'" +  
" , "+ port+" )";  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(insert\_table\_slave\_config\_name\_aql);  
db.close();  
\}  
\}  
  
  
public void my\_SQL\_DELETE\_Column\_SlaveConfigTable(String ip,long port)\{  
  
  
if(null == ip)\{  
return;  
\}  
  
  
String delete\_table\_slave\_config\_name\_aql=  
"delete from "+  
table\_slave\_config\_name+  
" where IP = "+  
"'"+ip.trim()+"'" +  
" and PORT = "+ port;  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(delete\_table\_slave\_config\_name\_aql);  
db.close();  
\}  
\}  
  
  
//本机规定：1、服务器端口为333  
//本数据表对于热点有用，对于通过路由连接的无用  
public void my\_SQL\_SELECT\_SlaveConfigTable(String input\_ip)\{  
  
  
\}

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*SlaveWifiTable\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
public void my\_SQL\_ADD\_Column\_SlaveWifiTable(String myssid,String Password)\{  
  
  
if(null == myssid)\{  
return;  
\}  
  
  
if(null == Password)\{  
Password = "null";  
\}  
  
  
String insert\_table\_slave\_config\_name\_aql=  
"insert into "+  
table\_slave\_wifi\_name+  
" ( SSID , PASSWORD ) values ( "+  
"'"+myssid.trim()+"'" +  
" , "+  
"'"+Password.trim()+"'" +" )";  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(insert\_table\_slave\_config\_name\_aql);  
db.close();  
\}  
\}  
  
  
public void my\_SQL\_DELETE\_Column\_SlaveWifiTable(String myssid)\{  
  
  
if(null ==myssid)\{  
return;  
\}  
  
  
String delete\_table\_slave\_config\_name\_aql=  
"delete from "+  
table\_slave\_wifi\_name+  
" where SSID = "+  
"'"+myssid.trim()+"'" ;//+  
/\*" and PORT = "+ port;\*/  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(delete\_table\_slave\_config\_name\_aql);  
db.close();  
\}  
\}  
  
  
//id号从1开始  
public void my\_SQL\_DELETE\_Column\_SlaveWifiTable(int selectID)\{  
  
  
String delete\_table\_slave\_config\_name\_aql=  
"delete from "+  
table\_slave\_wifi\_name+  
" where id = " + selectID;  
  
  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(delete\_table\_slave\_config\_name\_aql);  
db.close();  
\}  
\}  
  
  
  
  
public void my\_SQL\_Updata\_Column\_SlaveWifiTable(String myssid,String Password)\{  
String update\_table\_slave\_config\_name\_aql = null;  
if(null ==myssid)\{  
return;  
\}  
  
  
/\* if(null == Password)\{  
update\_table\_slave\_config\_name\_aql=  
"update "+  
table\_slave\_wifi\_name+  
" set PASSWORD = null"+  
" where SSID = "+  
"'"+myssid.trim()+"'" ;//+  
\*//\*" and PORT = "+ port;\*//\*  
\}else \{  
update\_table\_slave\_config\_name\_aql=  
"update "+  
table\_slave\_wifi\_name+  
" set PASSWORD = "+  
"'"+Password.trim()+"'"+  
" where SSID = "+  
"'"+myssid.trim()+"'" ;//+  
\*//\*" and PORT = "+ port;\*//\*  
\}\*/  
if(null == Password)\{  
Password = "null";  
\}  
update\_table\_slave\_config\_name\_aql=  
"update "+  
table\_slave\_wifi\_name+  
" set PASSWORD = "+  
"'"+Password.trim()+"'"+  
" where SSID = "+  
"'"+myssid.trim()+"'" ;  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(update\_table\_slave\_config\_name\_aql);  
db.close();  
\}  
\}  
public boolean my\_SQL\_SELECT\_SlaveWifiTable\_SSID(String wifi\_ssid)\{  
boolean result = false;  
if(null == wifi\_ssid)\{  
return result;  
\}  
  
  
//名字当中不能有\\" 符号（双引号）  
  
  
  
  
SQLiteDatabase db = null;  
Cursor cursor = null;  
  
  
db = this.getReadableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(null == db)\{  
return result;  
\}  
//count(\*) 表示查询到几条数据，\*表示返回所有内容  
//count(\*) 查询不到时候，返回1  
String sql = "select count(\*) from "+  
table\_slave\_wifi\_name+  
" where SSID = "+  
"'"+wifi\_ssid.trim()+"'";  
cursor = db.rawQuery(sql, null);  
if(null == cursor)\{  
return result;  
\}  
if(cursor.moveToNext())\{  
int count = cursor.getInt(0);  
if(count>0)\{  
result = true;  
\}  
\}  
cursor.close();  
db.close();  
return result;  
\}  
  
  
public String my\_SQL\_SELECT\_SlaveWifiTable\_Password(String wifi\_ssid)\{  
String result = null;  
if(null == wifi\_ssid)\{  
return result;  
\}  
SQLiteDatabase db = null;  
Cursor cursor = null;  
  
  
db = this.getReadableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(null == db)\{  
return result;  
\}  
String sql = "select \* from "+  
table\_slave\_wifi\_name+  
" where SSID = "+  
"'"+wifi\_ssid.trim()+"'";  
cursor = db.rawQuery(sql, null);  
if(null == cursor)\{  
return result;  
\}  
cursor.moveToFirst();  
if(cursor.getCount()> 0)\{  
int nameColumnIndex = cursor.getColumnIndex("PASSWORD");  
if(-1 != nameColumnIndex) \{  
result = cursor.getString(nameColumnIndex);  
\}  
\}  
cursor.close();  
db.close();  
return result;  
\}

public int my\_SQL\_SELECT\_SlaveWifiTable\_ReturnCounts()\{  
int result = 0;  
  
  
SQLiteDatabase db = null;  
Cursor cursor = null;  
  
  
db = this.getReadableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(null == db)\{  
return result;  
\}  
String sql = "select count(\*) from "+ table\_slave\_wifi\_name;  
cursor = db.rawQuery(sql, null);  
if(null == cursor)\{  
return result;  
\}  
result = cursor.getCount()-1;  
cursor.close();  
db.close();  
return result;  
\}  
  
  
/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*NoteTable\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/  
public int my\_SQL\_SELECT\_NoteTable\_ReturnMAXid(byte note)\{  
String noteName;  
int maxID=0;  
if((note <1) || (note>8))\{  
return maxID;  
\}  
noteName = table\_name\_buffer\[note -1\];  
  
  
SQLiteDatabase db = null;  
Cursor cursor = null;  
  
  
db = this.getReadableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(null == db)\{  
return maxID;  
\}  
String sql = "select max(id) from "+ noteName;  
cursor = db.rawQuery(sql, null);  
if(null == cursor)\{  
return maxID;  
\}  
  
  
cursor.moveToFirst();  
if(cursor.getCount()> 0)\{  
int nameColumnIndex = cursor.getColumnIndex("max(id)");  
if(-1 != nameColumnIndex) \{  
maxID = cursor.getInt(nameColumnIndex);  
\}  
\}  
cursor.close();  
db.close();  
return maxID;  
\}  
  
  
  
  
public float\[\] my\_SQL\_SELECT\_NoteTable\_ReturnData(byte note,int selectNub,int offsetNub)\{  
String noteName;  
if((note <1) || (note>8))\{  
return null;  
\}  
noteName = table\_name\_buffer\[note -1\];  
SQLiteDatabase db = null;  
Cursor cursor = null;  
  
  
db = this.getReadableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(null == db)\{  
return null;  
\}  
String sql = "select data\_vale from "+ noteName + " where id limit " + selectNub + " offset " + offsetNub;  
cursor = db.rawQuery(sql, null);  
if(null == cursor)\{  
return null;  
\}  
cursor.moveToFirst();  
int cnt = selectNub;  
if(cursor.getCount() < selectNub)\{  
cnt = cursor.getCount();  
\}  
float\[\] returnData = new float\[cnt\];  
  
  
cursor.moveToFirst();  
if(cursor.getCount()> 0)\{  
int nameColumnIndex = cursor.getColumnIndex("data\_vale");  
if(-1 != nameColumnIndex) \{  
for(int i =0 ;i<cnt;i++)\{  
returnData\[i\] = cursor.getFloat(nameColumnIndex);  
cursor.moveToNext();  
\}  
\}else \{  
returnData = null;  
\}  
\}  
  
  
  
  
return returnData;  
\}  
  
  
public void my\_SQL\_ADD\_NoteTable\_Data(byte note,float dataValue,float tempValue)\{  
String noteName;  
if((note <1) || (note>8))\{  
return;  
\}  
noteName = table\_name\_buffer\[note -1\];  
  
  
String saveTimer ="";  
Calendar sys\_timer=Calendar.getInstance();  
  
  
saveTimer+=sys\_timer.get(Calendar.YEAR)+"-"+sys\_timer.get(Calendar.MONTH)+"-"+sys\_timer.get(Calendar.DATE)+" "+  
sys\_timer.get(Calendar.HOUR\_OF\_DAY)+":"+sys\_timer.get(Calendar.MINUTE) +":"+sys\_timer.get(Calendar.SECOND);  
  
  
  
  
String insert\_table\_slave\_config\_name\_aql=  
"insert into "+  
noteName+  
" ( indate , data\_vale , temp\_vale ) values ( "+  
"'"+saveTimer.trim()+"'"+  
" , "+ dataValue + " , " + tempValue +" )";  
SQLiteDatabase db = null;  
db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的  
if(db != null)\{  
db.execSQL(insert\_table\_slave\_config\_name\_aql);  
db.close();  
\}  
\}  
  
  
\}