Too many Java – MySQL connections

I hate to ask a question that is widely asked on the Internet, but I can't seem to solve it

I started a project after a period of time. After a month of testing, I encountered the "too many connections" error I investigated it and added max_ Connections to "solve" it Then the job

Since then, more and more people began to use it, and it was hit hard again When I am the only user on the website, I enter "show processlist", and about 50 connections that are still open will appear (say "sleep" in the command) Now, I don't know why these are open, but in my code, I check and check each connection, I close

Namely

public int getSiteIdFromName(String name,String company)throws DataAccessException,java.sql.sqlException{

Connection conn = this.getSession().connection();
Statement smt = conn.createStatement();
ResultSet rs=null;
String query="SELECT id FROM site WHERE name='"+name+"' and company_id='"+company+"'";

rs=smt.executeQuery(query);
rs.next();

int id=rs.getInt("id");

rs.close();
smt.close();
conn.close();
return id;
}

Every time I do something else on the website, I open another connection, but I won't close it Is there a problem with my code? If not, what might be the problem?

Solution

Using your method, if you throw any exceptions before calling Conn. Close (), the connection will never be closed You need to get it (along with the statement and result set) in the try block and close it in the finally block Any code in finally will always be executed whether an exception is thrown or not With this, you can ensure that expensive resources are shut down

This is a rewrite:

public int getSiteIdFromName(String name,String company) throws DataAccessException,java.sql.sqlException {
    Connection conn = null;
    Statement smt = null;
    ResultSet rs = null;
    int id = 0;
    try {
        conn = this.getSession().connection();
        smt = conn.createStatement();
        String query = "SELECT id FROM site WHERE name='" + name + "' and company_id='" + company + "'";
        rs = smt.executeQuery(query);
        rs.next();
        id = rs.getInt("id");
    } finally {
        if (rs != null) try { rs.close(); } catch (sqlException logorIgnore) {}
        if (smt != null) try { smt.close(); } catch (sqlException logorIgnore) {}
        if (conn != null) try { conn.close(); } catch (sqlException logorIgnore) {}
    }
    return id;
}

That is, this code is sensitive to SQL injection attacks Use Preparedstatement instead of statement

You can also see:

> Sun Exceptions tutorial: The Finally block > Sun JDBC tutorial: introduction > Sun JDBC tutorial: how to use PreparedStatement > DAO tutorial: how to use basic JDBC code properly