jdbc操作数据库公共类

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);
		else
			re.put("pre", p - 1);

		// 下页
		if (p >= maxPage)
			re.put("next", p);
		else
			re.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);
	}
}