===========com.ruanyuan.dao.impl============
package com.ruanyuan.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDAO {
// 驱动
private static final String DRIVER="com.mysql.cj.jdbc.Driver";
// 数据库的URL url基本格式如下:连接地址+ssl连接关闭+字符集为utf-8+时区设置
private static final String URL="jdbc:mysql://127.0.0.1:3306/shopdb?useUnicode=true&characterEncoding=UTF-8&userSSL=false?serverTimezone=GMT%2B8";
// 数据库的用户名
private static final String UNAME="root";
// 数据路的用户密码
private static final String UPWD="root";
// 创建连接对象
protected Connection conn;
// 创建PreparedStatement对象
protected PreparedStatement pstmt;
// 创建ResultSet对象
protected ResultSet rs;
/**
*连接数据库
*/
public void getConnection() {
try {
// 注册并加载驱动
Class.forName(DRIVER);
// 连接数据库
conn=DriverManager.getConnection(URL,UNAME,UPWD);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
/**
* 释放资源
*/
public void closeResource() {
// 如果ResultSet对象不为空则释放资源
if(rs!=null) {
try {
rs.close();
} catch (Exception e) {
// TODO: handle exception
}
}
// 如果PreparedStatement对象不为空则释放资源
if(pstmt!=null) {
try {
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
}
}
// 如果Connection对象不为空则释放资源
if(conn!=null) {
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
/**执行SQL语句
*
* @param sql SQL语句
* @param paras 可变参数
* @return 返回受影响行数
*/
public int exeUpdate(String sql,String...paras) {
// 受影响行数
int count=0;
try {
// 连接数据库的准备工作
this.getConnection();
// 基于PreparedStatement对象创建sql语句
pstmt=conn.prepareStatement(sql);
// 设置动态参数
if(pstmt!=null && paras.length>0) {
for (int i = 0; i < paras.length; i++) {
pstmt.setString(i+1, paras[i]);
}
}
// 执行SQL语句,并获取受影响的行数
count =pstmt.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
// 释放资源
}finally {
this.closeResource();
}
// 返回受影响行数
return count;
}
/**查询所有
*
* @param sql SQL语句
* @param paras 可变参数
* @return
*/
public ResultSet exeQuery(String sql,String...paras) {
try {
// 连接数据库的准备工作
this.getConnection();
// 基于PreparedStatement对象创建sql语句
pstmt=conn.prepareStatement(sql);
// 设置动态参数
if(pstmt!=null && paras.length>0) {
for (int i = 0; i < paras.length; i++) {
pstmt.setString(i+1, paras[i]);
}
}
// 执行SQL语句,并获取受影响的行数
rs=pstmt.executeQuery();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
// 返回受影响行数
return rs;
}
}
—————————————————————
package com.ruanyuan.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.ruanyuan.dao.CategoryDAO;
import com.ruanyuan.entity.Category;
public class CategoryDAOImpl extends BaseDAO implements CategoryDAO{
/**重写添加数据
* @param category 类别对象
*/
@Override
public int inserDate(Category category) {
// 初始化受影响行数为0
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
String sql="INSERT INTO `category` (`CategoryId`, `CategoryName`) VALUES (NULL, '"+category.getCategoryName()+"');";
// 执行语句,并返回结果
count=super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
// 返回受影响行数,判断是否执行成功
return count;
}
/**重写删除数据
* @param category 类别对象
*/
@Override
public int deleteDate(Category category) {
// 初始化受影响行数为0
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
String sql="DELETE FROM category WHERE `category`.`CategoryID` = "+category.getCategoryID()+";";
// 执行语句,并返回结果
count=super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
// 返回受影响行数,判断是否执行成功
return count;
}
/**重写修改数据
* @param category 类别对象
*/
@Override
public int updateDate(Category category) {
// 初始化受影响行数为0
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
// UPDATE `category` SET `CategoryName` = '鞋袜' WHERE `category`.`CategoryID` = 12;
String sql="UPDATE `category` SET `CategoryName` = ? WHERE `CategoryID` = ?;";
String[] paras= {category.getCategoryName(),String.valueOf(category.getCategoryID())};
// 执行语句,并返回结果
count=super.exeUpdate(sql,paras);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
// 返回受影响行数,判断是否执行成功
return count;
}
/**重写多条件查询数据
*
* @param CategoryName 类别名称
* @return
*/
public List<Category> selectDateMore(String CategoryName){
// 创建类别集合
List<Category> categorys=new ArrayList<>();
try {
// SQL语句
String sql="SELECT * FROM `category` WHERE `CategoryName` LIKE '%"+CategoryName+"%';";
// 执行查询语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建对象emp类型
Category category =new Category();
// 获取的数据字段赋值给实体类
category.setCategoryID(rs.getInt("categoryId"));
category.setCategoryName(rs.getString("categoryName"));
// 类别对象添加到集合
categorys.add(category);
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return categorys;
}
/**重写编号查询数据
*
* @param CategoryID 类别编号
* @return
*/
@Override
public Category selectDateID(String CategoryID) {
// 初始化受影响行数为0
Category category = null;
try {
// SQL语句
String sql="SELECT * FROM `category` WHERE `CategoryID` = ?;";
// 执行查询语句
rs=super.exeQuery(sql,CategoryID);
while(rs.next()) {
// 创建对象emp类型
category =new Category();
// 获取的数据字段赋值给实体类
category.setCategoryID(rs.getInt("categoryId"));
category.setCategoryName(rs.getString("categoryName"));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return category;
}
/**重写名称查询数据
*
* @param categoryName 类别名称
* @return
*/
@Override
public Category selectDateName(String CategoryName) {
// 初始化受影响行数为0
Category category = null;
try {
// SQL语句
String sql="SELECT * FROM `category` WHERE `CategoryName` = ?;";
// 执行查询语句
rs=super.exeQuery(sql,CategoryName);
while(rs.next()) {
// 创建对象emp类型
category =new Category();
// 获取的数据字段赋值给实体类
category.setCategoryID(rs.getInt("categoryId"));
category.setCategoryName(rs.getString("categoryName"));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return category;
}
/**
* 重写查询所有数据信息
*/
@Override
public List<Category> selectAllDate(){
// 创建职员对象集合
List<Category> categorys= new ArrayList<>();
try {
// 获取SQL语句
String sql="SELECT * FROM `category`";
// 执行SQL语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建对象类型
Category category =new Category();
// 获取的数据字段赋值给实体类
category.setCategoryID(rs.getInt("categoryId"));
category.setCategoryName(rs.getString("categoryName"));
// 职员对象添加到集合中
categorys.add(category);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return categorys;
}
}
———————————————————————
package com.ruanyuan.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.ruanyuan.dao.EmployeeDAO;
import com.ruanyuan.entity.Employee;
public class EmployeeDAOImpl extends BaseDAO implements EmployeeDAO{
/**添加数据
*
* @param employee 对象
* @return
*/
@Override
public int insertDate(Employee employee) {
// TODO 自动生成的方法存根
// 受影响行数
int count=0;
try {
// sql语句
String sql="INSERT INTO `employee` (`EmployeeID`, `EmpName`, `EmpPwd`, `Sex`, `Age`, `HireLong`, `Salary`) VALUES (NULL, ?, ?, ?, ?, ?, ?);";
// 设置动态参数
String [] paras= {employee.getEmpName(),employee.getEmpPwd(),employee.getSex(),String.valueOf(employee.getAge()),employee.getHirelong(),employee.getSalary()};
// 执行sql
count =super.exeUpdate(sql, paras);
} catch (Exception e) {
// TODO: handle exception
}finally {
// 释放资源
super.closeResource();
}
return count;
}
/**重写删除数据
*
* @param employeeld 职员id
* @return
*/
@Override
public int deleteDate(int employeeld) {
// TODO 自动生成的方法存根
int count =0;
try {
// sql语句
String sql="DELETE FROM employee WHERE `EmployeeID` = "+employeeld+";";
// 执行sql语句
count = super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
}finally {
// 释放资源
super.closeResource();
}
return count;
}
/**修改数据
*
* @param empName 名称
* @param empPwd 密码
* @param empSex 性别
* @param empAge 年龄
* @param hireLong 聘用日期
* @param salary 月薪
* @param employeeID 编号
* @return
*/
@Override
public int updateDate(String empName, String empPwd, String empSex, String empAge, String hireLong ,String salary,String employeeID) {
// TODO 自动生成的方法存根
int count =0;
try {
// sql语句
String sql="UPDATE `employee` SET `EmpName` = '"+empName+"', "
+ " `EmpPwd` = '"+empPwd+"', `Sex` = '"+empSex+"', "
+ " `Age` = "+empAge+", `HireLong` = '"+hireLong+"',"
+ " `Salary` = "+salary+" WHERE `EmployeeID` = "+employeeID+";";
System.out.println(sql);
// 执行sql
count =super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
}finally {
// 释放资源
super.closeResource();
}
return count;
}
/**重写多条件查询
*
* @param empName 名字
* @param sex 性别
* @param beginAge 最小年龄范围
* @param endAge 最大年龄范围
* @param beginHireLong 开始入职时间
* @param endHireLong 结束入职时间
* @param beginSalary 开始薪水范围
* @param endSalary 结束薪水范围
* @return
*/
@Override
public List<Employee> selectDateMore(String empName, String sex, String beginAge, String endAge, String beginHireLong,String endHireLong, String beginSalary, String endSalary) {
// TODO 自动生成的方法存根
List<Employee> employees=new ArrayList<>();
try {
// sql语句
String sql="SELECT * FROM `employee` WHERE";
// 匆匆查询名称开始
if(empName!=null) {
sql=sql+" `EmpName` LIKE '%"+empName+"%'";
}
// 从查询性别开始
if(sex!=null && empName==null) {
sql=sql+" `Sex` = '"+sex+"'";
// 附加此条件
}else if(sex!=null && empName!=null ) {
sql=sql+" AND `Sex` = '"+sex+"'";
}
// 从查询年龄开始
if(beginAge!=null&& endAge!=null&& sex==null && empName==null) {
sql=sql+" `Age` BETWEEN "+beginAge+" AND "+endAge;
// 附加此条件
}else if((beginAge!=null&& endAge!=null&& sex==null) ||(beginAge!=null&& endAge!=null&&empName!=null)) {
sql=sql+" AND `Age` BETWEEN "+beginAge+" AND "+endAge;
}
// 从查询入职时间开始
if(beginHireLong!=null && endHireLong!=null && beginAge==null && endAge==null&& sex==null && empName==null) {
sql=sql+" `HireLong` BETWEEN '"+beginHireLong+"' AND '"+endHireLong+"'";
// 附加此条件
}else if((beginHireLong!=null && endHireLong!=null &&beginAge!=null && endAge!=null)||(beginHireLong!=null && endHireLong!=null &&sex!=null)||(beginHireLong!=null && endHireLong!=null &&empName!=null)) {
sql=sql+" AND `HireLong` BETWEEN '"+beginHireLong+"' AND '"+endHireLong+"'";
}
// 从查询薪水开始
if(beginSalary!=null &&endSalary!=null && beginHireLong==null && endHireLong==null && beginAge==null && endAge==null&& sex==null && empName==null) {
sql=sql+" `Salary` BETWEEN "+beginSalary+" AND "+endSalary;
// 附加此条件
}else if((beginSalary!=null &&endSalary!=null &&beginHireLong!=null &&endHireLong!=null)||(beginSalary!=null &&endSalary!=null &&beginAge!=null && endAge!=null)||(beginSalary!=null &&endSalary!=null &&sex!=null)||(beginSalary!=null &&endSalary!=null &&empName!=null)) {
sql=sql+" AND `Salary` BETWEEN "+beginSalary+" AND "+endSalary;
}
sql=sql+";";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建职员对象
Employee employee =new Employee();
// 设置对象值
employee.setEmployeeId(rs.getInt(1));
employee.setEmpName(rs.getString("empName"));
employee.setEmpPwd(rs.getString("empPwd"));
employee.setSex(rs.getString("Sex"));
employee.setAge(rs.getInt(5));
employee.setHirelong(rs.getString("hirelong"));
employee.setSalary(rs.getString("salary"));
// 对象添加到集合中
employees.add(employee);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return employees;
}
/**查询所有数据
*
* @return
*/
@Override
public List<Employee> SelectDateAll() {
// TODO 自动生成的方法存根
List<Employee> employees=new ArrayList<>();
try {
// sql语句
String sql="SELECT * FROM `employee`";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建职员对象
Employee employee =new Employee();
// 设置对象值
employee.setEmployeeId(rs.getInt(1));
employee.setEmpName(rs.getString("empName"));
employee.setEmpPwd(rs.getString("empPwd"));
employee.setSex(rs.getString("Sex"));
employee.setAge(rs.getInt(5));
employee.setHirelong(rs.getString("hirelong"));
employee.setSalary(rs.getString("salary"));
// 对象添加到集合中
employees.add(employee);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return employees;
}
/**通过id查询数据
*
* @param employeeld 职员对象
* @return
*/
@Override
public Employee selectDateID(int employeeld) {
// TODO 自动生成的方法存根
// 创建职员对象
Employee employee =null;
try {
// sql语句
String sql ="SELECT * FROM `employee` WHERE `EmployeeID` = "+employeeld+" ;";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建职员对象
employee =new Employee();
// 设置对象值
employee.setEmployeeId(rs.getInt(1));
employee.setEmpName(rs.getString("empName"));
employee.setEmpPwd(rs.getString("empPwd"));
employee.setSex(rs.getString("Sex"));
employee.setAge(rs.getInt(5));
employee.setHirelong(rs.getString("hirelong"));
employee.setSalary(rs.getString("salary"));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return employee;
}
/**重写通过名称查询数据
*
* @param employeeName 职员名称
* @return
*/
@Override
public Employee selectDateName(String employeeName) {
// TODO 自动生成的方法存根
Employee employee =null;
try {
// sql语句
String sql ="SELECT * FROM `employee` WHERE `EmpName` ='"+employeeName+"';";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建职员对象
employee =new Employee();
// 设置对象值
employee.setEmployeeId(rs.getInt(1));
employee.setEmpName(rs.getString("empName"));
employee.setEmpPwd(rs.getString("empPwd"));
employee.setSex(rs.getString("Sex"));
employee.setAge(rs.getInt(5));
employee.setHirelong(rs.getString("hirelong"));
employee.setSalary(rs.getString("salary"));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return employee;
}
}
————————————————————————
package com.ruanyuan.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.ruanyuan.dao.GoodDAO;
import com.ruanyuan.entity.Good;
public class GoodDAOImpl extends BaseDAO implements GoodDAO{
/**添加数据
*
* @param goods 供货商对象
* @return
*/
@Override
public int inserDate(Good goods) {
// TODO 自动生成的方法存根
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
String sql="INSERT INTO `goods` (`GoodID`, `GoodName`, `Price`, `CategoryID`, `OfferID`, `Stockes`) VALUES (NULL,'"+goods.getGoodName()+"',"+goods.getPrice()+","+goods.getCategoryID()+","+goods.getOfferID()+","+goods.getStockes()+");";
// 执行语句,并返回结果
count=super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
return count;
}
/**删除数据
*
* @param goods 供货商对象
* @return
*/
@Override
public int deleteDate(Good goods) {
// TODO 自动生成的方法存根
// 受影响行数
int count=0;
try {
// sql语句
String sql="DELETE FROM goods WHERE `goods`.`GoodID` = "+goods.getGoodID();
// 执行sql
count = super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
// 返回受影响行数
return count;
}
/**修改数据
*
* @param goods 供货商对象
* @return
*/
@Override
public int updateDate(Good goods) {
// TODO 自动生成的方法存根
int count=0;
try {
// sql语句
String sql="UPDATE `goods` SET `GoodName` = ?, `Price` = ?, `CategoryID` = ?, `Stockes` = ? WHERE `GoodID` = ?;";
// 存储动态参数
String[] paras = {goods.getGoodName(),String.valueOf(goods.getPrice()),String.valueOf(goods.getCategoryID()),String.valueOf(goods.getStockes()),String.valueOf(goods.getGoodID())};
// 执行sql语句
count=super.exeUpdate(sql,paras);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
return count;
}
/**多条件查询
*
* @param goodName 商品编号
* @param beginPrice 价格最小
* @param endPrice 价格最大
* @param CategoryID 类别编号
* @param OfferID 供货商编号
* @param beginStockes 库存最小
* @param endStockes 库存最大
* @return
*/
@Override
public List<Good> selectDateMore(String goodName, String beginPrice, String endPrice, String CategoryID, String OfferID, String beginStockes, String endStockes) {
// TODO 自动生成的方法存根
// 创建存储商品对象集合
List<Good> goods=new ArrayList<>();
try {
// sql语句
String sql="SELECT * FROM `goods` WHERE ";
// 条件从商品名称开始
if(goodName!=null) {
sql=sql+" `GoodName` LIKE '%"+goodName+"%'";
}
// 条件从价格开始
if(beginPrice!=null && endPrice!=null && goodName==null) {
sql=sql+" `Price` BETWEEN "+beginPrice+" AND "+endPrice;
// 附加此条件
}else if(beginPrice!=null && endPrice!=null && goodName!=null){
sql=sql+" AND `Price` BETWEEN "+beginPrice+" AND "+endPrice;
}
// 条件从类别编号开始
if(CategoryID!=null && goodName==null && beginPrice==null && endPrice==null) {
sql=sql+" `CategoryID` = "+CategoryID;
// 附加此条件
}else if((CategoryID!=null && goodName!=null)||(CategoryID!=null && beginPrice!=null && endPrice!=null)){
sql=sql+" AND `CategoryID` = "+CategoryID;
}
// 条件从供货商编号开始
if(OfferID!=null && goodName==null && beginPrice==null && endPrice==null && CategoryID==null ) {
sql=sql+" `OfferID` = "+OfferID;
// 附加此条件
}else if((OfferID!=null && goodName!=null)||(OfferID!=null && beginPrice!=null && endPrice!=null) || (OfferID!=null && CategoryID!=null)){
sql=sql+" AND `OfferID` = "+OfferID;
}
// 条件从库存开始
if(beginStockes!=null && goodName==null && beginPrice==null && endPrice==null && CategoryID==null && OfferID==null) {
sql=sql+" `Stockes` BETWEEN "+beginStockes+" AND "+endStockes;
// 附加此条件
}else if((beginStockes!=null && goodName==null)||(beginStockes!=null && beginPrice!=null && endPrice!=null) ||(beginStockes!=null && CategoryID!=null) || (beginStockes!=null && OfferID!=null)){
sql=sql+" AND `Stockes` BETWEEN "+beginStockes+" AND "+endStockes;
}
// 执行sql末尾
sql=sql+";";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
Good good=new Good();
// 获取数据字段赋值给实体类
good.setGoodID(rs.getInt(1));
good.setGoodName(rs.getString("GoodName"));
good.setPrice(rs.getInt(3));
good.setCategoryId(rs.getInt(4));
good.setOfferId(rs.getInt(5));
good.setStockes(rs.getInt(6));
// 对象添加到集合中
goods.add(good);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return goods;
}
/**ID查询
*
* @param goodID 商品编号
* @return
*/
@Override
public Good selectDateID(int goodID) {
// TODO 自动生成的方法存根
Good good=null;
try {
// sql语句
String sql="SELECT * FROM `goods` WHERE `GoodID` ="+goodID+";";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
good=new Good();
// 获取数据字段赋值给实体类
good.setGoodID(rs.getInt(1));
good.setGoodName(rs.getString("GoodName"));
good.setPrice(rs.getInt(3));
good.setCategoryId(rs.getInt(4));
good.setOfferId(rs.getInt(5));
good.setStockes(rs.getInt(6));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return good;
}
/**名称查询
*
* @param goodName 商品名称
* @return
*/
@Override
public Good selectDateName(String goodName) {
// TODO 自动生成的方法存根
// 创建商品实体类
Good good =new Good();
try {
// sql语句
String sql ="SELECT * FROM `goods` WHERE `GoodName` LIKE '%"+goodName+"%';";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 获取数据字段赋值给实体类
good.setGoodID(rs.getInt(1));
good.setGoodName(rs.getString("GoodName"));
good.setPrice(rs.getInt(3));
good.setCategoryId(rs.getInt(4));
good.setOfferId(rs.getInt(5));
good.setStockes(rs.getInt(6));
}
} catch (Exception e) {
// TODO: handle exception
}
return good;
}
/**查询所有数据
*
* @return
*/
@Override
public List<Good> selectAllDate() {
// TODO 自动生成的方法存根
// 创建上商品对象集合
List<Good> goods =new ArrayList<>();
try {
// sql语句
String sql="SELECT * FROM `goods` WHERE 1;";
// 执行sql语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建商品对象
Good good =new Good();
// 获取数据字段赋值给实体类
good.setGoodID(rs.getInt(1));
good.setGoodName(rs.getString("GoodName"));
good.setPrice(rs.getInt(3));
good.setCategoryId(rs.getInt(4));
good.setOfferId(rs.getInt(5));
good.setStockes(rs.getInt(6));
// 对象添加到集合
goods.add(good);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return goods;
}
/**重写通过类别编号查询信息
*
* @param categoryID 类别编号
* @return
*/
@Override
public Good selectDateCategoryID(int CategoryID) {
// 初始化受影响行数为0
Good good = null;
try {
// SQL语句
String sql="SELECT * FROM `goods` WHERE `CategoryID` = ?;";
// 执行查询语句
rs=super.exeQuery(sql,String.valueOf(CategoryID));
while(rs.next()) {
// 创建对象emp类型
good =new Good();
// 获取数据字段赋值给实体类
good.setGoodID(rs.getInt(1));
good.setGoodName(rs.getString("GoodName"));
good.setPrice(rs.getInt(3));
good.setCategoryId(rs.getInt(4));
good.setOfferId(rs.getInt(5));
good.setStockes(rs.getInt(6));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return good;
}
}
———————————————————————
package com.ruanyuan.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.ruanyuan.dao.OffersDAO;
import com.ruanyuan.entity.Good;
import com.ruanyuan.entity.Offers;
public class OffersDAOImpl extends BaseDAO implements OffersDAO{
/**重写添加数据
*
* @param offers 供货商对象
* @return
*/
@Override
public int inserDate(Offers offers) {
// TODO 自动生成的方法存根
// 初始化受影响行数为0
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
// INSERT INTO `offers` (`OfferID`, `OfferName`, `LegalIP`, `Address`, `Tel`) VALUES (NULL, '123', '123', '132', '132');
String sql="INSERT INTO `offers` (`OfferID`, `OfferName`, `LegalIP`, `Address`, `Tel`) VALUES (NULL,?,?,?,?);";
// 设置动态参数
String[] paras= {offers.getOfferName(),offers.getLegalIP(),offers.getAddress(),offers.getTel()};
// 执行语句,并返回结果
count=super.exeUpdate(sql,paras);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
// 返回受影响行数,判断是否执行成功
return count;
}
/**重写删除数据
*
* @param offers 供货商对象
* @return
*/
@Override
public int deleteDate(Offers offers) {
// TODO 自动生成的方法存根
// 初始化受影响行数为0
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
String sql="DELETE FROM offers WHERE `OfferID` = "+offers.getOfferID()+";";
// 执行语句,并返回结果
count=super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
// 返回受影响行数,判断是否执行成功
return count;
}
/**重写修改数据
*
* @param offers 供货商对象
* @return
*/
@Override
public int updateDate(Offers offers) {
// TODO 自动生成的方法存根
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
// UPDATE `offers` SET `OfferName` = '11111', `LegalIP` = '1111', `Address` = '1122', `Tel` = '122' WHERE `OfferID` = 10;
String sql="UPDATE `offers` SET ";
if(offers.getOfferName()!=null) {
sql=sql+" `OfferName` = \""+offers.getOfferName()+"\"";
}
if(offers.getLegalIP()!=null && offers.getOfferName()!=null){
sql=sql+", `LegalIP` = \""+offers.getLegalIP()+"\"";
}else if(offers.getLegalIP()!=null && offers.getOfferName()==null) {
sql=sql+" `LegalIP` = '"+offers.getLegalIP()+"\"";
}
if(offers.getAddress()!=null || offers.getLegalIP()!=null || offers.getOfferName()!=null){
sql=sql+", `Address` = \""+offers.getAddress()+"\"";
}else if(offers.getAddress()!=null && offers.getLegalIP()==null && offers.getOfferName()==null) {
sql=sql+" `Address` = \""+offers.getAddress()+"\"";
}
if(offers.getTel()!=null || offers.getAddress()!=null ||offers.getLegalIP()!=null || offers.getOfferName()!=null){
sql=sql+", `Tel` = \""+offers.getTel()+"\"";
}else if(offers.getTel()!=null || offers.getAddress()==null ||offers.getLegalIP()==null || offers.getOfferName()==null){
sql=sql+" `Tel` = \""+offers.getTel()+"\"";
}
sql=sql+" WHERE `OfferID` = \""+offers.getOfferID()+"\";";
// 执行语句,并返回结果
count=super.exeUpdate(sql);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
// 释放资源
super.closeResource();
}
// 返回受影响行数,判断是否执行成功
return count;
}
/**多条件查询
*
* @param offerName 供货商名称
* @param legalIP 法人代表
* @param address 公司地址
* @param tel 公司电话
* @return
*/
@Override
public List<Offers> selectDateMore(String offerName,String legalIP,String address,String tel) {
// TODO 自动生成的方法存根
// 创建存储供货商对象的集合
List<Offers> offers=new ArrayList<>();
try {
// SQL语句
String sql="SELECT * FROM `offers` WHERE";
// 如果供货商名称不为空加上此条件
if(offerName!=null) {
sql=sql+" `OfferName` LIKE '%"+offerName+"%'";
}
// 如果法人代表并且还有其他条件不为空不为空就附加上此条件
if(legalIP!=null && offerName!=null) {
sql=sql+" AND `LegalIP` LIKE '%"+legalIP+"%'";
// 如果只有目前一条条件,就不用加and
}else if(legalIP!=null && offerName==null) {
sql=sql+" `LegalIP` LIKE '%"+legalIP+"%'";
}
// 如果公司地址并且还有其他条件不为空不为空就附加上此条件
if((address!=null && offerName!=null) || (address!=null && legalIP!=null)) {
sql=sql+" AND `Address` LIKE '%"+address+"%'";
// 如果只有目前一条条件,就不用加and
}else if(address!=null && offerName==null && legalIP==null) {
sql=sql+" `Address` LIKE '%"+address+"%'";
}
// 如果公司电话并且还有其他条件不为空不为空就附加上此条件
if((tel!=null && offerName!=null) || (tel!=null && legalIP!=null) || (tel!=null && address!=null)) {
sql=sql+" AND `Tel` LIKE '%"+tel+"%'";
// 如果只有目前一条条件,就不用加and
}else if(tel!=null && offerName==null && legalIP==null && address==null) {
sql=sql+" `Tel` LIKE '%"+tel+"%'";
}
// 结束sql语句
sql=sql+";";
// 执行查询语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建对象emp类型
Offers offer =new Offers();
// 获取的数据字段赋值给实体类
offer.setOfferID(rs.getInt(1));
offer.setOfferName(rs.getString("offerName"));
offer.setLegalIP(rs.getString("legalIP"));
offer.setAddress(rs.getString("address"));
offer.setTel(rs.getString("tel"));
// 类别对象添加到集合
offers.add(offer);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return offers;
}
/**重写ID查询
*
* @param offerID 供货商编号
* @return
*/
@Override
public Offers selectDateID(int offerID) {
// TODO 自动生成的方法存根
// 初始化受影响行数为0
Offers offer = null;
try {
// SQL语句
String sql="SELECT * FROM `offers` WHERE `offerID` = ?;";
// 执行查询语句
rs=super.exeQuery(sql,String.valueOf(offerID));
while(rs.next()) {
// 创建对象emp类型
offer =new Offers();
// 获取的数据字段赋值给实体类
offer.setOfferID(rs.getInt(1));
offer.setOfferName(rs.getString("OfferName"));
offer.setLegalIP(rs.getString("LegalIP"));
offer.setAddress(rs.getString("Address"));
offer.setTel(rs.getString("Tel"));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return offer;
}
/**重写名称查询
*
* @param offersName 供货商名称
* @return
*/
@Override
public Offers selectDateName(String offersName) {
// TODO 自动生成的方法存根
// 初始化受影响行数为0
Offers offer = null;
try {
// SQL语句
String sql="SELECT * FROM `offers` WHERE `OfferName` = ?;";
// 执行查询语句
rs=super.exeQuery(sql,offersName);
while(rs.next()) {
// 创建对象emp类型
offer =new Offers();
// 获取的数据字段赋值给实体类
offer.setOfferID(rs.getInt(1));
offer.setOfferName(rs.getString("OfferName"));
offer.setLegalIP(rs.getString("LegalIP"));
offer.setAddress(rs.getString("Address"));
offer.setTel(rs.getString("Tel"));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return offer;
}
/**重写查询所有数据
*
* @return
*/
@Override
public List<Offers> selectAllDate() {
// TODO 自动生成的方法存根
// 创建存储供货商对象的集合
List<Offers> offers=new ArrayList<>();
try {
// 获取SQL语句
String sql="SELECT * FROM `offers`";
// 执行SQL语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建对象类型
Offers offer =new Offers();
// 获取的数据字段赋值给实体类
offer.setOfferID(rs.getInt(1));
offer.setOfferName(rs.getString("OfferName"));
offer.setLegalIP(rs.getString("LegalIP"));
offer.setAddress(rs.getString("Address"));
offer.setTel(rs.getString("Tel"));
// 职员对象添加到集合中
offers.add(offer);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return offers;
}
/**重写查询货商下是否含有销售信息,如果含有销售信息
*
* @param OfferID 商家编号
* @return
*/
@Override
public List<Good> selectSaleGood(int offerID){
// 创建存储供货商对象的集合
List<Good> goods=new ArrayList<>();
try {
// 获取SQL语句
String sql="SELECT * FROM `goods` WHERE `OfferID` = "+offerID+";";
// 执行SQL语句
rs=super.exeQuery(sql);
while(rs.next()) {
// 创建对象类型
Good good =new Good();
// 获取的数据字段赋值给实体类
good.setGoodID(rs.getInt(1));
good.setGoodName(rs.getString("GoodName"));
good.setPrice(rs.getInt(3));
good.setCategoryId(rs.getInt(4));
good.setOfferId(rs.getInt(5));
good.setStockes(rs.getInt(6));
// 职员对象添加到集合中
goods.add(good);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return goods;
}
}
--————————————————————————
package com.ruanyuan.dao.impl;
import java.util.ArrayList;
import java.util.List;
import com.ruanyuan.dao.SalesDAO;
import com.ruanyuan.entity.Sales;
public class SalesDAOImpl extends BaseDAO implements SalesDAO{
/**重写添加数据
*
* @param sales
* @return
*/
@Override
public int insetDate(Sales sales) {
int count = 0;
try {
// 连接数据库
super.getConnection();
// 获取SQL语句
// INSERT INTO `sales` (`SalesID`, `SellAmount`, `GoodId`, `EmployeeId`, `SellDate`) VALUES ('125', '100', '10', '1004', '2022-10-31 19:57:08.000000');
String sql="INSERT INTO `sales` (`SalesID`, `SellAmount`, `GoodId`, `EmployeeId`, `SellDate`) VALUES ("
+sales.getSalesld()
+","+sales.getSellAmount()
+","+sales.getGoodld()
+","+sales.getEmployeeld()
+","+sales.getSalesld()+");";
// 执行语句,并返回结果
count=super.exeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}finally {
super.closeResource();
}
return count;
}
/**重写删除数据
*
* @param sales
* @return
*/
@Override
public int deleteDate(Sales sales) {
int count=0;
try {
// sql语句
String sql="DELETE FROM sales WHERE `sales`.`SalesID` = "+sales.getSalesld()+";";
// 执行sql语句
count=super.exeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}finally {
super.closeResource();
}
return count;
}
/**重写修改数据
*
* @param sales
* @return
*/
@Override
public int updateDate(Sales sales) {
int count =0;
try {
// sql语句
String sql="UPDATE `sales` SET `SellAmount` = "+sales.getSellAmount()+", `GoodId` = "+sales.getGoodld()+", `EmployeeId` = "+sales.getEmployeeld()+" WHERE `SalesID` = "+sales.getSalesld()+";";
// 执行sql
count =super.exeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}
return count;
}
/**重写多条件查询
*
* @param beginSaleAmount 最小数量
* @param endSaleAmount 最大数量
* @param goodName 商品名称
* @param employeeName 职员姓名
* @param beginSaleDate 开始日期
* @param endSaleDate 结束日期
* @return
*/
@Override
public List<Sales> selectDateMore(String beginSaleAmount,String endSaleAmount,String goodName,String employeeName,String beginSaleDate,String endSaleDate) {
List<Sales> sales=new ArrayList<>();
try {
// sql语句
String sql="SELECT * FROM `sales` WHERE";
// 从销售数量开始查询
if(beginSaleAmount!=null && endSaleAmount!=null) {
sql=sql+" `SellAmount` BETWEEN "+beginSaleAmount+" AND "+endSaleAmount;
}
// 从商品名称开始查询
if(goodName!=null && beginSaleAmount==null && endSaleAmount==null) {
sql=sql+" `GoodId` ="+goodName;
// 附加商品名称
}else if(goodName!=null && beginSaleAmount!=null && endSaleAmount!=null) {
sql=sql+" AND `GoodId` ="+goodName;
}
// 从职员姓名开始查询
if(employeeName!=null && beginSaleAmount==null && endSaleAmount==null && goodName==null ) {
sql=sql+" `EmployeeId` ="+employeeName;
// 附加职员姓名
}else if((employeeName!=null && beginSaleAmount!=null && endSaleAmount!=null) || (employeeName!=null && goodName!=null) ) {
sql=sql+" AND `EmployeeId` ="+employeeName;
}
// 从聘用日期开始查询
if(beginSaleDate!=null && endSaleDate!=null && beginSaleAmount==null && endSaleAmount==null && goodName==null && employeeName==null) {
sql=sql+" `SellDate` BETWEEN '"+beginSaleDate+"' AND '"+endSaleDate+"'";
// 附加聘用日期
}else if((beginSaleDate!=null && endSaleDate!=null && beginSaleAmount!=null && endSaleAmount!=null) || (beginSaleDate!=null && endSaleDate!=null && goodName!=null)|| (beginSaleDate!=null && endSaleDate!=null && employeeName!=null)) {
sql=sql+" AND `SellDate` BETWEEN '"+beginSaleDate+"' AND '"+endSaleDate+"'";
}
// 结束sql
sql=sql+";";
// 执行sql语句
rs= super.exeQuery(sql);
while(rs.next()) {
// 创建销售类对象
Sales sale=new Sales();
// 设置对象值
sale.setSalesld(rs.getInt(1));
sale.setSellAmount(rs.getInt(2));
sale.setGoodld(rs.getInt(3));
sale.setEmployeeld(rs.getInt(4));
sale.setSellDate(rs.getString("SellDate"));
// 对象添加到集合
sales.add(sale);
}
} catch (Exception e) {
e.printStackTrace();
}
return sales;
}
/**重写通过id查询
*
* @param salesID
* @return
*/
@Override
public Sales selectDateID(int salesID) {
Sales sale=null;
try {
// sql语句
String sql="SELECT * FROM `sales` WHERE `SalesID`="+salesID+";";
// 执行sql语句
rs= super.exeQuery(sql);
while(rs.next()) {
// 创建销售类对象
sale=new Sales();
// 设置对象值
sale.setSalesld(rs.getInt(1));
sale.setSellAmount(rs.getInt(2));
sale.setGoodld(rs.getInt(3));
sale.setEmployeeld(rs.getInt(4));
sale.setSellDate(rs.getString("SellDate"));
}
} catch (Exception e) {
e.printStackTrace();
}
return sale;
}
/**重写查询所有数据
*
* @return
*/
@Override
public List<Sales> selectDateAll() {
// 创建存储对象的集合
List<Sales> sales= new ArrayList<>();
try {
// sql语句
String sql="SELECT * FROM `sales`";
// 执行sql语句
rs= super.exeQuery(sql);
while(rs.next()) {
// 创建销售类对象
Sales sale=new Sales();
// 设置对象值
sale.setSalesld(rs.getInt(1));
sale.setSellAmount(rs.getInt(2));
sale.setGoodld(rs.getInt(3));
sale.setEmployeeld(rs.getInt(4));
sale.setSellDate(rs.getString("SellDate"));
// 对象添加到集合
sales.add(sale);
}
} catch (Exception e) {
e.printStackTrace();
}
return sales;
}
/**通过职员id查询数据
*
* @param employeeld 职员对象
* @return
*/
@Override
public Sales selectDateEmployeeID(int employeeld) {
Sales sale=null;
try {
// sql语句
String sql="SELECT * FROM `sales` WHERE `EmployeeId`="+employeeld+";";
// 执行sql语句
rs= super.exeQuery(sql);
while(rs.next()) {
// 创建销售类对象
sale=new Sales();
// 设置对象值
sale.setSalesld(rs.getInt(1));
sale.setSellAmount(rs.getInt(2));
sale.setGoodld(rs.getInt(3));
sale.setEmployeeld(rs.getInt(4));
sale.setSellDate(rs.getString("SellDate"));
}
} catch (Exception e) {
e.printStackTrace();
}
return sale;
}
/**通过商品编号查询是否有销售信息
*
* @param goodID 商品编号
* @return
*/
public Sales selectDateGoodID(int goodID) {
Sales sale=null;
try {
// sql语句
String sql="SELECT * FROM `sales` WHERE `GoodId`="+goodID+";";
// 执行sql语句
rs= super.exeQuery(sql);
while(rs.next()) {
// 创建销售类对象
sale=new Sales();
// 设置对象值
sale.setSalesld(rs.getInt(1));
sale.setSellAmount(rs.getInt(2));
sale.setGoodld(rs.getInt(3));
sale.setEmployeeld(rs.getInt(4));
sale.setSellDate(rs.getString("SellDate"));
}
} catch (Exception e) {
e.printStackTrace();
}
return sale;
}
}