在我们做开发过程,我们接触最多的是一些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注入