增删改查的mysql项目_数据库项目案例之---登录注册和增删改查

最新推荐文章于 2022-10-27 20:48:42 发布

沐语薇

最新推荐文章于 2022-10-27 20:48:42 发布

阅读量283

点赞数

文章标签：增删改查的mysql项目

本文链接：https://blog.csdn.net/weixin_42469315/article/details/113908621

版权

登录注册和增删改查

(一)功能

1.使用了普通的jdbc和数据库连接池c3p0两种技术

2.使用了dao层(数据访问层)，service层(业务逻辑层)和servlet层(请求控制层)

3.实现了增删改查和登录注册技术

(二)页面展示

1.登录页面

2.注册页面

3.登录成功页面

4.登录失败页面

5.修改页面

6.修改成功页面

7.删除成功页面

(三)代码展示

(1)entity

1.User.java

packageentity;public classUser {private intid;privateString username;privateString password;public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}publicString getUsername() {returnusername;

}public voidsetUsername(String username) {this.username =username;

}publicString getPassword() {returnpassword;

}public voidsetPassword(String password) {this.password =password;

}

@OverridepublicString toString() {return "User{" +

"id=" + id +

", username='" + username + '\'' +

", password='" + password + '\'' +

'}';

}

(2)util

1.JDBCUtils.java 普通jdbc

packageutil;importcom.mchange.v2.c3p0.ComboPooledDataSource;importjava.io.IOException;importjava.io.InputStream;import java.sql.*;importjava.util.Properties;public classJDBCUtils {private static finalString driverClass;private static finalString url;private static finalString username;private static finalString password;static{

driverClass="com.mysql.jdbc.Driver";

url="jdbc:mysql://localhost:3306/zml";

username="root";

password= "root";

}/*** 注册驱动的方法

*@throwsClassNotFoundException*/

public static void loadDriver() throwsClassNotFoundException{

Class.forName(driverClass);

}/*** 获得连接的方法:

*@throwsSQLException*/

public static Connection getConnection() throwsException{

loadDriver();

Connection conn=DriverManager.getConnection(url, username, password);returnconn;

}/*** 资源释放*/

public static voidrelease(Statement stmt,Connection conn){if(stmt != null){try{

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt= null;

}if(conn != null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn= null;

}

}public static voidrelease(ResultSet rs,Statement stmt,Connection conn){if(rs!= null){try{

rs.close();

}catch(SQLException e) {

e.printStackTrace();

}

rs= null;

}if(stmt != null){try{

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt= null;

}if(conn != null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn= null;

}

2.JDBCUtils2.java 数据连接池c3p0

packageutil;importcom.mchange.v2.c3p0.ComboPooledDataSource;importjavax.sql.DataSource;importjava.beans.PropertyVetoException;importjava.sql.Connection;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;/*** 数据连接池c3p0*/

public classJDBCUtils2 {private static final ComboPooledDataSource dataSource = newComboPooledDataSource();static{try{

dataSource.setDriverClass("com.mysql.jdbc.Driver");

dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/zml");

dataSource.setUser("root");

dataSource.setPassword("root");

}catch(PropertyVetoException e) {

e.printStackTrace();

}

}/*** 获得连接的方法:

*@throwsSQLException*/

public static Connection getConnection() throwsException{

Connection conn=dataSource.getConnection();returnconn;

}/*** 资源释放*/

public static voidrelease(Statement stmt, Connection conn){if(stmt != null){try{

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt= null;

}if(conn != null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn= null;

}

}public static voidrelease(ResultSet rs, Statement stmt, Connection conn){if(rs!= null){try{

rs.close();

}catch(SQLException e) {

e.printStackTrace();

}

rs= null;

}if(stmt != null){try{

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt= null;

}if(conn != null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn= null;

}

(3)dao

1.LoginDao.java

packagedao;importutil.JDBCUtils;importutil.JDBCUtils2;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;public classLoginDao {public booleangetLogin(String username,String password){

Connection conn=null;

PreparedStatement pstmt=null;

ResultSet rs=null;boolean flag=false;try{//获得连接

conn=JDBCUtils2.getConnection();//编写sql

String sql="select password from user where username=? and password=?";//预编译sql

pstmt=conn.prepareCall(sql);

pstmt.setString(1,username);

pstmt.setString(2,password);//设置参数//执行sql

rs=pstmt.executeQuery();if(rs.next()){

flag=true;

}else{

flag=false;

}

}catch(Exception e){

}finally{

JDBCUtils2.release(rs,pstmt,conn);

}returnflag;

}

2.RegisterDao.java

packagedao;importentity.User;importutil.JDBCUtils;importutil.JDBCUtils2;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.util.ArrayList;importjava.util.List;public classRegisterDao {public intgetinsert(String username,String password){

Connection conn=null;

PreparedStatement pstmt=null;int num=0;try{//获得连接

conn=JDBCUtils2.getConnection();//编写sql

String sql="insert into user values(null,?,?)";//预编译sql

pstmt=conn.prepareCall(sql);

pstmt.setString(1,username);

pstmt.setString(2,password);//设置参数//执行sql

num=pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}finally{

JDBCUtils2.release(pstmt,conn);

}returnnum;

}

3.UserDao.java

packagedao;importcom.mchange.v2.c3p0.ComboPooledDataSource;importentity.User;importutil.JDBCUtils;importutil.JDBCUtils2;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;public classUserDao {public ListgetSelect(){

Connection conn=null;

PreparedStatement pstmt=null;

ResultSet rs=null;

List user=new ArrayList();

User a=null;try{//获得连接

conn=JDBCUtils2.getConnection();//编写sql

String sql="select * from user";//预编译sql

pstmt=conn.prepareCall(sql);//设置参数//执行sql

rs=pstmt.executeQuery();while(rs.next()){

a=newUser();

a.setId(rs.getInt(1));

a.setUsername(rs.getString(2));

a.setPassword(rs.getString(3));

user.add(a);

}

}catch(Exception e) {

e.printStackTrace();

}finally{

JDBCUtils.release(rs,pstmt,conn);

}returnuser;

}

4.UpdateDao.java

packagedao;importentity.User;importutil.JDBCUtils2;importjava.sql.Connection;importjava.sql.PreparedStatement;public classUpdateDao {public intgetUpdate(User user){

Connection conn=null;

PreparedStatement pstmt=null;int num=0;try{//获得连接

conn=JDBCUtils2.getConnection();//编写sql

String sql="update user set username=?,password=? where id=?";//预编译sql

pstmt=conn.prepareCall(sql);

pstmt.setString(1,user.getUsername());

pstmt.setString(2,user.getPassword());

pstmt.setInt(3,user.getId());//设置参数//执行sql

num=pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}finally{

JDBCUtils2.release(pstmt,conn);

}returnnum;

}

5.DeleteDao.java

packagedao;importutil.JDBCUtils2;importjava.sql.Connection;importjava.sql.PreparedStatement;public classDeleteDao {public int getDelete(intid){

Connection conn=null;

PreparedStatement pstmt=null;int num=0;try{//获得连接

conn=JDBCUtils2.getConnection();//编写sql

String sql="delete from user where id=?";//预编译sql

pstmt=conn.prepareCall(sql);

pstmt.setInt(1,id);//设置参数//执行sql

num=pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}finally{

JDBCUtils2.release(pstmt,conn);

}returnnum;

}

(4)service

1.UserService.java

packageservice;import dao.*;importentity.User;importjava.util.List;public classUserService {public ListgetServlet(){return newUserDao().getSelect();

}public booleangetLogin(String username,String password){return newLoginDao().getLogin(username,password);

}public intgetinsert(String username,String password){return newRegisterDao().getinsert(username,password);

}public intgetUpdate(User user){return newUpdateDao().getUpdate(user);

}public int getDelete(intid){return newDeleteDao().getDelete(id);

}

(5)servlet

1.LoginServlet.java

packageservlet;importservice.UserService;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;

@WebServlet("/LoginServlet")public class LoginServlet extendsHttpServlet {

@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

req.setCharacterEncoding("UTF-8");

String username=req.getParameter("username");

String password=req.getParameter("password");

UserService log=newUserService();boolean pwd=log.getLogin(username,password);

System.out.println(password+" "+pwd);if(pwd==true){

req.getRequestDispatcher("UserServlet").forward(req,resp);

}else{

req.getRequestDispatcher("error.jsp").forward(req,resp);

}

@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

doPost(req, resp);

}

2.RegisterServlet.java

@WebServlet("/RegisterServlet")public class RegisterServlet extendsHttpServlet {

@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

doPost(req, resp);

}

@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

req.setCharacterEncoding("UTF-8");

String username=req.getParameter("username");

String password=req.getParameter("password");

UserService u=newUserService();int num=u.getinsert(username,password);if(num>0){

req.getRequestDispatcher("index.jsp").forward(req,resp);

}else{

req.getRequestDispatcher("register.jsp").forward(req,resp);

}

3.UserServlet.java

packageservlet;importentity.User;importservice.UserService;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.util.List;

@WebServlet("/UserServlet")public class UserServlet extendsHttpServlet {

@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

doPost(req, resp);

}

@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

UserService u=newUserService();

List user=u.getServlet();

System.out.println("udrt"+user);for(User uu:user){

System.out.println(uu.getId()+" "+uu.getUsername()+" "+uu.getPassword());

}

req.setAttribute("User",user);

req.getRequestDispatcher("success.jsp").forward(req,resp);

}

4.UpdateServlet.java

packageservlet;importdao.UserDao;importentity.User;importservice.UserService;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;

@WebServlet("/UpdateServlet")public class UpdateServlet extendsHttpServlet {

@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

req.setCharacterEncoding("UTF-8");int id=Integer.decode(req.getParameter("id"));

String username=req.getParameter("username");

String password=req.getParameter("password");

User user=newUser();

user.setId(id);

user.setUsername(username);

user.setPassword(password);

UserService u=newUserService();int num=u.getUpdate(user);if(num>0){

req.getRequestDispatcher("LoginServlet").forward(req,resp);

}else{

req.getRequestDispatcher("update.jsp").forward(req,resp);

}

@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

doPost(req, resp);

}

5.DeleteServlet.java

@WebServlet("/DeleteServlet")public class DeleteServlet extendsHttpServlet {

@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {

doPost(req, resp);

}

@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {int id=Integer.decode(req.getParameter("id"));

UserService u=newUserService();int num=u.getDelete(id);

req.getRequestDispatcher("UserServlet").forward(req,resp);

}

(6)webapp

1.index.jsp

Title