exchangerecorddao.java

该项为帮助银行工作人员处理银行业务

package com.bluedot.bank.framework.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.bluedot.bank.framework.sql.MySqlInfo;
import com.bluedot.bank.framework.web.actionform.ExchangerecordBean;

public class ExchangerecordDao {
	public void create(ExchangerecordBean exchangerecordBean) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("insert into exchangerecord(record_id,account_id,exchange_time,amount,banlance,description) values(?,?,?,?,?,?)");
			int index = 1;
			statement.setString(index++, String.valueOf(new Date().getTime()));
			statement.setString(index++, exchangerecordBean.getAccount_id());
			statement.setTimestamp(index++, new Timestamp(System
					.currentTimeMillis()));
			statement.setFloat(index++, Float.valueOf(exchangerecordBean.getAmount()));
			statement.setFloat(index++, Float.valueOf(exchangerecordBean.getBanlance()));
			statement.setString(index++, exchangerecordBean.getDescription());

			statement.execute();
			connection.commit();
			statement.close();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}

	public List<ExchangerecordBean> select(String info, String type,String px,
			String time1, String time2) {
		List<ExchangerecordBean> list = new ArrayList<ExchangerecordBean>();
		Connection connection = null;
		
		ExchangerecordBean exchangerecordBean=new ExchangerecordBean();
		try 
		{
			connection = new MySqlInfo().getConnection();		
			StringBuffer sql=new StringBuffer("select * from exchangerecord where 1=1 ");
			if(!info.equals("all"))
			{
				sql.append("and " +info+ " like ? escape '!' ");
				if(!time1.equals("") && !time1.trim().isEmpty())
				{
					sql.append(" and exchange_time > ? ");
				}
				if(!time2.equals("") && !time2.trim().isEmpty())
				{
					sql.append(" and exchange_time < ? ");
				}
			}
						
			if(px.equals("desc"))
			{
				sql.append(" order by record_id desc");
			}
			else
			{
				sql.append(" order by record_id asc");
			}
			PreparedStatement preparedStatement=connection.prepareStatement(sql.toString());
			if(!info.equals("all"))
			{
				int index=1;
				preparedStatement.setString(index++, "%"
						+ type.replaceAll("!", "!!").replaceAll("%", "!%")+"%");
				if(!time1.equals("") && !time1.trim().isEmpty())
				{
					preparedStatement.setTimestamp(index++, Timestamp.valueOf(time1));
				}
				if(!time2.equals("") && !time2.trim().isEmpty())
				{
					preparedStatement.setTimestamp(index++, Timestamp.valueOf(time2));
				}
			}			
			ResultSet rs=preparedStatement.executeQuery();
			if(rs.next())
			{
				do
				{
					exchangerecordBean=new ExchangerecordBean();
					exchangerecordBean.setRecord_id(rs.getString("record_id"));
					exchangerecordBean.setAccount_id(rs.getString("account_id"));
					exchangerecordBean.setExchange_time(rs.getTimestamp("exchange_time"));
					exchangerecordBean.setAmount(rs.getString("amount"));
					exchangerecordBean.setBanlance(rs.getString("banlance"));
					exchangerecordBean.setDescription(rs.getString("description"));
					list.add(exchangerecordBean);
				}
				while(rs.next());
			}
			rs.close();
			preparedStatement.close();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}
}