Thêm category mới

Câu lệnh SQL

INSERT INTO CATEGORIES VALUES(NULL, ?, ?)

Thực thi

public void insert() {
        // TODO Auto-generated method stub
        String sql = "INSERT INTO CATEGORIES VALUES(NULL, ?, ?)";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, "category name");
            stmt.setString(2, "url thumbnail");

            stmt.execute();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Cập nhật category

Câu lệnh SQL

UPDATE CATEGORIES SET name = ?, thumbnail = ? WHERE id = ?

public void update() {
        // TODO Auto-generated method stub
        String sql = "UPDATE CATEGORIES SET name = ?, thumbnail = ? WHERE id = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, "category name");
            stmt.setString(2, "url thumbnail");
            stmt.setInt(3, 1); //1 là id của category
            return stmt.execute();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Xoá category

Câu lệnh SQL

DELETE FROM CATEGORIES WHERE ID = ?

public boolean delete(int id) {
        // TODO Auto-generated method stub
        String sql = "DELETE FROM CATEGORIES WHERE ID = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, id); //id la id của category cần xoá

            return stmt.execute();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

Tìm kiếm category theo id

Câu lệnh SQL

SELECT * FROM CATEGORIES WHERE ID = ?

public void find(int id) {
        // TODO Auto-generated method stub
        String sql = "SELECT * FROM CATEGORIES WHERE ID = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, id); //id la id của category cần tìm

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString("name");
                String thumbnail = rs.getString("thumbnail");
                System.out.format("%id:%s:%s", id, name, thumbnail).println();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

Lấy toàn bộ categories

Câu lệnh SQL

SELECT * FROM CATEGORIES

public void findAll() {
        // TODO Auto-generated method stub
        List<Category> cateList = new ArrayList<>();
        String sql = "SELECT * FROM CATEGORIES";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String thumbnail = rs.getString("thumbnail");

                System.out.format("%id:%s:%s", id, name, thumbnail).println();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return cateList;
    }