@SuppressWarnings("unchecked")
public String list() {
String tf_name = res.getParameter(request, "tf_name", "");
String hql = "";
String total_hql = "";
if(!"".equals(tf_name)){
hql = "from TBFile f where (f.tf_relName like '%" + tf_name + "%' or f.tf_no like '%" + tf_name
+ "%') and f.tf_state = '1' order by f.tf_inputdate";
total_hql = "select count(tf_id) total from tb_file f where f.tf_state='1' and tf_name like '%"+ tf_name +"%'";
request.setAttribute("tf_name", tf_name);
}else{
total_hql = "select count(tf_id) total from tb_file f where f.tf_state='1'";
hql = "from TBFile f where f.tf_state = '1' order by f.tf_inputdate desc";
}
Integer total=C3P0Utils.getTotal(total_hql);
//int total = systemService.getTotal(hql);
request.setAttribute("total", total);
// 有多少页可以浏览
int countPage = (total / Constants.WEBPAGESIZE) + (total % Constants.WEBPAGESIZE > 0 ? 1 : 0);
request.setAttribute("countPage", countPage);
int pageNo = 1;
if (request.getParameter(Constants.PAGE_NO) != null) {
pageNo = Integer.parseInt(request.getParameter(Constants.PAGE_NO));//当前页
}
List<TBFile> list = (List<TBFile>) systemService.findListByHql(hql, Constants.WEBPAGESIZE, pageNo);
request.setAttribute("pageNo", pageNo);
request.setAttribute("list", list);
return "list";
}
package com.system.db;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Utils {
private static ComboPooledDataSource source;
static {
source = new ComboPooledDataSource("myc3p0");//c3p0-config.xml <named-config name="myc3p0"> 配置名称保存�?�?.
}
public static ComboPooledDataSource getDataSource() {
return source;
}
/**
* 通过Sql得到clz的实体类集合
* @param sql
* @param clz
* @return
*/
public static List<?> getListBySql(String sql,Class<?> clz){
List<?> list = null;
QueryRunner run = new QueryRunner(C3P0Utils.getDataSource());
try {
list = (List<?>) run.query(sql,new BeanListHandler(clz));
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 通过Sql得到clz的实体类
* @param sql
* @param clz
* @return
*/
public static Object getObjectBySql(String sql,Class<?> clz){
Object obj = null;
QueryRunner run = new QueryRunner(C3P0Utils.getDataSource());
try {
obj = run.query(sql,new BeanHandler(clz));
} catch (SQLException e) {
e.printStackTrace();
}
return obj;
}
public static int getTotal(String sql){
QueryRunner run = new QueryRunner(C3P0Utils.getDataSource());
Total total = null;
try {
total = (Total) run.query(sql,new BeanHandler(Total.class));
} catch (SQLException e) {
e.printStackTrace();
}
if(total != null){
return total.getTotal();
}else{
return 0;
}
}
/**
* 更新,删除
* @param sql
* @return
*/
public static int executeSql(String sql){
int result = 0;
QueryRunner run = new QueryRunner(C3P0Utils.getDataSource());
try {
result = run.update(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public static void main(String[] args) {
QueryRunner run = new QueryRunner(C3P0Utils.getDataSource());
//select count(tf_id) from tb_testData f where f.tf_type = 'cs'
try {
Total total = (Total) run.query("select count(tf_id) total from tb_testData f where f.tf_type = 'cs'", new BeanHandler(Total.class));
System.out.println(total.getTotal());
//run.query("select count(tf_id) from tb_testData f where f.tf_type = 'cs'",new BeanHandler(Integer.class));
} catch (SQLException e) {
e.printStackTrace();
}
// try {
// @SuppressWarnings("unchecked")
// List<User> list = (List<User>) run.query("select * from tb_user",new BeanListHandler(User.class));
// for (User user : list) {
// System.out.println(user.getTf_email());
// }
// } catch (SQLException e) {
// e.printStackTrace();
// }
}
}
//WEB分页一页最大显示几行
public static final int WEBPAGESIZE = 25;
package com.system.common;
public final class Constants {
/**
* =======================公用属性======================================
*/
public static final String RESPASSWORD="888888";//默认密码
public static int MAX_RESULT_PER_PAGE = 10; // 每页显示的最大行数
public static final String SESSION_BEAN = "sessionBean";// 用户ID
//数据库中数据状态
public static final String STATE_VALID="1";//有效
public static final String STATE_INVALID="0";//无效
//wap分页一页最大显示几行
public static final int WAPPAGESIZE=5;
//WEB分页一页最大显示几行
public static final int WEBPAGESIZE = 25;
//web测试分页
public static final int WEBPAGE=10;
//系统初始账号
public static final String SYSTEMLOGIN = "001";
// 性别状态
public static final String SEX_N="1"; // 男
public static final String SEX_NV="2"; // 女
public static final String SEX_W="3"; // 未知
public static final String DATETYPE= "surveyTime";
public static final String BEGINTIME="00:00:00";
public static final String ENDTIME="23:59:59";
public static final String DATETYPE1= "surveyTime1";
public static final String DATETYPE2= "surveyTime2";
public static final String DATETYPE3= "surveyTime3";
public static final String PAGE_SIZE = "rows";
public static final String PAGE_NO = "page";
public static final String TOTAL = "total";
public static final String SEARCH_RESULT = "search_result";
public static final String MANAGER_TABLE_ADD = "add";
public static final String MANAGER_TABLE_DEL = "del";
public static final String MANAGER_TABLE_EDIT = "edit";
//其它语句的定义
public static final String CUSTOM_MSG = "无法删除此客户信息,删除后无法保证产品型号信息的完整性.....";
public static final String encfor_exist = "此封装形式已存在,请确认后再提交数据.....";
public static final String ZH_DENY = "错误信息提示:您的账号被禁用,如需要使用,请联系管理员.....";
public static final String MM_ERROR = "错误信息提示:密码错误,请重新输入!";
public static final String ZH_NOT_EXIST = "错误信息提示:用户不存在,请重新输入!";
public static final String ORG_FULL = "此组织下面存在子组织单位,无法删除......";
}
<c3p0-config>
<named-config name="myc3p0">
<property name="user">sa</property>
<property name="password">qwertyuiop@ims</property>
<property name="driverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver
</property>
<property name="jdbcUrl">jdbc:sqlserver://10.1.97.200:1433;databaseName=IMS_NEW
</property>
<!-- 若数据库中连接数不足时,一次向数据库服务器申请多少个链接 -->
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement">5</property>
<!--初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="initialPoolSize">10</property>
<!--连接池中保留的最小连接数。 -->
<property name="minPoolSize">10</property>
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize">50</property> <!-- intergalactoApp adopts a different approach to configuring statement
caching -->
<!--JDBC的标准参数,用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements 属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
如果maxStatements与maxStatementsPerConnection均为0,则缓存被关闭。Default: 0 -->
<property name="maxStatements">20</property>
<!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
<!-- 每个链接可以使用的Statements 对象的个数 -->
<property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
<property name="validationQuery" value="select * from dual"/>
</named-config>
</c3p0-config>