JDBC—总结(2).Dao模式

1.Dao模式

把java对数据库操作封装起来的一个较固定的套路。

2.Dao的组成部分

dao接口：定义所有的dao操作(CURD)

dao实现类：实现DAO接口中的操作
vo类：(value object)实例代表表中的一条记录，只有属性，没有操作；
工厂类：用来创建dao对象；(少量对象可以没有)

3.Dao模式流程

1.vo

package com.vo;

import java.sql.Date;

public class Dept1 {
	
	private int id;
	
	private String name;
	
	private String location;

	public Dept1(String name, String location) {
		super();
		this.name = name;
		this.location = location;
	}

	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 String getLocation() {
		return location;
	}

	public void setLocation(String location) {
		this.location = location;
	}

}

2.dao接口

//BaseDao类，可以用来把对数据库各个表的操作封装起来，使代码更简便，也更好用。

public class BaseDao {

		public void execute(String sql,Object...args){
			Connection conn=this.getConn();
			PreparedStatement ps=null;
			try {
				ps=conn.prepareStatement(sql);
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);
				}
				ps.execute();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				this.close(ps);
				this.close(conn);
			}
		}
		
		public List<Tbbook> select(String sql,Object...args){
			List<Tbbook> list=new ArrayList<Tbbook>();
			Connection conn=this.getConn();
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				ps=conn.prepareStatement(sql);
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);
				}
				rs=ps.executeQuery();
				while(rs.next()){
					Tbbook book=new Tbbook();
					book.setBookid(rs.getInt("bookid"));
					book.setBookname(rs.getString("bookname"));
					book.setPrice(rs.getInt("price"));
					book.setPublisher(rs.getString("publisher"));
					list.add(book);
					System.out.println();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				this.close(ps);
				this.close(conn);
			}
			return list;
		}
		
			public Connection getConn(){
				Connection conn=null;
				try {
					conn=DriverManager.getConnection("jdbc:mysql://localhost/test","root","zx199710170019");
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return conn;
			}
		
		public void close(AutoCloseable auto){
			
			if(auto!=null){
				try {
					auto.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	
}

TbbookDaoImpl : 实现了BaseDao，TbbookDao。因为有BaseDao所以这里的代码很简便，封装起来了。

其中的save()是可以用来插入任意表的记录。以后学多了就更简单了,结合上反射，注释，

对数据的操作就很方便了。

public class TbbookDaoImpl extends BaseDao implements TbbookDao {

	@Override
	public void insert(Tbbook b) {
		
		this.execute("insert into Tbbook values(null,?,?,?)", b.getBookname(),b.getPrice(),b.getPublisher());
		
	}

	@Override
	public void delete(int bookid) {
		
		this.execute("delete from Tbbook where bookid=?", bookid);
		
	}

	@Override
	public void update(Tbbook b) {
		
		this.execute("update Tbbook set bookname=?,price=?,publisher=?", b.getBookname(),b.getPrice(),b.getPublisher());
		
	}
	
	@Override
	public List<Tbbook> getBookByPage(int pageNum, int pageCount) {
		List<Tbbook> list=this.select("select bookid,bookname,price,publisher from tbbook limit ?,?", (pageNum-1)*pageCount,pageCount);
		return list;
	}

	@Override
	public Tbbook getBookById(int id) {
		List<Tbbook> list=this.select("select bookid,bookname,price,publisher from tbbook where bookid=?",id);
		if(!list.isEmpty()){
			return list.get(0);	
		}
		return null;	
	}

	@Override
	public List<Tbbook> getAll() {
		List<Tbbook> list=this.select("select bookid,bookname,price,publisher from tbbook order by bookid");
		return list;
	}
	
	
	private Connection getConnection() {
		Connection conn=null;
		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","zx199710170019");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	public void save(Object o)  {
		Class c=o.getClass();
		Field[] field=c.getDeclaredFields();
		Connection conn=this.getConnection();
		String sql="insert into "+c.getSimpleName()+"(";
		String sql1=") values(";
		for(Field f:field) {
			if(f.isAnnotationPresent(PrimaryKey.class)) {
				if(f.getAnnotation(PrimaryKey.class).value()) {
					sql+=f.getName()+",";
					sql1+="null,";
				}
				else {
					sql+=f.getName()+",";
					sql1+="?,";
				}
			}
			else if(f.isAnnotationPresent(Property.class)) {
					if(f.getAnnotation(Property.class).value().equals("")) {
						sql+=f.getName()+",";
						sql1+="?,";
						}
					else {
						sql+=f.getAnnotation(Property.class).value()+",";
						sql1+="?,";
					}
					}
		}
		sql=sql.replaceAll("\\,$","");
		sql1=sql1.replaceAll("\\,$","");
		sql+=sql1;
		sql+=")";
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for(int i=1;i<field.length;i++) {
			Field f=field[i];
			f.setAccessible(true);
			try {
				try {
					ps.setObject(i,f.get(o));
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		try {
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(sql);
	}

TbbookDaoTest ：实现类

public class TbbookDaoTest {

	public static void main(String[] args) {
		TbbookDaoImpl tb=new TbbookDaoImpl();
//		Tbbook book=new Tbbook("白夜行",99,"日本出版社");
		List<Tbbook> list=tb.getBookByPage(2, 2);
		System.out.println(list);		
		Tbbook book=tb.getBookById(250);
		System.out.println(book.toString());
		List<Tbbook> list1=tb.getAll();
		System.out.println(list1);
	}

}