mvc三层架构中经典的CRUD写法(1)
需要: mysql驱动jar包,jdbcUtil工具类及其配置文件
----------------------------------------------------------------------------------------------
dao接口层:
public interface CustomerDao {
/**
* 添加客户信息
* @param c
*/
void addCustomer(Customer c);
/**
* 根据id删除客户信息
* @param customerId
*/
void delCustomerById(String customerId);
/**
* 更新客户信息
* @param c
* @throws IdIsNullException 如果参数c的id为null,则抛此异常
*/
void updateCustomer(Customer c) throws IdIsNullException;
/**
* 查询所有的客户信息
* @return
*/
List<Customer> findAll();
/**
* 根据客户的id查询客户信息
* @param customerId
* @return
*/
Customer findCustomerById(String customerId);
}
----------------------------------------------------------------------------------------------
dao实现层:
public class CustomerDaoImpl implements CustomerDao {
public void addCustomer(Customer c) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into customer(id,name,gender,birthday,
cellphone,email,hobby,type,description) values(?,?,?,?,?,?,?,?,?)");
stmt.setString(1,c.getId());
stmt.setString(2,c.getName());
stmt.setString(3,c.getGender());
stmt.setDate(4,new java.sql.Date((c.getBirthday()).getTime()));
stmt.setString(5,c.getCellphone());
stmt.setString(6,c.getEmail());
stmt.setString(7,c.getHobby());
stmt.setString(8,c.getType());
stmt.setString(9,c.getDescription());
stmt.executeUpdate();
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public void delCustomerById(String customerId) {
if(customerId==null){
throw new IllegalArgumentException();
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("delete from customer where id=?");
stmt.setString(1, customerId);
stmt.executeUpdate();
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public List<Customer> findAll() {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List<Customer> cs = new ArrayList<Customer>();
try{
conn = JdbcUtil.getConnection();
// 注意: SQL语句查询多个字段时,不要加引号
stmt = conn.prepareStatement("select id,name,gender,birthday
,cellphone,email,hobby,type,description from customer");
rs = stmt.executeQuery();
while(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setCellphone(rs.getString("cellphone"));
c.setEmail(rs.getString("email"));
c.setHobby(rs.getString("hobby"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
cs.add(c);
}
return cs;
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public Customer findCustomerById(String customerId) {
if(customerId==null){
throw new IllegalArgumentException();
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select id,name,gender,birthday,cellphone,email,hobby,type,description from customer where id=?");
stmt.setString(1, customerId);
rs = stmt.executeQuery();
if(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setCellphone(rs.getString("cellphone"));
c.setEmail(rs.getString("email"));
c.setHobby(rs.getString("hobby"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
return c;
}else{
return null;
}
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public void updateCustomer(Customer c) throws IdIsNullException {
if(c==null){
throw new IllegalArgumentException();
}
if(c.getId()==null){
throw new IdIsNullException("The customer's id can not be null");
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("update customer set name=?,gender=?,birthday=?,cellphone=?
,email=?,hobby=?,type=?,description=? where id=?");
stmt.setString(1, c.getName());
stmt.setString(2, c.getGender());
stmt.setDate(3, new java.sql.Date(c.getBirthday().getTime()));
stmt.setString(4, c.getCellphone());
stmt.setString(5, c.getEmail());
stmt.setString(6, c.getHobby());
stmt.setString(7, c.getType());
stmt.setString(8, c.getDescription());
stmt.setString(9, c.getId());
stmt.executeUpdate();
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public List<Customer> findPageRecords(int startIndex, int pagesize) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List<Customer> cs = new ArrayList<Customer>();
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select id,name,gender,birthday,cellphone
,email,hobby,type,description from customer limit ?,?");
stmt.setInt(1, startIndex);
stmt.setInt(2, pagesize);
rs = stmt.executeQuery();
while(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setCellphone(rs.getString("cellphone"));
c.setEmail(rs.getString("email"));
c.setHobby(rs.getString("hobby"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
cs.add(c);
}
return cs;
}catch (Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}
----------------------------------------------------------------------------------------------
需要: mysql驱动jar包,jdbcUtil工具类及其配置文件
----------------------------------------------------------------------------------------------
dao接口层:
public interface CustomerDao {
/**
* 添加客户信息
* @param c
*/
void addCustomer(Customer c);
/**
* 根据id删除客户信息
* @param customerId
*/
void delCustomerById(String customerId);
/**
* 更新客户信息
* @param c
* @throws IdIsNullException 如果参数c的id为null,则抛此异常
*/
void updateCustomer(Customer c) throws IdIsNullException;
/**
* 查询所有的客户信息
* @return
*/
List<Customer> findAll();
/**
* 根据客户的id查询客户信息
* @param customerId
* @return
*/
Customer findCustomerById(String customerId);
}
----------------------------------------------------------------------------------------------
dao实现层:
public class CustomerDaoImpl implements CustomerDao {
public void addCustomer(Customer c) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("insert into customer(id,name,gender,birthday,
cellphone,email,hobby,type,description) values(?,?,?,?,?,?,?,?,?)");
stmt.setString(1,c.getId());
stmt.setString(2,c.getName());
stmt.setString(3,c.getGender());
stmt.setDate(4,new java.sql.Date((c.getBirthday()).getTime()));
stmt.setString(5,c.getCellphone());
stmt.setString(6,c.getEmail());
stmt.setString(7,c.getHobby());
stmt.setString(8,c.getType());
stmt.setString(9,c.getDescription());
stmt.executeUpdate();
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public void delCustomerById(String customerId) {
if(customerId==null){
throw new IllegalArgumentException();
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("delete from customer where id=?");
stmt.setString(1, customerId);
stmt.executeUpdate();
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public List<Customer> findAll() {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List<Customer> cs = new ArrayList<Customer>();
try{
conn = JdbcUtil.getConnection();
// 注意: SQL语句查询多个字段时,不要加引号
stmt = conn.prepareStatement("select id,name,gender,birthday
,cellphone,email,hobby,type,description from customer");
rs = stmt.executeQuery();
while(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setCellphone(rs.getString("cellphone"));
c.setEmail(rs.getString("email"));
c.setHobby(rs.getString("hobby"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
cs.add(c);
}
return cs;
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public Customer findCustomerById(String customerId) {
if(customerId==null){
throw new IllegalArgumentException();
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select id,name,gender,birthday,cellphone,email,hobby,type,description from customer where id=?");
stmt.setString(1, customerId);
rs = stmt.executeQuery();
if(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setCellphone(rs.getString("cellphone"));
c.setEmail(rs.getString("email"));
c.setHobby(rs.getString("hobby"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
return c;
}else{
return null;
}
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public void updateCustomer(Customer c) throws IdIsNullException {
if(c==null){
throw new IllegalArgumentException();
}
if(c.getId()==null){
throw new IdIsNullException("The customer's id can not be null");
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("update customer set name=?,gender=?,birthday=?,cellphone=?
,email=?,hobby=?,type=?,description=? where id=?");
stmt.setString(1, c.getName());
stmt.setString(2, c.getGender());
stmt.setDate(3, new java.sql.Date(c.getBirthday().getTime()));
stmt.setString(4, c.getCellphone());
stmt.setString(5, c.getEmail());
stmt.setString(6, c.getHobby());
stmt.setString(7, c.getType());
stmt.setString(8, c.getDescription());
stmt.setString(9, c.getId());
stmt.executeUpdate();
}catch(Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
public List<Customer> findPageRecords(int startIndex, int pagesize) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List<Customer> cs = new ArrayList<Customer>();
try{
conn = JdbcUtil.getConnection();
stmt = conn.prepareStatement("select id,name,gender,birthday,cellphone
,email,hobby,type,description from customer limit ?,?");
stmt.setInt(1, startIndex);
stmt.setInt(2, pagesize);
rs = stmt.executeQuery();
while(rs.next()){
Customer c = new Customer();
c.setId(rs.getString("id"));
c.setName(rs.getString("name"));
c.setGender(rs.getString("gender"));
c.setBirthday(rs.getDate("birthday"));
c.setCellphone(rs.getString("cellphone"));
c.setEmail(rs.getString("email"));
c.setHobby(rs.getString("hobby"));
c.setType(rs.getString("type"));
c.setDescription(rs.getString("description"));
cs.add(c);
}
return cs;
}catch (Exception e){
throw new DaoException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}
----------------------------------------------------------------------------------------------