商城项目之jdk8+jdbc+mariadb

jdk8+jdbc连接数据库

目录

前言 什么是JDBC

维基百科的简介：
　　Java 数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。
　　简单地说，就是用于执行SQL语句的一类Java API，通过JDBC使得我们可以直接使用Java编程来对关系数据库进行操作。通过封装，可以使开发人员使用纯Java API完成SQL的执行。

（一）准备工作

1.创建数据库

DROP TABLE IF EXISTS `t_goods`;
CREATE TABLE `t_goods` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '商品名称',
  `price` double(10,0) DEFAULT NULL COMMENT '价钱',
  `number` int(11) DEFAULT NULL COMMENT '数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_r_goods_salesperson`;
CREATE TABLE `t_r_goods_salesperson` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `goods_id` bigint(20) NOT NULL COMMENT '商品表外键',
  `salesperosn_id` bigint(20) NOT NULL COMMENT '销售员外键',
  `number` int(10) NOT NULL COMMENT '销售量',
  `sales_time` datetime NOT NULL COMMENT '商品卖出时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_salesperson`;
CREATE TABLE `t_salesperson` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `name` varchar(4) DEFAULT NULL COMMENT '销售员姓名',
  `password` varchar(30) DEFAULT NULL COMMENT '销售员密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.导包

![enter description here][1]

3.实体

public class Goods {
    private Integer id ;
    private String name ;
    private Double price ;
    private Integer number ;
    ....
省略构造器，setter，getter
}
public class GoodsSalesperson {
    private Integer id ;
    private Integer goodsId ;
    private Integer salespersonId ;
    private Integer number ;
    private Date salesTime ;
    ....
    省略构造器，setter，getter
}
public class Salesperson {
    private Integer id ;
    private String name ;
    private String password ;
    ....
    省略构造器，setter，getter
}

4.建立连接

public class DBUtils {
    public static Connection getConnetction() {
        Connection conn = null;
        String user   = "root";
        String passwd = "htbuy@2016";
        String url = "jdbc:mariadb://192.168.6.101:3306/test_shopping";
        try {
            //Class.forName("oracle.jdbc.driver.OracleDriver");
            Class.forName("org.mariadb.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url,user,passwd);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void closeResource(PreparedStatement pstmt, Connection conn) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void closeResource(PreparedStatement pstmt, ResultSet rs, Connection conn) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        try {
            if (rs != null ) {
                rs.close();
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5.insert

 /**
   * @description <p>添加商品到数据库t_goods表</p>
   * @author heshiyuan
   * @date 2017/7/20 11:39
   * @param goods 商品对象
   */
  public boolean addGoods(Goods goods) {
      boolean bool = false;
      conn = DBUtils.getConnetction();
      String sql = "INSERT INTO t_GOODS(id,NAME,PRICE,number) VALUES(?,?,?,?)";
      try {
          pstmt = conn.prepareStatement(sql);
          pstmt.setInt(1, goods.getId());
          pstmt.setString(2, goods.getName());
          pstmt.setDouble(3, goods.getPrice());
          pstmt.setInt(4, goods.getNumber());
          int rs = pstmt.executeUpdate();
          if (rs > 0) {
              bool = true;
          }
      } catch (SQLException e) {
          e.printStackTrace();
      } finally {
          DBUtils.closeResource(pstmt, conn);
      }
      return bool;
  }

6.update

/** @description <p>更改商品信息到数据库t_goods表</p>
     * @author heshiyuan
     * @param key   选择要更改商品信息
     * @param goods 商品对象
     * @date 2017/7/20 11:39
     */
    public boolean updateGoods(int key, Goods goods) {
        boolean bool = false;
        conn = DBUtils.getConnetction();
        switch (key) {
            case 1:        //   key=1,更改商品名称
                String sqlName = "UPDATE t_goods SET NAME=? WHERE ID=?";
                try {
                    pstmt = conn.prepareStatement(sqlName);
                    pstmt.setString(1, goods.getName());
                    pstmt.setLong(2, goods.getId());
                    int rs = pstmt.executeUpdate();
                    if (rs > 0) {
                        bool = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DBUtils.closeResource(pstmt, conn);
                }
                break;
            case 2:        //   key=2,更改商品价格
                String sqlPrice = "UPDATE t_goods SET PRICE=? WHERE ID=?";
                try {
                    conn = DBUtils.getConnetction();
                    pstmt = conn.prepareStatement(sqlPrice);
                    pstmt.setDouble(1, goods.getPrice());
                    pstmt.setLong(2, goods.getId());
                    int rs = pstmt.executeUpdate();
                    if (rs > 0) {
                        bool = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DBUtils.closeResource(pstmt, conn);
                }
                break;
            case 3:        //   key=3,更改商品数量
                String sqlNum = "UPDATE t_goods SET number=? WHERE ID=?";
                try {
                    conn = DBUtils.getConnetction();
                    pstmt = conn.prepareStatement(sqlNum);
                    pstmt.setInt(1, goods.getNumber());
                    pstmt.setLong(2, goods.getId());
                    int rs = pstmt.executeUpdate();
                    if (rs > 0) {
                        bool = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DBUtils.closeResource(pstmt, conn);
                }
                break;
            default:
                break;
        }
        return bool;
    }

7.select

    public  List<Map<String,Object>> dailyGsales() {
        List<Map<String,Object>> returnMapList = new ArrayList<>();
        conn = DBUtils.getConnetction();
        String sql = "SELECT" +
                " gs.sales_time as salesTime," +
                " gs.id as id," +
                " g.`name` as goodsName," +
                " s.`name` as salesName," +
                " g.price as price," +
                " gs.number as count," +
                " g.price * g.number as total" +
                " FROM" +
                " t_r_goods_salesperson gs left join t_salesperson s on gs.salesperosn_id = s.id" +
                " left join t_goods g on gs.goods_id = g.id" +
                ";";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String,Object> returnMap = new HashMap<>() ;
                returnMap.put("saleTime",rs.getTimestamp(1));
                returnMap.put("id",rs.getInt(2));
                returnMap.put("goodsName",rs.getString(3));
                returnMap.put("salesName",rs.getString(4));
                returnMap.put("price",rs.getDouble(5));
                returnMap.put("count",rs.getInt(6));
                returnMap.put("total",rs.getDouble(7));
                returnMapList.add(returnMap) ;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeResource(pstmt, rs, conn);
        }
        return returnMapList;
    }

8.delete

    /**
     * @description <p>从数据库goods表中-刪除商品</p>
     * @author heshiyuan
     * @date 2017/7/20 11:38
     */
    public boolean deleteGoods(int id) {
        boolean bool = false;
        String sql = "DELETE FROM t_GOODS WHERE ID=?";
        try {
            conn = DBUtils.getConnetction();
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1,id);
            int rs = pstmt.executeUpdate();
            if (rs > 0) {
                bool = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeResource(pstmt, conn);
        }
        return bool;
    }

（二）项目演示

1.商城主页面

2.商品列表

3.售出列表

（三）篇后感

目前的项目功能并不丰富，简单的jdbc增删改查，此项目会继续维护，
项目源码托管在GitHub：https://github.com/shiyuan2he/framework.git
后续会增加jdbc增删改查的封装（思路是java 反射），添加jdbc事务支持，aop切面拦截
持续更新中，敬请期待