JDBC-DAO经典模式 实现对数据库的增、删、改、查
JDBC(Java Data Base Connection)的作用是连接数据库
先看下jdbc连接SQLServer数据库的简单例子
代码实现(FirstJDBC):
- package com.jdbc;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- public class FirstJDBC {
- public static void main(String[] args)
- {
- //调用连接数据库的操作
- Connection con = createConnection();
- }
- /**
- * JDBC 建立 SQL Server数据库连接
- */
- private static Connection createConnection() {
- //定义加载驱动程序
- String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
- //定义 连接 服务器 和 数据库sample
- String dbURL = "jdbc:sqlserver://localhost:1433; DataBaseName = sample1" ;
- //默认用户名,不要用windows默认身份验证
- String userName = "sa" ;
- String userPassword = "zhichao" ;
- Connection connection = null ;
- Statement sta = null ;
- try {
- //正式加载驱动
- Class.forName(driverName);
- //开始连接
- connection = DriverManager.getConnection(dbURL, userName, userPassword);
- System.out.println("Connection Success !");
- //向数据库中执行SQL语句
- sta = connection.createStatement() ;
- ResultSet rs = sta.executeQuery("SELECT id,name,height From Table_1");
- while(rs.next())
- {
- int id = rs.getInt("id");
- String name = rs.getString("name");
- float height = rs.getFloat("height");
- System.out.println("id = "+id+" name = "+name+" height = "+height);
- }
- } catch (Exception e) {
- System.out.println("Connection Fail !");
- e.printStackTrace() ;
- }
- /**
- * 关闭数据库
- * @param connection
- */
- finally
- {
- try {
- if (null != sta)
- {
- sta.close() ;
- sta = null;
- System.out.println("Statement 关闭成功");
- }
- if (null != connection)
- {
- connection.close() ;
- connection = null;
- System.out.println("Connection 关闭成功");
- }
- } catch (Exception e) {
- e.printStackTrace() ;
- }
- }
- return connection ;
- }
- }
小结:
要写一个jdbc程序,先要加载相应数据库的驱动程序,驱动程序最好放在你建的工程里面,可以在你的工程下面建一个 lib文件夹以存储外部的jar文件,这样的话把你的工程拷贝到别的计算机运行,仍能成功执行。
jdbc代码一般步骤:
1)加载外部驱动程序(jar包)
2)正式加载驱动程序 (Class.forName(driverName) )
3)获取connection连接 (在jdk中的sql包中,只提供了一个类那就是DriverManeger,通过调用它的静态方法getConnection(),可以得到以数据库的连接
4)创建sql语句的声明(Statement),执行sql语句(查询),遍历结果集
5)关闭数据库连接(一般用finally{}来处理,或者调用方法的形式来完成,关闭之前先判断你要关闭的对象连接是否为空,如果空那会抛异常,所以先判断)
------------------------------------- ------------------------------------- ------------------------Data Access Objects-------------------- ------------------------------------------- ---------------------------
使用 DAO模式 来对数据库做增删改查操作
这种模式可以大概分为三个层:1.DAO层 2.服务层 3.表现层
1)表现层 :相当于客户端用来查看,提交信息的角色
2)服务层 :是表现层和DAO层的纽带,其实也没干什么事就是通知消息的角色
3)DAO :真正要做事的角色(对数据库的某些操作)
举个生活中的例子:
就好比你去餐厅吃饭,你充当一个 (表现层)的角色,然后有美女服务员(服务层),问你需要吃什么东西,给你下一张订单,让你填。之后服务员把订单传到 厨师(DAO层)那里,具体操作厨师会搞定,一段时间后厨师把做好的食物传给服务员,服务员把食物在传给客户,这些操作就算基本完成了。
执行顺序: 表现层-->服务层-->DAO层-->返回服务层-->返回表现层
来看看实现DAO模式的UML图:
代码实现:
1.Bean文件,在这主要作用(有点像中介存储的角色):当从数据库拿出数据后,一个个set到该类里,进行赋值,然后把该对象放到集合中,之后再get出来
Student.java
- package com.myjdbc.bean;
- public class Student {
- private Integer stuId;
- private String stuName ;
- private Integer stuAge;
- private String stuTel ;
- private String stuAddress ;
- private Integer groupId;
- public Integer getStuId() {
- return stuId;
- }
- public void setStuId(Integer stuId) {
- this.stuId = stuId;
- }
- public String getStuName() {
- return stuName;
- }
- public void setStuName(String stuName) {
- this.stuName = stuName;
- }
- public Integer getStuAge() {
- return stuAge;
- }
- public void setStuAge(Integer stuAge) {
- this.stuAge = stuAge;
- }
- public String getStuTel() {
- return stuTel;
- }
- public void setStuTel(String stuTel) {
- this.stuTel = stuTel;
- }
- public String getStuAddress() {
- return stuAddress;
- }
- public void setStuAddress(String stuAddress) {
- this.stuAddress = stuAddress;
- }
- public Integer getGroupId() {
- return groupId;
- }
- public void setGroupId(Integer groupId) {
- this.groupId = groupId;
- }
- }
2.java连接数据库的基本操作及关闭,封装在一个类中
JDBCUtils.java
- package com.myjdbc.utils;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- public class JDBCUtils {
- /**
- * 获取连接
- *
- */
- public static Connection getConnection()
- {
- String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
- String url = "jdbc:sqlserver://localhost:1433; DataBaseName = studentManager";
- String user = "sa" ;
- String password = "zhichao";
- Connection con = null ;
- try {
- Class.forName(driverName);
- con = DriverManager.getConnection(url, user, password);
- System.out.println("success");
- } catch (Exception e) {
- e.printStackTrace();
- }
- return con ;
- }
- /**
- * 关闭连接
- */
- public static void free(ResultSet rs, Statement sta , Connection con)
- {
- try {
- if(null != rs)
- {
- rs.close();
- rs = null ;
- }
- if(null != sta)
- {
- sta.close();
- sta = null ;
- }
- if(null != con)
- {
- con.close();
- con = null ;
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
3.定义一个DAO接口
StudentDAO.java
- package com.myjdbc.dao;
- import java.util.Set;
- import com.myjdbc.bean.Student ;
- public interface StudentDAO {
- public int addStudent(Student student) ;
- public int deleteStudent(String name);
- public int updateStudent(String name);
- public Student findStudent(String name);
- public Set<Student> findAll();
- }
4.实现DAO接口的类,具体DAO,做重要工作的类
ConcreteStudentDao.java
- package com.myjdbc.dao;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.HashSet;
- import java.util.Set;
- import com.myjdbc.bean.Student;
- import com.myjdbc.dao.StudentDAO;
- import com.myjdbc.utils.JDBCUtils;
- public class ConcreteStudentDao implements StudentDAO{
- //增加一个学生
- public int addStudent(Student student)
- {
- Connection con = null ;
- PreparedStatement ps = null ;
- int i = 0 ;
- try
- {
- con = JDBCUtils.getConnection();
- String sql = "insert into student(stuName,stuAge,stuTel,stuAddress,groupId) values(?,?,?,?,?)";
- ps = con.prepareStatement(sql);
- ps.setString(1, student.getStuName());
- ps.setInt(2, student.getStuAge());
- ps.setString(3, student.getStuTel());
- ps.setString(4, student.getStuAddress());
- ps.setInt(5, student.getGroupId());
- i = ps.executeUpdate() ;
- }
- catch(SQLException e)
- {
- throw new DAOException(e.getMessage(),e);
- }
- finally
- {
- JDBCUtils.free(null, ps, con);
- }
- return i;
- }
- //删除一个学生
- public int deleteStudent(String name)
- {
- Connection con = null ;
- PreparedStatement ps = null ;
- int i = 0 ;
- try
- {
- con = JDBCUtils.getConnection();
- String sql = "delete from student where stuName =?";
- ps = con.prepareStatement(sql);
- ps.setString(1, name);
- i = ps.executeUpdate() ;
- }
- catch(SQLException e)
- {
- throw new DAOException(e.getMessage(),e);
- }
- finally
- {
- JDBCUtils.free(null, ps, con);
- }
- return i;
- }
- //修改一个学生
- public int updateStudent(String name)
- {
- Connection con = null ;
- PreparedStatement ps = null ;
- int i = 0 ;
- try
- {
- con = JDBCUtils.getConnection();
- String sql = "update student set stuAge=stuAge+1 where stuName =?";
- ps = con.prepareStatement(sql);
- ps.setString(1, name);
- i = ps.executeUpdate() ;
- }
- catch(SQLException e)
- {
- throw new DAOException(e.getMessage(),e);
- }
- finally
- {
- JDBCUtils.free(null, ps, con);
- }
- return i;
- }
- //查询一行
- public Student findStudent(String name)
- {
- Connection con = null ;
- PreparedStatement ps = null ;
- Student stu = null ;
- ResultSet rs = null;
- try
- {
- con = JDBCUtils.getConnection();
- String sql = "select stuName,stuAge,stuTel,stuAddress,groupId from student where stuName =?";
- ps = con.prepareStatement(sql);
- ps.setString(1, name);
- rs = ps.executeQuery() ;
- stu = new Student();
- while(rs.next())
- {
- stu.setStuName(rs.getString(1));
- stu.setStuAge(rs.getInt(2));
- stu.setStuTel(rs.getString(3));
- stu.setStuAddress(rs.getString(4));
- stu.setGroupId(rs.getInt(5));
- }
- }
- catch(SQLException e)
- {
- throw new DAOException(e.getMessage(),e);
- }
- finally
- {
- JDBCUtils.free(rs, ps, con);
- }
- return stu;
- }
- //查询所有
- public Set<Student> findAll()
- {
- Connection con = null ;
- PreparedStatement ps = null ;
- Student stu = null ;
- ResultSet rs = null;
- Set<Student> set = null ;
- try
- {
- con = JDBCUtils.getConnection();
- String sql = "select stuName,stuAge,stuTel,stuAddress,groupId from student";
- ps = con.prepareStatement(sql);
- set = new HashSet<Student>() ;
- rs = ps.executeQuery() ;
- while(rs.next())
- {
- stu = new Student();
- stu.setStuName(rs.getString(1));
- stu.setStuAge(rs.getInt(2));
- stu.setStuTel(rs.getString(3));
- stu.setStuAddress(rs.getString(4));
- stu.setGroupId(rs.getInt(5));
- set.add(stu);
- }
- }
- catch(SQLException e)
- {
- throw new DAOException(e.getMessage(),e);
- }
- finally
- {
- JDBCUtils.free(rs, ps, con);
- }
- return set;
- }
- }
5.自定义异常 继承了运行时异常,具体操作让父类实现
DAOException.java
- package com.myjdbc.dao;
- /**
- * 自定义异常
- * @author Administrator
- *
- */
- public class DAOException extends RuntimeException {
- public DAOException()
- {
- super();
- }
- public DAOException(String messege,Throwable cause)
- {
- super(messege,cause);
- }
- public DAOException(String messege)
- {
- super(messege);
- }
- public DAOException(Throwable cause)
- {
- super(cause);
- }
- }
6定义一个服务类(服务层),本来还要定义一个接口,这里简写了,客户与DAO的纽带,持有DAO对象的引用
StudentService.java
- package com.myjdbc.service;
- import java.util.Set;
- import com.myjdbc.bean.Student;
- import com.myjdbc.dao.StudentDAO;
- import com.myjdbc.dao.ConcreteStudentDao;
- public class StudentService {
- StudentDAO sd = new ConcreteStudentDao();
- public int add(Student student)
- {
- return this.sd.addStudent(student);
- }
- public int delete(String name)
- {
- return this.sd.deleteStudent(name);
- }
- public int update(String name)
- {
- return this.sd.updateStudent(name);
- }
- public Student find(String name)
- {
- return this.sd.findStudent(name);
- }
- public Set<Student> findAll()
- {
- return this.sd.findAll();
- }
- }
7.定义一个测试类,相当于 (表现层)
Client.java
- package com.myjdbc.test;
- import java.util.HashSet;
- import java.util.Iterator;
- import java.util.Set;
- import com.myjdbc.bean.Student;
- import com.myjdbc.service.StudentService;
- public class Client {
- public static void main(String[] args)
- {
- Student stu = new Student();
- Set<Student> set = new HashSet<Student>();
- // stu.setStuName("zhangsan");
- // stu.setStuAge(20);
- // stu.setStuTel("18779157911");
- // stu.setStuAddress("china");
- // stu.setGroupId(1);
- StudentService ss = new StudentService();
- //System.out.println(ss.add(stu));
- //System.out.println(ss.delete("aa"));
- //System.out.println(ss.update("bb"));
- //stu = ss.find("cc");
- //System.out.println(stu.getStuName() +" " +stu.getStuAge()+" "+stu.getStuTel()+" "+stu.getStuAddress()+" "+stu.getGroupId());
- set = ss.findAll() ;
- Iterator<Student> iterator = set.iterator();
- while(iterator.hasNext())
- {
- Student student = (Student)iterator.next() ;
- System.out.println(student.getStuName() +" " +student.getStuAge()+" "+student.getStuTel()+" "+student.getStuAddress()+" "+student.getGroupId());
- }
- }
- }