JDBC 通过ResultSet执行查询操作（4）

// 表结构
create database mydb; # 创建数据库

use mydb；#使用数据库

create table customer_table( #创建分类表
id int PRIMARY KEY AUTO_INCREMENT,  
name varchar(100),
age varchar(100),
birth DATE
);

class MyTest {
    // 关闭数据库资源(注意关闭要从里到外)
    public void releaseDB(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    // 获取数据库连接
    public Connection myConnection() throws Exception {
        String driverClass = "com.mysql.jdbc.Driver";
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String user = "user";
        String password = "password";

        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
        return connection;
    }
    public void  testResultSet(){
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try{
            // 1. 获得Connection
            conn = myConnection();
            // 2. 获取Statement
            statement = conn.createStatement();
            // 3. 准备SQL
            String sql = "select id,name,email,birth from customers where id = 4";
            // 4. 执行查询，得到ResultSet
            rs = statement.executeQuery(sql);
            // 5. 处理ResultSet
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString("name");
                String email = rs.getString(3);
                Date birth = rs.getData(4);
                System.out.println(id);
                System.out.println(name);
                System.out.println(email);
                System.out.println(birth);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            releaseDB.release(rs,statement,conn);
        }
    }

    // 可以执行的sql：update、insert、delete
    public void myStatement() throws SQLException {
        Connection conn = null;
        Statement statement = null;
        try {
            // 1. 获取数据库连接
            conn = myConnection();
            // 2. 准备执行的SQL
            String sql = "Insert into table(name,email,birth) values('xyz','xyz@123.com','xxxx-xx-xx')";
            // 3. 执行SQL（注意执行的SQL可以是INSERT、UPDATE或DELETE。但不能是SELECT）
            // 1）获取操作SQL语句的Statement对象
            // 通过调用Connection的createStatement()方法来获取
            statement = conn.createStatement();
            // 2）调用Statement对象的executeUpdate(sql)执行SQL语句进行插入
            statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

// 方法说明：获取指定列的名称
getColumnName(int column)：
// 方法说明：返回当前ResultSet对象中的列数
getColumnCount()：
// 获取用于打印输出和显示的指定列的建议标题。
String getColumnLabel(int column)

public void myTest01() throws ClassNotFoundException, SQLException {
    String driverClass = "com.mysql.jdbc.Driver";
    String jdbcUrl = null;
    String user = null;
    String password = null;
    Class.forName(driverClass);
    Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
    String sql = "SELECT flow_id flowId, type, id_card idCard, "
                + "exam_card examCard, student_name studentName, "
                + "location, grade " + "FROM examstudent WHERE flow_id = ?";
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setInt(1, 5);
    resultSet = preparedStatement.executeQuery();
    ResultSetMetaData rsmd = resultSet.getMetaData();
    String columnName = rsmd.getColumnName(1); // 获取指定列的名称
    int columCount = rsmd.getColumnCount();// 返回当前ResultSet对象中的列数
    String columnLabel= rsmd.getColumnLabel(1);//获取用于打印输出和显示的指定列的建议标题。
    }