DAO设计模式之禅之数据库万能查询操作

DAO设计模式

          DAO(Data Access Object) 数据访问对象是第一个面向对象的接口，它显露了 Microsoft Jet 数据库引擎（由 Microsoft Access 所使用），并允许 Visual Basic 开发者通过 ODBC 像直接连接到其他数据库一样，直接连接到 Access 表。DAO 最适用于单系统应用程序或小范围本地分布使用。

          这里的DAO也就一个功能实现重用的效果、直接来示例代码吧！

第一类：用户类

package com.dao.bean;/* * 属性的封装类 * A class to do one thing */public class Animals {private int id;private String name;private int age;private int anId;public Animals(){}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 int getAge() {return age;}public void setAge(int age) {this.age = age;}public int getAnId() {return anId;}public void setAnId(int anId) {this.anId = anId;}}

第二：数据库链接类

package com.jdbc.uitl;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;/* *  DAO工具类 *  A class to do one thing *  */public class JDBCUtil {public static Connection open(){Connection conn = null;try {//加载驱动Class.forName("com.mysql.jdbc.Driver");//获取连接数据库conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/animal","root","123456");  }catch (Exception e) {System.out.println("数据库连接失败！");}return conn;}//关闭 2种 资源的方法public static void closeRes(Connection conn ,PreparedStatement ps){try {if(ps!=null){ps.close();}if(conn!=null){conn.close();}} catch (Exception e) {System.out.println("数据库资源已经关闭！");}}//关闭 3种 资源的方法public static void closeRes(Connection conn ,PreparedStatement ps,ResultSet rs){try {if(rs!=null){rs.close();}if(ps!=null){ps.close();}if(conn!=null){conn.close();}} catch (Exception e) {System.out.println("数据库资源已经关闭！");}}}

第三：Dao类

package com.dao.data;import java.lang.reflect.Field;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import com.jdbc.uitl.JDBCUtil;/* * 万能DAO *  * */public class DaoSelect {//1、查询所有属性[返回一个集合、ArrayList]@SuppressWarnings({ "rawtypes", "unchecked" })public ArrayList getList(Class<?> cl){ArrayList ar = new ArrayList();Connection conn = JDBCUtil.open();PreparedStatement ps = null;ResultSet rs = null;//获取类型名字、这里表示表名[对象.getSimpleName()]String sql = "select *  from "+ cl.getSimpleName();//获取属性[对象.getDeclaredFields()]Field[] fi = cl.getDeclaredFields();try {//链接数据库ps = conn.prepareStatement(sql);rs = ps.executeQuery();Object ob = null;while(rs.next()){ob = cl.newInstance();for(Field ff : fi){//属性可见ff.setAccessible(true);ff.set(ob, rs.getObject(ff.getName()));}ar.add(ob);}} catch (Exception e) {System.out.println("查询所以属性操作失败！");}finally{//关闭资源JDBCUtil.closeRes(conn, ps, rs);}return ar;}//2、查询单个属性[返回一个属性、Object]@SuppressWarnings("rawtypes")public Object getObById(Class cl,int id){Object ob = null;Connection conn = JDBCUtil.open();PreparedStatement ps = null;ResultSet rs =null;//获取属性[对象.getDeclaredFields()]Field[] fi = cl.getDeclaredFields();//获取属性名[对象.getSimpleName()]String sql = "select * from "+cl.getSimpleName()+" where "+fi[0].getName()+" = "+id;     try {//链接数据库ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()){ob = cl.newInstance();for(Field ff : fi){ff.setAccessible(true);ff.set(ob,rs.getObject(ff.getName()));}}} catch (Exception e) {System.out.println("查询单个属性操作失败！");}finally{JDBCUtil.closeRes(conn, ps, rs);}return ob;}//3、查询未知属性[返回一个集合、ArrayList]@SuppressWarnings({ "rawtypes", "unchecked" })public ArrayList getListBySome(Class cl ,String name,Object value){ArrayList ar = new ArrayList();Object ob = null;Connection conn = JDBCUtil.open();PreparedStatement ps = null;ResultSet rs = null;//获取属性、属性名[对象.getDeclaredFields()][对象.getSimpleName()]Field[] fi = cl.getDeclaredFields();String sql = "select * from "+cl.getSimpleName()+" where "+name+" = '"+value+"'";try {//连接数据库ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()){ob = cl.newInstance();for(Field ff : fi){ff.setAccessible(true);ff.set(ob, rs.getObject(ff.getName()));}ar.add(ob);}} catch (Exception e) {System.out.println("查询未知属性操作失败！");}finally{JDBCUtil.closeRes(conn, ps, rs);}return ar;}}

第四：实现类

package com.dao.data;import java.util.ArrayList;import com.dao.bean.Animals;/* * 万能测试Class *  */public class TestSelect {@SuppressWarnings("unchecked")public static void main(String[] args) {DaoSelect dao = new DaoSelect();////1、Animals类、所有属性ArrayList<Animals> ar = dao.getList(Animals.class);for (Animals an: ar) {System.out.print("编号：" + an.getId() + "——名字：" + an.getName());System.out.println("——年龄：" + an.getAge() + "——类型ID：" + an.getAnId());}////2、Animals类、查询单个属性//Animals ar1 = (Animals) dao.getObById(Animals.class, 2);//System.out.println("id为2的名字是：" + ar1.getName());//3、Animals类、查询单个属性//ArrayList<Animals> ar1 = dao.getListBySome(Animals.class, "name", "陈郑游");////ArrayList<Animals> ar = dao.getListBySome(Animals.class, "age", "21");//for (Animals an: ar ) {//System.out.println("编号：" + an.getId() + "——名字：" + an.getName());//}}}

第五：数据库SQL语句

//动物类数据库create database animals ;use  animals;create table  animals (id int primary key,  name varchar(20) ,sex char(2), age int ,anid int );