java-007

package com.czz.daosimpl;

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

/**
 * derbyDAO
 * 
 * @author Marshal Wu
 * 
 */
public class DerbyUserDao implements ManagerUserDao {

	private Connection getConnection() throws SQLException {
		Connection connection = DriverManager
				.getConnection("jdbc:derby://localhost/test;user=test;password=test");
		connection.setAutoCommit(false);
		return connection;
	}

	public void delete(User user) {
		Connection connection = null;
		try {
			connection = this.getConnection();
			PreparedStatement statement = connection
					.prepareStatement("delete from user_info where id=?");
			statement.setLong(1, user.getId());
			statement.execute();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			this.rollback(connection);
			throw new RegisterException(e);
		} finally {
			this.close(connection);
		}

	}

	public User get(long id) {
		Connection connection = null;
		try {
			connection = this.getConnection();
			PreparedStatement statement = connection
					.prepareStatement("select id,user_name,email,birthday from user_info where id=?");
			statement.setLong(1, id);
			ResultSet resultSet = statement.executeQuery();
			User user = null;

			if (resultSet.next()) {
				user = getUser(resultSet);
			}

			statement.close();
			connection.commit();
			return user;
		} catch (SQLException e) {
			this.rollback(connection);
			throw new RegisterException(e);
		} finally {
			this.close(connection);
		}
	}

	private User getUser(ResultSet resultSet) throws SQLException {
		User user;
		user = new User();
		user.setId(resultSet.getLong("id"));
		user.setName(resultSet.getString("user_name"));
		user.setEmail(resultSet.getString("email"));
		user.setBirthday(resultSet.getDate("birthday"));
		return user;
	}

	public List<User> getAll() {
		Connection connection = null;
		try {
			connection = this.getConnection();
			PreparedStatement statement = connection
					.prepareStatement("select id,user_name,email,birthday from user_info");
			ResultSet resultSet = statement.executeQuery();
			List<User> list = new ArrayList<User>();

			while (resultSet.next()) {
				User user = getUser(resultSet);
				list.add(user);
			}

			statement.close();
			connection.commit();
			return list;
		} catch (SQLException e) {
			this.rollback(connection);
			throw new RegisterException(e);
		} finally {
			this.close(connection);
		}
	}

	public void save(User user) {
		if (user.getId() == 0) {
			this.create(user);
		} else {
			this.update(user);
		}

	}

	private void update(User user) {
		Connection connection = null;

		try {
			connection = this.getConnection();
			PreparedStatement statement = connection
					.prepareStatement("update user_info set user_name=?,email=?,birthday=? where id=?");
			int index = 1;
			statement.setString(index++, user.getName());
			statement.setString(index++, user.getEmail());
			statement.setDate(index++, new Date(user.getBirthday().getTime()));
			statement.setLong(index++, user.getId());
			statement.execute();

			statement.close();
			connection.commit();
		} catch (SQLException e) {
			this.rollback(connection);
			throw new RegisterException(e);
		} finally {
			this.close(connection);
		}

	}

	private void create(User user) {
		Connection connection = null;

		try {
			connection = this.getConnection();

			PreparedStatement statement = connection
					.prepareStatement("insert into user_info(user_name,email,birthday) values(?,?,?)");
			int index = 1;
			statement.setString(index++, user.getName());
			statement.setString(index++, user.getEmail());
			if (user.getBirthday() == null) {
				statement.setDate(index++, null);
			} else {
				statement.setDate(index++, new Date(user.getBirthday()
						.getTime()));
			}

			statement.execute();
			statement.close();

			user.setId(this.getId(connection));
			connection.commit();
		} catch (SQLException e) {
			this.rollback(connection);
			throw new RegisterException(e);
		} finally {
			this.close(connection);
		}
	}

	private int getId(Connection connection) throws SQLException {
		CallableStatement callableStatement = connection
				.prepareCall("values identity_val_local()");

		ResultSet resultSet = callableStatement.executeQuery();
		resultSet.next();
		int id = resultSet.getInt(1);

		resultSet.close();
		callableStatement.close();

		return id;
	}

	private void rollback(Connection connection) {
		if (connection != null) {
			try {
				connection.rollback();
			} catch (SQLException e) {
			}
		}
	}

	private void close(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
			}
		}
	}

	public void deleteAll() {

	}
}