|
package equ.utils;
/*
操作sqlite数据库，实现对数据库的增删改查功能
JDBC Mysql、sqlite有对应的jar，maven的官网上下载
*/
import equ.model.Equ;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class SqliteDb {
// 连接数据库
private Connection connect() {
// jdbc:subprotocol:subname
// jdbc:数据库类型：数据库相对于工程的路径
String url = "jdbc:sqlite:src/equ/db/equ.db";
try {
// 加载类
Class.forName("org.sqlite.JDBC");
// 连接数据库
Connection conn = DriverManager.getConnection(url);
System.out.println("连接数据库成功");
return conn;
} catch (Exception e) {
System.out.println("连接数据库异常，异常信息为：" + e.getMessage());
}
return null;
}
// 断开数据库
private void disconnect(Connection conn) {
try {
conn.close();
} catch (SQLException e) {
System.out.println("断开数据库异常，异常信息为：" + e.getMessage());
}
}
// 执行sql语句，执行插入、删除、更新类的操作，返回true/false
private boolean executeUpdate(Connection conn,String sql) {
// Statement是执行sql语句的接口
Statement statement = null;
try {
statement = conn.createStatement();
int count = statement.executeUpdate(sql);
System.out.println("执行sql语句成功，影响" + count + "条数据");
statement.close();
return count >= 1;
} catch (SQLException e) {
System.out.println("执行sql语句异常，异常信息为：" + e.getMessage());
}
return false;
}
// 执行sql语句，执行查询类的操作，返回查询的结果
private List<Equ> executeQuery(Connection conn, String sql) {
List<Equ> equs = new ArrayList<>();
Statement statement = null;
try {
statement = conn.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 conn = db.connect();
db.executeUpdate(conn, "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(conn);
}
// 添加设备
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 conn = 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(conn, sql);
db.disconnect(conn);
return bool;
}
// 删除设备
public static boolean deleteEqu(String id) {
// delete from equ where id = '10001';
SqliteDb db = new SqliteDb();
Connection conn = db.connect();
id = String.format("delete from equ where id = '%s'", id);
boolean bool = db.executeUpdate(conn, id);
db.disconnect(conn);
return bool;
}
// 查找设备
public static List<Equ> queryAll(String names) {
// select * from equ;
SqliteDb db = new SqliteDb();
Connection conn = db.connect();
names = String.format("select * from %s", names);
List<Equ> bool = db.executeQuery(conn, names);
return bool;
}
// 根据名字模糊查询
public static List<Equ> queryByName(String name) {
// select * from equ where name like '%笔记本%';
SqliteDb db = new SqliteDb();
Connection conn = db.connect();
name = String.format("select * from equ where name like '%s'", name);
List<Equ> bool = db.executeQuery(conn, name);
db.disconnect(conn);
return bool;
}
// 更新设备信息,根据id修改其他信息
public static boolean updateEqu(Equ equ) {
// update equ set name = '华硕' where id = '10001';
String id = equ.getId();
String name = equ.getName();
String location = equ.getLocation();
SqliteDb db = new SqliteDb();
Connection conn = db.connect();
id = String.format("update equ set name = '%s',location = '%s' where id = '%s'", name,location,id);
boolean bool = db.executeUpdate(conn, id);
db.disconnect(conn);
return bool;
}
// 测试代码，可删除
public static void main(String[] args) {
/*SqliteDb db = new SqliteDb();
Connection conn = db.connect();
db.executeUpdate(conn, "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.executeUpdate(conn, "insert into equ values('10001','14寸笔记本电脑','1018房间','否','2021-1-1','','无')");
List<Equ> equs = db.executeQuery(conn, "select * from equ");
System.out.println(equs);
db.disconnect(conn);*/
SqliteDb.initTable();
/*Equ equ = new Equ("10005", "电视", "1019房间");
SqliteDb.addEqu(equ);
Equ equ1 = new Equ("10006", "冰箱", "1019房间",
"否", "2019-4-5", "无", "");
SqliteDb.addEqu(equ1);*/
// SqliteDb.deleteEqu("10001");
// SqliteDb.queryByName("%电视%");
// SqliteDb.queryAll("equ");
Equ equ = new Equ("10005", "你好", "1021房间");
SqliteDb.updateEqu(equ);
}
}
|
