一、学习重点
二、学习内容
案例一:查询(面对对象思想)
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; } }