Query a single record

Description

Demo Code


import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class Main{
    public static String URL;
    public static String USER_NAME;
    public static String PASSWORD;
    /**/*from   w  w w .  j  av a2s .  co m*/
         * Query a single record
         * @param sql
         * @param args
         * @return List<Map<String,Object>>
         */
        public static List<Map<String, Object>> queryForList(String sql,
                Object... args) {
            List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
            Connection con = null;
            ResultSet rs = null;
            PreparedStatement ps = null;
            try {
                con = getconnnection();
                ps = con.prepareStatement(sql);
                if (args != null) {
                    for (int i = 0; i < args.length; i++) {
                        ps.setObject((i + 1), args[i]);
                    }
                }
                rs = ps.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<String, Object>();
                    for (int i = 1; i <= columnCount; i++) {
                        map.put(rsmd.getColumnLabel(i), rs.getObject(i));
                    }
                    result.add(map);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                close(rs, ps, con);
            }
            return result;
        }
    /**
         * Query a single record
         * @param sql
         * @param args
         * @return List<T>
         */
        public static <T> List<T> queryForList(String sql,Class<T> clz,Object ... args){
   List<T> result = new ArrayList<T>();
   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
   try {
      con = getconnnection();
      ps = con.prepareStatement(sql);
      if(args != null){
         for (int i = 0; i < args.length; i++) {
            ps.setObject((i+1), args[i]);
         }
      }
      rs = ps.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      int columnCount = rsmd.getColumnCount();
      while(rs.next()){
         T obj = clz.newInstance();
         for (int i = 1; i <= columnCount ; i++) {               
            String columnName = rsmd.getColumnName(i);
            String methodName = "set"+columnName.substring(0,1).toUpperCase()+
                  columnName.substring(1, columnName.length());
            Method method [] = clz.getMethods();
            for (Method meth : method) {
               if(methodName.equals(meth.getName())){
                  meth.invoke(obj, rs.getObject(i));      
               }
            }
         }
         result.add(obj);
      }
   } catch (InstantiationException e) {
      e.printStackTrace();
   } catch (IllegalAccessException e) {
      e.printStackTrace();
   } catch (SQLException e) {
      e.printStackTrace();
   } catch (IllegalArgumentException e) {
      e.printStackTrace();
   } catch (InvocationTargetException e) {
      e.printStackTrace();
   }fianlly{
      close(rs,ps,con);
   }
   return result;
}
    /**
         * Get connection
         * @return
         */
        public static Connection getconnnection() {
            Connection con = null;
            try {
                con = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return con;
        }
    /**
         * Close connection
         * @param rs
         * @param st
         * @param con
         */
        public static void close(ResultSet rs, Statement st, Connection con) {
            try {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                } finally {
                    try {
                        if (st != null) {
                            st.close();
                        }
                    } finally {
                        if (con != null)
                            con.close();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    /**
         * Close connection
         * @param rs
         */
        public static void close(ResultSet rs) {
            Statement st = null;
            Connection con = null;
            try {
                try {
                    if (rs != null) {
                        st = rs.getStatement();
                        rs.close();
                    }
                } finally {
                    try {
                        if (st != null) {
                            con = st.getConnection();
                            st.close();
                        }
                    } finally {
                        if (con != null) {
                            con.close();
                        }
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    /**
         * Close connection
         * @param st
         * @param con
         */
        public static void close(Statement st, Connection con) {
            try {
                try {
                    if (st != null) {
                        st.close();
                    }
                } finally {
                    if (con != null)
                        con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}
Query a single record - Java JDBC

Description

Demo Code

Related Tutorials
