Java连接MySQL数据库db.properties文件
Java连接SQLServer数据库db.properties文件
查看sqlserver端口号
exec sys.sp_readerrorlog 0, 1,'listening'
Powerdesigner设置主键自增的方式
选择菜单Database中 change current DBMS 选择Microsoft SQL Server 2005
在表中ID属性,勾上identity即可
Mybatis xml文件小于符号解决
采用xml转义字符
JSTL if判空
JSTL if list判空
<%@tagliburi="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@tagliburi="http://java.sun.com/jsp/jstl/functions"prefix="fun"%>
<c:if test="${fun:length(list)<= 0}">
list对象为空
</c:if>
Mybatis 需要传入多个参数
方法一:
方法二:采用Map
Public User selectUser(Map paramMap);
对应的Mapper.xml
<select id=" selectUser"resultMap="BaseResultMap">
select * fromuser_user_t where user_name =#{userName,jdbcType=VARCHAR} anduser_area=#{userArea,jdbcType=VARCHAR}
</select>
数据库按时间顺序查询语句
select * from jb_excel_input wherejbtime>='2011-01-05' and jbtime<='2017-11-15'
and convert(char(8),jbtime,108)>='00:00:00'and convert(char(8),jbtime,108)<='23:00:00'
Double四舍五入保留三位小数
Double timeNh3 =btime.getFiqnh3()-ftime.getFiqnh3();
BigDecimal b = new BigDecimal(timeNh3);
Double etimeNh3 =b.setScale(3,BigDecimal.ROUND_HALF_UP).doubleValue();
System.out.println("时间为"+btime.getJbtime()+"的时段氨产量为"+etimeNh3);
Mybatis 逆向工程生成pojo字段名和数据库保持一致
<table tableName="jb_rawdata_handle_1">
<propertyname="useActualColumnNames" value="true" />
</table>
Sql按天查询,统计
SELECT
MONTH( data_time ),
SUM( fi_103 ),SUm(hour_nh3_yield)
FROM
jb_rawdata_handle_1
WHERE
YEAR( data_time )= 2016//根据年来统计
Month(data_time)= 4//根据月来统计
GROUPBY
MONTH( data_time )
Sql按天统计分组,亲测好用
selectconvert(varchar(10),tdate,120) tdate,sum(sal) sal from test group by convert(varchar(10),tdate,120);
工作中使用到的例子
selectconvert(varchar(10),data_time,120) data_time,sum(hour_nh3_yield)hour_nh3_yield fromjb_rawdata_handle_1 group byconvert(varchar(10),data_time,120);
外加了添加,起止时间查询
select convert(varchar(10),data_time,120)data_time,sum(hour_nh3_yield) hour_nh3_yield from jb_rawdata_handle_1 where data_time>='2016-04-25' anddata_time<='2016-04-27' group by convert(varchar(10),data_time,120) order bydata_time desc ;
Sql按天统计分组,而且在Java中使用亲测好用(血的教训)
select convert(varchar(10),data_time,120)+' 00:00:00' as data_time
,sum(time_nh3_yield) time_nh3_yield ,
sum(time_electric_consume)time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
fromjb_rawdata_handle_1 where data_time >= '2015-02-21' and data_time <='2018-02-12'
group byconvert(varchar(10),data_time,120)+' 00:00:00' order by data_time desc ;
Sql按月统计分组,而且在Java中使用亲测好用(血的教训)
selectconvert(varchar(7),data_time,120)+'-00 00:00:00'asdata_time
,sum(time_nh3_yield) time_nh3_yield ,
sum(time_electric_consume) time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
from jb_rawdata_handle_1 wheredata_time >='2015-02-21'and data_time <='2018-02-12'
groupbyconvert(varchar(7),data_time,120)+'-00 00:00:00'orderbydata_time desc;
sql月报表,查询指定月下的每一日
selectconvert(varchar(10),data_time,120)+' 00:00:00'asdata_time
,sum(time_nh3_yield) time_nh3_yield ,
sum(time_electric_consume) time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
from jb_rawdata_handle_1 wheredata_time >='2016-05-0100:00:00'and data_time <='2016-05-30 23:59:59'
groupbyconvert(varchar(10),data_time,120)+' 00:00:00'orderbydata_time desc;
sql季度报表,指定年季度,查询季度下的每一个月
selectconvert(varchar(7),data_time,120)+'-01 00:00:00'asdata_time
,sum(time_nh3_yield) time_nh3_yield ,
sum(time_electric_consume) time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
from jb_rawdata_handle_1 wheredata_time >='2016-04-0100:00:00'and data_time <='2016-06-30 23:59:59'
groupbyconvert(varchar(7),data_time,120)+'-01 00:00:00'orderbydata_time asc;
Sql月报表,指定年月,查询该月下的每一日
selectconvert(varchar(10),data_time,120)+' 00:00:00'asdata_time
,sum(time_nh3_yield) time_nh3_yield ,
sum(time_electric_consume) time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
from jb_rawdata_handle_1 wheredata_time >='2016-04-0100:00:00'and data_time <='2016-04-30 23:59:59'
groupbyconvert(varchar(10),data_time,120)+' 00:00:00'orderbydata_time asc;
sql年报表,用户指定年,查询指定年下的每一个月的数据
selectconvert(varchar(7),data_time,120)+'-01 00:00:00'asdata_time
,sum(time_nh3_yield) time_nh3_yield ,
sum(time_electric_consume) time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
from jb_rawdata_handle_1 wheredata_time >='2016-01-0100:00:00'and data_time <='2016-12-31 23:59:59'
groupbyconvert(varchar(7),data_time,120)+'-01 00:00:00'orderbydata_time asc;
sql统计分组查询,还带每一组的样本数
selectconvert(varchar(10),data_time,120)+' 00:00:00'asdata_time
,sum(time_nh3_yield) time_nh3_yield ,count(orig_id)orig_id,
sum(time_electric_consume) time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
from jb_rawdata_handle_1 wheredata_time >='2015-02-2100:00:00'and data_time <='2018-02-12 23:59:59'
groupbyconvert(varchar(10),data_time,120)+' 00:00:00'orderbydata_time asc;
sql月报表,查询指定月下的每一日,按分组统计
<!-- 日报表按班查看 -->
<selectid="selectDataByContidionAndClass"resultType="com.saitejn.pojo.JbRawdataHandle1">
select convert(varchar(10),data_time,120)+' 00:00:00' as data_time
,sum(time_nh3_yield)time_nh3_yield ,count(orig_id)orig_id,
sum(time_electric_consume)time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume,count(orig_id)orig_id
from jb_rawdata_handle_1 where data_time>= #{0} and data_time <= #{1} and shift = #{2}
group byconvert(varchar(10),data_time,120)+' 00:00:00' order by data_time asc ;
sql字符串的截取
selectconvert(varchar(10),data_time,120) as time, SUBSTRING(convert(varchar(14),data_time,120),11,19)+'00:00:00'as data_time,sum(time_nh3_yield) time_nh3_yield,
sum(time_electric_consume)time_electric_consume ,sum(time_raw_gas_consume) time_raw_gas_consume
fromjb_rawdata_handle_1 where data_time >='2015-02-21'and data_time<='2018-02-12'
group bySUBSTRING(convert(varchar(14),data_time,120),11,19)+'00:00:00',convert(varchar(10),data_time,120) order by time asc;
jsp页面保留两位小数,格式化时间等
<%@ taglib prefix="fmt"uri="http://java.sun.com/jsp/jstl/fmt" %>
<td><fmt:formatNumber value="${day.time_nh3_yield}"pattern="##.##" minFractionDigits="2"></fmt:formatNumber></td>
判断是否为瑞年
//将String装换为int
int year= Integer.parseInt(Quarter_year);
if(year% 4 == 0 && year % 100 != 0 || year % 400 == 0){
System.out.println("是闰年");
}else{
System.out.println("不是闰年");
}
Frame中访问WEB-INF中的jsp
需要在web.xml文件中配置
<servlet>
<servlet-name>main</servlet-name>
<jsp-file>/WEB-INF/jsp/admin/hindex.jsp</jsp-file>
</servlet>
<servlet-mapping>
<servlet-name>main</servlet-name>
<url-pattern>/main</url-pattern>
</servlet-mapping>
引用
<frameset rows="88,*,31"cols="*" frameborder="no" border="0"framespacing="0">
<frame src="./top" name="topFrame"scrolling="No" noresize="noresize" id="topFrame"title="topFrame" />
<frameset cols="187,*" frameborder="no"border="0" framespacing="0">
<frame src="./left" name="leftFrame"scrolling="No" noresize="noresize" id="leftFrame"title="leftFrame" />
<frame src="./main" name="rightFrame"id="rightFrame" title="rightFrame" />
</frameset>
<frame src="./button" name="bottomFrame"scrolling="No" noresize="noresize"id="bottomFrame" title="bottomFrame" />
</frameset>
查询时间段内的某一个具体的时间区
select convert(varchar(10),时间,120) as 时间
,sum(时段氨产量) 时段氨产量 , sum(时段净蒸汽耗)时段净蒸汽耗,sum(时段气耗)时段气耗,
sum(吨氨气耗) 吨氨气耗 ,sum(吨氨原料气耗) 吨氨原料气耗,count(累积氨产量) 累积氨产量
fromjy_rawdata_handle_1 where ((RIGHT(convert(varchar(50),时间,120) ,8 ))) >= '00:10:00'and ((RIGHT (convert(varchar(50),时间,120) ,8 ))) <= '08:00:00'
group by convert(varchar(10),时间,120) order by 时间 asc
poi读取Excel
public static void getExcelAsFile(Stringfile) throws FileNotFoundException, IOException{
//1.得到Excel常用对象
// POIFSFileSystem fs = new POIFSFileSystem(newFileInputStream("d:/FTP/test.xls"));
POIFSFileSystem fs = new POIFSFileSystem(newFileInputStream("d:/FTP/new1.xls"));
//2.得到Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//3.得到Excel工作表对象
HSSFSheet sheet = wb.getSheetAt(0);
//总行数
int trLength = sheet.getLastRowNum();
//4.得到Excel工作表的行
HSSFRow row = sheet.getRow(0);
//总列数
int tdLength = row.getLastCellNum();
//5.得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell((short)1);
//6.得到单元格样式
CellStyle cellStyle = cell.getCellStyle();
for(int i=0;i<trLength;i++){
//得到Excel工作表的行
HSSFRow row1 = sheet.getRow(i);
for(int j=0;j<tdLength;j++){
//得到Excel工作表指定行的单元格
HSSFCell cell1 = row1.getCell(j);
/**
* 为了处理:Excel异常Cannot get a text value from a numeric cell
* 将所有列中的内容都设置成String类型格式
*/
if(cell1!=null){
cell1.setCellType(Cell.CELL_TYPE_STRING);
}
//获得每一列中的值
System.out.print(cell1.getStringCellValue()+"\t\t\t");
}
System.out.println();
}
}
Poi写入Excel
//1.创建Excel工作薄对象
HSSFWorkbook wb = new HSSFWorkbook();
//2.创建Excel工作表对象
HSSFSheet sheet = wb.createSheet("new Sheet");
//3.创建Excel工作表的行
HSSFRow row = sheet.createRow(6);
//4.创建单元格样式
CellStyle cellStyle =wb.createCellStyle();
// 设置这些样式
cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//5.创建Excel工作表指定行的单元格
row.createCell(0).setCellStyle(cellStyle);
//6.设置Excel工作表的值
row.createCell(0).setCellValue("aaaa");
row.createCell(1).setCellStyle(cellStyle);
row.createCell(1).setCellValue("bbbb");
//设置sheet名称和单元格内容
wb.setSheetName(0,"第一张工作表");
//设置单元格内容 cell.setCellValue("单元格内容");
// 最后一步,将文件存到指定位置
try
{
FileOutputStream fout = newFileOutputStream("E:/students.xls");
wb.write(fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
部署web项目
先使用 telnet ip 端口号 ping,是否ping通
查看端口号:netstat –ano 查看所有的端口号
查看被占用端口对应的PID,输入命令:netstat -aon|findstr "49157",回车,记下最后一位数字,即PID
继续输入tasklist|findstr "2720",回车,查看是哪个进程或者程序占用了2720端口
Mysql建立外键
需要外键表:sys_user 需要外键表的字段名 :depid 被指向表:system_depts
ALTER TABLE sys_user ADD CONSTRAINT depidFOREIGN KEY (depid) REFERENCES system_depts(ID) ON DELETE CASCADE;