utilsql.java

这是本人曾经在公司里用的,内部开发框架,基于struts+hibernate今天分享给大家

/**
 * 
 */
package cn.bway.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.*;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.hibernate.Session;
import cn.bway.admin.vo.StaffInfoVO;

/**
 * @author Kson
 *
 */
public class UtilSQL {

	protected static Logger log = LogManager.getLogger(UtilSQL.class);
	private static ResultSet rs = null;
	private static PreparedStatement stat = null;
	public static String startDate = "2008-01-28";
	
	public static List getStaffInfo(StaffInfoVO vo, Connection conn)
			throws BwayHibernateException {
		List resultList = new ArrayList();
		StaffInfoVO resultVO = null;
		try {
			String strsql = "select * from staffinfo where 1=1 ";
			if (null != vo.getStaffname() && !vo.getStaffname().equals("")) {
				strsql += " and staffname = '" + vo.getStaffname() + "' ";
			} else if (null != vo.getStaffid() && !vo.getStaffid().equals("")) {
				strsql += " and staffid = '" + vo.getStaffid() + "' ";
			}
			strsql += " and passward = '" + vo.getPassward() + "' ";

			stat = conn.prepareStatement(strsql);
			rs = stat.executeQuery();
			while (rs.next()) {
				resultVO = new StaffInfoVO();
				resultVO.setId(rs.getString("id"));
				resultVO.setStaffid(rs.getString("staffid"));
				resultVO.setStaffname(rs.getString("staffname"));
				resultVO.setSex(rs.getString("sex"));
				resultVO.setPassward(rs.getString("passward"));
				resultVO.setStaffcnname(rs.getString("staffcnname"));
				resultVO.setPhone(rs.getString("phone"));
				resultVO.setEmail(rs.getString("email"));
				resultList.add(resultVO);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			log.info("��ѯԱ����ݿ��쳣: " + ex.getMessage());
		}
		return resultList;
	}

	//获得ID号
	public static String getTableValueId(String tmpTableName, Session sess)
			throws BwayHibernateException {
		String id = "0";
		String sql = "";
		ResultSet rs = null;
		PreparedStatement stat = null;
		try {
			sql = "update makerconfig set currentvalue = currentvalue +1 where tablename='"	+ tmpTableName + "' ";
			sess.connection().createStatement().executeUpdate(sql);
			sql = "select * from makerconfig where tablename='" + tmpTableName	+ "' ";
			stat = sess.connection().prepareStatement(sql);
			rs = stat.executeQuery();
			if (rs.next()) {
				id = String.valueOf(rs.getInt("currentvalue"));
				System.out.println("the id:  " + id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;
	}
	
	

}