JDBC对数据库进行增删改查

package com.softeem.oa.dto;

import java.util.Date;

/*******************************************************************************
 * DTO操作将数据库中的属性添加进来，获取set，get方法和构造器
 */	
public class Emp implements java.io.Serializable {
	//field
	/**  **/
	private int eno;
	/**  **/
	private String ename;
	/**  **/
	private String job;
	/**  **/
	private Double sal;
	/**  **/
	private Date hiredate;
	/**  **/
	private int dno;
	//method
	public int getEno() {
		return eno;
	}
	public void setEno(int eno) {
		this.eno = eno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public Double getSal() {
		return sal;
	}
	public void setSal(Double sal) {
		this.sal = sal;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public int getDno() {
		return dno;
	}
	public void setDno(int dno) {
		this.dno = dno;
	}
	//override toString Method 
	public String toString() {
		StringBuffer sb=new StringBuffer();
		sb.append("{");
		sb.append("'eno':'"+this.getEno()+"',");
		sb.append("'ename':'"+this.getEname()+"',");
		sb.append("'job':'"+this.getJob()+"',");
		sb.append("'sal':'"+this.getSal()+"',");
		sb.append("'hiredate':'"+this.getHiredate()+"',");
		sb.append("'dno':'"+this.getDno()+"'");
		sb.append("}");
		return sb.toString();
	}
}

package com.softeem.oa.dao;

import java.util.List;
//创建一个接口来定义增删改查的方法
public interface IBaseDAO<T> {

 public boolean insert(T obj);
	
	public boolean update(T obj);

	public boolean deleteById(T obj);
	
	public T findById(T obj);
	
	public List<T> findByPage(int pageNow,int pageSize);

	
}

package com.softeem.jdbc.utils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import com.alibaba.druid.pool.DruidDataSource;

/*这是一个工具类，有如下功能：
 * 1.获取连接
 * 2.关闭资源
 * 3.执行更新操作
 * 4.执行查询列表操作
 * 5.执行查询单条记录操作
 */

public class DBUtils {

		//声明druid连接池对象
			private static DruidDataSource pool ;
			//地址
			private static String url;
			private static String username;
			private static String password;
			//初始连接数
			private static int initialSize;
			//最大活动连接数
			private static int maxActive;
			//最小闲置连接数
			private static int minIdle;
			//最大等待时间
			private static long maxWait;
			
			
			static{
				init();
			}
			//加载属性文件并读取属性文件的内容将其设置给连接信息
			private static void loadProp(){

		       try {
		    	   //属性文件位于src更目录时，加“/”则不要使用classloader，两者取其一
		    	   InputStream is =DBUtils.class.getResourceAsStream("/jdbc.properties");
		    	   Properties p = new Properties();
				   p.load(is);
				   
				   url = p.getProperty("jdbc.url");
				   username = p.getProperty("jdbc.username");
				   password = p.getProperty("jdbc.password");
				   initialSize = Integer.parseInt(p.getProperty("initialSize"));
				   maxActive = Integer.parseInt(p.getProperty("maxActive"));
				   minIdle = Integer.parseInt(p.getProperty("minIdle"));
				   maxWait = Long.parseLong(p.getProperty("maxWait"));
			} catch (IOException e) {
				e.printStackTrace();
			}
			}
			//初始化连接池
			private static void init(){
				pool = new DruidDataSource();
				//加载属性文件初始化
				loadProp();
				pool.setUrl(url);
				pool.setUsername(username);
				pool.setPassword(password);
				//设置连接池初始连接数
				pool.setInitialSize(initialSize);
				//设置最大连接数
		        pool.setMaxActive(maxActive);
		        //设置最小闲置连接数
		        pool.setMinIdle(minIdle);
		        //设置最大的等待时间
		        pool.setMaxWait(maxWait);
			}
			public static Connection getConn(){
				try {
					//如果连接池为空或异常关闭，则重新初始化一个
					if(pool == null || pool.isClosed()){
						init();
					}
					return pool.getConnection();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return null;
			}
	
	//关闭资源
	public static void close(Statement stmt,Connection conn){
		try {
			if(stmt != null){
				stmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//封装通用的更新操作，对更新有关的操作都能实现
	public static boolean exeUpdate(Connection conn,String sql,Object... obj){
		PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i+1, obj[i]);
				}
				return ps.executeUpdate()>0;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				close(ps,null);
			}
		
		return false;
	}
	//封装查询多条（泛型，，集合框架，反射，JDBC）
	public static<T> List<T> queryList(Class<T> t,String sql,Object...params){
		List<T> list = new ArrayList<>();
		T obj = null;
		Connection conn = null;
		PreparedStatement ps = null;
			try {
					conn = getConn();
					ps = conn.prepareStatement(sql);
					for (int i = 0; i < params.length; i++) {
						ps.setObject(i+1, params[i]);
					}
					ResultSet rs = ps.executeQuery();
					//获取查询结果集的元数据
					ResultSetMetaData rsmd = rs.getMetaData();
					//声明
					Map<String,Object> map = new HashMap<>();
					//遍历结果集
					while(rs.next()){
						//防止缓存上一条数据
						map.clear();
						for (int i = 0; i < rsmd.getColumnCount(); i++) {
							//获取列名
							String cname = rsmd.getColumnLabel(i+1);
						    //获取列值
							Object value = rs.getObject(cname);
							//将值存储到map
							map.put(cname, value);
						}
						//利用反射将map中的数据注入到Java对象中，并将对象存入集合
						if(! map.isEmpty()){
							Set<String> columnNames = map.keySet();
							//创建对象
							obj = t.newInstance();
							for (String column : columnNames) {
								//根据建获取值
								Object value = map.get(column);
								//获取属性对象
								Field f = t.getDeclaredField(column);
								//设置可访问状态
								f.setAccessible(true);
								//为属性设值
								f.set(obj, value);
							}
							list.add(obj);
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (NoSuchFieldException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InstantiationException e) {
					e.printStackTrace();
				}
			return list;
	}
	//封装查询单个对象方法
	public static<T> T queryOne(Class<T> t,String sql,Object...params){
		T objs = null;
		Connection conn = null;
		PreparedStatement ps = null;
			try {
					conn = getConn();
					ps = conn.prepareStatement(sql);
					for (int i = 0; i < params.length; i++) {
						ps.setObject(i+1, params[i]);
					}
					ResultSet rs = ps.executeQuery();
					//获取查询结果集的元数据
					ResultSetMetaData rsmd = rs.getMetaData();
					//遍历结果集orm
					if(rs.next()){
						objs = t.newInstance();
						for (int i = 0; i < rsmd.getColumnCount(); i++) {
							//获取列名
							String cname = rsmd.getColumnLabel(i+1);
						    //获取列值
							Object value = rs.getObject(cname);
							//根据类名称获取java类的属性名
                            Field f = t.getDeclaredField(cname);
                            f.setAccessible(true);
                            f.set(objs, value);
						}
					
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (NoSuchFieldException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InstantiationException e) {
					e.printStackTrace();
				}
			return objs;
			
	}
	
}

package com.softeem.oa.dao;

import java.sql.Connection;
import java.util.List;

import com.softeem.jdbc.utils.DBUtils;
import com.softeem.oa.dto.Emp;

public class EmpDAO implements IBaseDAO<Emp>{

private Connection conn;
	
	public EmpDAO(Connection conn) {
		super();
		this.conn = conn;
	}
//添加
	@Override
	public boolean insert(Emp obj) {
		String sql = "insert into emp(ename,job,sal,hiredate,dno) values(?,?,?,now(),?)";
		return DBUtils.exeUpdate(conn,sql, obj.getEname(),obj.getJob(),obj.getSal(),obj.getDno());
	}
//更新
	@Override
	public boolean update(Emp obj) {
		String sql = "update emp set sal=? where eno=?";
		return DBUtils.exeUpdate(conn, sql, obj.getSal(),obj.getDno());
	}
//删除
	@Override
	public boolean deleteById(Emp obj) {
		String sql = "delete from emp where eno=?";
		return DBUtils.exeUpdate(conn, sql, obj.getEno());
	}
//查询dange
	@Override
	public Emp findById(Emp obj) {
		String sql = "select * from emp where eno=?";
		return DBUtils.queryOne(Emp.class, sql, obj.getEno());
	}
//查询多个
	@Override
	public List<Emp> findByPage(int pageNow, int pageSize) {
		String sql = "select * from emp limit ?,?";
		int startNum = (pageNow-1)*pageSize;
		return DBUtils.queryList(Emp.class, sql, startNum,pageSize);
	}
}