WEB基础--JDBC操作数据库

使用JDBC操作数据库

使用JDBC查询数据

五部曲：建立驱动，建立连接，获取SQL语句，执行SQL语句，释放资源

建立驱动

 //1.加载驱动
 Class.forName("com.mysql.cj.jdbc.Driver");

建立连接

//2.连接数据库
String url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
String username="root";
String password="123456";
connection=DriverManager.getConnection(url,username,password);

获取SQL语句

 //3。获取SQL
 statement=connection.createStatement();
 String sql="Select * From product ";

执行SQL语句

这里用了个ResultSet来接收查询到的数据，见名知意，ResultSet--结果集

 //4.执行SQL
            ResultSet resultSet=statement.executeQuery(sql);
            while (resultSet.next()){
                Long ID=resultSet.getLong("id");
                String productName=resultSet.getString("productName");
                Double salePrice=resultSet.getDouble("salePrice");
                System.out.println(ID);
                System.out.println(productName);
                System.out.println(salePrice);
            }

释放资源

statement.close();
connection.close();

运行结果（因结果篇幅过大，故此处只展示部分结构）：

完整参考代码：

public class JDBC2 {
    public static void main(String[] args) throws  Exception {
        Connection connection=null;//初始化
        Statement statement=null;//初始化
        try{
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.连接数据库
            String url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
            String username="root";
            String password="123456";
            connection=DriverManager.getConnection(url,username,password);
            //3。获取SQL
            statement=connection.createStatement();
            String sql="Select * From product ";
            //4.执行SQL
            ResultSet resultSet=statement.executeQuery(sql);
            while (resultSet.next()){
                Long ID=resultSet.getLong("id");
                String productName=resultSet.getString("productName");
                Double salePrice=resultSet.getDouble("salePrice");
                System.out.println(ID);
                System.out.println(productName);
                System.out.println(salePrice);
            }
            //5.释放资源
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            try {
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

使用JDBC修改数据

五步曲再来一遍

建立驱动

 //1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");

建立连接

//2.建立连接
 String url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
String username="root";
String password="123456";
connection= DriverManager.getConnection(url,username,password);

获取SQL语句

 //3.获取SQL
            String sql="UPDATE `test`.`product` SET `productName` = '罗技M90', `dir_id` = 3, `salePrice` = 90.00, `supplier` = '罗技', `brand` = '罗技', `cutoff` = 0.50, `costPrice` = 35.00 WHERE `id` = 22;\n";
            statement=connection.createStatement();

执行SQL语句

  //4.执行SQL
   statement.executeUpdate(sql);
   System.out.println("更新数据成功");

释放资源

​//释放资源
statement.close();
connection.close();

​

运行结果:

完整代码参考

package com.WEB.JDBC;

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

public class JDBC4 {
    public static void main(String[] args) throws Exception {
        Connection connection=null;
        Statement statement=null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.连接数据库
            String url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
            String username="root";
            String password="020315";
            connection= DriverManager.getConnection(url,username,password);
            //3.获取SQL
            String sql="UPDATE `test`.`product` SET `productName` = '罗技M90', `dir_id` = 3, `salePrice` = 90.00, `supplier` = '罗技', `brand` = '罗技', `cutoff` = 0.50, `costPrice` = 35.00 WHERE `id` = 22;\n";
            statement=connection.createStatement();
            //4.执行SQL
            statement.executeUpdate(sql);
            System.out.println("更新数据成功");
            //5.释放资源
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            try {
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

使用JDBC插入数据

五步曲再再来一遍

建立驱动

 //1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");

建立连接

//2.建立连接
 String url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
String username="root";
String password="123456";
connection= DriverManager.getConnection(url,username,password);

获取SQL语句

 //3.获取SQL
  String sql="INSERT INTO `test`.`product`(`id`, `productName`, `dir_id`, `salePrice`, `supplier`, `brand`, `cutoff`, `costPrice`) VALUES (null, 'ROG极恶之眼', 3, 1190.00, 'ROG', 'ROG', 0.50, 1135.00);\n";
 statement=connection.createStatement();

执行SQL语句

//4.执行SQL
   statement.executeUpdate(sql);
   System.out.println("插入数据成功");

释放资源

​//释放资源
statement.close();
connection.close();

​

运行结果:

完整代码参考：

public class JDBC3 {
    public static void main(String[] args) throws  Exception {
        try {
            Connection connection=null;
            Statement statement=null;
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.建立连接
            String url="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
            String username="root";
            String password="123456";
            connection= DriverManager.getConnection(url,username,password);
            //3.获取SQL
            String sql="INSERT INTO `test`.`product`(`id`, `productName`, `dir_id`, `salePrice`, `supplier`, `brand`, `cutoff`, `costPrice`) VALUES (null, 'ROG极恶之眼', 3, 1190.00, 'ROG', 'ROG', 0.50, 1135.00);\n";
            statement=connection.createStatement();
            //4.执行SQL
            statement.executeUpdate(sql);
            System.out.println("插入数据成功");
            //5.释放资源
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            try {
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

写在最后：JDBC是JAVA操作数据的规范，我的面试官曾经和我说过一句话："要是能够把JDBC那些语句默写出来，那么Mybatis肯定是没有问题。"JDBC是规范，也是Mybatis基于封装的东西，基础语句要多加练习，这样才能熟能生巧。笔者小，中，大厂均有面试经历，每日分享JAVA全栈知识，希望与大家共同进步。