数据库学习之JDBC

JDBC

JDBC：SUN公司为了简化开发人员对数据库的统一操作，提供了一个java操作数据库的规范，称为JDBC。

我们只需掌握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(60),
  birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','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');

1、创建一个普通的项目

2、导入数据库驱动

测试代码：

package com.llx.study01;

import java.sql.*;

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

        // 2. 用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=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("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的对象

4、获得返回的结果集

5、释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, username,
password);
// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务滚回
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?
useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql -- 3306
// 协议 ://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce -- 1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement 执行SQL 的对象

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.getObject();

遍历

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

释放资源

resultSet.close();
statement.close();
connection.close(); // 耗资源，用完关掉！

statement对象

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

Statement对象的executeUpdate()方法，用于向数据库发送增、删、改的sql语句，executeUpdate()方法执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。

Statement.executeQuery()方法用于向数据库发送查询语句，executeQuery()方法返回代表查询结果的ResultSet对象。

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

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

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

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

使用executeUpdate(String sql)方法完成数据修改操作，示例操作：

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

使用executeQuery(String sql)方法完成数据查询操作，示例操作：

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

具体的代码实现

先在src下建立资源文件db.properties

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

1、提取工具类

package com.llx.study02;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

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 = JdbcUtils.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、编写增删改的方法，executeUpdate

增加

package com.llx.study02;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    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,'llx','123456','32618342@163.com','2020-01-01')";

            int num = st.executeUpdate(sql);
            if (num>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

3、查询executeQuery

package com.llx.study02;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from users where id = 1";

            rs = st.executeQuery(sql);

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露。使用or关键字会使SQL被拼接，哪怕是空的信息，都可以被执行成功。

SELECT * FROM users WHERE `Name` = '' or '1=1' AND `password`= '' or '1=1';

PreparedStatement对象

PreparedStatement 可以防止SQL注入。效率更好！

PreparedStatement 对象和Statement 对象的区别是 它会使用 ? 占位符代替参数，预编译sql，然后手动给参数赋值。

具体代码实现

插入：

package com.llx.study03;

import java.util.Date;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            // 使用 ? 占位符表示参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";    // 预编译SQL，先写SQL，不执行
            st = conn.prepareStatement(sql);
            // 手动给参数赋值
            st.setInt(1,5);
            st.setString(2,"hehe");
            st.setString(3,"123");
            st.setString(4,"12423@qq.com");
            // sql.Date   表示数据库的时间类
            // util.Date  表示java的时间类
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            int num = st.executeUpdate();
            if (num>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

查询：

package com.llx.study03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "select * from users where id = ?";

            st = conn.prepareStatement(sql);  // 预编译

            st.setInt(1,3);  // 传递参数

            rs = st.executeQuery();     // 执行

            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

防止SQL注入

PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符。假设其中存在转义字符，会被直接转义。

使用IDEA连接数据库

连接数据库

选择数据库

双击查看数据库

更新数据

编写sql代码

事务

代码实现

1. 开启事务 conn.setAutoCommit(false);
2. 一组业务执行完毕，提交事务
3. 可以在catch 语句中显示的定义 回滚语句，但默认失败就会回滚

package com.llx.study03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    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'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 业务完毕，提交事务
            conn.commit();
            System.out.println("成功");

        } catch (SQLException e) {
            // 如果失败，则默认回滚，则可以不写这段代码
            /*try {
                conn.rollback(); // 如果失败则回滚事务

            } catch (SQLException e1) {
                e1.printStackTrace();
            }*/
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

数据库连接池

由于数据库连接到执行完毕以及释放，是十分浪费系统资源的。

为了避免资源的浪费，可以使用池化技术。

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

开源数据源实现

DBCP
C3P0
Druid

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

DBCP

需要用到的 jar 包有：commons-dbcp-1.4 、 commons-pool-1.6

C3P0

需要用到的 jar 包有：c3p0-0.9.5.5、mchange-commons-java-0.2.19

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