商城项目之jdk8+jdbc+mariadb

╰+哭是因爲堅強的太久メ 2022-06-10 05:36 109阅读 0赞

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 添加商品到数据库t_goods表 * @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 更改商品信息到数据库t_goods表 * @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 从数据库goods表中-刪除商品 * @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.商城主页面  
![这里写图片描述][SouthEast]  
2.商品列表  
![这里写图片描述][SouthEast 1]  
3.售出列表  
![这里写图片描述][SouthEast 2]

#### （三）篇后感 ####

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

[SouthEast]: /images/20220610/32494e855be74d6991bff6cf0200d0a6.png
[SouthEast 1]: /images/20220610/e41c0c27f1b640588c27d8efdfb3d7c3.png
[SouthEast 2]: /images/20220610/4140129b3698447f8bfe010cfeca1bf8.png
[https_github.com_shiyuan2he_framework.git]: https://github.com/shiyuan2he/framework.git