JDBC_操纵MySQL最底层的方法封装详解

无可厚非，关于JDBC一般大家都会去使用第三方提供的已经封装好的架包，但我的这篇文章是给大家介绍自己封装的工具类，就是为了让大家能够了解DBUtils各种方法底层的工作原理：

首先是创建一个和MySQL数据库连接的properties的文件，创建好该文件之后就开始编写代码了

public class DBTools<T> {

private Class<T> cls = null;public DBTools(Class<T> cls){this.cls = cls;}private static String driverClass;private static String url;private static String user;private static String password;static {

//首先需要将保存连接数据库的properties文件中的内容导出来，用来连接数据库ResourceBundle rb = ResourceBundle.getBundle("dbconfig");driverClass = rb.getString("driverClass");url = rb.getString("url");user = rb.getString("user");password = rb.getString("password");try {

//通过反射获取到Driver类Class.forName(driverClass);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//1.连接数据库方法public static Connection getConnection() throws Exception{Connection con = DriverManager.getConnection(url, user, password);return con;}//2.关闭方法，此处用到了方法的重载public static void closeAll(Connection con,ResultSet rs,Statement st) throws SQLException{if(rs!=null){rs.close();}closeAll(st, con);}public static void closeAll(Statement st,Connection con) throws SQLException{if(st !=null){st.close();}if(con !=null){con.close();}}//3.该方法描述数据库中表的二维结构//此中用到了List<Map<String,Object>>结构/** //List<Map<String,Object>> list=new ~;//String username=list.get(0).get("id");//System.out.println(username); */public static List<Map<String, Object>> getMapList(String sql){List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();Connection con = null;Statement st = null;ResultSet rs = null;try {con = getConnection();st = con.createStatement();rs = st.executeQuery(sql);int count = rs.getMetaData().getColumnCount();for(;rs.next();){Map<String,Object> map = new HashMap<String, Object>();for(int i =1;i<=count;i++){map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));}list.add(map);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {closeAll(con, rs, st);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return list;}//4.遍历该二维结构的方法public static void showMapList(List<Map<String,Object>> list){for(int i=0;i<list.size();i++){Map<String ,Object> map = list.get(i);for(Map.Entry<String , Object> m:map.entrySet()){System.out.print(m.getKey()+":"+m.getValue());}System.out.println();}}//5.数据库的增，删，改，方法public static int updateData(String sql){Connection con = null;Statement st = null;int rs = 0;try {con = getConnection();st = con.createStatement();rs = st.executeUpdate(sql);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {closeAll(st, con);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return rs;}//6.获取单个数据库对象的方法public static Student getStudent(String where){Connection con = null;Statement st = null;ResultSet rs = null;Student s = null;try {con = getConnection();st = con.createStatement();rs = st.executeQuery("select * from Student where "+ where);while(rs.next()){int sage = (Integer) rs.getObject("sage");int sid = (Integer) rs.getObject("sid");String ssex = (String) rs.getObject("ssex");String sname = (String) rs.getObject("sname");s = new Student(sid, sname, sage, ssex); }} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return s;}//7.从数据库中查询获取表，把每行所对应的数值属性变为java中的对象添加到集合中//（此方法要保证 表头=类名 列名=类中属性 以表中的Student表为例）public static List<Student> getStudent(){List<Student> list = new ArrayList<Student>();Connection con = null;Statement st = null;ResultSet rs = null;try {con = getConnection();st = con.createStatement();rs = st.executeQuery("select * from Student");while(rs.next()){int sage = (Integer) rs.getObject("sage");int sid = (Integer) rs.getObject("sid");String ssex = (String) rs.getObject("ssex");String sname = (String) rs.getObject("sname");Student s = new Student(sid, sname, sage, ssex);list.add(s);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {closeAll(con, rs, st);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return list;}//8.利用泛型来封装一个getBean方法获取对象，非静态//必须说明一点，该方法属性和数据库中的表必须一一匹配对应，且名字必须相同public T getBean(String where){Connection con = null;Statement st = null;ResultSet rs = null;try {T obj = cls.newInstance();con = getConnection();st = con.createStatement();String tableName = cls.getSimpleName();rs = st.executeQuery("select * from "+tableName+" where "+where);if(rs.next()){//将反射回来的类的属性构建成数组，然后从ResultSet中取值和这个数组中的name匹配赋值Field [] fields = cls.getDeclaredFields();for(int i=0;i<fields.length;i++){fields[i].setAccessible(true);//先将fields[i]的名字获取到，然后rs通过getObject(“列名”)方法获取到值fields[i].set(obj, rs.getObject(fields[i].getName()));}}return obj ;} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {closeAll(con, rs, st);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return null;}//9.封装一个可以获取整张表的getBeanList方法public List<T> getBeanList(){List<T> list = new ArrayList<T>();

String tableName = cls.getSimpleName();String sql = "select * from "+tableName;Connection con = null;Statement st = null;ResultSet rs = null;try {con = getConnection();st = con.createStatement();rs = st.executeQuery(sql);Field [] fields = cls.getDeclaredFields();while(rs.next()){T obj = cls.newInstance();for(int i=0;i<fields.length;i++){fields[i].setAccessible(true);fields[i].set(obj, rs.getObject(fields[i].getName()));}list.add(obj);}return list;} catch (Exception e) {e.printStackTrace();}finally{try {closeAll(con, rs, st);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return null;}//10、注解获取数据库表中的单个数据public T getBeanAnn(String where){Connection con = null;Statement st = null;ResultSet rs = null;TableSwap swap = cls.getAnnotation(TableSwap.class);String tableName = swap.value();String sql = "select * from "+ tableName +" where "+ where;try {con = getConnection();st = con.createStatement();rs = st.executeQuery(sql);Field[] fields = cls.getDeclaredFields();if(rs.next()){T obj = cls.newInstance();for(int i=0;i<fields.length;i++){fields[i].setAccessible(true);TableSwap tableSwap = fields[i].getAnnotation(TableSwap.class);fields[i].set(obj, rs.getObject(tableSwap.value()));}return obj;}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {closeAll(con, rs, st);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return null;}//11、用注解获取整个表，。注解的好处，那就是不必要求java类名和属性名和数据库中的各种名字一一对应，用的时候只需要在上边进行注解就行了

//把注解的接口放在了这个工具类代码的下边public List<T> getBeanAnnList(){List<T> list = new ArrayList<T>();Connection con = null;Statement st = null;ResultSet rs = null;TableSwap ts = cls.getAnnotation(TableSwap.class);String tableName = ts.value();String sql = "select * from "+tableName;try {con = getConnection();st = con.createStatement();rs = st.executeQuery(sql);Field[] fields = cls.getDeclaredFields();while(rs.next()){T obj = cls.newInstance();for(int i=0;i<fields.length;i++){fields[i].setAccessible(true);TableSwap tableSwap = fields[i].getAnnotation(TableSwap.class);fields[i].set(obj, rs.getObject(tableSwap.value()));}list.add(obj);}return list;} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {closeAll(con, rs, st);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return null;}}

//注解接口

import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Target({ElementType.FIELD,ElementType.TYPE})//该注解能放在什么地方@Retention(RetentionPolicy.RUNTIME)//生命周期public @interface TableSwap {String value() default "";}

有了注解，你只需要将你要用到的数据库中的各种名字注解在类名或者属性上就行了，方便简单