java_刘国平_2021.03.15 - 菁英科技（卓目鸟学苑）- 专注软件测试菁英教育

package equ.utils;
import equ.model.Equ;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/*
操作sqlite数据库，实现对数据库的增删改查功能
JDBC Mysql、sqlite有对应的jar, maven的官网上下载。
*/
public class SqliteDb {
//连接数据库
private Connection connect(){
//jdbc:subprotocol数据库类型:subname数据库相对于工程的路径
String url = "jdbc:sqlite:src/equ/db/equ.db";
try{
//加载类
Class.forName("org.sqlite.JDBC");
//连接数据库
Connection coon = DriverManager.getConnection(url);
System.out.println("连接数据库成功");
return coon;
}catch (Exception e){
System.out.println("连接数据库异常，异常信息为："+e.getMessage());
}
return null;
}
//断开数据库连接
private void disconnect(Connection coon){
try{
coon.close();
}catch (SQLException e){
System.out.println("断开数据库异常，异常信息为："+e.getMessage());
}
}
//执行sql语句,执行插入、删除、更新类的操作，返回true/false
private boolean executeUpdate(Connection coon,String sql){
//Statement是执行sql语句的接口
Statement statement=null;
try {
statement=coon.createStatement();
int count=statement.executeUpdate(sql);
System.out.println("执行sql语句成功，影响"+count+"条数据");
statement.close();//return之前释放
return count>=1;
}catch (SQLException e){
System.out.println("执行sql语句异常，异常信息为："+e.getMessage());
}
return false;
}
//执行sql语句,执行查询类的操作，返回查询的结果
private List<Equ> executeQuery(Connection coon,String sql){
List<Equ> equs = new ArrayList<>();
Statement statement=null;
try{
statement=coon.createStatement();
ResultSet rs =statement.executeQuery(sql);
if(rs !=null){//查询结果不为空
while(rs.next()){
//根据列名获取每一列的值
String id=rs.getString("id");
String name=rs.getString("name");
String location=rs.getString("location");
String baoFei=rs.getString("baoFei");
String buyTime=rs.getString("buyTime");
String baoFeiTime=rs.getString("baoFeiTime");
String record=rs.getString("record");
//根据获取的信息构造Equ对象
Equ equ = new Equ(id,name,location,baoFei,buyTime,baoFeiTime,record);
equs.add(equ);
}
System.out.println("执行sql语句，查询到"+equs.size()+"条数据");
}
}catch (SQLException e){
System.out.println("执行sql语句异常，异常信息为："+e.getMessage());
}
return equs;
}
//创建表
public static void initTable(){
SqliteDb db = new SqliteDb();
Connection coon = db.connect();
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","无","无");
SqliteDb.updateEqu(equ);

复制代码