javaee jdbc工具类

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/taobao?characterEncoding=utf-8
uname=root
pwd=root

package com.test.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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.print.attribute.standard.RequestingUserName;





public class DbHelper {
	
	private Connection connection;
	
	private PreparedStatement preparedStatement;
	
	private ResultSet resultSet;
	
	public DbHelper()
	{
		getConnection();
	}
	//打开连接
	public void getConnection()
	{
		 try {			
			  if(connection==null || connection.isClosed())
			  {
				//将文件中的数据转到集合中 再从集合取数据
				Properties properties=new Properties();
				
				InputStream iStream=this.getClass().getResourceAsStream("/db.properties");
				
			    properties.load(iStream);
			    
			    //获得集合中数据
			    String driver=properties.getProperty("driver");
			    
			    String url=properties.getProperty("url");
			    
			    String uname=properties.getProperty("uname");
			    
			    String pwd=properties.getProperty("pwd");
				  
				  
			    Class.forName(driver);
			 
			    this.connection= DriverManager.getConnection(url, uname, pwd);
			  }
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		 
		 
	}	
	
	//执行 增 删 改 语句 返回受影响的行数
	public int executeUpdate(String sql,List<Object> paramList)
	{
		 getConnection();   //打开连接
		 
		 try {
			
			 this.preparedStatement=connection.prepareStatement(sql);
			
			 //给sql语句的参数赋值
			 if(paramList!=null)
			 {
				  for(int i=0;i<paramList.size();i++)
				  {
					  this.preparedStatement.setObject(i+1, paramList.get(i));
				  }
			 }
			 
			 return this.preparedStatement.executeUpdate();
			 
			 
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close();
		}
		 
		return 0;
	}
	
	//执行查询
	public List<Map<String, Object>> executeQuery(String sql,List<Object> paramList)
	{
		 getConnection();
		 
		 try {
			 this.preparedStatement=connection.prepareStatement(sql);
			
			 //给sql的参数赋值
			 if(paramList!=null)
			 {
				  for(int i=0;i<paramList.size();i++)
				  {
					    this.preparedStatement.setObject(i+1, paramList.get(i));
				  }
			 }
			 
			 //执行查询
			 this.resultSet= this.preparedStatement.executeQuery();
			 
			 //定义一个集合用来存放结果集中的数据
			 List<Map<String, Object>> resultList=new ArrayList<Map<String,Object>>();
			 
			 //读取结果集中的数据存入到集合resultList中
			 
			 //获得结果集中的列名
			 ResultSetMetaData resultSetMetaData= this.resultSet.getMetaData();
			 
			 
			 while(resultSet.next())
			 {
				 //存放结果集中的每一条记录  uid=1 uname="zhangsan" pwd="123"
				 Map<String, Object> map=new HashMap<String,Object>();
				 
				 for(int i=1;i<=resultSetMetaData.getColumnCount();i++)
				 {
					   //获得第i列的名称
					   String columnname=  resultSetMetaData.getColumnName(i);
				       //获得第i列的值
					   Object columnvalue=resultSet.getObject(columnname);
					   
					   map.put(columnname, columnvalue);
					   
				 }
				 
				 //将map存入到list中
				 resultList.add(map);
				 
			 }
			 
			 return resultList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close();
		}
		 
		return null;
		 
	}
	
	//关闭连接
	public void close()
	{
		 if(resultSet!=null)
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		 
		 if(preparedStatement!=null)
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		 
		 if(connection!=null)
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	

}