2022-08-19 第五组张明敏学习笔记

小张同学的小张

已于 2022-08-21 20:47:13 修改

阅读量253

点赞数

分类专栏：数据库 Java JDBC 文章标签：学习

于 2022-08-21 20:10:47 首次发布

本文链接：https://blog.csdn.net/qq_50392643/article/details/126454307

版权

Java 同时被 3 个专栏收录

29 篇文章 0 订阅

订阅专栏

数据库

6 篇文章 0 订阅

订阅专栏

JDBC

4 篇文章 0 订阅

订阅专栏

使用属性文件的好处：

1、实现了代码和数据的分离，如果需要修改配置信息，直接在属性文件中修改即可，不需要深代码
2、如果修改了配置信息，省去了编译的过程

读取外部的properties属性文件

 @Test
    public void test04() throws ClassNotFoundException, SQLException, IOException {

        Properties properties = new Properties();
        properties.load(Ai.class.getClassLoader().getResourceAsStream("jdbc.properties"));

        String url = properties.getProperty("mysql.url");
        String driverName = properties.getProperty("mysql.driverName");
        String username = properties.getProperty("mysql.username");
        String password = properties.getProperty("mysql.password");

      // 1.加载驱动类
        Class.forName(driverName);
      // 4.获取连接
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println(connection);
        System.out.println(Objects.nonNull(connection) ? "数据库连接成功" : "数据库连接失败");
    }

public class Ch01 {

    public static void main(String[] args) throws IOException {

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String name = scanner.next();
        System.out.println("请输入密码");
        String password = scanner.next();
        if (login(name, password) == true) {
            System.out.println("登陆成功");
        } else {
            System.out.println("用户名或密码错误！！！");
        }
    }

        public static boolean login (String name, String password) throws IOException {
            if (name == null || password == null) {
                return false;
            }
            Connection conn = JDBCUtil.getConnection();
            Statement stmt = null;
            ResultSet rs = null;
            try {
                stmt = conn.createStatement();
                String sql = "select * from account where username='" + name + "' and password='" + password + "'";
                rs = stmt.executeQuery(sql);
                return rs.next();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtil.close(conn, stmt, rs);
            }
            return true;
        }
}

小测试：

需求：查询学生姓名，分数，科目名

利用面向对象的思想

得到一个集合。
所有的查询和拼装集合的操作都在Dao类中去做
我们在Demo这个类中只做测试。

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 + '\'' +
                '}';
    }
}

public class StudentScoreCourseDao {

    private final Connection conn;
    {
        try {
            conn = JDBCUtil.getConnection();
        } 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 students s " +
                "left join scores r on s.id = r.s_id " +
                "left join courses 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 students s " +
                "left join scores r on s.id = r.s_id " +
                "left join courses 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;
    }

}

public class Demo {

    @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());
    }

}

工具类：

public class JDBCUtil {


    public static Connection getConnection() throws IOException {

        Properties properties = new Properties();

        Connection conn = null;
        try {
        properties.load(JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));

        // 数据库的url
        String url = properties.getProperty("mysql.url");
        String driverName = properties.getProperty("mysql.driverName");
        String username = properties.getProperty("mysql.username");
        String password = properties.getProperty("mysql.password");

        conn =  DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }





    public static void close(Connection conn, Statement stmt) {
        if (Objects.nonNull(stmt)){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (Objects.nonNull(conn)){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if (Objects.nonNull(stmt)){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (Objects.nonNull(conn)){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (Objects.nonNull(rs)){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

获取元数据

元数据：表格本身的数据
表格的列名，结果集的列名

public class Ch01 {


    @Test
    public void test03() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtil.getConnection();

            String sql = "select * from user";

            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();

            // 获取元数据
            /*
                元数据：表格本身的数据
                    表格的列名，结果集的列名
             */
            ResultSetMetaData metaData = rs.getMetaData();
//            System.out.println(metaData.getColumnName(1));
//            System.out.println(metaData.getColumnName(2));
//            System.out.println(metaData.getColumnCount());
//            metaData.get
            for (int i = 1; i <= metaData.getColumnCount() ; i++) {
                metaData.getColumnName(i);
            }


            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                System.out.println("id：" + id + "，username：" + username + "，password：" + password);
            }

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

}

事物：

数据库事务：是数据库的特性

Mysql的数据库引擎

1.在MySQL中，只有使用了Innodb引擎的数据库才支持事务
2.事务处理可以用来维护数据的完整性。保证sql语句要么全部执行，要么全部不执行。
3. 发生在DML中，增删改。

事务的四大特征ACID

1、原子性 A。
一个事务，要么全部完成，要么全部不完成。
2、一致性 C。
在事务开始之前和事务结束之后，数据库的完整性没有被破坏。
3、隔离性 Isolation
数据库允许多个事务同时对数据进行处理。每个事务之间是相互隔离。
4、持久性 D
事务结束以后，对数据的增删改是永久性的。

术语：提交事务，回滚事务（事务回滚）

1、事务一旦提交，就不可能回滚。
2、当一个连接对象被创建时，默认情况下自动提交事务。
3、关闭连接时，数据会自动提交事务。

操作事务的步骤：
1、关闭事务的自动提交

当做出增删改操作，把变化发生在内存中，提交事务，才会真正提交给数据库。

true：开启（默认） false：关闭
开启一个事务

conn.setAutoCommit(false)

提交事务
conn.commit()

事务回滚
conn.rollback()

public class Ch02 {

    @Test
    public void test01() {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            conn = JDBCUtil.getConnection();
            // 关闭事务的自动提交
            // true：开启（默认） false：关闭
            // 开启一个事务
            conn.setAutoCommit(false);
            // 把id为1的账户余额-1000
            String sql1 = "update bank set balance = balance - 1000 where id = 1";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt1.executeUpdate();

            String sql2 = "update bank set balance = balance + 1000 where id = 2";
            pstmt2 = conn.prepareStatement(sql2);
            pstmt2.executeUpdate();

            int i = 10 / 0;

            // 提交事务
            conn.commit();

            System.out.println("转账成功...");

        } catch (Exception e) {
            try {
                // 事务回滚
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn, pstmt1);
            JDBCUtil.close(null, pstmt2);
        }
    }
}

小案例：银行卡

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

public class Card {
    private int id;
    private String accountid;
    private Double balance;

    public Card() {
    }

    public Card(int id, String accountid, Double balance) {
        this.id = id;
        this.accountid = accountid;
        this.balance = balance;
    }

    public int getId() {
        return id;
    }

    public void setId(int 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;
    }

    @Override
    public String toString() {
        return "Card{" +
                "id=" + id +
                ", accountid='" + accountid + '\'' +
                ", balance=" + balance +
                '}';
    }
}

public class CardDao {

    private final Connection conn;

    {
        try {
            conn = JDBCUtil.getConnection();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    //开户
    public Integer add(String accountid, Double balance) {
        int i = 0;
        PreparedStatement prep = null;
        String sql = "insert into bank (account,balance) values (?,?) ";
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1, accountid);
            prep.setDouble(2, balance);
            i = prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(conn, prep);
        }
        return i;
    }


    //取款
    public Integer out(String accountid, Double balance) {
        PreparedStatement prep = null;
        ResultSet rs = null;
        double b = 0;
        String sql = "select balance from bank where account = ?";
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,"1234567789");
            rs = prep.executeQuery();
            while (rs.next()) {
                b = rs.getDouble("balance");
            }
            if (b >= balance) {
                sql = "update bank set balance = balance - ? where account = ?";
                prep = conn.prepareStatement(sql);
                prep.setDouble(1, balance);
                prep.setString(2, accountid);
                int i = prep.executeUpdate();
                return i;
            } else {
                throw new RuntimeException("余额不足，取款失败");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(conn, prep, rs);
        }
        return null;
    }
}

public class Demo {
    public static void main(String[] args) {
        CardDao cardDao = new CardDao();
//        cardDao.add("123456789",0.00);
        cardDao.out("1234567789",2000.0);

    }

小案例2：

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

public class AccountDao {

    private final Connection conn;
    {
        try {
            conn = JDBCUtil.getConnection();
        } 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;
    }

}

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);

    }

}