侧边栏壁纸
博主头像
Epoch

Java开发、Python爬虫、微服务、分布式、前端

  • 累计撰写 93 篇文章
  • 累计创建 110 个标签
  • 累计收到 8 条评论
标签搜索

目 录CONTENT

文章目录
Java 数据库

Java中的JDBC

Epoch
Epoch
2021-02-04 / 0 评论 / 0 点赞 / 319 阅读 / 1,864 字 / 正在检测是否收录...

JDBC开发流程

1.加载并注册JDBC驱动

2.创建数据库连接

3.创建Statement对象

4.遍历查询结果

5.关闭连接，释放资源

如下操作:

package com.xmaven;

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

/**
 * @ClassName Sample
 * @Description TODO
 * @Author Ambition
 * @Date 2021/2/3 21:05
 * @Version 1.0.0
 **/
public class Sample {

    /**
     * 标准JDBC数据库五个操作步骤
     * 1.加载并注册JDBC驱动
     * 2.创建数据库连接
     * 3.创建Statement对象
     * 4.遍历查询结果
     * 5.关闭连接，释放资源
     */
    public static void main(String[] args) {
        Connection conn = null;
        try {
            //1.加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.创建数据库连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bystart?useSSL=false&useUnicode=true&serverTimezone=Asia/Shanghai",
                    "root",
                    "root"
            );
            //3.创建Statement对象
            Statement statement = conn.createStatement();
            ResultSet result = statement.executeQuery("select * from user");
            //4.遍历查询结果
            while (result.next()) {
                Integer id = result.getInt(1);
                String username = result.getString("username");
                String password = result.getString("password");
                Float salary = result.getFloat("salary");
                String dname = result.getString("dname");
                System.out.println(id + "-" + username + "-" + password + "-" + salary + "-" + dname);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    //5.关闭连接，释放资源
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


}

MySQL连接字符串

1、格式:jdbc:mysql://[主机ip][:端口]/数据库名?参数列表
2、主机ip与端口是可选设置，默认值为127.0.0.1与3306
3、参数列表采用url编码，格式:参数1=值1&参数2=值2&...
参数名 建议参数值 说明
useSSL true(生产)/false(开发) 是否禁用ssl
useUnicode true 启用Unicode编码传输数据
characterEncoding UTF-8 使用UTF-8编码传输数据
serverTimezone Asia/Shanghai 使用东8时区时间，UTC+8
allowPublicKeyRetrieval true 允许冲客户端获取公钥加密传输

完整写法

package com.xmaven;

import java.sql.*;

/**
 * @ClassName ConnectionSample
 * @Description TODO
 * @Author Ambition
 * @Date 2021/2/4 17:12
 * @Version 1.0.0
 **/
public class ConnectionSample {

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/bystart?useSSL=false&useUnicode=true" +
                    "&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            String user = "root";
            String pwd = "root";
            //2.创建数据库连接
            conn = DriverManager.getConnection(url, user, pwd);
            //3.创建Statement对象
            stmt = conn.createStatement();
            //结果集
            rs = stmt.executeQuery("select * from user");
            //4.遍历查询结果
            /**
             * rs.next()返回boolean值，代表是否存在下一条数据
             * 如果有，返回true，同时结果集提取下一条数据
             * 如果没有，返回false，循环停止
             */
            while (rs.next()) {
                Integer id = rs.getInt(1);
                String username = rs.getString("username");
                String password = rs.getString("password");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(id + "-" + username + "-" + password + "-" + salary + "-" + dname);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭连接，释放资源
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (conn != null || !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

预防SQL攻击

1、SQL注入攻击是指利用SQL漏洞越权获取数据的黑客行为
2、SQL注入攻击更远是未对原始SQL中的敏感字符做特殊处理
3、解决方法:放弃Statement改用PrepareStatement处理SQL

上面这种方式，如果使用SQL凭借的话，凭借单引号，可以注入SQL攻击

这里不讲解如何SQL攻击，讲解如何预防SQL攻击

PrepareStatement

PrepareStatement:预编译Statement是Statement的子接口
PrepareStatement对SQL进行参数化，预防SQL注入攻击
PrepareStatement比Statement执行效率更高

原始代码

image-20210204184152193.png

使用PrepareStatement

image-20210204184231487.png

package com.xmaven;

import java.sql.*;

/**
 * @ClassName ConnectionSample
 * @Description TODO
 * @Author Ambition
 * @Date 2021/2/4 17:12
 * @Version 1.0.0
 **/
public class PstmtConnectionSample {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //1.加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/bystart?useSSL=false&useUnicode=true" +
                    "&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            String user = "root";
            String pwd = "root";
            //2.创建数据库连接
            conn = DriverManager.getConnection(url, user, pwd);
            //3.创建Statement对象
            String sql = "select * from user where dname = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"主管部门"); // 注意参数索引从1开始
            //结果集
            rs = pstmt.executeQuery();
            //4.遍历查询结果
            /**
             * rs.next()返回boolean值，
             * 如果有，返回true，同时结果集提取下一条数据
             * 如果没有，返回false，循环停止
             */
            while (rs.next()) {
                Integer id = rs.getInt(1);
                String username = rs.getString("username");
                String password = rs.getString("password");
                Float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                System.out.println(id + "-" + username + "-" + password + "-" + salary + "-" + dname);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭连接，释放资源
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (conn != null || !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

封装DbUtils工具

package com.xmaven.utils;

import java.sql.*;

/**
 * @ClassName DbUtils
 * @Description TODO
 * @Author Ambition
 * @Date 2021/2/4 20:06
 * @Version 1.0.0
 **/
public class DbUtils {

    /**
     * 创建新的数据库连接
     * @return  新的connection对象
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        //1.加载并注册JDBC驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/bystart?useSSL=false&useUnicode=true" +
                "&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String user = "root";
        String pwd = "root";
        //2.创建数据库连接
        Connection conn = DriverManager.getConnection(url, user, pwd);
        return conn;
    }

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

    }

}

JDBC执行INSERT语句

image-20210204201435106.png

package com.xmaven;

import com.xmaven.utils.DbUtils;

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

/**
 * @ClassName InsertCommand
 * @Description TODO 新增数据
 * @Author Ambition
 * @Date 2021/2/4 20:15
 * @Version 1.0.0
 **/
public class InsertCommand {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "insert into user(username,password,salary,dname) values(?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "Ambition");
            pstmt.setString(2, "a123456");
            pstmt.setFloat(3, 2500f);
            pstmt.setString(4, "顶级Boss");
            int cnt = pstmt.executeUpdate();//所有的写操作都是用executeUpdate
            System.out.println("cnt:" + cnt);
            System.out.println("插入数据成功");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }

}

JDBC执行UPDATE语句

image-20210204203049217.png

package com.xmaven;

import com.xmaven.utils.DbUtils;

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

/**
 * @ClassName UpdateCommand
 * @Description TODO 修改数据
 * @Author Ambition
 * @Date 2021/2/4 20:15
 * @Version 1.0.0
 **/
public class UpdateCommand {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "update user set username = ? where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"www.xmaven.cn");
            pstmt.setInt(2,6);
            int cnt = pstmt.executeUpdate();//所有的写操作都是用executeUpdate
            if (cnt == 1){
                System.out.println("修改成功！");
            }else {
                System.out.println("修改数据不成功！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }

}

JDBC执行DELETE语句

image-20210204203350601.png

package com.xmaven;

import com.xmaven.utils.DbUtils;

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

/**
 * @ClassName DeleteCommand
 * @Description TODO 删除数据
 * @Author Ambition
 * @Date 2021/2/4 20:15
 * @Version 1.0.0
 **/
public class DeleteCommand {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from user where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,6);
            int cnt = pstmt.executeUpdate();//所有的写操作都是用executeUpdate
            if (cnt == 1){
                System.out.println("删除成功！");
            }else {
                System.out.println("删除数据不成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }

}
0
java基础 datebase JDBC
版权归属： Epoch
本文链接： https://www.xmaven.cn/archives/java-zhong-de-jdbc
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区