JDBC应用（一）

JDBC：

全称：Java DataBase Connectivity
概论：Java数据库连接规范（一套接口）

JDBC四个核心对象：

1.DriverManager:用于注册数据库连接
2.Connection:与数据连接对象
3.Statement:操作数据库SQL语句对象
4.ResultSet:结果集或一张虚拟表

JDBC连接步骤：

1.注册驱动：DriverManager
2.获取连接：Connection
3.获取SQL语句执行对象：Statement
4.执行SQL语句返回结果集：ResultSet
5.处理结果集
6.关闭资源：close()

SQL语句注入问题：

产生原因：SQL语句使用了字符拼接。

如：statement对象执行该语句：
select * from users where name='" + name + "' and password='" + password + "';

password项输入：xx or '1'='1'时，将以：
select * from users where name='2222asd' and password='aaassd' or '1'='1' 运行

解决方法：
select * from users where name=？ and password=？;
? 占位符
给SQL语句的占位符，进行赋值
参数1 填索引 SQL语句中问号索引

用statement的子类PreparedStatement

注册连接的两种方法：

方法一：

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

//  该方法不建议以使用，因为会导致驱动被注册两次（Driver类内部的静态代码块，已经注册了一遍），如下是Mysql驱动jar包中Driver类
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    // ~ Static fields/initializers
    // ---------------------------------------------
    //
    // Register ourselves with the DriverManager
    //
    static {
         try {
             java.sql.DriverManager.registerDriver(new Driver());
         } catch (SQLException E) {
             throw new RuntimeException("Can't register driver!");
         }
    }
    // ~ Constructors
    // -----------------------------------------------------------
    /**
     * Construct a new driver and register it with DriverManager
     *
     * @throws SQLException
     *             if a database error occurs.
     */
    public Driver() throws SQLException {
         // Required for Class.forName().newInstance()
    }

方法二：

//  直接把该类加载到内存当中
Class.forName("com.mysql.jdbc.Driver");

获取连接对象三种方法：

方法一：

String url = "jdbc:mysql://localhost:3306/myjdbc";
Connection connection = DriverManager.getConnection(url, "root", "123456");

方法二：

//  运用Properties集合添加用户名和密码
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "123456");
Connection connection = DriverManager.getConnection(url, info);

方法三：

//  将用户名和密码直接写入数据访问地址中
String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
Connection connection = DriverManager.getConnection(url);

获取SQL语句执行对象：

//  SQL实现添加、修改、删除时，使用
executeUpdate(String sql) 返回int类型
//  SQL实现查询时，使用：
executeQuery(String sql) 返回ResultSet对象类型

处理结果集基础方法：

提供一个指针，默认从表的字段栏开始。调用一次next()，指针向下移动一行。提供一些get方法。
//  根据序号取值，索引从1开始
Object getObject(int columnIndex); 
//  根据列名取值
Object getObject(String ColomnName); 

举例：

添加测试：

@Test
public void testInsert() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc";
    Connection connection = DriverManager.getConnection(url, "root", "123456");
    Statement statement = connection.createStatement();
    String sql = "insert into users values (5,'zhouqi','123','111@qq.com','1997-02-15')";
    //  受影响的行数
    int row = statement.executeUpdate(sql);
    if (row > 0) {
        System.out.println("插入成功");
    }
    statement.close();
    connection.close();
}

更新测试：

@Test
public void testUpdate() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc";
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "123456");
    Connection connection = DriverManager.getConnection(url, info);
    Statement statement = connection.createStatement();
    String sql = "update users set name='sunba' where id=5";
    int row = statement.executeUpdate(sql);
    if (row > 0) {
        System.out.println("更新成功" + row + "行");
    }
    statement.close();
    connection.close();
}

删除测试：

@Test
public void testDelete() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
    Connection connection = DriverManager.getConnection(url);
    Statement statement = connection.createStatement();
    String sql = "delete from users where id=5";
    int row = statement.executeUpdate(sql);
    if (row > 0) {
        System.out.println("删除成功" + row + "行");
    }
    statement.close();
    connection.close();
}

查询测试：

@Test
public void testSelect() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/myjdbc";
    Connection connection = DriverManager.getConnection(url, "root", "123456");
    Statement statement = connection.createStatement();
    String sql = "select id,name,email from users";
    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()) {
        System.out.println(resultSet.getObject("id"));
        System.out.println(resultSet.getObject("name"));
        System.out.println(resultSet.getObject("email"));
    }
    resultSet.close();
    statement.close();
    connection.close();
}

处理结果集升级方法：

思路：将数据库表中数据封装入对象，再将对象存入集合进行遍历。

举例：

@Test
public void testSelect(){
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/myjdbc";
        connection = DriverManager.getConnection(url, "root", "123456");
        statement = connection.createStatement();
        String sql = "select * from users";
        resultSet = statement.executeQuery(sql);
        ArrayList<User> list = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setName(resultSet.getString("name"));
            user.setPassword(resultSet.getString("password"));
            user.setEmail(resultSet.getString("email"));
            user.setBirthday(resultSet.getDate("birthday"));
            list.add(user);
        }
        for (User user : list) {
            System.out.println(user);
        }
    } catch (ClassNotFoundException e) {
        throw new RuntimeException("驱动加载失败");
    } catch (SQLException e) {
        throw new RuntimeException("获取连接失败");
    } finally {
        //  关闭资源，非空判断，防止空指针出现
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException("关闭失败");
            }
            //  加快系统回收速度
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException("关闭失败");
            }
            //  加快系统回收速度
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException("关闭失败");
            }
            //  加快系统回收速度
            connection = null;
        }
    }
}

封装方法使用：

配置文件名：dbinfo.properties
文件内容：
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myjdbc
user=root
password=123456

public class JDBCUtil {
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    //  创建驱动方法（使用properties集合实现）
    //  静态代码块好处：
    //  1.只加载一次；
    //  2.随着类的加载而加载.
    static {
        Properties properties = new Properties();
        try {
            FileInputStream fis = new FileInputStream("src/dbinfo.properties");
            properties.load(fis);
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    static {
        ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
        driverClass = rb.getString("driverClass");
        url = rb.getString("url");
        user = rb.getString("user");
        password = rb.getString("password");
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static void closeAll(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException("关闭失败");
            }
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException("关闭失败");
            }
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException("关闭失败");
            }
            connection = null;
        }
    }
}