依赖包:
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.1.0.7</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
由于只是做小工具,因此不需要用连接池了
查询工具类QueryRunnerDao :
public class QueryRunnerDao {
private final QueryRunner runner = new QueryRunner();
private Connection connection;
public QueryRunnerDao() {
getConn();
}
public void getConn() {
try {
connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "xxx");
} catch (SQLException e) {
e.printStackTrace();
}
}
public <T> List<T> queryByBeanList(String sql, Class<T> c, Object... params) throws SQLException {
List<T> rs = runner.query(connection, sql,
new BeanListHandler<T>(c), params);
DbUtils.close(connection);
return rs;
}
}
UserTabColumn:
package com.my.db.util;
public class UserTabColumn {
private String columnName;
private String dataType;
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
public String getDataType() {
return dataType;
}
public void setDataType(String dataType) {
this.dataType = dataType;
}
}
获取字段名及字段类型:TableUtils
import java.sql.SQLException;
import java.util.List;
public class TableUtils {
public List<UserTabColumn> queryDBColumn(String tableName) {
try {
return QueryRunnerDao.getInstance().queryByBeanList("select column_name,data_type from USER_TAB_COLUMNS t where table_name=upper(?)",
UserTabColumn.class, tableName);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void main(String[] args) {
List<UserTabColumn> list = new TableUtils().queryDBColumn("test_table");
list.stream().forEach(userTabColumn -> {
System.out.println(userTabColumn.getColumnName()+","+userTabColumn.getDataType());
});
}
}
运行后发现有list数据,但里面的内容是空的,原因是数据库字段是下划线的,javaBean是驼峰的。
解决方法:BeanListHandler 改成下面这个去创建
public <T> List<T> queryByBeanList(String sql, Class<T> c, Object... params) throws SQLException {
//new BasicRowProcessor(new GenerousBeanProcessor()) 的作用就是转换器,GenerousBeanProcessor可以实现数据库与java驼峰转换
List<T> rs = runner.query(connection, sql,
new BeanListHandler<T>(c, new BasicRowProcessor(new GenerousBeanProcessor())), params);
DbUtils.close(connection);
return rs;
}