package org.student.dao;
import java.sql.Connection;
import org.student.entity.Login;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//处理登录
public class LoginDao {
//private static PreparedStatement pstmt=null;
//private static Connection connection=null;
//private static ResultSet rs=null;
//private static int result=-1;
private final static String URI = “jdbc:mysql://localhost:3306/login?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false”;
private final static String dbUserName=“root”;
private final static String dbPassword=“123456”;
private final static String DRIVER = “com.mysql.jdbc.Driver”;
public static int login(Login login) {
Connection connection=null;
PreparedStatement pstmt=null;
int result=-1;
//boolean flag=false;
int flag=-1;//-1为登录异常 0为用户名和密码错误
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection(URI,dbUserName,dbPassword);
String sql="select count(*) from user where user_name=? and user_password=?";
pstmt =connection.prepareCall(sql);
pstmt.setString(1, login.getUser_name());
pstmt.setString(2, login.getUser_password());
rs=pstmt.executeQuery();
if(rs.next()) {
result=rs.getInt(1);
}
if(result>0) {
return 1;
}else {
return 0;
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
return -1;
}catch(SQLException e) {
e.printStackTrace();
return -1;
}
catch(Exception e) {
e.printStackTrace();
return -1;
}finally {
try{
if(rs!=null)
rs.close();
if(pstmt!=null)
pstmt.close();
if(connection!=null)
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}
}
}
//根据学号删除
public boolean deletebyid(int user_id) {
Connection connection=null;
PreparedStatement pstmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection(URI,dbUserName,dbPassword);
String sql="delete from imformation where user_id=?";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, user_id);
int count=pstmt.executeUpdate();
if(count>0) {
return true;
}else
return false;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return false;
}catch(SQLException e) {
e.printStackTrace();
return false;
}catch(Exception e) {
e.printStackTrace();
return false;
}
finally {
try {
if(pstmt!=null)pstmt.close();
if(connection!=null)connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
//根据id修改
public boolean update(int user_id, Login login) {
Connection connection=null;
PreparedStatement pstmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection(URI,dbUserName,dbPassword);
String sql="update imformation set user_name=?,admin=?,rate=?,cost=?,time=?,money=? where user_id=?";
pstmt=connection.prepareStatement(sql);
pstmt.setString(1, login.getUser_name());
pstmt.setString(2, login.getAdmin());
pstmt.setString(3, login.getRate());
pstmt.setString(4, login.getCost());
pstmt.setString(5, login.getTime());
pstmt.setString(6, login.getMoney());
pstmt.setInt(7, user_id);
int count=pstmt.executeUpdate();
if(count>0) {
return true;
}else
return false;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return false;
}catch(SQLException e) {
e.printStackTrace();
return false;
}catch(Exception e) {
e.printStackTrace();
return false;
}
finally {
try {
if(pstmt!=null)pstmt.close();
if(connection!=null)connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
//查询全部
public List<Login> all() {
List<Login> logins=new ArrayList<>();
Login login=null;
Connection connection=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
if(connection==null) {
connection=DriverManager.getConnection(URI,dbUserName,dbPassword);
}
//DriverManager.getConnection(URI,dbUserName,dbPassword);
String sql="select * from imformation";
pstmt=connection.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
int id=rs.getInt("user_id");
String name=rs.getString("user_name");
String admin=rs.getString("admin");
String rate=rs.getString("rate");
String cost=rs.getString("cost");
String time=rs.getString("time");
String money=rs.getString("money");
login=new Login(id,name,admin,rate,cost,time,money);
logins.add(login);
}
return logins;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}catch(Exception e) {
e.printStackTrace();
return null;
}
finally {
try {
if(rs!=null)rs.close();
if(pstmt!=null)pstmt.close();
if(connection!=null)connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
public boolean isExist(int user_id) {//判断此人存不存在
return queryinfobyid(user_id)==null?false:true;
}
public boolean addinfo(Login login) {
Connection connection=null;
PreparedStatement pstmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection(URI,dbUserName,dbPassword);
String sql="insert into imformation values(?,?,?,?,?,?,?)";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, login.getUser_id());
pstmt.setString(2, login.getUser_name());
pstmt.setString(3, login.getAdmin());
pstmt.setString(4, login.getRate());
pstmt.setString(5, login.getCost());
pstmt.setString(6, login.getTime());
pstmt.setString(7, login.getMoney());
int count=pstmt.executeUpdate();
if(count>0) {
return true;
}else
return false;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return false;
}catch(SQLException e) {
e.printStackTrace();
return false;
}catch(Exception e) {
e.printStackTrace();
return false;
}
finally {
try {
if(pstmt!=null)pstmt.close();
if(connection!=null)connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
//根据id查
public Login queryinfobyid(int user_id) {
Login login=null;
Connection connection=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
if(connection==null) {
connection=DriverManager.getConnection(URI,dbUserName,dbPassword);
}
String sql="select * from imformation where user_id=? ";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, user_id);
rs=pstmt.executeQuery();
if(rs.next()) {
int id=rs.getInt("user_id");
String name=rs.getString("user_name");
String admin=rs.getString("admin");
String rate=rs.getString("rate");
String cost=rs.getString("cost");
String time=rs.getString("time");
String money=rs.getString("money");
login=new Login(id,name,admin,rate,cost,time,money);
}
return login;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}catch(Exception e) {
e.printStackTrace();
return null;
}
finally {
try {
if(rs!=null)rs.close();
if(pstmt!=null)pstmt.close();
if(connection!=null)connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
package org.student.entity;
public class Login {
private int user_id;
private String user_name;
private String user_password;
private String admin;
private String rate;
private String cost;
private String time;
private String money;
public String getAdmin() {
return admin;
}
public void setAdmin(String admin) {
this.admin = admin;
}
public String getRate() {
return rate;
}
public void setRate(String rate) {
this.rate = rate;
}
public String getCost() {
return cost;
}
public void setCost(String cost) {
this.cost = cost;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public String getMoney() {
return money;
}
public void setMoney(String money) {
this.money = money;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getUser_password() {
return user_password;
}
public void setUser_password(String user_password) {
this.user_password = user_password;
}
public Login(int user_id,String user_name,String admin,String rate,String cost,String time,String money) {
super();
this.user_id = user_id;
this.user_name = user_name;
this.admin = admin;
this.rate = rate;
this.cost = cost;
this.time = time;
this.money = money;
}
public Login(String user_name,String admin,String rate,String cost,String time,String money) {
this.user_name = user_name;
this.admin = admin;
this.rate = rate;
this.cost = cost;
this.time = time;
this.money = money;
}
public Login(String user_name,String user_password) {
super();
this.user_name = user_name;
this.user_password = user_password;
}
public Login(int user_id,String user_name,String user_password) {
super();
this.user_id=user_id;
this.user_name = user_name;
this.user_password = user_password;
}
@Override
public String toString() {
return this.getUser_id()+"-"+this.getUser_name()+"-"+this.getAdmin()+"-"+this.getRate()+"-"+this.getCost()+"-"+this.getTime()+"-"+this.getMoney();
}
public Login() {
}
}
package org.student.service;
import java.util.List;
import org.student.dao.LoginDao;
import org.student.entity.Login;
import org.student.dao.LoginDao;
//业务逻辑层 查+增
public class infoservice {
LoginDao logindao=new LoginDao();
public Login querybyid(int user_id) {//根据学号查询
return logindao.queryinfobyid(user_id);
}
public List<Login> queryall(){//查询全部
return logindao.all();
}
public boolean updatebyid(int user_id,Login login) {//根据ID更新
if(logindao.isExist(user_id)) {
return logindao.update(user_id, login);
}else {
return false;
}
}
public boolean deletebyid(int user_id){//删除
if(logindao.isExist(user_id)) {
return logindao.deletebyid(user_id);
}else {
return false;
}
}
public boolean addinfo(Login login) {
if(!logindao.isExist(login.getUser_id())) {//不存在,添加
logindao.addinfo(login);
return true;
}else {
System.out.println("此人已存在");
return false;
}
}
public static void main(String[] args) {
}
}
package org.student.servlet;
import org.student.entity.Login;
import org.student.service.infoservice;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/addservlet")
public class addservlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding(“utf-8”);
int user_id=Integer.parseInt(request.getParameter(“user_id”));
String user_name=request.getParameter(“user_name”);
String admin=request.getParameter(“admin”);
String rate=request.getParameter(“rate”);
String cost=request.getParameter(“cost”);
String time=request.getParameter(“time”);
String money=request.getParameter(“money”);
Login login =new Login(user_id,user_name,admin,rate,cost,time,money);
infoservice service=new infoservice();
boolean result=service.addinfo(login);
//设置响应编码
response.setContentType("text/html;charset=utf-8");
response.setCharacterEncoding("utf-8");
PrintWriter out=response.getWriter();//响应对象
if(result) {
out.println("增加成功");
response.sendRedirect("queryservlet");
}else {
out.println("增加失败");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
package org.student.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.student.service.infoservice;
public class deleteservlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//删除
request.setCharacterEncoding(“utf-8”);
//接受前端传来的学号
int id=Integer.parseInt(request.getParameter(“user_id”));
infoservice service=new infoservice();
boolean result=service.deletebyid(id);
response.setContentType(“text/html;charset=utf-8”);
response.setCharacterEncoding(“utf-8”);
if(result) {
//response.getWriter().println(“删除成功”);
response.sendRedirect(“queryservlet”);//重新查询信息
}else {
response.getWriter().println(“删除失败”);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
package org.student.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.student.entity.Login;
import org.student.service.infoservice;
public class querybyidservlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
int user_id=Integer.parseInt(request.getParameter("user_id"));//接受前端ID
infoservice service=new infoservice();
Login login=service.querybyid(user_id);
System.out.println(login);
//将数据发送到jsp页面
//如果request中没有数据,使用重定向跳转response.sendRedirect();
//如果request中有数据(request.setAttribute()),使用请求转发跳转
request.setAttribute("login", login);
request.getRequestDispatcher("indexinfo.jsp").forward(request,response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
package org.student.servlet;
import java.io.IOException;
import java.util.List;
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 org.student.entity.Login;
import org.student.service.infoservice;
/**
-
Servlet implementation class queryservlet
*/
public class queryservlet extends HttpServlet {
private static final long serialVersionUID = 1L;/**
- @see HttpServlet#HttpServlet()
*/
public queryservlet() {
super();
// TODO Auto-generated constructor stub
}
/**
- @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding(“utf-8”);
infoservice service=new infoservice();
List logins=service.queryall();
System.out.println(logins);
request.setAttribute(“logins”, logins);
//因为requset域中有数据,因此需要通过请求转发的方式进行跳转(重定向会丢失request域)
//pageContext<request<session<application越小性能越好
request.getRequestDispatcher(“index.jsp”).forward(request, response);
}
/**
- @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
- @see HttpServlet#HttpServlet()
}
package org.student.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.student.entity.Login;
import org.student.service.infoservice;
public class updateservlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
int id=Integer.parseInt(request.getParameter("user_id"));
//修改后的内容
String user_name=request.getParameter("user_name");
String admin=request.getParameter("admin");
String rate=request.getParameter("rate");
String cost=request.getParameter("cost");
String time=request.getParameter("time");
String money=request.getParameter("money");
//将修改后的内容封装到javabean
Login login= new Login(user_name,admin,rate,cost,time,money);
infoservice service=new infoservice();
boolean result=service.updatebyid(id, login);
response.setContentType("text/html;charset=utf-8");
response.setCharacterEncoding("utf-8");
if(result) {
// response.getWriter().println("删除成功");
response.sendRedirect("queryservlet");
}else {
response.getWriter().println("修改失败");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
<%@page import=“org.student.entity.Login”%>
<%@page import=“java.util.List”%>
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8”%>
<%
}
%>
</table>
<a href="add.jsp">新增</a>
ID | 用户名 | 剩余费用 | 操作 |
---|---|---|---|
<%= login.getUser_id() %> | <%= login.getUser_name() %> | <%= login.getMoney() %> | 删除 |
Login login=(Login)request.getAttribute("login");
%>
<!-- 通过表单展示此人 -->
<form action="updateservlet">
ID:<input type="text" name="user_id"value="<%=login.getUser_id()%>"readonly="readonly"/><br/>
用户名:<input type="text" name="user_name" value="<%=login.getUser_name()%>"/><br/>
管理员:<input type="text" name="admin" value="<%=login.getAdmin()%>"/><br/>
费率:<input type="text" name="rate" value="<%=login.getRate()%>"/><br/>
花费:<input type="text" name="cost" value="<%=login.getCost()%>"/><br/>
时间:<input type="text" name="time" value="<%=login.getTime()%>"/><br/>
剩余钱数:<input type="text" name="money" value="<%=login.getMoney()%>"/><br/>
<input type="submit" value="修改">
<a href="queryservlet">返回</a>
</form>
v