employeedaojdbc.java

对XML和DB的CURD的基本操作.使用的两个比较常用的组合设计模式：MVC模式+DAO模式。

package com.mycompany.myapp.dao.jdbc;

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

import com.mycompany.myapp.dao.EmployeeDao;
import com.mycompany.myapp.model.Employee;

/**
 * the jdbc implement for employeeDao
 */
public class EmployeeDAOJdbc implements EmployeeDao {
	
	public boolean insertEmployee(Employee employee) {
		boolean b = false;
		Connection conn = null;
		PreparedStatement stmt = null;
		String sql = null;
		try {
			sql = "insert into employee(firstname,lastname,phonenumber,address) values (?,?,?,?)";
			conn = DbHelper.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, employee.getFirstName());
			stmt.setString(2, employee.getLastName());
			stmt.setString(3, employee.getPhoneNumber());
			stmt.setString(4, employee.getAddress());
			int rst = stmt.executeUpdate();
			if (rst != 0) {
				b = true;
			}
			stmt.close();
			DbHelper.closeConnection();

		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}

	public boolean updateEmployee(Employee employee) {
		boolean b = false;
		Connection conn = null;
		PreparedStatement stmt = null;
		String sql = null;
		try {
			sql = "update employee set firstname=?,lastname=?,phonenumber=?,address=? where id="
					+ employee.getId();
			conn = DbHelper.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, employee.getFirstName());
			stmt.setString(2, employee.getLastName());
			stmt.setString(3, employee.getPhoneNumber());
			stmt.setString(4, employee.getAddress());
			int rst = stmt.executeUpdate();
			if (rst != 0) {
				b = true;
			}
			stmt.close();
			DbHelper.closeConnection();

		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}

	//@Override
	public Employee get(Long id) {
		Employee employee = null;
		String sql = "select * from employee where id=" + id;
		ResultSet rs = null;
		try {
			rs = DbHelper.getResultSet(sql);
			if (rs.next()) {
				employee = new Employee();
				employee.setId(rs.getLong("id"));
				employee.setFirstName(rs.getString("firstname"));
				employee.setLastName(rs.getString("lastname"));
				employee.setPhoneNumber(rs.getString("phonenumber"));
				employee.setAddress(rs.getString("address"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return employee;
	}

	//@Override
	public List<Employee> getAll() {
		List<Employee> employees = null;
		Employee employee = null;
		String sql = "select * from employee";
		ResultSet rs = null;
		try {
			employees = new ArrayList<Employee>();
			rs = DbHelper.getResultSet(sql);
			while (rs.next()) {
				employee = new Employee();
				employee.setId(rs.getLong("id"));
				employee.setFirstName(rs.getString("firstname"));
				employee.setLastName(rs.getString("lastname"));
				employee.setPhoneNumber(rs.getString("phonenumber"));
				employee.setAddress(rs.getString("address"));
				employees.add(employee);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return employees;
	}

//	@Override
	public void remove(Long id) {
		String sql = "delete from employee where id=" + id;
		try {
			boolean bSuccess = DbHelper.execute(sql);
			if (!bSuccess) {
				System.out.println("error occur when do delete where id=" + id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//@Override
	public Employee insert(Employee employee) {
		insertEmployee(employee);
		return employee;
	}
	
	//@Override
	public Employee update(Employee employee) {
		updateEmployee(employee);
		return employee;
	}
}