JDBC DAO模式笔记

DAO全称为 Data Access Object ---数据访问对象(把对数据库的操作封装到一个部分)

组成：

1.一个VO类，与数据表表单的变量一致.并有变量的 setter，getter方法

2.一个数据库连接类，装有连接数据库和关闭数据库的方法

3.DAO接口，写有操作数据库的方法

4.实现DAO接口的实现类

例如：

一个数据表中的值域为  ID,NAME,AGE 

VO类：（Customer类）

public class Customer {
	private int id;
	private String name;
	private int age;
	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;
	}
	
	public String toString()
	{
		return "id:"+id+" name:"+name+" age:"+age;
	}
	
}

连接和关闭数据库的类(DBUtil类)

public class DBUtil {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	
	static{
		Properties prop = new Properties();		//第一步：新建一个配置文件对象
		try {
			Reader in = new FileReader("src\\config.properties");	//第二步：指定输入流为文件输入流
			prop.load(in);
		} catch (Exception e) {
			e.printStackTrace();
		}
		driver = prop.getProperty("driver");	// getProperty(“driver”)用于获取配置文件 driver= 后的值
		url = prop.getProperty("url");
		username = prop.getProperty("username");
		password = prop.getProperty("password");
	}//static
	
	//打开（连接）数据库
	public static Connection open()
	{		
			try {
				Class.forName(driver);
				return DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
	}
	
	//关闭数据库
	public static void close(Connection conn)
	{
		if(conn!=null)
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
}

DAO接口：

import java.util.List;

public interface CustomerDAO {
	public void add(Customer c);
	public void update(Customer c);
	public void delete(int id);
	public Customer getCustomerById(int id);
	public List<Customer> query();
}

DAO实现类(DaoImp)

import java.sql.Connection;
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 CustomerDaoImp implements CustomerDAO {

	//增
	public void add(Customer c) {
		Connection conn = DBUtil.open();
		String sql = "insert into person(name,age)values(?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,c.getName());
			pstmt.setInt(2, c.getAge());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally
		{
			DBUtil.close(conn);
		}
	}
	//修改
	public void update(Customer c) {
		Connection conn = DBUtil.open();
		String sql = "update person set name=?,age=? where id =?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, c.getName());
			pstmt.setInt(2, c.getAge());
			pstmt.setInt(3, c.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn);
		}
	}
	//删除
	public void delete(int id) {
		Connection conn = DBUtil.open();
		String sql = "delete from person where id = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn);
		}
	}
	//以ID查找
	public Customer getCustomerById(int id) {
		Connection conn = DBUtil.open();
		String sql = "select id,name,age from person where id = ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next())
			{
				Customer c = new Customer();
				c.setId(rs.getInt(1));
				c.setName(rs.getString(2));
				c.setAge(rs.getInt(3));
				return c;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn);
		}
		return null;
	}
	//遍历整个数据表
	public List<Customer> query() {
		Connection conn = DBUtil.open();
		String sql = "select id,name,age from person";
		List<Customer> list = new ArrayList<Customer>();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next())
			{
				int id = rs.getInt(1);
				String name = rs.getString(2);
				int age = rs.getInt(3);
				Customer c = new Customer();
				c.setId(id);
				c.setName(name);
				c.setAge(age);
				list.add(c);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally
		{
			DBUtil.close(conn);
		}
		return null;
	}

}