package com.conn;
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;
public class DBConnection {
private PreparedStatement ps=null;
private ResultSet rs=null;
private Connection conn=null;
private static String driver;
private static String url;
private static String user;
private static String password;
static{
try {
Properties p=new Properties();
InputStream in=DBConnection.class.getClassLoader().getResourceAsStream("jdbc.properties");
p.load(in);
driver=p.getProperty("driver");
url=p.getProperty("url");
user=p.getProperty("user");
password=p.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public Connection getConn(){
try {
Class.forName(driver);
return DriverManager.getConnection(url,user,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public void closeAll(){
try {
if(rs!=null)
rs.close();
if(ps!=null)
ps.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 执行增删改的方法
* @param param
* @param sql
* @return
*/
public int updateSQL(String sql,Object... param){
int num=0;
conn=this.getConn();
try {
ps=conn.prepareStatement(sql);
if(param!=null){
for(int i=0;i<param.length;i++){
ps.setString(i+1, param[i].toString());
}
num=ps.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
this.closeAll();
}
return num;
}
/**
* 执行查询的通用方法
* @param sql
* @param param
* @return集合
*/
public List<Map<String ,String>> excuteSQL(String sql,String...param){
List list=null;
conn=this.getConn();
try {
ps=conn.prepareStatement(sql);
if(param!=null){
for(int i=0;i<param.length;i++){
ps.setString(i+1, param[i]);
}
rs=ps.executeQuery();
ResultSetMetaData m = rs.getMetaData();
if(rs!=null){
list =new ArrayList();
while(rs.next()){
Map<String,String> map=new HashMap<String,String>();
for(int i=0;i<m.getColumnCount();i++){
map.put(m.getColumnName(i+1), rs.getString(i+1));
}
list.add(map);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
this.closeAll();
}
return list;
}
}