import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql. Statement ;
import java.sql.Timestamp;
import java.sql.Types;
import javax. servlet .jsp.tagext.TryCatchFinally;
import com.mysql. jdbc .Driver;
import com.sun.xml. internal .ws. close able;
public class DbManager {
public static Connection getConnection() throws SQLException {
return getConnection(“test”, “root”, “root”);
}
private static Connection getConnection(String dbName, String userName,
String password) throws SQLException {
// 获取数据库连接
String url = “jdbc:mysql://localhost:3306/” + dbName
+ “useUnicode=true&characterEncoding=utf-8”;
// 注册驱动
DriverManager.registerDriver(new Driver());
return DriverManager.getConnection(url, userName, password);
}
public static void set params (PreparedStatement preStmt,Object… params) throws SQLException{
if(params==null||params.length==0)//如果没有参数，直接返回
return;
for(int i=1;i<params.length;i++){//依次设置参数
Object param=params[i-1];
if(param==null){
preStmt.setNull(i, Types.NULL);
}else if(param instanceof Integer ){//设置Integer参数
preStmt.setInt(i, (Integer)param);
}else if(param instanceof String){
preStmt.setString(i, (String)param);//设置String参数
}else if (param instanceof Double){
preStmt.setDouble(i, (Double)param);
}else if (param instanceof Long){
preStmt.setLong(i, (Long)param);
}else if(param instanceof Timestamp){
preStmt.setTimestamp(i, (Timestamp)param);
}else if (param instanceof Boolean){
preStmt.setBoolean(i, (Boolean)param);
}else if(param instanceof Date){
preStmt.setDate(i, (Date)param);
}
}
}
public static int executeUpdate(String sql) throws SQLException{
//执行sql,返回影响的行数
return executeUpdate(sql,new Object[]{});
}
public static int executeUpdate(String sql,Object… params) throws SQLException{
//执行sql,返回影响的行数
Connection conn=null;// Connection对象
PreparedStatement preStmt =null;
try {
conn =getConnection();
preStmt = conn.prepareStatement(sql);//预编译带参数的sql语句
setParams(preStmt, params);//设置参数
return preStmt.executeUpdate();//返回执行结果
} finally {
if(preStmt!=null)preStmt.close();//关闭preStmt
if(conn!=null)conn.close();//关闭conn
}
}
/***
* 获取总数。
* @param sql 格式必须为select count(*) from …
* @return
* @throws SQLExcption
*/
public static int getCount(String sql) throws SQLException{
Connection conn=null;
Statement stmt = null;
ResultSet rs =null;
try{
conn =getConnection();
stmt =conn.createStatement();
rs=stmt.executeQuery(sql);
return rs.getInt(1); //返回第一列数据
}finally{
if(rs!=null)rs.close();
if(stmt!=null)stmt.close();
if(conn!=null)conn.close();
}
}
}
