JDBC学习笔记_jddb-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_35193677/article/details/122298643

本文详细介绍了如何使用Java JDBC进行数据库操作，包括MySQL、Oracle和SQLServer的JDBC驱动和连接字符串。示例代码展示了如何执行SQL查询、插入、删除、更新操作，并涉及事务管理，确保数据一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.简介

JDBC全称为Java数据库连接(Java Database Connectivity),是一套用于执行SQL语句的Java API.

2.下载jar(https://dev.mysql.com/downloads/)

3.简单查询

import java.sql.*;

public class JDBCSample {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/imooc?useUnicode=true&characterEncoding=UTF-8";
        Connection connection = null;
        try {
            //1.加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.创建数据库连接
            connection = DriverManager.getConnection(url, "root", "123456");
            //3.创建Statement对象
            Statement statement = connection.createStatement();
            String sql = "select * from t_message where job=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"Android");
            System.out.println(preparedStatement);
            ResultSet resultSet = preparedStatement.executeQuery();
            //4.遍历查询结果
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String content = resultSet.getString("content");
                String job = resultSet.getString("job");
                System.out.println("id:" + id + "     content:" + content + "    job:" + job);

            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭连接,释放资源
            try {
                if (connection != null && !connection.isClosed())
                    connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

4.数据库与连接字符串

数据库	JSBC驱动类	连接字符串
MySQL5	com.mysql.jdbc.Driver	jabc:mysql://主机ip:端口/数据库名
MySQL8	com.mysql.cj.jdbc.Driver	jabc:mysql://主机ip:端口/数据库名
Oracle	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@主机ip:端口:数据库名
SQL Server	com.mircosoft.sqlserver.jdbc.SQLServerDriver	jabc:mircosoft:sqlserver:主机ip:端口号;databasename=数据库名

5.MySQL连接字符串常用参数

参数名	建议参数	说明
useSSL	true(生产) false(开发)	是否禁用ssl
useUnicode	true	是否禁用ssl 启用Unicode编码传输数据
serverTimezone	Asia/Shanghai	使用东8时区时间 UTC+8
allowPublicKeyRetrieval	true	允许从客户端获取公钥加密传输

6.Insert、Delete、Update、Query

6.1 Insert

package com.mo.jdbc.hrapp.command;

import com.mo.jdbc.common.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class InsertCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入员工编号:");
        int id = in.nextInt();

        System.out.println("请输入员工姓名:");
        String name = in.next();

        System.out.println("请输入员工薪资:");
        float salary = in.nextFloat();
        System.out.println("请输入员工岗位:");
        String job = in.next();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtils.getConnection();
            String sql = "insert into t_message (id,content,create_time ,salary,job) values(?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.setString(2,name);
            pstmt.setString(3,"2022-1-4");
            pstmt.setFloat(4,salary);
            pstmt.setString(5,job);

            int count = pstmt.executeUpdate();
            System.out.println("count:"+count);
            System.out.println(name+"员工已办理入职手续");
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.closeConnection(null,pstmt,conn);
        }
    }
}

6.2 Delete

package com.mo.jdbc.hrapp.command;

import com.mo.jdbc.common.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 员工离职
 */
public class DeleteCommand implements Command {
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入员工编号");
        int id = in.nextInt();

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtils.getConnection();
            String sql = "delete from t_message where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            System.out.println(pstmt);

            int cnt = pstmt.executeUpdate();
            if (cnt ==1){
                System.out.println("员工离职手续已办理成功");
            }else {
                System.out.println("未找到"+id+"编员工数据");
            }
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.closeConnection(null,pstmt,conn);
        }
    }
}

6.3 Update

package com.mo.jdbc.hrapp.command;

import com.mo.jdbc.common.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 员工调薪
 */
public class UpdateCommand implements Command {
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入员工编号");
        int id = in.nextInt();

        System.out.println("请输入员工薪资");
        float salary = in.nextFloat();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtils.getConnection();
            String sql = "update t_message set salary=? where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1,salary);
            pstmt.setInt(2,id);
            int cnt = pstmt.executeUpdate();
            if (cnt ==1){
                System.out.println("员工薪资调整完毕");
            }else {
                System.out.println("未找到"+id+"编员工数据");
            }
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.closeConnection(null,pstmt,conn);
        }
    }
}

6.4 Query

package com.mo.jdbc.hrapp.command;

import java.sql.*;
import java.util.Scanner;

public class QueryCommand implements Command {
    @Override
    public void execute() {
        String url = "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8" +
                "&serverTimezone=Asia/Shanghai&PublicKeyRetrieval=true";
        System.out.println("输入岗位类型");
        Scanner scanner = new Scanner(System.in);
        String job = scanner.next();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(url, "root", "123456");
            statement = connection.createStatement();
            String sql = "select * from t_message where job='" + job + "' ";
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String job1 = resultSet.getString("job");
                String salary = resultSet.getString("salary");
                String content = resultSet.getString("content");
                System.out.println("id:" + id + "--名字:" + content + "--job:" + job1 + "--salary:" + salary);

            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {

                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null && !connection.isClosed())
                    connection.close();

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

package com.mo.jdbc.common;

import java.sql.*;

public class DBUtils {
    /**
     * 创建洗的数据库连接
     * @return 新的Connections对象
     * @throws SQLException .
     * @throws ClassNotFoundException .
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        String url = "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8" +
                "&serverTimezone=Asia/Shanghai&PublicKeyRetrieval=true";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        return conn;
    }

    /**
     * 关闭连接 释放资源
     * @param rs .
     * @param stmt .
     * @param conn .
     */
    public static void closeConnection(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        try {
            if (conn != null && !conn.isClosed())
                conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

7.JDBC事物管理

事物是一种可靠的，一致的方式，访问和操作数据库的程序单元
事务依赖数据库实现,MySQL通过事务区作为数据缓冲地带

7.1自动提交事务(默认)

每执行一次写操作SQL自动提交事务
自动提交开启方法 conn:setAutoCommit(true)
JDBC默认自动提交,此模式无法保证数据一致性

7.2手动提交事务

显示调用commit()与rollback()管理事务
手动提交开启方法 conn.setAutoCommit(false)
手动提交事务可保证多数据一致性,但必须手动调用commit()/rollback()

import com.mo.jdbc.common.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Random;

public class TransactionSample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtils.getConnection();
            //设置手动提交
            conn.setAutoCommit(false);
            String sql = "insert into t_message(id,content,create_time ,salary,job) values(?,?,?,?,?)";
            for (int i = 8; i < 50; i++) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工" + i);
                pstmt.setString(3, "2022-1-5");
                pstmt.setString(4, String.valueOf(1000 + new Random().nextInt(100)));
                pstmt.setString(5, "Android");
                pstmt.executeUpdate();
            }
            //提交
            conn.commit();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
            try {
                //有异常回滚数据 保证多数据一致性
                if (conn != null && !conn.isClosed())
                    conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBUtils.closeConnection(null, pstmt, conn);
        }
    }
}