Java 之 JDBC

最新推荐文章于 2022-08-22 10:14:47 发布

原创最新推荐文章于 2022-08-22 10:14:47 发布 · 256 阅读

CC 4.0 BY-SA版权

JDBC

JDBC(Java Database Connectivity)
Java 数据库连接规范(一套接口)由 Sun 公司提供的

JDBC 核心类

JDBC 四个核心类
DriverManager   创建连接
Connection      连接类
Statement       执行 sql 语句
ResultSet       结果集

JDBC 连接步骤

1.注册驱动
2.获取连接 Connection
3.获取 sql 语句的执行对象 Statement
4.执行 sql 语句,返回结果集 ResultSet
5.处理结果集
6.关闭资源

注册驱动

// 需要注意的是,这种注册方式,相当于注册了两遍
// 因为 Driver 类的内部的静态代码块已经注册了一遍
DriverManager.registerDriver(new Driver));

// 直接把该类加载到内存当中,参数是全限定类名(包名 + 类名)
Class.forName("com.mysql.jdbc.Driver");

获取连接对象

// url 是访问数据库连接地址
// myjdbc 为数据库名
String url = "jdbc:mysql://localhost:3306/myjdbc";

连接方式一:
// root 数据库用户名
// 123456 数据库密码
Connection connection = DriverManager.getConnection(url, "root", "123456");
连接方式二:
Properties info = new Properties();
// 添加用户名和密码
// 注意:键值别拼错了
info.setProperty("user", "root");
info.setProperty("password", "123456);
Connection connection = DriverManage.getConnection(url, info);
连接方式三:相当于使用了一个 get 请求,携带参数访问链接
String url = "jdbc:mysql://localhost:3306/myjdbc?user=root&password=123456";
Connection connection = DriverManager.getConnection(url);

// 获取执行 sql 语句的对象 Statement
Statement statement = connection.createStatement();
// 执行 sql 语句, 返回结果集
// 结果集中添加的索引要和查询语句中的字段对应
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
// 处理结果集
// 循环遍历结果集,输出结果
// 有记录 next() 方法返回 true, 没有返回 false
while(resultSet.next()) {
    // 打印数据
    // 注意:查询数据库时,索引从1开始
    System.out.print(resultSet.getObject(1) + " ");
    System.out.print(resultSet.getObject(2) + " ");
    System.out.print(resultSet.getObject(3) + " ");
    System.out.print(resultSet.getObject(4) + " ");
    System.out.println(resultSet.getObject(5) + " ");
    System.out.println("--------------------");
}
// 关闭资源
resultSet.close();
statement.close();
connection.close();

这里写图片描述

增删改查

@Test 
public void testInsert() 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 = "insert into users values(5, 'jianzhong', '123123', '123123@qq.com', '2018-03-22')";
    int row = statement.executeUpdate(sql);
    System.out.println(row);
    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?user=root&password=123456";
    Connection connection = DriverManager.getConnection(url);
    Statement statement = connection.createStatement();
    String sql = "update users set name='xuesheng'";
    int row = statement.executeUpdate(sql);
    System.out.println(row);
    if (row > 0) {
        System.out.println("更新成功");
    }
    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);
    System.out.println(row);
    if (row > 0) {
        System.out.println("删除成功");
    }
    statement.close();
    connection.close();
}

@Test 
public void testSelect() 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 = "select id, name, email from users";
    ResultSet resultSet = statement.executeQuery(sql);
    // 处理结果集
    while (resultSet.next()) {
        // 可以直接填字段名称
        System.out.print(resultSet.getObject("id") + " ");
        System.out.print(resultSet.getObject("name") + " ");
        System.out.println(resultSet.getObject("email") + " ");
    }
    resultSet.close();
    statement.close();
    connection.close();
}

连接数据库的异常处理

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DemoException {
    public static void main(String[] args) {
        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> arrayList = new ArrayList<>();
            while(resultSet.next()) {
                // 创建 User 对象
                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"));
                // 放入集合中
                arrayList.add(user);
            }
            // 遍历查看
            for (User user : arrayList) {
                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;
            }
        }
    }
}

将 JDBC 封装为一个工具类方法使用
创建 dbinfo.properties 文件,作为配置文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myjdbc
user=root
password=123456

JDBC 工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;

public class JDBCUtil {
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    // 使用静态代码块加载驱动,读取配置文件
    static {
        // 第一种读取配置文件的方法:利用集合读取文件
        Properties properties = new Properties();
        FileInputStream fileInputStream;
        try {
            fileInputStream = new FileInputStream("src/dbinfo.properties");
            properties.load(fileInputStream);
            // 读文件
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 第二种读取配置文件的方法:使用系统类来读取配置文件
        ResourceBundle resourceBundle = ResourceBundle.getBundle("dbinfo");
        // 获取文件中的数据
        driverClass = resourceBundle.getString("driverClass");
        url = resourceBundle.getString("url");
        user = resourceBundle.getString("user");
        password = resourceBundle.getString("password");


        // 让驱动类只加载一次
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    // 获取数据库连接的方法
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        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;
        }
    }
}

测试 JDBC 工具类是否成功
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.junit.Test;

public class TestJDBCUtil {
    @Test
    public void testSelect() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 获取连接
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users";
            resultSet = statement.executeQuery(sql);
            // 处理结果集(把数据的记录封装到对象中)
            ArrayList<User> arrayList = new ArrayList<>();
            while(resultSet.next()) {
                // 创建 User 对象
                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"));
                // 放入集合中
                arrayList.add(user);
            }
            // 遍历查看
            for (User user : arrayList) {
                System.out.println(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(resultSet, statement, connection);
        }
    }
}

sql 语句注入问题

import java.util.Scanner;

public class Login {
    public static void main(String[] args) {
        // 接收用户输入的账号和密码
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入账号:");
        String name = scanner.nextLine();
        System.out.println("请输入密码:");
        String password = scanner.nextLine();
        // 调用查询方法
        DoLogin doLogin = new DoLogin();
        User user = doLogin.findUser(name, password);
        if (user != null) {
            System.out.println(user);
        } else {
            System.out.println("登录失败");
        }
        scanner.close();
    }
}


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

public class DoLogin {
    // 通过用户名密码查找用户
    public User findUser(String name, String password) {
        String sql = "select * from users where name = '" + name + "' and password = '" + password + "'";
        // 查询数据库
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            connection = JDBCUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            // 只返回一条数据
            System.out.println(sql);
            if (resultSet.next()) {
                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"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(resultSet, statement, connection);
        }
        return user;
    }
}

sql 语句注入问题,添加恒成立的条件
产生的结果使输入 sql 的语句成为 select * from users;
而不是所想要的单人用户登录获取信息

这里写图片描述

解决方法
public User findUser(String name, String password) {
    // ? 占位符
    String sql = "select * from users where name = ? and password = ?";
    // 查询数据库
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    User user = null;
    try {
        connection = JDBCUtil.getConnection();
        // 对 sql 语句进行预编译
        preparedStatement = connection.prepareStatement(sql);
        // 给 sql 语句的占位符进行赋值
        // 参数1填索引, sql 语句中问号索引,
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, password);
        resultSet = preparedStatement.executeQuery();
        // 只返回一条数据
        System.out.println(sql);
        if (resultSet.next()) {
            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"));
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeAll(resultSet, preparedStatement, connection);
    }
    return user;
}

这里写图片描述

只有在输入正确信息时,才能对应登录获取信息

这里写图片描述
http://blog.youkuaiyun.com/huzongnan/article/list