java时间条件_Java与MySQL时间条件搜索

package com.deviceycmin01.dao;

import java.sql.SQLException;

import java.util.List;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.pojo.DeviceYCMin01;

import com.util.DataSourceUtils;

public class DeviceYCMin01Dao{

private String table = "device_yc_min01";

/**

* 搜索今天条数

*

* @param name

* @return

* @throws SQLException

*/

public int getCountDao() throws SQLException{

QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "select count(*) from "+table+" where to_days(time) = to_days(now())";

Long query = (Long)runner.query(sql,new ScalarHandler());

return query.intValue();

}

/**

* 查询今天

*

* @param index

* @param currentCount

* @return

* @throws SQLException

*/

public List device_yc_minJinTianDao(int index, int currentCount) throws SQLException{

QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "select * from "+table+" where to_days(time) = to_days(now()) order by time desc limit ?,?";

List query = queryRunner.query(sql,new BeanListHandler(DeviceYCMin01.class),index,currentCount);

return query;

}

/**

* 搜索昨天条数

* @return

* @throws SQLException

*/

public int getZuoTianCountDao() throws SQLException{

QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "SELECT count(*) FROM " + table + " WHERE TO_DAYS(NOW()) - TO_DAYS(time) = 1";

Long query = (Long)runner.query(sql,new ScalarHandler());

return query.intValue();

}

/**

* 昨天

* @param index

* @param currentCount

* @return

* @throws SQLException

*/

public List device_yc_minZuoTianDao(int index, int currentCount) throws SQLException{

QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "SELECT * FROM " + table + " WHERE TO_DAYS(NOW()) - TO_DAYS(time) = 1 limit ?,?";

List query = queryRunner.query(sql,new BeanListHandler(DeviceYCMin01.class),index,currentCount);

return query;

}

/**

* 搜索本周条数

* @return

* @throws SQLException

*/

public int getBenZhouCountDao() throws SQLException{

QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "SELECT count(*) FROM " + table + " WHERE YEARWEEK(date_format(time,'%Y-%m-%d')) = YEARWEEK(now())";

Long query = (Long)runner.query(sql,new ScalarHandler());

return query.intValue();

}

/**

* 本周

* @param index

* @param currentCount

* @return

* @throws SQLException

*/

public List device_yc_minBenZhouDao(int index, int currentCount) throws SQLException{

QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "SELECT * FROM " + table + " WHERE YEARWEEK(date_format(time,'%Y-%m-%d')) = YEARWEEK(now()) limit ?,?";

List query = queryRunner.query(sql,new BeanListHandler(DeviceYCMin01.class),index,currentCount);

return query;

}

/**

* 搜索上周条数

* @return

* @throws SQLException

*/

public int getShangZhouCountDao() throws SQLException{

QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "SELECT count(*) FROM " + table + " WHERE YEARWEEK(date_format(time,'%Y-%m-%d')) = YEARWEEK(now())-1";

Long query = (Long)runner.query(sql,new ScalarHandler());

return query.intValue();

}

/**

* 上周

* @param index

* @param currentCount

* @return

* @throws SQLException

*/

public List device_yc_minShangZhouDao(int index, int currentCount) throws SQLException{

QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "SELECT * FROM " + table + " WHERE YEARWEEK(date_format(time,'%Y-%m-%d')) = YEARWEEK(now())-1 limit ?,?";

List query = queryRunner.query(sql,new BeanListHandler(DeviceYCMin01.class),index,currentCount);

return query;

}

/**

* 搜索本月条数

* @return

* @throws SQLException

*/

public int getBenYueCountDao() throws SQLException{

QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "select count(*) from " + table + " where date_format(time,'%Y-%m')=date_format(now(),'%Y-%m')";

Long query = (Long)runner.query(sql,new ScalarHandler());

return query.intValue();

}

/**

* 本月

* @param index

* @param currentCount

* @return

* @throws SQLException

*/

public List device_yc_minBenYueuDao(int index, int currentCount) throws SQLException{

QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "select * from " + table + " where date_format(time,'%Y-%m')=date_format(now(),'%Y-%m') limit ?,?";

List query = queryRunner.query(sql,new BeanListHandler(DeviceYCMin01.class),index,currentCount);

return query;

}

/**

* 搜索上月条数

* @return

* @throws SQLException

*/

public int getShnagYueCountDao() throws SQLException{

QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "SELECT count(*) FROM " + table + " WHERE PERIOD_DIFF(date_format(now(),'%Y%m' ),date_format(time,'%Y%m'))";

Long query = (Long)runner.query(sql,new ScalarHandler());

return query.intValue();

}

/**

* 上月

* @param index

* @param currentCount

* @return

* @throws SQLException

*/

public List device_yc_minShnagYueuDao(int index, int currentCount) throws SQLException{

QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "select * from " + table + " WHERE PERIOD_DIFF(date_format(now(),'%Y%m' ),date_format(time,'%Y%m'))=1 limit ?,?";

List query = queryRunner.query(sql,new BeanListHandler(DeviceYCMin01.class),index,currentCount);

return query;

}

/**

* 搜索时间段条数

* @return

* @throws SQLException

*/

public int getShiJianDuanCountDao(String startDate, String endDate) throws SQLException{

QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "select count(*) from " + table + " where time>(?) and time

Long query = (Long)runner.query(sql,new ScalarHandler(), startDate, endDate);

return query.intValue();

}

/**

* 时间段

* @param index

* @param currentCount

* @return

* @throws SQLException

*/

public List device_yc_minShiJianDuanDao(String startDate, String endDate,int index, int currentCount) throws SQLException{

QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());

String sql = "select * from " + table + " where time>(?) and time

List query = queryRunner.query(sql,new BeanListHandler(DeviceYCMin01.class), startDate, endDate, index,currentCount);

return query;

}

}