JavaJDBC基本操作(增,刪,該,查)總結

package trade.axht.java.dao;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;

import trade.axht.java.conn.JDBCUtils;

import java.util.*;
import java.lang.reflect.*;
import java.sql.*;
/**
 * 
*基類帶泛型，派生類可以帶泛型參數繼承該類，通過反射對Beans對象操作
 * @author Administrator
 *
 * @param 
 */
public class DAO {//帶泛型的基類，派生類可以帶具體beans泛型參數繼承該類

	public Class clazz;
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public DAO(){
	
	System.out.println(getClass()); //打印class trade.axht.java.dao.userImpl.****DaoImpl
	Type type=getClass().getGenericSuperclass(); 
	System.out.println(type);
	/**獲取繼承【該類(DAO)】帶具體泛型的基類 或者獲取此類的基類（Object)，簡單的講就是誰繼承Dao這個類后，在加載的時候，就會來加載這個構造函數。加載class的對象為Dao的派生類。派生類的基類就是Dao,并能獲取基類帶有的具體泛型。
	JDK文檔描述是這樣的：返回表示此Class所表示的實體（類、接口、基本類型或 void）的直接超類的Type。如果超類是參數化類型，則返回的對象必須準確反映源///代碼中所使用的實際類型參數。
	所以最終打印為**/
	ParameterizedType parameterizedType=(ParameterizedType)type;//ParameterizedType 表示參數化類型，如 Collection。
	System.out.println(parameterizedType);
	Type[] ars=parameterizedType.getActualTypeArguments();/**返回表示此類型實際類型參數的對象的數組。就是返回Collection中的泛型參數T,V類型的Type表示形式。Type 是 Java 編程語言中所有類型的公共高級接口。它們包括原始類型、參數化類型、數組類型、類型變量和基本類型。**/
	System.out.println(ars);
	clazz=(Class) ars[0];//獲取泛型參數的第一個Class對象
	System.out.println(clazz);
	//	System.out.println(clazz);
	}
	/**
	 * 增，刪，改操作
	 * @param sql
	 * @param args
	 * @return
	 */
	public int executeUpdate(String sql,Object...args){
	Connection connection=JDBCUtils.getConnection();
	PreparedStatement preparedStatement=null;
	try {
	preparedStatement=connection.prepareStatement(sql);
	if(args!=null&&args.length>0){
	for(int i=0;i getForList(String sql, Object... args) {

	List list = new ArrayList<>();

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

	try {
	//1. 得到結果集
	connection = JDBCUtils.getConnection();
	preparedStatement = connection.prepareStatement(sql);
	if(args!=null&&args.length>0){
	for (int i = 0; i < args.length; i++) {
	preparedStatement.setObject(i + 1, args[i]);
	}
	}

	resultSet = preparedStatement.executeQuery();
	
	//2處理結果解，得到Map的list，其中一個Map對象就是一條記錄。
	//Map的key為resultSet中的列的別名，Map的value為列的值
	List> values = 
	handleResultSetToMapList(resultSet);
	
	//3.把List> 轉化成Class對象(clazz)的實例集List
	list = transfterMapListToBeanList( values);

	} catch (Exception e) {
	e.printStackTrace();
	} finally {
	JDBCUtils.releaseConnection(connection,preparedStatement,resultSet);
	}

	return list;
	}
	
	
	public List transfterMapListToBeanList(List> values) throws InstantiationException,
	IllegalAccessException, InvocationTargetException {

	List result = new ArrayList<>();

	T bean = null;

	if (values.size() > 0) {
	for (Map m : values) {
	bean = clazz.newInstance();
	for (Map.Entry entry : m.entrySet()) {
	String propertyName = entry.getKey();
	Object value = entry.getValue();
	//利用org.apache.commons.beanutils.BeanUtils工具類反射設置對象屬性
	BeanUtils.setProperty(bean, propertyName, value);
	
	/*	try {
	ReflectorUtil.setProperty(bean, propertyName, value);
	} catch (NoSuchFieldException | SecurityException | IllegalArgumentException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}*/
	}
	result.add(bean);
	}
	}

	return result;
	}
	
	
	public List> handleResultSetToMapList(
	ResultSet resultSet) throws SQLException {
	
	List> values = new ArrayList<>();
	//獲取列名
	List columnLabels = getColumnLabels(resultSet);
	Map map = null;
	while (resultSet.next()) {
	map = new HashMap<>();

	for (String columnLabel : columnLabels) {
	Object value = resultSet.getObject(columnLabel);
	map.put(columnLabel, value);
	}
	values.add(map);
	}
	return values;
	}
	
	private List getColumnLabels(ResultSet rs) throws SQLException {
	List labels = new ArrayList<>();

	ResultSetMetaData rsmd = rs.getMetaData();
	for (int i = 0; i < rsmd.getColumnCount(); i++) {
	labels.add(rsmd.getColumnLabel(i + 1));
	}

	return labels;
	}
	public int getCount(String sql,String...id){
	Connection connection=null;
	ResultSet resultSet=null;
	PreparedStatement preparedStatement=null;
	try {
	 connection=JDBCUtils.getConnection();
	 resultSet=null;
	 preparedStatement=connection.prepareStatement(sql);
	if(id!=null&&id.length>0){
	for(int i=1;i<=id.length;i++)
	preparedStatement.setString(i, id[i-1]);
	}
	resultSet=preparedStatement.executeQuery();
	if(resultSet.next()){
	return resultSet.getInt(1);
	}
	else {
	return 0;
	}
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	return 0;
	}finally{
	JDBCUtils.releaseConnection(connection, preparedStatement, resultSet);
	}
	}
	
}

package trade.axht.java.dao.userImpl;

import trade.axht.java.dao.DAO;
import trade.axht.java.dao.ManagerDAO;
import trade.axht.java.domain.Manager;

public class ManagerDaoImpl extends DAO {

	@Override
	public int getCount(Manager manager) {
	// TODO Auto-generated method stub
	String sql="select Count(*) from tb_manager where username=? and password=?";
	return getCount(sql, manager.getUsername(),manager.getPassword());
	
	}

}

package trade.axht.java.domain;

public class Manager {
	private int id;
	private String username;
	private String password;
	public int getId() {
	return id;
	}
	public void setId(int id) {
	this.id = id;
	}
	public String getUsername() {
	return username;
	}
	public void setUsername(String username) {
	this.username = username;
	}
	public String getPassword() {
	return password;
	}
	public void setPassword(String password) {
	this.password = password;
	}
	public Manager() {
	super();
	// TODO Auto-generated constructor stub
	}
	public Manager(int id, String username, String password) {
	super();
	this.id = id;
	this.username = username;
	this.password = password;
	}
	
}

package trade.axht.java.conn;

import javax.sql.*;
import java.sql.*;
import java.util.*;
import java.io.*;

public class JDBCUtils {
	private static DataSource dataSource=null;

	static{ 
	Properties properties=new Properties();
	InputStream in=JDBCUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");//加載配置文件
	try {
	properties.load(in);
	
	dataSource=org.apache.commons.dbcp2.BasicDataSourceFactory.createDataSource(properties);//利用數據庫連接池(dbcp2)獲取數據源
	
	} catch (Exception e) {
	// TODO Auto-generated catch block
	System.out.println("數據庫連接出錯!");
	e.printStackTrace();
	}
	}
	public static Connection getConnection(){
	try {
	return dataSource.getConnection();
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	
	e.printStackTrace();
	return null;
	}
	}
	public static void releaseConnection(Connection connection,Statement statement,ResultSet resultSet) {
	if (connection!=null) {
	try {
	connection.close();
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	}
	if (statement!=null) {
	try {
	statement.close();
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	}
	if (resultSet!=null) {
	try {
	resultSet.close();
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	}
	}
}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/blog
username=root
password=brozer
initialSize=5
maxIdle=10
maxTotal=50
maxWaitMillis=5000
minIdle=5