|
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() {
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();
System.out.println("断开数据库成功");
} catch (SQLException e) {
System.out.println("断开数据量异常，异常信息为：" + e.getMessage());
}
}
// 执行sql语句
private boolean executeUpdate(Connection conn, String 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语句，执行插入、删除、更新类的操作，返回true/false
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());
e.printStackTrace();
}
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),\n" +
"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();
return false;
}
// 查找设备
public static List<Equ> queryAll() {
// selest * from equ;
return null;
}
// 根据名字模糊查找
public static List<Equ> queryByName(String name) {
// select * from equ where name like '%笔记本%';
return null;
}
// 更新设备信息，根据id修改其他信息
public static boolean updateEqu(Equ equ) {
return false;
}
// 测试代码，可删除
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),\n" +
"baoFei varchar(2),buyTime varchar(16),baoFeiTime varchar(16),record varchar(128));");
db.executeUpdate(conn, "insert into equ values ('10002','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);
}
}
|
