自定義JDBC框架：

定義必要的信息、獲取數(shù)據(jù)庫的連接、釋放資源都是重復(fù)的代碼，在操作JDBC時(shí)通常都是執(zhí)行SQL語句就可以了，所以需要抽取出來一個(gè)模板類來封裝一些方法（Update、Query），專門執(zhí)行增刪改查的SQL語句，簡化使用步驟。

DataBaseMetaData：數(shù)據(jù)庫的源信息

• java.sql.DataBaseMetaData：封裝了整個(gè)數(shù)據(jù)庫的綜合信息

例如：

• String getDatabaseProductName()：獲取數(shù)據(jù)庫產(chǎn)品的名稱
• int getDatabaseProductVersion()：獲取數(shù)據(jù)庫產(chǎn)品的版本號(hào)

ParameterMetaData：參數(shù)的源信息

• java.sql.ParameterMetaData：封裝的是預(yù)編譯執(zhí)行者對(duì)象中每個(gè)參數(shù)的類型和屬性
• 這個(gè)對(duì)象可以通過預(yù)編譯執(zhí)行者對(duì)象中的getParameterMetaData()方法來獲取

核心功能：

int getParameterCount()：獲取sql語句中參數(shù)的個(gè)數(shù)

ResultSetMetaData：結(jié)果集的源信息

java.sql.ResultSetMetaData：封裝的是結(jié)果集對(duì)象中列的類型和屬性
這個(gè)對(duì)象可以通過結(jié)果集對(duì)象中的getMetaData()方法來獲取

核心功能：

• int getColumnCount()：獲取列的總數(shù)
• String getColumnName(int i)：獲取列名

查詢的方法：

• 查詢一條記錄并封裝對(duì)象的方法：queryForObject()
• 查詢多條記錄并封裝到集合的方法：queryForList()
• 查詢聚合函數(shù)并返回單條數(shù)據(jù)的方法：queryForScalar()

JDBCTemplate類增刪改功能的編寫

public class JDBCTemplate {// 1. 定義參數(shù)變量：數(shù)據(jù)源、連接對(duì)象、執(zhí)行者對(duì)象、結(jié)果集對(duì)象private DataSource dataSource;private Connection connection;private PreparedStatement ps;private ResultSet resultSet;// 2. 通過有參構(gòu)造為數(shù)據(jù)賦值public JDBCTemplate(DataSource dataSource) {this.dataSource = dataSource;}// 3. 定義Update方法,參數(shù)：sql語句、sql語句中的參數(shù)public int update(String sql, Object... objects) {// 4. 定義int變量，用于接收增刪改后影響的行數(shù)int result = 0;try {// 5. 通過數(shù)據(jù)獲取一個(gè)數(shù)據(jù)庫連接connection = dataSource.getConnection();// 6. 通過數(shù)據(jù)庫連接對(duì)象獲取執(zhí)行者對(duì)象，并對(duì)sql語句進(jìn)行預(yù)編譯ps = connection.prepareStatement(sql);// 7. 通過執(zhí)行者對(duì)象獲取參數(shù)的源信息對(duì)象ParameterMetaData parameterMetaData = ps.getParameterMetaData();// 8. 通過參數(shù)源信息對(duì)象獲取參數(shù)的個(gè)數(shù)int count = parameterMetaData.getParameterCount();// 9. 判斷參數(shù)數(shù)量是否一致if (count != objects.length) {throw new RuntimeException("參數(shù)個(gè)數(shù)不匹配");}// 10. 為sql語句占位符賦值for (int i = 0; i < objects.length; i++) {ps.setObject(i + 1, objects[i]);}// 11. 執(zhí)行sql語句并接收結(jié)果result = ps.executeUpdate();} catch (SQLException sqlException) {sqlException.printStackTrace();} finally {// 12. 釋放資源DataSourceUtils.close(connection, ps);}// 13. 返回結(jié)果return result;} }

JDBCTemplate類查詢功能的編寫：

• 實(shí)體類

/*學(xué)生實(shí)體類*/ public class Student {private Integer sid;private String name;private Integer age;private Date birthday;public Student() {}public Student(Integer sid, String name, Integer age, Date birthday) {this.sid = sid;this.name = name;this.age = age;this.birthday = birthday;}public Integer getSid() {return sid;}public void setSid(Integer sid) {this.sid = sid;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}@Overridepublic String toString() {return "Student{" +"sid=" + sid +", name='" + name + '\'' +", age=" + age +", birthday=" + birthday +'}';} }

• ResultSetHandler接口

/*用于處理結(jié)果集的接口*/ public interface ResultSetHandler<T> {//處理結(jié)果集的抽象方法。<T> T handler(ResultSet rs); }

• BeanHandler實(shí)現(xiàn)類

/*實(shí)現(xiàn)類1：用于完成將查詢出來的一條記錄，封裝到Student對(duì)象中*/ public class BeanHandler<T> implements ResultSetHandler<T> {//1.聲明對(duì)象類型變量private Class<T> beanClass;//2.有參構(gòu)造對(duì)變量賦值public BeanHandler(Class<T> beanClass) {this.beanClass = beanClass;}/*將ResultSet結(jié)果集中的數(shù)據(jù)封裝到beanClass類型對(duì)象中*/@Overridepublic T handler(ResultSet rs) {//3.聲明對(duì)象T bean = null;try{//4.創(chuàng)建傳遞參數(shù)的對(duì)象bean = beanClass.newInstance();//5.判斷是否有結(jié)果集if(rs.next()) {//6.得到所有的列名//6.1先得到結(jié)果集的源信息ResultSetMetaData rsmd = rs.getMetaData();//6.2還要得到有多少列int columnCount = rsmd.getColumnCount();//6.3遍歷列數(shù)for(int i = 1; i <= columnCount; i++) {//6.4得到每列的列名String columnName = rsmd.getColumnName(i);//6.5通過列名獲取數(shù)據(jù)Object columnValue = rs.getObject(columnName);//6.6列名其實(shí)就是對(duì)象中成員變量的名稱。于是就可以使用列名得到對(duì)象中屬性的描述器(get和set方法)PropertyDescriptor pd = new PropertyDescriptor(columnName.toLowerCase(),beanClass);//6.7獲取set方法Method writeMethod = pd.getWriteMethod();//6.8執(zhí)行set方法，給成員變量賦值writeMethod.invoke(bean,columnValue);}}} catch (Exception e) {e.printStackTrace();}//7.將對(duì)象返回return bean;} }

• BeanListHandler實(shí)現(xiàn)類

/*實(shí)現(xiàn)類2：用于將結(jié)果集封裝到集合中*/ public class BeanListHandler<T> implements ResultSetHandler<T> {//1.聲明對(duì)象變量private Class<T> beanClass;//2.有參構(gòu)造為變量賦值public BeanListHandler(Class<T> beanClass) {this.beanClass = beanClass;}@Overridepublic List<T> handler(ResultSet rs) {//3.創(chuàng)建集合對(duì)象List<T> list = new ArrayList<>();try{//4.遍歷結(jié)果集對(duì)象while(rs.next()) {//5.創(chuàng)建傳遞參數(shù)的對(duì)象T bean = beanClass.newInstance();//6.得到所有的列名//6.1先得到結(jié)果集的源信息ResultSetMetaData rsmd = rs.getMetaData();//6.2還要得到有多少列int columnCount = rsmd.getColumnCount();//6.3遍歷列數(shù)for(int i = 1; i <= columnCount; i++) {//6.4得到每列的列名String columnName = rsmd.getColumnName(i);//6.5通過列名獲取數(shù)據(jù)Object columnValue = rs.getObject(columnName);//6.6列名其實(shí)就是對(duì)象中成員變量的名稱。于是就可以使用列名得到對(duì)象中屬性的描述器(get和set方法)PropertyDescriptor pd = new PropertyDescriptor(columnName.toLowerCase(),beanClass);//6.7獲取set方法Method writeMethod = pd.getWriteMethod();//6.8執(zhí)行set方法，給成員變量賦值writeMethod.invoke(bean,columnValue);}//7.將對(duì)象保存到集合中list.add(bean);}} catch (Exception e) {e.printStackTrace();}//8.返回結(jié)果return list;} }

• ScalarHandler實(shí)現(xiàn)類

/*實(shí)現(xiàn)類3：用于返回一個(gè)聚合函數(shù)的查詢結(jié)果*/ public class ScalarHandler<T> implements ResultSetHandler<T> {@Overridepublic Long handler(ResultSet rs) {//1.聲明一個(gè)變量Long value = null;try{//2.判斷是否有結(jié)果if(rs.next()) {//3.獲取結(jié)果集的源信息ResultSetMetaData rsmd = rs.getMetaData();//4.獲取第一列的列名String columnName = rsmd.getColumnName(1);//5.根據(jù)列名獲取值value = rs.getLong(columnName);}} catch(Exception e) {e.printStackTrace();}//6.將結(jié)果返回return value;} }

• JDBCTemplate類

public class JDBCTemplate {private DataSource dataSource;private Connection con;private PreparedStatement pst;private ResultSet rs;public JDBCTemplate(DataSource dataSource) {this.dataSource = dataSource;}/*專用于執(zhí)行聚合函數(shù)sql語句的方法*/public Long queryForScalar(String sql, ResultSetHandler<Long> rsh, Object...objs) {Long result = null;try{con = dataSource.getConnection();pst = con.prepareStatement(sql);//獲取sql語句中的參數(shù)源信息ParameterMetaData pData = pst.getParameterMetaData();int parameterCount = pData.getParameterCount();//判斷參數(shù)個(gè)數(shù)是否一致if(parameterCount != objs.length) {throw new RuntimeException("參數(shù)個(gè)數(shù)不匹配");}//為sql語句中的?占位符賦值for (int i = 0; i < objs.length; i++) {pst.setObject(i+1,objs[i]);}//執(zhí)行sql語句rs = pst.executeQuery();//通過ScalarHandler方式對(duì)結(jié)果進(jìn)行處理result = rsh.handler(rs);} catch(Exception e) {e.printStackTrace();} finally {//釋放資源DataSourceUtils.close(con,pst,rs);}//將結(jié)果返回return result;}/*專用于查詢所有記錄sql語句的方法*/public <T> List<T> queryForList(String sql, ResultSetHandler<T> rsh, Object...objs) {List<T> list = new ArrayList<>();try{con = dataSource.getConnection();pst = con.prepareStatement(sql);//獲取sql語句中的參數(shù)源信息ParameterMetaData pData = pst.getParameterMetaData();int parameterCount = pData.getParameterCount();//判斷參數(shù)個(gè)數(shù)是否一致if(parameterCount != objs.length) {throw new RuntimeException("參數(shù)個(gè)數(shù)不匹配");}//為sql語句中的?占位符賦值for (int i = 0; i < objs.length; i++) {pst.setObject(i+1,objs[i]);}//執(zhí)行sql語句rs = pst.executeQuery();//通過BeanListHandler方式對(duì)結(jié)果進(jìn)行處理list = rsh.handler(rs);} catch(Exception e) {e.printStackTrace();} finally {//釋放資源DataSourceUtils.close(con,pst,rs);}//將結(jié)果返回return list;}/*專用于執(zhí)行查詢一條記錄sql語句的方法*/public <T> T queryForObject(String sql, ResultSetHandler<T> rsh, Object...objs) {T obj = null;try{con = dataSource.getConnection();pst = con.prepareStatement(sql);//獲取sql語句中的參數(shù)源信息ParameterMetaData pData = pst.getParameterMetaData();int parameterCount = pData.getParameterCount();//判斷參數(shù)個(gè)數(shù)是否一致if(parameterCount != objs.length) {throw new RuntimeException("參數(shù)個(gè)數(shù)不匹配");}//為sql語句中的?占位符賦值for (int i = 0; i < objs.length; i++) {pst.setObject(i+1,objs[i]);}//執(zhí)行sql語句rs = pst.executeQuery();//通過BeanHandler方式對(duì)結(jié)果進(jìn)行處理obj = rsh.handler(rs);} catch(Exception e) {e.printStackTrace();} finally {//釋放資源DataSourceUtils.close(con,pst,rs);}//將結(jié)果返回return obj;} }

測(cè)試自定義JDBC框架的使用：

public class JDBCTemplateTest {//創(chuàng)建JDBCTemplate對(duì)象JDBCTemplate template = new JDBCTemplate(DataSourceUtils.getDataSource());@Testpublic void selectScalar() {//查詢student表的記錄條數(shù)String sql = "SELECT COUNT(*) FROM student";Long count = template.queryForScalar(sql, new ScalarHandler<Long>());System.out.println(count);}@Testpublic void selectAll() {//查詢所有學(xué)生信息String sql = "SELECT * FROM student";List<Student> list = template.queryForList(sql, new BeanListHandler<Student>(Student.class));for(Student stu : list) {System.out.println(stu);}}@Testpublic void selectOne() {//查詢張三這條記錄String sql = "SELECT * FROM student WHERE sid=?";//通過BeanHandler將結(jié)果封裝成一個(gè)Student對(duì)象Student stu = template.queryForObject(sql, new BeanHandler<Student>(Student.class), 1);System.out.println(stu);}@Testpublic void insert() {//新增周七記錄String sql = "INSERT INTO student VALUES (?,?,?,?)";Object[] params = {5,"周七",27,"2007-07-07"};int result = template.update(sql, params);System.out.println(result);}@Testpublic void delete() {//刪除周七這條記錄String sql = "DELETE FROM student WHERE sid=?";int result = template.update(sql, 5);System.out.println(result);}@Testpublic void update() {//修改張三的年齡為33String sql = "UPDATE student SET age=? WHERE name=?";Object[] params = {33,"張三"};int result = template.update(sql,params);System.out.println(result);} } 創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的JDBC自定义框架的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。