web表格增删改_[22]-JDBC Statement对象增删改查

最新推荐文章于 2023-04-14 21:27:37 发布

郄小虎Tiger

最新推荐文章于 2023-04-14 21:27:37 发布

阅读量118

点赞数

文章标签： web表格增删改

本文链接：https://blog.csdn.net/weixin_30016593/article/details/113324238

版权

该博客介绍了JDBC在数据库操作中的应用，包括插入、删除、更新和查询数据的示例代码。强调了executeQuery()方法用于查询操作，而executeUpdate()用于增删改。在查询时，通过ResultSet获取结果，并提醒注意使用getString而非getInt以避免表格结构调整导致的错误。同时展示了原始的查询和增删改操作的简单实现。

摘要由CSDN通过智能技术生成

JDBC查询操作

知识点:

查询操作区别于增删改(DML)

区别于增删改的:

executeUpdate

使用

executeQuery()

注意: ()内是要跟SQL语句的

这里重点是要注意一下,查询的方式,其他的增删改基本一致

查询略微复杂

重写getInt方法中建议使用String参数的那一个,因为int类型,如果表格修改,容易出错

注意ResultSet中封装的并不是我们查询到的所有的结果集，而是返回了查询到的结果

集的数据库游标。通过ResultSet中的next(方法操作游标的位置获取结果集。(详细操作可以看后面的查询原始版那个是使用指针获取信息的)

package com.lin;


import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.Statement;


public class jdbcCRUD {

    //添加数据
    public void  insertJDBC(String department_name,int location_id){
        Connection connection =null;
        Statement statement = null;

        try {
            connection = jdbcUtil.getConnection();
            String sql = "insert into departments values(default,'" + department_name + "','" + location_id + "')";
            statement = connection.createStatement();
            int flag = statement.executeUpdate(sql);
            if (flag==1){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(statement,connection,null);
        }

    }

    //删除数据
    public void deleteJDBCById(int department_id){
        Connection connection = null;
        Statement statement = null;
        try {
            connection = jdbcUtil.getConnection();
            String sql = "delete from departments where department_id="+department_id+"";
            statement =connection.createStatement();
            int flag = statement.executeUpdate(sql);
            if (flag==1){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(statement, connection,null);
        }
    }
    //修改数据
    public void updateJDBCByid(int department_id,String department_name){
        Connection connection = null;
        Statement statement = null;

        try {
            connection = jdbcUtil.getConnection();
            String sql ="update departments set department_name = '"+department_name+"' where department_id="+department_id;
            statement = connection.createStatement();
            int flag = statement.executeUpdate(sql);
            if (flag==1){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
    }


    //查询数据
    public void  selectJDBCById(int department_id){
        Connection connection=null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = jdbcUtil.getConnection();
            String sql = "select * from departments where department_id ="+department_id;
            statement = connection.createStatement();
            //执行查询返回结果
             resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                int department_id1 = resultSet.getInt("department_id");
                String department_name = resultSet.getString("department_name");
                int anInt = resultSet.getInt(3);//不建议使用
                System.out.println("部门编号:"+department_id1+"  部门名称:"+department_name+"  位置编号:"+anInt);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeResource(statement,connection,resultSet);
        }
    }


    public static void main(String[] args) {
        jdbcCRUD jdbcCRUD = new jdbcCRUD();
        // jdbcCRUD.insertJDBC("事业部",8);
        // jdbcCRUD.deleteJDBCById(16);
         jdbcCRUD.selectJDBCById(1);
       // jdbcCRUD.updateJDBCByid(1,"人事部");

    }
}

增删改原始版

package com.lin.liang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUPDATE {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement =null;
        //声明变量
        try {
//      加载驱动
            Class.forName("com.mysql.jdbc.Driver");
//      连接数据库
            String  rul = "jdbc:mysql://127.0.0.1:3306/slayer";
            String  user = "root";
            String  pwd  = "root";
            connection = DriverManager.getConnection(rul,user,pwd);
//      创建Statement
             statement = connection.createStatement();
//      通过statement 发送SQL语句 得到结果
//            添加
            String sql ="insert into test1 values('测试5',123)";
//            删除
            String sql2 ="delete from test1 where name='测试6'";
//            修改
            String sql3 ="update test1 set pwd = 111 where name='测试1'";

            int n1 = statement.executeUpdate(sql);
            int n2 = statement.executeUpdate(sql2);
            int n3 = statement.executeUpdate(sql3);
//      处理结果
            if(n1>0&&n2>0&&n3>0){
                System.out.println("操作成功");
            }else {
                System.out.println("操作失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (statement !=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

查询原始版略微复杂

package com.lin.liang;

import java.sql.*;

public class JDBCExamine {
    public static void main(String[] args) {
//   申明
        Connection connection =null;
        Statement statement = null;
        ResultSet resultSet =null;
        try {
//   加载驱动
            Class.forName("com.mysql.jdbc.Driver");
//   建立连接
            String url ="jdbc:mysql://127.0.0.1:3306/slayer";
            String user="root";
            String pwd ="root";
            connection = DriverManager.getConnection(url,user,pwd);
//   创建Statement发送器
           statement =connection.createStatement();
//   创建SQL语句使用Statement 发送处理结果
            String sql ="select * from emp";
//   返回结果集  resultSet 结果集
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                //next 指针位置,初始位置在表格开头每次运行下移一位
//  1,2,3,4,5,6,7,8 代表列 的顺序
                int empno = resultSet.getInt(1);
                String ename = resultSet.getString(2);
                String  job = resultSet.getString(3);
                int mgr = resultSet.getInt(4);
                Date hiredate = resultSet.getDate("hiredate");
                double sal = resultSet.getDouble(6);
                double comm = resultSet.getDouble(7);
                int deptno = resultSet.getInt(8);
                System.out.println(empno+"--"+ename+"--"+job+"--"+mgr+"--"+hiredate+"--"+sal+"--"+comm+"--"+deptno);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement !=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection !=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}