JDBC第一篇【JDBC入门】

什么是JDBC?

答：JDBC全称时Java Data Base Connectivity，是执行sql语句的Java Api。

使用JDBC的步骤：

1. 导入数据库驱动包
2. 注册驱动
3. 获取数据库连接对象
4. 获取执行Sql语句的对象
5. 执行Sql
6. 处理结果集
7. 关闭连接

    public void JDBC() {
        try {
            //注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取数据库连接对象
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC", "root", "12345678");
            //获取执行Sql语句的对象
            Statement statement = connection.createStatement();
            //执行Sql
            ResultSet resultSet = statement.executeQuery("select  * from user where id = 1");
            //遍历结果集
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1));
                System.out.println(resultSet.getString(2));
                System.out.println(resultSet.getString(3));
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            /*
            关闭资源时，先使用的后关闭
            关闭前判断是否为null
            */
            try {
                if (resultSet != null)
                    resultSet.close();
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

防止sql注入

改造：

    String sql = "select  * from user  where id = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setInt(1, 1);
    resultSet = preparedStatement.executeQuery();

setAutoCommit

connection.setAutoCommit(true) 默认为自动提交，每执行一个update ,delete或者insert的时候都会自动提交到数据库，无法回滚事务。

connection.setAutoCommit(false) 关闭自动提交，也就是开启事务,update ,delete或者insert需要手动提交

JDBCUtil

public class JDBCUtil {
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/myvue?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
        String username = "root";
        String password = "12345678";
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static Statement getStatement() {
        Connection connection = getConnection();
        try {
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return statement;
    }

    public static ResultSet getResultSet(String sql){
     statement = getStatement();
        try {
            resultSet = statement.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    public static void close() {
        try {
            if (resultSet != null)
                resultSet.close();
            if (statement != null)
                statement.close();
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}