【JavaWeb】JDBC实现数据库增删改查功能

分手后的思念是犯贱 2023-02-27 12:18 1阅读 0赞

JDBC，简单点来说，就是用Java操作数据库，下面简单介绍怎么实现数据库的增删改查功能。

**1、添加数据**

package cn.itcast.jdbc;
    
    import java.sql.*;
    
    public class JdbcDemo2 { 
    
        public static void main(String[] args) { 
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
    
            try { 
                //1、注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2、定义sql
                String sql = "insert into course values(?,?,?)";
                //3、获取Connection对象
                //student表示你要操作的数据库
                //如果是locakhost：3306，也可以简写为"jdbc:mysql:///student"
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
                //4、获取执行sql的对象
                preparedStatement = connection.prepareStatement(sql);
                //传入参数
                preparedStatement.setInt(1,5);
                preparedStatement.setString(2,"JavaWeb");
                preparedStatement.setInt(3,88);
                //5、执行sql
                int count = preparedStatement.executeUpdate();
                //6、处理结果
                System.out.println(count);
                if (count > 0) { 
                    System.out.println("添加成功");
                } else { 
                    System.out.println("添加失败");
                }
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                //7、释放资源
                //避免空指针异常
                if (preparedStatement != null) { 
                    try { 
                        preparedStatement.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
                if (connection != null) { 
                    try { 
                        connection.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
            }
        }
    }

**2、删除数据**

package cn.itcast.jdbc;
    
    import java.sql.*;
    
    public class JdbcDemo4 { 
        public static void main(String[] args) { 
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            try { 
                //1、注册驱动
                Class.forName("com.mysql.jdbc.Driver");
    
                //2、获取连接对象
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
    
                //3、定义sql
                String sql = "delete from course where cno = ?";
    
                //4、获取执行sql对象
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,5);
    
                //5、执行sql
                int count = preparedStatement.executeUpdate();
    
                //6、处理结果
                System.out.println(count);
                if (count > 0) { 
                    System.out.println("删除成功");
                } else { 
                    System.out.println("删除失败");
                }
    
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                //7、释放资源
                if (preparedStatement != null) { 
                    try { 
                        preparedStatement.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
                if (connection != null) { 
                    try { 
                        connection.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
            }
    
        }
    }

**3、修改数据**

package cn.itcast.jdbc;
    
    import java.sql.*;
    
    public class JdbcDemo3 { 
        public static void main(String[] args) { 
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            try { 
                //1、注册驱动
                Class.forName("com.mysql.jdbc.Driver");
    
                //2、获取连接对象
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");
    
                //3、定义sql
                String sql = "update course set period = ? where cno = ?";
    
                //4、获取执行sql对象
                preparedStatement = connection.prepareStatement(sql);
                //设置参数
                preparedStatement.setInt(1,90);
                preparedStatement.setInt(2,1);
    
    
                //5、执行sql
                int count = preparedStatement.executeUpdate();
    
                //6、处理结果
                System.out.println(count);
                if (count > 0) { 
                    System.out.println("修改成功！");
                } else { 
                    System.out.println("修改失败！");
                }
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                //7、释放资源
                if (preparedStatement != null) { 
                    try { 
                        preparedStatement.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
                if (connection != null) { 
                    try { 
                        connection.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
            }
    
        }
    }

**4、查询数据**

package cn.itcast.jdbc;
    
    import cn.itcast.domain.Course;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    public class JDBCDemo5 { 
    
        /** * 查询所有Course对象 * @return */
        public static void main(String[] args) { 
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            List<Course> list = null;
            try { 
                //1、注册驱动
                Class.forName("com.mysql.jdbc.Driver");
                //2、获取连接
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");
    
                //3、定义sql
                String sql = "select * from course";
                //4、获取执行sql的对象
                preparedStatement = connection.prepareStatement(sql);
                //5、执行sql
                resultSet = preparedStatement.executeQuery();
                //6、遍历结果集，封装对象，装载集合
                Course course = null;
                list = new ArrayList<Course>();
                while (resultSet.next()) { 
                    //获取数据
                    int cno = resultSet.getInt("cno");
                    String cname = resultSet.getString("cname");
                    int period = resultSet.getInt("period");
                    //创建Course对象并赋值
                    course = new Course();
                    course.setCno(cno);
                    course.setCname(cname);
                    course.setPeriod(period);
                    //装载集合
                    list.add(course);
    
                }
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                if (resultSet != null) { 
                    try { 
                        resultSet.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
                if (preparedStatement != null) { 
                    try { 
                        preparedStatement.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
                if (connection != null) { 
                    try { 
                        connection.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
            }
            System.out.println(list);
        }
    
    }

我们可以发现，增删改的操作基本都是差不多的语句，且执行sql的语句都是一样的，都是preparedStatement.executeUpdate()。但查询操作就有所不同了，返回的是一个结果集，且执行sql的语句就是preparedStatement.executeQuery()。