Y en la segunda clase digamos que viene un ABC (Altas, Bajas, Cambios), en la Base de datos Utilizando la clase anterior.
Ademas de los conceptos que manejan estas clases que me gustaria entender cualquier comentario relacionado es bienvenido.
Código PHP:
package samples.restaurant;
import java.util.ArrayList;
import java.sql.*;
public class RestaurantService {
public ArrayList getRestaurantList() {
ArrayList list = new ArrayList();
Connection c=null;
try {
c=ConnectionManager.getConnection();
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM restaurant ORDER BY name");
while (rs.next()) {
list.add(new Restaurant(
rs.getInt("restaurant_id"),
rs.getString("name"),
rs.getString("address"),
rs.getString("city"),
rs.getString("zip"),
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public ArrayList getRestaurantListByArea(double x1, double x2, double y1, double y2) {
ArrayList list = new ArrayList();
Connection c=null;
try {
c=ConnectionManager.getConnection();
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM restaurant WHERE map_x>="+x1+" AND map_x<="+x2+" AND map_y>="+y1+" AND map_y<="+y2+" ORDER BY name");
while (rs.next()) {
list.add(new Restaurant(
rs.getInt("restaurant_id"),
rs.getString("name"),
rs.getString("address"),
rs.getString("city"),
rs.getString("zip"),
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public ArrayList getRestaurantListByCategories(double x1, double x2, double y1, double y2, ArrayList selectedCategories) {
int size=selectedCategories.size();
StringBuffer where=new StringBuffer();
if (size>0) {
where.append("AND (");
for (int i=0; i<size; i++) {
if (i>0) where.append(" OR ");
where.append("category_id="+((Double)selectedCategories.get(i)).intValue());
}
where.append(")");
}
ArrayList list = new ArrayList();
Connection c=null;
try {
c=ConnectionManager.getConnection();
Statement s = c.createStatement();
String sql="SELECT r.restaurant_id, name, address, city, zip, phone, link, image, description, review FROM restaurant as r, restaurant_restaurant_category as rrc WHERE r.restaurant_id=rrc.restaurant_id AND map_x>="+x1+" AND map_x<="+x2+" AND map_y>="+y1+" AND map_y<="+y2+" "+where+" GROUP BY r.restaurant_id, name, address, city, zip, phone, link, image, description, review ORDER BY name";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
list.add(new Restaurant(
rs.getInt("restaurant_id"),
rs.getString("name"),
rs.getString("address"),
rs.getString("city"),
rs.getString("zip"),
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public Restaurant getRestaurant(int restaurantId) {
Restaurant restaurant=null;
Connection c=null;
try {
c=ConnectionManager.getConnection();
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM restaurant WHERE restaurant_id="+restaurantId);
if (rs.next()) {
restaurant=new Restaurant(
rs.getInt("restaurant_id"),
rs.getString("name"),
rs.getString("address"),
rs.getString("city"),
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return restaurant;
}
public void updateRestaurantBundle(Restaurant restaurant, ArrayList categories, ArrayList links) {
try {
updateRestaurant(restaurant);
updateCategories(restaurant.getRestaurantId(), categories);
updateLinks(restaurant.getRestaurantId(), links);
} catch (Exception e) {
e.printStackTrace();
}
}
public void updateCategories(int restaurantId, ArrayList categories) {
Connection c=null;
try {
c=ConnectionManager.getConnection();
Statement s = c.createStatement();
s.execute("DELETE FROM restaurant_restaurant_category WHERE restaurant_id="+restaurantId);
PreparedStatement ps = c.prepareStatement("INSERT INTO restaurant_restaurant_category (restaurant_id, category_id) VALUES (?,?)");
int size=categories.size();
for (int i=0; i<size; i++) {
ps.setInt(1, restaurantId);
ps.setInt(2, ((Double) categories.get(i)).intValue());
ps.execute();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public int addRestaurant(Restaurant restaurant) {
int pk=0;
Connection c=null;
try {
c=ConnectionManager.getConnection();
PreparedStatement ps = c.prepareStatement("INSERT INTO restaurant (name, address, city, zip, phone, link, image, description, review, map_x, map_y) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
ps.setString(1, restaurant.getName());
ps.setString(2, restaurant.getAddress());
Statement s=c.createStatement();
ResultSet rs=s.executeQuery("SELECT last_insert_id() FROM restaurant");
rs.next();
pk=rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return pk;
}
public int addRestaurantBundle(Restaurant restaurant, ArrayList categories, ArrayList links) {
// TO DO: Implement appropriate COMMIT logic
int pk=addRestaurant(restaurant);
this.updateCategories(pk, categories);
this.updateLinks(pk, links);
return pk;
}
public void updateRestaurant(Restaurant restaurant) {
Connection c=null;
try {
c=ConnectionManager.getConnection();
PreparedStatement ps = c.prepareStatement("UPDATE restaurant SET name=?, address=?, city=?, zip=?, phone=?, link=?, image=?, description=?, review=?, map_x=?, map_y=? WHERE restaurant_id=?");
ps.setString(1, restaurant.getName());
ps.setString(2, restaurant.getAddress());
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void deleteRestaurant(int restaurantId) {
Connection c=null;
try {
c=ConnectionManager.getConnection();
PreparedStatement ps = c.prepareStatement("DELETE FROM restaurant WHERE restaurant_id=?");
ps.setInt(1, restaurantId);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
Un saludo,
Ko^Ke