java中JDBC基本操作的一个工具类,一些基本方法

package com.help;


import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import com.microsoft.sqlserver.jdbc.SQLServerException;


public final class Dbutils {
//1005和1008常量字段
public static final int TYPE=ResultSet.TYPE_SCROLL_SENSITIVE;
public static final int UPDATE=ResultSet.CONCUR_UPDATABLE;
private Dbutils(){}
//传入参数获取sql2005数据库连接的方法
public static Connection getConnection(String server,
String db,String user,String password){
SQLServerDataSource sds=new SQLServerDataSource();
sds.setServerName(server);
sds.setDatabaseName(db);
sds.setUser(user);
sds.setPassword(password);
Connection connection=null;
try {
connection=sds.getConnection();
} catch (SQLServerException e) {
e.printStackTrace();
}
return connection;
}
public static Connection getConnection(String db){
SQLServerDataSource sds=new SQLServerDataSource();
sds.setServerName("localhost");
sds.setDatabaseName(db);
sds.setUser("sa");
sds.setPassword("123456");
Connection connection=null;
try {
connection=sds.getConnection();
} catch (SQLServerException e) {
e.printStackTrace();
}
return connection;
}
//关闭数据库资源的方法,对此方法重载关闭多种数据库资源
public static void close(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Statement stmt){
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}


//传入标准sql查询语句和Connection返回一个ResultSet(结果集),此SQL只能进行查询不能修改表
public static ResultSet getResultSet(String sql,Connection connection){
Statement stmt=null;
ResultSet rst=null;
try {
stmt=connection.createStatement(Dbutils.TYPE, Dbutils.UPDATE);
rst=stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rst;
}
//传入sql语句,可以对表进行增删改查
public static void Update(String sql,Connection connection){
Statement stmt=null;
int a=0;
try {
stmt=connection.createStatement(Dbutils.TYPE,Dbutils.UPDATE);
a=stmt.executeUpdate(sql);
} catch (SQLException e) {
MyUnit.showMessage(e.getMessage());
}if(a>0){
System.out.println("更新成功");
}
}
//遍历结果集返回一个字符串
public static String getString(ResultSet rs){
ResultSetMetaData rsmt=null;
try {
rsmt=rs.getMetaData();
int a=rsmt.getColumnCount();
while(rs.next()){
for (int i = 1; i <= a; i++) {
if(i!=1){
System.out.print(",");
}
System.out.print(rs.getObject(i));
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//返回一个表结果集行数的方法
public static int getRowCount(Connection connection,String tname){
ResultSet rs=getResultSet("select count(*) from "+"["+tname+"]", connection);

int page=0;
try {
while(rs.next()){
page=(Integer)rs.getObject(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return page;
}
//返回一个分页页数
public static int getPages(Connection connection,String tname,int pagesize){
if(connection==null||tname==null||pagesize<=0){
return 0;
}
int rows=getRowCount(connection, tname);
int pageSize=rows%pagesize==0?rows/pagesize:rows/pagesize+1;
return pageSize;
}
//获得一个带有列名的list
public static List<String> getColumnName(Connection connection,String tname){
PreparedStatement pstmt=null;
ResultSet rs=null;
List<String> ls=new ArrayList<String>();
try {
pstmt=connection.prepareStatement("select* from "+tname +" where 1<>1",Dbutils.TYPE,Dbutils.UPDATE);
rs=pstmt.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
int column=rsmd.getColumnCount();
for (int i = 1; i <=column; i++) {
ls.add(rsmd.getColumnName(i));
}

} catch (SQLException e) {
e.printStackTrace();
}finally{
Dbutils.close(rs);
Dbutils.close(pstmt);
}
return ls;

}
//Jsp和servlet中get请求处理中文乱码的方法
public static String getNewString(String str) throws UnsupportedEncodingException{
return new String(str.getBytes("ISO-8859-1"),"UTF-8");
}
//传入3个参数:Connection,pageSize分页数,tableName,用分页方法返回一个List集合,其中每个元素都是一个结果集
public static String SelectPage(Connection connection,int pageSize,String columnname,String tablename){
//构造分页查询的标准SQL语句,其中row_number() over(order by 列名)函数返回一个自增的自然数列,
//按条件:rowid between ? and ?对表进行分页,其中?为每次分页的起始和结束
String sql="select * from (select row_number() over(order by "+
columnname+") rowid, * from "+tablename+") as temp where rowid between ? and ?";
PreparedStatement pstmt=null;
int rows = Dbutils.getRowCount(connection, tablename);//调用获得行数的方法
//分页的页数:如果可以被行数整除,商就是分页数,不能整除(商+1)就是分页数
int pages = rows % pageSize == 0 ? rows / pageSize : rows/pageSize + 1;
ResultSet rs=null;
String str=null;
try {
pstmt=connection.prepareStatement(sql,Dbutils.TYPE,Dbutils.UPDATE);
for (int i = 1; i <=pages ; i++) {
int start=1+(i-1)*pageSize;
int end=start+pageSize-1;
pstmt.setInt(1, start);
pstmt.setInt(2, end);
rs=pstmt.executeQuery();
str=Dbutils.getString(rs);
}
} catch (SQLException e) {
e.printStackTrace();
}
return str;
}

}