JDBC封装DAO原理

一.编写DAO
1.封装连接的类
2.用户类
3.DAO抽象方法接口
4.实现DAO接口的类
5.测试类

在resources下的db.properties文件

#properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql
jdbc.username=root
jdbc.password=
init=2
maxactive=2

封装连接的类：

package day03;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class DBUtils {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	private static int init;
	private static int maxactive;
	private static BasicDataSource bds=null;
	static {
		bds=new BasicDataSource();
		Properties cfg=new Properties();
		InputStream inStream=DBUtils.class
				.getClassLoader()
				.getResourceAsStream("db.properties");
		try {
			cfg.load(inStream);
			driver=cfg.getProperty("jdbc.driver");
			url=cfg.getProperty("jdbc.url");
			username=cfg.getProperty("jdbc.username");
			password=cfg.getProperty("jdbc.password");
			init=Integer.parseInt(cfg.getProperty("init"));
			maxactive=Integer.parseInt(cfg.getProperty("maxactive"));
			
			//设置BasicDataSource必要参数
			bds.setDriverClassName(driver);
			bds.setUrl(url);
			bds.setUsername(username);
			bds.setPassword(password);
			//设置BasicDataSource管理策略参数
			bds.setInitialSize(init);
			bds.setMaxActive(maxactive);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() {
		Connection conn=null;
		try {
			conn=bds.getConnection();
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	public static void closeConnection(Connection conn) {
		if (conn!=null) {
			try {
				//把事务恢复成自动提交再归还
				conn.setAutoCommit(true);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void rollback(Connection conn) {
		if(conn!=null) {
			try {
				conn.rollback();
			}catch(SQLException e1) {
				e1.printStackTrace();
			}
		}
	}
}

用户类：

package day03;

public class User {
	//javaBean
	private int id;
	private String name;
	private String pwd;
	private int age;
	public User() {
		
	}
	public User(int id, String name, String pwd, int age) {
		super();
		this.id = id;
		this.name = name;
		this.pwd = pwd;
		this.age = 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 String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
}

DAO接口：

package day03;

import java.util.List;

public interface UserDAO {
	//通过ID查找用户
	public User findUserById(int id);
	//查询USER中所有的内容,并返回
	public List<User> findAllUser();
	//更改user表中某一条记录
	public int updateUser(User user);
	//
	public int saveUser(User user);
}

DAO实现类：

package day03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserDAO1 implements UserDAO{
	private static final String search_by_id
		="select * from user_1 where id=?";
	private static final String search_all
		="select * from user_1";
	private static final String update_user_password
	="update user_1 set name=?,password=? where id=?";
	private static final String insert_user
	="insert into user_1 values(null,?,?,?)";
	
	public User findUserById(int id) {
		Connection conn=null;
		try {
			conn=DBUtils.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement ps=conn.prepareStatement(search_by_id);
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			int i=0;
			String name=null;
			String pwd=null;
			int age=0;
			while(rs.next()){
				i=rs.getInt(1);
				name=rs.getString(2);
				pwd=rs.getString(3);
				age=rs.getInt(4);
				return new User(i, name, pwd, age);
			}
			rs.close();
			ps.close();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			DBUtils.rollback(conn);
		}finally{
			DBUtils.closeConnection(conn);
		}
		return null;
	}

	public List<User> findAllUser() {
		Connection conn=null;
		try {
			conn=DBUtils.getConnection();
			conn.setAutoCommit(false);
			Statement sta=conn.createStatement();
			ResultSet rs=sta.executeQuery(search_all);
			List<User> list=new ArrayList<User>();
			while (rs.next()) {
				int i=rs.getInt(1);
				String name=rs.getString(2);
				String pwd=rs.getString(3);
				int age=rs.getInt(4);
				User user=new User(i, name, pwd, age);
				list.add(user);
			}
			conn.commit();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			DBUtils.rollback(conn);
		}finally {
			DBUtils.closeConnection(conn);
		}
		return null;
	}

	public int updateUser(User user) {
		Connection conn=null;
		try {
			conn=DBUtils.getConnection();
			PreparedStatement ps=conn.prepareStatement(update_user_password);
			ps.setString(1, user.getName());
			ps.setString(2, user.getPwd());
			ps.setInt(3, user.getId());
			int n=ps.executeUpdate();
			if (n!=1) {
				throw new Exception("修改失败");
			}
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
			DBUtils.rollback(conn);
		}finally {
			DBUtils.closeConnection(conn);
		}
		return 0;
	}

	public int saveUser(User user) {
		Connection conn=null;
		try {
			conn=DBUtils.getConnection();
			PreparedStatement ps=conn.prepareStatement(insert_user);
			ps.setString(1, user.getName());
			ps.setString(2, user.getPwd());
			ps.setInt(3, user.getAge());
			int n=ps.executeUpdate();
			if (n!=1) {
				throw new Exception("插入失败");
			}
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
			DBUtils.rollback(conn);
		}finally {
			DBUtils.closeConnection(conn);
		}
		return 0;
	}

}

测试类：

package day03;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class Demo5 {
	public static void main(String[] args) {
		//数据--->数据库读取出来的
		//jdbc--->conn--->ps--->rs--->user
		//List<User> arr_user=UserDAO.getUserAll();
		
		//用户填写,代码生成的---->保存到库中
		//user-->DAO-->sql-->conn--->ps-->db
		
		//用户填写，代码生成的
		/*User u1 = new User(1,"lily","123",12);
		User u2 = new User(2,"rose","223",13);
		List<User> list = new ArrayList<User>();
		list.add(u1);
		list.add(u2);*/
		
		//验证dao的findUserById方法
				UserDAO dao=new UserDAO1();
//				User user=dao.findUserById(1);
//				System.out.println(user.getName());
				
				//验证dao的findall
				List<User> list=dao.findAllUser();
//				for (int i = 0; i < list.size(); i++) {
//					System.out.println(list.get(i).getId());
//				}
				
				//验证dao的修改方法
//				User user=new User(2, "xiongda", "321", 18);
//				dao.updateUser(user);
				
				//验证dao的插入方法
//				User user=new User(1232, "xiongda", "321", 18);
//				dao.saveUser(user);
		
		//登录验证功能
		Scanner scan = new Scanner(System.in);
		System.out.println("请输入你的用户名：");
		String name = scan.nextLine();
		System.out.println("请输入你的密码：");
		String pwd = scan.nextLine();
		scan.close();
		
		for(int i=0;i<list.size();i++) {
			if (name.equals(list.get(i).getName()) && pwd.equals(list.get(i).getPwd())) {
				System.out.println("登录成功");
				break;
			}
			System.out.println("登录失败");
		}
	}
}