目前所有的代码都放在一个main方法中进行测试，层次不够清晰明确，分工不够明确
可以对于目前功能进行分包设计
-
util:主要用来存放工具类
-
dao： data access object，数据访问对象，所有的crud功能都可以写在这里，里面主要存放dao的接口
-
dao.impl： dao的实现类，真正实现dao的crud功能
-
entity： 实体类，数据库中的一张表基本对应Java中的实体类。
-
test：目前的测试类，可以测试方法的执行结果和正确性
util.DBUtil.java
package com.saas.util;
import java.sql.*;
public class DBUtil {
private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost:3306/saas";
private static final String DB_USER = "saas";
private static final String DB_PASS = "saas123";
private static Connection conn = null;
/**
* 获取数据库连接对象
* @return 数据库连接对象
*/
public static Connection getConn(){
try {
// 1. 注册驱动
// Class.forName(DB_DRIVER);
// 2. 获取连接对象
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (Exception e) {
throw new RuntimeException(e);
}
return conn;
}
/**
* 关闭数据库连接所需要的所有资源
* @param conn 连接对象
* @param stmt 声明对象
* @param rs 结果集对象
*/
public static void closeAll(Connection conn, Statement stmt, ResultSet rs){
try {
if(rs != null){ // 结果集不为空
rs.close(); // 关闭结果集
rs = null; // 将结果集置空，以便Java虚拟机尽快回收资源
}
if(stmt != null){ // 声明对象不为空
stmt.close(); // 关闭声明对象
stmt = null; // 将声明对象置空，以便Java虚拟机尽快回收资源
}
if(conn != null){ // 连接对象不为空
conn.close(); // 关闭连接对象
conn = null; // 将连接对象置空，以便Java虚拟机尽快回收资源
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
entity.Student.java
package com.saas.entity;
public class Student {
private int sid;
private String name;
private String sex;
private double score;
private int cid;
private int age;
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", score=" + score +
", cid=" + cid +
", age=" + age +
'}';
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
dao.IStudentDao.java(接口)
package com.saas.dao;
import com.saas.entity.Student;
import java.util.List;
public interface IStudentDao {
/**
* 获取所有的学生对象集合
* @return 学生对象集合
*/
List<Student> getAllStudents();
/**
* 获取满足分页条件的学生对象集合
* @param cp current page， 当前页码
* @param ps page size， 每页的最大条目数
* @return 满足分页条件的所有的学生对象集合
*/
List<Student> getStudentsByPage(int cp, int ps);
/**
* 根据sid获取该sid的学生对象
* @param sid 学号
* @return 学号对应的学生
*/
Student getStudentBySid(int sid);
/**
* 新增学生对象
* @param s 要新增的学生
* @return 新增学生执行后的受影响行数
*/
int saveStudent(Student s);
/**
* 修改学生对象
* @param s 新修改的学生对象
* @return 修改学生后受影响的行数
*/
int updateStudent(Student s);
/**
* 删除学生对象
* @param s 要删除的学生对象
* @return 删除学生对象之后受影响的行数
*/
int deleteStudent(Student s);
/**
* 根据学生的sid删除学生对象
* @param sid 要删除的学生编号
* @return 删除该sid对应学生的受影响行数
*/
int deleteStudent(int sid);
}
dao.impl.StudentDaoImpl
package com.saas.dao.impl;
import com.saas.dao.IStudentDao;
import com.saas.entity.Student;
import com.saas.util.DBUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements IStudentDao {
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;
@Override
public List<Student> getAllStudents() {
return null;
}
@Override
public List<Student> getStudentsByPage(int cp, int ps) {
List<Student> list = null;
conn = DBUtil.getConn();
int si = (cp - 1) * ps;
try {
String sql = "select * from student limit " + si + ", " + ps;
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if(rs != null){
list = new ArrayList<>();
Student stu = null;
while (rs.next()){
stu = new Student();
stu.setSid(rs.getInt(1));
stu.setName(rs.getString(2));
stu.setSex(rs.getString(3));
stu.setScore(rs.getDouble(4));
stu.setCid(rs.getInt(5));
stu.setAge(rs.getInt(6));
list.add(stu);
}
}
DBUtil.closeAll(conn, stmt, rs);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return list;
}
@Override
public Student getStudentBySid(int sid) {
Student stu = null;
conn = DBUtil.getConn();
String sql = "select * from student where sid = " + sid;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()){
stu = new Student();
stu.setSid(rs.getInt(1));
stu.setName(rs.getString(2));
stu.setSex(rs.getString(3));
stu.setScore(rs.getDouble(4));
stu.setCid(rs.getInt(5));
stu.setAge(rs.getInt(6));
}
DBUtil.closeAll(conn, stmt, rs);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return stu;
}
@Override
public int saveStudent(Student s) {
return 0;
}
@Override
public int updateStudent(Student s) {
int result = 0;
conn = DBUtil.getConn();
String sql = "update student set name ='" + s.getName() + "', sex = '" + s.getSex() + "', score = " +
s.getScore() + ", cid = " + s.getCid() + ", age = " + s.getAge() + " where sid =" + s.getSid();
try {
stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
DBUtil.closeAll(conn, stmt, rs);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return result;
}
@Override
public int deleteStudent(Student s) {
return 0;
}
@Override
public int deleteStudent(int sid) {
return 0;
}
}
test.TestStudentDao
package com.saas.test;
import com.saas.dao.IStudentDao;
import com.saas.dao.impl.StudentDaoImpl;
import com.saas.entity.Student;
import java.util.List;
public class TestStudentDao {
public static void main(String[] args) {
IStudentDao isd = new StudentDaoImpl();
// List<Student> list = isd.getStudentsByPage(3, 3);
//
// for (Student s : list) {
// System.out.println(s);
// }
Student stu = isd.getStudentBySid(200);
System.out.println(stu);
System.out.println("-------------------------");
Student s = new Student();
s.setSid(116);
s.setName("zhiduoxing");
s.setSex("male");
s.setScore(101);
s.setCid(3);
s.setAge(22);
System.out.println(isd.updateStudent(s) > 0);
}
}