import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
 
/**
 * @author asben14[at]outlook (dot) fr 
 * @see for suggestions, comments
 * or donations here is my blog <a href="http://fivesnippets.blogspot.com/">fivesnippets.blogspot.com</a>
 */
public class DBHelper {
 
 public static java.sql.PreparedStatement pst;
 public static ResultSet rs;
 public static java.sql.ResultSetMetaData rsmd;
 public static Connection con;
 
 /**
  * Initializes connection, can be replaced by DBHelper
  * constructor with some changes (make static variables attributes) in order
  * to have multiple connections instantly with many preparedStatement
  * objects
  */
 public static void init() {
  try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e1) {
   e1.printStackTrace();
  }
  String url = "jdbc:mysql://localhost:3306/test";
  String user = "put a username";
  String password = "put your password";
  try {
   con = DriverManager.getConnection(url, user, password);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
 
 public DBHelper() {
  // TODO Auto-generated constructor stub
 }
 
 /**
  * select rows from any table, provided that the table has the same columns as the bean
  * fields, similarity here is about names and types, types need to be
  * mapped correctly check the link bellow 
  * @see <a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html">Java, JDBC and MySQL Types</a>
  * @return Iterator of objects representing selected rows
  */
 public static Iterator getBeans(String condition, Object bean, String table)
   throws SQLException, IllegalArgumentException, SecurityException,
   IllegalAccessException {
  System.out.println("SELECT * FROM " + table + " WHERE " + condition);
  pst = con.prepareStatement("SELECT * FROM " + table + " WHERE "
    + condition);
  rs = pst.executeQuery();
  rsmd = rs.getMetaData();
  ArrayList link = new ArrayList();
  Object element = null;
  try {
   element = bean.getClass().newInstance();
  } catch (InstantiationException e) {
 
  }
  // the critical work is made here, these two loops are fetching data
  // from the resultSet object which contains rows, into the iterator 
  // one row by one bean
  while (rs.next()) {
   rsmd = rs.getMetaData();
   int length = element.getClass().getDeclaredFields().length;
   int length1 = rsmd.getColumnCount();
   for (int i = 0; i < length1; i++) {
    for (int j = 0; j < length; j++) {
     if (element.getClass().getDeclaredFields()[j].getName()
       .equals(rsmd.getColumnName(i + 1))) {
      element.getClass().getDeclaredFields()[j].set(element,
        rs.getObject(i + 1));
     }
    }
   }
   link.add(element);
   try {
    element = bean.getClass().newInstance();
   } catch (InstantiationException e) {
 
   }
  }
  return link.iterator();
 }
 
 /**
  * @return a single row from any table and map it into a bean,
  * provided that it has the same columns as the bean fields,
  * similarity here is about names and types, types need to be mapped
  * correctly
  */
 public static Object getBean(String condition, Object bean, String table)
   throws SQLException, IllegalArgumentException, SecurityException,
   IllegalAccessException {
  System.out.println("SELECT * FROM " + table + " WHERE " + condition);
  pst = con.prepareStatement("SELECT * FROM " + table + " WHERE "
    + condition);
  rs = pst.executeQuery();
  rsmd = rs.getMetaData();
  rs.next();
  int length = bean.getClass().getDeclaredFields().length;
  int length1 = rsmd.getColumnCount();
  if (rs.getRow() == 1) {
   for (int i = 0; i < length1; i++) {
    for (int j = 0; j < length; j++) {
     if (bean.getClass().getDeclaredFields()[j].getName()
       .equals(rsmd.getColumnName(i + 1))) {
      bean.getClass().getDeclaredFields()[j].set(bean,
        rs.getObject(i + 1));
     }
    }
   }
  } else
   return null;
  return bean;
 }
}