JDBC

2022-05-25 09:40:29

JDBC

数据库驱动

驱动：声卡、显卡、数据库

JDBC

JDBC

为了简化开发人员的操作，对数据库的统一操作提供了一个规范，即为JDBC（Java操作数据库的规范）

这些规范的实现由具体的厂商去做

对于开发人员来说，我们只需要掌握JDBC接口的操作即可

第一个JDBC程序

创建测试数据库

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(40),
	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‘);

导入数据库驱动

JDBC

编写测试代码

package com.kuang.lesson01;

import java.sql.*;

//我的第一个JDBC程序
public class JdbcFirstDemo {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&SSL=true&serverTimezone=UTC";
        String username = "root";
        String password = "123456";
        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行sql，查看返回结果
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

  1. 加载驱动
  2. 连接数据库 DriverManager
  3. 获得执行sql的对象 Statement
  4. 获得返回的结果集
  5. 释放连接

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");

Connection connection = DriverManager.getConnection(url, username, password);

//connection代表数据库
//数据库设置自动提交    
//事务提交    
//事务回滚    
connection.setAutoCommit();
connection.commit();
connection.rollback();

URL

 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&SSL=true&serverTimezone=UTC";

//mysql -- 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

//oracle -- 1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement执行SQL的对象 PrepareStatement

String sql = "SELECT * FROM users"; //编写sql

statement.executeQuery(); 	//查询操作，返回ResultSet
statement.execute();  //执行任何sql
statement.executeUpdate();  //更新、插入、删除都用这个，返回一个受影响的行数

ResultSet查询的结果集，封装了所有的查询结果

获得指定的数据类型

resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行

释放连接

Statement对象

jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

CRUD操作-create

Statement st = conn.createStatement();
String sql = "insert into user(...) values(...) ";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！！！");
}

CRUD操作-delete

Statement st = conn.createStatement();
String sql = "delete from user where id=1 ";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功！！！");
}

CRUD操作-update

Statement st = conn.createStatement();
String sql = "update user set name=‘‘ where name=‘‘ ";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功！！！");
}

CRUD操作-read

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
	//根据获取列的数据类型
}

代码实现

  1. 提取工具类

    public class JdbcUtils {
    
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    
    static {
        
        try {
            InputStream in = Jdbc.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            
            //1.驱动只用加载一次
            Class.forName(driver);
            
        } catch(Exception e){
            e.printStackTrace();
        }
        
    }
    
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    
    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

  2. db.properties

    driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&SSL=true&serverTimezone=UTC"
username=root
password=123456

  3. 测试类

    public class Test {
    public static void main(String[] args) {
        
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        
        try {
            
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement(); //获得SQL的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" + 
"VALUES(4,‘kuangshen‘,‘123456‘,‘24736743@qq.com‘,‘2020-01-01‘)";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！");
            }
                      
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            
            JdbcUtils.release(conn,st,rs);
            
        }
    }
}

SQL注入问题

SQL存在漏洞，会被攻击导致数据泄露，SQL会被拼接or

public class Demo1 {
    public static void main(String[] args) {
        //login("kuangshen","123456");
        login(" ‘or‘1=1"," ‘or‘1=1");
    }
    
    //登录业务
    public static void login(String username, String password) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        
        try {
            
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement(); //获得SQL的执行对象
            //SELECT * FROM users WHERE `name` = ‘kuangshen‘ AND `password` = ‘123456‘
            String sql = "SELECT * FROM users WHERE `NAME`=‘"+username+"‘ AND `password` =‘"+password+"‘";
            rs = st.executeQuery(sql);
            where(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
                      
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            
            JdbcUtils.release(conn,st,rs);
            
        }
    }
}

PreparedStatement

可以防止SQL注入，效率更高

public class Test {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        try {
            
            conn = JdbcUtils.getConnection(); //获取数据库连接
            //使用？号占位符代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
            
            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行
            
            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"qinjiang");
            st.setString(3,"1232112");
            st.setString(4,"2473467@qq.com");
            //sql.Date  util.Date
            st.setDate(5,java.sql.Date(new Date().getTime()));
            
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功！");
            }
                      
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {            
            JdbcUtils.release(conn,st,rs);
        }
    }
}

PreparedStatement防止SQL注入的本质：把传递进来的参数当作字符

假设其中存在转义字符，比如说 ‘ 会被直接转义

使用IDEA连接数据库

JDBC操作事务

CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40),
    money FLOAT
);

INSERT INTO account(name,money) VALUES(‘A‘,1000);
INSERT INTO account(name,money) VALUES(‘B‘,1000);
INSERT INTO account(name,money) VALUES(‘C‘,1000);


public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        try{
            conn = JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            conn.setAutoCommit(false);
            
            String sql1 = "update account set money = money-100 where name = ‘A‘";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();
            
            String sql2 = "update account set money = money+100 where name = ‘B‘";
            conn.prepareStatement(sql2);
            st.executeUpdate();
            
            //业务完毕，提交事务
            conn.commit();
            System.out.println("成功！");
            
        } catch (SQLException e) {
            conn.rollback();
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

数据库连接池

数据库连接 --- 执行完毕 --- 释放

连接 -- 释放 十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

常用连接数10个

最小连接数：10

最大连接数：100 业务最高承载上限

编写连接池，实现一个接口 DataSource

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了

DBCP

dpcpconfig.properties

需要用到jar包

public class JdbcUtils_DBCP {
    
    private static DataSource dataSource = null;
    
    static {
        
        try {
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
            
            //创建数据源 工厂模式--> 创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
            
            
        } catch(Exception e){
            e.printStackTrace();
        }
        
    }
    
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //从数据源中获取连接
    }
    
    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

C3P0

c3p0-config.xml

public class JdbcUtils_C3P0 {
    
    private static DataSource dataSource = null;
    
    static {
        
        try {
            
            dataSource = new ComboPooledDataSource("MySQL");
            
            
        } catch(Exception e){
            e.printStackTrace();
        }
        
    }
    
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //从数据源中获取连接
    }
    
    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

结论

无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变

JDBC

上一篇：mysql(mariadb)相关设置


下一篇：CF1492E Almost Fault-Tolerant Database