JDBC操作数据库——查询与更新

JDBC连接步骤

导入JDBC包：将Java语言的*import*语句添加到Java代码中导入所需的类。

- 注册JDBC驱动程序：此步骤将使JVM将所需的驱动程序实现加载到内存中，以便它可以满足您的JDBC 请求。

- 数据库URL配置：这是为了创建一个格式正确的地址，指向要连接到的数据库。

- 创建连接对象：最后，调用DriverManager对象的getConnection（）方法来建立实际的数据库连 接。

状态通道查询示例：

package com;

import java.sql.*;

public class Demo1 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql，创建状态通道(进行sql语句的发送)
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from student");//executeQuery(sql) 执行查询 并将结果赋值给resultSet
            //4.取出结果集信息
            while (resultSet.next()){  //判断是否有下一条数据
                //取出数据: resultSet.getXXX("列名");     xxx表示数据类型
                System.out.println("姓名："+resultSet.getString("sname")
                        +",class:"+resultSet.getString("class")+",出生日期："+resultSet.getDate("sbirthday"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                //5.关闭资源
                if (resultSet != null){
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

状态通道更新数据库操作

package com;

import java.sql.*;

public class Demo2 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql，创建状态通道(进行sql语句的发送)
            statement = connection.createStatement();
            //返回结果为受影响的行数
            int result = statement.executeUpdate("delete from student where sno='123'");//executeUpdate(sql) 执行增删改时使用
            if (result>0){
                System.out.println("执行成功！");
            }else {
                System.out.println("执行失败！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                //5.关闭资源

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

    }
}

预状态通道进行查询：

package com;

import java.sql.*;

public class Demo4 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pps =  null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql，创建预状态通道(进行sql语句的发送)
            String sql = "select * from student where sno=? and class=?";
            pps = connection.prepareStatement(sql);
            String sno2 = "101";
            String class2 ="95033";
            //给占位符赋值    (下标，内容)
            pps.setString(1,sno2);
            pps.setString(2,class2);
            //执行sql
            resultSet = pps.executeQuery();//executeQuery(sql) 执行查询
            if (resultSet.next()){
                System.out.println("successed!");
                }else {
                System.out.println("failed!");
            }
           /* //4.取出结果集信息
            while (resultSet.next()){  //判断是否有下一条数据
                //取出数据: resultSet.getXXX("列名");     xxx表示数据类型
                System.out.println("姓名："+resultSet.getString("sname")
                        +",class:"+resultSet.getString("class")+",出生日期："+resultSet.getDate("sbirthday"));
            }*/
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                //5.关闭资源
                if (resultSet != null){
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

预状态通道批处理更新数据库：

package com;

import java.sql.*;

public class PreparedStatementBatch {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pps = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            connection.setAutoCommit(false);//事务设置成手动提交
            //3.定义sql，创建状态通道(进行sql语句的发送)
            pps = connection.prepareStatement("insert into teacher(tname) values(?)");
            //4.赋值操作
            pps.setString(1,"李四A");
            pps.addBatch();

            pps.setString(1,"李四B");
            pps.addBatch();

            pps.setString(1,"李四C");
            pps.addBatch();

            pps.setString(1,"李四D");
            pps.addBatch();

            int[] ints = pps.executeBatch();//ints是受影响的行数

            connection.commit();
            for (int anInt : ints) {
                System.out.println("anInt="+anInt);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                //5.关闭资源

                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}