package com.zjl.jc.dao;
import java.util.*;
import com.zjl.jc.vo.*;public interface IProductDAO {
/**
* 数据的增加操作一般以doxxx的方式命名
* @param product 要增加的数据对象
* @return 是否增加成功的标记
* @throws Exception 有异常交给被调用处处理
*/
public boolean doCreate(Product product) throws Exception;
/**
* 查询全部的数据,一般以findXxx的方式命名
* @param keyWord 查询关键字
* @return 返回全部的查询结果,每一个product对象表示一行记录
* @throws Exception 有异常交给被调用处处理
*/
public List<Product> findAll(String keyWord) throws Exception;
/**
* 根据产品编号查询产品信息
* @param product_id 产品编号
* @return 产品的vo对象
* @throws Exception 有异常交给被调用处处理
*/
public Product findById(int product_id) throws Exception;
}public class ProductDAOImpl implements IProductDAO {
private Connection conn = null;
private PreparedStatement pstmt = null; // 数据库操作对象
public ProductDAOImpl(Connection conn) {
this.conn = conn;
}
public boolean doCreate(Product product) throws Exception {
boolean flag = false;
String sql = "insert into product(product_id,product_name,price,spec,pattern,category,spirit,"
+"years,locality,maker,dealer,herstelldatum,shelf_life,rangerover,sale_date,buyer,add_time)"
+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.setInt(1, product.getProduct_id());
this.pstmt.setString(2, product.getProduct_name());
this.pstmt.setDouble(3, product.getPrice());
this.pstmt.setString(4, product.getSpec());
this.pstmt.setString(5, product.getPattern());
this.pstmt.setString(6, product.getCategory());
this.pstmt.setString(7, product.getSpirit());
this.pstmt.setDate(8, new java.sql.Date(product.getYear().getTime()));
this.pstmt.setString(9, product.getLocality());
this.pstmt.setString(10, product.getMaker());
this.pstmt.setString(11, product.getDealer());
this.pstmt.setString(12, product.getHerstelldatum());
this.pstmt.setString(13, product.getShelf_life());
this.pstmt.setBoolean(14, product.isRangerover());
this.pstmt.setDate(15, new java.sql.Date(product.getSale_date()
.getTime()));
this.pstmt.setString(16, product.getBuyer());
this.pstmt.setDate(17, new java.sql.Date(product.getAdd_time()
.getTime()));
if(this.pstmt.executeUpdate() > 0) {
flag = true;
}
this.pstmt.close();
return flag;
}
public List<Product> findAll(String keyWord) throws Exception {
List<Product> all = new ArrayList<Product>();
String sql = "select product_id,product_name,price,spec,pattern,category,spirit,"
+"years,locality,maker,dealer,herstelldatum,shelf_life,rangerover,sale_date,buyer,add_time"
+"from product where product_name like=? or price like=?";
this.pstmt = conn.prepareStatement(sql);
this.pstmt.setString(1, "%"+keyWord+"%");
this.pstmt.setString(2, "%"+keyWord+"%");
ResultSet rs = this.pstmt.executeQuery();
Product product = null; // 定义product对象
while(rs.next()) { //依次取出每条数据
product = new Product();
product.setProduct_id(rs.getInt(1));
product.setProduct_name(rs.getString(2));
product.setPrice(rs.getDouble(3));
product.setSpec(rs.getString(rs.getString(4)));
product.setPattern(rs.getString(5));
product.setCategory(rs.getString(6));
product.setSpirit(rs.getString(7));
product.setYear(rs.getDate(8));
product.setLocality(rs.getString(9));
product.setMaker(rs.getString(10));
product.setDealer(rs.getString(11));
product.setHerstelldatum(rs.getString(12));
product.setShelf_life(rs.getString(13));
product.setRangerover(rs.getBoolean(14));
product.setSale_date(rs.getDate(15));
product.setBuyer(rs.getString(16));
product.setAdd_time(rs.getDate(17));
all.add(product);
}
this.pstmt.close();
return all;
}
public Product findById(int productId) throws Exception {
Product product = null;
String sql = "select product_id,product_name,price,spec,pattern,category,spirit,"
+"years,locality,maker,dealer,herstelldatum,shelf_life,rangerover,sale_date,buyer,add_time"
+"from product where product_id=?";
this.pstmt = conn.prepareStatement(sql);
this.pstmt.setInt(1, productId);
ResultSet rs = this.pstmt.executeQuery();
if(rs.next()) { //如果可以查询到结果
product = new Product();
product.setProduct_id(rs.getInt(1));
product.setProduct_name(rs.getString(2));
product.setPrice(rs.getDouble(3));
product.setSpec(rs.getString(rs.getString(4)));
product.setPattern(rs.getString(5));
product.setCategory(rs.getString(6));
product.setSpirit(rs.getString(7));
product.setYear(rs.getDate(8));
product.setLocality(rs.getString(9));
product.setMaker(rs.getString(10));
product.setDealer(rs.getString(11));
product.setHerstelldatum(rs.getString(12));
product.setShelf_life(rs.getString(13));
product.setRangerover(rs.getBoolean(14));
product.setSale_date(rs.getDate(15));
product.setBuyer(rs.getString(16));
product.setAdd_time(rs.getDate(17));
}
this.pstmt.close();
return product; //如果查询不到结果则返回空,默认值为null
}
}public class ProductDAOProxy implements IProductDAO {
private DatabaseConnection dbc = null;
private IProductDAO dao = null; // 声明真实主题dao对象
public ProductDAOProxy() throws Exception { // 在构造方法中实例化数据库连接,同时实例化dao
this.dbc = new DatabaseConnection();
this.dao = new ProductDAOImpl(this.dbc.getConnection()); // 实例化真实主题类
}
public boolean doCreate(Product product) throws Exception {
boolean flag = false; // 定义标志位
try {
if (this.dao.findById(product.getProduct_id()) != null) { // 如果要插入的产品编号不存在
flag = this.dao.doCreate(product); // 调用真实主题操作
}
} catch (Exception e) {
throw e; // 将异常交给调用处处理
} finally {
this.dbc.colseConnection();
}
return flag;
}
public List<Product> findAll(String keyWord) throws Exception {
List<Product> all = null; // 定义返回的集合
try {
this.dao.findAll(keyWord);
} catch (Exception e) {
throw e; // 将异常交给调用处处理
} finally {
this.dbc.colseConnection();
}
return all;
}
public Product findById(int productId) throws Exception {
Product product = new Product(); // 定义product对象
try {
product = this.dao.findById(product.getProduct_id());
} catch (Exception e) {
throw e;
} finally {
this.dbc.colseConnection();
}
return product;
}
}
package com.zjl.jc.dbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/zjl";
public static final String DBUSER = "root";
public static final String DBPASS = "root";
private Connection conn = null;
public DatabaseConnection() throws Exception { // 在构造方法中进行数据库的连接
try {
Class.forName(DBDRIVER);
this.conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
} catch (Exception e) {
throw e;
}
}
public Connection getConnection() {
return conn;
}
public void colseConnection() throws SQLException { // 数据库关闭,
if (this.conn != null) { // 避免NullPointeException异常
this.conn.close();
}
}
}
package com.zjl.jc.factory;
import com.zjl.jc.dao.IProductDAO;
import com.zjl.jc.dao.proxy.ProductDAOProxy;
public class DAOFactory { //工厂就是接口和实现类的汇合点
public static IProductDAO getProductDAOInstance() throws Exception { //取得DAO接口的实例
return new ProductDAOProxy(); // 取得代类的实例
}
}
package com.zjl.jc.vo;
import java.util.Date;
public class Product {
private int product_id; // 编号
private String product_name; // 名称
private double price; //价格
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
private String spec; // 规格
private String pattern; // 型号
private String category; // 类型
private String spirit; // 酒精度
private Date years; // 年份
private String locality; // 原产地
private String maker; // 生产厂家
private String dealer; // 销售商
private String herstelldatum; // 生产日期
private String shelf_life; // 保质期
private boolean rangerover; // 销售状态
private Date sale_date; // 销售日期
private String buyer; // 购买人
private Date add_time; // 加入日期
//get set方法省略………………
Exception in thread "main" com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where product_id=1000' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
at com.zjl.jc.dao.impl.ProductDAOImpl.findById(ProductDAOImpl.java:95)
at com.zjl.jc.dao.proxy.ProductDAOProxy.doCreate(ProductDAOProxy.java:23)
at TestDAOInsert.main(TestDAOInsert.java:30)
在线等 我自己调了一个year是数据库中的关键字 我改成years了 这是我根据李兴华老师的书自己写的例子 我是新手 谢谢你们了！！！！
import com.zjl.jc.factory.DAOFactory;
import com.zjl.jc.vo.Product;
public class TestDAOInsert {
public static void main(String[] args) throws Exception {
Product product = null;
for (int i = 0; i < 5; i++) {
product = new Product(); //实例化新的product对象
product.setProduct_id(1000 + i);
product.setProduct_name("青岛啤酒" + i);
product.setPrice(10 + i);
product.setSpec("瓶装" + i);
product.setPattern("1星" + i);
product.setCategory("啤酒");
product.setSpirit("3°");
product.setYear(new java.util.Date());
product.setLocality("青岛");
product.setMaker("青岛啤酒厂");
product.setDealer("ligeng");
product.setHerstelldatum("2010年10月" + i);
product.setShelf_life("1个月");
product.setRangerover(false);
product.setSale_date(new java.util.Date());
product.setBuyer("张森");
product.setAdd_time(new java.util.Date());
DAOFactory.getProductDAOInstance().doCreate(product);
}
}}Exception in thread "main" com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where product_id=1000' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
at com.zjl.jc.dao.impl.ProductDAOImpl.findById(ProductDAOImpl.java:95)
at com.zjl.jc.dao.proxy.ProductDAOProxy.doCreate(ProductDAOProxy.java:23)
at TestDAOInsert.main(TestDAOInsert.java:30)// 在线等 在线等啊 谢谢谢谢
太长了。。没有看到main方法在哪里根据提示应该是sql语句错了在这句代码附近仔细检查一下：'where product_id=1000' 。