how2j上DAO的实现

忘是亡心i 2022-04-24 13:50 104阅读 0赞

### how2j上DAO的实现 ###

how2j上的内容，我整理一下上传，代码自己手打的，都是运行过上传。  
DAO的实现只要通过代码大概了解即可

--------------------

两个知识点：

**ORM = Object Relationship Database Mapping**

对象和关系数据库的映射

简单说，一个对象，对应数据库里的一条记录

**DAO = DataAccess Object**

数据访问对象

实际上就是运用了ORM中的思路，把数据库相关的操作都封装在这个类里面，其他地方看不到JDBC的代码

--------------------

**主要设计思路**

1.  把驱动的初始化放在了构造方法HeroDAO里：
    
    public HeroDAO() \{  
    try \{  
    Class.forName(“com.mysql.jdbc.Driver”);  
    \} catch (ClassNotFoundException e) \{  
    e.printStackTrace();  
    \}  
    \}

因为驱动初始化只需要执行一次，所以放在这里更合适，其他方法里也不需要写了，代码更简洁

1.  提供了一个getConnection方法返回连接

所有的数据库操作都需要事先拿到一个数据库连接Connection，以前的做法每个方法里都会写一个，如果要改动密码，那么每个地方都需要修改。 通过这种方式，只需要修改这一个地方就可以了。 代码变得更容易维护，而且也更加简洁。

--------------------

**习题解答：**

练习1：  
设计一个HeroDAO，实现了DAO接口，提供相应的功能实现Hero类的ORM

**DAO.java**

package jdbc;
    
    import java.util.List;
    
    public interface DAO { 
    
    	
    	//获取总数
    	public int getTotal();
    	
    	//增加
        public void add(Hero hero);
        
        //修改
        public void update(Hero hero);
        
        //删除
        public void delete(int id);
        
        //获取
        public Hero get(int id);
        
        //查询
        public List<Hero> list();
        
        //分页查询
        public List<Hero> list(int start, int count);
    	
    	
    
    }

**HeroDAO.java**

package jdbc;
    
    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.ArrayList;
    import java.util.List;
    
    public class HeroDAO implements DAO{ 
    
    	//DAO = Data Access Object
    	//数据访问对象
    	//实际上就是运用了练习-ORM中的思路，把数据库相关的操作都封装在这个类里面，其他地方看不到JDBC的代码
    	
    	public HeroDAO() { 
    		try { 
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e) { 
    			e.printStackTrace();
    		}
    	}
    	
    	public Connection getConnection() throws SQLException{ 
    		return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root","root");
    	}
    	
    	//获取总数
    	public int getTotal() { 
    		
    		int total = 0;
    		try (
    				Connection c = getConnection();
    				Statement s = c.createStatement();
    				
    				){ 
    			
    			String sql =  "select count(*) from hero";
    			
    			ResultSet rs = s.executeQuery(sql);
    			while(rs.next()) { 
    				total = rs.getInt(1);
    			}
    			
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		} 
    		
    		return total;
    		
    	}
    	
    	//增加
    	public void add(Hero hero) { 
    		
    		String sql = "insert into hero value(null, ?, ? ,?)";
    		
    		try (
    				Connection c = getConnection();
    				PreparedStatement ps = c.prepareStatement(sql);
    				
    				){ 
    			
    			ps.setString(1, hero.name);
    			ps.setFloat(2, hero.hp);
    			ps.setInt(3, hero.damage);
    			ps.execute();
    			
    			ResultSet rs = ps.getGeneratedKeys();
    			if(rs.next()) { 
    				int id = rs.getInt(1);
    				hero.id = id;
    			}
    			
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}
    		
    	}
    	
    	//修改
    	public void update(Hero hero) { 
    		
    		String sql = "update hero set name = ?, hp = ?, damage = ? where id = ?";
    		try (
    				Connection c = getConnection();
    				PreparedStatement ps = c.prepareStatement(sql);
    				
    				){ 
    			
    			ps.setString(1, hero.name);
    			ps.setFloat(2, hero.hp);
    			ps.setInt(3, hero.damage);
    			ps.setInt(4, hero.id);
    			ps.execute();
    			
    			
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		} 
    	
    	}
    	
    	//删除
    	public void delete(int id) { 
    		
    		
    		
    		try (
    				Connection c = getConnection();
    				Statement s = c.createStatement();
    				
    				){ 
    			
    			String sql = "delete from hero where id = " + id;
    			s.execute(sql);
    			
    			
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}
    	}
    	
    	//通过id获取英雄
    	public Hero get(int id) { 
    		
    		Hero hero = null;
    		
    		try (
    				Connection c = getConnection();
    				Statement s = c.createStatement();
    				
    				){ 
    
    			String sql = "select * from hero where id = " + id;
    			ResultSet rs = s.executeQuery(sql);
    			
    			if (rs.next()){ 
    				hero = new Hero();
    				
    				hero.name = rs.getString(2);
    				hero.hp = rs.getFloat(3);
    				hero.damage = rs.getInt(4);
    				
    			}
    			
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		} 
    		return hero;
    	}
    	
    	//查询
    	public List<Hero> list() { 
    		
    		return list(0, Short.MAX_VALUE);
    	}
    	
    	//分页查询,可以扩展出list()
    	public List<Hero> list(int start, int count) { 
    		
    		List<Hero> heros = new ArrayList<>();
    		String sql = "select * from hero order by id desc limit ?, ?";
    		
    		try (
    				Connection c = getConnection();
    				PreparedStatement ps = c.prepareStatement(sql);
    				
    				){ 
    			
    			ps.setInt(1, start);
    			ps.setInt(2, count);
    			
    			ResultSet rs = ps.executeQuery();
    			while (rs.next()) { 
    				Hero hero = new Hero();
    				
    				hero.id = rs.getInt(1);
    				hero.name = rs.getString(2);
    				hero.hp = rs.getFloat(3);
    				hero.damage = rs.getInt(4);
    				
    				heros.add(hero);
    				
    			}
    			
    			
    			
    			
    		} catch(SQLException e) { 
    			e.printStackTrace();
    		}
    		
    		
    		return heros;
    	}
    	
    	public static void main(String[] args) { 
    		
    		HeroDAO h = new HeroDAO();
    		System.out.println(h.getTotal());
    		
    	}
    	
    }

--------------------

练习2：  
设计一个ItemDAO，实现了DAO接口，提供相应的功能实现Item类的ORM

**DAO.java**

package jdbc;
    
    import java.util.List;
    
    public interface DAO { 
    	
    	//获取总数
    	public int getTotal();
    	
    	//增加
        public void add(Item item);
        
        //修改
        public void update(Item item);
        
        //删除
        public void delete(int id);
        
        //获取
        public Item get(int id);
        
        //查询
        public List<Item> list();
        
        //分页查询
        public List<Item> list(int start, int count);
    }

**ItemDAO**

package jdbc;
    
    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.ArrayList;
    import java.util.List;
    
    public class ItemDAO implements DAO{ 
    
    	public ItemDAO() { 
    		try { 
    			Class.forName("com.mysql.cj.jdbc.Driver");
    		} catch (ClassNotFoundException e) { 
    			e.printStackTrace();
    		}
    	}
    	
    	public Connection getConnection() throws SQLException { 
    		return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2j?characterEncoding=UTF-8&serverTimezone=UTC","root", "root");
    	}
    	
    	public int getTotal() { 
    		
    		int total = 0;
    		
    		try (
    				Connection c = getConnection();
    				Statement s = c.createStatement();
    				
    				){ 
    
    			String sql = "select count(*) from item";
    			
    			ResultSet rs = s.executeQuery(sql);
    			while(rs.next()) { 
    				total = rs.getInt(1);
    			}
    			
    		} catch(SQLException e) { 
    			e.printStackTrace();
    		}
    		
    		return total;
    	}
    	
    	public void add(Item item) { 
    		
    		String sql = "insert into item value(null, ?, ?)";
    		
    		try (
    				Connection c = getConnection();
    				PreparedStatement ps = c.prepareStatement(sql);
    				
    				){ 
    
    			ps.setString(1, item.name);
    			ps.setInt(2, item.price);
    			
    			
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}
    	
    	
    	}
    	
    	public void delete(int id) { 
    		
    		try (
    				Connection c = getConnection();
    				Statement s = c.createStatement();
    				
    				){ 
    			
    			String sql = "delete from item where id = " + id;
    			s.execute(sql);
    		
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		}
    	}
    	
    	public void update(Item item) { 
    		
    		String sql = "update item set name = ?, price = ? where id = ?";
    		
    		try (
    				Connection c = getConnection();
    				PreparedStatement ps = c.prepareStatement(sql);
    				
    				){ 
    			
    			ps.setString(1, item.name);
    			ps.setInt(2, item.price);
    			ps.setInt(3, item.id);
    			ps.execute();
    			
    		} catch(SQLException e) { 
    			e.printStackTrace();
    		}
    	}
    	
    	public Item get(int id) { 
    		
    		Item item = null;
    		
    		try (
    				Connection c = getConnection();
    				Statement s = c.createStatement();
    				
    				){ 
    			
    			String sql = "select * from item where id = " + id;
    			ResultSet rs = s.executeQuery(sql);
    			
    			if (rs.next()) { 
    				item = new Item();
    				
    				item.name = rs.getString(2);
    				item.price = rs.getInt(3);
    				
    			}
    			
    			
    			
    		} catch (SQLException e) { 
    			e.printStackTrace();
    		} 
    		
    		return item;
    	}
    	
    	public List<Item> list() { 
    		
    		
    		return list(0, Short.MAX_VALUE);
    	}
    	
    	public List<Item> list(int start, int count) { 
    		
    		
    		List<Item> items = new ArrayList<>();
    		String sql = "select * from item order by id desc limit ?,?";
    		
    		try (
    				Connection c = getConnection();
    				PreparedStatement ps = c.prepareStatement(sql);
    				
    				){ 
    			
    			ps.setInt(1, start);
    			ps.setInt(2, count);
    			
    			ResultSet rs = ps.executeQuery();
    			while (rs.next()) { 
    				Item item = new Item();
    				
    				item.id = rs.getInt(1);
    				item.name = rs.getString(2);
    				item.price = rs.getInt(3);
    				
    				items.add(item);
    			}
    			
    			
    			
    		} catch(SQLException e) { 
    			e.printStackTrace();
    		}
    		
    		return items;
    	}
    	
    	
    	
    	
    	
    }