复习jdbc增删改查

最新推荐文章于 2024-10-05 22:51:28 发布

pursuit.198

最新推荐文章于 2024-10-05 22:51:28 发布

阅读量117

点赞数

文章标签： java 数据库 sql

本文链接：https://blog.csdn.net/qq_60465411/article/details/128371078

版权

jdbc笔记

分层：
表示层：用户交互、用户请求、响应
业务逻辑层：处理功能（复杂逻辑）
数据访问层：执行sql语句（增、删、改、查）

用户请求---》controller---》service---》dao---》db---》dao---》service---》controller---》jsp---》响应
------------------------------------------------------------------------------------------------------------

首先我们先预习一下BasDao

package com.bdqn.utli;

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

/**
 * @author xpc
 * @create 2022-12-16 14:10
 * 数据库操作类
 */
public class BaseDao {
    private static String dirver; //驱动
    private static String url; //连接
    private static String user; //用户名
    private static String pwd; //密码

    Connection connection =null; //数据库连接对象

    static {
        init();
    }

    /**
     * 初始化连接，读取配置文件内容
     */
    public static void init(){
        Properties properties =new Properties();
        String configFile="database.properties";
        InputStream is= BaseDao.class.getClassLoader().getResourceAsStream(configFile);
        try{
            properties.load(is);
        }
        catch (Exception e){    
            e.printStackTrace();
        }
        dirver =properties.getProperty("driver");
        url =properties.getProperty("url");
        user =properties.getProperty("username");
        pwd =properties.getProperty("password");

    }

    /**
     * 获取连接对象
     * @return 连接对象
     */
    public Connection getConnection(){
        try{
            if(null ==connection || connection.isClosed()){
                Class.forName(dirver); //加载驱动
                connection = DriverManager.getConnection(url,user,pwd);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭全部
     * @param conn 连接对象
     * @param stmt 执行对象
     * @param rs 结果集
     */
    public void clossALL(Connection conn, Statement stmt, ResultSet rs){
        if(null != rs){
            try{
                rs.close();
            }
            catch (Exception e){
                e.printStackTrace();
            }
        }
        if(null != stmt){
            try{
                stmt.close();
            }
            catch (Exception e){
                e.printStackTrace();
            }
        }
        if(null != conn){
            try{
                conn.close();
            }
            catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 增、删、改操作
     * @param sql sql语句
     * @param param 参数数组
     * @return 受影响行数
     */
    public int executeUpdate(String sql,Object[] param){
        int num =0; //受影响行数
        PreparedStatement pstmt =null;
        connection = getConnection(); // 获取连接对象
        try{
            pstmt =connection.prepareStatement(sql); //获得执行对象
            if(null!=param){
                for(int i=0;i<param.length;i++){
                    //占位符
                    pstmt.setObject(i+1,param[i]);
                }
            }
            num =pstmt.executeUpdate();//返回受影响行数
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            clossALL(connection,pstmt,null);
        }
        return num;
    }
}

database.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/hospital?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
username=root
password=123.com

解析BasDao

首先大家看上面的代码，首先我们创建四个私有的变量分分别对应database.properties里面的连接对象，创建一个connection作为数据库连接对象，在声明一个静态方法等待下面调用，创建init读取配置文件的方法，configFile来读取文件再来读取里面的内容变量和我们文件的内容相对应， getConnection获取连接对象如果值等于connection 并且 isClosed就加载驱动，clossALL为关闭对象连接，造成缓存这里按照先创建的最后管，后创建的先关，executeUpdate增删改操作， getConnection获取连接对象，prepareStatement获取执行对象和sql num返回受影响行数

了解完BasDao我们开始增删改操作

写增删改我们需要俩个Java包另一个使不使用都不重要

第二个mysql是必须要有的要不没有办法写增删改想要的可以私聊我，第一个我们后面再讲。

这里就不告诉大家怎莫导入了，前面的文章有

提供资源：

数据库和表

/*
SQLyog Professional v12.09 (64 bit)
MySQL - 5.7.27-log : Database - hospital
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`hospital` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `hospital`;

/*Table structure for table `checkitem` */

DROP TABLE IF EXISTS `checkitem`;

CREATE TABLE `checkitem` (
  `checkItemID` int(4) NOT NULL AUTO_INCREMENT COMMENT '检查项目编号',
  `checkItemName` varchar(50) NOT NULL COMMENT '检查项目名称',
  `checkItemCost` float NOT NULL COMMENT '检查项目价格',
  PRIMARY KEY (`checkItemID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='检查项目表';

/*Data for the table `checkitem` */

insert  into `checkitem`(`checkItemID`,`checkItemName`,`checkItemCost`) values (1,'血常规',30),(2,'尿常规',20),(3,'血脂、血糖检查',25),(4,'凝血五项',50),(5,'肺炎支、衣原体(快速)',66),(6,'胃镜',70),(7,'肠镜',70);

/*Table structure for table `department` */

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (
  `depID` int(4) NOT NULL AUTO_INCREMENT COMMENT '科室编号',
  `depName` varchar(50) NOT NULL COMMENT '科室名称',
  PRIMARY KEY (`depID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='科室表';

/*Data for the table `department` */

insert  into `department`(`depID`,`depName`) values (1,'急诊科'),(2,'呼吸科'),(3,'内科');

/*Table structure for table `department_checkitem` */

DROP TABLE IF EXISTS `department_checkitem`;

CREATE TABLE `department_checkitem` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '关系编号',
  `depID` int(4) NOT NULL COMMENT '科室编号',
  `checkItemID` int(4) NOT NULL COMMENT '检查项目编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='科室与检查项目关系表';

/*Data for the table `department_checkitem` */

insert  into `department_checkitem`(`id`,`depID`,`checkItemID`) values (1,1,1),(2,1,2),(3,2,1),(4,2,5),(5,3,1),(6,3,2),(7,3,3),(8,3,4);

/*Table structure for table `patient` */

DROP TABLE IF EXISTS `patient`;

CREATE TABLE `patient` (
  `patientID` int(4) NOT NULL AUTO_INCREMENT COMMENT '病人编号',
  `password` varchar(20) NOT NULL COMMENT '登录密码',
  `birthDate` varchar(50) DEFAULT NULL COMMENT '出生日期',
  `gender` varchar(4) NOT NULL DEFAULT '男' COMMENT '性别',
  `patientName` varchar(50) NOT NULL COMMENT '病人姓名',
  `phoneNum` varchar(50) DEFAULT NULL COMMENT '联系电话',
  `email` varchar(70) DEFAULT NULL COMMENT '邮箱',
  `identityNum` varchar(20) DEFAULT NULL COMMENT '身份证号',
  `address` varchar(255) DEFAULT '地址不详' COMMENT '地址',
  PRIMARY KEY (`patientID`),
  UNIQUE KEY `identityNum` (`identityNum`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='病人表';

/*Data for the table `patient` */

insert  into `patient`(`patientID`,`password`,`birthDate`,`gender`,`patientName`,`phoneNum`,`email`,`identityNum`,`address`) values (2,'1234567','1987-05-02','女','廖慧颖','13800000007','huiying@qq.com','220000198705022200','广州市'),(3,'890123','1975-03-02','男','李伟忠','13800000008','wz@qq.com','230000197503022300','沈阳市'),(4,'901234','1986-10-11','男','姚维新','13800000009','ywx@hotmail.com','310000198610113100','北京市'),(5,'012345','1975-03-04','男','陈建','138000000010','cz@qq.com','320000197503043200','北京市'),(6,'1234567','2020-09-09','男','张张张','138','123@163.com','123456789123456789','北京');

/*Table structure for table `prescription` */

DROP TABLE IF EXISTS `prescription`;

CREATE TABLE `prescription` (
  `examID` int(4) NOT NULL AUTO_INCREMENT COMMENT '检查编号',
  `patientID` int(4) NOT NULL COMMENT '病人编号',
  `depID` int(4) NOT NULL COMMENT '开处方的科室编号',
  `checkResult` varchar(500) DEFAULT NULL COMMENT '检查结果',
  `checkItemID` int(4) NOT NULL COMMENT '检查项目编号',
  `examDate` datetime NOT NULL COMMENT '检查日期',
  UNIQUE KEY `examID` (`examID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='处方表';

/*Data for the table `prescription` */

insert  into `prescription`(`examID`,`patientID`,`depID`,`checkResult`,`checkItemID`,`examDate`) values (1,1,1,'正常',1,'2020-01-02 00:00:00'),(2,1,1,'正常',2,'2020-01-02 00:00:00'),(3,3,2,'肺炎支原体阳性',5,'2020-04-05 00:00:00'),(4,1,1,'正常',1,'2020-02-06 00:00:00'),(5,8,3,'正常',4,'2020-03-02 00:00:00'),(6,8,3,'血糖偏高',3,'2020-03-02 00:00:00'),(7,8,3,'正常',1,'2020-03-02 00:00:00'),(8,10,3,'正常',3,'2020-03-02 00:00:00'),(9,1,1,'白细胞数量偏高',1,'2020-07-08 00:00:00');

/*Table structure for table `whiletest` */

DROP TABLE IF EXISTS `whiletest`;

CREATE TABLE `whiletest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

/*Data for the table `whiletest` */

insert  into `whiletest`(`id`,`val`) values (6,'0.7606394290924072'),(7,'0.8436362147331238'),(8,'0.8906109929084778'),(9,'0.9886881709098816'),(10,'0.5493205785751343'),(11,'0.8543413281440735'),(12,'0.20248331129550934'),(13,'0.44939255714416504'),(14,'0.639512836933136'),(15,'0.8493866920471191'),(16,'0.32839491963386536'),(17,'0.09381447732448578'),(18,'0.4838877022266388'),(19,'0.1379951685667038'),(20,'0.2383127510547638');

/* Procedure structure for procedure `proc_checkitem_insert` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_checkitem_insert` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_checkitem_insert`(
	in checkitems varchar(100)
    )
BEGIN
	declare comma_pos int;
	declare current_checkitem varchar(20);
	loop_label: loop
		set comma_pos =locate(',',checkitems);
		set current_checkitem =substr(checkitems,1,comma_pos-1);
		if current_checkitem <> '' then
			set checkitems =SUBSTR(checkitems,1,comma_pos+1);
		else
			SET current_checkitem =checkitems;
		end if;
		insert into checkitem(checkitemName,checkitemcost) values(current_checkitem,70);
		if comma_pos =0 or current_checkitem='' then
			leave loop_label;
		end if;
	end loop loop_label;
	
    END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_exam_getExamDat` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_exam_getExamDat` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_exam_getExamDat`(in patient_name varchar(50),in dep_id int,out last_exam_date datetime)
BEGIN
	declare patient_id int; #声明局部变量
	#根据条件病人姓名，查询病人编号赋值给变量patient_id
	select patientID,`patientName`  into patient_id from`patient` where patientName=patient_name;
	select patient_id; #输入病人id
	select max(examdate) into last_exam_date from `prescription`
	where patientID=patient_id and depID=dep_id;
END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_exam_getExamDate` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_exam_getExamDate` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_exam_getExamDate`(in patient_name varchar(50),in dep_id int,out last_exam_date datetime)
BEGIN
	declare patient_id int; #声明局部变量
	#根据条件病人姓名，查询病人编号赋值给变量patient_id
	select patientID  into patient_id from`patient` where patientName=patient_name;
	select patient_id; #输入病人id
	select max(examdate) into last_exam_date from `prescription`
	where patientID=patient_id and depID=dep_id;
END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_hospital_patient_count` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_hospital_patient_count` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_hospital_patient_count`()
begin
		select count(*) from patient;
	end */$$
DELIMITER ;

/* Procedure structure for procedure `proc_hospital_patient_count2` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_hospital_patient_count2` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_hospital_patient_count2`(out patientNum INT)
BEGIN
		#给变量patientNum赋值
		SELECT COUNT(*) into patientNum FROM patient;
	END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_hospital_patient_count3` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_hospital_patient_count3` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_hospital_patient_count3`(
		in patient_name varchar(50), #输入
		in dep_id int,               #输入
		out last_exam_date datetime)
BEGIN
		#声明局部变量
		declare patient_id int;
		#根据参数名称条件，查询病人id
		select patientID into patient_id from patient where patientName =patient_name;
		#输出病人id
		select patient_id;
		#根据科室id，输出最近一次检查时间
		select max(examDate) into last_exam_date from prescription 
		where patientID =patient_id and depID =dep_id;
	END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_patient_count001` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_patient_count001` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_patient_count001`()
begin #过程体卡开始
	select count(*) from patient;
end */$$
DELIMITER ;

/* Procedure structure for procedure `proc_patient_count002` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_patient_count002` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_patient_count002`( out  patientNum int )
begin
	select count(*) into  patientNum from patient;
end */$$
DELIMITER ;

/* Procedure structure for procedure `proc_pre_check_calsubsidy` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_pre_check_calsubsidy` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_pre_check_calsubsidy`(
	IN i_patientID INT,
	IN i_year VARCHAR(10),
	IN t_income INT,
	OUT o_subsidy FLOAT
	)
BEGIN
	DECLARE t_totalCost FLOAT;
	SELECT SUM(checkItemCost) INTO t_totalCost FROM prescription p1
	INNER JOIN checkitem ON p1.checkItemID = checkItem.`checkItemID`
	WHERE patientID =i_patientID AND
	examDate >=CONCAT(i_year,'-01-01') AND
	examDate <=CONCAT(i_year,'-12-31');
	
	IF t_income >=0 AND t_income <5000 THEN
	SET o_subsidy =t_totalCost *0.2;
	ELSEIF t_income >=5000 AND t_income <10000 THEN
	SET o_subsidy =t_totalCost *0.15;
	ELSEIF t_income >=10000 AND t_income <30000 THEN
	SET o_subsidy =t_totalCost *0.05;
	ELSE
	SET o_subsidy =0;
	END IF;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_pre_check_case` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_pre_check_case` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_pre_check_case`(
	IN i_patientID INT,
	IN i_year VARCHAR(10),
	IN t_income INT,
	OUT o_subsidy FLOAT
	)
BEGIN
	DECLARE t_totalCost FLOAT;
	SELECT SUM(checkItemCost) INTO t_totalCost FROM prescription p1
	INNER JOIN checkitem ON p1.checkItemID = checkItem.`checkItemID`
	WHERE patientID =i_patientID AND
	examDate >=CONCAT(i_year,'-01-01') AND
	examDate <=CONCAT(i_year,'-12-31');
	case
		when t_income>=0 and t_income <5000 then set o_subsidy=t_totalCost*0.2;
		WHEN t_income<10000  THEN SET o_subsidy=t_totalCost*0.15;
		WHEN t_income<30000  THEN SET o_subsidy=t_totalCost*0.05;
		WHEN t_income<0 AND t_income >30000 THEN SET o_subsidy=0;
	end case;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_test_insert` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_test_insert` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_test_insert`(in rows int)
BEGIN
	declare rand_val float;
	while rows>0 do
		select rand() into rand_val;
		insert into whileTest values(null,rand_val);
		set rows =rows -1;
	end while;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_test_insert2` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_test_insert2` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_test_insert2`(in rows int)
BEGIN
	declare rand_val float;
	 loop_label:while rows>0 do
		select rand() into rand_val;
		if rand_val<0.5 then
			iterate loop_label;
		end if;
		insert into whileTest values(null,rand_val);
		set rows =rows -1;
	end while loop_label;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `proc_test_while002` */

/*!50003 DROP PROCEDURE IF EXISTS  `proc_test_while002` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_test_while002`(in rows int)
BEGIN 
	DECLARE  rand_val float;
	select rows;
	while rows>0 do
	select rand() into rand_val;
	insert into whiletest values(null,rand_val);
	set rows=rows-1;
end while;
END */$$
DELIMITER ;

/*Table structure for table `v_patient_address` */

DROP TABLE IF EXISTS `v_patient_address`;

/*!50001 DROP VIEW IF EXISTS `v_patient_address` */;
/*!50001 DROP TABLE IF EXISTS `v_patient_address` */;

/*!50001 CREATE TABLE  `v_patient_address`(
 `姓名` varchar(50) ,
 `地址` varchar(255) 
)*/;

/*Table structure for table `v_pdc` */

DROP TABLE IF EXISTS `v_pdc`;

/*!50001 DROP VIEW IF EXISTS `v_pdc` */;
/*!50001 DROP TABLE IF EXISTS `v_pdc` */;

/*!50001 CREATE TABLE  `v_pdc`(
 `病人编号` int(4) ,
 `科室名` varchar(50) ,
 `检查项` varchar(50) ,
 `结果` varchar(500) 
)*/;

/*Table structure for table `view_patient` */

DROP TABLE IF EXISTS `view_patient`;

/*!50001 DROP VIEW IF EXISTS `view_patient` */;
/*!50001 DROP TABLE IF EXISTS `view_patient` */;

/*!50001 CREATE TABLE  `view_patient`(
 `patientID` int(4) ,
 `patientName` varchar(50) ,
 `address` varchar(255) 
)*/;

/*Table structure for table `view_patient_prescription` */

DROP TABLE IF EXISTS `view_patient_prescription`;

/*!50001 DROP VIEW IF EXISTS `view_patient_prescription` */;
/*!50001 DROP TABLE IF EXISTS `view_patient_prescription` */;

/*!50001 CREATE TABLE  `view_patient_prescription`(
 `patientID` int(4) ,
 `patientName` varchar(50) ,
 `checkResult` varchar(500) ,
 `examDate` datetime 
)*/;

/*View structure for view v_patient_address */

/*!50001 DROP TABLE IF EXISTS `v_patient_address` */;
/*!50001 DROP VIEW IF EXISTS `v_patient_address` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_patient_address` AS select `patient`.`patientName` AS `姓名`,`patient`.`address` AS `地址` from `patient` */;

/*View structure for view v_pdc */

/*!50001 DROP TABLE IF EXISTS `v_pdc` */;
/*!50001 DROP VIEW IF EXISTS `v_pdc` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_pdc` AS select `p`.`patientID` AS `病人编号`,`d`.`depName` AS `科室名`,`c`.`checkItemName` AS `检查项`,`p`.`checkResult` AS `结果` from ((`prescription` `p` join `department` `d`) join `checkitem` `c`) where ((`p`.`depID` = `d`.`depID`) and (`c`.`checkItemID` = `p`.`checkItemID`)) */;

/*View structure for view view_patient */

/*!50001 DROP TABLE IF EXISTS `view_patient` */;
/*!50001 DROP VIEW IF EXISTS `view_patient` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_patient` AS select `patient`.`patientID` AS `patientID`,`patient`.`patientName` AS `patientName`,`patient`.`address` AS `address` from `patient` */;

/*View structure for view view_patient_prescription */

/*!50001 DROP TABLE IF EXISTS `view_patient_prescription` */;
/*!50001 DROP VIEW IF EXISTS `view_patient_prescription` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_patient_prescription` AS select `p`.`patientID` AS `patientID`,`p`.`patientName` AS `patientName`,`t`.`checkResult` AS `checkResult`,`t`.`examDate` AS `examDate` from (`patient` `p` join `prescription` `t`) where (`t`.`patientID` = `p`.`patientID`) */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

创建实体类

代码

package com.bdqn.entity;

import java.util.Date;

public class Patient {
    private int patientID;
    private  String password;
    private String birthDate;
    private String gender;
    private String patientName;
    private String phoneNum;
    private String email;
    private String identityNum;
    private String address;

    public int getPatientID() {
        return patientID;
    }

    public void setPatientID(int patientID) {
        this.patientID = patientID;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(String birthDate) {
        this.birthDate = birthDate;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getPatientName() {
        return patientName;
    }

    public void setPatientName(String patientName) {
        this.patientName = patientName;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getIdentityNum() {
        return identityNum;
    }

    public void setIdentityNum(String identityNum) {
        this.identityNum = identityNum;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

创建dao接口和实现

接口代码

package com.bdqn.dao;

import com.bdqn.entity.Patient;

public interface IPatientDao {
    //增加
    public int insertPatient(Patient patient);
    //删除
    public  int updatePatient(Patient patient);
    //修改
    public  int deletePatient(int patientID);
}

实现

实现接口代码

package com.bdqn.dao.impl;

import com.bdqn.dao.IPatientDao;
import com.bdqn.entity.Patient;
import com.bdqn.utli.BaseDao;

public class PatientDaoImpl extends BaseDao implements IPatientDao {
    @Override
    public int insertPatient(Patient patient) {
        String sql="insert into patient(password,birthDate,gender,patientName,phoneNum,email,identityNum,address)values(?,?,?,?,?,?,?,?)";
        Object[] param={patient.getPassword(),patient.getBirthDate(),patient.getGender(),patient.getPatientName(),patient.getPhoneNum(),patient.getEmail(),patient.getIdentityNum(),patient.getAddress()};
        return this.executeUpdate(sql,param);
    }

    @Override
    public int updatePatient(Patient patient) {
        String sql="update patient set password=? where patientID=?";
        Object[] param={patient.getPassword(),patient.getPatientID()};
        return this.executeUpdate(sql,param);
    }

    @Override
    public int deletePatient(int patientID) {
        String sql="delete from patient where patientID=?";
        Object[] param={patientID};
        return this.executeUpdate(sql,param);
    }
}

创建service层

service接口

service接口代码

package com.bdqn.service;

import com.bdqn.entity.Patient;

public interface IPatientService  {
    //添加
    public  boolean savePatient(Patient patient);
    //修改
    public  boolean updatePatient(Patient patient);
    //删除
    public  boolean deletePatient(int patientID);
}

service实现

代码

package com.bdqn.service.impl;

import com.bdqn.dao.IPatientDao;
import com.bdqn.dao.impl.PatientDaoImpl;
import com.bdqn.entity.Patient;
import com.bdqn.service.IPatientService;

public class PatientServiceImpl implements IPatientService {
    IPatientDao iPatientDao=new PatientDaoImpl();
    @Override
    public boolean savePatient(Patient patient) {
        boolean flag=false;
        if (iPatientDao.insertPatient(patient)>0){
            flag=true;
        }
        return flag;
    }

    @Override
    public boolean updatePatient(Patient patient) {
        boolean flag=false;
        if (iPatientDao.updatePatient(patient)>0){
            flag=true;
        }
        return flag;
    }

    @Override
    public boolean deletePatient(int patientID) {
        boolean flag=false;
        if (iPatientDao.deletePatient(patientID)>0){
            flag=true;
        }
        return flag;
    }
}

添加测试类

添加

代码

package com.bdqn.demo;

import com.bdqn.entity.Patient;
import com.bdqn.service.IPatientService;
import com.bdqn.service.impl.PatientServiceImpl;

public class Test {
    public static void main(String[] args)
{
        IPatientService iPatientService=new PatientServiceImpl();
        Patient patient=new Patient();
        patient.setPassword("1234567");
        patient.setBirthDate("2020-09-09");
        patient.setEmail("123@163.com");
        patient.setGender("男");
        patient.setAddress("北京");
        patient.setPatientName("张张张");
        patient.setPhoneNum("138");
        patient.setIdentityNum("123456789123456789");
        if (iPatientService.savePatient(patient)){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }

    }
}

添加删除

删除代码

package com.bdqn.demo;

import com.bdqn.entity.Patient;
import com.bdqn.service.IPatientService;
import com.bdqn.service.impl.PatientServiceImpl;

public class Test02 {
    public static void main(String[] args) {
        IPatientService iPatientService=new PatientServiceImpl();

        if (iPatientService.deletePatient(1)){
            System.out.println("成功");
        }else {
            System.out.println("失败");
        }
    }
}

修改

修改代码

package com.bdqn.demo;

import com.bdqn.entity.Patient;
import com.bdqn.service.IPatientService;
import com.bdqn.service.impl.PatientServiceImpl;

public class Test03 {
    public static void main(String[] args) {
        IPatientService iPatientService = new PatientServiceImpl();
        Patient patient = new Patient();
        patient.setPatientID(2);
        patient.setPassword("1234567");
        if (iPatientService.updatePatient(patient)) {
            System.out.println("成功");
        } else {
            System.out.println("失败");
        }
    }
}