idea连接数据库类

Question

上面的一段是user连接数据库和一些类方法的代码，下面是我模仿着更改的teacher连接数据库的类方法和代码，但是teacher的这个dao类里面就连接不上数据库报错500

package com.demo.dao1;

import com.demo.ben.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    PreparedStatement ps = null;
    static Connection connection = null;
    ResultSet resultSet = null;
    public List<User> queryAll() throws Exception {

        List<User> user_list = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //先获取connection对象
            connection = DriverManager
                    .getConnection("jdbc:mysql://localhost:3306/gu2","root","");
            //获取真正操作数据的对象
            ps = connection.prepareCall("select * from `user`");
            //执行数据库查询操作
            resultSet = ps.executeQuery();
            //把数据库结果集转成java的List集合
            user_list = new ArrayList<User>();
            while(resultSet.next()){
                User user = new User();
                user.setId((String) resultSet.getObject(1));
                user.setName((String) resultSet.getObject(2));
                user.setUser_class((String) resultSet.getObject(3));
                user.setGender((String) resultSet.getObject(4));
                user.setPhone((String) resultSet.getObject(5));
                user.setPassword((String) resultSet.getObject(6));
                user_list.add(user);
            }

        }catch (Exception e){
            e.printStackTrace();
        }
//        finally {
//            connection.close();
//            ps.close();
//            resultSet.close();
//        }
        return user_list;

    }

    //user register send the information to the database
    public static void insert(String Account, String Password, String Name, String Class, String Gender, String Telephone)throws Exception{

        PreparedStatement prep  = connection.prepareStatement("insert into gu2.user value(?,?,?,?,?,?)");
        prep.setString(1,Account);
        prep.setString(2,Name);
        prep.setString(3,Class);
        prep.setString(4,Gender);
        prep.setString(5,Telephone);
        prep.setString(6,Password);
        prep.execute();
    }

    //user login request the information from database
    public static String[] getUser(String Account) throws SQLException {
        PreparedStatement prep = connection.prepareStatement("select * from gu2.user where User_id=?");
        prep.setString(1,Account);
        prep.execute();
        ResultSet resultSet = prep.executeQuery();
        //get the id from the database
        if (resultSet.next()){
            String UserAccount = resultSet.getString("User_id");
            String UserPassword = resultSet.getString("Password");
            String[] a = new String[]{UserAccount,UserPassword};
            return a;
        }
        //do not get the id and pass from the database
        String[] a = new String[]{"$Yang Yang$","$Yang Yang$"};
        return a;
    }

    //check the input information should be correct
    public static String Rcheck(String Account)throws Exception{
        PreparedStatement prep = connection.prepareStatement("select * from gu2.user where User_id=?");
        prep.setString(1,Account);
        prep.execute();
        ResultSet resultSet = prep.executeQuery();
        if (resultSet.next()){
            String UserAccount = resultSet.getString("User_id");
            String exist= "$Yang Yang$";
            return exist;
        }
        return Account;
    }


}

package com.demo.dao1;

import com.demo.ben.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDao {
    PreparedStatement ps = null;
    static Connection connection = null;
    ResultSet resultSet = null;
    public List<Teacher> queryAll() throws Exception {

        List<Teacher> teacher_list = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //先获取connection对象
            connection = DriverManager
                    .getConnection("jdbc:mysql://localhost:3306/gu2","root","");
            //获取真正操作数据的对象
            ps = connection.prepareCall("select * from `teacher`");
            //执行数据库查询操作
            resultSet = ps.executeQuery();
            //把数据库结果集转成java的List集合
            teacher_list = new ArrayList<Teacher>();
            while(resultSet.next()){
                Teacher teacher = new Teacher();
                teacher.setId((String) resultSet.getObject(1));
                teacher.setName((String) resultSet.getObject(2));
                teacher.setGender((String) resultSet.getObject(3));
                teacher.setPhone((String) resultSet.getObject(4));
                teacher.setPassword((String) resultSet.getObject(5));
                teacher.setTClass((String) resultSet.getObject(6));
                teacher_list.add(teacher);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return teacher_list;
    }

    //teacher register send the information to the database
    public static void insert(String Account, String Password, String Name, String Class, String Gender, String Telephone)throws Exception{

        PreparedStatement prep  = connection.prepareStatement("insert into gu2.teacher value(?,?,?,?,?,?)");
        prep.setString(1,Account);
        prep.setString(2,Name);
        prep.setString(3,Gender);
        prep.setString(4,Telephone);
        prep.setString(5,Password);
        prep.setString(6,Class);
        prep.execute();
    }

    //user login request the information from database
    public static String[] getTeacher(String Account) throws SQLException {
        PreparedStatement prep = connection.prepareStatement("select * from gu2.teacher where Teacher_id=?");
        prep.setString(1,Account);
        prep.execute();
        ResultSet resultSet = prep.executeQuery();
        //get the id from the database
        if (resultSet.next()){
            String TeacherAccount = resultSet.getString("Teacher_id");
            String TeacherPassword = resultSet.getString("Password");
            String[] a = new String[]{TeacherAccount,TeacherPassword};
            return a;
        }
        //do not get the id and pass from the database
        String[] a = new String[]{"$Yang Yang$","$Yang Yang$"};
        return a;
    }

    //check the input information should be correct
    public static String Rcheck(String Account)throws Exception{
        PreparedStatement prep = connection.prepareStatement("select * from gu2.teacher where Teacher_id=?");
        prep.setString(1,Account);
        prep.execute();
        ResultSet resultSet = prep.executeQuery();
        if (resultSet.next()){
            String UserAccount = resultSet.getString("Teacher_id");
            String exist= "$Yang Yang$";
            return exist;
        }
        return Account;
    }
}

Answer 1

• 你 58行那里的 connection 没有赋值呀，还是null；

• 你的连接对象，也只是在 queryAll里面才 赋值的

• 写个数据库工具类，获取连接就调这个工具类吧

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author huazie
 * @version 2.0.0
 * @since 2.0.0
 */
public class DBUtil {

    private DBUtil() {
    }

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // 打印异常
        }
    }

    /**
     * 获取数据库连接对象
     *
     * @param url  数据库URL
     * @param name 用户名
     * @param pwd  密码
     * @return 数据库连接对象
     */
    public static Connection getConnection(String url, String name, String pwd) throws SQLException {
        return DriverManager.getConnection(url, name, pwd);
    }

    /**
     * <p> 释放连接Connection </p>
     *
     * @param conn 数据库连接对象
     */
    private static void closeConnection(Connection conn) {
        try {
            if (null != conn) {
                conn.close();
            }
        } catch (SQLException e) {
            // 打印异常
        }
    }

    /**
     * <p> 释放statement </p>
     *
     * @param statement Statement对象
     */
    private static void closeStatement(Statement statement) {
        try {
            if (null != statement) {
                statement.close();
            }
        } catch (SQLException e) {
            // 打印异常
        }
    }

    /**
     * <p> 释放ResultSet结果集 </p>
     *
     * @param rs 结果集对象
     */
    private static void closeResultSet(ResultSet rs) {
        try {
            if (null != rs) {
                rs.close();
            }
        } catch (SQLException e) {
            // 打印异常
        }
    }

    /**
     * <p> 释放资源 </p>
     *
     * @param conn      数据库连接对象
     * @param statement 数据库状态对象
     * @param rs        数据库结果集对象
     */
    public static void close(Connection conn, Statement statement, ResultSet rs) {
        closeResultSet(rs);
        closeStatement(statement);
        closeConnection(conn);
    }

    /**
     * <p> 释放连接 </p>
     *
     * @param conn 数据库连接对象
     */
    public static void close(Connection conn) {
        closeConnection(conn);
    }

    /**
     * <p> 释放状态 </p>
     *
     * @param statement 数据库状态对象
     */
    public static void close(Statement statement) {
        closeStatement(statement);
    }

    /**
     * <p> 释放结果集 </p>
     *
     * @param rs 数据库结果集对象
     */
    public static void close(ResultSet rs) {
        closeResultSet(rs);
    }
}