db.executeUpdate(coon,"create table if not exists equ(id varchar(8) primary key,name varchar(32),location varchar(32),baoFei varchar(2),buyTime varchar(16),baoFeiTime varchar(16),record varchar(128));");
db.disconnect(coon);
}
//添加设备
public static boolean addEqu(Equ equ){
//获取设备的属性
String id =equ.getId();
String name=equ.getName();
String location =equ.getLocation();
String baoFei=equ.getBaoFei();
String buyTime=equ.getBuyTime();
String baoFeiTime=equ.getBaoFeiTime();
String record = equ.getRecord();
SqliteDb db = new SqliteDb();
Connection coon = db.connect();
String sql=String.format("insert into equ values('%s','%s','%s','%s','%s','%s','%s');",id,name,location,baoFei,buyTime,baoFeiTime,record);
boolean bool =db.executeUpdate(coon,sql);
db.disconnect(coon);
return bool;
}
//删除设备
public static boolean deleteEqu(String id){
SqliteDb db = new SqliteDb();
Connection coon = db.connect();
String sql=String.format("delete from equ where id='%s';",id);
boolean bool =db.executeUpdate(coon,sql);
db.disconnect(coon);
return bool;
}
//查找设备
public static List<Equ> queryAll(){
List<Equ> equs =new ArrayList();
SqliteDb db = new SqliteDb();
Connection coon = db.connect();
String sql="select * from equ;";
equs =db.executeQuery(coon,sql);
db.disconnect(coon);
return equs;
}
//根据名字模糊查找
public static List<Equ> queryByName(String name){
//select * from equ where name like '%'
List<Equ> equs =new ArrayList();
SqliteDb db = new SqliteDb();
Connection coon = db.connect();
equs =db.executeQuery(coon,"select * from equ where name like '%"+name+"%';");
db.disconnect(coon);
return equs;
}
//更新设备信息,根据id修改其他信息
public static boolean updateEqu(Equ equ){
String id =equ.getId();
String name=equ.getName();
String location =equ.getLocation();
String baoFei=equ.getBaoFei();
String buyTime=equ.getBuyTime();
String baoFeiTime=equ.getBaoFeiTime();
String record = equ.getRecord();
SqliteDb db = new SqliteDb();
Connection coon = db.connect();
String sql=String.format("update equ set name='%s',location='%s',baoFei='%s',buyTime='%s',baoFeiTime='%s',record='%s' where id='%s';",name,location,baoFei,buyTime,baoFeiTime,record,id);
boolean bool =db.executeUpdate(coon,sql);
db.disconnect(coon);
return bool;
}
//测试代码
public static void main(String[] args) {
//建表
SqliteDb.initTable();
//添加设备
Equ equ1 = new Equ("10002","电视","1019房间");
SqliteDb.addEqu(equ1);
Equ equ2 = new Equ("10003","路由器","1019房间","否","2019-4-5","无","无");
SqliteDb.addEqu(equ2);
Equ equ3 = new Equ("10004","路由器","1019房间","否","2019-4-5","无","无");
SqliteDb.addEqu(equ3);
//删除
SqliteDb.deleteEqu("10001");
//查询所有
System.out.println(SqliteDb.queryAll());
//模糊查询
System.out.println(SqliteDb.queryByName("路由"));
//更新设备信息,根据id修改其他信息
Equ equ = new Equ("10001","笔记本","1020房间","否","2019-4-5","无","无");