JDBC基础Demo

hrui0706

已于 2024-08-21 17:12:43 修改

阅读量563

点赞数 19

文章标签： java

于 2024-08-21 14:59:30 首次发布

本文链接：https://blog.csdn.net/tiantiantbtb/article/details/141391988

版权

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>

    <dependencies>
        <!-- Add your dependencies here -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
    </dependencies>


    <build>
        <finalName>jdbc</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId><!--构建包含依赖的jar包-->
                <version>3.2.1</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <mainClass>com.example.JDBCTest01</mainClass><!--指定启动类-->
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

项目结构

增删改

package com.example;

import com.mysql.cj.jdbc.ConnectionImpl;

import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;

/**
 * @author hrui
 * @date 2024/8/20 8:27
 */
public class JDBCTest01 {

    public static void main(String[] args){
        //1.注册驱动
//        Driver driver = new com.mysql.cj.jdbc.Driver();
//        DriverManager.registerDriver(driver);
        //Class.forName("com.mysql.cj.jdbc.Driver");//这一步将上面两部做了  新版本这一步也可以省略
        //2.获取连接    在JDBC 4.0之后，可以省略注册驱动的步骤 getConnection时候会读META-INF/services/java.sql.Driver中内容并注册驱动
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql:/localhost:3306/jdbc", "root", "123456");
            System.out.println(connection);//com.mysql.cj.jdbc.ConnectionImpl@548ad73b
            //3.获取SQL执行对象
            statement = connection.createStatement();
            //String sql = "insert into user values(null,\"haha\",\"123456\")";
            String sql = "insert into user values(null,\"哈哈\",\"123456\"),(null,\"haha3\",\"123456\")";
            //4.执行sql(executeUpdate专门用于执行DML语句insert delete update 返回影响数据的记录条数)
            int i = statement.executeUpdate(sql);
            System.out.println(i);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源依次从小到大
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

增删改以配置文件方式配置文件放在resources下

package com.example;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Properties;

/**
 * 配置文件方式配置数据库连接
 * @author hrui
 * @date 2024/8/21 12:39
 */
public class JDBCTest02 {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        try {
            //使用类加载器加载 properties 文件
            InputStream input = JDBCTest02.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties prop = new Properties();
            prop.load(input);
            String url = prop.getProperty("url");
            String username = prop.getProperty("username");
            String password = prop.getProperty("password");
            //获取连接
            connection = DriverManager.getConnection(url, username, password);
            //获取执行对象
            statement = connection.createStatement();
            String sql="insert into user values(null,\"haha3\",\"456\")";
            //执行sql
            int count = statement.executeUpdate(sql);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();

        }finally {
            if(statement!=null){
                try {
                    statement.close();
                } catch (Exception e2) {
                    e2.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (Exception e2) {
                    e2.printStackTrace();
                }
            }
        }
    }
}

resources下创建jdbc.properties

url=jdbc:mysql://localhost:3306/jdbc
username=root
password=123456

基础查询操作

package com.example;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * 查询数据 需要处理结果集
 * @author hrui
 * @date 2024/8/21 14:06
 */
public class JDBCTest03 {
    public static void main(String[] args) {

        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //使用类加载器加载 properties 文件
            InputStream input = JDBCTest03.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties prop = new Properties();
            prop.load(input);
            String url = prop.getProperty("url");
            String username = prop.getProperty("username");
            String password = prop.getProperty("password");
            connection = DriverManager.getConnection(url, username, password);

            statement = connection.createStatement();
            String sql="select * from user";
            //int executeUpdate (insert/delete/update)
            //ResultSet executeQuery (select)
            resultSet = statement.executeQuery(sql);//专门执行DQL语句的方法

            //注意:所有列都可以转String 类型 但是DECIMAL 或 NUMERIC 可能转换导致丢失精度
            //还有一些特殊类型 可能导致转换不是预期
            while (resultSet.next()){
                //以查询出来的下标获取  注意 下标根据select xxx,xxx,xxx确定
//                int id = resultSet.getInt(1);
//                String name = resultSet.getString(2);
//                String pw = resultSet.getString(3);
                //按查询出来的列明获取 注意是查询结果的列明  如 select id, username as ua, password  那么不应该是username 而是ua
                int id = resultSet.getInt("id");
                String name = resultSet.getString("username");
                String pw = resultSet.getString("password");
                System.out.println(id+":"+name+":"+pw);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}

演示Statement存在sql注入的问题

package com.example;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;

/**
 * 演示sql注入问题
 * @author hrui
 * @date 2024/8/21 15:33
 */
public class JDBCTest04 {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;

        try {

            Scanner scanner = new Scanner(System.in);
            System.out.println("输入用户名:");
            String username = scanner.nextLine();
            System.out.println("输入密码:");
            String password = scanner.nextLine();
            String sql = "select * from user where username='"+username+"' and password='"+password+"'";
            System.out.println(sql);
            //使用类加载器加载 properties 文件 获取 url username password
            InputStream input = JDBCTest04.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties prop = new Properties();
            prop.load(input);

            connection = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            if(resultSet.next()){
                System.out.println("登录成功");
            }else {
                System.out.println("登录失败");
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}

SQL注入的原因

直接拼接：由于用户输入的数据直接拼接到 SQL 语句中，并被送入数据库引擎进行编译，恶意的 SQL 代码也参与了编译和执行过程，导致 SQL 注入攻击。
参与编译：用户输入的未经过滤或转义的数据参与了 SQL 语句的编译，导致了 SQL 注入风险。

解决SQL注入问题

Statement 数据库操作对象

PreparedStatement 预编译的数据库操作对象

public interface PreparedStatement extends Statement

解决SQL注入根本在于,用户提供的信息不参与SQL语句的编译过程,这样即使用户提供的信息包含SQL语句的关键字,但是没有参与编译,就会不起作用

PreparedStatement 的原理是:预先对SQL语句进行了编译

package com.example;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * 解决SQL注入问题
 * @author hrui
 * @date 2024/8/21 15:50
 */
public class JDBCTest05 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {

            Scanner scanner = new Scanner(System.in);
            System.out.println("输入用户名:");
            String username = scanner.nextLine();
            System.out.println("输入密码:");
            String password = scanner.nextLine();
            String sql = "select * from user where username=? and password=?";

            //使用类加载器加载 properties 文件 获取 url username password
            InputStream input = JDBCTest04.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties prop = new Properties();
            prop.load(input);

            connection = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);//是字符串就set字符串  int就setInt
            preparedStatement.setString(2,password);

            resultSet = preparedStatement.executeQuery();//sql不需要在往里传  如果再传的话,会重新编译

            if(resultSet.next()){
                System.out.println("登录成功");
            }else {
                System.out.println("登录失败");
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (preparedStatement!=null){
                try {
                    preparedStatement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}

对比Statement和PreparedStatement

1.Statement存在SQL注入风险

2.PreparedStatement预编译,Statement是编译一次执行一次,PreparedStatement编译一次可执行多次,效率相对高.PreparedStatement会做类型安全检查(就是你setString 结果出入的是100 编译器报错)

package com.example;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Properties;

/**
 * PrepareStatement 增删改
 * @author hrui
 * @date 2024/8/21 16:28
 */
public class JDBCTest06 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {

            InputStream resourceAsStream = JDBCTest06.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties prop = new Properties();
            prop.load(resourceAsStream);

            connection = DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
            preparedStatement = connection.prepareStatement("insert into user(username,password) values(?,?)");
            preparedStatement.setString(1,"hrui");
            preparedStatement.setString(2,"123456");
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (preparedStatement!=null){
                try {
                    preparedStatement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC事务机制默认是自动提交

JDBC工具类封装

package com.example;

import java.sql.*;
import java.util.ResourceBundle;

/**
 * @author hrui
 * @date 2024/8/21 16:51
 */
public class DBUtil {
    //静态变量:在类加载时候执行,并且是有先后顺序的.自上而下
    private static ResourceBundle bundle=ResourceBundle.getBundle("resource1.jdbc");
    //JDBC4开始  DriverManager.getConnection会读Mysql驱动包中的配置文件
    //配置文件在META-INF/META-INF/services/java.sql.Driver
    //private static String driver=bundle.getString("driver");
    private static String url=bundle.getString("url");
    private static String username=bundle.getString("username");
    private static String password=bundle.getString("password");

//    static{
//        //注册驱动(注册却动只需要注册一次,放在静态代码块当中.DBUtil类加载的时候执行)
//        try {
//            //"com.mysql.jdbc.Driver"是连接数据库的驱动,不能写死,因为可能以后改成Oracle数据库
//            //如果连接Oracle数据库的时候,还需要修改java代码,显然违背OCP开闭原则
//            //OCP开闭原则:对扩展开发,对修改关闭.(什么是复合OCP呢?在进行功能扩展的时候,不需要修改java源代码)
//            //Class.forName("com.mysql.cj.jdbc.Driver");
//            //Class.forName(driver);
//            DriverManager.getConnection(url,username, password);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    //获取数据库连接对象  抛异常,谁使用,谁负责
    public static Connection getConnection() throws SQLException {
        //JDBC4开始  DriverManager.getConnection会读Mysql驱动包中的配置文件
        //配置文件在META-INF/META-INF/services/java.sql.Driver
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    //关闭连接 释放资源

    /**
     * 释放资源
     * @param conn 数据库连接对象
     * @param st 执行对象
     * @param rs 结果集对象
     */
    public static void close(Connection conn, Statement st, ResultSet rs){

        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

注意模糊查询时候的错误写法