Para tener acceso a datos tenemos que tener un POJO que nos represente la entidad y un DAO para las operaciones comunes.
package com.trifulcas.dao;
import java.sql.Date;
public class Actor {
private int actor_id;
private String first_name;
private String last_name;
private Date last_update;
public Actor(int actor_id, String first_name, String last_name, Date last_update) {
this.actor_id = actor_id;
this.first_name = first_name;
this.last_name = last_name;
this.last_update = last_update;
}
/**
* @return the actor_id
*/
public int getActor_id() {
return actor_id;
}
/**
* @param actor_id the actor_id to set
*/
public void setActor_id(int actor_id) {
this.actor_id = actor_id;
}
/**
* @return the first_name
*/
public String getFirst_name() {
return first_name;
}
/**
* @param first_name the first_name to set
*/
public void setFirst_name(String first_name) {
this.first_name = first_name;
}
/**
* @return the last_name
*/
public String getLast_name() {
return last_name;
}
/**
* @param last_name the last_name to set
*/
public void setLast_name(String last_name) {
this.last_name = last_name;
}
/**
* @return the last_update
*/
public Date getLast_update() {
return last_update;
}
/**
* @param last_update the last_update to set
*/
public void setLast_update(Date last_update) {
this.last_update = last_update;
}
public String toString() {
return actor_id+" | "+first_name+" | "+last_name+" | "+last_update;
}
}
Para acceder a los datos:
public class ActorDAO {
private Connection con;
private int max_records = 1000;
public ActorDAO() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
} catch (Exception ex) {
System.out.println(ex);
}
}
public Actor getActor(int id) {
Actor res = null;
try {
String sql = "select * from actor where actor_id=?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setInt(1, id);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
res = new Actor(rs.getInt("actor_id"), rs.getString("first_name"), rs.getString("last_name"),
rs.getDate("last_update"));
}
} catch (Exception ex) {
System.out.println(ex);
}
return res;
}
public List<Actor> getActors(int limite) {
List<Actor> res = new ArrayList<Actor>();
try {
String sql = "select * from actor limit ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setInt(1, limite);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Actor temp = new Actor(rs.getInt("actor_id"), rs.getString("first_name"), rs.getString("last_name"),
rs.getDate("last_update"));
res.add(temp);
}
} catch (Exception ex) {
System.out.println(ex);
}
return res;
}
/**
* Devuelve un arraylist de actores cuyo apellido contenga la cadena que le pasamos
* @param cad cadena a buscar
* @return ArrayList de actores
*/
public List<Actor> getActors(String cad) {
List<Actor> res = new ArrayList<Actor>();
try {
String sql = "select * from actor where last_name like ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, '%'+cad+'%');
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Actor temp = new Actor(rs.getInt("actor_id"), rs.getString("first_name"), rs.getString("last_name"),
rs.getDate("last_update"));
res.add(temp);
}
} catch (Exception ex) {
System.out.println(ex);
}
return res;
}
public List<Actor> getActors() {
return getActors(max_records);
}
public boolean addActor(Actor actor) {
try {
String sql = "insert into actor (first_name, last_name) values (?,?)";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, actor.getFirst_name());
stmt.setString(2, actor.getLast_name());
int res = stmt.executeUpdate();
return res == 1;
} catch (Exception ex) {
System.out.println(ex);
}
return false;
}
public boolean updateActor(Actor actor) {
try {
String sql = "update actor set first_name=?, last_name=? where actor_id=?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, actor.getFirst_name());
stmt.setString(2, actor.getLast_name());
stmt.setInt(3, actor.getActor_id());
int res = stmt.executeUpdate();
return res == 1;
} catch (Exception ex) {
System.out.println(ex);
}
return false;
}
public boolean deleteActor(Actor actor) {
try {
if (actor != null) {
return deleteActor(actor.getActor_id());
}
} catch (Exception ex) {
System.out.println(ex);
}
return false;
}
public boolean deleteActor(int actor_id) {
try {
String sql = "delete from actor where actor_id=?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setInt(1, actor_id);
int res = stmt.executeUpdate();
return res == 1;
} catch (Exception ex) {
System.out.println(ex);
}
return false;
}
}
Podemos probarlo en el main de una clase cualquiera:
public static void main(String[] args) {
// He abstraído la base de datos
ActorDAO bd=new ActorDAO();
Actor penelope=bd.getActor(1);
System.out.println(penelope.getFirst_name());
List<Actor> actores=bd.getActors();
for(Actor actor:actores) {
System.out.println(actor);
}
Actor nuevo=new Actor(1,"w","e",null);
}
