封装JDBC工具类

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;



public class JdbcUtil {
	/**
	 * 获取链接
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
       public static Connection getConnection() throws ClassNotFoundException, SQLException {
    	   Properties pro=PropertiesUtil.getProPety("jdbc.properties");
    	   String url=pro.getProperty("url");
    	   String driver=pro.getProperty("driver");
    	   String user=pro.getProperty("user");
    	   String password=pro.getProperty("password");
    	   //加载驱动
    	   Class.forName(driver);
    	   //创建链接
    	   Connection conn=(Connection) DriverManager.getConnection(url, user, password);
    	   
    	   return conn;
       }  
       
       
       public static void closeResource(Connection conn,PreparedStatement ps,ResultSet rs) {
    	   if(conn!=null) {
    		   try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	   }
    	   if(ps!=null) {
    		   try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	   }
    	   if(rs!=null) {
    		   try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	   }
       }
}

package oneday;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;



import util.JdbcUtil;

public class TestJdbcUtil {
       public static void main(String[] args) { 
        	   Connection conn=null;
        	   PreparedStatement st=null;
        	   //加载驱动
        	   //创建链接
        	   try {
    			conn=JdbcUtil.getConnection();
    			//获取PreparedStatement对象
    			String sql="insert into classroom values(null,?,?)";
    			st=conn.prepareStatement(sql);
    			//执行SQL语句
    			st.setString(1, "matlab");
    			st.setString(2, "建模师");
    			int count=st.executeUpdate();
    			if(count==1) {
    				System.out.println("插入成功");
    			}else {
    				System.out.println("插入失败");
    			}
    		} catch (ClassNotFoundException | SQLException e) {
    			
    			e.printStackTrace();
    		}finally {
    			if(st!=null) {
    				try {
    					st.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    			if(conn!=null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
           }
	}

package classname;

public class ClassRoom {
       private int id;
       private String name;
       private String descs;
       
	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 getDescs() {
		return descs;
	}

	public void setDescs(String descs) {
		this.descs = descs;
	}
    
	public ClassRoom() {
		
	}

	public ClassRoom(int id, String name, String descs) {
		this.id = id;
		this.name = name;
		this.descs = descs;
	}
	

	public ClassRoom(String name, String descs) {
		this.name = name;
		this.descs = descs;
	}

	@Override
	public String toString() {
		return "ClassRoom [id=" + id + ", name=" + name + ", descs=" + descs + "]";
	}
	
       
}

package util;

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;


public class DBhelper {
/**
 * sql工具类，帮助我们完成一些常见sql操作
 * 
 */
	
	public int update(String sql,Object...args) {     //可变参数，JDK1.8提供 ，类似Object [] args 
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			//为？赋值
			for(int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			//执行SQL
			int count=ps.executeUpdate();
			conn.commit();
			return count;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
			JdbcUtil.closeResource(conn, ps, null);
		}
		return -1;
	}
	
	//不用，也不推荐用
	/*public ResultSet query(String sql,Object...args) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=JdbcUtil.getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			rs=ps.executeQuery();
			
			return rs;
		} catch (ClassNotFoundException | SQLException e) {
			
			e.printStackTrace();
		}finally {
			//JdbcUtil.closeResource(conn, ps, null);
		}
		return null;
	}*/
	
	public <T>T queryOne(Class<T> clzz, String sql,Object...args) throws InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		T t=null;
		try {
			conn=JdbcUtil.getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			rs=ps.executeQuery();
			ResultSetMetaData rsmd=ps.getMetaData();
			int columCount=rsmd.getColumnCount();
			if(rs.next()) {
				//通过反射获取字节码对应的对象
				t=clzz.newInstance();
				for(int i=0;i<columCount;i++) {
					String columnLabel=rsmd.getColumnLabel(i+1);
					Object columnvalue=rs.getObject(columnLabel);
					//Object columnvalue=rs.getObject(i+1);
					//使用字节码获取对应属性
					Field field=clzz.getDeclaredField(columnLabel);
					//设置属性的访问权限
					field.setAccessible(true);
					field.set(t, columnvalue);
				}
				
			}
			
		} catch (ClassNotFoundException | SQLException e) {
			
			e.printStackTrace();
		}finally {
			
		}
		return t;
	}
}

package oneday;

import static org.junit.jupiter.api.Assertions.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.jupiter.api.Test;

import classname.ClassRoom;
import util.DBhelper;
import util.JdbcUtil;

class TestDBhelper {
    private DBhelper helper=new DBhelper();
    @Test
   	void testupdateByInsert() {
   		ClassRoom cr=new ClassRoom("老干部进修班","一群成功人士");
   		String sql="insert into classroom values(?,?,?)";
   		int count=helper.update(sql,cr.getId(),cr.getName(),cr.getDescs());
   		if(count>0) {
   			System.out.println("输入成功");
   		}else {
   			System.out.println("输入失败");
   		}
   	}
   	@Test
   	void testupdateByDel() {
   		String sql="delete from classroom where id=?";
   		int count=helper.update(sql,26);
   		if(count>0) {
   			System.out.println("删除成功");
   		}else {
   			System.out.println("删除失败");
   		}
   	}
 	@Test
 	void testupdateByUpdate() {
 		ClassRoom cr=new ClassRoom(30,"kkkjnhiujn","dfffsdg");
 		String sql="update classroom set name=?,descs=? where id=?";
 		int count=helper.update(sql,cr.getName(),cr.getDescs(),cr.getId());
 		if(count>0) {
 			System.out.println("更新成功");
 		}else {
 			System.out.println("更新失败");
 		}
 	}
//	@Test
//	void testRs() {
//		String sql="select * from classroom";
//		ResultSet rs=helper.query(sql);
//		List<ClassRoom> crs= new ArrayList<ClassRoom>();
//		try {
//			ClassRoom cr=null;
//			while(rs.next()) {
//				cr=new ClassRoom();
//				cr.setId(rs.getInt("id"));
//				cr.setName(rs.getNString("name"));
//				cr.setDescs(rs.getString("descs"));
//				crs.add(cr);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			JdbcUtil.closeResource(null, null, rs);
//		}
		//第一种打印方式
//		for(int i=0;i<crs.size();i++) {
//			System.out.println(crs.get(i));
//		}
		//第二种打印方式
//		for(ClassRoom c:crs) {
//			System.out.println(c);
//		}
//		crs.forEach(System.out::println);
//	}
	
	@Test
  	void testQueryOne() throws InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		String sql="select * from classroom where id=?";
		ClassRoom room=helper.queryOne(ClassRoom.class, sql, 1);
		System.out.println(room);
	}
}