学习JDBC—Dao模式

DAO:Data Access Object: 数据存取对象。关系型数据 对象

DAO是一个操作数据库的设计模式(DAO是一种访问数据库的设计模式，大概的思路就是将对一个数据库（和一个数据表）的操作封装到一个类中，由其他的类来调用这个类的方法完成对数据库的操作，这样可以简化负责业务处理的类的复杂性和耦合性)

工程结构

以商城商品分类的操作为例

entity包:实体类

dao包：

        dao接口

        impl：实现类包        

                实现dao接口的类

测试包：

        测试类

util包：

        工具集

工具类：//将对数据库的基本操作封装成工具类，方便多次使用

public class DbHelper {
    private static String url;
    private static String user;
    private static String pwd;
    
    static {
        Properties properties=new Properties();
        InputStream inputStream=DbHelper.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(inputStream);
            url= properties.getProperty("url");
            user= properties.getProperty("user");
            pwd= properties.getProperty("pwd");
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    
    private static Connection getCon() throws SQLException {
        return DriverManager.getConnection(url,user,pwd);
    }
    
    public static boolean update(String sql,Object...params){
        Connection connection=null;
        PreparedStatement statement=null;
        int n=0;
        try {
            connection=getCon();
            statement= connection.prepareStatement(sql);
            setParams(statement, params);
            n=statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            
        }
        return n>0?true:false;
    }
    public static List<Map<String,Object>> query(String sql,Object...params){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Map<String,Object>> list=new ArrayList<>();
        try {
            connection=getCon();
            statement= connection.prepareStatement(sql);
            setParams(statement,params);
            resultSet= statement.executeQuery();
            ResultSetMetaData resultSetMetaData= resultSet.getMetaData();
            while (resultSet.next()){
                Map<String,Object> rowMap=new HashMap<>();
                for (int i = 1; i <=resultSetMetaData.getColumnCount() ; i++) {
                    rowMap.put(resultSetMetaData.getColumnLabel(i),resultSet.getObject(i)==null?" ":resultSet.getObject(i));
                }
                list.add(rowMap);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(connection, statement, resultSet);
        }
        return list;
    }
    private static void closeAll(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        if (resultSet !=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        if (statement !=null){
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        if (connection !=null){
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    private static void setParams(PreparedStatement statement, Object[] params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i+1, params[i]);
        }
    }
}

Category实体类：//该类中只有属性值，并且属性值对应数据表中的所有字段

public class Category {
    private int categoryId;
    private int parentId;
    private String categoryName;
    public int getCategoryId() {
        return categoryId;
    }
    public void setCategoryId(int categoryId) {
        this.categoryId = categoryId;
    }
    public int getParentId() {
        return parentId;
    }
    public void setParentId(int parentId) {
        this.parentId = parentId;
    }
    public String getCategoryName() {
        return categoryName;
    }
    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }
}

     dao接口：接口中有对商城分类数据库进行操作的方法

public interface CategoryDao {
    boolean insert (Category category);
    boolean delete(int id);
    boolean update(Category category);
    Map<String,Object> queryById(int id);
    List<Map<String,Object>> queryAll();
}

   dao接口实现类：封装了对商城分类数据库的具体方法

public class CategoryDaoImpl implements CategoryDao {
    @Override
    public boolean insert(Category category) {
        String sql="insert into tb_category(category_name,parent_id) values(?,?)";
        return DbHelper.update(sql,category.getCategoryName(),category.getParentId());
    }
    @Override
    public boolean delete(int id) {
        String sql="delete from tb_category where category_id=?";
        return DbHelper.update(sql,id);
    }
    @Override
    public boolean update(Category category) {
        String sql="update tb_category set category_name=? where category_id=?";
        return DbHelper.update(sql,category.getCategoryName(),category.getCategoryId());
    }
    @Override
    public Map<String, Object> queryById(int id) {
        String sql="select category_id,category_name,parent_id from tb_category where category_id=?";
        List<Map<String,Object>> list=DbHelper.query(sql,id);
        return list.get(0);
    }
    @Override
    public List<Map<String, Object>> queryAll() {
        String sql="select category_id,category_name,parent_id from tb_category ";
        return DbHelper.query(sql);
    }
}