6.封装对数据库CRUD操作的工具类

工具类

package cn.cc.mvc.util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class DbUtil {
	private static Connection con=null;
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/day7","root","cuichen975541045");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//执行insert update delete语句的方法,sql中无?
	public int executeOper(String sql) {
		PreparedStatement pstm = null;
		int result = 0;
		try {
			pstm = con.prepareStatement(sql);
			result = pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	//执行insert update delete语句的方法,sql语句中有?
	public int executeOper(String sql,List<Object> para) {
		int result = 0;
		PreparedStatement pstm = null;
		try {
			pstm = con.prepareStatement(sql);
			//给sql语句中的?赋值
			for (int i = 0; i < para.size(); i++) {
				Object o = para.get(i);
				if (o instanceof String) {
					pstm.setString(i+1, o.toString());
				}
				if (o instanceof Integer) {
					pstm.setInt(i+1, Integer.parseInt(o.toString()));
				}
				if (o instanceof Double) {
					pstm.setDouble(i+1, Double.parseDouble(o.toString()));
				}
			}
			result = pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	//执行select 语句中没有?
	public ResultSet executeSelect(String sql) {
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = con.prepareStatement(sql);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	//执行select 语句中有?
	public ResultSet executeSelect(String sql,List<Object> para) {
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = con.prepareStatement(sql);
			for (int i = 0; i < para.size(); i++) {
				Object o = para.get(i);
				if (o instanceof Integer) {
					pstm.setInt(i+1, Integer.parseInt(o.toString()));
				}
				if (o instanceof String) {
					pstm.setString(i+1, o.toString());
				}
				if (o instanceof Double) {
					pstm.setDouble(i+1, Double.parseDouble(o.toString()));
				}
			}
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	//关闭资源的方法
	public void closeResource(ResultSet rs,PreparedStatement pstm,Connection con) {
		try {
			if (rs!=null) {
				rs.close();
			}
			if (pstm!=null) {
				pstm.close();
			}
			if (con!=null) {
				con.close();
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}
}