package com.until;
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.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class BaseDao {
//定义四大金刚
static String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
static String url="jdbc:sqlserver://localhost:1433; DatabaseName=qy67";
static String user="sa";
static String password="123456";
PreparedStatement ps=null;
ResultSet rs=null;
//1.加载驱动
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2.获取数据库连接
public static Connection getConnection() {
Connection conn=null;
try {
conn=DriverManager.getConnection(url, user, password);//获取连接
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//6.关闭数据库连接
public static void closeConnection(ResultSet rs,PreparedStatement ps,Connection conn) {
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public PreparedStatement setParam(PreparedStatement ps,Object[] params) {
//循环设置参数
if(params!=null){
for (int i = 0; i < params.length; i++) {
try {
ps.setObject(i+1, params[i]);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return ps;
}
public boolean executeUpdate(String sql,Object[] params) {
Connection conn=getConnection();
try {
ps=conn.prepareStatement(sql);
ps=setParam(ps, params);
int ret=ps.executeUpdate();
if(ret>0){
System.out.println("操作成功！");
return true;
}else{
System.out.println("操作失败！");
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeConnection(null, ps, conn);
}
return false;
}
public List<Map<String, Object>> executeQuery(String sql,Object[] params){
List<Map<String, Object>> objectList=new ArrayList<Map<String, Object>>();
Connection conn=getConnection();
try{
ps=conn.prepareStatement(sql);
ps=setParam(ps, params);
rs=ps.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
while(rs.next()){
Map<String, Object> rowMap=new LinkedHashMap<String, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {//循环遍历所有列
rowMap.put(rsmd.getColumnName(i+1), rs.getObject(i+1));
}
objectList.add(rowMap);
}
}catch(SQLException e){
e.printStackTrace();
}finally{
closeConnection(rs, ps, conn);
}
return objectList;
}
}
