jsp调用数据库
deviceInfos.jsp
<%@page import="java.io.BufferedReader"%>
<%@page import="java.io.FileReader"%>
<%@page import="java.io.*"%>
<%@ page import="java.sql.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="utf-8" %>
<html>
<head>
<title>jsp作业3</title>
<meta charset="utf-8">
<script src="js/jquery-1.9.1.min.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="my_css.css">
<script type="text/javascript">
var nowChangingRow;
function deleteRow(r)
{
if(confirm("您确定需要删除吗?"))
{
var nowChangingRow = r.parentNode.parentNode.rowIndex;
var table=document.getElementById("equipmentTable");
var nowRow=table.rows[nowChangingRow].cells;
location.href="${pageContext.request.contextPath}/EquipmentDeleteServlet?deviceCode="+nowRow[2].innerHTML;
}
}
function isRightTime(installationTime)
{
var i;
if(installationTime.length<19)
{
alert("输入的时间字符串过短!");
return 0;
}
/*下面是判断是否是数字*/
for(i=0;i<=3;++i)if(installationTime[i]<'0'||installationTime[i]>'9')return 0;
if(installationTime[4]!='-')return 0;
for(i=5;i<=6;++i)if(installationTime[i]<'0'||installationTime[i]>'9')return 0;
if(installationTime[7]!='-')return 0;
for(i=8;i<=9;++i)if(installationTime[i]<'0'||installationTime[i]>'9')return 0;
if(installationTime[10]!=' ')return 0;
for(i=11;i<=12;++i)if(installationTime[i]<'0'||installationTime[i]>'9')return 0;
if(installationTime[13]!=':')return 0;
for(i=14;i<=15;++i)if(installationTime[i]<'0'||installationTime[i]>'9')return 0;
if(installationTime[16]!=':')return 0;
for(i=17;i<=18;++i)if(installationTime[i]<'0'||installationTime[i]>'9')return 0;
if(installationTime.length>19)
{
alert("输入的时间字符串过长!");
return 0;
}
var nian=0,yue=0,ri=0,shi=0,fen=0,miao=0;
for(i=0;i<=3;++i)nian=nian*10+(installationTime[i]-'0');
for(i=5;i<=6;++i)yue=yue*10+(installationTime[i]-'0');
for(i=8;i<=9;++i)ri=ri*10+(installationTime[i]-'0');
for(i=11;i<=12;++i)shi=shi*10+(installationTime[i]-'0');
for(i=14;i<=15;++i)fen=fen*10+(installationTime[i]-'0');
for(i=17;i<=18;++i)miao=miao*10+(installationTime[i]-'0');
if(yue>12)
{
alert("月份不能超过12!");
return 0;
}
if((yue==1||yue==3||yue==5||yue==7||yue==8||yue==10||yue==12)&&ri>31)
{
alert("该月份日期不能超过31!");
return 0;
}
if((yue==4||yue==6||yue==9||yue==11)&&ri>30)
{
alert("该月份日期不能超过30!");
return 0;
}
if(yue==2)
{
if((nian%100!=0&&nian%4==0)||(nian%100==0&&nian%400==0))//闰年
{
if(ri>29)
{
alert("这一年2月日期不能超过29!");
return 0;
}
}
else
{
if(ri>28)
{
alert("这一年2月日期不能超过28!");
return 0;
}
}
}
if(shi>23)
{
alert("小时不能超过23!");
return 0;
}
if(fen>59)
{
alert("分钟不能超过59!");
return 0;
}
if(miao>59)
{
alert("秒数不能超过59!");
return 0;
}
return 1;
}
function isExisting(str)
{
var table=document.getElementById("equipmentTable");
var i,totRow=table.rows.length;
for(i=1;i<totRow;++i)
{
nowRow=table.rows[i].cells;
if(i!=nowChangingRow&&str===nowRow[2].innerHTML)return true;
}
return false;
}
function confirmInput1()
{
if ( $("#equipmentName1").val() == null || $("#equipmentName1").val() == "" )
{
alert("设备名称不能为空!");
$("#equipmentName1").focus();
return false;
}
if ( $("#equipmentCode1").val() == null || $("#equipmentCode1").val() == "" )
{
alert("设备编码不能为空!");
$("#equipmentCode1").focus();
return false;
}
if (isExisting($("#equipmentCode1").val()))
{
alert("设备编码不能重复!");
$("#equipmentCode1").focus();
return false;
}
if ( $("#installationTime1").val() == null || $("#installationTime1").val() == "" )
{
alert("安装时间不能为空!");
$("#installationTime1").focus();
return false;
}
if ( isRightTime( $("#installationTime1").val() )==0)
{
alert("安装时间格式必须是yyyy-MM-dd HH:mm:ss");
$("#installationTime1").focus();
return false;
}
if ( $("#file1").val() == null || $("#file1").val() == "" )
{
alert("设备图片不能为空!");
return false;
}
if(confirm("您确定需要新增吗?"))
{
return true;
}
return false;
}
function changeDispaly(r)
{
nowChangingRow = r.parentNode.parentNode.rowIndex;
var table=document.getElementById("equipmentTable");
var nowRow=table.rows[nowChangingRow].cells;
//$("#changeTable").toggle();
$("#changeTable").show();
$("#siteName2").val(nowRow[0].innerHTML);
$("#equipmentName2").val(nowRow[1].innerHTML);
$("#equipmentCode2").val(nowRow[2].innerHTML);
$("#equipmentCategory2").val(nowRow[3].innerHTML);
$("#equipmentType2").val(nowRow[4].innerHTML);
$("#installationTime2").val(nowRow[5].innerHTML);
$("#equipmentStatus2").val(nowRow[6].innerHTML);
}
function confirmInput2()
{
if ( $("#equipmentName2").val() == null || $("#equipmentName2").val() == "" )
{
alert("设备名称不能为空!");
$("#equipmentName2").focus();
return false;
}
if ( $("#equipmentCode2").val() == null || $("#equipmentCode2").val() == "" )
{
alert("设备编码不能为空!");
$("#equipmentCode2").focus();
return false;
}
if (isExisting($("#equipmentCode2").val()))
{
alert("设备编码不能重复!");
$("#equipmentCode2").focus();
return false;
}
if ( $("#installationTime2").val() == null || $("#installationTime2").val() == "" )
{
alert("安装时间不能为空!");
$("#installationTime2").focus();
return false;
}
if ( isRightTime( $("#installationTime2").val() )==0)
{
alert("安装时间格式必须是yyyy-MM-dd HH:mm:ss");
$("#installationTime2").focus();
return false;
}
if ( $("#file2").val() == null || $("#file2").val() == "" )
{
alert("设备图片不能为空!");
return false;
}
if(confirm("您确定需要修改吗?"))
{
var table=document.getElementById("equipmentTable");
var nowRow=table.rows[nowChangingRow].cells;
$("#oldEquipmentCategory").val(nowRow[2].innerHTML);
nowChangingRow=-1;
return true;
}
$("#changeTable").hide();
return true;
}
function findInformation()
{
var table=document.getElementById("equipmentTable");
var i,totRow=table.rows.length;
for(i=1;i<totRow;++i)
{
var nowRow=table.rows[i].cells;
if(nowRow[0].innerHTML != $("#findSiteName").val()&& nowRow[1].innerHTML != $("#findEquipmentName").val())
{
table.deleteRow(i);
i=i-1;totRow=totRow-1;
}
}
}
$(function() //初始化
{
$("#changeTable").toggle();
});
</script>
</link>
</head>
<body>
<form method="POST" id="findTable">
<fieldset>
<legend>查询条件</legend>
站点名称(*)：<input type="text" value="" id="findSiteName"><br />
设备名称(*)：<input type="text" value="" id="findEquipmentName"><br />
<input type="button" id="findButton" value="查询" onclick="findInformation();" />
<form action="">
<button onclick="">返回</button>
</form>
</fieldset>
</form>
<table id="equipmentTable" border="1">
<caption>设备信息</caption>
<thead>
<tr>
<th>站点名称</th><th>设备名称</th><th>设备编码</th><th>设备分类</th><th>设备类型</th><th>安装时间</th><th>设备状态</th><th>操作</th>
</tr>
<% Connection con;
Statement sql;
ResultSet rs;
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(Exception e)
{
out.println("忘记把MySQL数据库的JDBC-数据库驱动程序复制到JDK的扩展目录中");
}
try
{
String uri= "jdbc:mysql://localhost:3306/deviceinfo";
String user="test";
String password="123456";
con=DriverManager.getConnection(uri,user,password);
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM Deviceinfo ");
while(rs.next())
{
out.print("<tr>");
out.print("<td>"+rs.getString("siteName")+"</td>");
out.print("<td>"+rs.getString("deviceName")+"</td>");
out.print("<td>"+rs.getString("deviceCode")+"</td>");
out.print("<td>"+rs.getString("deviceCategory")+"</td>");
out.print("<td>"+rs.getString("deviceType")+"</td>");
out.print("<td>"+rs.getString("installTime")+"</td>");
out.print("<td >"+rs.getString("deviceState")+"</td>");
out.print("</td><td><a href='#' onclick='changeDispaly(this);'>修改</a> <a href='#' onclick='deleteRow(this);'>删除</a></td></tr>");
out.print("</tr>") ;
}
out.print("</table>");
con.close();
}
catch(SQLException e)
{
out.print(e);
}
%>
</thead>
</table>
<form method="post" id="addTable" enctype="multipart/form-data" action="EquipmentAddServlet">
<fieldset>
<legend>新增</legend>
站点名称(*)：
<select id="siteName1" name="siteName1">
<option value="昌都生态监测站">昌都生态监测站</option>
<option value="申扎生态监测站">申扎生态监测站</option>
<option value="日喀则生态监测站">日喀则生态监测站</option>
</select>
<br />
设备名称(*)：<input type="text" value="" id="equipmentName1" name="equipmentName1"><br />
设备编码(*)：<input type="text" value="" id="equipmentCode1" name="equipmentCode1"><br />
设备分类(*)：
<select id="equipmentCategory1" name="equipmentCategory1">
<option value="土壤">土壤</option>
<%
for(int i=1;i<=10;++i)out.print("<option value="+i+">"+i+"</option>");
%>
</select>
<br />
设备类型(*)：
<select id="equipmentType1" name="equipmentType1">
<option value="冻土检测">冻土检测</option>
<%
for(int i=1;i<=10;++i)out.print("<option value="+i+">"+i+"</option>");
%>
</select>
<br />
安装时间(*)：<input type="text" value="" id="installationTime1" name="installationTime1"><br /><br />
设备状态(*)
<select id="equipmentStatus1" name="equipmentStatus1">
<option value="正常">正常</option>
<option value="异常">异常</option>
</select>
<br />
<img id="img1" src=""/>
设备图片(*)<input type="file" value="" id="file1" name="file1"><br />
<input type="submit" id="button1" value="确定" onclick="return confirmInput1();" />
</fieldset>
</form>
<script>
document.getElementById("file1").onchange=function()
{
var reader=new FileReader();
var tmpfile=document.getElementById("file1").files;
reader.readAsDataURL(tmpfile[0]);
reader.onload=function(){document.getElementById("img1").src=reader.result;};
};
</script>
<form method="POST" id="changeTable" enctype="multipart/form-data" action="EquipmentEditServlet">
<fieldset>
<legend>修改</legend>
站点名称(*)：
<select id="siteName2" name="siteName2">
<option value="昌都生态监测站">昌都生态监测站</option>
<option value="申扎生态监测站">申扎生态监测站</option>
<option value="日喀则生态监测站">日喀则生态监测站</option>
</select>
<br />
设备名称(*)：<input type="text" value="" id="equipmentName2" name="equipmentName2"><br />
设备编码(*)：<input type="text" value="" id="equipmentCode2" name="equipmentCode2"><br />
<input type="text" value="" id="oldEquipmentCategory" name="oldEquipmentCategory" hidden>
设备分类(*)：
<select id="equipmentCategory2" name="equipmentCategory2">
<option value="土壤">土壤</option>
<%
for(int i=1;i<=10;++i)out.print("<option value="+i+">"+i+"</option>");
%>
</select>
<br />
设备类型(*)：
<select id="equipmentType2" name="equipmentType2">
<option value="冻土检测">冻土检测</option>
<%
for(int i=1;i<=10;++i)out.print("<option value="+i+">"+i+"</option>");
%>
</select>
<br />
安装时间(*)：<input type="text" value="" id="installationTime2" name="installationTime2"><br /><br />
设备状态(*)
<select id="equipmentStatus2" name="equipmentStatus2">
<option value="正常">正常</option>
<option value="异常">异常</option>
</select>
<br />
<img id="img2" src=""/>
设备图片(*)<input type="file" value="" id="file2" name="file2"><br />
<input type="submit" id="button2" value="确定" onclick="return confirmInput2();" />
</fieldset>
</form>
<script>
document.getElementById("file2").onchange=function()
{
var reader=new FileReader();
var tmpfile=document.getElementById("file2").files;
reader.readAsDataURL(tmpfile[0]);
reader.onload=function(){document.getElementById("img2").src=reader.result;};
};
</script>
</body>
</html>
EquipmentAddServlet
package myServlet;
import java.io.*;
import java.sql.*;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.Part;
@WebServlet("/EquipmentAddServlet")
@MultipartConfig
public class EquipmentAddServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private String getFileName(Part part)
{
String header = part.getHeader("Content-Disposition");
System.out.println("header:" + header);
int begin=header.indexOf("filename=\"")+"filename=\"".length();
int end=header.lastIndexOf("\"");
String fileName=header.substring(begin,end);
return fileName;
}
public EquipmentAddServlet()
{
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
request.setCharacterEncoding("utf-8");
Part part=request.getPart("file1");
String fileName=this.getFileName(part);
String filePath=request.getServletContext().getRealPath("/upload")+ File.separator +fileName;
part.write(filePath);
Connection con = null;
Statement sql;
ResultSet rs;
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(Exception e)
{
}
try
{
String uri= "jdbc:mysql://localhost:3306/deviceinfo";
String user="test";
String password="123456";
con=DriverManager.getConnection(uri,user,password);
sql=con.createStatement();
String addOperate = "insert into deviceinfo(siteName,deviceName,deviceCode,deviceCategory,deviceType,installTime,deviceState) values (?,?,?,?,?,?,?)";
PreparedStatement preparedStatement = con.prepareStatement(addOperate);
preparedStatement.setString(1, request.getParameter("siteName1"));
preparedStatement.setString(2, request.getParameter("equipmentName1"));
preparedStatement.setString(3, request.getParameter("equipmentCode1"));
preparedStatement.setString(4, request.getParameter("equipmentCategory1"));
preparedStatement.setString(5, request.getParameter("equipmentType1"));
preparedStatement.setString(6, request.getParameter("installationTime1"));
preparedStatement.setString(7, request.getParameter("equipmentStatus1"));
preparedStatement.executeUpdate();
}
catch(SQLException e)
{
}
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
response.sendRedirect(request.getHeader("referer"));
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
EquipmentDeleteServlet
package myServlet;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
@WebServlet("/EquipmentDeleteServlet")
public class EquipmentDeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public EquipmentDeleteServlet()
{
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
request.setCharacterEncoding("utf-8");
String deviceCode=request.getParameter("deviceCode");
Connection con = null;
Statement sql;
ResultSet rs;
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(Exception e)
{
}
try
{
String uri= "jdbc:mysql://localhost:3306/deviceinfo";
String user="test";
String password="123456";
con=DriverManager.getConnection(uri,user,password);
sql=con.createStatement();
request.setCharacterEncoding("utf-8");
String deleteOperate = "delete from deviceinfo where deviceCode =?";
PreparedStatement preparedStatement = con.prepareStatement(deleteOperate);
preparedStatement.setString(1,deviceCode);
preparedStatement.executeUpdate();
}
catch(SQLException e)
{
}
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
response.sendRedirect(request.getHeader("referer"));
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
EquipmentEditServlet
package myServlet;
import java.io.*;
import java.sql.*;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.Part;
@WebServlet("/EquipmentEditServlet")
@MultipartConfig
public class EquipmentEditServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private String getFileName(Part part)
{
String header = part.getHeader("Content-Disposition");
System.out.println("header:" + header);
int begin=header.indexOf("filename=\"")+"filename=\"".length();
int end=header.lastIndexOf("\"");
String fileName=header.substring(begin,end);
return fileName;
}
public EquipmentEditServlet()
{
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
request.setCharacterEncoding("utf-8");
Part part=request.getPart("file2");
String fileName=this.getFileName(part);
String filePath=request.getServletContext().getRealPath("/upload")+ File.separator +fileName;
part.write(filePath);
Connection con = null;
Statement sql;
ResultSet rs;
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(Exception e)
{
}
try
{
String uri= "jdbc:mysql://localhost:3306/deviceinfo";
String user="test";
String password="123456";
con=DriverManager.getConnection(uri,user,password);
sql=con.createStatement();
request.setCharacterEncoding("utf-8");
String changeOperate = "update deviceinfo set siteName = ? ,deviceName = ?,deviceCode=?,deviceCategory=?,deviceType=?,installTime=?,deviceState=? WHERE deviceCode =?";
PreparedStatement preparedStatement = con.prepareStatement(changeOperate);
preparedStatement.setString(1, request.getParameter("siteName2"));
preparedStatement.setString(2, request.getParameter("equipmentName2"));
preparedStatement.setString(3, request.getParameter("equipmentCode2"));
preparedStatement.setString(4, request.getParameter("equipmentCategory2"));
preparedStatement.setString(5, request.getParameter("equipmentType2"));
preparedStatement.setString(6, request.getParameter("installationTime2"));
preparedStatement.setString(7, request.getParameter("equipmentStatus2"));
preparedStatement.setString(8,request.getParameter("oldEquipmentCategory"));
preparedStatement.executeUpdate();
}
catch(SQLException e)
{
}
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
response.sendRedirect(request.getHeader("referer"));
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
