MYSQL-JDBC数据库实战-宠物商店

 

 dao/AccountDao

package com.xk.jdbc.dao;

public interface AccountDao {
    /**
     * 增加账单
     *
     */
    int addCount(Integer deal_type, Integer pet_id,Integer buyer_id,Integer seller_id,Integer price);
}

dao/BaseDao

package com.xk.jdbc.dao;

import org.apache.commons.beanutils.BeanUtils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class BaseDao {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    private static Connection connection;
    private static Statement sm;
    private static PreparedStatement pst;
    private static ResultSet rs;

    static {
        init();
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public static void init(){
        //创建Propeities对象  保存 配置文件中的信息
        Properties properties = new Properties();
        //加载配置文件到输入流中
        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("database.properties");
        //执行properties的load方法，加载配置文件
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        password = properties.getProperty("password");
    }
    public static Connection getconnection(){
        try {
            connection = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static <T> List<T> executeQuery(String sql, List list,Class<T> Clazz){
        getconnection();
        List<T> tList = new ArrayList<>();
        try {
            //通过prepareStatement预编译sql
            pst = connection.prepareStatement(sql);
            //判断list中是否存在 需要进入sql语句的参数
            if(list != null){
                //通过循环将参数载入prepareStatement中
                for (int i = 0; i <list.size(); i++) {
                    pst.setObject((i+1),list.get(i));
                }
            }
            rs = pst.executeQuery();
            //获取查询后的虚拟结果集的结构信息
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                //实例化对象（clazz是什么类型 obj就是什么类型）
                T obj = Clazz.newInstance();
                for (int i = 0; i <rsmd.getColumnCount() ; i++) {
                    //获取数据库列名
                    String colName = rsmd.getColumnName(i+1);
                    //获取数据库值
                    Object value = rs.getObject(colName);
                    //调用导入的包下的方法，传入对象，列名，值，会拿列名与对象中的属性对比找到与列名相同名的属性，将值赋给该对象的属性
                    BeanUtils.setProperty(obj,colName,value);
                }
                tList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return tList;
    }

    public static ResultSet showExecuteQuery(String sql) {
        getconnection();
        try {
            // 通过 prepareStatement 预编译SQL
            sm = connection.createStatement();
            rs = sm.executeQuery(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    public static int executeUpdate(String sql,List list){
        getconnection();
        int row = 0;
        try {
            connection.setAutoCommit(false);
            //通过prepareStatement预编译sql
            pst = connection.prepareStatement(sql);
            //判断list集合中是否存在 需要进入sql语句的参数
            if (null != list  && list.size() != 0){
                //通过循环 将参数写入prepareStatement中
                for (int i = 0; i <list.size() ; i++) {
                    pst.setObject((i+1),list.get(i));
                }
            }
            row = pst.executeUpdate();
            connection.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                connection.rollback();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            closeAll();
        }
        return row;
    }

    public static void closeAll(){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

dao/PetDao

package com.xk.jdbc.dao;

import com.xk.jdbc.entity.Pet;

import java.util.List;

public interface PetDao {

    /**
     * 查询所有的宠物
     */
    List<Pet> queryPetList(Integer pet_id,String type_name,Integer health,Integer love,String birthday,Integer owner_id,Integer store_id,Integer pet_money,int sell);

    /**
     * 修改宠物信息
     */
    int revisePet(String name, String whatinformation,Integer value,String id,Integer number);

    /**
     * 增加宠物
     */
    int addPet(String petType,String name,Integer health,Integer love,Integer store_id,Integer pet_money);
}

 dao/PetMasterDao

package com.xk.jdbc.dao;

import com.xk.jdbc.entity.PetMaster;

import java.util.List;

public interface PetMasterDao {
    /**
     * 查询所有的宠物主人
     */
    List<PetMaster> queryPetMasterList(Integer owner_id,String masterName,String masterPwd,Integer masterMoney);


}

 dao/PetShopDao

package com.xk.jdbc.dao;

import com.xk.jdbc.entity.PetShop;

import java.util.List;

public interface PetShopDao {
    /**
     * 查询所有的宠物商店
     */
    List<PetShop> queryPetShopList( Integer store_id,String name,String passwor,Integer balance);

    /**
     * 开宠物商店
     */
    int openShop(String name,String passwd,Integer money);
}

dao/impl/Accountimpl

package com.xk.jdbc.dao.impl;

import com.xk.jdbc.dao.AccountDao;
import com.xk.jdbc.dao.BaseDao;

import java.util.ArrayList;
import java.util.List;

public class Accountimpl implements AccountDao {
    @Override
    public int addCount(Integer deal_type, Integer pet_id,Integer buyer_id,Integer seller_id,Integer price) {
        List list = new ArrayList();
        String sql = "INSERT into account(deal_type,pet_id,buyer_id,seller_id,price) VALUES (?,?,?,?,?)";
        list.add(deal_type);
        list.add(pet_id);
        list.add(buyer_id);
        list.add(seller_id);
        list.add(price);
        int index = BaseDao.executeUpdate(sql,list);
        return 0;
    }
}

 dao/impl/PetDaoimpl

package com.xk.jdbc.dao.impl;

import com.xk.jdbc.dao.BaseDao;
import com.xk.jdbc.dao.PetDao;
import com.xk.jdbc.entity.Pet;

import java.util.ArrayList;
import java.util.List;

public class PetDaoimpl implements PetDao {

    /**
     * 查询所有的宠物
     */
    @Override
    public List<Pet> queryPetList(Integer pet_id, String type_name, Integer health, Integer love, String birthday,
                                  Integer owner_id, Integer store_id, Integer pet_money,int sell) {
        List list = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select pet.*, ");
        sql.append("petmaster.masterName, ");
        sql.append("from pet ");
        sql.append("right JOIN petmaster ");
        sql.append("on petmaster.owner_id = pet.owner_id ");
        sql.append("where 1=1 ");
        if (null != pet_id){
            sql.append("and pet_id = ?");
            list.add(pet_id);
        }
        if (null != type_name){
            sql.append("and type_name like ?");
            list.add("%"+type_name+"%");
        }
        if (null != pet_id){
            sql.append("and health = ?");
            list.add(health);
        }
        if (null != pet_id){
            sql.append("and love = ?");
            list.add(love);
        }
        if (null != pet_id){
            sql.append("and birthday like ?");
            list.add("%"+birthday+"%");
        }
        if (null != pet_id){
            sql.append("and owner_id = ?");
            list.add(owner_id);
        }
        if (null != pet_id){
            sql.append("and store_id = ?");
            list.add(store_id);
        }
        if (null != pet_id){
            sql.append("and pet_money = ?");
            list.add(pet_money);