学习java—第四十三天学习笔记

2019.8.28 NIIT第四十三天 今天是个综合案例

软件开发流程

1. 需求分析--软件需要实现什么功能
2. 原型设计--设计页面--axure 墨刀
3. 数据库设计--创建库--创建表--约束
4. 项目框架搭建--使用分层思想
   1. 在开发工具IDEA中新建项目
   2. 加入需要的jar包
   3. 使用分层的思想
      1. dao层
      2. service层
      3. Test层
5. 编码--coding
6. 测试--循环修改

重点

如果出现操作需要执行多个SQL语句时，需要考虑是否需要用到事务 被放入到事务中的操作，在dao层需要将异常抛出到service层进行捕获 事务所用的Connection需要从ThreadLocal中获取 public static ThreadLocal local=new ThreadLocal<>();

案例代码

domain(Bean类)

package com.igeek.domain;

public class Product {
    private int pid;
    private String pname;
    private int price;
    private String flag;
    private String category_id;

    public Product(String pname, int price, String flag, String category_id) {
        this.pname = pname;
        this.price = price;
        this.flag = flag;
        this.category_id = category_id;
    }

    @Override
    public String toString() {
        return "Product{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", price=" + price +
                ", flag='" + flag + '\'' +
                ", category_id='" + category_id + '\'' +
                '}';
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public String getFlag() {
        return flag;
    }

    public void setFlag(String flag) {
        this.flag = flag;
    }

    public String getCategory_id() {
        return category_id;
    }

    public void setCategory_id(String category_id) {
        this.category_id = category_id;
    }

    public Product() {
    }

    public Product(int pid, String pname, int price, String flag, String category_id) {

        this.pid = pid;
        this.pname = pname;
        this.price = price;
        this.flag = flag;
        this.category_id = category_id;
    }
}

Utils工具类

package com.igeek.utils;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCTools {

    private static DruidDataSource dataSource;

    public static ThreadLocal<Connection> local=new ThreadLocal<>();

    //1.获取连接池
    public static DataSource getDataSource(){
        init();
        return  dataSource;
    }


    //2.获取连接
    public static Connection getConnection(){
        init();
        try {
            return  dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //3.关闭连接
    public static void closeConn(Connection conn, Statement st){
        try {
            if(conn!=null){
                if(st!=null){
                    st.close();
                }
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    //加载配置文件
    public static void init(){
        try {
            //1.从属性文件中获取连接信息
            Properties ps = new Properties();
            ps.load(new FileInputStream("src/db.properties"));
            dataSource = new DruidDataSource();

            dataSource.setDriverClassName(ps.getProperty("driverClassName"));
            dataSource.setUrl(ps.getProperty("url"));
            dataSource.setUsername(ps.getProperty("username"));
            dataSource.setPassword(ps.getProperty("password"));
            dataSource.setMaxActive(Integer.parseInt(ps.getProperty("maxActive")));
            dataSource.setMaxWait(Integer.parseInt(ps.getProperty("maxWait")));
            dataSource.setInitialSize(Integer.parseInt(ps.getProperty("initialSize")));
        }catch (Exception e){
            e.printStackTrace();
        }

    }

}

Test层

package com.igeek.test;

import com.igeek.dao.ProductDao;
import com.igeek.domain.Product;
import com.igeek.service.ProdectService;

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

public class TestProdect {
    static ProdectService ps=new ProdectService();
    public static void main(String[] args) {
        while(true){
            System.out.println("请输入操作命令:");
            System.out.println("C：创建、U：修改、D：删除、DA：批量删除、I：通过id查询、FA：查询所有、Q：退出");
            Scanner sr=new Scanner(System.in);
            String code=sr.next().toUpperCase();
            //根据命令判断操作
            switch(code){
                case "C":
                    addProduct();
                    break;
                case "U":
                    //修改商品
                    editProduct();
                    break;
                case "D":
                    //删除商品
                    deleteById();
                    break;
                case "DA":
                    //批量删除
                    deleteAllProduct();
                    break;
                case "I":
                    //通过id查询
                    selectById();
                    break;
                case "FA":
                    //查询所有
                    findAll();
                    break;
                case "Q":
                    System.out.println("操作结束");
                    System.exit(0);
                    break;
                default:
                    System.out.println("输入错误请重新输入");
                    break;
            }
        }

    }

    private static void deleteAllProduct() {
        System.out.println("进入批量删除模式");
        List<Integer> list=new ArrayList<>();
        Scanner sr6=new Scanner(System.in);
        while(true){
            System.out.println("请输入需要删除的商品ID，输入-1退出");
            int pid=sr6.nextInt();
            if(pid==-1){
                break;
            }else{
                list.add(pid);
            }
        }
        System.out.println("是否确认删除以上所有商品(Y/N)");
        String flag=sr6.next();
        if(flag.equalsIgnoreCase("Y")){
            ps.deleteAll(list);
            System.out.println("批量删除完成");
        }
    }

    private static void findAll() {
        List<Product> list=ps.findAll();
        if(list!=null){
            for (Product p:list){
                System.out.println(p);
            }
        }else{
            System.out.println("");
        }
    }

    private static void selectById() {
        System.out.println("请输入商品ID:");
        Scanner sc4=new Scanner(System.in);
        int pid=sc4.nextInt();
        Product p=ps.findProductById(pid);
        if(p!=null){
            System.out.println(p);
        }else{
            System.out.println("未能找到该商品");
        }
    }

    private static void deleteById() {
        System.out.println("请输入商品ID:");
        Scanner sc3=new Scanner(System.in);
        int pid=sc3.nextInt();
        Product p=ps.findProductById(pid);
        if(p!=null){
            //删除商品
            System.out.println("是否确认删除Y/N");
            String flag =sc3.next();
            if(flag.equals("Y")){
                ps.deleteProductById(pid);
                System.out.println("删除成功");
            }
        }else{
            System.out.println("商品不存在");
        }
    }

    private static void editProduct() {
        System.out.println("请输入商品ID:");
        Scanner sc2=new Scanner(System.in);
        int id=sc2.nextInt();
        Product p=ps.findProductById(id);
        if(p!=null){
            //让用户输入商品信息进行修改
            System.out.println("请输入产品名称");
            String name=sc2.next();
            System.out.println("请输入产品价格");
            int price=sc2.nextInt();
            p.setPname(name);
            p.setPrice(price);
            ps.editProduct(p);
            System.out.println("修改成功");
        }else{
            System.out.println("该商品不存在");
        }
    }

    private static void addProduct() {
        Scanner sc1=new Scanner(System.in);
        System.out.println("请输入产品名称");
        String name=sc1.next();
        System.out.println("请输入产品价格");
        int price=sc1.nextInt();
        Product p=new Product(name,price,"1","c004");
        ps.addProdect(p);
        System.out.println("添加成功");
    }
}

Service层

package com.igeek.service;

import com.igeek.dao.ProductDao;
import com.igeek.domain.Product;
import com.igeek.utils.JDBCTools;
import org.apache.commons.dbutils.DbUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class ProdectService {
    ProductDao dao=new ProductDao();
    public void addProdect(Product p) {
        dao.save(p);
    }

    public Product findProductById(int id) {
        return dao.findById(id);
    }

    public void editProduct(Product p) {
        dao.update(p);
    }

    public void deleteProductById(int pid) {
        try {
            dao.delete(pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public List<Product> findAll() {

        return dao.findAll();
    }

    public void deleteAll(List<Integer> list) {
        Connection conn=null;
        try {
            conn=JDBCTools.getConnection();
            JDBCTools.local.set(conn);
            conn.setAutoCommit(false);
            for (Integer pid:list) {
                dao.delete(pid);
            }
            DbUtils.commitAndClose(conn);
        } catch (SQLException e) {
            try {
                DbUtils.rollbackAndClose(conn);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
    }
}

dao层

package com.igeek.dao;

import com.igeek.domain.Product;
import com.igeek.utils.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class ProductDao {
    QueryRunner qr=new QueryRunner(JDBCTools.getDataSource());
    String sql="";
    public void save(Product p) {
        sql="insert into product values (?,?,?,?,?)";
        try {
            qr.update(sql,null,p.getPname(),p.getPrice(),p.getFlag(),p.getCategory_id());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Product findById(int id) {
        sql="select * from product where pid = ?";
        try {
            //不出异常返回结果对象
            return qr.query(sql,new BeanHandler<>(Product.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //如果抛出异常则返回null
        return null;
    }

    public void update(Product p) {
        sql="update product set pname=?,price=?,flag=?,category_id=? where pid = ?";
        try {
            qr.update(sql,p.getPname(),p.getPrice(),p.getFlag(),p.getCategory_id(),p.getPid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(int pid) throws SQLException {
        sql="delete from product where pid=?";
        qr.update(JDBCTools.local.get(),sql,pid);
    }


    public List<Product> findAll() {
        sql="select * from product";
        try {
            return qr.query(sql,new BeanListHandler<>(Product.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}