package bookstore.dao;
import bookstore.idao.ICustomerDao;
import bookstore.model.Customer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class CustomerDao implements ICustomerDao {
@Override
public Customer find(String name, String password) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
Customer customer = null;
try {
con = MySQLHelper.connect();
String sql = "select * from bs_customer where name=? and password=?";
ps = con.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, password);
rs = ps.executeQuery();
if (rs.next()) {
customer = new Customer();
customer.setId(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setPassword(rs.getString("password"));
customer.setRealname(rs.getString("realname"));
customer.setPhone(rs.getString("phone"));
customer.setEmail(rs.getString("email"));
customer.setAddress(rs.getString("address"));
customer.setZipcode(rs.getString("zipcode"));
customer.setDatetime(rs.getDate("datetime"));
}
} catch (Exception e) {
} finally {
MySQLHelper.closeResult(rs);
MySQLHelper.closePreparedStatement(ps);
MySQLHelper.closeConneciton(con);
}
return customer;
}
@Override
public void update(Customer customer) {
Connection con = null;
PreparedStatement ps = null;
try {
con = MySQLHelper.connect();
String sql = "update bs_customer set name=?,password=?,realname=?,phone=?,email=?,address=?,zipcode=? where id=?";
ps = con.prepareStatement(sql);
ps.setString(1, customer.getName());
ps.setString(2, customer.getPassword());
ps.setString(3, customer.getRealname());
ps.setString(4, customer.getPhone());
ps.setString(5, customer.getEmail());
ps.setString(6, customer.getAddress());
ps.setString(7, customer.getZipcode());
ps.setString(8, customer.getName());
ps.setInt(9, customer.getId());
ps.executeUpdate();
} catch (Exception e) {
} finally {
MySQLHelper.closePreparedStatement(ps);
MySQLHelper.closeConneciton(con);
}
}
@Override
public int insert(Customer customer) {
Connection con = null;
PreparedStatement ps = null;
int i=0;
try {
con = MySQLHelper.connect();
String sql = "insert into bs_customer(" +
"name,password,realname,phone,email," +
"address,zipcode) values(?,?,?,?,?,?,?)";
ps = con.prepareStatement(sql);
ps.setString(1, customer.getName());
ps.setString(2, customer.getPassword());
ps.setString(3, customer.getRealname());
ps.setString(4, customer.getPhone());
ps.setString(5, customer.getEmail());
ps.setString(6, customer.getAddress());
ps.setString(7, customer.getZipcode());
i=ps.executeUpdate();
} catch (Exception e) {
} finally {
MySQLHelper.closePreparedStatement(ps);
MySQLHelper.closeConneciton(con);
}
return i;
}
@Override
public void delete(Integer id) {
Connection con = null;
PreparedStatement ps = null;
try {
con = MySQLHelper.connect();
String sql = "delete from bs_customer where id=?";
ps.setInt(1, id);
ps.executeUpdate();
} catch (Exception e) {
} finally {
MySQLHelper.closePreparedStatement(ps);
MySQLHelper.closeConneciton(con);
}
}
@Override
public Customer findById(Integer id) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
Customer customer = null;
try {
con = MySQLHelper.connect();
String sql = "select * from bs_customer where customer_name=?";
ps = con.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
customer = new Customer();
customer.setId(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setPassword(rs.getString("password"));
customer.setRealname(rs.getString("realname"));
customer.setPhone(rs.getString("phone"));
customer.setEmail(rs.getString("email"));
customer.setAddress(rs.getString("address"));
customer.setZipcode(rs.getString("zipcode"));
customer.setDatetime(rs.getDate("datetime"));
}
} catch (Exception e) {
} finally {
MySQLHelper.closeResult(rs);
MySQLHelper.closePreparedStatement(ps);
MySQLHelper.closeConneciton(con);
}
return customer;
}
@Override
public List<Customer> findAll() {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Customer> list = new ArrayList<Customer>();
try {
con = MySQLHelper.connect();
String sql = "select * from bs_customer";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
Customer customer = null;
while (rs.next()) {
customer = new Customer();
customer.setId(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setPassword(rs.getString("password"));
customer.setRealname(rs.getString("realname"));
customer.setPhone(rs.getString("phone"));
customer.setEmail(rs.getString("email"));
customer.setAddress(rs.getString("address"));
customer.setZipcode(rs.getString("zipcode"));
customer.setDatetime(rs.getDate("datetime"));
list.add(customer);
}
} catch (Exception e) {
} finally {
MySQLHelper.closeResult(rs);
MySQLHelper.closePreparedStatement(ps);
MySQLHelper.closeConneciton(con);
}
return list;
}
@Override
public List<Customer> findByPage(Integer pageSize, Integer pageNo) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Customer> list = new ArrayList<Customer>();
try {
con = MySQLHelper.connect();
String sql = "select * from bs_customer limit " + (pageNo - 1) * pageSize + "," + pageSize;
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
Customer customer = null;
while (rs.next()) {
customer = new Customer();
customer.setId(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setPassword(rs.getString("password"));
customer.setRealname(rs.getString("realname"));
customer.setPhone(rs.getString("phone"));
customer.setEmail(rs.getString("email"));
customer.setAddress(rs.getString("address"));
customer.setZipcode(rs.getString("zipcode"));
customer.setDatetime(rs.getDate("datetime"));
list.add(customer);
}
} catch (Exception e) {
} finally {
MySQLHelper.closeResult(rs);
MySQLHelper.closePreparedStateme
评论0
最新资源