详解JDBC数据库链接及相关方法的封装

public class Consql {
 private static Consql consql=null;//单例设计模式
 private Connection conn=null;//数据库链接
 private final String url;//数据库url
 private final String username;//数据库用户名
 private final String password;//数据库密码
 //驱动类的加载
 static{//以静态代码块的形式加载驱动类，静态代码块只在类加载的时候执行一次
 try {
 Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException e) {
 e.printStackTrace();
 }
 }
 //构造函数
 private Consql(String url,String username,String password) throws SQLException{
 this.url = url;
 this.username = username;
 this.password = password;
 open();//创建连接
 }
 private Connection open() throws SQLException
 {
 try {//驱动器获取数据库链接
 conn=DriverManager.getConnection(url, username, password);
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 //e.printStackTrace();
 throw e;
 } 
 return conn; 
 }
 /**
 * 带限制条件查找
 * @param sql 带占位符？的sql语句
 * @param t 返回相关类型对象的类（T.class）
 * @param params 替换占位符的数据，为动态数组
 * @return ArrayList<T>
 * @throws SQLException 
 */
 public <T> ArrayList<T> select(String sql,Class<T> t,Object...params) throws SQLException
 {//获取T类所有public方法
 Method[] declaredMethods = t.getDeclaredMethods();
 //创建一个盛放该类型对象集合
 ArrayList<T> arrayList=new ArrayList<>();
 try (PreparedStatement pStatement=conn.prepareStatement(sql);)
 { 
 for(int i=0;i<params.length;i++)
 {
 pStatement.setObject(i+1, params[i]);
 } 
 try(ResultSet rSet=pStatement.executeQuery();) 
 {
 ResultSetMetaData rData=rSet.getMetaData();
 //获取查询到结果表的列数
 int columnCount = rData.getColumnCount(); 
 while (rSet.next()) {
 T a=t.newInstance();//创建泛型类实例
 for(int i=0;i<columnCount;i++)
 {//获得方数组里的set方法，这里造成了局限性，只能数据库表列名与对象名一致，且只能是set方法
 String aString="set"+rData.getColumnName(i+1);
 for (Method method : declaredMethods) {
 if(method.getParameterCount()==1&&method.getReturnType().toString().equals("void")&&method.getName().equalsIgnoreCase(aString))
 {//这里存在问题，前两个判断条件基本没用，主要是最初不想用上面拼串的方式来判断是不是调用该参数的方法
 method.setAccessible(true);
 //利用反射调用该方法
 method.invoke(a, rSet.getObject(i+1));
 break;
 }
 }
 }
 arrayList.add(a);
 }
 } catch (InstantiationException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (IllegalAccessException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (IllegalArgumentException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (InvocationTargetException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } 
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 return arrayList; 
 }
 /**
 * 数据插入
 * @param sql 带占位符？的sql语句
 * @param params 替换占位符的数据，动态数组
 * @throws SQLException
 */
 public void insert(String sql,Object...params) throws SQLException
 {
 try(PreparedStatement pStatement=conn.prepareStatement(sql);) {
 
 for(int i=0;i<params.length;i++)
 {
 pStatement.setObject(i+1, params[i]);
 }
 pStatement.executeUpdate();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 }
 /**
 * 数据更新
 * @param sql 带占位符？的sql语句
 * @param params 替换占位符的数据，动态数组
 * @throws SQLException
 */
 public void update(String sql,Object...params) throws SQLException
 {
 try(PreparedStatement pStatement=conn.prepareStatement(sql);) {
 
 for(int i=0;i<params.length;i++)
 {
 pStatement.setObject(i+1, params[i]);
 }
 pStatement.executeUpdate();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 }
 /**
 * 带限制条件删除
 * @param sql 带占位符？的sql语句
 * @param params 替换占位符的数据，动态数组
 * @throws SQLException
 */
 public void delete(String sql,Object...params) throws SQLException
 {
 try(PreparedStatement pStatement=conn.prepareStatement(sql);) {
 
 for(int i=0;i<params.length;i++)
 {
 pStatement.setObject(i+1, params[i]);
 }
 pStatement.executeUpdate();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 }
 /**
 * 删除全部，不带有限制
 * @param sql
 * @throws SQLException
 */
 public void deleteall(String sql) throws SQLException
 {
 try(PreparedStatement pStatement=conn.prepareStatement(sql);) { 
 pStatement.executeUpdate();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 }
 /**
 * 无限制条件查找
 * @param sql 
 * @param t 泛型类T.class
 * @return ArrayList<T>
 * @throws SQLException 
 */
 public <T> ArrayList<T> select(String sql,Class<T> t) throws SQLException
 {
 Method[] declaredMethods = t.getDeclaredMethods();
 ArrayList<T> arrayList=new ArrayList<>();
 try (PreparedStatement pStatement=conn.prepareStatement(sql);)
 { 
 try(ResultSet rSet=pStatement.executeQuery();) 
 {
 ResultSetMetaData rData=rSet.getMetaData();
 int columnCount = rData.getColumnCount(); 
 while (rSet.next()) {
 T a=t.newInstance();
 for(int i=0;i<columnCount;i++)
 {
 String aString="set"+rData.getColumnName(i+1);
 for (Method method : declaredMethods) {
 if(method.getName().equalsIgnoreCase(aString))
 {
 method.setAccessible(true);
 method.invoke(a, rSet.getObject(i+1));
 break;
 }
 }
 }
 arrayList.add(a);
 }
 } catch (InstantiationException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (IllegalAccessException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (IllegalArgumentException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (InvocationTargetException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } 
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 return arrayList; 
 }
 /**
 * 返回表中数据行数
 * @param tableName 数据库表名
 * @return 行数
 * @throws SQLException
 */
 public int count(String tableName) throws SQLException
 {
 String sql="select count(*) from "+tableName;
 try(PreparedStatement pStatement=conn.prepareStatement(sql);
 ResultSet rsSet=pStatement.executeQuery(); )
 { 
 if(rsSet.next())
 {
 return rsSet.getInt(1);
 } 
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 return 0;
 }
 /**
 * 判断数据是否存在
 * @param sql 带占位符？的sql语句
 * @param params 替换占位符的数据，动态数组
 * @return boolean
 * @throws SQLException
 */
 public boolean isExist(String sql,Object...params) throws SQLException
 { 
 try(PreparedStatement pStatement=conn.prepareStatement(sql);)
 {
 for(int i=0;i<params.length;i++)
 {
 pStatement.setObject(i+1, params[i]);
 }
 try(ResultSet rsSet=pStatement.executeQuery();) {
 if(rsSet.next())
 {
 return true;
 }
 } finally {
 
 }
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 throw e;
 }
 return false; 
 }
 /**
 * 创建实例
 * @param url 数据库url
 * @param username 用户名
 * @param password 密码
 * @return consql对象
 * @throws SQLException
 */
 public static Consql getnewInstance(String url,String username,String password) throws SQLException
 {
 if(consql==null)
 consql=new Consql(url, username, password);
 return consql; 
 }
 //垃圾回收，貌似并不能达到析构函数的效果
 protected void finalize() throws Throwable
 {
 if(conn!=null)
 {
 conn.close(); 
 }
 super.finalize();
 }
}