自定义JDBC框架

连接数据库的工具类(数据源)：

DBCPUtils:

package com.heima.utils;import java.awt.image.DataBuffer;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSourceFactory;public class DBCPUtils {private static DataSource ds ;static {//将配置文件加载进来InputStream in = DBCPUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties") ;Properties props = new Properties() ;try {props.load(in) ;ds = BasicDataSourceFactory.createDataSource(props) ;} catch (Exception e) {throw new RuntimeException("服务器忙") ;}}//提供获取连接的方法public static Connection getConnection(){try {return ds.getConnection() ;} catch (SQLException e) {throw new RuntimeException("服务器忙") ;}}}

dbcpconfig.properties:

自定义连接数据库的框架:

package com.heima.DBAssist;import java.sql.Connection;import java.sql.ParameterMetaData;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.heima.handler.ResultHandler;import com.heima.handler.impl.ResultSetHandlerImpl;import com.heima.handler.impl.ResultSetListenerHandlerImpl;import com.heima.utils.DBCPUtils;//自定义框架public class DBAsssist {// 执行添改删语句public boolean update(String sql, Object... params) {//sql：要执行的sql语句，params：sql语句中的参数（即？的值）// 拿到连接对象Connection conn = DBCPUtils.getConnection();int t = 0;try {// 创建预处理命令对象PreparedStatement pstmt = conn.prepareStatement(sql);// 对?进行赋值// 获取ParameterMetaData对象ParameterMetaData pmd = pstmt.getParameterMetaData();// 拿到?的个数int n = pmd.getParameterCount();if (n > 0) {// sql语句里有?号if (params == null || params.length != n) {throw new RuntimeException("参数的个数不匹配");}// 依次给每个?赋值for (int i = 0; i < n; i++) {pstmt.setObject(i + 1, params[i]);}}t = pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {try {conn.close(); // 还回池中了} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return t > 0 ? true : false;}// 执行查询语句public Object query(String sql,Class clazz, Object... params) {// 拿到连接对象Connection conn = DBCPUtils.getConnection();try {// 创建预处理命令对象PreparedStatement pstmt = conn.prepareStatement(sql);// 对?进行赋值// 获取ParameterMetaData对象ParameterMetaData pmd = pstmt.getParameterMetaData();// 拿到?的个数int n = pmd.getParameterCount();if (n > 0) {// sql语句里有?号if (params == null || params.length != n) {throw new RuntimeException("参数的个数不匹配");}// 依次给每个?赋值for (int i = 0; i < n; i++) {pstmt.setObject(i + 1, params[i]);}}ResultSet rs = pstmt.executeQuery();return new ResultSetListenerHandlerImpl().handler(rs, clazz) ;} catch (SQLException e) {throw new RuntimeException() ;} finally {try {conn.close(); // 还回池中了} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

在执行查询功能时，查询到的结果应该封装到一个javabean中，编写一个类，实现把结果集中的数据封装到bean中：

ResultHandler.java:

package com.heima.handler;import java.sql.ResultSet;//此时的需求是把结果集封装到javabean中，用该接口来完成此功能public interface ResultHandler {//返回封装的对象（结果集）/** *  * @param rs 要封装的结果集 * @param clazz  封装到的那个对象 * @return  封装的对象 */public Object handler(ResultSet rs,Class clazz) ;}

handler.impl:

ResultSetHandlerImpl.java(只针对一条记录的)：

package com.heima.handler.impl;import java.lang.reflect.Field;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import com.heima.handler.ResultHandler;//只适用于结果集只有一条记录的情况//对象的属性名和表中的字段名应当一致public class ResultSetHandlerImpl implements ResultHandler {@Overridepublic Object handler(ResultSet rs, Class clazz) {//结果集的属性名字要和封装到的那个对象的bean里面的属性名字一样//拿到结果集的元数据对象try {if(rs.next()){ResultSetMetaData rsmd = rs.getMetaData() ;//拿到共有多少列，即拿到列名，找到对应的类当中的对象，依次调用set方法，把值设进去int columnCount = rsmd.getColumnCount() ;//先创建对象Object obj = clazz.newInstance() ;for (int i = 0; i < columnCount; i++) {//拿到列名String columnName = rsmd.getColumnName(i+1) ;//拿到对象对应的属性    列名就是属性的名字 Field field = clazz.getDeclaredField(columnName) ;//设置私有属性可以访问field.setAccessible(true) ;//拿到此列对应的值Object objectValue = rs.getObject(i+1) ;//给属性赋值field.set(obj, objectValue) ;}return obj ;}elsereturn null ;} catch (Exception e) {throw new RuntimeException() ;}}}

ResultSetListenerHandlerImpl.java(针对多条记录)

package com.heima.handler.impl;import java.lang.reflect.Field;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.util.ArrayList;import java.util.List;import com.heima.handler.ResultHandler;//只适用于结果集有多条记录的情况//对象的属性名和表中的字段名应当一致public class ResultSetListenerHandlerImpl implements ResultHandler {@Overridepublic Object handler(ResultSet rs, Class clazz) {List<Object> list = new ArrayList<Object>() ;//拿到结果集的元数据对象try {while(rs.next()){ResultSetMetaData rsmd = rs.getMetaData() ;//拿到公有多少列int columnCount = rsmd.getColumnCount() ;//先创建对象Object obj = clazz.newInstance() ;for (int i = 0; i < columnCount; i++) {//拿到列名String columnName = rsmd.getColumnName(i+1) ;//拿到对象对应的属性Field field = clazz.getDeclaredField(columnName) ;//设置私有属性可以访问field.setAccessible(true) ;//拿到此列对应的值Object objectValue = rs.getObject(i+1) ;//给属性赋值field.set(obj, objectValue) ;}list.add(obj) ;}return list ;} catch (Exception e) {throw new RuntimeException() ;}}}

bean:

package com.heima.bean;import java.io.Serializable;public class Account implements Serializable{private int id ;private String name ;private float 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 float getMoney() {return money;}public void setMoney(float money) {this.money = money;}@Overridepublic String toString() {return "Account [id=" + id + ", name=" + name + ", money=" + money+ "]";}}

Test:

package com.heima.test;/** * use day16 * create table account * ( *    id int primary key, *    name varchar(30) , *    money float * ) */import java.util.List;import org.junit.Test;import com.heima.DBAssist.DBAsssist;import com.heima.bean.Account;//测试自定义框架public class Test2 {// 测试添加@Testpublic void test() {DBAsssist db = new DBAsssist();db.update("insert into account(id,name,money) values(?,?,?)", 2, "乔峰",2000);}// 测试更新@Testpublic void test1() {DBAsssist db = new DBAsssist();db.update("update account set money = money + ? where id = ?", 500, 1);}// 测试更新@Testpublic void test2() {DBAsssist db = new DBAsssist();db.update("delete from account where id = ?", 1);}// 测试查询@Testpublic void test3() {DBAsssist db = new DBAsssist();List<Account> list = (List<Account>)db.query("select * from account", Account.class) ;for (int i = 0; i < list.size(); i++) {System.out.println(list.get(i));}}}