使用反射的方法实现JDBC数据库连接与事物

/*****************************************************创建链接数据库工具类********************************************/

package com.gs.stu.util;

import java.io.IOException;
import java.io.InputStream;
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.util.HashMap;
import java.util.Map;
import java.util.Properties;






public class JDBCUtil {
private static Properties pro = new Properties();
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
InputStream in = JDBCUtil.class.
getResourceAsStream("jdbc.properties");
pro.load(in);
String driverclass = pro.getProperty("driverclass");
String jdbcurl = pro.getProperty("jdbcurl");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
Class.forName(driverclass);
Connection conn = (Connection) DriverManager.getConnection(jdbcurl, user, password);
return conn;
}
// public static <T>T get(Class<T> clazz,String sql,Object...args){
// T entity = null;
// Connection conn = null;
// PreparedStatement ps = null;
// ResultSet rs = null;
// try {
// //1.得到ResultSet对象
// conn = JDBCUtil.getConnection();
// ps = conn.prepareStatement(sql);
// for(int i = 0 ; i<args.length;i++){
// ps.setObject(i+1, args[i]);
// }
// rs = ps.executeQuery();
// //2.得到ResultSetMetaData对象
// ResultSetMetaData rsmd = rs.getMetaData();
// //3.创建一个Map<String,Object>对象，键：SQL查询的列的别名。值：列的值
// Map<String,Object>values = new HashMap<>();
// //4.处理结果集，利用ResultSetMetaDate填充3对应的Map对象
// if(rs.next()){
// for(int i = 0; i <rsmd.getColumnCount();i++){
// String columnLabel = rsmd.getColumnLabel(i+1);
// Object columnValue = rs.getObject(i+1);
// values.put(columnLabel, columnValue);
// }
// }
// //5.若Map不为空集，利用反射创建clazz对应的对象
// if(values.size()>0){
// entity = clazz.newInstance();
// //6.遍历Map对象，利用反射为class对象的对应的属性赋值
// for(Map.Entry<String, Object> entry:values.entrySet()){
// String fieldName= entry.getKey();
// Object value = entry.getValue();
// ReflectionUtils.setFieldValue(entity, fieldName, value);
// }
// }
// } catch (ClassNotFoundException | InstantiationException
// | IllegalAccessException | IOException | SQLException e) {
// e.printStackTrace();
// }finally{
// closeResultSet(rs);
// closePrepareStatement(ps);
// closeConnection(conn);
// }
// return entity;
//
// }

public static <T> T get(Class<T> clazz, String sql, Object... args) {
T entity = null;


Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;


try {
//1. 得到 ResultSet 对象
connection = JDBCUtil.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();


//2. 得到 ResultSetMetaData 对象
ResultSetMetaData rsmd = resultSet.getMetaData();

//3. 创建一个 Map<String, Object> 对象, 键: SQL 查询的列的别名, 
//值: 列的值
Map<String, Object> values = new HashMap<>();

//4. 处理结果集. 利用 ResultSetMetaData 填充 3 对应的 Map 对象
if(resultSet.next()){
for(int i = 0; i < rsmd.getColumnCount(); i++){
String columnLabel = rsmd.getColumnLabel(i + 1);
Object columnValue = resultSet.getObject(i + 1);

values.put(columnLabel, columnValue);
}
}

//5. 若 Map 不为空集, 利用反射创建 clazz 对应的对象
if(values.size() > 0){
entity = clazz.newInstance();

//5. 遍历 Map 对象, 利用反射为 Class 对象的对应的属性赋值. 
for(Map.Entry<String, Object> entry: values.entrySet()){
String fieldName = entry.getKey();
Object value = entry.getValue();
ReflectionUtils.setFieldValue(entity, fieldName, value);
}
}



} catch (Exception e) {
e.printStackTrace();
} finally {
closeResultSet(resultSet);
closePrepareStatement(preparedStatement);
closeConnection(connection);
}


return entity;
}

public static int update(String sql,Object ... args){
Connection conn = null;
PreparedStatement ps = null;
int row = 0 ;
try {
conn = getConnection();
ps = (PreparedStatement) conn.prepareStatement(sql);
for(int i = 0 ; i<args.length;i++){
ps.setObject(i+1, args[i]);
}
row = ps.executeUpdate();

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
closePrepareStatement(ps);
closeConnection(conn);
}
return row ;
}

public static void closeConnection(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closePrepareStatement(PreparedStatement ps){
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeResultSet(ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public void show() throws ClassNotFoundException, IOException, SQLException{
System.out.println(getConnection());
}

}

/**********************************************使用增、删、改、查的方法*************************************/

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.gs.stu.dao.impl;


import com.gs.stu.bean.Course;
import com.gs.stu.bean.Schedule;
import com.gs.stu.bean.Teacher;
import com.gs.stu.bean.User;
import com.gs.stu.util.JDBCUtil;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 *
 * @author Administrator
 */
public class SysScheduleDaoImpl {
    
    public Schedule findScheduleById(int id) {
Schedule schedule = null;
try {
JDBCUtil.getConnection();
String sql="select * from Schedule where id=?";
schedule = JDBCUtil.get(Schedule.class, sql, id);
} catch (ClassNotFoundException | IOException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return schedule;
}
       // where teacherId=?
         public List<Schedule> findAllByTeacherId(int teacherId) {
List<Schedule> list = new ArrayList<Schedule>();
Schedule schedule = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
                int teacherId_temp=teacherId;
try {
conn= JDBCUtil.getConnection();
String sql = "select * from Schedule where teacherId=? ";
ps = conn.prepareStatement(sql);
                        ps.setInt(1,teacherId);
rs = ps.executeQuery();
while(rs.next()){
                        schedule = new Schedule();
                        schedule.setId(rs.getInt(1));
                        schedule.setCouseId(rs.getInt(2));
                        schedule.setTeacherId(rs.getInt(3));
                        schedule.setTime(rs.getString(4));
                        schedule.setAddress(rs.getString(5));
                        schedule.setLimitNumber(rs.getInt(6));
                        schedule.setActualNumber(rs.getInt(7));
                        list.add(schedule);
                    }
} catch (ClassNotFoundException | IOException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
         
    public List<Schedule> findAllSchedule() {
List<Schedule> list = new ArrayList<Schedule>();
Schedule schedule = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn= JDBCUtil.getConnection();
String sql = "select * from Schedule";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
                        schedule = new Schedule();
                        schedule.setId(rs.getInt(1));
                        schedule.setCouseId(rs.getInt(2));
                        schedule.setTeacherId(rs.getInt(3));
                        schedule.setTime(rs.getString(4));
                        schedule.setAddress(rs.getString(5));
                        schedule.setLimitNumber(rs.getInt(6));
                        schedule.setActualNumber(rs.getInt(7));
                        list.add(schedule);
                    }
} catch (ClassNotFoundException | IOException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
    
    public int deleteSchedule(int id) {
                int row = 0;
                try {
                        JDBCUtil.getConnection();
                        String sql ="delete from Schedule where id=?";
                        row = JDBCUtil.update(sql, id);
                } catch (ClassNotFoundException | IOException | SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return row;
}


    public int addSchedule(Schedule sch) {
        int row = 0;
        try {
                JDBCUtil.getConnection();
                String sql = "insert into Schedule(courseId,teacherId,time,address,limitNumber,actualNumber) values(?,?,?,?,?,?)";
                row = JDBCUtil.update(sql, sch.getCouseId(),sch.getTeacherId(),
                        sch.getTime(),sch.getAddress(),sch.getLimitNumber(),sch.getActualNumber());
        } catch (ClassNotFoundException | IOException | SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
        return row;
                
  }
   public int updateSchedule(Schedule sch) {
int row = 0;
try {
                    JDBCUtil.getConnection();
                    String sql ="update Schedule set courseId=?,teacherId=?,time=?,address=?,limitNumber=?,actualNumber=? where id=?";
                    row = JDBCUtil.update(sql, sch.getCouseId(),sch.getTeacherId(),
                    sch.getTime(),sch.getAddress(),sch.getLimitNumber(),sch.getActualNumber());
} catch (ClassNotFoundException | IOException | SQLException e) {
// TODO Auto-generated catch block
                    e.printStackTrace();
}
return row;
} 
}