Posted By

prgrmmraben on 09/16/14


Tagged

mysql map iterator rows beans


Versions (?)

Map Mysql tables to java beans, by a one class


 / Published in: Java
 

URL: http://fivesnippets.blogspot.com/2014/09/map-mysql-tables-to-java-beans-by.html

DBHelper can retrieve Mysql tables into java objects, without any external libraries or a mapping configuration file. note: please test before use, then use at your own risk.

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. import java.util.ArrayList;
  6. import java.util.Iterator;
  7.  
  8. /**
  9.  * @author asben14[at]outlook (dot) fr
  10.  * @see for suggestions, comments
  11.  * or donations here is my blog <a href="http://fivesnippets.blogspot.com/">fivesnippets.blogspot.com</a>
  12.  */
  13. public class DBHelper {
  14.  
  15. public static java.sql.PreparedStatement pst;
  16. public static ResultSet rs;
  17. public static java.sql.ResultSetMetaData rsmd;
  18. public static Connection con;
  19.  
  20. /**
  21.   * Initializes connection, can be replaced by DBHelper
  22.   * constructor with some changes (make static variables attributes) in order
  23.   * to have multiple connections instantly with many preparedStatement
  24.   * objects
  25.   */
  26. public static void init() {
  27. try {
  28. Class.forName("com.mysql.jdbc.Driver");
  29. } catch (ClassNotFoundException e1) {
  30. e1.printStackTrace();
  31. }
  32. String url = "jdbc:mysql://localhost:3306/test";
  33. String user = "put a username";
  34. String password = "put your password";
  35. try {
  36. con = DriverManager.getConnection(url, user, password);
  37. } catch (SQLException e) {
  38. e.printStackTrace();
  39. }
  40. }
  41.  
  42. public DBHelper() {
  43. // TODO Auto-generated constructor stub
  44. }
  45.  
  46. /**
  47.   * select rows from any table, provided that the table has the same columns as the bean
  48.   * fields, similarity here is about names and types, types need to be
  49.   * mapped correctly check the link bellow
  50.   * @see <a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html">Java, JDBC and MySQL Types</a>
  51.   * @return Iterator of objects representing selected rows
  52.   */
  53. public static Iterator getBeans(String condition, Object bean, String table)
  54. System.out.println("SELECT * FROM " + table + " WHERE " + condition);
  55. pst = con.prepareStatement("SELECT * FROM " + table + " WHERE "
  56. + condition);
  57. rs = pst.executeQuery();
  58. rsmd = rs.getMetaData();
  59. ArrayList link = new ArrayList();
  60. Object element = null;
  61. try {
  62. element = bean.getClass().newInstance();
  63. } catch (InstantiationException e) {
  64.  
  65. }
  66. // the critical work is made here, these two loops are fetching data
  67. // from the resultSet object which contains rows, into the iterator
  68. // one row by one bean
  69. while (rs.next()) {
  70. rsmd = rs.getMetaData();
  71. int length = element.getClass().getDeclaredFields().length;
  72. int length1 = rsmd.getColumnCount();
  73. for (int i = 0; i < length1; i++) {
  74. for (int j = 0; j < length; j++) {
  75. if (element.getClass().getDeclaredFields()[j].getName()
  76. .equals(rsmd.getColumnName(i + 1))) {
  77. element.getClass().getDeclaredFields()[j].set(element,
  78. rs.getObject(i + 1));
  79. }
  80. }
  81. }
  82. link.add(element);
  83. try {
  84. element = bean.getClass().newInstance();
  85. } catch (InstantiationException e) {
  86.  
  87. }
  88. }
  89. return link.iterator();
  90. }
  91.  
  92. /**
  93.   * @return a single row from any table and map it into a bean,
  94.   * provided that it has the same columns as the bean fields,
  95.   * similarity here is about names and types, types need to be mapped
  96.   * correctly
  97.   */
  98. public static Object getBean(String condition, Object bean, String table)
  99. System.out.println("SELECT * FROM " + table + " WHERE " + condition);
  100. pst = con.prepareStatement("SELECT * FROM " + table + " WHERE "
  101. + condition);
  102. rs = pst.executeQuery();
  103. rsmd = rs.getMetaData();
  104. rs.next();
  105. int length = bean.getClass().getDeclaredFields().length;
  106. int length1 = rsmd.getColumnCount();
  107. if (rs.getRow() == 1) {
  108. for (int i = 0; i < length1; i++) {
  109. for (int j = 0; j < length; j++) {
  110. if (bean.getClass().getDeclaredFields()[j].getName()
  111. .equals(rsmd.getColumnName(i + 1))) {
  112. bean.getClass().getDeclaredFields()[j].set(bean,
  113. rs.getObject(i + 1));
  114. }
  115. }
  116. }
  117. } else
  118. return null;
  119. return bean;
  120. }
  121. }

Report this snippet  

You need to login to post a comment.