Jdbc 学习笔记

小黄鸡

已于 2022-10-11 19:53:26 修改

阅读量405

点赞数

分类专栏： javaweb 文章标签：数据库 sql mysql jdbc

于 2022-10-08 19:53:17 首次发布

本文链接：https://blog.csdn.net/SImple_a/article/details/127205567

版权

javaweb 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1 jdbc示例

使用jdbc框架，更新数据库中的数据

public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String userName = "root";
        String pwd = "1234";

        //1 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2 获取链接
        Connection connection;
        connection = DriverManager.getConnection(url, userName, pwd);

        //3 定义sql
        String sql = "update account set money = 2000 where id = 1";

        //4 获取执行sql的对象 Statement
        Statement statement = connection.createStatement();

        //5 执行sql
        int count = statement.executeUpdate(sql);

        //6 输出结果
        System.out.println(count);

        //7 释放资源
        statement.close();
        connection.close();
    }

}

执行结果输出 1

2 jdbc事务使用示例

使用jdbc框架，来实现MySql中事务的操作

public static void main(String[] args) throws Exception {
        //1 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2 获取连接
        String url = "jdbc:mysql:///db1?useSSL = false";
        String useName = "root";
        String passWord = "1234";

        Connection connection = DriverManager.getConnection(url, useName, passWord);

        //3 定义sql
        String sql1 = "update account set money = 3000 where id = 1";
        String sql2 = "update account set money = 3000 where id = 2";

        //4 获取执行sql对象Statement
        Statement statement = connection.createStatement();

        try {
            connection.setAutoCommit(false);
            //5 执行sql
            int count1 = statement.executeUpdate(sql1);//受影响的行数

            System.out.println(count1);

//            int i = 3 / 0;//手动加错误
            int count2 = statement.executeUpdate(sql2);
            System.out.println(count2);

            /* ==============  提交事务  ===============*/
            connection.commit();
        } catch (Exception e) {
            /* ===================== 回滚事务 =======================*/
            connection.rollback();
            e.printStackTrace();
        }


        //6 释放资源
        statement.close();
        connection.close();
    }

以上代码可以正常更新数据库，如果把注释掉的手动错误不注释掉的话，sql1的语句将会被回滚。

3 jdbc执行DQL

使用jdbc来查询数据库中的内容

public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String userName = "root";
        String pwd = "1234";

        //1 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2 获取链接
        Connection connection;
        connection = DriverManager.getConnection(url, userName, pwd);

        //3 定义sql
        String sql = "select * from account";

        //4 获取执行sql的对象 Statement
        Statement statement = connection.createStatement();

        //5 执行sql
        ResultSet resultSet = statement.executeQuery(sql);

        //6 输出结果
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            double money = resultSet.getDouble("money");

            System.out.println("id = " + id + ", name = " + name + ", money = " + money);
        }

        //7 释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }

输出结果

id = 1, name = 张三, money = 3000.0
id = 2, name = 李四, money = 3000.0

4 sql注入

4.1 模拟注入问题

public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String userName = "root";
        String pwd = "1234";
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, userName, pwd);
        String name = "zhsan";
        String password = "' or '1' = '1";
        String sql = "select * from tb_user where username = '"+name + "' and password = '" + password + "'";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

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

        resultSet.close();
        statement.close();
        connection.close();
    }

以上代码执行后，不管name和password输入什么值，都会提示登录成功，因为后面的or ‘1’ = ‘1’ 条件成立

4.2 使用PreparedStatement改进注入问题

public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:3306/db1";
        String userName = "root";
        String pwd = "1234";
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, userName, pwd);
        String name = "zhsan";
        String password = "' or '1' = '1";
        String sql = "select * from tb_user where username = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setString(1, name);
        preparedStatement.setString(2, password);

        ResultSet resultSet = preparedStatement.executeQuery();

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

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

此时将会输出登录失败，改成正确的用户名和密码后，会提示登录成功

5 数据库连接池

使用数据库连接池来避免频繁的创建和销毁连接，提升系统响应速度。
使用Druid数据库连接池框架

public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("Jdbc-Demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }

6 jdbc练习

创建表，添加假数据

-- 创建表tb_brand
create table tb_brand(
    id int primary key auto_increment,
    brand_name varchar(20),
    company_name varchar(20),
    ordered int,
    description varchar(100),
    status int
);

-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values('三只松鼠', '三鼠公司', 5, '好吃不贵', '0'),
('华为', '华为数科', 120, '提供高端科技', '1'),
('三只松鼠', '三鼠公司', 50, '美好的事即将发生', '1');

6.1 添加数据

public static void testAdd() throws Exception {
        String brandName = "比亚迪";
        String companyName = "比亚迪车业";
        int ordered = 300;
        String description = "都是电车干就完了";
        int status = 1;

        Properties properties = new Properties();
        properties.load(new FileInputStream("Jdbc-Demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?, ?, ?, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setString(1, brandName);
        preparedStatement.setString(2, companyName);
        preparedStatement.setInt(3, ordered);
        preparedStatement.setString(4, description);
        preparedStatement.setInt(5, status);

        int count = preparedStatement.executeUpdate();
        System.out.println("count = " + count);

        preparedStatement.close();
        connection.close();
    }

6.2 查询数据

public static void testSelectAll() throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("Jdbc-Demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql = "select * from tb_brand;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        Brand brand = null;
        List<Brand> brandList = new ArrayList<>();
        while(resultSet.next()) {
            brand = new Brand();
            brand.setId(resultSet.getInt("id"));
            brand.setBrandName(resultSet.getString("brand_name"));
            brand.setCompanyName(resultSet.getString("company_name"));
            brand.setOrdered(resultSet.getInt("ordered"));
            brand.setDescription(resultSet.getString("description"));
            brand.setStatus(resultSet.getInt("status"));
            brandList.add(brand);
        }

        System.out.println(brandList);
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

6.3 修改数据

public static void testUpdate() throws Exception {
        String brandName = "比亚迪";
        String companyName = "比亚迪车业";
        int ordered = 30;
        String description = "四个车轱辘的车企";
        int status = 1;
        int id = 4;

        Properties properties = new Properties();
        properties.load(new FileInputStream("Jdbc-Demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql = "update tb_brand\n" +
                "set brand_name = ?,\n" +
                "company_name = ?,\n" +
                "ordered = ?,\n" +
                "description = ?,\n" +
                "status = ?\n" +
                "where id = ?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setString(1, brandName);
        preparedStatement.setString(2, companyName);
        preparedStatement.setInt(3, ordered);
        preparedStatement.setString(4, description);
        preparedStatement.setInt(5, status);
        preparedStatement.setInt(6, id);

        int count = preparedStatement.executeUpdate();
        System.out.println("count = " + count);

        preparedStatement.close();
        connection.close();
    }

6.4 删除数据

public static void testDelete() throws Exception {
        int id = 4;

        Properties properties = new Properties();
        properties.load(new FileInputStream("Jdbc-Demo/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql = "delete from tb_brand where id = ?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setInt(1, id);

        int count = preparedStatement.executeUpdate();
        System.out.println("count = " + count);

        preparedStatement.close();
        connection.close();
    }