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

本篇文章分享Java Web实验九：使用JDBC完成数据的增删改查，对你有帮助的话记得收藏一下，看Java资料网收获更多编程知识

实验九   使用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表

整体代码框架(IDEA和MySQL连接成功)

删除用户数据(id:202233366 name:赵六 email:zhaoliu@qq.com birthday:2003-6-21)

增加用户数据(id:202233366 name:赵六 email:zhaoliu@qq.com birthday:2003-6-21)

修改用户数据(id:202233366 name:赵六 email:zhaoliu@qq.com birthday:2003-6-21改为id:202233366 name:王五 email:wangwu@qq.com birthday:2004-6-21)

查询用户数据(查看所有)

查询用户数据(查看指定id用户数据，这里查询202233366)

文章来源:https://blog.csdn.net/jianguosongzi/article/details/137775169
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！