JDBC笔记

1、数据库驱动

2、JDBC

3、第一个JDBC程序

1️⃣ 建表

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

2️⃣ 创建普通项目

3️⃣ 导入jar包

mysql-connector-java

4️⃣ 编写测试代码

package com.cty.lesson1;

import java.sql.*;

public class JdbcDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法

        //2、用户信息和url
        //三个参数
        //useUnicode 编码支持中文
        //characterEncoding 设定中文字符集
        //userSSL 使用安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=true";
        String username = "root";
        String password = "123456";

        //3、连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

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

        //5、执行sql的对象去执行sql
        String sql = "SELECT * FROM users";

        //返回的结果集 结果集中封装了我们全部的查询出来的结果
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("id=" + resultSet.getObject("NAME"));
            System.out.println("id=" + resultSet.getObject("PASSWORD"));
            System.out.println("id=" + resultSet.getObject("email"));
            System.out.println("id=" + resultSet.getObject("birthday"));
            System.out.println("===================================================");
        }

        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

4、statement对象

5、sql注入

sql存在漏洞，会被攻击导致泄露

本质：SQL会被拼接，实现一些别的命令

6、preparedStatement对象

可以防止sql注入

package com.cty.lesson2;

import java.sql.*;

/**
 * @author caoyb
 */
public class PreparedStatementDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法

        //2、用户信息和url
        //三个参数
        //useUnicode 编码支持中文
        //characterEncoding 设定中文字符集
        //userSSL 使用安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=true";
        String username = "root";
        String password = "123456";

        //3、连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //使用？占位符代替参数
        String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";

        //预编译sql，先写sql，不执行
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //手动给参数赋值
        preparedStatement.setInt(1,4);
        preparedStatement.setString(2,"cty");
        preparedStatement.setString(3,"123456");
        preparedStatement.setString(4,"13123@qq.com");
        preparedStatement.setDate(5, new java.sql.Date(System.currentTimeMillis()));

        //执行
        int i = preparedStatement.executeUpdate();
        if(i > 0){
            System.out.println("插入成功");
        }
        preparedStatement.close();
        connection.close();
    }
}

7、事务

package com.cty.lesson3;

import java.sql.*;

public class Demo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法

        //2、用户信息和url
        //三个参数
        //useUnicode 编码支持中文
        //characterEncoding 设定中文字符集
        //userSSL 使用安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=true";
        String username = "root";
        String password = "123456";

        //3、连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        PreparedStatement preparedStatement = null;


        //关闭数据库的自动提交功能，自动会开启事务
        connection.setAutoCommit(false);

        String sql1 = "update account set money = money - 100 where name = 'A'";
        preparedStatement =  connection.prepareStatement(sql1);
        preparedStatement.executeUpdate();

        String sql2 = "update account set money = money + 100 where name = 'B'";
        preparedStatement =  connection.prepareStatement(sql2);
        preparedStatement.executeUpdate();

        connection.commit();
        System.out.println("成功！");
        try {
            //如果失败就回滚
            connection.rollback();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            preparedStatement.close();
            connection.close();
        }
    }
}

8、数据库连接池