package com.tao.utils;
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 org.springframework.stereotype.Repository;
/**
* @author 陈建
* @描述：dao基础类
* 2018-10-25下午2:54:57
*/
@Repository
public class BaseDao {
//四大金刚
static String driver="com.mysql.jdbc.Driver";
static String url="jdbc:mysql://localhost:3306/day35?useSSL=false";
static String user="root";
static String password="root";
//加载驱动
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取数据库连接
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//关闭数据库连接
public static void closeConnection(Connection conn ,PreparedStatement ps ,ResultSet rs) {
if(rs!=null){
try {
rs.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 List<Map<String,Object>> executeQuery(String sql,Object...params) {
List<Map<String,Object>> objList= new ArrayList<Map<String,Object>>();
Connection conn = getConnection();
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = conn.prepareStatement(sql);
ps = setParams(ps, params);
//执行sql
rs = ps.executeQuery();
//获取表元数据信息
ResultSetMetaData metaData = rs.getMetaData();
//解析结果集
while(rs.next()){
Map<String,Object> row= new HashMap<String, Object>();
//metaData.getColumnCount()获取列数
for (int i = 0; i < metaData.getColumnCount(); i++) {
row.put(metaData.getColumnName(i+1), rs.getObject(i+1));
}
objList.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeConnection(conn, ps, rs);
}
return objList;
}
//增删改通用方法
public boolean executeUpdate(String sql,Object...params) {
Connection conn = getConnection();
PreparedStatement ps=null;
try {
ps = conn.prepareStatement(sql);
ps = setParams(ps, params);
//执行sql
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(conn, ps, null);
}
return false;
}
//设值通用方法
public PreparedStatement setParams(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;
}
}