新增一个患者表：患者id，患者名称，既往病史,新增一个挂号表：挂号id，患者id，本次病情

1.新增一个患者表：患者id，患者名称，既往病史

2.新增一个挂号表：挂号id，患者id，本次病情

3.新增一个药品表：药品id,药品名称，单次用量，一天几次

4.新增一个挂号药品中间表：本次看病的使用药物

id,挂号id 患者id 药品id 数量

作业要求：

完成患者的新增——返回id

完成挂号的新增——返回id

完成患者的开药——循环开药{1.感冒通 2.皮炎片}

package com.jdbc.day04.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;


/**
 * @Author Jin XueYang
 * @Date 2022/3/22
 */
public class SqlDataUtils {
    public static String url = "jdbc:mysql://localhost:3307/schooldb?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8";
    public static String username = "root";
    public static String pwd = "123456";

    //加载驱动，静态代码块只执行一次，驱动加载一次就可以
    static {
        try {
            Properties properties = new Properties();
            InputStream resourceAsStream = SqlDataUtils.class.getResourceAsStream("db.properties");
            properties.load(resourceAsStream);
            String classname = properties.get("db.className").toString();
            url = properties.get("db.url").toString();
            username = properties.get("db.username").toString();
            pwd = properties.get("db.password").toString();
            Class.forName(classname);
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }


    //获取数据库连接
    public Connection getCon() throws SQLException {
        return DriverManager.getConnection(url, username, pwd);
    }

    public int exUpdate(String sql, Object... obj) {
        Connection con = null;
        PreparedStatement pt = null;
        ResultSet res = null;
        try {
            con = this.getCon();
            pt = con.prepareStatement(sql);
            for (int i = 1; i <= obj.length; i++) {
                pt.setObject(i, obj[i - 1]);
            }
            int i = pt.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close(res, pt, con);
        }
        return -1;
    }

    //仅仅返回新增主键而服务
    public int insertForkey(String sql, Object... obj) {
        Connection con = null;
        PreparedStatement pt = null;
        ResultSet res = null;
        try {
            con = this.getCon();
            pt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 1; i <= obj.length; i++) {
                pt.setObject(i, obj[i - 1]);
            }
            int i = pt.executeUpdate();
            res = pt.getGeneratedKeys();

            if (res.next()) {
                return res.getInt(1);
            }
            return 0;//代表新增成功但是没有拿到主键
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close(res, pt, con);
        }
        return -1;//代表失败
    }

    public List exQuery(String sql, Object... obj) {
        Connection con = null;
        PreparedStatement pt = null;
        ResultSet res = null;
        try {
            con = this.getCon();
            pt = con.prepareStatement(sql);
            for (int i = 1; i <= obj.length; i++) {
                pt.setObject(i, obj[i - 1]);
            }
            res = pt.executeQuery();
            ResultSetMetaData metaData = res.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<Object> list = new ArrayList<>();
            while (res.next()) {
                Map<Object, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(metaData.getColumnName(i), res.getObject(i));
                }
                list.add(map);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close(res, pt, con);
        }
        return null;
    }

    //关闭资源
    public void close(ResultSet result, PreparedStatement state, Connection con) {
        try {
            if (result != null) {
                result.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (state != null) {
                state.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

package com.jdbc.day03;

import com.jdbc.day03.utils.SqlDataUtils;

import java.util.List;
import java.util.Map;
import java.util.Scanner;

/**
 * @Author Jin XueYang
 * @Date 2022/3/24
 */
public class Medicinal {
    public static void main(String[] args) {
        SqlDataUtils es = new SqlDataUtils();
        Scanner scanner = new Scanner(System.in);
        String sqlPatient = "insert into hospital_patient values(null,?,?)";
        int key = es.insertForkey(sqlPatient,"李飞","yes");
        String sqlRegister = "insert into hospital_register values(null,?,?)";
        int key1 = es.insertForkey(sqlRegister,key,"伤口发炎");

        while(true){
            System.out.println("请输入开药名称");
            String mName = scanner.next();
            System.out.println("请输入药品的数量");
            int num = scanner.nextInt();
            String check = "select * from hospital_drugs where dname=?";
            List<Map> list = es.exQuery(check, mName);
            if(list!=null&list.size()>0){
                Map map = list.get(0);
                Object did = map.get("did");
                String sqlMiddle = "insert into hospital_reg_drugs values(null,?,?,?,?) ";
                es.exUpdate(sqlMiddle,key1,key,did,num);
            }else{
                break;
            }
        }
    }
}