java-DAO模式实现数据库增,删,改,查和传统模式实现数据库增,删,改,查对比-JDBC

傷城~ 2022-06-14 00:45 392阅读 0赞

传统模式和DAO模式下对数据库进行增删改查对比操作

**传统模式：**

首先创建数据库表结构:（数据库以mysql为例，下同，项目使用数据库可改）

![Center][]

传统模式java代码：

package com.hkd.myschool;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    public class Phone {
    
    	/**
    	 * @param args
    	 */
    	static String[] arr = {"添加数据","查询数据","删除数据","退出操作"};
    	static Scanner sc = new Scanner(System.in);
    	//入口main函数
    	public static void main(String[] args) {
    		// 添加驱动
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	//循环操作
    	cc:do{//添加循环标签cc
           System.out.println("请选择您要进行的操作：");
           for(int i=0;i<arr.length;i++){
        	   System.out.println((i+1)+"."+arr[i]);
           }
    	      int choose = sc.nextInt();
    	      switch(choose){
    	         case 1:
    	        	 System.out.println("请输入id：");
    	        	 int id = sc.nextInt();
    	        	 System.out.println("请输入姓名：");
    	        	 String name = sc.next();
    	        	 System.out.println("请输入手机号：");
    	        	 String tel = sc.next();
    	        	 System.out.println("请输入机号类型：");
    	        	 String type = sc.next();
    	        	 add(id,name,tel,type);
    	        	 break;
    	         case 2:
    	        	 show();
    	        	 break;
    	         case 3:
    	        	 System.out.println("请输入该人员姓名：");
    	        	 String inputname = sc.next();
    	        	 delete(inputname);
    	        	 break;
    	         case 4:
    	        	 System.out.println("谢谢使用！");
    	        	 break cc;//通过标签跳出do while循环
    	         default:
    	        	 break cc;//通过标签跳出do while循环
    	      }
            }while(true); 
    		
    		//测试使用
    		/*try {
    			Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/phone", "root", "root");
    			//创建查询对象
    			Statement st = conn.createStatement();
    			String sql = "insert into phoneinfo values(null,'lcn','18437963795','移动')";
    			
    			int a =st.executeUpdate(sql);
    			
    			st.close();
    			conn.close();
    			
    			if(a>0){
    			System.out.println("成功");
    		    }else{
    			System.out.println("失败");
    		    }
    			
    		   } catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		   }*/
    	}
    	//--------------------------------------------------功能模块------------------------------------------------------
    	//添加方法
    	public static void add(int id,String name,String tel,String type){
    		
    		try {
    		Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/phone", "root", "root");
    		//创建查询对象
    		Statement st = conn.createStatement();
    		String sql = "insert into phoneinfo values("+id+",'"+name+"','"+tel+"','"+type+"')";//添加数据，注意传参时的转换
    		
    		int a =st.executeUpdate(sql);
    		st.close();
    		conn.close();
    		if(a>0){
    			System.out.println("添加成功");
    		    }else{
    			System.out.println("添加失败");
    		    }
    	   } catch (SQLException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	   }
    	}
    
    	//删除方法
    	public static void delete(String inputname){
    		try {
    			Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/phone", "root", "root");
    		//创建查询对象
    		Statement st = conn.createStatement();
    		String sql = "delete from phoneinfo where name='"+inputname+"'";
    		st.executeUpdate(sql);//更新数据
    		
    		st.close();
    		conn.close();
    	   } catch (SQLException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	   }
    	}
    
    	//查询方法
    	public static void show(){
    		try {
    			Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/phone", "root", "root");
    		//创建查询对象
    		Statement st = conn.createStatement();
    		String sql = "select * from phoneinfo";
    		
    		ResultSet rs = st.executeQuery(sql);
    		while(rs.next()){
    			System.out.println("编号："+rs.getInt("id"));
    			System.out.println("姓名："+rs.getString("name"));
    			System.out.println("手机号："+rs.getString("tel"));
    			System.out.println("机号类型："+rs.getString("type"));
    			System.out.println();
    		}
    		
    		st.close();
    		conn.close();
    	   } catch (SQLException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	   }
    	}
    	//----------------------------------------------------------------------------------------------------------------
    }

![Center 1][]

![Center 2][]

![Center 3][]

数据库操作界面图示：

![Center 4][]

**DAO模式下对数据库进行增删改查：**

首先创建数据库表结构:

![Center 5][]

java代码：

架构图：

![Center 6][]

BaseDao类：

package com.hkd.myschool.dao;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import java.sql.PreparedStatement;
    
    public class BaseDao {
    	String DBUser="root";//用户名
    	String DBPwd="root";//密码
    	String DBHost="127.0.0.1";//服务器地址
        String DBName="phone1";//数据库名称
        int Port = 3306;//端口
        int DBType = 1;//数据库类型
        
        Connection conn;//连接对象
        PreparedStatement ps;//查询对象
        ResultSet rs;//结果集
        
        public void open(){
          try {
        	if(DBType==1){
    		    Class.forName("com.mysql.jdbc.Driver");
        		conn=DriverManager.getConnection("jdbc:mysql://"+DBHost+":"+Port+"/"+DBName, DBUser, DBPwd);
        	}else if(DBType==2){
        		Class.forName("com.microsoft.sqlserver.sqlserverDriver");
        		conn=DriverManager.getConnection("jdbc:sqlserver://"+DBHost+":"+Port+";database="+DBName, DBUser, DBPwd);
        	}
          } catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
        }
        public void close(){
        try {
        	  if(rs!=null) rs.close();
        	    ps.close();
    			conn.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
        }
        /**
         * 
         * @param sql
         * @param parm
         * @return
         */
        public int executeUpdate(String sql,Object[] parm){
        	open();
        	int a=0;
        	try{
        	ps= conn.prepareStatement(sql);
        	if(parm!=null){
        		for(int i=0;i<parm.length;i++){
        			ps.setObject(i+1, parm[i]);
        		}
        	}
        	a=ps.executeUpdate();
        	}catch(Exception e){
        		e.printStackTrace();
        	}
        	close();
        	return a;
        }
        public ResultSet exectueQuery(String sql,Object[] parm)
        {
        	open();
        	
        	try{
        	ps= conn.prepareStatement(sql);
        	if(parm!=null){
        		for(int i=0;i<parm.length;i++){
        			ps.setObject(i+1, parm[i]);
        		}
        	}
        	rs=ps.executeQuery();
        	}catch(Exception e){
        		e.printStackTrace();
        	}
        	
        	return rs;
        	
        }
    }

StudentDao类：

package com.hkd.myschool.dao;
    
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import com.hkd.myschool.entiy.Student;
    
    public class StudentDao extends BaseDao{
    	public int add(Student stu){
    		String sql = "insert into student values(null,?,?,?)";
    		Object[] parm={stu.getStudentName(),stu.getStudentAge(),stu.getStudentTel()};
    		
    		
    		int a = executeUpdate(sql, parm);		
    		return a;
    		
    	}
    	public int delete(int studentNo){
    		String sql = "delete from student where studentNo=?";
    		Object[] parm={studentNo};
    		int a = executeUpdate(sql, parm);
    		
    		return a;
    	}
    	
    	
    	public int update(Student stu){
    		String sql = "update student set studentName=? , studentAge=? , studentTel=? where studentNo=?";
    		Object[] parm={stu.getStudentName(),stu.getStudentAge(),stu.getStudentTel(),stu.getStudentNo()};
    		int a = executeUpdate(sql, parm);
    		
    		return a;
    	}
        public List<Student> getALL(){
        	String sql = "select * from student";
        	rs=exectueQuery(sql, null);
        	List<Student> list = new ArrayList<Student>();
         try {
        	while(rs.next()){
        		Student s = new Student();
        		s.setStudentNo(rs.getInt("studentNo"));
        		s.setStudentName(rs.getString("studentName"));
        		s.setStudentAge(rs.getInt("studentAge"));
    			s.setStudentTel(rs.getInt("studentTel"));
    			list.add(s);
        	}
        	close();
    	} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
         return list;
         
        }
    }

Student类：

package com.hkd.myschool.entiy;
    
    public class Student {
        private int studentNo;
        private String studentName;
        private int studentAge;
        private int studentTel;
    	public int getStudentNo() {
    		return studentNo;
    	}
    	public void setStudentNo(int studentNo) {
    		this.studentNo = studentNo;
    	}
    	public String getStudentName() {
    		return studentName;
    	}
    	public void setStudentName(String studentName) {
    		this.studentName = studentName;
    	}
    	public int getStudentAge() {
    		return studentAge;
    	}
    	public void setStudentAge(int studentAge) {
    		this.studentAge = studentAge;
    	}
    	public int getStudentTel() {
    		return studentTel;
    	}
    	public void setStudentTel(int studentTel) {
    		this.studentTel = studentTel;
    	}
        
        
    }

StudentBiz类：

package com.hkd.myschool.entiy;
    
    import java.util.List;
    import java.util.Scanner;
    
    import com.hkd.myschool.dao.StudentDao;
    
    public class StudentBiz {
    	  Student stu = new Student();
    	  StudentDao sd = new StudentDao();
    	  Scanner sc = new Scanner(System.in);
          public void add(){
        	  System.out.println("请输入姓名：");
        	  stu.setStudentName(sc.next());
        	  System.out.println("请输入年龄：");
        	  stu.setStudentAge(sc.nextInt());
        	  System.out.println("请输入电话：");
        	  stu.setStudentTel(sc.nextInt());
        	  int a=sd.add(stu);
        	  if(a>0)System.out.println("成功");
        	  else System.out.println("失败");
        	  }
          public void del(){
        	  System.out.println("请输入编号：");
        	  int a=sd.delete(sc.nextInt());
        	  if(a>0)System.out.println("成功");
        	  else System.out.println("失败");
        	  }   
          public void update(){
        	  System.out.println("请输入要修改的编号：");
        	  stu.setStudentNo(sc.nextInt());
        	  System.out.println("请输入要修改的姓名：");
        	  stu.setStudentName(sc.next());
        	  System.out.println("请输入要修改的年龄：");
        	  stu.setStudentAge(sc.nextInt());
        	  System.out.println("请输入要修改的电话：");
        	  stu.setStudentTel(sc.nextInt());
        	  int a=sd.update(stu);
        	  if(a>0)System.out.println("修改成功");
        	  else System.out.println("修改失败");
          }
          public void show(){
        	  List<Student> list=sd.getALL();
        	  for(Student s:list){
        		  System.out.println(s.getStudentNo()+"\t"+s.getStudentName()+"\t"+s.getStudentAge()+"\t"+s.getStudentTel());
        	  }
    	  }
          
    }

Main入口类：

package com.hkd.myschool.entiy;
    
    import java.util.Scanner;
    
    public class Main {
    
    	/**
    	 * @param args
    	 */
    	public static void main(String[] args) {
    		Scanner sc = new Scanner(System.in);
    		StudentBiz sb = new StudentBiz();
    		System.out.println("*********************");
    		System.out.println("***     添加学生            ***");
    		System.out.println("***     删除学生            ***");
    		System.out.println("***     修改学生            ***");
    		System.out.println("***     查询学生            ***");
    		System.out.println("*********************");
    		
    		System.out.println("请输入您的选择：");
    		int a = sc.nextInt();
    		switch(a){
    		     case 1:
    		    	 sb.add();
    		    	 break;
                 case 2:
    		    	 sb.del();
    		    	 break;
                 case 3:
    		    	 sb.update();
    		    	 break;
                 case 4:
    		    	 sb.show();
    		    	 break;
    
    		}
    
    	}
    
    }

输出图示：

先显示一下数据库里的人员信息；键盘输入4

![Center 7][]  
进行添加操作：键盘输入1

![Center 8][]  
  
查询添加的数据：键盘输入4  
![Center 9][]数据添加成功！

删除数据操作：键盘输入2

执行删除编号为2的学生数据

![Center 10][]

再次查询表中的数据：键盘输入4

![Center 11][]

编号为2的学生信息已经删除

修改数据自行同理，不做演示！  
数据库操作界面图示：

![Center 12][]

[Center]: /images/20220614/e6743fffa1da414e95a4d629fba1f942.png
[Center 1]: /images/20220614/3629259cd9e04008a3d4fe11146f8f5a.png
[Center 2]: /images/20220614/dcfc934a20d5473ab80c732906e4b5cd.png
[Center 3]: /images/20220614/443599b660b24df0b139d67d5384dc0d.png
[Center 4]: /images/20220614/c0715a0ec31c409281927083340dde33.png
[Center 5]: /images/20220614/c3a769b412974eaaaa916db512da6d59.png
[Center 6]: /images/20220614/a5160d6290f34ee096d323c8ad11f925.png
[Center 7]: /images/20220614/1a9c7511c0cc4a6bae43793187702dd7.png
[Center 8]: /images/20220614/39567adbab6c4dbb93353dd5d2b9e92b.png
[Center 9]: /images/20220614/9ba9710470a54388a55e99fdffab9239.png
[Center 10]: /images/20220614/84d57094452e4d48acc397001efe6b52.png
[Center 11]: /images/20220614/b0849dfc188347ff93d97d079ea2812b.png
[Center 12]: /images/20220614/577937d6a62049daa40fbba733523ba7.png