products.java

来自「网络商店源代码用NetBeans6.1Beta开发」· Java 代码 · 共 95 行

package model;

import java.util.*;
import java.sql.*;
import model.db.*;

public class Products {

    private static final String table = "products";

    static void createTable() throws Exception {
        final String table_def =
                "id INT AUTO_INCREMENT NOT NULL," + "description TEXT," + "price DECIMAL(9,2)," + "PRIMARY KEY(id)";

        Connection cx = Database.connect();
        Statement st = cx.createStatement();

        String sql_op = "DROP TABLE IF EXISTS " + table;

        System.out.println("=> " + sql_op);
        st.executeUpdate(sql_op);

        sql_op = "CREATE TABLE " + table + "(" + table_def + ")";

        System.out.println("=> " + sql_op);
        st.executeUpdate(sql_op);
    }

    static void create(Product n) throws Exception {
        Connection cx = Database.connect();
        String sql_op = "INSERT INTO " + table + "(description,price)" + " VALUES (?, ?)";

        System.out.println("=> " + sql_op);

        PreparedStatement pst = cx.prepareStatement(sql_op);

        pst.setString(1, n.getDescription());
        pst.setBigDecimal(2, n.getPrice());

        pst.executeUpdate();
    }

    static Product find(int id) throws Exception {
        Connection cx = Database.connect();
        Statement st = cx.createStatement();

        String query = "SELECT * FROM " + table + " WHERE id=" + id;

        ResultSet rs = st.executeQuery(query);
        if (!rs.next()) {
            return null;
        }

        return new Product(
                rs.getString("description"),
                rs.getBigDecimal("price"),
                rs.getInt("id"));
    }


    static List<Product> findAll() throws Exception {
        Connection cx = Database.connect();
        Statement st = cx.createStatement();

        String query = "SELECT * FROM " + table;

        System.out.println(query);

        ResultSet rs = st.executeQuery(query);

        List<Product> L = new ArrayList<Product>();
        while (rs.next()) {
            L.add(new Product(
                    rs.getString("description"),
                    rs.getBigDecimal("price"),
                    rs.getInt("id")));
        }
        return L;
    }

    static void updateRecord(int id, String price) throws Exception {
        Connection cx = Database.connect();

        String sql_op = "UPDATE " + table + " SET price = ? WHERE id=" + id;

        PreparedStatement pst = cx.prepareStatement(sql_op);

        pst.setString(1, price);

        pst.executeUpdate();


    }
}