Questions about Mysql integration with Java

Question

I am creating a system in Netbeans, using the Java language and the Mysql database. I wrote the following code to make the connection between the program and the database:

public class Conexao
{
private static final String DRIVER="com.mysql.jdbc.Driver",URL="jdbc:mysql://localhost:3306/banco_dados",USUARIO="root",SENHA="root";

public static Connection obter()
{
    try
    {
        Class.forName(DRIVER);
        return DriverManager.getConnection(URL,USUARIO,SENHA);
    }
    catch (ClassNotFoundException|SQLException ex)
    {
        JOptionPane.showMessageDialog(null,"Erro ao estabelecer conexão com o MySQL.");
    }
    return null;
}

public static void fechar(Connection c)
{
    try
    {
        if(c!=null)
            c.close();
    }
    catch(SQLException ex)
    {
        JOptionPane.showMessageDialog(null,"Erro ao fechar conexão com o MySQL.");
    }
}

public static void fechar(Connection c, PreparedStatement ps)
{
    fechar(c);
    try
    {
        if(ps!=null)
            ps.close();
    }
    catch(SQLException ex)
    {
        JOptionPane.showMessageDialog(null,"Erro ao fechar conexão com o MySQL.");
    }
}

public static void fechar(Connection c, PreparedStatement ps, ResultSet rs)
{
    fechar(c,ps);
    try
    {
        if(rs!=null)
            rs.close();
    }
    catch(SQLException ex)
    {
        JOptionPane.showMessageDialog(null,"Erro ao fechar conexão com o MySQL.");
    }
}
}

For class Usuario, created the class UsuarioDAO. I will still create methods for reading, editing and deletion.

public class UsuarioDAO
{
public static void inserir(Usuario usuario)
{
    Connection c=Conexao.obter();
    PreparedStatement ps=null;
    try
    {
        ps=c.prepareStatement("insert into usuarios(nome,senha) values(?,?)");
        ps.setString(1,usuario.getNome());
        ps.setBytes(2,usuario.getSenha());
        ps.executeUpdate();
    }
    catch(SQLException ex)
    {
        JOptionPane.showMessageDialog(null,"Erro ao inserir dados no MySQL.");
    }
    finally
    {
        Conexao.fechar(c,ps);
    }
}
}

Regarding the above classes, I have the following doubts:

1. It is necessary to create the class Conexao and the classes DAO manually or there is some functionality in Netbeans that can configure the program’s connection to the database using a form or something like?

2. Is it necessary to open and close the connection every time you make a query or change in the database? Why?

3. It is necessary to create three overloads of the method fechar in class Conexao? Why?

Answer 1

1. It is necessary to create the class Conexaoand the classes DAO manually or there is some functionality in Netbeans that can configure the program’s connection to the database using the graphical user interface?

Getting the graphical interface to know the database or vice versa is bad programming practice, as it goes against the MVC standard. The reason is that details of the database are things of your model, that is, what models the data of your application and business rules. Therefore, the model should not be polluted with display/presentation logic of this data. Therefore, those JOptionPane.showMessageDialog within the DAO is a bad thing to do.

The database is something that is at the base of the model, probably in a layer below that of services and business rules and encapsulated in it. If the model exposes access to the database directly to the visualization layer, this indicates a break of the encapsulation, which implies a bad architecture. Therefore, the graphical user interface should not access your database directly without using the model.

However, if you want to use some automatic generation of Daos in Netbeans, you can take a look at this plugin: http://plugins.netbeans.org/plugin/60454/sql-dal-maker (never used, so I don’t know if it’s good).

2. Is it necessary to open and close the connection every time you make a query or change in the database? Why?

It’s not really necessary, but to control it properly is something significantly laborious. I recommend you always open and close the connection for security and only think about reusing connections when you are already very experienced in Java, because it is very easy to make mistakes with this.

3. It is necessary to create three overloads of the method fechar in class Conexao? Why?

Not required. Use the syntax Try-with-Resources:

public class UsuarioDAO {

    private static final String INSERIR_SQL = 
            "INSERT INTO usuarios (nome, senha) VALUES (?, ?)";

    public static void inserir(Usuario usuario) {
        try (
            Connection c = Conexao.obter();
            PreparedStatement ps = c.prepareStatement(INSERIR_SQL);
        ) {
            ps.setString(1,usuario.getNome());
            ps.setBytes(2,usuario.getSenha());
            ps.executeUpdate();
        } catch (SQLException ex) {
            // A fazer: Não misturar lógica de apresentação com lógica de banco de dados.
            JOptionPane.showMessageDialog(null, "Erro ao inserir dados no MySQL.");
        }
    }
}

With the syntax of Try-with-Resources, compiler inserts a block on its own Finally which closes all objects that are opened at the beginning of Try (in the part between the parentheses). The purpose of this syntax is exactly to rid the programmer of the need and complexity of explicitly closing open resources. To understand more, see my question and your answer.

And here comes your class Conexao revised:

public class Conexao {
    private static final String URL = "jdbc:mysql://localhost:3306/banco_dados";
    private static final String USUARIO = "root";
    private static final String SENHA = "root";

    public static Connection obter() throws SQLException {
        return DriverManager.getConnection(URL, USUARIO, SENHA);
    }
}

In class Conexao, the Class.forName(DRIVER) is no longer required from Java 6. However, if you want to preserve it for some reason, include it in the class as well:

    private static final String DRIVER = "com.mysql.jdbc.Driver";
    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

Initialization should be done ideally when the class is loaded, and if this goes wrong, the application is hopelessly broken (classpath error).

In addition, the method obter() should not attempt to treat the SQLException, and rather relaunch it so that it is treated elsewhere. And return null there is a bad idea, is to ask to have a NullPointerException afterward.