package org.ld.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;public class DaoTempl extends BaseDAO {public HashMap selectInfoAll(String tableName, String sqls)throws Exception {HashMap infoMap = null;Connection conn = null;PreparedStatement prep = null;String sql = "select * from " + tableName + " where 1=1 " + sqls;System.out.println(sql);try {conn = this.getConnection();prep = conn.prepareStatement(sql);ResultSet rs = prep.executeQuery();while (rs.next()) {infoMap = this.getResultSetMetaData(rs);}prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return infoMap;}public HashMap selectInfoevery(String tableName, String columns, String sqls)throws Exception {HashMap infoMap = null;Connection conn = null;PreparedStatement prep = null;String sql = "select " + columns + " from " + tableName + " where 1=1 "+ sqls;System.out.println(sql);try {conn = this.getConnection();prep = conn.prepareStatement(sql);ResultSet rs = prep.executeQuery();while (rs.next()) {infoMap = this.getResultSetMetaData(rs);}prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return infoMap;}public List selectForSql(String sqls) throws Exception {Connection conn = null;PreparedStatement prep = null;List list = new ArrayList();System.out.println(sqls);try {conn = this.getConnection();prep = conn.prepareStatement(sqls);ResultSet rs = prep.executeQuery();while (rs.next()) {HashMap infoMap = this.getResultSetMetaData(rs);list.add(infoMap);}prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return list;}public HashMap select_list(String tableName, String sqls, String page,int pageNum) throws Exception {HashMap re = new HashMap();List list = new ArrayList();Connection conn = null;ResultSet rs = null;PreparedStatement prep = null;String sqlstr = "";int p = 1;try {if (page != null && !"".equalsIgnoreCase(page)) {p = Integer.parseInt(page);} else {page = "1";}} catch (Exception e) {p = 1;e.printStackTrace();}String sqlcount = "select count(*) as c from " + tableName+ " where 1=1 " + sqls;System.out.println("count:" + sqlcount);int count = 0;int maxPage = 0;int startPage = 0;int endPage = 0;try {conn = this.getConnection();prep = conn.prepareStatement(sqlcount);ResultSet rs0 = prep.executeQuery();if (rs0.next()) {count = rs0.getInt("c");}rs0.close();prep.close();maxPage = count / pageNum;if (count % pageNum != 0)maxPage++;startPage = (p - 1) * pageNum + 1;endPage = p * pageNum;sqlstr = "SELECT * FROM (select *,ROW_NUMBER() Over(order by id desc) as rowNum from "+ tableName+ " where 1=1 "+ sqls+ " ) as myTable where rowNum between "+ startPage+ " and " + endPage + ";";prep = conn.prepareStatement(sqlstr);rs = prep.executeQuery();HashMap colMap = null;while (rs.next()) {colMap = new HashMap();colMap = getResultSetMetaData(rs);list.add(colMap);}rs.close();prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}re.put("list", list);re.put("maxPage", maxPage);re.put("count", count);re.put("page", page);// 上页if (p == 1)re.put("pre", p);elsere.put("pre", p - 1);// 下页if (p >= maxPage)re.put("next", p);elsere.put("next", p + 1);return re;}public List select_list(String tableName, String sqls) throws Exception {List list = new ArrayList();Connection conn = null;PreparedStatement prep = null;String sql = "select * from " + tableName + " where 1=1 " + sqls;System.out.print(sql);try {conn = this.getConnection();prep = conn.prepareStatement(sql);ResultSet rs = prep.executeQuery();while (rs.next()) {HashMap exportMap = this.getResultSetMetaData(rs);list.add(exportMap);}prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return list;}public boolean delete_info(String tableName, String id) throws Exception {boolean flag = false;Connection conn = null;PreparedStatement prep = null;String sql = "delete from " + tableName + " where id = ?";try {conn = this.getConnection();prep = conn.prepareStatement(sql);prep.setInt(1, org.ld.util.CommUtil.null2Int(id));flag = prep.execute();prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return flag;}public boolean delete_info_query(String tableName, String sqls)throws Exception {boolean flag = false;Connection conn = null;PreparedStatement prep = null;String sql = "delete from " + tableName + " where " + sqls;try {conn = this.getConnection();prep = conn.prepareStatement(sql);flag = prep.execute();prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return flag;}public boolean insert_table(String tableName, HashMap parameter)throws Exception {boolean flag = false;Connection conn = null;PreparedStatement prep = null;int count = parameter.size();String columns = "", values = "";Iterator it = parameter.keySet().iterator();while (it.hasNext()) {String key = (String) it.next();columns += key + ",";values += parameter.get(key) + ",";}if (columns.endsWith(","))columns = columns.substring(0, columns.lastIndexOf(","));if (values.endsWith(","))values = values.substring(0, values.lastIndexOf(","));String sql = "insert into " + tableName + " (" + columns + ") values ("+ values + ")";System.out.println("insert:" + sql);try {conn = this.getConnection();prep = conn.prepareStatement(sql);flag = prep.execute();prep.close();return true;} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return flag;}public boolean update_table(String tableName, HashMap parameter,String condition) throws Exception {boolean flag = false;Connection conn = null;PreparedStatement prep = null;String updatestr = "";Iterator it = parameter.keySet().iterator();while (it.hasNext()) {String key = (String) it.next();updatestr += key + "=" + (String) parameter.get(key) + ",";}if (updatestr.endsWith(","))updatestr = updatestr.substring(0, updatestr.lastIndexOf(","));String sql = "update " + tableName + " set " + updatestr + " where "+ condition;try {conn = this.getConnection();prep = conn.prepareStatement(sql);flag = prep.executeUpdate() > 0;prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return flag;}// 查询个数public int select_list_count(String tableName, String sqls)throws Exception {List list = new ArrayList();Connection conn = null;PreparedStatement prep = null;String sql = "select count(1) from " + tableName + " where 1=1 " + sqls;System.out.print(sql);try {conn = this.getConnection();prep = conn.prepareStatement(sql);ResultSet rs = prep.executeQuery();if (rs.next()) {return rs.getInt(1);}prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return 0;}// 根据sql查询个数public int select_list_countBySql(String sqls) throws Exception {Connection conn = null;PreparedStatement prep = null;try {conn = this.getConnection();prep = conn.prepareStatement(sqls);ResultSet rs = prep.executeQuery();if (rs.next()) {return rs.getInt(1);}prep.close();} catch (Exception e) {e.printStackTrace();} finally {try {conn.close();} catch (Exception e) {e.printStackTrace();}}return 0;}public static void main(String[] args) {// HashMap hm = new HashMap();// List list = null;// java.util.Iterator it = hm.entrySet().iterator();String s = "abc,ddd,aac,bb,";if (s.endsWith(","))s = s.substring(0, s.lastIndexOf(","));System.out.println(s);}}
