java sqlserver2008

工具：eclipse，jdk，sqljdbc4.jar

0.      将sqljdbc4.jar放在Java\jre7\lib路径下。

1.      新建工程，右键properties====java buildpath=====libraries=======add external jars=========找到sqljdbc.jar，选中。

工程目录下会多出一个referenced libraries，如图

2．建立一个SQL_Util类，作为辅助，该类中有一个静态方法vertify()，返回一个Connection对象。

代码

public class SQL_Util {public static final String JDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";//SQL数据库引擎public static final String URL = "jdbc:sqlserver://localhost:1433;DatabaseName= databaseName";//set a urlprivate static final String USER = "sa";private static final String PASSWORD = "password";//set username andpassword//以下是一个静态方法，用于连接数据库，该方法返回一个Connection（数据库连接对象）public static final Connection vertify() {Connection con= null;try {Class.forName(JDriver);//加载数据库引擎} catch (ClassNotFoundException e){System.out.println("加载数据库引擎失败");System.exit(0);}if(con==null){try{con =DriverManager.getConnection(URL,USER,PASSWORD);//获取数据库连接对象} catch (SQLException e){e.printStackTrace();}}return con;} 
}

//获取列数据public static String[] getColData(String colname, String tableName){String colData[] = null;Connection con = null;ResultSet rs = null;PreparedStatement pst = null;String sql = "select * from "+ tableName;//获取PreparedStatement变量try{con = SQL_Util.vertify();pst = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);//获取结果集rs = pst.executeQuery();//结果集滚动到最后一行rs.last();int x = rs.getRow();//获取行数colData = new String[x];int y = rs.getMetaData().getColumnCount();//获取列数//结果集滚回第一行rs.first();String data[][] = new String[x][y];//设置一个二维数组存放表格数据String head[] = new String[y];//设置一个一位数组作为表格列名
//			获取结果的列名，存储到一位数组中for(int i = 0; i<y; i++) {head[i] = rs.getMetaData().getColumnName(i+1).trim();}
//			将结果集中的数据取出，存放进二维数组for(int i = 0; i<x; i++) {for(int j = 0; j<y; j++) {data[i][j] = rs.getString(j+1);}rs.next();}int colnum = 0;for(int i = 0;i<head.length; i++){if(colname.equals(head[i])) { colnum = i; }}for(int i = 0; i<x; i++){colData[i] = data[i][colnum];}} catch (SQLException e){e.printStackTrace();}finally{try {con.close();} catch (SQLException e) {e.printStackTrace();}	}return colData;}//获取行数据public static String[] getRowData(int row, String tableName) throws SQLException{String[] rowData = null;Connection con = null;ResultSet rs = null;PreparedStatement pst = null;String sql = "select * from "+ tableName;try{//获取PreparedStatement变量con = SQL_Util.vertify();pst = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);//获取结果集rs = pst.executeQuery();//结果集滚动到最后一行rs.last();int x = rs.getRow();//获取行数int y = rs.getMetaData().getColumnCount();//获取列数rowData = new String[y];//结果集滚回第一行rs.first();String data[][] = new String[x][y];//设置一个二维数组存放表格数据String head[] = new String[y];//设置一个一位数组作为表格列名
//			获取结果的列名，存储到一位数组中for(int i = 0; i<y; i++) {head[i] = rs.getMetaData().getColumnName(i+1);}
//			将结果集中的数据取出，存放进二维数组for(int i = 0; i<x; i++) {for(int j = 0; j<y; j++) {data[i][j] = rs.getString(j+1);}rs.next();}for(int i = 0; i<data[row].length; i++) {rowData[i] = data[row][i];}}catch(SQLException e){e.printStackTrace();}finally{try {con.close();} catch (SQLException e) {e.printStackTrace();}	}return rowData;}//获取表格的属性名public static String[] getTableHead(String tableName) throws SQLException {String head[] = null;Connection con = null;ResultSet rs = null;PreparedStatement pst = null;String sql = "select * from "+ tableName;try{//获取PreparedStatement变量con = SQL_Util.vertify();pst = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);//获取结果集rs = pst.executeQuery();//结果集滚动到最后一行rs.last();int x = rs.getRow();//获取行数String s[] = new String[x];int y = rs.getMetaData().getColumnCount();//获取列数System.out.println(x+"行"+y+"列");//结果集滚回第一行rs.first();String data[][] = new String[x][y];//设置一个二维数组存放表格数据head = new String[y];//设置一个一位数组作为表格列名
//			获取结果的列名，存储到一位数组中for(int i = 0; i<y; i++) {head[i] = rs.getMetaData().getColumnName(i+1);}
//			将结果集中的数据取出，存放进二维数组for(int i = 0; i<x; i++) {for(int j = 0; j<y; j++) {data[i][j] = rs.getString(j+1);}rs.next();}}catch(SQLException e){e.printStackTrace();}finally{try {con.close();} catch (SQLException e) {e.printStackTrace();}	}return head;}//获取表格public static String[][] getTableData(String tableName) throws SQLException {String data[][] = null;Connection con = null;//连接数据库并获得Connection变量ResultSet rs = null;PreparedStatement pst = null;String sql = "select * from "+ tableName;try{//获取PreparedStatement变量con = SQL_Util.vertify();pst = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);//获取结果集rs = pst.executeQuery();//结果集滚动到最后一行rs.last();int x = rs.getRow();//获取行数String s[] = new String[x];int y = rs.getMetaData().getColumnCount();//获取列数System.out.println(x+"行"+y+"列");//结果集滚回第一行rs.first();data = new String[x][y];//设置一个二维数组存放表格数据String head[] = new String[y];//设置一个一位数组作为表格列名
//			获取结果的列名，存储到一位数组中for(int i = 0; i<y; i++) {head[i] = rs.getMetaData().getColumnName(i+1);}
//			将结果集中的数据取出，存放进二维数组for(int i = 0; i<x; i++) {for(int j = 0; j<y; j++) {data[i][j] = rs.getString(j+1);}rs.next();}}catch(SQLException e){e.printStackTrace();}finally{try {con.close();} catch (SQLException e) {e.printStackTrace();}	}return data;}
}