通过 Java 语言封装的 JDBC 工具可以很方便的实现操作数据库的功能，使用过程中，有时候需要用到 JDBC 本身的数据，比如链接的 URL、用户名、密码、查询过程中的参数和查询结果等，这些都称为 JDBC 的元数据。
JDBC 中的元数据类有三种：
- DataBaseMetaData: 获取 Connection 的信息，比如用户名、链接端口等；
- ParameterMetaData：获取查询语句参数的信息，比如参数个数、名称、数据类型等；
- ResultSetMetaData：获取查询结果的信息，如结果个数、数据表列名、列的数据类型等；
下面是具体实现代码：
DataBaseMetaData 获取链接信息
public void testDatabaseMetaData() throws Exception{
Connection connection = JDBCUtils.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
String url = metaData.getURL();
//root@localhost 只能本地读写 root@% 通配符:允许外界访问；
String username = metaData.getUserName();
String productName = metaData.getDatabaseProductName();
String productVersion = metaData.getDatabaseProductVersion();
String driverName = metaData.getDriverName();
String driverVersion = metaData.getDriverVersion();
boolean readOnly = metaData.isReadOnly();
System.out.println("url: " + url);
System.out.println("username: " + username);
System.out.println("productName: " + productName);
System.out.println("productVersion: " + productVersion);
System.out.println("driverName: " + driverName);
System.out.println("driverVersion: " + driverVersion);
System.out.println("driverVersion: " + driverVersion);
System.out.println("readOnly: " + readOnly);
}
可以获取到如下结果：
url: jdbc:mysql://localhost:3306/day16_transaction
username: root@localhost
productName: MySQL
productVersion: 8.0.11
driverName: MySQL Connector Java
driverVersion: mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e )
driverVersion: mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e )
readOnly: false
通过元数据实现对 JDBC 操作简化封装的框架
核心类 DBAssist
public class DBAssist {
public void update(String sql, Object[] args){
Connection conn = JDBCUtils.getConnection();
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
ParameterMetaData pmd = stmt.getParameterMetaData();
int paramCount = pmd.getParameterCount();
if (paramCount > 0){
if (args == null){
throw new IllegalArgumentException("参数不能为空");
}
if (paramCount != args.length){
throw new IllegalArgumentException("参数长度不匹配");
}
for (int i = 0; i < args.length; i++){
stmt.setObject(i + 1, args[i]);
}
}
stmt.executeUpdate();
}catch (Exception e){
e.printStackTrace();
throw new MyRunTimeException();
}finally {
release(null, stmt, conn);
}
}
public Object query(String sql, Object[] args, ResultSetHandle rsh){
Connection conn = JDBCUtils.getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(sql);
ParameterMetaData pmd = stmt.getParameterMetaData();
int paramCount = pmd.getParameterCount();
if (paramCount > 0){
if (args == null){
throw new IllegalArgumentException("参数不能为空");
}
if (paramCount != args.length){
throw new IllegalArgumentException("参数长度不匹配");
}
for (int i = 0; i < args.length; i++){
stmt.setObject(i + 1, args[i]);
}
}
//结果
rs = stmt.executeQuery();
// ResultSetMetaData rsmd = rs.getMetaData();
return rsh.handle(rs);
}catch (Exception e){
e.printStackTrace();
throw new MyRunTimeException();
}finally {
release(rs, stmt, conn);
}
}
public void release(ResultSet rs, Statement stmt, Connection conn){
if (rs!=null){
try {
rs.close();
}catch (Exception e){
e.printStackTrace();
}
rs = null;
}
if (stmt!=null){
try {
stmt.close();
}catch (Exception e){
e.printStackTrace();
}
stmt = null;
}
if (conn!=null){
try {
conn.close();
}catch (Exception e){
e.printStackTrace();
}
conn = null;
}
}
}
辅助类 ：接口扩展类 ResultSetHandle 和 异常类 MyRunTimeException:
public interface ResultSetHandle {
Object handle(ResultSet rs) throws IllegalAccessException, InstantiationException;
}
public class MyRunTimeException extends RuntimeException {
public MyRunTimeException(){
}
public MyRunTimeException(String msg){
super(msg);
}
public MyRunTimeException(Throwable cause){
super(cause);
}
public MyRunTimeException(String msg, Throwable cause){
super(msg, cause);
}
}
使用时的接口实现类：
public class BeanListHandler implements ResultSetHandle {
private Class clazz;
public BeanListHandler(Class clazz){
this.clazz = clazz;
}
@Override
public Object handle(ResultSet rs) throws IllegalAccessException, InstantiationException {
ArrayList list = new ArrayList();
try {
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()){
//实例化bean
Object bean = clazz.newInstance();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++){
Object value = rs.getObject(i);
String columnName = rsmd.getColumnName(i);
//声明方法
Field declaredFiled = clazz.getDeclaredField(columnName);
declaredFiled.set(bean, value);
}
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
throw new MyRunTimeException(e);
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
return list;
}
}
public class BeanHandler implements ResultSetHandle {
private Class clazz;
public BeanHandler(Class clazz){
this.clazz = clazz;
}
@Override
public Object handle(ResultSet rs) throws IllegalAccessException, InstantiationException {
try {
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()){
//实例化bean
Object bean = clazz.newInstance();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++){
Object value = rs.getObject(i);
String columnName = rsmd.getColumnName(i);
//声明方法
Field declaredFiled = clazz.getDeclaredField(columnName);
declaredFiled.set(bean, value);
}
return bean;
}
} catch (SQLException e) {
e.printStackTrace();
throw new MyRunTimeException(e);
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
return null;
}
}
存放实例 Bean ：
public class Customer {
int id ;
String name;
Double money;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
@Override
public String toString() {
return "id = " + id + ", name: "+name + ", money = " + money;
}
}
使用：
public class SomeDao {
private DBAssist dbAssist = new DBAssist(null);
@Test
public void insert(){
dbAssist.update("insert into account values(?, ?, ?)", new Object[]{20,"lili",27});
}
@Test
public void delete(){
dbAssist.update("delete from account where id=?", new Object[]{20});
}
@Test
public void update(){
dbAssist.update("update account set money=? where id = ?", new Object[]{20, 1});
}
@Test
public void getAll(){
select();
}
@Test
public void getOne(){
selectOne();
}
public List selectOne(){
//TODO AAA
Customer customer = (Customer)dbAssist.query("select * from account where id = 1", null, new BeanHandler(Customer.class));
System.out.println(customer);
return null;
}
public List select(){
//TODO AAA
ArrayList<Customer> list = (ArrayList<Customer>)dbAssist.query("select * from account", null, new BeanListHandler(Customer.class));
for (Customer customer: list
) {
System.out.println(customer);
}
return null;
}
}
网友评论