案例一 学生查询
需求:查询学生姓名,分数,科目名
- 利用面向对象的思想
得到一个集合。
所有的查询和拼装集合的操作都在Dao类中去做
我们在Demo这个类中只做测试。
Demo
public class 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());
}
}
StudentScoreCourse
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 + '\'' +
'}';
}
}
StudentScoreCourseDao
这里有一个自己整理的工具包就不放在这里了!
JDBCUtil
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;
}
}
案例二 银行信息表
- 创建一张银行信息表
字段:主键 银行卡号,余额…- 封装方法,存款,取款,转账,所有的操作最终要数据持久化。
- 查询余额的方法。
- 开户、修改密码。
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;
}
}
Account
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;
}
}
AccountDao
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;
}
}