专业实训第三天 java+数据库单表+小型项目实训+简单商品管理平台

商品管理平台

数据库

数据库：GoodsDB
表：Goods
列：

列名	类型	属性1	属性2
gid	int	主键	自 增
gname	varchar	20	非空
gprice	int	–	非空
gdetail	varchar	50	非空

开发步骤流程

1. 创建项目GoodsSys
2. 导入jar工具包
3. 创建包dao
4. 创建并完成实体类Goods
5. 创建并完成工具类BaseDao
   
   1. getCon()
   2. closeAll()
   3. execute()
6. 创建并完成数据访问类GoodsDao
   
   1. getAll（）
   2. add（）
   3. update（）
   4. delete（）
7. 创建界面类page
   
   1. mainPage()
   2. getAll()
   3. add()
   4. update()
   5. delete()
8. 创建测试类，并调用主页mainPage方法

代码

创建数据库

DROP DATABASE IF EXISTS GoodsDB;

CREATE DATABASE GoodsDB;

USE GoodsDB;

CREATE TABLE Goods(
    gid INT PRIMARY KEY AUTO_INCREMENT,
    gname VARCHAR(20) NOT NULL,
    gprice INT NOT NULL,
    gdetail VARCHAR(50) NOT NULL
);

INSERT INTO Goods VALUES(NULL,'book',10,'it is very good!')

SELECT * FROM Goods

实体类Goods

public class Goods {
    private int id;
    private String name;
    private int price;
    private String detail;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getPrice() {
        return price;
    }
    public void setPrice(int price) {
        this.price = price;
    }
    public String getDetail() {
        return detail;
    }
    public void setDetail(String detail) {
        this.detail = detail;
    }
    public Goods(int id, String name, int price, String detail) {
        super();
        this.id = id;
        this.name = name;
        this.price = price;
        this.detail = detail;
    }
    public Goods() {
        super();
    }
}

工具类BaseDao

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/GoodsDb";
    private String name = "root";
    private String pwd = "123456";

    public Connection getCon(){
        Connection con = null;      
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url,name,pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public void closeAll(Connection con,PreparedStatement ps,ResultSet rs){
        try {
            if(rs != null){
                rs.close();
            }
            if(ps != null){
                ps.close();
            }
            if(con != null){
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int execute(String sql ,Object ... param){
        int res = 0;
        Connection con = null;
        PreparedStatement ps = null;
        con = this.getCon();
        try {
            ps = con.prepareStatement(sql);
            int i = 1;
            for ( Object ob : param) {
                ps.setObject(i, ob);
                i++;
            }
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(con, ps, null);
        }
        return res;
    }
}

数据访问类GoodsDao

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class GoodsDao extends BaseDao{
    public ArrayList<Goods> getAll(){
        ArrayList<Goods> list = new ArrayList<Goods>();
        String sql = "select * from Goods";
        BaseDao bd = new BaseDao();
        Connection con = bd.getCon();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Goods g = new Goods(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4));
                list.add(g);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            bd.closeAll(con, ps, rs);
        }
        return list;
    }

    public int add(Goods good){
        String sql = "insert into Goods values(null,?,?,?)";
        return this.execute(sql, good.getName(),good.getPrice(),good.getDetail());
    }

    public int updata(Goods good){
        String sql = "update Goods set gname = ?,gprice = ?,gdetail=? where gid = ?";
        return this.execute(sql, good.getName(),good.getPrice(),good.getDetail(),good.getId());
    }

    public int delete(int id){
        String sql = "delete from Goods where gid = ?";
        return this.execute(sql, id);
    }
}

页面类Page

import java.util.ArrayList;
import java.util.Scanner;

public class Page {
    Scanner sin = new Scanner(System.in);
    GoodsDao gd = new GoodsDao();
    public void pageMain() {
        System.out.println("***********************************************");
        System.out.println("**************简单商品管理系统******************");
        System.out.println("***********************************************");

        while(true){
            System.out.println("***********************************************");
            System.out.println("1.查询商品");
            System.out.println("2.添加商品");
            System.out.println("3.修改商品");
            System.out.println("4.删除商品");
            System.out.println("5.退出系统");
            System.out.println("***********************************************");
            System.out.println("please choice id");
            int cs = sin.nextInt();
            if(cs == 1){
                getAll();
            } else if (cs == 2){
                add();
            } else if (cs == 3){
                updata();
            } else if (cs == 4){
                delete();
            } else if (cs == 5){
                break;
            } else {
                System.out.println("your choice not right!paleace reput!");
            }
        }
    }
    public void getAll(){
        ArrayList<Goods> list = gd.getAll();
        System.out.println("***********************************************");
        System.out.println("id \t name \t price \t detail");
        System.out.println("***********************************************");
        for (Goods g : list) {
            System.out.print(g.getId()+"\t");
            System.out.print(g.getName()+"\t");
            System.out.print(g.getPrice()+"\t");
            System.out.print(g.getDetail()+"\n");
        }
        System.out.println("***********************************************");
    }
    public void add(){
        System.out.println("pleace input name:");
        String name  = sin.next();

        System.out.println("pleace input price:");
        int price  = sin.nextInt();

        System.out.println("pleace input detail:");
        String detail  = sin.next();

        int res = gd.add(new Goods(0,name,price,detail));
        if(res == 1){
            System.out.println("add success!");
        } else {
            System.out.println("add error");
        }
    }

    public void updata(){
        System.out.println("pleace input id:");
        int id  = sin.nextInt();

        System.out.println("pleace input name:");
        String name  = sin.next();

        System.out.println("pleace input price:");
        int price  = sin.nextInt();

        System.out.println("pleace input detail:");
        String detail  = sin.next();

        int res = gd.updata(new Goods(id,name,price,detail));
        if(res == 1){
            System.out.println("updata success!");
        } else {
            System.out.println("updata error");
        }
    }
    public void delete(){
        System.out.println("pleace input id:");
        int id  = sin.nextInt();
        int res = gd.delete(id);
        if(res == 1){
            System.out.println("delete success!");
        } else {
            System.out.println("delete error");
        }
    }
}

测试类Text

public class Text {
    public static void main(String[] args) {
        Page p = new Page();
        p.pageMain();
    }
}

end

运用的东西并不难，也不是很多，都是比较基础的，还有部分是以前没有见过的，比如万能增删改模板，主要学习项目的主要流程，部分代码风格，以及企业代码技巧。