JDBC数据库编程——三层架构

数据库操作层—–>业务层—->界面层

将类根据功能置于不同包中。

1、sdut.po

package sdut.po; public class Student { private int id; private String name; private int age; public Student(int id, String name, int age) { super(); this.id = id; this.name = name; this.age = age; } public Student() { } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", age=" + age + "]"; } }

2、sdut.dao BaseDao

package sdut.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class BaseDao { Connection con; PreparedStatement pst; ResultSet rs; public Connection getConn() throws ClassNotFoundException, SQLException { // 1 加载驱动 Class.forName("com.mysql.jdbc.Driver"); // 2 获得数据库的连接 Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8", "root", "usbw"); return con; } public void closeAll() throws SQLException { if(rs!=null) { rs.close(); } if(pst!=null) { pst.close(); } if(con!=null) { pst.close(); } } }

StudentDao

package sdut.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import sdut.po.Student; public class StudentDao extends BaseDao{ //增加 public int addStudent(Student student) throws ClassNotFoundException, SQLException { con=getConn(); String insertSQL="insert into student values(null,?,?)"; pst = con.prepareStatement(insertSQL); pst.setString(1,student.getName()); pst.setInt(2, student.getAge()); int result=pst.executeUpdate(); closeAll(); return result; } //删除 public int delStudent(int id) throws ClassNotFoundException, SQLException { con=getConn(); String sql="delete from student where id=?"; pst = con.prepareStatement(sql); pst.setInt(1,id); int result=pst.executeUpdate(); closeAll(); return result; } //修改 public int updateStudent(Student student) throws ClassNotFoundException, SQLException { con=getConn(); String sql="update student set name=?,age=? where id=?"; pst = con.prepareStatement(sql); pst.setString(1,student.getName()); pst.setInt(2, student.getAge()); pst.setInt(3,student.getId()); int result=pst.executeUpdate(); closeAll(); return result; } //查询所有记录 public List<Student> queryAll() throws ClassNotFoundException, SQLException { con=getConn(); String sql="select * from student"; pst=con.prepareStatement(sql); rs = pst.executeQuery(); List<Student> stuList=new ArrayList<Student>(); while(rs.next()) { Student stu=new Student(); stu.setId(rs.getInt(1)); stu.setName(rs.getString(2)); stu.setAge(rs.getInt(3)); stuList.add(stu); } closeAll(); return stuList; } //根据主键查询记录 public Student queryById(int id) throws ClassNotFoundException, SQLException { con=getConn(); String sql="select * from student where id=?"; pst=con.prepareStatement(sql); pst.setInt(1, id); rs = pst.executeQuery(); Student stu=null; if(rs.next()) { stu=new Student(rs.getInt(1),rs.getString(2),rs.getInt(3)); } closeAll(); return stu; } }

3、业务层sdut.biz

4、表示层sdut.aMain

package sdut.aMain; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import sdut.dao.StudentDao; import sdut.po.Student; public class Main { public static void main(String[] args) throws ClassNotFoundException, SQLException { StudentDao dao=new StudentDao(); Student stu1=new Student(1,"系统",22); /*int result = dao.addStudent(stu1); if(result==1) { System.out.println("插入记录成功！"); }*/ /* int result2 = dao.delStudent(1); if(result2==1) { System.out.println("删除记录成功！"); }*/ Student stu2=new Student(3,"11111",32); int result3 = dao.updateStudent(stu2); if(result3==1) { System.out.println("修改记录成功！"); } List<Student> stuList=dao.queryAll(); for(Student stu:stuList) { System.out.println(stu); } System.out.println("======="); Student stu = dao.queryById(100); System.out.println(stu); } }

5、执行结果：

修改记录成功！ Student [id=1, name=李丽, age=86] Student [id=2, name=王五, age=99] Student [id=3, name=11111, age=32] Student [id=4, name=SDUT, age=90]