Day2_JDBC——DAO

一.储备知识

DAO（Data Access Object），数据访问对象，个人理解是DAO的作用是将数据库里面的数据和Java里面的对象联系起来。

DAO类是通用数据库的操作类，java操作数据库时需要以下几个资源：

1. 数据库：表
2. Java实体类（即JavaBean），一张表对应一个Java实体类，类的属性一般和表的列名相同
3. 一张表的操作封装一个DAO（即users表–>UserDao，emp表–>EmpDao）
4. DAO类：数据库基本操作的通用方法

二.DAO类的代码

DAO.java

package com.atguigu.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.atguigu.utils.JDBCUtils;

public class DAO {
	
	    //查询记录，返回一个实例对象
        public <T>T getBean(Class<T> type, String sql, Object...params){
        	Connection conn = JDBCUtils.getConn();
        	PreparedStatement ps = null;
        	ResultSet rs = null;
        	T t = null;
        	try {
				ps = conn.prepareStatement(sql);
				for(int i = 0; i < params.length; i++) {
					ps.setObject(i+1, params[i]);
				}
				rs = ps.executeQuery();
				if(rs.next()) {
					try {
						t = type.newInstance();
						ResultSetMetaData metaData = rs.getMetaData();
						int count = metaData.getColumnCount();
						for(int i = 0; i < count; i++) {
							String columnLabel = metaData.getColumnLabel(i+1);
							Object value = rs.getObject(i+1);
							try {
								Field field = type.getDeclaredField(columnLabel);
								field.setAccessible(true);
								field.set(t, value);
							} catch (NoSuchFieldException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} catch (SecurityException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
						}
					} catch (InstantiationException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else {
					System.out.println("查询失败！");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				JDBCUtils.releaseConn(rs, ps, conn);
			}
        	return t;
        }
        
        //查询记录，返回一个List集合对象
        public <T>List<T> getBeanList(Class<T> type, String sql, Object...params){
        	Connection conn = JDBCUtils.getConn();
        	PreparedStatement ps = null;
        	ResultSet rs = null;
        	List<T> list = new ArrayList<>();
        	try {
				ps = conn.prepareStatement(sql);
				for(int i = 0; i < params.length; i++) {
	        		ps.setObject(i+1, params[i]);
	        	}
	        	rs = ps.executeQuery();
	        	while(rs.next()){
	        		try {
						T t = type.newInstance();
						ResultSetMetaData metaData = rs.getMetaData();
		        		int count = metaData.getColumnCount();
		        		for(int i = 0; i < count; i++) {
		        			String columnLabel = metaData.getColumnLabel(i+1);
		        			Object value = rs.getObject(i+1);
		        			Field field = null;
							try {
								field = type.getDeclaredField(columnLabel);
								field.setAccessible(true);
			        			field.set(t, value);
							} catch (NoSuchFieldException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} catch (SecurityException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
		        		}
		        		list.add(t);
					} catch (InstantiationException 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();
			}finally {
				JDBCUtils.releaseConn(rs, ps, conn);
			}
            return list;
        }
        
        //增删改方法
        public int update(String sql, Object...params) {
        	Connection conn = JDBCUtils.getConn();
        	PreparedStatement ps = null;
        	int count = 0;
        	try {
				ps = conn.prepareStatement(sql);
				for(int i = 0; i < params.length; i++) {
					ps.setObject(i+1, params[i]);
				}
				count = ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	return count;
        }
        
}

TestDAO.java

package com.atguigu.test;

import java.util.List;

import org.junit.jupiter.api.Test;

import com.atguigu.bean.User;
import com.atguigu.dao.DAO;

class TestDAO {
	        DAO dao = new DAO();
	         
	        //测试返回一个对象的查询方法
			@Test
			public void test01() {
		           String sql = "select id, username, password, balance"
		        		        + " from users where id = ?";
		           Class<User> type = User.class;
		           User user = dao.getBean(type, sql, 1);
		           System.out.println(user);
			}
			
			
			//测试返回一个List集合对象的查询方法
			@Test
			public void test02() {
		           String sql = "select id, username, password, balance"
		        		        + " from users where id > ?";
		           Class<User> type = User.class;
		           List<User> list = dao.getBeanList(type, sql, 0);
		           System.out.println(list);
			}
			
			//测试增加记录的方法
			@Test
			public void test03() {
		           String sql = "insert into users( id, username, password, balance)"
		        		        + " values( ?, ?, ?, ?)";
		           Integer id = 2;
		           String username = "lier";
		           String password = "123";
		           double balance = 1000.0;
		           Class<User> type = User.class;
		           int count = dao.update(sql, id, username, password, balance);
		           System.out.println(count);
			}
			
			//测试删除记录的方法
			@Test
			public void test04() {
		           String sql = "delete from users where id = ? ";
		           Integer id = 5;
		           Class<User> type = User.class;
		           int count = dao.update(sql, id);
		           System.out.println(count);
			}
			
			//测试修改记录的方法
			@Test
			public void test05() {
		           String sql = "update users set balance = ? where id = ? ";
		           double balance = 1000.0;
		           Integer id = 4;
		           Class<User> type = User.class;
		           int count = dao.update(sql, balance, id);
		           System.out.println(count);
			}

}