ChⅥ 数据库应用编程

// 实际开发中，密码不应该明文存储。通常应使用配置文件、环境变量或密钥管理服务。public class ProcessAccountData {    private static final String SQL_URL =            "jdbc:mysql://localhost:3306/free_chat?serverTimezone=GMT&useSSL=false&allowPublicKeyRetrieval=true";    private static final String DB_USER = "root";    private static final String DB_PASSWORD = "<password>";    /**     * @apiNote 从数据库读取账号数据，写入 validUsers     * @param validUsers 要写入账号数据的 HashMap     * @throws IOException     */    public void readAccountFile(ConcurrentHashMap<String, User> validUsers) {        try {            Class.forName("com.mysql.cj.jdbc.Driver");        } catch (ClassNotFoundException e) {            e.printStackTrace();            return;        }        try (Connection conn = DriverManager.getConnection(SQL_URL, DB_USER, DB_PASSWORD);                Statement stmt = conn.createStatement();                ResultSet rs = stmt.executeQuery("SELECT account, pwd FROM accounts")) {            while (rs.next()) {                User user = new User(rs.getString("account"), rs.getString("pwd"));                validUsers.put(rs.getString("account"), user);            }        } catch (SQLException e) {            e.printStackTrace();        }    }    /**     * @apiNote 注册时，将 userId 和 pwd 写入数据库     * @param userId 用户 id     * @param pwd 用户密码     * @throws IOException     */    public void writeAccountFile(String userId, String pwd) {        String sql = "INSERT INTO accounts (account, pwd) VALUES (?, ?)";        try (Connection conn = DriverManager.getConnection(SQL_URL, DB_USER, DB_PASSWORD);                PreparedStatement pstmt = conn.prepareStatement(sql)) {            pstmt.setString(1, userId);            pstmt.setString(2, pwd);            pstmt.executeUpdate();        } catch (SQLException e) {            e.printStackTrace();        }    }}

-- 创建函数CREATE [ OR REPLACE ] FUNCTION name([ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ])[ RETURNS retype | RETURNS TABLE ( column_name column_type [, ...] ) ]AS $$         -- $$ 用于声明函数体的开始DECLARE  -- 声明段BEGIN  -- 函数体语句END;$$ LANGUAGE lang_name;  -- LANGUAGE 后面指明所用的过程语言-- 删除函数DROP FUNCTION [ IF EXISTS ] name( [ [ argmode ] [ argname ] argtype [, ...] ] )[ CASCADE | RESTRICT ];

variable_name data_type;

variable_name RECORD;

DECLARE  counter integer;  rec RECORD;

IF condition THEN  statement;END IF;IF condition THEN  statement;ELSE  statement;END IF;IF condition1 THEN  statement;ELSIF condition2 THEN  statement;ELSE  statement;END IF;

LOOP  statement;END LOOP [label];LOOP  i = i + 1;  EXIT WHEN i > 10;END LOOP;LOOP  i = i + 1;  EXIT WHEN i > 100;  CONTINUE WHEN i < 50;  j = j + 1;END LOOP;WHILE condition LOOP  statement;END LOOP;FOR i IN 1..10 LOOP  RAISE NOTICE 'i = %', i;END LOOP;CREATE FUNCTION out_record() RETURNS RECORD AS $$DECLARE  rec RECORD;BEGIN  FOR rec IN SELECT * FROM student LOOP    RAISE NOTICE '学生数据: %, %', rec.studentID, rec.studentName;  END LOOP;  RETURN rec;END;$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION count_records()RETURNS integer AS $$DECLARE  student_count integer;BEGIN  SELECT count(*) INTO student_count FROM student;  -- INTO 用于将查询结果赋值给变量  RETURN student_count;END;$$ LANGUAGE plpgsql;SELECT count_records();

CREATE OR REPLACE FUNCTION get_credit()  RETURNS TABLE(    vsid CHAR,    vsname VARCHAR,    vscredit BIGINT  )AS $vscredit$BEGIN  RETURN QUERY  SELECT    c.studentid AS vsid,    c.studentname AS vsname,    SUM(a.credit) AS vscredit  FROM course AS a  JOIN grade AS b ON a.courseid = b.courseid  JOIN student AS c ON b.studentid = c.studentid  WHERE b.grade >= 60  GROUP BY c.studentid, c.studentname;END;$vscredit$ LANGUAGE plpgsql;SELECT * FROM get_credit();

CREATE OR REPLACE PROCEDURE add_data(a integer, b integer, INOUT c integer)AS $$BEGIN  c = a + b;END;$$ LANGUAGE plpgsql;

-- 创建表CREATE TABLE stu_score (  sid character(10) NOT NULL,  cid character(10) NOT NULL,  score numeric(5, 1),  CONSTRAINT stu_score_pkey PRIMARY KEY (sid, cid));CREATE TABLE audit_score (  username character(20),      -- 用户名  sid character(10),  cid character(10),  updatetime timestamp,        -- 修改时间  oldscore numeric(5, 1),      -- 修改前的成绩  newscore numeric(5, 1)       -- 修改后的成绩);-- 创建触发器函数CREATE OR REPLACE FUNCTION score_audit()RETURNS TRIGGERAS $score_audits$BEGIN  IF TG_OP = 'DELETE' THEN    INSERT INTO audit_score    VALUES (user, OLD.sid, OLD.cid, now(), OLD.score, NULL);    RETURN OLD;  ELSIF TG_OP = 'UPDATE' THEN    INSERT INTO audit_score    VALUES (user, OLD.sid, OLD.cid, now(), OLD.score, NEW.score);    RETURN NEW;  ELSIF TG_OP = 'INSERT' THEN    INSERT INTO audit_score    VALUES (user, NEW.sid, NEW.cid, now(), NULL, NEW.score);    RETURN NEW;  END IF;  RETURN NULL;END;$score_audits$ LANGUAGE plpgsql;-- 创建触发器CREATE TRIGGER score_audit_triggerAFTER INSERT OR UPDATE OR DELETE ON stu_scoreFOR EACH ROW EXECUTE FUNCTION score_audit();-- 修改触发器名称ALTER TRIGGER score_audit_trigger ON stu_score RENAME TO score_audit_trig;-- 删除触发器DROP TRIGGER IF EXISTS score_audit_trig ON stu_score CASCADE;

-- 声明游标cur_student CURSOR FOR SELECT * FROM student;cur_student_one CURSOR (key integer) IS SELECT * FROM student WHERE sid = key;-- 打开游标OPEN cur_vars1 FOR SELECT * FROM student WHERE sid = mykey;OPEN cur_vars1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);OPEN cur_student;OPEN cur_student_one('20160230302001');-- 使用游标提取值FETCH cur_vars1 INTO rowvar;  -- rowvar 为行变量FETCH cur_student INTO sid, sname, sex;-- 关闭游标CLOSE cursor_name;