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

莫张周刘王 · 2024-11-14 15:13:13

实验九使用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<User> findAll(){
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
ArrayList<User> list=new ArrayList<User>();
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("lisi@qq.com");
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("zhaoliu@qq.com");
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<User> list=userDao.findAll();
for(int i=0;i<list.size();i++){
System.out.println("id:"+list.get(i).getId()+"\t"+"name:"+list.get(i).getUsername()+"\t\t"+"email:"+list.get(i).getEmail()+"\t\t"+"birthday:"+list.get(i).getBirthday());
}
}
}

复制代码

FindUserByIdTest.java:

public class FindUserByIdTest {
public static void main(String[] args) {
UserDao userDao=new UserDao();
User user= userDao.find(202233366);
System.out.println("id:"+user.getId()+"\tname:"+user.getUsername()+"\t\temail:"+user.getEmail()+"\t\tbirthday:"+user.getBirthday());
}
}

复制代码

运行截图

MySQL命令窗查看user表