首页 > 其他分享 >2022-08-19 第五组 罗佳明

2022-08-19 第五组 罗佳明

时间：2022-08-19 21:45:47浏览次数：63  
标签：String rs 19 08 第五组 sql public conn pstmt

一、学习重点

 

 

二、学习内容

案例一：查询（面对对象思想）

package com.jsoft.morning.test;

import org.junit.Test;

import java.util.List;

public class Demo {

    /**
     * 需求：查询学生姓名，分数，科目名
     *
     *      利用面向对象的思想
     *
     * 得到一个集合。
     * 所有的查询和拼装集合的操作都在Dao类中去做
     * 我们在Demo这个类中只做测试。
     *
     */

    StudentScoreCourseDao dao = new StudentScoreCourseDao();

    @Test
    public void test01() {
        // 查询学生姓名，成绩，科目名
//        System.out.println(dao.getAll());
//        System.out.println(dao.getById(2));
//        System.out.println(dao.getBySidAndCid(2, 2));
//        System.out.println(dao.getCount(1));
        System.out.println(dao.getOrderScore());
    }

}
package com.jsoft.morning.test;

public class StudentScoreCourse {

    private String sname;
    private Integer score;
    private String cname;

    public StudentScoreCourse() {
    }

    public StudentScoreCourse(String sname, Integer score, String cname) {
        this.sname = sname;
        this.score = score;
        this.cname = cname;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public Integer getScore() {
        return score;
    }

    public void setScore(Integer score) {
        this.score = score;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    @Override
    public String toString() {
        return "StudentScoreCourse{" +
                "sname='" + sname + '\'' +
                ", score=" + score +
                ", cname='" + cname + '\'' +
                '}';
    }
}
package com.jsoft.morning.test;


import util.JDBCUtil;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentScoreCourseDao {

    private final Connection conn;
    {
        try {
            conn = JDBCUtil.getConnection();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询的方法
     * 查询学生姓名，分数，科目名
     */
    public List<StudentScoreCourse> getAll() {
        List<StudentScoreCourse> stus = new ArrayList<>(16);
        String sql = "select s.name sname,r.score,c.name cname " +
                "from student s " +
                "left join scores r on s.id = r.s_id " +
                "left join course c on  c.id = r.c_id";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                String sname = rs.getString("sname");
                int score = rs.getInt("score");
                String cname = rs.getString("cname");
                StudentScoreCourse ssc = new StudentScoreCourse(sname,score,cname);
                stus.add(ssc);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn,pstmt,rs);
        }

        return stus;
    }

    /**
     * 查询某一个学生的分数
     */
    public List<StudentScoreCourse> getById(Integer sid) {
        List<StudentScoreCourse> stus = new ArrayList<>(16);
        String sql = "select s.name sname,r.score,c.name cname " +
                "from student s " +
                "left join scores r on s.id = r.s_id " +
                "left join course c on c.id = r.c_id where s_id = ?";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,sid);
            rs = pstmt.executeQuery();
            while(rs.next()){
                String sname = rs.getString("sname");
                int score = rs.getInt("score");
                String cname = rs.getString("cname");
                StudentScoreCourse ssc = new StudentScoreCourse(sname,score,cname);
                stus.add(ssc);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn,pstmt,rs);
        }

        return stus;
    }


    /**
     * 查询某一个学生的某一科的分数
     */
    public StudentScoreCourse getBySidAndCid(Integer sid,Integer cid) {

        PreparedStatement pstmt = null;
        ResultSet rs = null;

        StudentScoreCourse ssc = null;
        String sql = "select s.name sname,r.score,c.name cname " +
                "from student s " +
                "left join scores r on s.id = r.s_id " +
                "left join course c on  c.id = r.c_id where s_id = ? and c_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,sid);
            pstmt.setInt(2,cid);
            rs = pstmt.executeQuery();
            while(rs.next()){
                String sname = rs.getString("sname");
                int score = rs.getInt("score");
                String cname = rs.getString("cname");
                ssc = new StudentScoreCourse(sname,score,cname);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn,pstmt,rs);
        }

        return ssc;
    }

    /**
     * 查询某一个学生的总分
     */
    public Integer getCount(Integer id) {

        Integer score = 0;

        String sql = "select sum(score) score " +
                "from scores  " +
                "GROUP BY s_id HAVING s_id = ?";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                score = rs.getInt("score");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(conn,pstmt,rs);
        }

        return score;
    }

    /**
     * 查询某一个学生的平均分
     */

    /**
     * 按照总分排序
     */
    public List<Integer> getOrderScore() {

        PreparedStatement pstmt = null;
        ResultSet rs = null;

        List<Integer> scores = new ArrayList<>();

        String sql = "select sum(score) score " +
                "from scores  " +
                "GROUP BY s_id ORDER BY score desc,s_id asc";

        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                int score = rs.getInt("score");
                scores.add(score);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return scores;
    }

}

案例二：银行信息表（查询、取款...）

package com.jsoft.afternoon.test;


import org.junit.Test;

/**
 * 1、创建一张银行信息表
 *      字段：主键 银行卡号，余额......
 * 2、封装方法，存款，取款，转账，所有的操作最终要数据持久化。
 * 3、查询余额的方法。
 * 4、开户、修改密码。
 */
public class Demo {

    private AccountDao accountDao = new AccountDao();

    @Test
    public void test01() {
//        System.out.println(accountDao.out("1102345678", 2000.00));
        accountDao.transform("1102345678","1209876543",10000.00);

    }

}
package com.jsoft.afternoon.test;

public class Account {

    private Integer id;
    private String accountid;
    private Double balance;


    public Account() {
    }

    public Account(Integer id, String accountid, Double balance) {
        this.id = id;
        this.accountid = accountid;
        this.balance = balance;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getAccountid() {
        return accountid;
    }

    public void setAccountid(String accountid) {
        this.accountid = accountid;
    }

    public Double getBalance() {
        return balance;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }
}
package com.jsoft.afternoon.test;


import util.JDBCUtil;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AccountDao {

    private final Connection conn;
    {
        try {
            conn = JDBCUtil.getConnection();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 转账
     */
    public Integer transform(String out,String in,Double balance){
        // 取款之前要先查询
        ResultSet rs = null;
        PreparedStatement preparedStatement = null;
        PreparedStatement preparedStatement2 = null;
        double b = 0;

        String sql = "select balance from bank where accountid = ?";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,out);

            rs = preparedStatement.executeQuery();
            while(rs.next()) {
                b = rs.getDouble("balance");
            }

            if(b >= balance) {
                // 余额够
                // 执行修改
                conn.setAutoCommit(false);
                sql = "update bank set balance = balance - ? where accountid = ?";
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setDouble(1,balance);
                preparedStatement.setString((int)2,out);
                int i = preparedStatement.executeUpdate();

                sql = "update bank set balance = balance + ? where accountid = ?";
                preparedStatement2 = conn.prepareStatement(sql);
                preparedStatement2.setDouble(1,balance);
                preparedStatement2.setString((int)2,in);
                i = preparedStatement2.executeUpdate();

                conn.commit();

                return i;

            }else{
                // 余额不够
                throw new RuntimeException("余额不足，转账失败");
            }

        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn,preparedStatement,rs);
            JDBCUtil.close(null,preparedStatement2);
        }
    }

    /**
     * 取款
     */
    public Integer out(String accountid,Double balance) {
        // 取款之前要先查询
        ResultSet rs = null;
        PreparedStatement preparedStatement = null;
        double b = 0;

        String sql = "select balance from bank where accountid = ?";

        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,accountid);

            rs = preparedStatement.executeQuery();
            while(rs.next()) {
                b = rs.getDouble("balance");
            }

            if(b >= balance) {
                // 余额够
                // 执行修改
                sql = "update bank set balance = balance - ? where accountid = ?";
                preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setDouble(1,balance);
                preparedStatement.setString((int)2,accountid);

                int i = preparedStatement.executeUpdate();
                return i;

            }else{
                // 余额不够
                throw new RuntimeException("余额不足，取款失败");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn,preparedStatement,rs);
        }
    }

    /**
     * 存款
     * @param accountid
     * @param balance
     * @return
     */
    public Integer in(String accountid,Double balance) {
        int i = 0;

        String sql = "update bank set balance = ? where accountid = ?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setDouble(1,balance);
            preparedStatement.setString(2,accountid);

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn,preparedStatement);
        }

        return i;
    }

    /**
     * 开户
     * @param accountid
     * @param balance
     * @return
     */
    public Integer add(String accountid,Double balance) {

        int i = 0;

        String sql = "insert into bank (accountid,balance) values (?,?)";
        PreparedStatement preparedStatement = null;
        try {
             preparedStatement = conn.prepareStatement(sql);
             preparedStatement.setString(1,accountid);
             preparedStatement.setDouble(2,balance);

            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn,preparedStatement);
        }

        return i;
    }

}

三、学习总结

标签：String,rs,19,08,第五组,sql,public,conn,pstmt
From： https://www.cnblogs.com/ljmskipper/p/16603133.html

相关文章

  • 2022-08-19 第八组 卢睿 学习心得
    目录JDBCStatement的不足SQL注入PreparedStatement：预编译（预加载）接口案例ResultSetMetaData(了解即可)数据库事务Mysql的数据库引擎4事务的四大特征ACID原子性A。一致性C......
  • 8.19
    CF1720D2题意：给定序列\(A\)，求\(A\)的最长子序列\(B\)，满足\(a_p\oplusp+1<a_{p+1}\oplusp\)\(n\leq3*10^5,0\leqa_i\leq10^9\)题解：枚举两边的值从高位到地位有多......
  • 2022-8-19 第一组 (≥▽≤) 学习笔记
    目录1.JDBC2.数据库事务面试题1.JDBCStatement的不足之处大量的字符串拼接，代码可读性降低sql注入PreparedStatement——预编译（预加载）接口通过Connection获取的......
  • 【2022-08-19】mysql基础知识(六)
    mysql基础知识(六)mysql之视图view什么是视图？视图就是通过查询得到的一张虚拟表，然后保存下来，下次直接进行使用即可。即：将SQL语句的查询结果当做虚拟表保存起来，以后可......
  • 20220819总结
    这次考试太烂了，又没考过Diavolo。T1简单的入门题，先热身。#include<iostream>#defineintlonglong#defineN5001usingnamespacestd;intn,ans;doublea[N]......
  • 2022-08-19 田龙跃 JDBC知识
    JAVA链接数据库步骤1.加载驱动2.建立链接3.获取statement语句对象执行sql4.处理结果集5.关闭连接加载驱动Class.forName(驱动名称)建立连接connection=DriverMa......
  • 2022-8-19第一组孙乃宇JDBC学习2
    JDBC的学习Statement的不足：大量的字符串拼接，代码可读性降低。sql注入SQL注入：BUG通过字符串的拼接，可以得到一个恒等的sql语句，可以跳过某些判断。如login("zxcvzx......
  • 2022-08-19 第四组 王佳齐 学习笔记
    思维导图学习笔记PreparedStatement：预编译（预加载）接口2.事务处理可以用来维护数据的完整性。保证sql语句要么全执行，要么全部不执行。1.通过conn获取的对象2.是Stateme......
  • 2022-08-19 记录一下 奥睿科 2.5/3.5英寸双盘位USB3.0硬盘底座 使用感受
    什么？电脑识别不了硬盘？？？我把京东客服给骂了，再到我写这个随笔的时候，有点心疼那个京东客服。为了扩容，昨天入手了希捷的2t机械家用盘，以及这次的主角奥睿科硬盘底座，简称硬盘盒......
  • 8.19总结
    啊~，本周的第一个暴零所罗门王的宝藏\(solution\)第一眼的时候完全没有想到是图论，当然暴零不是这个原因把行和列进行连边，因为行i的旋转次数+列j的旋转次数一定等于\(c_{......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99