Sqlite连接数据库工具类

• 连接数据库 DataBaseConfig

@Slf4j
public class DataBaseConfig {

    private static final String CLASS_NAME = "org.sqlite.JDBC";
    private static final String DRIVER = "jdbc:sqlite:";
    private static final String DB_URL = "/database/bluetooth.db";

    //程序只加载一次
    static {
        try {
            Class.forName(CLASS_NAME);
            log.info("驱动加载完毕！");
        } catch (ClassNotFoundException exception) {
            exception.printStackTrace();
            log.error("驱动出现异常，请检查驱动！！！");
        }
    }

    public static Connection getConnection() {
        try {
            log.info("Opened database successfully");
            File directory = new File("");// 参数为空
            String courseFile = directory.getCanonicalPath();
            return DriverManager.getConnection(DRIVER + courseFile + DB_URL);
        } catch (SQLException | IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 关闭连接
     */
    public static void getClose(Connection conn, PreparedStatement ps) {
        closes(conn, ps);
    }

    private static void closes(Connection conn, PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection conn, PreparedStatement ps, Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        closes(conn, ps);
    }

    public static void getClose(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        closes(conn, ps);

    }

• 数据实体 UserLogin

@Data
public class UserLogin {

    private Long id;
    private Long userId;
    private String userName;
    private String devicePort;
    private int deviceBaudRate;
    private String loginTime;

    public UserLogin() {
    }

    public UserLogin(Long id, Long userId, String userName, String devicePort, int deviceBaudRate, String loginTime) {
        this.id = id;
        this.userId = userId;
        this.userName = userName;
        this.devicePort = devicePort;
        this.deviceBaudRate = deviceBaudRate;
        this.loginTime = loginTime;
    }
}

• 数据接口 UserLoginMapper

public interface UserLoginMapper {

    List<UserLogin> findAllUsers();

    UserLogin findLogin(Long userId);

    UserLogin findName(String userName);

    int insert(UserLogin userLogin);

    int delete(Long userId);

    int modify(UserLogin userLogin);
}

• 数据接口实现 UserLoginService

@Slf4j
public class UserLoginService implements UserLoginMapper {

    @Override
    public List<UserLogin> findAllUsers() {
        List<UserLogin> userLogins = new ArrayList<>();
        Connection connection = DataBaseConfig.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "SELECT * FROM user_login;";
        try {
            if (connection != null) {
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    UserLogin userLogin = new UserLogin(resultSet.getLong("id"),
                            resultSet.getLong("user_id"),
                            resultSet.getString("user_name"),
                            resultSet.getString("device_port"),
                            resultSet.getInt("device_baud_rate"),
                            resultSet.getString("login_time"));
                    userLogins.add(userLogin);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBaseConfig.getClose(connection, preparedStatement, resultSet);
        }
        log.info("userLogins ====> " + userLogins);
        return userLogins;
    }

    @Override
    public UserLogin findLogin(Long userId) {
        Connection connection = DataBaseConfig.getConnection();
        PreparedStatement prst = null;
        ResultSet resultSet = null;
        UserLogin userLogin = new UserLogin();
        String sql = "SELECT * FROM user_login WHERE user_id = ?;";
        try {
            if (connection != null) {
                prst = connection.prepareStatement(sql);
                prst.setLong(1, userId);
                resultSet = prst.executeQuery();
                if (resultSet.next()) {
                    userLogin = new UserLogin(resultSet.getLong("id"),
                            resultSet.getLong("user_id"),
                            resultSet.getString("user_name"),
                            resultSet.getString("device_port"),
                            resultSet.getInt("device_baud_rate"),
                            resultSet.getString("login_time"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBaseConfig.getClose(connection, prst, resultSet);
        }
        log.info(" userLogin ======>" + userLogin);
        return userLogin;
    }

    @Override
    public UserLogin findName(String userName) {
        Connection connection = DataBaseConfig.getConnection();
        PreparedStatement prst = null;
        ResultSet resultSet = null;
        UserLogin userLogin = new UserLogin();
        String sql = "SELECT * FROM user_login WHERE user_name = ?;";
        try {
            if (connection != null) {
                prst = connection.prepareStatement(sql);
                prst.setString(1, userName);
                resultSet = prst.executeQuery();
                if (resultSet.next()) {
                    userLogin = new UserLogin(resultSet.getLong("id"),
                            resultSet.getLong("user_id"),
                            resultSet.getString("user_name"),
                            resultSet.getString("device_port"),
                            resultSet.getInt("device_baud_rate"),
                            resultSet.getString("login_time"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBaseConfig.getClose(connection, prst, resultSet);
        }
        log.info(" userLogin ======>" + userLogin);
        return userLogin;
    }

    @Override
    public int insert(UserLogin userLogin) {
        Connection connection = DataBaseConfig.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "INSERT INTO user_login(user_id, user_name, device_port, device_baud_rate, login_time) VALUES(?, ?, ?, ?, ?);";
        // 更新的条数
        int result = 0;
        try {
            if (connection != null) {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setLong(1, userLogin.getUserId());
                preparedStatement.setString(2, userLogin.getUserName());
                preparedStatement.setString(3, userLogin.getDevicePort());
                preparedStatement.setInt(4, userLogin.getDeviceBaudRate());
                preparedStatement.setString(5, userLogin.getLoginTime());
                result = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBaseConfig.getClose(connection, preparedStatement);
        }
        log.info("insert success count " + result);
        return result;
    }

    @Override
    public int delete(Long userId) {
        Connection connection = DataBaseConfig.getConnection();
        PreparedStatement preparedStatement = null;
        int result = 0;
        String sql = "DELETE from user_login where user_id = ?;";
        try {
            if (connection != null && userId != 0) {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setLong(1, userId);
                result = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBaseConfig.getClose(connection, preparedStatement);
        }
        log.info("delete success count" + result);
        return result;
    }

    @Override
    public int modify(UserLogin userLogin) {
        Connection connection = DataBaseConfig.getConnection();
        PreparedStatement preparedStatement = null;
        int result = 0;
        String sql = "UPDATE user_login set user_name = ?, device_port = ?, device_baud_rate = ?, login_time = ? where user_id = ?;";
        try {
            if (connection != null) {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, userLogin.getUserName());
                preparedStatement.setString(2, userLogin.getDevicePort());
                preparedStatement.setInt(3, userLogin.getDeviceBaudRate());
                preparedStatement.setString(4, userLogin.getLoginTime());
                preparedStatement.setLong(5, userLogin.getUserId());
                result = preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBaseConfig.getClose(connection, preparedStatement);
        }
        log.info("update success count " + result);
        return result;
    }
}

• 单例工厂 UserLoginFactory

/**
 * 单例工厂
 * Created by YongXin Xue on 2021/08/10 0:29
 */
public class UserLoginFactory {

    private static UserLoginMapper userLoginMapper;

    public static UserLoginMapper getUserLoginMapper(){
        if (userLoginMapper == null){
            userLoginMapper = new UserLoginService();
        }
        return userLoginMapper;
    }

}