CRUD com JDBC

Figura 1: Base de dados
Após ter visto o Exemplo JDBC & MySQL e ter entendido como a conexão com a base de dados funciona, podemos ver como criar uma tabela e manipular a mesma. Para exemplificar criei uma tabela user para fazer as operações de CRUD (Create, Read, Update, Delete), conforme a Figura 1.

Download Projeto eclipse

Seguem as classes.

  • JDBCExample.java
    • Classe Main, aplicação.
package com.mypackage.app;

import java.sql.SQLException;
import java.util.List;

import com.mypackage.dao.Crud;
import com.mypackage.model.User;

public class JDBCExample {

 public static void main(String[] argv) {

  Crud dao = new Crud();

  try {
   dao.createDataBase();
   
   dao.insertUser(1, "user001", "password001");
   dao.insertUser(2, "user002", "password002");
   dao.insertUser(3, "user003", "password003");
   dao.updateUser(3, "user003", "password0033333");
   
   List<User> list = dao.listAll();

   for (User user : list) {
    System.out.println("-> " + user.getIdUser() + " , "
      + user.getLogin() + ", " + user.getPassword());
   }

   dao.deleteUser(1);
   dao.deleteUser(2);
   dao.deleteUser(3);

  } catch (SQLException e) {
   System.out.println(e.getMessage());
  }

 }

}

  • Crud.java
    • Os métodos que acessam o banco de dados se concentram nesta classe.
package com.mypackage.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mypackage.model.User;
import com.mypackage.util.Constants;

public class Crud {

 private void close(Statement statement, Connection dbConnection) {
  try {
   if (statement != null) {
    statement.close();
   }

   if (dbConnection != null) {
    dbConnection.close();
   }
  } catch (SQLException e) {

   System.out.println(e.getMessage());

  }

 }

 public void insertUser(int id, String user, String pass)
   throws SQLException {

  Connection dbConnection = MyConnection.getConnection();
  Statement statement = null;

  String sql = "INSERT INTO user (iduser, login, password) VALUES(" + id
    + ",'" + user + "','" + pass + "')";

  try {
   statement = dbConnection.createStatement();

   System.out.println(sql);

   statement.execute(sql);

   System.out.println("Record is inserted into USER table!");

  } catch (SQLException e) {

   System.out.println(e.getMessage());

  } finally {

   close(statement, dbConnection);

  }

 }

 public List<User> listAll() throws SQLException {

  Connection dbConnection = MyConnection.getConnection();
  Statement statement = null;
  List<User> list = new ArrayList<User>();

  String sql = "SELECT * FROM user";

  try {
   statement = dbConnection.createStatement();

   System.out.println(sql);

   ResultSet rs = statement.executeQuery(sql);

   while (rs.next()) {

    User user = new User();

    user.setIdUser(rs.getInt("iduser"));
    user.setLogin(rs.getString("login"));
    user.setPassword(rs.getString("password"));

    list.add(user);

   }

  } catch (SQLException e) {

   System.out.println(e.getMessage());

  } finally {

   close(statement, dbConnection);

  }

  return list;
 }

 public void deleteUser(int id) throws SQLException {

  Connection dbConnection = MyConnection.getConnection();
  Statement statement = null;

  String sql = "DELETE FROM user WHERE " + "iduser = " + id;
  try {
   statement = dbConnection.createStatement();

   System.out.println(sql);

   statement.execute(sql);

  } catch (SQLException e) {

   System.out.println(e.getMessage());

  } finally {

   close(statement, dbConnection);

  }

 }

 public void updateUser(int id, String user, String pass)
   throws SQLException {

  Connection dbConnection = MyConnection.getConnection();
  Statement statement = null;

  String sql = "UPDATE user SET login='" + user + "', password='" + pass
    + "' WHERE idUser=" + id;

  try {
   statement = dbConnection.createStatement();

   System.out.println(sql);

   statement.executeUpdate(sql);

   System.out.println("Record is updated into USER table!");

  } catch (SQLException e) {

   System.out.println(e.getMessage());

  } finally {

   close(statement, dbConnection);

  }

 }

 public void createDataBase() throws SQLException {

  Connection dbConnection = MyConnection.getConnection();
  Statement statement = null;

  dropDataBase();

  String sqlCreate = "CREATE  TABLE `" + Constants.DB_THEME + "`.`user` (" 
    + "`iduser` INT NOT NULL ,"
    + "`login` VARCHAR(45) NOT NULL ,"
    + "`password` VARCHAR(45) NOT NULL ,"
    + "PRIMARY KEY (`iduser`) )";

  try {
   statement = dbConnection.createStatement();

   statement.execute(sqlCreate);

  } catch (SQLException e) {

   System.out.println(e.getMessage());

  } finally {

   close(statement, dbConnection);

  }

 }

 public void dropDataBase() throws SQLException {

  Connection dbConnection = MyConnection.getConnection();
  Statement statement = null;

  String sqlDrop = "DROP TABLE IF EXISTS user";

  try {
   statement = dbConnection.createStatement();

   statement.execute(sqlDrop);

  } catch (SQLException e) {

   System.out.println(e.getMessage());

  } finally {

   close(statement, dbConnection);

  }

 }

}

  • MyConnection.java
    • Classe de conexão com o MySQL
package com.mypackage.dao;

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

import com.mypackage.util.Constants;

public class MyConnection {

 public static Connection getConnection() {

  Connection connection = null;

  try {

   Class.forName(Constants.DB_DRIVER);

  } catch (ClassNotFoundException e) {

   System.out.println("MySQL JDBC Driver not found!");
   e.printStackTrace();
   return connection;

  }

  try {
   connection = DriverManager.getConnection(Constants.DB_CONNECTION, Constants.DB_USER,
     Constants.DB_PASSWORD);

  } catch (SQLException e) {
   System.out.println("Connection Failed! Check output console.");
   e.printStackTrace();
   return connection;
  }

  return connection;

 }
}

  • User.java
    • Entidade que representa o objeto User tanto no Java como no banco de dados.
package com.mypackage.model;

public class User {

 private int idUser;
 private String login;
 private String password;

 public int getIdUser() {
  return idUser;
 }

 public void setIdUser(int idUser) {
  this.idUser = idUser;
 }

 public String getLogin() {
  return login;
 }

 public void setLogin(String login) {
  this.login = login;
 }

 public String getPassword() {
  return password;
 }

 public void setPassword(String password) {
  this.password = password;
 }

}

  • Constants.java
    • Centralizei todas as constante na mesma classe prara facilitar a manutenção.
package com.mypackage.util;

public class Constants {

 public static final String DB_DRIVER = "com.mysql.jdbc.Driver";
 public static final String DB_THEME = "myTheme";
 public static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/" + DB_THEME;
 public static final String DB_USER = "root";
 public static final String DB_PASSWORD = "root";

}

Cya.

Exemplo JDBC & MySQL

Este post exemplifica como se conectar com uma base de dados MySQL através do JDBC.

1. Download do MySQL Server (SGBD - Sistema Gerenciador do Banco de Dados)
-> http://dev.mysql.com/downloads/mysql/5.1.html


2. Download do MySQL Installer (para windows)
3. Download do MySQL JDBC Driver (Interface responsável por conectar o Java ao MySQL)
-> http://dev.mysql.com/downloads/connector/j/5.1.html

4. Ler o artigo de "Introdução ao JDBC" no GUJ
-> http://www.guj.com.br/articles/7

5. Rode o exemplo a seguir como preferir. Sugiro a criação de um projeto usando a IDE que você domina e coloque o Driver do MySQL como dependência do projeto.
  • 5.1. Testando a Conexão Java JDBC
  • JDBCExample.java
package com.mypackage.example;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCExample {
 
 public static void main(String[] argv) {
 
  System.out.println("MySQL JDBC Connection Test");
 
  try {
 
   Class.forName("com.mysql.jdbc.Driver");
 
  } catch (ClassNotFoundException e) {
 
   System.out.println("MySQL JDBC Driver not found!");
   e.printStackTrace();
   return;
 
  }
 
  System.out.println("MySQL JDBC Driver registred.");
  Connection connection = null;
 
  try {
   connection = DriverManager
     .getConnection("jdbc:mysql://localhost:3306/myTheme",
       "user", "password");
 
  } catch (SQLException e) {
   System.out.println("Connection Failed! Check output console.");
   e.printStackTrace();
   return;
  }
 
  if (connection != null) {
   System.out.println("Done!");
  } else {
   System.out.println("Failed to make connection!");
  }
 }
}

Se a mensagem "Done!" apareceu, podemos seguir em frente, caso contrário, tente identificar o erro ou deixe um comentário. Em um próximo post veremos como fazer acesso aos dados de uma tabela do MySQL.

See you.
 
Rodolfo Chaves © 2010-2012