JDBC操作数据库

电玩女神 2022-04-10 09:47 272阅读 0赞

## JDBC ##

# JDBC #

Java database connectivity  
是java语言操作数据库的 api (应用程序编程接口)  
java.sql.\*

接口  
Connection 连接 代表了java和数据之间的通道，桥梁  
Statement 语句 可以用来执行 insert, update, delete , select …  
ResultSet 结果集 代表的是查询的结果

类  
DriverManager 工具类，获取连接对象  
SQLException 异常对象 是 Exception的子类型，属于检查异常

## 操作步骤顺序 ##

1.  加载驱动 (Driver) jdbc的驱动就是一个连接工厂，生成的产品是连接对象  
    com.mysql.jdbc.Driver 是Driver的mysql实现类  
    oracle.jdbc.Driver 是Driver的oracle实现类

Class.forName("驱动类名");
       例如：
       Class.forName("com.mysql.jdbc.Driver");

jdbc 3.0 以上版本都可以省略加载驱动这一步

1.  获取连接对象

DriverManager.getConnection(url, 用户名, 密码); // 内部调用了 Driver 对象获取数据库连接

url 的格式 :

jdbc:mysql://ip地址:端口号/数据库名?参数

例如：mysql数据库

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");

1.  创建语句

Statement stmt = conn.createStatement();

1.  执行sql  
    执行 insert , update, delete

int rows = stmt.executeUpdate()

rows表示该语句影响行数

执行select

ResultSet rs = stmt.executeQuery(); //rs为返回的结构集

取出查询到的元素

while(rs.next()) { 
    	rs.getInt("列名");
    	rs,getString("列名");
    }

1.  释放资源 ( 先打开的资源后关闭 )

rs.close();
    stmt.close();
    conn.close();

# PreparedStatement 预编译语句对象 #

Statement stmt = conn.createStatement();  
stmt.executeUpdate(String sql);

// 1. 需要预先提供sql语句

PreparedStatement psmt = conn.prepareStatement(String sql);

// 2. 可以在sql中用？占位某个值

insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)

// 3. 给？赋值

使用PreparedStatement中一系列以 set开头的方法
    setString(?的位置, 值)
    setInt(?的位置, 值)
    setDate(?的位置, 值)
    setObject((?的位置, 值);
    
    psmt.setString(1, "李四");
    psmt.setString(2, "1999-9-7");

// 4. 执行sql

psmt.executeUpdate();

注意： ？能够占位的只有值， 不能是表名、列名、关键字

## SQL注入攻击 ##

create table user (  
username varchar(20) primary key,  
password varchar(20)  
);  
insert into user(username, password) values(‘zhangsan’,‘123’);  
防范方法：

1.  对参数内存做检查，内部不能有sql关键字例如：or
2.  PreparedStatement

## 获取自增的主键值 ##

Statement.RETURN\_GENERATED\_KEYS 是一个选项，表示要返回自增的主键值

String sql = "insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)"; 
    try (PreparedStatement psmt
                             = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS) ) { 
    	 psmt.setString(1, "aaa");
         psmt.setString(2, "1999-1-1");
         psmt.setString(3, "男");
         int rows = psmt.executeUpdate();
         System.out.println("影响行数:" + rows);
        // 返回一个结果集，结果集中存储了刚刚生成的主键值
           ResultSet rs = psmt.getGeneratedKeys();
          // 就一行一列
          rs.next();
          int id = rs.getInt(1);
          System.out.println("主键值是：" + id);
    }

## 将插入、查询封装成方法 ##

插入

public static void insert(String sql, Object ... values) { 
            try(Connection conn = getConnection()){ 
                try(PreparedStatement psmt = conn.prepareStatement(sql)){ 
                    int i = 1;
                    // 循环给？参数赋值
                    for (Object value : values) { 
                        psmt.setObject(i, value);
                        i++;
                    }
                    // 执行sql语句
                    psmt.executeUpdate();
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            }
        }

public static void main(String[] args) { 
           String sql = "insert into student(sid,sname,birthday,sex) values(null, ?, ?, ?)";
            insert(sql, "张三", "1999-1-1", "男");
        }

查询

//定义接口
    public interface RowMapper<T> { 
        public T mapper(ResultSet rs) throws SQLException;
    }

public static <T> List<T> findBy(String sql, RowMapper<T> rowMapper, Object... values) { 
            try(Connection conn = getConnection()){ 
                try(PreparedStatement psmt = conn.prepareStatement(sql)){ 
                    int i = 1;
                    // 循环给？参数赋值
                    for (Object value : values) { 
                        psmt.setObject(i, value);
                        i++;
                    }
                    // 执行sql语句
                    try(ResultSet rs = psmt.executeQuery()){ 
                        List<T> list = new ArrayList<>();
                        while(rs.next()) { 
                            // 把rs 中的一行记录变为 java中的一个对象(Student,...)
                            T obj = rowMapper.mapper(rs);
                            list.add(obj);
                        }
                        return list;
                    }
                }
            } catch (SQLException e) { 
                e.printStackTrace();
                return Collections.emptyList();
            }
        }
        ```
     ```java
     public static void main(String[] args) { 
            List<Dept> depts = findBy("select * from dept where dname=?", rs -> { 
                int deptno = rs.getInt("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                return new Dept(deptno, dname, loc);
            }, "市场部");
            
            for (Dept dept : depts) { 
                System.out.println(dept);
            }
        }
    }

## 事务控制 ##

begin; // 开始事务 start transaction;  
多条sql语句

commit; // 提交事务, 最终确认，让所有修改生效  
rollback; // 回滚事务，撤销事务内所有的修改

jdbc 默认是让每条sql的执行作为一个独立的事务  
让事务变为手动提交：

try { 
        Connection.setAutoCommit(false);
        // 执行多条sql 接下来，事务会在执行第一条 insert, update, delete 时 开始
        // 提交事务
        Connection.commit();
    } catch(Exception e ) { 
        // 回滚事务
        Connection.rollback();
    }

try(Connection conn = JdbcUtils.getConnection()) { 
        try { 
            // 让事务变成手动提交
            conn.setAutoCommit(false);
            PreparedStatement stmt = conn.prepareStatement("delete from student where sid = ?");
            stmt.setInt(1, 1005);
            // begin
            stmt.executeUpdate();
            int i = 1 / 0; // 出现异常
            stmt.setInt(1, 1007);
            stmt.executeUpdate();
            conn.commit(); // 手动提交事务
        } catch (SQLException e) { 
            conn.rollback(); // 手动回滚事务
        }
    } catch (SQLException e) { 
        e.printStackTrace();
    }

## 性能提升 ##

### 1. 批量增删改 ###

10 万

create table big (
        id int primary key auto_increment,
        name varchar(100)
    );

批处理  
PreparedStatement.addBatch() 加入批处理包  
PreparedStatement.executeBatch() 把批处理包中所有sql，一次性发送给数据库服务器

对于mysql需要通过连接参数，开启批处理功能：  
在连接字符串之后添加：

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

示例代码：

try(Connection conn = JdbcUtils.getConnection()) { 
        PreparedStatement psmt = conn.prepareStatement("insert into big(name) values(?)");
        long start = System.currentTimeMillis();
        for (int i = 1; i <= 100001; i++) { 
            psmt.setString(1, "张" +i);
            psmt.addBatch(); // 10001 10002 .... ... 19999 20000
            if(i % 10000 == 0) { 
                psmt.executeBatch(); // 发送给服务器并清空批处理包
            }
        }
        psmt.executeBatch(); // 收尾的
        long end = System.currentTimeMillis();
        System.out.println("共花费了:" +(end-start)+"毫秒");
    } catch (SQLException e) { 
        e.printStackTrace();
    }

### 2. 查询性能提升 ###

返回记录行数 10000  
PreparedStatement.setFetchSize(50);  
mysql 中需要在连接参数中加入： useCursorFetch=true，来开启抓取大小的限制功能  
还可以给fetchSize一个默认值： 连接参数中加入： defaultFetchSize=50

while(rs.next()) {  2001~3000
    
    }

## 连接池 (connection pool) ##

apache dbcp 最老牌  
c3p0 连接池  
alibaba druid (德鲁伊) sql监控

javax.sql.DataSource 连接池接口  
Conneciton conn = datasource.getConnection(); 从池中返回连接对象

conn.close(); // 一般都覆盖了关闭方法，将连接归还给连接池，而不是真正关闭  
文档地址：  
[https://github.com/alibaba/druid/wiki/DruidDataSource配置属性列表][https_github.com_alibaba_druid_wiki_DruidDataSource]

*  用DriverManager 获取连接，称为直连
 *  用DataSource 获取连接，称为池连

DruidDataSource dataSource = new DruidDataSource();
    dataSource.setUrl("jdbc:mysql://localhost:3306/test");
    dataSource.setUsername("root");
    dataSource.setPassword("root");
    
    // dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 可选步骤，注册驱动
    dataSource.setInitialSize(5); // 初始连接数
    dataSource.setMaxActive(10); // 最大连接数
    dataSource.setMinIdle(5);    // 最小连接数
    dataSource.setValidationQuery("select 1"); // 一条简单的sql语句，用来保活
    dataSource.setTestWhileIdle(true); // 当空闲时时不时检查一下连接的有效性, 利用ValidationQuery中的sql语句
    dataSource.setTimeBetweenEvictionRunsMillis(60*1000); // 默认一分钟
    
    try(Connection conn = dataSource.getConnection()){ 
    
        // conn.close();// 归还连接池了
    }

[https_github.com_alibaba_druid_wiki_DruidDataSource]: https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8