Я действительно застрял с этой проблемой, поэтому я был бы рад, если бы кто-то мог мне помочь!
когда я вхожу в систему через минуту, система выходит из системы, когда я пытаюсь снова войти в систему, я получаю эту ошибку: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: после закрытия соединения операции не разрешены.
Как я могу предотвратить это соединение закрыто. и какой будет лучший пул соединений для этого
NB: я все еще учусь, делаю свой первый CRUD! Использование базы данных:
private static Connection connet;
public static Connection getConnection() {
if( connet != null )
return connet;
InputStream inputStream = DButil.class.getClassLoader().getResourceAsStream( "/db.properties" );
Properties properties = new Properties();
try {
properties.load( inputStream );
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
String userName = properties.getProperty("user");
String password = properties.getProperty("password");
Class.forName(driver);
connet = DriverManager.getConnection(url,userName,password);
} catch (IOException | ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return connet;
}
// connection commit
public static void commit() {
try {
connet.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
// rollback data
public static void rollback() {
if (connet != null) {
try {
connet.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
// close Connection
public static void closeConnection( Connection toBeClosed ) {
if( toBeClosed == null )
return;
try {
toBeClosed.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
Свойства БД:
url = jdbc:mysql://localhost:3306/dbname?autoReconnect=true
driver = com.mysql.jdbc.Driver
user = usernanem
password = password
Реализация Дао:
private Connection connet;
public UsersDaoImplementation()
{
connet=DButil.getConnection();
}
@Override
public void addUser(Users user) {
try {
String query = "INSERT INTO Users (First_Name, Last_Name, Address, Phone_Number, UserName, Password, idRole, Date_of_Birth) VALUES (?,?,?,?,?,?,?,?)";
PreparedStatement preparedStatement = connet.prepareStatement( query );
preparedStatement.setString(1, user.getFirst_Name());
preparedStatement.setString(2, user.getLast_Name());
preparedStatement.setString(3,user.getAddress());
preparedStatement.setInt(4, user.getPhone_Number());
preparedStatement.setString(5, user.getUserName());
preparedStatement.setString(6, user.getPassword());
preparedStatement.setInt(7, user.getIdRole());
//preparedStatement.setDate(8, (Date) user.getDate_of_Birth());
//preparedStatement.setDate(8, (java.sql.Date) user.getDate_of_Birth());
preparedStatement.setDate(8, new java.sql.Date (user.getDate_of_Birth().getTime()));
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void deleteUser(int idUsers) {
try {
String query ="DELETE FROM Users WHERE idUsers = ?";
PreparedStatement preparedStatement = connet.prepareStatement( query );
preparedStatement.setInt(1, idUsers);
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void updateUser(Users user) {
try {
String query = "UPDATE Users SET First_Name=?, Last_Name=?, Address=?, Phone_Number=?, UserName=?, Password=?, idRole=?, Date_of_Birth=?";
PreparedStatement preparedStatement = connet.prepareStatement( query );
preparedStatement.setString(1, user.getFirst_Name());
preparedStatement.setString(2, user.getLast_Name());
preparedStatement.setString(3,user.getAddress());
preparedStatement.setInt(4, user.getPhone_Number());
preparedStatement.setString(5, user.getUserName());
preparedStatement.setString(6, user.getPassword());
preparedStatement.setInt(7, user.getIdRole());
preparedStatement.setDate(8, new java.sql.Date (user.getDate_of_Birth().getTime()));
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public List<Users> getAllUsers() {
List<Users> users = new ArrayList<Users>();
try {
Statement statement = connet.createStatement();
ResultSet resultSet = statement.executeQuery( "SELECT * FROM Users" );
while (resultSet.next())
{
Users user = new Users();
user.setIdUsers(resultSet.getInt("idUsers"));
user.setFirst_Name(resultSet.getString("First_Name"));
user.setLast_Name(resultSet.getString("Last_Name"));
user.setAddress(resultSet.getString("Address"));
user.setPhone_Number(resultSet.getInt("Phone_Number"));
user.setUserName(resultSet.getString("UserName"));
user.setPassword(resultSet.getString("Password"));
user.setIdRole(resultSet.getInt("idRole"));
user.setDate_of_Birth(resultSet.getDate("Date_of_Birth"));
users.add(user);
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
@Override
public Users getUserbyId(int idUsers) {
Users user = new Users();
try {
String query = "SELECT * FROM Users WHERE idUsers=?";
PreparedStatement preparedStatement = connet.prepareStatement( query );
preparedStatement.setInt(1, idUsers);
ResultSet resultSet = preparedStatement.executeQuery();
while( resultSet.next() ) {
user.setIdUsers(resultSet.getInt("idUsers"));
user.setFirst_Name(resultSet.getString("First_Name"));
user.setLast_Name(resultSet.getString("Last_Name"));
user.setAddress(resultSet.getString("Address"));
user.setPhone_Number(resultSet.getInt("Phone_Number"));
user.setUserName(resultSet.getString("UserName"));
user.setPassword(resultSet.getString("Password"));
user.setIdRole(resultSet.getInt("idRole"));
user.setDate_of_Birth(resultSet.getDate("Date_of_Birth"));
}
resultSet.close();
preparedStatement.close();
}
catch (SQLException e) {
e.printStackTrace();
}
return user;
}
@Override
public boolean validate(String UserName, String Password) {
boolean status = false;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
String query = "SELECT * FROM Users WHERE UserName=? and Password=?";
preparedStatement = connet.prepareStatement( query );
preparedStatement.setString(1, UserName);
preparedStatement.setString(2, Password);
resultSet = preparedStatement.executeQuery();
status=resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (connet != null) {
try {
connet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return status;
}
Ошибка :
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1232)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1225)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4104)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4073)
at org.jupiterM.dao.UsersDaoImplementation.validate(UsersDaoImplementation.java:162)
at org.jupiterM.controller.LoginJ.doPost(LoginJ.java:63)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
