package equ.utils;
import equ.model.Equ;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
/*
操作sqlite数据库,实现对数据库的增删改查功能。
JDBC Mysql、sqlite有对对应的jar,maven的官网下载
*/
public class SqliteDb {
//连接数据库
private Connection connect(){
//jdbc:subprotocol:subname
//ddbc:数据库类型:数据库相对于工程的路径
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 (ClassNotFoundException | SQLException 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,baoFeiTime,buyTime,record);
equs.add(equ);
}
System.out.println("执行sql语句,查询到"+equs.size()+"条语句");
}
}catch (SQLException e){
System.out.println("执行sql语句异常,异常信息为:"+e.getMessage());
}
return equs;
}
|