Java Web实验九：使用JDBC完成数据的增删改查

2024-06-26 12:51| 来源: 网络整理| 查看: 265

实验九使用JDBC完成数据的增删改查

一、实验目的

1. 熟练JDBC的基本概念和原理；

2. 掌握使用JDBC进行数据库连接和操作的方法；

3. 熟悉JDBC在增删改查中的应用；

二、实验内容

在实际项目的开发中，用户信息是存放在数据库中的，管理员对用户信息进行管理的过程，无时无刻不涉及到增删改查操作。本次实验要求创建数据库表user表，包括属性id（主键）、name、password、email、birthday等字段，其中id为学号，创建数据库表后使用JDBC实现对数据库中用户信息的增加（JdbcInsertTest.java）、删除（DeleteUserTest.java）、修改（UpdateUserTest.java）和查询（FindAllUserTest.java和FindUserByIdTest.java）操作，要求数据库表中有一行信息为学生本人的学号姓名。

三、实验结果（源代码、运行截图）

源代码

User.java:

import java.util.Date; public class User { private int id; private String name; private String password; private String email; private Date birthday; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return name; } public void setUsername(String username) { this.name = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } }

JDBCUtil.java:

import java.sql.*; public class JDBCUtil { public static Connection getConnection() throws SQLException, ClassNotFoundException{ Class.forName("com.mysql.cj.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/javatest9?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8"; String username="root"; String password="123456"; Connection conn= DriverManager.getConnection(url,username,password); return conn; } public static void release(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 void release(ResultSet rs,Statement stmt,Connection conn){ if(rs!=null){ try { rs.close(); }catch (SQLException e){ e.printStackTrace(); } rs=null; } release(stmt,conn); } }

UserDao.java:

import java.sql.Array; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; public class UserDao { //添加用户 public boolean insert(User user){ Connection conn=null; Statement stmt=null; ResultSet rs=null; try { conn=JDBCUtil.getConnection(); stmt=conn.createStatement(); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); String birthday=sdf.format(user.getBirthday()); String sql="INSERT INTO user(id,name,password,email,birthday)"+ "VALUES(" +user.getId() +",'" +user.getUsername() +"','" +user.getPassword() +"','" +user.getEmail() +"','" + birthday+"')"; int num=stmt.executeUpdate(sql); if(num>0){ return true; } return false; }catch (Exception e){ e.printStackTrace(); }finally { JDBCUtil.release(rs,stmt,conn); } return false; } //查询操作 public ArrayList findAll(){ Connection conn=null; Statement stmt=null; ResultSet rs=null; ArrayList list=new ArrayList(); try { conn=JDBCUtil.getConnection(); stmt=conn.createStatement(); String sql="SELECT * FROM user"; rs=stmt.executeQuery(sql); while(rs.next()){ User user=new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setBirthday(rs.getDate("birthday")); list.add(user); } return list; }catch (Exception e){ e.printStackTrace(); }finally { JDBCUtil.release(rs,stmt,conn); } return null; } //查找指定id操作 public User find(int id){ Connection conn=null; Statement stmt=null; ResultSet rs=null; try { conn=JDBCUtil.getConnection(); stmt=conn.createStatement(); String sql="SELECT * FROM user WHERE id="+id; rs=stmt.executeQuery(sql); while(rs.next()){ User user=new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setBirthday(rs.getDate("birthday")); return user; } return null; }catch (Exception e){ e.printStackTrace(); }finally { JDBCUtil.release(rs,stmt,conn); } return null; } //删除操作 public boolean delete(int id){ Connection conn=null; Statement stmt=null; ResultSet rs=null; try{ conn=JDBCUtil.getConnection(); stmt=conn.createStatement(); String sql="DELETE FROM user WHERE id="+id; int num=stmt.executeUpdate(sql); if(num>0){ return true; } return false; }catch (Exception e){ e.printStackTrace(); }finally { JDBCUtil.release(rs,stmt,conn); } return false; } //修改用户 public boolean update(User user){ Connection conn=null; Statement stmt=null; ResultSet rs=null; try { conn=JDBCUtil.getConnection(); stmt=conn.createStatement(); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); String birthday=sdf.format(user.getBirthday()); String sql="UPDATE user set name='"+user.getUsername() +"',password='"+user.getPassword()+"',email='" +user.getEmail()+"',birthday='"+birthday +"'WHERE id="+user.getId(); int num=stmt.executeUpdate(sql); if(num>0){ return true; } return false; }catch (Exception e){ e.printStackTrace(); }finally { JDBCUtil.release(rs,stmt,conn); } return false; } }

JdbcInsertTest.java:

import java.util.Calendar; import java.util.Date; public class JdbcInsertTest { public static void main(String[] args) { UserDao ud=new UserDao(); User user=new User(); user.setId(202266623); user.setUsername("李四"); user.setPassword("123"); user.setEmail("[email protected]"); Calendar calendar= Calendar.getInstance(); calendar.set(2003, Calendar.FEBRUARY, 21); //年月日也可以具体到时分秒如calendar.set(2015, 10, 12,11,32,52); Date date=calendar.getTime();//date就是你需要的时间 user.setBirthday(date); boolean b=ud.insert(user); System.out.println(b); } }

DeleteUserTest.java:

public class DeleteUserTest { public static void main(String[] args) { UserDao userDao=new UserDao(); boolean b=userDao.delete(202233366); System.out.println(b); } }

UpdateUserTest.java:

import java.util.Calendar; import java.util.Date; public class UpdateUserTest { public static void main(String[] args) { UserDao userDao=new UserDao(); User user=new User(); user.setId(202233366); user.setUsername("赵六"); user.setPassword("12345"); user.setEmail("[email protected]"); Calendar calendar= Calendar.getInstance(); calendar.set(2003, Calendar.JUNE, 21); //年月日也可以具体到时分秒如calendar.set(2015, 10, 12,11,32,52); Date date=calendar.getTime();//date就是你需要的时间 user.setBirthday(date); boolean b=userDao.update(user); System.out.println(b); } }

FindAllUserTest.java:

import java.util.ArrayList; public class FindAllUserTest { public static void main(String[] args) { UserDao userDao=new UserDao(); ArrayList list=userDao.findAll(); for(int i=0;i

【本文地址】

公司简介

联系我们