servlet对数据库的基本操作

最新推荐文章于 2020-11-25 18:29:03 发布

Imys_

最新推荐文章于 2020-11-25 18:29:03 发布

阅读量1.8k

点赞数

分类专栏： servlet 数据库表单文章标签： servlet 数据库 server equals string html

本文链接：https://blog.csdn.net/Imys_/article/details/7727906

版权

servlet 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

表单

1 篇文章 0 订阅

订阅专栏

登陆页面login.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>login.html</title>

<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="this is my page">
<meta http-equiv="content-type" content="text/html; charset=utf-8">


</head>

<body>



<form action="/dbdemo/servlet/LoginServlet" method="post">
用户账号:<input type="text" name="deptno" /><br><br><hr>
<input type="submit" value="登陆" />
</form>

<form action="/dbdemo/servlet/AddServlet" method="post">
用户账号：<input type="text" name="deptno" /><br>
用户名：    <input type="text" name="dname" /><br>
职位：        <input type="text" name="loc" /><br>
<input type="submit" value="注册" />
</form>
</body>
</html>

处理页面handle.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>handle.html</title>

<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="this is my page">
<meta http-equiv="content-type" content="text/html; charset=utf-8">



</head>

<body>
<form action="/dbdemo/servlet/QueryAllServlet" method="post">
<input type="submit" name="" value="查询所有用户" /><hr>
</form>
<form action="/dbdemo/servlet/DeleteServlet" method="post">
账号：<input type="text" name="delDeptno" />
<input type="submit" name="" value="删除" /><hr>
</form>

<form action="/dbdemo/servlet/UpdateServlet" method="post">
账号：<input type="text" name="updDeptno" />
名称：<input type="text" name="updDname" />
职位：<input type="text" name="updLoc" />
<input type="submit" name="" value="更新" /><hr>
</form>

<form action="/dbdemo/servlet/QueryByIdServlet" method="post">
账号：<input type="text" name="qDeptno" />
<input type="submit" name="" value="查询" /><hr>
</form>

</body>
</html>

dao层

DbUtils.java

package com.neusoft.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class DbUtils {
private static Connection conn = null;

public static Connection getConnection()
{

// 1、加载oracle驱动
// new oracle.jdbc.driver.OracleDriver();
// Class.forName("oracle.jdbc.driver.OracleDriver");
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// 2、连接数据库
conn = DriverManager
.getConnection("jdbc:oracle:thin:@localhost:1521:oracle",
"scott", "imys0603");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;

}

public static void closeConnection()
{
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void closeStatement(Statement stmt)
{
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

Dept.java

package com.neusoft.dao;

public class Dept {
private int deptno;
private String dname;
private String loc;
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}

}

LoginDaoImpl.java

package com.neusoft.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.dao.DbUtils;
import com.neusoft.dao.Dept;

public class LoginDaoImpl implements LoginDaoInterface{

public void delete(int deptno) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
pstmt = conn.prepareStatement("DELETE FROM dept WHERE deptno=?");
pstmt.setInt(1, deptno);
pstmt.executeUpdate();

} catch (SQLException e) {
e.printStackTrace();
} finally {

DbUtils.closeStatement(pstmt);
DbUtils.closeConnection();

}
}

public void insert(Dept dept) {

PreparedStatement pstmt = null;
try {
Connection conn = DbUtils.getConnection();
pstmt = conn.prepareStatement("insert into dept(deptno,dname,loc) values(?,?,?)");
pstmt.setInt(1, dept.getDeptno());
pstmt.setString(2, dept.getDname());
pstmt.setString(3, dept.getLoc());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeConnection();
DbUtils.closeStatement(pstmt);
}

}

public boolean login(int deptno) {
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Connection conn=DbUtils.getConnection();
pstmt = conn.prepareStatement("select * from dept where deptno=?");
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
if (rs.next()) {
return true;
} else {
return false;
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
} finally {
// 4、关闭数据库

try {
if (pstmt != null) {
pstmt.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}

}
}

public List<Dept> query() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List<Dept> list = new ArrayList<Dept>();
try {
conn = DbUtils.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
while (rs.next()) {
Dept dept = new Dept();
dept.setDeptno(rs.getInt(1));
dept.setDname(rs.getString(2));
dept.setLoc(rs.getString(3));
list.add(dept);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}

} catch (SQLException e) {
e.printStackTrace();
}
DbUtils.closeStatement(stmt);
DbUtils.closeConnection();

}
return list;
}

public void update(Dept dept) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
pstmt = conn.prepareStatement("UPDATE dept SET dname=?,loc=? WHERE deptno=?");
pstmt.setString(1, dept.getDname());
pstmt.setString(2, dept.getLoc());
pstmt.setInt(3, dept.getDeptno());
pstmt.executeUpdate();

} catch (SQLException e) {
e.printStackTrace();
} finally {

DbUtils.closeStatement(pstmt);
DbUtils.closeConnection();

}

}

public Dept queryById(int deptno) {

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Dept dept = new Dept();
try {
conn = DbUtils.getConnection();
String sql = "SELECT deptno,dname,loc FROM DEPT WHERE deptno = ?";

pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();

while(rs.next()){
dept.setDeptno(rs.getInt("deptno"));
dept.setDname(rs.getString("dname"));
dept.setLoc(rs.getString("loc"));
}

} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}

} catch (SQLException e) {
e.printStackTrace();
}
DbUtils.closeStatement(pstmt);
DbUtils.closeConnection();

}
return dept;
}

}

LoginDaoInterface.java

package com.neusoft.dao;

import java.util.List;

import com.neusoft.dao.Dept;

public interface LoginDaoInterface {
boolean login(int deptno);
void update(Dept dept);
void insert(Dept dept);
void delete(int deptno);
List<Dept> query();
Dept queryById(int deptno);
}

filter层

EncodeFilter.java

package com.neusoft.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class EncodeFilter implements Filter{
private FilterConfig config=null;
public void destroy() {
// TODO Auto-generated method stub

}

public void doFilter(ServletRequest req, ServletResponse res,
FilterChain chain) throws IOException, ServletException {
// TODO Auto-generated method stub
HttpServletRequest request=(HttpServletRequest)req;
HttpServletResponse response=(HttpServletResponse)res;
// request.setCharacterEncoding("utf-8");
// response.setCharacterEncoding("utf-8");
String en=config.getInitParameter("en");
request.setCharacterEncoding(en);
response.setCharacterEncoding(en);
chain.doFilter(request, response);
}

public void init(FilterConfig con) throws ServletException {
// TODO Auto-generated method stub
this.config=con;
}

}

servlet层

AddServlet.java

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.Dept;
import com.neusoft.dao.LoginDaoImpl;
import com.neusoft.dao.LoginDaoInterface;

public class AddServlet extends HttpServlet {

/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

this.doPost(request, response);
}

/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");

Dept dept = new Dept();
String deptnoo = request.getParameter("deptno");
int deptno = Integer.parseInt(deptnoo);
String dname = request.getParameter("dname");
String loc = request.getParameter("loc");

dept.setDeptno(deptno);
dept.setDname(dname);
dept.setLoc(loc);

LoginDaoInterface ldi=new LoginDaoImpl();
ldi.insert(dept);

out.print("<script language='javascript'> alert('账号注册成功！');javascript:location='../login.html';</script>");

out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}

}

deleteServlet.java

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.LoginDaoImpl;
import com.neusoft.dao.LoginDaoInterface;

public class DeleteServlet extends HttpServlet {

/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

this.doPost(request, response);
}

/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");

String deptnoo = request.getParameter("delDeptno");
int deptno = Integer.parseInt(deptnoo);

LoginDaoInterface ldi=new LoginDaoImpl();
ldi.delete(deptno);
out.println("账号 " + deptno +" 已经被删除!");
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}

}

loginServlet.java

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.LoginDaoImpl;
import com.neusoft.dao.LoginDaoInterface;

public class LoginServlet extends HttpServlet {

/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

this.doPost(request, response);
}

/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");

PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");

String deptnoo = request.getParameter("deptno");
int deptno = Integer.parseInt(deptnoo);

LoginDaoInterface ldi=new LoginDaoImpl();
boolean result=ldi.login(deptno);
if (result == true) {
out.println("welcome " + deptno +" !");
response.sendRedirect("../handle.html");
} else {
out.print("<script language='javascript'> alert('账号木有注册！');javascript:location='../login.html';</script>");
}
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}

}

QueryAllServlet.java

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.Dept;
import com.neusoft.dao.LoginDaoImpl;
import com.neusoft.dao.LoginDaoInterface;

public class QueryAllServlet extends HttpServlet {

/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

this.doPost(request, response);
}

/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");
LoginDaoInterface ldi=new LoginDaoImpl();
List<Dept> depts=ldi.query();
for(int i=0;i<depts.size();i++){
out.println(depts.get(i).getDeptno()+"\t"+depts.get(i).getDname()
+"\t"+depts.get(i).getLoc()+"<br>");
}
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}

}

QueryByIdServlet.java

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.Dept;
import com.neusoft.dao.LoginDaoImpl;
import com.neusoft.dao.LoginDaoInterface;

public class QueryByIdServlet extends HttpServlet {

/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

this.doPost(request, response);
}

/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");

String deptnoo = request.getParameter("qDeptno");
int deptno = Integer.parseInt(deptnoo);

LoginDaoInterface ldi=new LoginDaoImpl();
Dept dept = ldi.queryById(deptno);
out.println(dept.getDeptno() + "\t" + dept.getDname() + "\t" + dept.getLoc());

out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}

}

UpdateServlet.java

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.Dept;
import com.neusoft.dao.LoginDaoImpl;
import com.neusoft.dao.LoginDaoInterface;

public class UpdateServlet extends HttpServlet {

/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

this.doPost(request, response);
}

/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
out.println("<HTML>");
out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
out.println(" <BODY>");
Dept dept=new Dept();

String deptnoo = request.getParameter("updDeptno");
int deptno = Integer.parseInt(deptnoo);
String dname = request.getParameter("updDname");
String loc = request.getParameter("updLoc");
dept.setDeptno(deptno);
dept.setDname(dname);
dept.setLoc(loc);
LoginDaoInterface ldi=new LoginDaoImpl();
ldi.update(dept);
out.println("更新成功！");
out.println(" </BODY>");
out.println("</HTML>");
out.flush();
out.close();
}

}

web.xml

<?xml version="1.0" encoding="utf-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

<filter>
<filter-name>encode</filter-name>
<filter-class>com.neusoft.filter.EncodeFilter</filter-class>
<init-param>
<param-name>en</param-name>
<param-value>utf-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encode</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>

<servlet>
<servlet-name>LoginServlet</servlet-name>
<servlet-class>com.neusoft.servlet.LoginServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>AddServlet</servlet-name>
<servlet-class>com.neusoft.servlet.AddServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>QueryAllServlet</servlet-name>
<servlet-class>com.neusoft.servlet.QueryAllServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>DeleteServlet</servlet-name>
<servlet-class>com.neusoft.servlet.DeleteServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>UpdateServlet</servlet-name>
<servlet-class>com.neusoft.servlet.UpdateServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>QueryByIdServlet</servlet-name>
<servlet-class>com.neusoft.servlet.QueryByIdServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>LoginServlet</servlet-name>
<url-pattern>/servlet/LoginServlet</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>AddServlet</servlet-name>
<url-pattern>/servlet/AddServlet</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>QueryAllServlet</servlet-name>
<url-pattern>/servlet/QueryAllServlet</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>DeleteServlet</servlet-name>
<url-pattern>/servlet/DeleteServlet</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>UpdateServlet</servlet-name>
<url-pattern>/servlet/UpdateServlet</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>QueryByIdServlet</servlet-name>
<url-pattern>/servlet/QueryByIdServlet</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>