JDBC检索返回实体list

 

理论依据多态性和工厂模式。

说明：实体bean和dao类中基本的数据库操作(本例中的方法)都是用工具生成的代码，用vba读取表定义书(Excel2003格式)，格式见最后的图片。

 

1，  创建实体类的基类EOBase

package goods.model;

import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

public abstract class EOBase {

	/**
	 * 设置实体某一个属性的值
	 */
	public void setValue(String key, Object val) {

	}

	/**
	 * 取得实体某一个属性的值
	 */
	public Object getValue(String key) {
		return "";
	}

	// key的map，为了测试遍历，把它改成public，本来是protected
	public final Map<String, String> Key_map = new HashMap<String, String>();

	/**
	 * 取得Date类型的年月日 。根据数据库返回的日期时间的格式，修改这个方法。
	 *
	 * @param str
	 * @return
	 */
	protected static Date getDate(String str) {

		// 取得日期和时间两部分
		String[] strTempDateTime = str.split(" ");
		// 取得年月日三部分
		String[] strTempDate = strTempDateTime[0].split("-");

		Date date = new Date(Integer.valueOf(strTempDate[0]) - 1900, Integer
				.valueOf(strTempDate[1]) - 1, Integer.valueOf(strTempDate[2]));
		return date;
	}

	/**
	 *
	 * 把eoFrom的值，设置到自己对应的属性中
	 *
	 * @param eoFrom
	 */
	public void copyFromEO(EOBase eoFrom) {

		// 如果eoFrom是空,则返回
		if (eoFrom == null) {
			return;
		}

		// 遍历eoFrom的属性
		for (String key : eoFrom.Key_map.values()) {
			try {
				// 设置到自己对应的属性
				this.setValue(key, eoFrom.getValue(key));
			} catch (Exception ex) {
				System.out.println(ex);
			}
		}
	}

	/**
	 *
	 * 把自己的属性，设置到eoTo的对应的属性中
	 *
	 * @param eoTo
	 */
	public void copyToEO(EOBase eoTo) {

		// 如果eoTo是空,则返回
		if (eoTo == null) {
			return;
		}

		// 遍历自己的属性
		for (String key : this.Key_map.values()) {
			try {
				// 把自己的属性的值，赋给eoTo对应的属性
				eoTo.setValue(key, this.getValue(key));
			} catch (Exception ex) {
				System.out.println(ex);
			}
		}
	}

	/**
	 *
	 * 将map转换成实体
	 *
	 * @param map
	 */
	public void copyFromMap(Map map) {

		// 如果map是空,则返回
		if (map == null) {
			return;
		}

		// map的迭代器
		Iterator iterator = map.entrySet().iterator();

		// 遍历map
		while (iterator.hasNext()) {
			try {
				// 取得map的键值对
				Map.Entry entry = (Map.Entry) iterator.next();
				// 实体的key
				Object key = entry.getKey();
				// 实体value
				Object val = entry.getValue();

				// 设置实体属性的值
				this.setValue(key.toString(), val);
			} catch (Exception ex) {
				System.out.println(ex);
			}
		}
	}

	/**
	 *
	 * 将实体转换成map
	 *
	 * @param map
	 */
	public void copyToMap(Map map) {

		// 如果map是空,则返回
		if (map == null) {
			return;
		}

		// 遍历自己的属性
		for (String key : this.Key_map.values()) {
			try {
				// 把自己的属性的值，赋给map
				map.put(key, this.getValue(key));
			} catch (Exception ex) {
				System.out.println(ex);
			}
		}
	}
}

 

2，新建实体类

package goods.model;

/**
 * Goods entity
 *
 * @author See Tools
 */
public class Goods extends EOBase implements java.io.Serializable{

    //货物编号
    private String goodsNum;
    //货物种类
    private String goodsType;
    //规格
    private String modelNumber;
    //面积
    private Float area;
    //长度
    private Float length;
    //宽度
    private Float width;
    //厚度
    private Float thick;
    //颜色
    private String color;
    //质量等级
    private String qualityLev;
    //产地(采购单位)
    private String madeIn;
    //备注
    private String remarks;
    //登录年月日
    private String insertYMD;

    // Key word
    public static final String Key_goodsNum = "goodsNum";
    public static final String Key_goodsType = "goodsType";
    public static final String Key_modelNumber = "modelNumber";
    public static final String Key_area = "area";
    public static final String Key_length = "length";
    public static final String Key_width = "width";
    public static final String Key_thick = "thick";
    public static final String Key_color = "color";
    public static final String Key_qualityLev = "qualityLev";
    public static final String Key_madeIn = "madeIn";
    public static final String Key_remarks = "remarks";
    public static final String Key_insertYMD = "insertYMD";

    /** 构造函数 */
    public Goods() {
        this.Key_map.put(Goods.Key_goodsNum, Goods.Key_goodsNum);
        this.Key_map.put(Goods.Key_goodsType, Goods.Key_goodsType);
        this.Key_map.put(Goods.Key_modelNumber, Goods.Key_modelNumber);
        this.Key_map.put(Goods.Key_area, Goods.Key_area);
        this.Key_map.put(Goods.Key_length, Goods.Key_length);
        this.Key_map.put(Goods.Key_width, Goods.Key_width);
        this.Key_map.put(Goods.Key_thick, Goods.Key_thick);
        this.Key_map.put(Goods.Key_color, Goods.Key_color);
        this.Key_map.put(Goods.Key_qualityLev, Goods.Key_qualityLev);
        this.Key_map.put(Goods.Key_madeIn, Goods.Key_madeIn);
        this.Key_map.put(Goods.Key_remarks, Goods.Key_remarks);
        this.Key_map.put(Goods.Key_insertYMD, Goods.Key_insertYMD);
    }

    public String getGoodsNum() {
        return this.goodsNum;
    }

    public void setGoodsNum(String goodsNum) {
         this.goodsNum = goodsNum;
    }

    public String getGoodsType() {
        return this.goodsType;
    }

    public void setGoodsType(String goodsType) {
         this.goodsType = goodsType;
    }

    public String getModelNumber() {
        return this.modelNumber;
    }

    public void setModelNumber(String modelNumber) {
         this.modelNumber = modelNumber;
    }

    public Float getArea() {
        return this.area;
    }

    public void setArea(Float area) {
         this.area = area;
    }

    public Float getLength() {
        return this.length;
    }

    public void setLength(Float length) {
         this.length = length;
    }

    public Float getWidth() {
        return this.width;
    }

    public void setWidth(Float width) {
         this.width = width;
    }

    public Float getThick() {
        return this.thick;
    }

    public void setThick(Float thick) {
         this.thick = thick;
    }

    public String getColor() {
        return this.color;
    }

    public void setColor(String color) {
         this.color = color;
    }

    public String getQualityLev() {
        return this.qualityLev;
    }

    public void setQualityLev(String qualityLev) {
         this.qualityLev = qualityLev;
    }

    public String getMadeIn() {
        return this.madeIn;
    }

    public void setMadeIn(String madeIn) {
         this.madeIn = madeIn;
    }

    public String getRemarks() {
        return this.remarks;
    }

    public void setRemarks(String remarks) {
         this.remarks = remarks;
    }

    public String getInsertYMD() {
        return this.insertYMD;
    }

    public void setInsertYMD(String insertYMD) {
         this.insertYMD = insertYMD;
    }

    public Object getValue(String key) {
        if(Goods.Key_goodsNum.equals(key)){
            return this.goodsNum;
        }
        if(Goods.Key_goodsType.equals(key)){
            return this.goodsType;
        }
        if(Goods.Key_modelNumber.equals(key)){
            return this.modelNumber;
        }
        if(Goods.Key_area.equals(key)){
            return this.area;
        }
        if(Goods.Key_length.equals(key)){
            return this.length;
        }
        if(Goods.Key_width.equals(key)){
            return this.width;
        }
        if(Goods.Key_thick.equals(key)){
            return this.thick;
        }
        if(Goods.Key_color.equals(key)){
            return this.color;
        }
        if(Goods.Key_qualityLev.equals(key)){
            return this.qualityLev;
        }
        if(Goods.Key_madeIn.equals(key)){
            return this.madeIn;
        }
        if(Goods.Key_remarks.equals(key)){
            return this.remarks;
        }
        if(Goods.Key_insertYMD.equals(key)){
            return this.insertYMD;
        }
        return null;
    }

    public void setValue(String key, Object obj) {
        if(Goods.Key_goodsNum.equals(key)){
            this.goodsNum = (String)obj;
            return;
        }
        if(Goods.Key_goodsType.equals(key)){
            this.goodsType = (String)obj;
            return;
        }
        if(Goods.Key_modelNumber.equals(key)){
            this.modelNumber = (String)obj;
            return;
        }
        if(Goods.Key_area.equals(key)){
            this.area = (Float)obj;
            return;
        }
        if(Goods.Key_length.equals(key)){
            this.length = (Float)obj;
            return;
        }
        if(Goods.Key_width.equals(key)){
            this.width = (Float)obj;
            return;
        }
        if(Goods.Key_thick.equals(key)){
            this.thick = (Float)obj;
            return;
        }
        if(Goods.Key_color.equals(key)){
            this.color = (String)obj;
            return;
        }
        if(Goods.Key_qualityLev.equals(key)){
            this.qualityLev = (String)obj;
            return;
        }
        if(Goods.Key_madeIn.equals(key)){
            this.madeIn = (String)obj;
            return;
        }
        if(Goods.Key_remarks.equals(key)){
            this.remarks = (String)obj;
            return;
        }
        if(Goods.Key_insertYMD.equals(key)){
            this.insertYMD = (String)obj;
            return;
        }
    }

}

3，  实体bean的工厂

package goods.factory;

import goods.model.EOBase;
import goods.model.Goods;


public class EOBeanFactory {

 //取得一个bean
 public static EOBase getBean(String strBean) {

  if (strBean == null || strBean.isEmpty()){
   return null;
  }

  if (C_Goods.equals(strBean)){
   return new Goods();
  }
 
  return null;
 }

 //货物的bean
 public static final String C_Goods = "Goods";

}

 

 

 4，DaoBean的工厂类，DaoBeanFactory。ConstantUtil.C_ApplicationContextPath是spring配置文件的路径。如果不用spring可以像实体bean工厂一样，直接new一个dao。

package goods.factory;

import goods.dao.GoodsDao;



public class DaoBeanFactory {

//	private static ApplicationContext applicationContext = new FileSystemXmlApplicationContext(ConstantUtil.C_ApplicationContextPath);

	//取得一个bean
	public static Object getBean(String strBean) {

//		return DaoBeanFactory.applicationContext.getBean(strBean);
		return new GoodsDao();
	}

	//货物的bean
	public static final String C_goodsDao = "goodsDao";

}

5，DAO的基类，这里用的hibernate方法取得的jdbc，可以直接用jdbc的方法取得resultSet。删除掉了几个方法，在，主要看检索list。

package goods.dao;
import goods.factory.EOBeanFactory;
import goods.model.EOBase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * Data access interface for domain model
 *
 * @author MyEclipse Persistence Tools
 */
//public class DaoManager extends HibernateDaoSupport {
public class DaoManager {
 // 构造函数
 public DaoManager(Class p_class) {
 }

 // 取得数据的list
 protected List findList(String queryString, String p_className) {
  return this.findListJdbc(queryString, p_className);
 }
 /**
  *  取得数据的list
  * @param queryString 检索语句
  * @param p_className 实体类名称的字符串
  */
 private List findListJdbc(String queryString, String p_className) {
  System.out.println(queryString);
  try {
   //session
   //Session session = this.getHibernateTemplate().getSessionFactory().openSession();
   //数据库连接
   //Connection conn = session.connection();
   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "test", "123");
   PreparedStatement preparedStatement = conn.prepareStatement(queryString);
   ResultSet resultSet = preparedStatement.executeQuery();
   //结果集数据源信息
   ResultSetMetaData rsmd = resultSet.getMetaData();
       //返回的list
         List<EOBase> retList = new ArrayList();
   while (resultSet.next()) {
    //用基类声明，用工厂返回实体
    EOBase entity = EOBeanFactory.getBean(p_className);
       for ( int i = 1; i <= rsmd.getColumnCount(); i++ ){
        //设置实体的属性
        entity.setValue(rsmd.getColumnName(i), resultSet.getObject(i));
       }
       retList.add(entity);
   }
   //关闭语句
   preparedStatement.close();
   //关闭连接
   conn.close();
   //关闭session
   //session.close();
   return retList;
  } catch (Exception ex) {
   return null;
  }
 }
}

 

 

6，实体的dao类

package goods.dao;

import goods.factory.EOBeanFactory;
import goods.model.Goods;

import java.util.List;

/**
 *GoodsDao
 *
 * @author See Tools
 */
public class GoodsDao extends DaoManager{

    // 构造函数
    public GoodsDao() {
         super(GoodsDao.class);
    }

    //全件检索
    public List getAllGoods() {
        String queryString = "select goodsNum, goodsType, modelNumber, area, length, width, thick, color, qualityLev, madeIn, remarks, insertYMD from Goods order by goodsNum";
        return this.findList(queryString, EOBeanFactory.C_Goods);
    }

    //主键检索返回一个List
    public List getGoodsListByKey(Goods p_Goods) {
        String queryString = "select goodsNum, goodsType, modelNumber, area, length, width, thick, color, qualityLev, madeIn, remarks, insertYMD from Goods where goodsNum = '" + p_Goods.getGoodsNum() + "'";
        return this.findList(queryString, EOBeanFactory.C_Goods);
    }

    //主键检索返回一个实例
    public Goods getGoodsByKey(Goods p_Goods)
    {
        List list = this.getGoodsListByKey(p_Goods);
        if (list == null || list.size() < 1) {
            return null;
            }
        else {
            return (Goods)list.get(0);
        }
    }
//
//    //追加
//    public boolean insertGoods(Goods p_Goods) {
//        String queryString = "insert Into Goods(goodsNum, goodsType, modelNumber, area, length, width, thick, color, qualityLev, madeIn, remarks, insertYMD) values('" + p_Goods.getGoodsNum() + "', '" + p_Goods.getGoodsType() + "', '" + p_Goods.getModelNumber() + "', " + p_Goods.getArea() + ", " + p_Goods.getLength() + ", " + p_Goods.getWidth() + ", " + p_Goods.getThick() + ", '" + p_Goods.getColor() + "', '" + p_Goods.getQualityLev() + "', '" + p_Goods.getMadeIn() + "', '" + p_Goods.getRemarks() + "', '" + p_Goods.getInsertYMD() + "')";
//        return this.executeNonQuery(queryString);
//    }
//
//    //更新
//    public boolean updateGoodsByKey(Goods p_Goods) {
//        String queryString = "update Goods set goodsType = '" + p_Goods.getGoodsType() + "', modelNumber = '" + p_Goods.getModelNumber() + "', area = " + p_Goods.getArea() + ", length = " + p_Goods.getLength() + ", width = " + p_Goods.getWidth() + ", thick = " + p_Goods.getThick() + ", color = '" + p_Goods.getColor() + "', qualityLev = '" + p_Goods.getQualityLev() + "', madeIn = '" + p_Goods.getMadeIn() + "', remarks = '" + p_Goods.getRemarks() + "', insertYMD = '" + p_Goods.getInsertYMD() + "' where goodsNum = '" + p_Goods.getGoodsNum() + "'";
//        return this.executeNonQuery(queryString);
//    }
//
//    //按主键删除
//    public boolean delGoodsByKey(Goods p_Goods) {
//        String queryString = "delete from Goods where goodsNum = '" + p_Goods.getGoodsNum() + "'";
//        return this.executeNonQuery(queryString);
//    }
//
//    //全件删除
//    public boolean delAllGoods() {
//        String queryString = "delete from Goods";
//        return this.executeNonQuery(queryString);
//    }

}

 

7，  测试类

package test;

import goods.dao.GoodsDao;
import goods.factory.DaoBeanFactory;
import goods.model.EOBase;
import goods.model.Goods;

import java.util.List;

public class test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

		// 取得dao
		GoodsDao goodsDao = (GoodsDao) DaoBeanFactory.getBean(DaoBeanFactory.C_goodsDao);
		// 调用检索方法
		List<Goods> goodsList = goodsDao.getAllGoods();
		
		//list的大小
		int iSize = goodsList.size();
		//遍历结果list
		EOBase goods = null;
		for(int i = 0; i < iSize; i++){
			goods = goodsList.get(i);
			
			//遍历实体bean的属性
			for (String key : goods.Key_map.values()) {
				// 设置到自己对应的属性
				System.out.println(key + "=" +goods.getValue(key));
			}
		}
	}

}

下边的表结构，用vba来生成实体bean和dao类中基本的数据库操作(本例中的方法)的代码。