java - 用Java实现单例数据库连接

Question

我正在尝试用 Java 实现单例数据库连接。我认为我的大部分正确，但是当我尝试在其他类中使用它时，我不断收到 java.lang.NullPointerExceptions。

我收到的错误是在我的 FileManipulation 类中，在我执行 conn.PreparedStatement(query); 的PreparedStatement 行上。我认为 conn 被传递为空，但我不知道为什么或如何修复它。

这是数据库代码:

public class dbConnection {

    static String newLine = System.getProperty("line.separator");

    volatile static dbConnection s;

    public static Connection conn; 
    public static Statement stmt = null;

    private dbConnection() throws SQLException {
        // create single instance of DB connection 
        Properties connectionProps = new Properties();
        connectionProps.put("user", "root");
        connectionProps.put("password", "password");
        // port & db name 
        conn = DriverManager.getConnection("jdbc:mysql://"
                + "localhost" + ":" + "3306" + "/" + "DBNAME",
                connectionProps);
    }

    public Connection getConnection() throws SQLException {
        return conn; 
    }

    public static dbConnection getInstance() throws SQLException {

        if (s == null) {
            synchronized(dbConnection.class) {
                if (s == null) {
                    s = new dbConnection(); 
                    System.out.print("First DB connection created." + newLine); 
            }
        }
    }
        conn = s.getConnection();
        return s; 
    }
}

这是我在类里面尝试使用它的方法:

public class FileManipulation {

    final static String authoritiesTable = "Authorities";

    static Connection conn = null; 
    static Statement stmt = null;
    static BufferedReader reader = null;

    static String position;
    static String username;
    static String password; 

    public static void authorities_check() {
        try {
            Scanner scan = new Scanner( System.in );
            boolean authentication = false;

            while (authentication == false) {
                //get user input 
                System.out.print("Enter your position: ");
                position = scan.nextLine();
                System.out.print("Enter your username: ");
                username = scan.nextLine();
                System.out.print("Enter your password: ");
                password = scan.nextLine();
                if (position != null && username != null && password != null) {
                    authentication = true; 
                }
            }
            String query = "SELECT * FROM " + authoritiesTable + " WHERE position = ?" + " AND " + "username = ?" + " AND " + "password = ?";
            PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(query); 
            pstmt.setString(1, position);
            pstmt.setString(2, username);
            pstmt.setString(3, password);
            ResultSet rs = pstmt.executeQuery(); 

            //if user with position, username, and password exists
            if (rs.next()) { 
                //let them edit the file 
                System.out.println("User authenticated...");
            }
            else {
                System.out.println("Error: Could not authenticate!");
            }
            scan.close();
        }
        catch(SQLException se) {
            se.printStackTrace();
        }
        finally {
            try {
                if(stmt != null || conn != null) {
                    conn.close();
                }
            }
            catch(SQLException se) {
                se.printStackTrace();
            }
        }
    }

    public static void main (String[] args) throws SQLException {
        dbConnection connect = dbConnection.getInstance();
        authorities_check();
    }
}

Answer 1

您用于查询的connection，不是您在dbConnection类中定义的连接，请执行以下操作:

public static void main (String[] args) throws SQLException {
    dbConnection connect = dbConnection.getInstance();
    conn=connect.getConnection();
    authorities_check();
}