Doubts about MySQL integration with Java

9

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 , I created the class UsuarioDAO . I'll still create methods for reading, editing, and deleting.

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);
    }
}
}

In relation to the classes above, I have the following doubts:

  • Is it necessary to create the Conexao class and the DAO classes manually or is there any functionality in NetBeans that can configure the connection of the program to the database using a form or something? >

  • Is it necessary to open and close the connection every time you query or change the database? Why?

  • Is it necessary to create three overloads of method fechar in class Conexao ? Why?

  • asked by anonymous 21.12.2016 / 13:56

    1 answer

    10
      
  • Is it necessary to create the Conexao class and the DAO classes manually or is there any functionality in NetBeans that can configure the program's connection to the database using the graphical interface?
  •   

    Making the graphical interface know the database or vice versa is a bad programming practice because it goes against the MVC standard. The reason is that database details are things of your model, that is, what models your application data and business rules. Therefore, the model should not be polluted with the display / presentation logic of this data. Therefore, those JOptionPane.showMessageDialog within 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 database access directly to the view layer, this indicates a break in the encapsulation, which implies a poor architecture. Therefore, the graphical interface should not access your database directly without being through the template.

    However, if you want to use some automatic generation of DAOs in Netbeans, you can take a look at this plugin: link (I've never used it, so I do not know if it's good).

      
  • Is it necessary to open and close the connection whenever you make a query or change in the database? Why?
  •   

    It's not really necessary, but managing it properly is something that is significantly laborious. I recommend you always open and close the connection for security and just think about reusing connections when you are already well experienced in Java, as it is very easy to mess up with this.

      
  • Is it necessary to create three overloads of method fechar in class Conexao ? Why?
  •   

    No need. Use the try-with-resources syntax:

    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 try-with-resources syntax, the compiler automatically inserts a finally block that closes all objects that are opened at the beginning of try (in the part between the parentheses). The purpose of this syntax is precisely to free the programmer from the need and complexity of explicitly closing open resources. To understand more, see this question and answer .

    And here's 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 , Class.forName(DRIVER) is no longer required from Java 6. However, if you want to preserve it for some reason, include this 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 ideally be done when the class is loaded, and if this goes wrong, the application is hopelessly broken (classpath error).

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

        
    21.12.2016 / 14:38