mysql增删改查 dao_jsp+mysql开发最简单的商品增删改查的实例

package com.phome.db;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import com.phome.vo.ProductVo;

import com.phome.vo.UsersVo;

public class ProductDAO {

/**

* 根据商品id查询商品信息

* @param proid

* @return

*/

public ProductVo getProductById(int proid)

{

String sql="select * from product where proid=?";

DbHelper dbhelp=new DbHelper();

Connection conn=null;

PreparedStatement pst=null;

try {

conn=dbhelp.getConnect();

pst=conn.prepareStatement(sql);

pst.setInt(1, proid);

ResultSet rs=pst.executeQuery();

while (rs.next())

{

ProductVo pro=new ProductVo();

pro.setProid(rs.getInt("proid"));

pro.setProName(rs.getString("proName"));

pro.setPrice(rs.getDouble("proprice"));

pro.setPicture(rs.getString("propicture"));

return pro;

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally

{

try {

if (pst!=null)

{

pst.close();

}

if (conn!=null && !conn.isClosed())

{

conn.close();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return null;

}

/**

* 查询所有商品信息，放入ArrayList集合中

* @return

*/

//ArrayList就是可变数组，可以保持任何数据

public ArrayList getAllProduct()

{

ArrayList list=new ArrayList();

String sql="select * from product";

DbHelper dbhelp=new DbHelper();

Connection conn=null;

PreparedStatement pst=null;

try {

conn=dbhelp.getConnect();

pst=conn.prepareStatement(sql);

ResultSet rs=pst.executeQuery();

while (rs.next())

{

ProductVo pro=new ProductVo();

pro.setProid(rs.getInt("proid"));

pro.setProName(rs.getString("proName"));

pro.setPrice(rs.getDouble("proprice"));

pro.setPicture(rs.getString("propicture"));

list.add(pro);//把数据对象放入ArrayList集合中。

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally

{

try {

if (pst!=null)

{

pst.close();

}

if (conn!=null && !conn.isClosed())

{

conn.close();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return list;

}

/**

* 添加商品信息到数据库

* @param product

* @return

*/

public int saveProduct(ProductVo product)

{

String sql="insert into product values (default,?,?,?)";

Object[] args=new Object[]{product.getProName(),product.getPrice(),product.getPicture()};

DbHelper dbHelper=new DbHelper();

int rows=dbHelper.executeUpdate(sql, args);

return rows;

}

/**

* 根据商品信息修改对应的数据库数据

* @param product

* @return

*/

public int updateProduct(ProductVo product)

{

String sql="update product set proName=?,proprice=?,propicture=? where proid=?";

Object[] args=new Object[]{product.getProName(),product.getPrice(),product.getPicture(),product.getProid()};

DbHelper dbHelper=new DbHelper();

int rows=dbHelper.executeUpdate(sql, args);

return rows;

}

/**

* 根据商品Id在数据库中删除该商品信息

* @param id

* @return

*/

public int deleteProduct(int id)

{

String sql="delete from product where proid=?";

Object[] args=new Object[]{id};

DbHelper dbHelper=new DbHelper();

int rows=dbHelper.executeUpdate(sql, args);

return rows;

}

}