详解MySQL之JDBC

8 篇文章 0 订阅

JDBC

1. 驱动注册到底做了什么?
静态代码块:
	特征: 类文件加载直接运行,并且有且只运行一次
	作用: 初始化程序

Class.forName("com.mysql.jdbc.Driver");
利用反射 Class.forName方法,加载指定的包名.类名对应的.class字节码文件。
等于指定的.class文件以及存在,以及可以运行!!!
package com.mysql.jdbc;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}
/*
JDBC com.mysql.jdbc.Driver 类源码
通过反射Class.forName("com.mysql.jdbc.Driver");就已经加载当前com.mysql.jdbc.Driver完成。并且这里存在一个static修饰的静态代码块
静态代码块是随着类文件的加载而直接运行。

在当前静态代码块中执行了
	DriverManager.registerDriver(new Driver());
	DriverManager JDBC驱动管理类,调用的注册驱动的方法,而且注册的驱动是当前com.mysql.jdbc.Driver

提供给当前Java程序连接数据库的能力!!!
驱动加载
*/
2. Statement使用
2.1 Statement介绍
interface java.sql.Statement
	JDBC规范中SQL语句搬运工对象
获取Statement实现类对象的方法
通过java.sql.Connection createStatment获取

int executeUpdate(String sql);
	执行update delete insert... 这一些SQL语句
java.sql.ResultSet executeQuery(String sql);
	执行select SQL语句
void close();
2.2 使用Statement完成增加操作
@Test
public void testAdd() {
    Connection connection = null;
    Statement statement = null;
    try {
        // 1. 驱动加载
        Class.forName("com.mysql.jdbc.Driver");
        
        // 2. 准备连接数据库使用的资源
        String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=tru";
        String user = "root";
        String password = "123456";
       
        // 3. 准备SQL语句
        String sql = "insert into javaee1911.person(name, sex, age, salary, info) value('龙龙の福林', 0, 16, 100, 'XXXXXXX')";
        
        // 4. 获取数据库连接
        connection = DriverManager.getConnection(url, user, password);
        
        // 5. 通过Connection获取Statement对象
        statement = connection.createStatement();
       
        // 6. 执行SQL语句
        int i = statement.executeUpdate(sql);
       
        // 7. 展示数据库操作受到影响的行数
        System.out.println("Affected rows " + i);
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        // finally是代码一定会运行的内容,在这里处理资源问题
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
2.3 使用Statement完成删除操作
public void testDelete() {
    Connection connection = null;
    Statement statement = null;
    try {
        // 1. 驱动加载
        Class.forName("com.mysql.jdbc.Driver");
        
        // 2. 准备数据库连接所需资源
        String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
        String user = "root";
        String password = "123456";
        
        // 3. SQL语句
        String sql = "delete from javaee1911.person where id = 25";
        
        // 4. 获取数据库连接
        connection = DriverManager.getConnection(url, user, password);
        
        // 5. 获取Statement
        statement = connection.createStatement();
        
        // 6. 执行SQL语句
        int i = statement.executeUpdate(sql);
        System.out.println("Affected rows " + i);
    } catch (ClassNotFoundException | 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();
            }
        }
    }
}
2.4 使用Statement完成修改操作
public void testUpdate() {
    Connection connection = null;
    Statement statement = null;
    try {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        // 2. 准备数据库连接所需的数据
        String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
        String user = "root";
        String password = "123456";
        
        // 3. 准备SQL语句
        String sql = "update javaee1911.person set name = '狂霸酷帅叼' where id = 24";
        
        // 4. 获取数据库连接
        connection = DriverManager.getConnection(url, user, password);
        
        // 5. 获取Statement
        statement = connection.createStatement();
        
        // 6. 执行SQL语句
        int i = statement.executeUpdate(sql);
        System.out.println("Affected rows " + i);
    } catch (ClassNotFoundException | 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();
            }
        }
    }
}
2.5 使用Statement完成查询操作
public void testSelectOne() {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    Person p = null;
    
    try {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        // 2. 准备数据连接必要资源
        String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
        String user = "root";
        String password = "123456";
        
        // 3. 准备SQL语句
        String sql = "select * from javaee1911.person where id = 1";
        
        // 4. 获取数据库连接对象
        connection = DriverManager.getConnection(url, user, password);
        
        // 5. 获取Statement对象
        statement = connection.createStatement();
        
        // 6. 执行SQL语句获取ResultSet结果集对象
        resultSet = statement.executeQuery(sql);
        
        // 7. 解析ResultSet结果集对象
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            boolean sex = resultSet.getBoolean("sex");
            int age = resultSet.getInt("age");
            float salary = resultSet.getFloat("salary");
            String info = resultSet.getString("info");
            
            p = new Person(id, name, sex, age, salary, info);
        }
        System.out.println(p);
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
       
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public void testSelectAll() {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    List<Person> list = new ArrayList<>();
    try {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 准备数据连接必要资源
        String url = "jdbc:mysql://localhost:3306/javaee1911?useSSL=true";
        String user = "root";
        String password = "123456";
        // 3. 准备SQL语句
        String sql = "select * from javaee1911.person";
        // 4. 获取数据库连接对象
        connection = DriverManager.getConnection(url, user, password);
        // 5. 获取Statement对象
        statement = connection.createStatement();
        // 6. 执行SQL语句获取ResultSet结果集对象
        resultSet = statement.executeQuery(sql);
        // 7. 解析ResultSet结果集对象
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            boolean sex = resultSet.getBoolean("sex");
            int age = resultSet.getInt("age");
            float salary = resultSet.getFloat("salary");
            String info = resultSet.getString("info");
            list.add(new Person(id, name, sex, age, salary, info));
        }
        for (Person person : list) {
            System.out.println(person);
        }
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
3. java.sql.ResuleSet
java.sql.ResultSet 数据库结果集接口
--| XXX getXXX(int ColumnIndex);
	根据查询结果集字段下标顺序获取对应的字段数据
--| XXX getXXX(String ColumnName);
	根据查询结果集字段名获取对应的字段数据
4. JdbcUtil工具类封装
可以考虑复用的代码:
	1. 数据库驱动加载
	2. 数据库连接的必要资源
	3. 获取数据库连接对象java.sql.Connection
	4. 对于资源的处理关闭操作

Properties
	属性类,所有数据都是键值对形式
	name=杰克 age=16 sex=男

准备一个Properties文件,存储到src目录下,修改自己的数据属性
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/javaee1911?useSSL=true
user=root
password=123456
package util;
/*
可以考虑复用的代码:
	1. 数据库驱动加载
	2. 数据库连接的必要资源
	3. 获取数据库连接对象java.sql.Connection
	4. 对于资源的处理关闭操作
 */

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * JdbcUtil JDBC工具类封装
 *
 * @author Anonymous
 */
public class JdbcUtil {

    // 准备必要的数据库连接资源
    private static String url;
    private static String user;
    private static String password;

    // 利用static静态代码块特征加载驱动
    static {

        try {
            // 读取src目录下db.properties文件
            Properties properties = new Properties();
            properties.load(new FileInputStream("./src/db.properties"));

            // 从Properties类内获取对应Key数据
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");

            String driverClass = properties.getProperty("driverClass");

            Class.forName(driverClass);
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接对象 java.sql.Connection
     *
     * @return java.sql.Connection对象
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }

    /**
     * 关闭资源方法,这里关闭的是数据库连接对象Connection
     *
     * @param connection java.sql.Connection
     */
    public static void close(Connection connection) {
        close(connection, null, null);
    }

    /**
     * 关闭资源方法,这里关闭的是数据库连接对象Connection和Statement对象
     *
     * @param connection java.sql.Connection
     * @param statement  java.sql.Statement
     */
    public static void close(Connection connection, Statement statement) {
        close(connection, statement, null);
    }

    /**
     * 关闭资源方法,这里关闭的是数据库连接对象Connection,Statement对象和ResultSet对象
     *
     * @param connection java.sql.Connection
     * @param statement  java.sql.Statement
     * @param resultSet  java.sql.ResultSet
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
5. PreparedStatement使用
5.1 使用PreparedStatement完成增加操作
public void testAdd() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    
    // 1. 获取数据库连接
    connection = JdbcUtil.getConnection();
    
    /*
     2. 准备SQL语句
     ?是SQL语句参数占位符!!!
    */
    String sql = "insert into javaee1911.person(name, sex, age, salary, info) value(?, ?, ?, ?, ?)";
    
    try {
        // 3. 根据SQL语句,通过Connection对象得到对应的PreparedStatement对象
        preparedStatement = connection.prepareStatement(sql);
        
        // 4. 处理SQL语句占位符参数 SQL语句占位符参数下标从1开始
        preparedStatement.setObject(1, "多复习");
        preparedStatement.setObject(2, false);
        preparedStatement.setObject(3, 20);
        preparedStatement.setObject(4, 15.5);
        preparedStatement.setObject(5, "温故而知新,可以为师矣");
        
        // 5. 执行SQL语句
        int i = preparedStatement.executeUpdate();
        System.out.println("Affected Rows " + i);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(connection, preparedStatement);
    }
}
5.2 使用PreparedStatement完成删除操作
public void testDelete() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    connection = JdbcUtil.getConnection();
    String sql = "delete from person where id = ?";
    try {
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, 19);
        int i = preparedStatement.executeUpdate();
        System.out.println("Affected Rows " + i);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(connection, preparedStatement);
    }
}
5.3 使用PreparedStatement完成修改操作
public void testUpdate() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    connection = JdbcUtil.getConnection();
    String sql = "update person set name = ?, age = ? where id = ?";
    try {
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, "测试");
        preparedStatement.setObject(2, 30);
        preparedStatement.setObject(3, 10);
        int i = preparedStatement.executeUpdate();
        System.out.println("Affected Rows " + i);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(connection, preparedStatement);
    }
}
5.4 使用PreparedStatement完成查询操作
public void testSelectOne() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    Person p = null;
    connection = JdbcUtil.getConnection();
    String sql = "select * from person where id = ?";
    try {
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, 9);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            boolean sex = resultSet.getBoolean("sex");
            int age = resultSet.getInt("age");
            float salary = resultSet.getFloat("salary");
            String info = resultSet.getString("info");
            p = new Person(id, name, sex, age, salary, info);
        }
        System.out.println(p);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(connection, preparedStatement, resultSet);
    }
}

public void testSelectAll() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    connection = JdbcUtil.getConnection();
    List<Person> list = new ArrayList<>();
    String sql = "select * from person";
    try {
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            boolean sex = resultSet.getBoolean("sex");
            int age = resultSet.getInt("age");
            float salary = resultSet.getFloat("salary");
            String info = resultSet.getString("info");
            list.add(new Person(id, name, sex, age, salary, info));
        }
        for (Person person : list) {
            System.out.println(person);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(connection, preparedStatement, resultSet);
    }
}
6. Statement和PreparedStatement选择问题
选择PreparedStatement
	1. 可以解决基本的SQL注入问题
	2. SQL预处理之后可以复用,提高效率
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值