|
|
|
步骤 2 : 基本的CRUD操作 步骤 3 : 非CRUD方法
PropertyValueDAO用于建立对于PropertyValue对象的ORM映射
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import tmall.bean.Product;
import tmall.bean.Property;
import tmall.bean.PropertyValue;
import tmall.util.DBUtil;
public class PropertyValueDAO {
public int getTotal() {
int total = 0;
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "select count(*) from PropertyValue";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(PropertyValue bean) {
String sql = "insert into PropertyValue values(null,?,?,?)";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, bean.getProduct().getId());
ps.setInt(2, bean.getProperty().getId());
ps.setString(3, bean.getValue());
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
bean.setId(id);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void update(PropertyValue bean) {
String sql = "update PropertyValue set pid= ?, ptid=?, value=? where id = ?";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, bean.getProduct().getId());
ps.setInt(2, bean.getProperty().getId());
ps.setString(3, bean.getValue());
ps.setInt(4, bean.getId());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "delete from PropertyValue where id = " + id;
s.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public PropertyValue get(int id) {
PropertyValue bean = new PropertyValue();
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "select * from PropertyValue where id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
int pid = rs.getInt("pid");
int ptid = rs.getInt("ptid");
String value = rs.getString("value");
Product product = new ProductDAO().get(pid);
Property property = new PropertyDAO().get(ptid);
bean.setProduct(product);
bean.setProperty(property);
bean.setValue(value);
bean.setId(id);
}
} catch (SQLException e) {
e.printStackTrace();
}
return bean;
}
public PropertyValue get(int ptid, int pid ) {
PropertyValue bean = null;
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "select * from PropertyValue where ptid = " + ptid + " and pid = " + pid;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
bean= new PropertyValue();
int id = rs.getInt("id");
String value = rs.getString("value");
Product product = new ProductDAO().get(pid);
Property property = new PropertyDAO().get(ptid);
bean.setProduct(product);
bean.setProperty(property);
bean.setValue(value);
bean.setId(id);
}
} catch (SQLException e) {
e.printStackTrace();
}
return bean;
}
public List<PropertyValue> list() {
return list(0, Short.MAX_VALUE);
}
public List<PropertyValue> list(int start, int count) {
List<PropertyValue> beans = new ArrayList<PropertyValue>();
String sql = "select * from PropertyValue order by id desc limit ?,? ";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
PropertyValue bean = new PropertyValue();
int id = rs.getInt(1);
int pid = rs.getInt("pid");
int ptid = rs.getInt("ptid");
String value = rs.getString("value");
Product product = new ProductDAO().get(pid);
Property property = new PropertyDAO().get(ptid);
bean.setProduct(product);
bean.setProperty(property);
bean.setValue(value);
bean.setId(id);
beans.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return beans;
}
public void init(Product p) {
List<Property> pts= new PropertyDAO().list(p.getCategory().getId());
for (Property pt: pts) {
PropertyValue pv = get(pt.getId(),p.getId());
if(null==pv){
pv = new PropertyValue();
pv.setProduct(p);
pv.setProperty(pt);
this.add(pv);
}
}
}
public List<PropertyValue> list(int pid) {
List<PropertyValue> beans = new ArrayList<PropertyValue>();
String sql = "select * from PropertyValue where pid = ? order by ptid desc";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, pid);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
PropertyValue bean = new PropertyValue();
int id = rs.getInt(1);
int ptid = rs.getInt("ptid");
String value = rs.getString("value");
Product product = new ProductDAO().get(pid);
Property property = new PropertyDAO().get(ptid);
bean.setProduct(product);
bean.setProperty(property);
bean.setValue(value);
bean.setId(id);
beans.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return beans;
}
}
基本的CRUD操作与CategoryDAO的CRUD一样，在此不做赘述。
注： 这四个方法并没有在实际业务中被调用，只是习惯性得被设计了，在测试的时候会被使用到。 public List<PropertyValue> list() public List<PropertyValue> list(int start, int count) public PropertyValue get(int id) public int getTotal()
除开CRUD之外，PropertyValueDAO还提供了一些其他用于支持业务的方法。
根据属性id和产品id，获取一个PropertyValue对象 public PropertyValue get(int ptid, int pid ) 初始化某个产品对应的属性值 public void init(Product p) 查询某个产品下所有的属性值 public List<PropertyValue> list(int pid) 注：部分非CRUD的业务方法，需要结合业务场景才能更好地理解，现在理解不透彻也很正常，在后续学习到相关场景的时候，再回过头来看，就明白了。
HOW2J公众号，关注后实时获知最新的教程和优惠活动，谢谢。
提问之前请登陆
提问已经提交成功，正在审核。 请于 我的提问 处查看提问记录，谢谢
|
