如有不妥之处请大家赐教(源码文章末尾有连接)(此项目用的最新版的JDBC)
1.项目整体结构
2.实现效果图(没写样式)
3.数据库部分
4.代码部分
Biz.java(此类将被Servlet调用)
package biz;
import java.sql.Connection;
import java.util.List;
import dao.userDao;
import model.User;
import util.FactoryDemo;
public class Biz {
//数据库连接类
FactoryDemo fa = new FactoryDemo();
//事先声明
Connection conn = null;
//数据库插入数据的方法(被Servlet调用)
public boolean InsertData(String id,String user,String pwd) throws Exception {
boolean flag = false;
User use = new User();
use.setId(Integer.parseInt(id));
use.setUsername(user);
use.setPassword(pwd);
conn = fa.getConnection();
//Dao层的方法
int result = userDao.InsertUser(use, conn);
if(result>0) {
flag = true;
}
conn.close();
return flag;
}
//遍历所有对象存到 集合中并返回
public List<User> findAll() throws Exception{
conn = fa.getConnection();
List<User> use= userDao.selectData(conn);
conn.close();
return use;
}
//删除单个对象
public boolean delUser(String id) throws Exception {
boolean flag = false;
conn = fa.getConnection();
//返回SQL执行结构的数值(影响了几行)
int result = userDao.delDao(conn, id);
if(result>0) {
flag=true;
}
return flag;
}
//返回通过单个id查询得到的相应的对象
public User selectSingleUser(String id) throws Exception {
conn = fa.getConnection();
User result = userDao.selectSingle(conn,id);
return result;
}
//保存数据
public boolean saveUser(String id,String username,String password,String oldId) throws Exception {
boolean flag = false;
conn = fa.getConnection();
int result = userDao.saveDao(conn, id,username,password,oldId);
//System.out.println("........"+result);
if(result>0) {
flag=true;
}
return flag;
}
}
Dao层(此类被Biz类调用)
userDao.java
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.User;
import util.FactoryDemo;
public class userDao {
//在Biz中被调用
public static int InsertUser(User user ,Connection conn) throws Exception {
String sql = "insert into user(id,username,password) values (?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, user.getId());
ps.setString(2, user.getUsername());
ps.setString(3, user.getPassword());
int result = ps.executeUpdate();
//关闭资源
ps.close();
return result;
}
//多个对象数据的查询
public static List<User> selectData(Connection conn) throws Exception {
List<User> users = new ArrayList<User>();
String sql = "select * from user";
PreparedStatement ps = conn.prepareStatement(sql);
//执行SQL并且返回结果集合
ResultSet rs = ps.executeQuery();
while(rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
users.add(user);
}
return users;
}
//用来删除USer
public static Integer delDao(Connection conn,String id) throws Exception {
String sql = "delete from user where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,id);
//SQL执行返回结果
int result = ps.executeUpdate();
return result;
}
//得到一个id所对应的那个对象的其他值(得到一条记录)
public static User selectSingle(Connection conn, String id) throws Exception {
//UPDATE user SET username = '123321' WHERE id = 2 update user set username = '?' where id =?
//SQL语句
User user = null;
String sql = "select * from user where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,id);
ResultSet rs = ps.executeQuery();
//全部执行
while(rs.next()) {
User use = new User();
use.setId(rs.getInt("id"));
use.setUsername(rs.getString("username"));
use.setPassword(rs.getString("password"));
user=use;
}
// System.out.println("use..."+user.getPassword());
return user;
}
//保存数据
public static Integer saveDao(Connection conn, String id,String username,String password,String oldId) throws SQLException {
String sql = "update user set id=?, username = ?,password=? where id =?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,id);
ps.setString(2,username);
ps.setString(3,password);
//解决了id更改问题
ps.setInt(4,Integer.parseInt(oldId));
int result = ps.executeUpdate();
//System.out.println("result..."+id+"......"+Integer.parseInt(oldId));
return result;
}
}
Factory层(连接数据库资源)
factorydemo.java
package util;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
public class FactoryDemo {
public Connection getConnection() throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获得数据库连接
//此处填写自己的,数据库地址和密码
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT","root", "root");
return conn;
}
}
Servlet层
addServlet.java
package controllers;
import java.io.IOException;
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 biz.Biz;
/**
* Servlet implementation class addServlte
*/
@WebServlet("/addServlte")
public class addServlte extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public addServlte() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
// response.getWriter().append("Served at: ").append(request.getContextPath());
//设置字符集
request.setCharacterEncoding("UTF-8");
String id = request.getParameter("id");
String username = request.getParameter("user");
String password = request.getParameter("pwd");
Biz biz = new Biz();
try {
boolean flag = biz.InsertData(id,username, password);
if(flag) {
response.sendRedirect("findServlet");
}else {
response.sendRedirect("adduser.jsp");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @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);
}
}
delServlet.java
package controllers;
import java.io.IOException;
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 biz.Biz;
/**
* Servlet implementation class delServlet
*/
@WebServlet("/delServlet")
public class delServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public delServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
// response.getWriter().append("Served at: ").append(request.getContextPath());
String id = request.getParameter("id");
// System.out.println(id);
Biz bi = new Biz();
try {
boolean flag = bi.delUser(id);
if(flag) {
//跳转到这个Servlet去重新加载数据
response.sendRedirect("findServlet");
}else {
//如果false出错则跳转到这个jsp
response.sendRedirect("error.jsp");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @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);
}
}
findServlet.java
package controllers;
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 biz.Biz;
import model.User;
/**
* Servlet implementation class findServlet
*/
@WebServlet("/findServlet")
public class findServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public findServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
Biz biz = new Biz();
List<User> users = null;
try {
//遍历所有对象
users = biz.findAll();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//设置属性并进行页面跳转
request.setAttribute("users", users);
//页面跳转,重定向
request.getRequestDispatcher("show.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);
}
}
updateServlet.java
package controllers;
import java.io.IOException;
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 biz.Biz;
import model.User;
/**
* Servlet implementation class updateServlet
*/
@WebServlet("/updateServlet")
public class updateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public updateServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
// response.getWriter().append("Served at: ").append(request.getContextPath());
String id = request.getParameter("id");
System.out.println(id);
Biz biz = new Biz();
try {
User user = biz.selectSingleUser(id);
//测试用,测试是否能得到User对象,得到对象后我们要将对象传输到对应的jsp页面
// System.out.println(user.getPassword()+user.getUsername());
request.setAttribute("user", user);
//页面重定向
request.getRequestDispatcher("update.jsp").forward(request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @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);
}
}
saveServlet.java
package controllers;
import java.io.IOException;
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 biz.Biz;
/**
* Servlet implementation class saveServlet
*/
@WebServlet("/saveServlet")
public class saveServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public saveServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
// response.getWriter().append("Served at: ").append(request.getContextPath());
//此处得到改变后的id
String id = request.getParameter("id");
String username = request.getParameter("user");
String password = request.getParameter("pwd");
//接收表单中隐藏的id
String oldId = request.getParameter("oldId");
// System.out.println(id+"......zhe.....");
Biz biz = new Biz();
try {
boolean flag = biz.saveUser(id, username, password,oldId);
if(flag) {
//完成后页面重定向
response.sendRedirect("findServlet");
}else {
response.sendRedirect("error.jsp");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @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);
}
}
model,持久层
package model;
public class User {
private Integer id;
private String username;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
源码下载点击下载
sql 数据库 SQL