商城项目之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切面拦截
持续更新中，敬请期待