Java 数据库连接 - Sqlite

Java 数据库连接 - Sqlite

PS:
1. 连接依赖库：[sqlite-jdbc-xxx.jar](https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc)(根据连接的数据库版本选择)
2. 支持一次连接执行多次sql语句；
3. 仅本地连接；

使用说明：

public static void main(String[] args) {
    SqliteManager dbManager = SqliteManager.getInstance();
    dbManager.connectDB();
    //执行的sql语句：数据表得存在
    String sql = "select * from M3u8Task";
    test(dbManager.exeQuery(sql));
    //...可执行多条 sql 语句
    //执行完后记得关闭连接
    dbManager.closeDB();
}

//遍历打印返回数据表属性及数值
private static void test(ResultSet rs) {
    ResultSetMetaData rsmd;
    try {
        rsmd = rs.getMetaData();//获取属性名
        String[] arr = new String[0];
        if (rsmd != null) {
            int count = rsmd.getColumnCount();//统计属性个数
            arr = new String[count];
            for (int i = 1; i <= count; i++) {
                arr[i - 1] = rsmd.getColumnName(i);//把属性名输入arr
                System.out.print(rsmd.getColumnName(i) + "  ");//输出属性名
            }
        }
        System.out.println();
        while (rs.next()) {
            for (String s : arr) {
                System.out.print(rs.getString(s) + "  ");
            }
            System.out.println();//控制格式
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

SqliteManager.java

package util;

import java.sql.*;

/**
 * sqlite 数据库连接控制器
 * sqlite-jdbc-3.32.0.1.jar
 */
public class SqliteManager {
    // 静态成员 支持单例模式
    private static SqliteManager instance = null;
    private Connection connection = null;//连接数据库
    private Statement statement = null;//用于执行sql命令
    private String path;//数据库文件路径
    private boolean isConnected;//连接状态

    // 单例模式
    public static SqliteManager getInstance() {
        if (instance == null) {
            synchronized (SqliteManager.class) {
                if (instance == null) {
                    instance = new SqliteManager();
                }
            }
        }
        return instance;
    }

    public SqliteManager() {
        isConnected = false;
        try {
            Class.forName("org.sqlite.JDBC");//加载数据库驱动
        } catch (Exception e) {
            e.printStackTrace();
        }

        //默认地址：程序打包后访问路径有变化
        this.path = Constants.BOOL.IS_RELEASE ? "db/M3u8.sqlite" : "./MFrame/res/db/M3u8.sqlite";
    }

    //1. 连接数据库
    public boolean connectDB() {
        if (CommonUtil.isEmpty(path)) {
            return false;
        }
        if (isConnected) {
            return true;
        }
        try {
            String url = "jdbc:sqlite:" + path;   //定义连接数据库的url(url:访问数据库的URL路径)
            connection = DriverManager.getConnection(url);    //获取数据库连接
            statement = connection.createStatement();//得到Statement实例
            CommonUtil.printLog("数据库连接成功...");
        }
        //捕获异常信息
        catch (SQLException e) {
            e.printStackTrace();
            statement = null;
            isConnected = false;
        }
        return isConnected;
    }

    //2. 关闭连接
    public void closeDB() {
        try {
            if (statement != null) {
                statement.close();
                statement = null;
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        isConnected = false;
        CommonUtil.printLog("关闭数据库连接！");
    }

    //3. 查询操作 -> 返回查询结果
    public ResultSet exeQuery(String sql) {

        ResultSet rs = null;
        try {
            if (statement != null) {
                rs = statement.executeQuery(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    //4. 更新操作 -> 返回查询结果
    //return: (1) the row count for SQL Data Manipulation Language (DML) statements
    //		  (2) 0 for SQL statements that return nothing
    public int exeUpdate(String sql) {
        int result = 0;
        try {
            if (statement != null) {
                result = statement.executeUpdate(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}