JDBC介绍

JDBC Java Database Contectivity，它是可以执行SQL语句的Java API。

执行步骤

1. 加载数据库驱动
2. 建立数据库连接
3. 创建一个Statement对象
4. 执行查询
5. 处理ResultSet
6. 关闭连接

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //加载mysql驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            String url = "jdbc:mysql://localhost:3306/testdb?characterEncoding=utf8&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";
            String user = "sa";
            String password = "admin";
            //建立数据库连接
            connection = DriverManager.getConnection(url, user, password);
            //创建一个Statement对象
            statement = connection.createStatement();
            String sql = "select * from userinfo";
            //执行查询
            resultSet = statement.executeQuery(sql);
            //处理ResultSet结果集
            while (resultSet.next()) {
                String userId = resultSet.getString("UserId");
                String userName = resultSet.getString("UserName");
                String userSex = resultSet.getString("UserSex");
                String userAge = resultSet.getString("UserAge");
                String userAddress = resultSet.getString("UserAddress");
                System.out.println(userId + " " + userName + " " + userSex + " " + userAge + " " + userAddress);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

Connection对象

客户端与数据库所有的交互都是通过Connection来完成的。

常用的方法：

• createStatement()   创建向数据库发送sql的Statement对象
• prepareStatement(sql)  创建向数据库发送预编译sql的PrepareStatement对象
• prepareCall(sql)   创建执行存储过程的CallableStatement对象
• setAutoCommit(boolean autoCommit)  设置事务自动提交
• commit()   提交事务
• rollback()   回滚事务

Statement对象

Statement对象用于向数据库发送sql语句，对数据库的增删改查都可以通过此对象发送sql语句完成。

• executeQuery(String sql)  查询
• executeupdate(String sql)  增删改
• execute(String sql)   任意sql语句都可以，但是目标不明确
• addBatch(String sql)  把多条sql语句放进同一个批处理中
• executeBatch()  向数据库发送一批sql语句执行

ResultSet对象

ResultSet对象代表Sql语句的执行结果，当Statement对象执行executeQuery时，会返回一个ResultSet对象，ResultSet对象维护了一个数据行的游标【简单理解成指针】，调用ResultSet.next()方法，可以让游标指向具体的数据行，获取该行的数据。

常用方法：

• getObject(String columnName)  获取任意类型的数据
• getString(String columnName)   获取指定类型的数据
• next()   对结果集进行滚动查看的方法
• previous() 
• absolute(int low)
• beforeFirst()
• afterLast()

写一个简单的工具类

    static String driver = null;
    static String url = null;
    static String user = null;
    static String password = null;

  static {
        try {
/*            InputStream resourceAsStream = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");*/
            ResourceBundle db = ResourceBundle.getBundle("db");
            driver = db.getString("driver");
            url = db.getString("url");
            user = db.getString("user");
            password = db.getString("password");
            Class.forName(driver);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

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

 public static void Handle() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            String sql = "select * from userinfo";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String userId = resultSet.getString("UserId");
                String userName = resultSet.getString("UserName");
                String userSex = resultSet.getString("UserSex");
                String userAge = resultSet.getString("UserAge");
                String userAddress = resultSet.getString("UserAddress");
                System.out.println(userId + " " + userName + " " + userSex + " " + userAge + " " + userAddress);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet, statement, connection);
        }
}

 注意配置的文件的位置