JDBC巩固复习

一、 JDBC的基本操作

1.JDBC的概述

• 什么是JDBC
  1. JDBC（java Data Base Connectivity，java连接数据）
  2. 是一种用于执行SQL语句的java API，为多种关系数据库提供统一的访问
  3. 它是由一组java语言编写的类和接口

• JDBC访问数据库步骤
  • 1：加载一个Driver驱动
  • 2：创建数据库连接（Connection）
  • 3： 创建SQL命令发送器Statement
  • 4：通过Statement发送SQL命令并得到结果
  • 5：处理结果（Select语句）
  • 6：关闭数据库资源
    - ResultSet
    - Statement
    - Connection

package com.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        String driver = "com.mysql.jdbc.Driver";
        Class.forName(driver);
        //2.建立连接
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);
      //  System.out.println(connection);
        //3.创建SQL命令发送器
        Statement statement = connection.createStatement();
        //4.向数据库发送SQL命令发送器
        String sql = "insert into book values(6,'百年孤独','文艺类','99.00')";
        int n = statement.executeUpdate(sql);//insert into 、 delete DML都使用其语句
        //5.处理结果
        if (n>0){
            System.out.println("添加成功！");
        } else{
            System.out.println("添加失败！");
        }
        //6.关闭资源
        statement.close();
        connection.close();
    }
}

二、JDBC基本操作

package com.demo1;

import com.entity.Book;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TestSelect {
    /*
    前台页面获取数据
    * */
    public static void main(String[] args) {
        List<Book> all = findAll();
        //显示查询结果
        System.out.println("编号\t书名\t\t分类\t\t价格");
        for (Book book:all) {
            System.out.println(book.getId()+"\t"+book.getbName()+"\t"+book.getBookStyle()+"\t"+ book.getPrice());
        }
    }

    public static List<Book> findAll(){
        Connection connection = null;
        Statement statement =null;
        ResultSet rs = null;
        List<Book> books = new ArrayList<>();
        try {
            String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver);
            String url = "jdbc:mysql://127.0.0.1:3306/myemployees?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);
            statement = connection.createStatement();
            String sql = "select * from book";
            rs = statement.executeQuery(sql);
            while (rs.next()){
                //获取当前行的各个列的数据
                int id = rs.getInt("Id");
                String bName = rs.getString("bName");
                String bookStyle = rs.getString("bookStyle");
                double price = rs.getDouble("Price");
                //将当前的行的数据封装Book对象中
                Book book = new Book(id,bName,bookStyle,price);
                //将对象添加到list集合中
                books.add(book);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally{
            //关闭资源
            try {
               if (rs != null){
                   rs.close();
               }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (statement != null){
                    statement.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
               if (connection != null){
                   connection.close();
               }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return books;
    }
}

注意：
Statement和PreparedStatment的关系和区别
关系： public interface PreparedStatement extends Statement
区别

• PreparedStatment安全性高，可以避免SQL注入
• PreparedStatment简单不繁琐，不用进行字符串拼接
• PreparedStatment性能高，用在执行多个相同数据库DML操作时

本文地址：https://blog.csdn.net/qq_41911599/article/details/110792691