JAvaのDBHelper

7917 ワード

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.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

public class DBHelper{
//     
private Connection conn = null;
//   PreparedStatement  
private PreparedStatement psts = null;
//   ResultSet  
private ResultSet rs = null;
//         
private static String className = "com.mysql.jdbc.Driver";
//       URL
private static String url = "jdbc:mysql://localhost:3306/myweb";
//         
private static String user = "root";
//         
private static String pwd = "root";
//  ThreadLocal   
private static ThreadLocal threadlocal = new ThreadLocal<>();

static {
	
	try {
		InputStream in = DBHelper.class.getResourceAsStream("/dbconfig.properties");
		Properties p = new Properties();
		
		p.load(in);
		
		//     
		className = p.getProperty("jdbc.className");
		//       URL
		url = p.getProperty("jdbc.url");
		//         
		user = p.getProperty("jdbc.user");
		//         
		pwd = p.getProperty("jdbc.password");	
	} catch (IOException e) {
		e.printStackTrace();
		System.out.println("          。");
	}
		
}

// 2.     
static {
	try {
		//     
		Class.forName(className);

	} catch (ClassNotFoundException e) {
		e.printStackTrace();
		System.out.println("         !");
	}
}

// 3.     
/**
 *         
 */
private void getConnection() throws SQLException {
	//          
	conn = threadlocal.get();
	if (conn == null || conn.isClosed()) {
		conn = DriverManager.getConnection(url, user, pwd);
		threadlocal.set(conn);
	}
}

/**
 *            
 * 
 * @param sql
 *            SQL  
 * @param objs
 *            SQL       ,    
 * @return        
 */
public long executeInsert(String sql, Object... objs) throws SQLException {
	//     
	getConnection();
	//   PreparedStatement    
	psts = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
	//       ,     ?  
	setParameters(objs);
	//   SQL        
	psts.executeUpdate();
	//            
	rs = psts.getGeneratedKeys();
	//         
	if (rs.next())
		return rs.getLong(1);
	else
		return -1;
}

/**
 *     (  、  、  )   
 * 
 * @param sql
 *            SQL  
 * @param objs
 *            SQL       ,    
 * @return     
 * @throws SQLException
 *                  
 */
public int executeUpdate(String sql, Object... objs) throws SQLException {
	//     
	getConnection();
	//   PreparedStatement    
	psts = conn.prepareStatement(sql);
	//       ,     ?  
	setParameters(objs);
	//   SQL        
	return psts.executeUpdate();
}

/**
 *        
 * 
 * @param sql
 *            SQL  
 * @param objs
 *            SQL       ,    
 * @return      
 * @throws SQLException
 */
public ResultSet executeQuery(String sql, Object... objs) throws SQLException {
	//     
	getConnection();

	//   PreparedStatement    
	psts = conn.prepareStatement(sql);
	//       ,     ?  
	setParameters(objs);
	//   SQL       
	return psts.executeQuery();
}

/**
 *        
 * 
 * @param sql
 *            SQL  
 * @param objs
 *            SQL       ,    
 * @return     
 * @throws SQLException
 */
public Object executeScalar(String sql, Object... objs) throws SQLException {
	//     
	getConnection();
	//   PreparedStatement    
	psts = conn.prepareStatement(sql);
	//       ,     ?  
	setParameters(objs);
	//   SQL       
	rs = psts.executeQuery();
	
	//          
	if (rs.next())
		return rs.getObject(1);
	else
		return null;
}
/**
 *       (IRowMapper  :  )
 * @param rowmapper
 * @param sql
 * @param objs
 * @return       
 * @throws SQLException 
 */
public List getList(IRowMapper rowmapper,String sql, Object... objs) throws SQLException{
	//       
	rs = executeQuery(sql, objs);
	
	//  
	List list = new ArrayList<>();
	//     
	while(rs.next()) {
	   T t=	rowmapper.rowMapper(rs);
	   list.add(t);
	}
	return list;
}

/**
 *       (    )
 * @param tclass
 * @param sql
 * @param objs
 * @return
 * @throws SQLException
 * @throws SecurityException 
 * @throws NoSuchFieldException 
 * @throws IllegalAccessException 
 * @throws InstantiationException
 */
public List getList(Class tclass, String sql, Object... objs) throws SQLException, NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException{
	//       
	rs = executeQuery(sql, objs);
	//              
    List list = new ArrayList();
    //       (       )
    //              ,      .  。  。             
    Field[] fs = tclass.getDeclaredFields();
    //      
    if (rs != null) {
        while (rs.next()) {
            //     
          T t = (T) tclass.newInstance();
            //   
            for (int i = 0; i < fs.length; i++) {
                /*
                 * fs[i].getName():     
                 * 
                 * f:       
                 */
                Field f = t.getClass().getDeclaredField(fs[i].getName());
                //   true            
                f.setAccessible(true);
                /*
                 * f.getType().getName():         
                 */
                //            
                if (f.getType().getName().equals(String.class.getName())) {
                    f.set(t, rs.getString(fs[i].getName())); 
                } else if (f.getType().getName().equals(int.class.getName())) {
                    f.set(t, rs.getInt(fs[i].getName()));
                }
                else if (f.getType().getName().equals(short.class.getName())) {
                    f.set(t, rs.getShort(fs[i].getName()));
                }
                else if (f.getType().getName().equals(byte.class.getName())) {
                    f.set(t, rs.getByte(fs[i].getName()));
                }
                else if (f.getType().getName().equals(long.class.getName())) {
                    f.set(t, rs.getLong(fs[i].getName()));
                }
                else if (f.getType().getName().equals(double.class.getName())) {
                    f.set(t, rs.getDouble(fs[i].getName()));
                }
                else if (f.getType().getName().equals(float.class.getName())) {
                    f.set(t, rs.getFloat(fs[i].getName()));
                }
                else if (f.getType().getName().equals(Date.class.getName())) {
                	f.set(t, rs.getDate(fs[i].getName()));
                }
                else if (f.getType().getName().equals(boolean.class.getName())) {
                    f.set(t, rs.getBoolean(fs[i].getName()));
                }
            }

            list.add(t);
        }
    }
    //     
    return list;
}


/**
 *   SQL   
 * 
 * @param objs
 * @throws SQLException
 */
private void setParameters(Object... objs) throws SQLException {
	if (objs != null)
		for (int i = 0; i < objs.length; i++) {
			psts.setObject(i + 1, objs[i]);
		}
}

/**
 *     
 * @throws SQLException
 */
public void beginTransaction() throws SQLException {
	//     
	getConnection();
	conn.setAutoCommit(false);
}
/**
 *     
 * @throws SQLException
 */
public void commitTransaction() throws SQLException {
	conn.commit();
}

/**
 *     
 * @throws SQLException
 */
public void rollbackTransaction() throws SQLException {
	conn.rollback();
}

// 8.        
/**
 *        
 */
public void close() {
	try {
		if (rs != null)
			rs.close();
	} catch (SQLException e) {
		rs = null;
		e.printStackTrace();
	}

	try {
		if (psts != null)
			psts.close();
	} catch (SQLException e) {
		psts = null;
		e.printStackTrace();
	}
	if (conn != null)
		conn = null;
	}
}
interface IRowMapper {
  public T rowMapper(ResultSet rs) throws SQLException;
}