ghy_page_tools_new.java

一套网上书店系统采用JAVABEAN+SERVLET+JSP

package ghy_page_tools;

import ghy_db.Conn_Manager;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ghy_page_tools_new {

	String select_string;
	// 定义一个SQL查询的select的子句

	String from_string;
	// 定义一个SQL查询的from的子句

	String where_string;
	// 定义一个SQL查询的where的子句

	String orderby_string;
	// 定义一个SQL查询的order by的子句

	String key_name;
	// 数据库中主键的名称

	String type_field_name;
	// 数据库中类别字段的名称

	boolean isMulTable = false;
	// 是否是单表还是多表的操作，false是单表操作

	private PageBean pagebean;
	// PageBean是JSP页面中的上一页下一页的页面导航数据的封装

	int page_list = 15;
	// 一页显示15条记录

	private List record_list;

	private int getRecordCount(String type) throws Throwable {

		ResultSet rs = null;

		int count = 0;

		if (!isMulTable) {// 是单表的操作
			if (type.equals("all")) {// 查询出单表中的所有all记录数

				ResultSet rsrs = Conn_Manager.getConnection().createStatement()
						.executeQuery(
								"select count(" + key_name + ") " + from_string
										+ " " + where_string);

				rs = Conn_Manager.getConnection().createStatement()
						.executeQuery(
								"select count(" + key_name + ") " + from_string
										+ " " + where_string);
				rs.next();
				count = rs.getInt(1);
				rs.close();
				return count;

			} else {// 查询出单表的指定类别的记录数
				if ("".equals(where_string)) {
					// 如果查询出单表的指定类别的记录数的条件中无where子句则执行
					rs = Conn_Manager
							.getConnection()
							.createStatement()
							.executeQuery(
									"select count(" + key_name + ") "
											+ from_string + " where "
											+ type_field_name + "=" + type + "");
					rs.next();
					count = rs.getInt(1);
					rs.close();
					return count;
				} else {// 如果查询出单表的指定类别的记录数的条件中有where子句则执行
					rs = Conn_Manager.getConnection().createStatement()
							.executeQuery(
									"select count(" + key_name + ") "
											+ from_string + " " + where_string
											+ " and " + type_field_name + "="
											+ type + "");
					rs.next();
					count = rs.getInt(1);
					rs.close();
					return count;
				}
			}

		} else {// 多表的操作
			if (type.equals("all")) {// 查询多表中的所有数据的记录数
				rs = Conn_Manager.getConnection().createStatement()
						.executeQuery(
								"select count(*) " + from_string + " "
										+ where_string);
				rs.next();
				count = rs.getInt(1);
				rs.close();
				return count;
			} else {// 查询出多表中指定类别的记录数
				rs = Conn_Manager.getConnection().createStatement()
						.executeQuery(
								"select count(*) " + from_string + " "
										+ where_string + " and "
										+ type_field_name + "=" + type + "");
				rs.next();
				count = rs.getInt(1);
				rs.close();
				return count;
			}
		}

	}

	private List getRecordList(String type, String tablename, String page)
			throws Throwable {

		List recordList = new ArrayList();

		ResultSet rs = null;

		if (!isMulTable) {// 如果是单表进行查询
			if (type.equals("all")) {// 查询单表的所有数据
				if ("".equals(orderby_string)) {
					// 查询单表所有数据的HQL语句如果无order by子句时则执行
					rs = Conn_Manager
							.getConnection()
							.createStatement()
							.executeQuery(
									select_string
											+ " "
											+ from_string
											+ " "
											+ where_string
											+ " order by "
											+ key_name
											+ " desc limit "
											+ Integer.valueOf(page_list)
											* (Integer.valueOf(page).intValue() - 1)
											+ "," + Integer.valueOf(page_list));

				} else {
					// 查询单表所有数据的HQL语句如果有order by子句时则执行
					rs = Conn_Manager
							.getConnection()
							.createStatement()
							.executeQuery(
									select_string
											+ " "
											+ from_string
											+ " "
											+ where_string
											+ " "
											+ orderby_string
											+ " limit "
											+ Integer.valueOf(page_list)
											* (Integer.valueOf(page).intValue() - 1)
											+ "," + Integer.valueOf(page_list));
				}
			} else {
				// 查询单表的指定类别的数据
				if ("".equals(where_string)) {
					// 查询单表所有数据的HQL语句如果无where子句时则执行
					if ("".equals(orderby_string)) {
						// 查询单表所有数据的HQL语句如果无order by子句时则执行
						rs = Conn_Manager.getConnection().createStatement()
								.executeQuery(
										select_string
												+ " "
												+ from_string
												+ " where "
												+ type_field_name
												+ "="
												+ type
												+ " order by "
												+ key_name
												+ " desc limit "
												+ Integer.valueOf(page_list)
												* (Integer.valueOf(page)
														.intValue() - 1) + ","
												+ Integer.valueOf(page_list));

					} else {
						// 查询单表所有数据的HQL语句如果有order by子句时则执行
						rs = Conn_Manager.getConnection().createStatement()
								.executeQuery(
										select_string
												+ " "
												+ from_string
												+ " where "
												+ type_field_name
												+ "="
												+ type
												+ " "
												+ orderby_string
												+ " desc limit "
												+ Integer.valueOf(page_list)
												* (Integer.valueOf(page)
														.intValue() - 1) + ","
												+ Integer.valueOf(page_list));

					}
				} else {
					// 查询单表所有数据的HQL语句如果有where子句时则执行
					if ("".equals(orderby_string)) {
						// 查询单表所有数据的HQL语句如果无order by子句时则执行
						rs = Conn_Manager.getConnection().createStatement()
								.executeQuery(
										select_string
												+ " "
												+ from_string
												+ " "
												+ where_string
												+ " and "
												+ type_field_name
												+ "="
												+ type
												+ " order by "
												+ key_name
												+ " desc limit "
												+ Integer.valueOf(page_list)
												* (Integer.valueOf(page)
														.intValue() - 1) + ","
												+ Integer.valueOf(page_list));
					} else {
						// 查询单表所有数据的HQL语句如果有order by子句时则执行
						rs = Conn_Manager.getConnection().createStatement()
								.executeQuery(
										select_string
												+ " "
												+ from_string
												+ " "
												+ where_string
												+ " and "
												+ type_field_name
												+ "="
												+ type
												+ " "
												+ orderby_string
												+ " limit "
												+ Integer.valueOf(page_list)
												* (Integer.valueOf(page)
														.intValue() - 1) + ","
												+ Integer.valueOf(page_list));
					}
				}
			}
			while (rs.next()) {
				String[] row = new String[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					row[i] = rs.getString(i + 1);
				}
				recordList.add(row);
			}
		}
		// /////////////////////////////////上面是单表的操作

		else {// 如果是全部则查询全部信息
			System.out.println(select_string + " " + from_string + " "
					+ where_string + " order by " + key_name + " desc limit "
					+ Integer.valueOf(page_list)
					* (Integer.valueOf(page).intValue() - 1) + ","
					+ Integer.valueOf(page_list));
			if (type.equals("all")) {
				// 如果没有order by子句执行
				System.out.println("abcabcabc9999999999999999999999999="
						+ select_string + " " + from_string + " "
						+ where_string + " " + orderby_string + " limit "
						+ Integer.valueOf(page_list)
						* (Integer.valueOf(page).intValue() - 1) + ","
						+ Integer.valueOf(page_list));
				if ("".equals(orderby_string)) {
					rs = Conn_Manager
							.getConnection()
							.createStatement()
							.executeQuery(
									select_string
											+ " "
											+ from_string
											+ " "
											+ where_string
											+ " order by "
											+ key_name
											+ " desc limit "
											+ Integer.valueOf(page_list)
											* (Integer.valueOf(page).intValue() - 1)
											+ "," + Integer.valueOf(page_list));
				} else {
					// 否则执行无order by子句的情况

					rs = Conn_Manager
							.getConnection()
							.createStatement()
							.executeQuery(
									select_string
											+ " "
											+ from_string
											+ " "
											+ where_string
											+ " "
											+ orderby_string
											+ " limit "
											+ Integer.valueOf(page_list)
											* (Integer.valueOf(page).intValue() - 1)
											+ "," + Integer.valueOf(page_list));
				}
			} else {
				if ("".equals(orderby_string)) {
					// 如果是多表进行查询，并且查询出指定类别的记录，而且无order by子句
					rs = Conn_Manager
							.getConnection()
							.createStatement()
							.executeQuery(
									select_string
											+ " "
											+ from_string
											+ " "
											+ where_string
											+ " and "
											+ type_field_name
											+ "="
											+ type
											+ " order by "
											+ key_name
											+ " desc limit "
											+ Integer.valueOf(page_list)
											* (Integer.valueOf(page).intValue() - 1)
											+ "," + Integer.valueOf(page_list));
				} else {
					// 如果是多表进行查询，并且查询出指定类别的记录，而且有order by子句
					rs = Conn_Manager
							.getConnection()
							.createStatement()
							.executeQuery(
									select_string
											+ " "
											+ from_string
											+ " "
											+ where_string
											+ " and  "
											+ type_field_name
											+ "="
											+ type
											+ " "
											+ orderby_string
											+ " limit "
											+ Integer.valueOf(page_list)
											* (Integer.valueOf(page).intValue() - 1)
											+ "," + Integer.valueOf(page_list));

				}
			}
			while (rs.next()) {

				String[] row = new String[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
					row[i] = rs.getString(i + 1);
				}
				recordList.add(row);
			}
		}

		rs.close();

		return recordList;

	}

	private PageBean createPageBean(int page_count, int page, String type) {
		if (type.equals("all")) {
			return pagebean = new PageBean(page_count, page, "all");
		} else {
			return pagebean = new PageBean(page_count, page, type);
		}
	}

	// 取得类别下拉表中的所有数据
	// 参数是一个SQL语句：String id_typename_tablename
	public Map getTypeList(String id_typename_tablename) throws Throwable {

		// 定义存放类别表中类别的容器LinkedHashMap
		LinkedHashMap type_list = new LinkedHashMap();

		ResultSet rs = Conn_Manager.getConnection().createStatement()
				.executeQuery(id_typename_tablename);

		type_list.put("all", "all");

		while (rs.next()) {
			type_list.put(rs.getInt(1), rs.getString(2));
		}

		rs.close();

		return type_list;

	}

	public void setPage_Service(String select_string, String from_string,
			String where_string, String orderby_string, String type,
			String type_field_name, String key_name, String page,
			int page_list, boolean isMulTable) throws Throwable {
		try {
			this.select_string = select_string;

			this.from_string = from_string;

			this.where_string = where_string;

			this.orderby_string = orderby_string;

			this.type_field_name = type_field_name;

			this.key_name = key_name;

			this.isMulTable = isMulTable;

			this.page_list = page_list;

			int page_count;

			int record_count = 0;
			System.out.println("**************************2"+page);
			System.out.println(select_string + " " + from_string + " "
					+ where_string + " order by " + key_name + " desc limit "
					+ 1 + "," + 5);
			if (("".equals(type)) || (type == null)) {
				record_count = this.getRecordCount("all");
				System.out.println("类别为all 记录数是：" + record_count);
			} else {
				record_count = this.getRecordCount(type);
				System.out.println("类别为" + type + " 记录数是：" + record_count);

			}

			if (record_count == 0) {
				System.out.println("记录数：0　生成new PageBean(0, 0, \"all\")");
				if (type == null || "".equals(type)) {
					// 记录记录数为0，并且type为空时创建PageBean
					pagebean = new PageBean(1, 1, "all");
					record_list = getRecordList("all", key_name, "1");
				} else {
					// 记录记录数为0，并且type不为空时创建PageBean
					pagebean = new PageBean(1, 1, type);
					record_list = getRecordList(type, key_name, "1");
				}
			} else {
				// 如果记录不为0时执行
				System.out.println("记录数不为0");
				if ((record_count % page_list) == 0) {
					// 计算出页数
					page_count = record_count / page_list;
					System.out.println("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"+page_count);
				} else {
					// 计算出页数
					page_count = record_count / page_list + 1;
					System.out.println("bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"+page_count);
				}

				if (("".equals(type)) || (type == null)) {
					// 根据页数取得一页中的记录条目
					record_list = getRecordList("all", key_name, "1");
					// 创建当前条件下的分页导航
					pagebean = createPageBean(page_count,1, "all");

				} else {
					record_list = getRecordList(type, key_name, page);

					if (record_list.size() == 0) {
						record_list = getRecordList(type, key_name, "1");
						pagebean = createPageBean(page_count, Integer.valueOf(
								"1").intValue(), type);
					} else {
						pagebean = createPageBean(page_count, Integer.valueOf(
								page).intValue(), type);
					}

				}

			}
		} catch (Throwable e) {
			throw e;
		}
	}

	public PageBean getPagebean() {
		return pagebean;
	}

	public List getRecord_list() {
		return record_list;
	}

}