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 /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 /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;}}}}
