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<DeviceYCMin01> 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<DeviceYCMin01> query = queryRunner.query(sql,new BeanListHandler<DeviceYCMin01>(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<DeviceYCMin01> 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<DeviceYCMin01> query = queryRunner.query(sql,new BeanListHandler<DeviceYCMin01>(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<DeviceYCMin01> 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<DeviceYCMin01> query = queryRunner.query(sql,new BeanListHandler<DeviceYCMin01>(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<DeviceYCMin01> 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<DeviceYCMin01> query = queryRunner.query(sql,new BeanListHandler<DeviceYCMin01>(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<DeviceYCMin01> 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<DeviceYCMin01> query = queryRunner.query(sql,new BeanListHandler<DeviceYCMin01>(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<DeviceYCMin01> 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<DeviceYCMin01> query = queryRunner.query(sql,new BeanListHandler<DeviceYCMin01>(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<DeviceYCMin01> 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<(?) limit ?,?";
		List<DeviceYCMin01> query = queryRunner.query(sql,new BeanListHandler<DeviceYCMin01>(DeviceYCMin01.class), startDate, endDate, index,currentCount);
		return query;
	}
}

 

转载于:https://my.oschina.net/u/2999760/blog/1553909