JDBC详述

在我们做开发过程，我们接触最多的是一些DAO框架，如：mybatis,hibernate,ibatis等，其实他们的底层都是基于jdbc来实现的

我们分几个模块来了解jdbc:

1、获取库/表/字段信息

2、事务操作

3、Statement / PreparedStatement

获取库/表/字段信息

定义数据库实体： 

@Data
@Accessors(chain = true)
public class Database {
    private String name;
    private List<Table> tableList;
}

定义表实体：  

@Data
@Accessors(chain = true)
public class Table {

    private List<Column> columnList;

    private String tableCat;
    private String tableSchem;
    private String tableName;
    private String tableType;
    private String tableRemarks;
    private String typeCat;
    private String typeSchem;
    private String tableTypeName;
    private String selfReferencingColName;
    private String refGeneration;

}

定义字段实体：   

@Data
@Accessors(chain = true)
public class Column {
    private String name;
    private String type;
    private String dataType;
    private String columnSize;
    private String remarks;
    private String isNullable;
    private String isAutoincrement;
}

测试类： 

public class JdbcTest {
    public static void main(String[] args) throws Exception{
        Connection conn = DriverManager.getConnection("jdbc:mysql://ip:port/databaseName","username","password");
        List<Database> databaseList =  getMetaData(conn);
    }
    public  static List<Database>  getMetaData( Connection conn ) throws Exception{
        try {
            String schema = conn.getSchema();
            String catalog = conn.getCatalog();
            DatabaseMetaData databaseMetaData = conn.getMetaData();

            ResultSet resultSet1 = databaseMetaData.getCatalogs();
            List<Database> databaseList = new ArrayList<>();
            while(resultSet1.next()){
                Database database = new Database();
                String catalogName = resultSet1.getString(1);
                database.setName(catalogName);
                ResultSet tablesRs = databaseMetaData.getTables(catalogName, null, null, new String[]{"TABLE"});
                List<Table> tableList = new ArrayList<>();
                while(tablesRs.next()){
                    Table table = new Table();
                    String tableCat = tablesRs.getString("TABLE_CAT");
                    String tableSchem = tablesRs.getString("TABLE_SCHEM");
                    String tableName = tablesRs.getString("TABLE_NAME");
                    String tableType = tablesRs.getString("TABLE_TYPE");
                    String tableRemarks = tablesRs.getString("REMARKS");
                    String typeCat = tablesRs.getString("TYPE_CAT");
                    String typeSchem = tablesRs.getString("TYPE_SCHEM");
                    String tableTypeName = tablesRs.getString("TYPE_NAME");
                    String selfReferencingColName = tablesRs.getString("SELF_REFERENCING_COL_NAME");
                    String refGeneration = tablesRs.getString("REF_GENERATION");
                    table.setTableName(tableName).setRefGeneration(refGeneration).setTableCat(tableCat).setTableRemarks(tableRemarks)
                    .setTableSchem(tableSchem).setTableType(tableType).setTypeCat(typeCat).setTypeSchem(typeSchem).setTableTypeName(tableTypeName)
                    .setSelfReferencingColName(selfReferencingColName);

                    ResultSet columnRs = databaseMetaData.getColumns(catalogName, null, tableName, null);
                    List<Column> columnList = new ArrayList<>();
                    while(columnRs.next()){
                        Column column = new Column();
                        String colName = columnRs.getString("COLUMN_NAME");// 列名
                        String typeName = columnRs.getString("TYPE_NAME");// 类型名称
                        String dataType = columnRs.getString("DATA_TYPE");// 类型名称
                        String columnSize = columnRs.getString("COLUMN_SIZE");// 类型名称
                        String columnRemarks = columnRs.getString("REMARKS");// 类型名称
                        String isNullable = columnRs.getString("IS_NULLABLE");// 类型名称
                        String isAutoincrement = columnRs.getString("IS_AUTOINCREMENT");// 类型名称

                        column.setName(colName).setType(typeName).setColumnSize(columnSize).setDataType(dataType).setRemarks(columnRemarks)
                                .setIsAutoincrement(isAutoincrement).setIsNullable(isNullable);

                        columnList.add(column);
                    }
                    table.setColumnList(columnList);
                    tableList.add(table);
                }
                database.setTableList(tableList);
                databaseList.add(database);
            }
            databaseList = databaseList.stream().filter(x->x.getName().equals("bigdata_bdp_dev")).collect(Collectors.toList());
            return databaseList;
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                if (conn != null) {
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }

这里要注意的是catalog和schema的区别，其实在数据库规范中，schema是数据库的概念，catalog是在数据库之上的一个目录，比如我们去查一张表的数据

select * from catalog.schema.table这样的层次去查找

但是，在mysql中，并没有catalog.schema这样的层次，一般只需要select * from catalog.table就可以了，大家可以去看下postgresql，它的命名空间就是catalog.schema就是这样的层次

事务操作

    public  static void execute() throws Exception{
        Connection conn = DriverManager.getConnection("jdbc:mysql://ip:port/database",username,password);

        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //设置事务非自动提交
            conn.setAutoCommit(false);
            //设置当前会话的事务隔离级别，只对当前session有效
            conn.setTransactionIsolation(Isolation.REPEATABLE_READ.value());
            statement = conn.createStatement();

            statement.execute("UPDATE yc_test_copy SET `name` = 'vvvv' where name = 1");

            statement.execute("UPDATE yc_test_cop1y SET `name` = 'vvvv' where name = 1");
            //事务提交
            conn.commit();
        }catch(Exception e){
            //事务回滚
            conn.rollback();
            e.printStackTrace();
        }finally{
            try {
                if (conn != null) {
                    conn.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

其实像hibernate和mybatis的事务管理器也是基于上述来进行实现的

Statement / PreparedStatement

Statement的几种用法：

   public  static Object execute(Connection conn) throws Exception{
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            //1、INSERT/UPDATE/DELETE
            int updateCount = statement.executeUpdate("UPDATE yc_test_copy SET `name` = 'vvvv' where name = 1");

            //2、SELECT
            ResultSet rs = statement.executeQuery("SELECT * FROM `yc_test_copy`");

            //3、INSERT/UPDATE/DELETE/SELECT
            Boolean flag = statement.execute("UPDATE yc_test_copy SET `name` = 'vvvvv' where name ='vvvv'");
            if(flag){
                //SELECT
                return statement.getResultSet();
            }else{
                //INSERT/UPDATE/DELETE
                return statement.getUpdateCount();
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                if (conn != null) {
                    conn.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }

 PreparedStatement的几种用法：

public  static Object preparedStatementExecute(Connection conn) throws Exception{
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {

            //SELECT * FROM `yc_test_copy`
            statement = conn.prepareStatement("UPDATE yc_test_copy SET `name` = 'vvvvv' where name = ?");
            statement.setString(1,"vvvv");
            //SELECT
            ResultSet rs = statement.executeQuery();
            
           //INSERT/UPDATE/DELETE
            int updateCount = statement.executeUpdate();

            boolean flag = statement.execute();
            if(flag){
                //SELECT
                return statement.getResultSet();
            }else{
                //INSERT/UPDATE/DELETE
                return statement.getUpdateCount();
            }

        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                if (conn != null) {
                    conn.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }

可见PreparedStatement 可以对sql注入参数，防止参数被当做sql来进行拼接，防止sql注入，而Statement只能在sql里面拼接参数，会出现sql注入