sqlserverjdbc.java

连接数据库 以及对数据库中的数据进行更新

package com.handson.jdbc;
//import com.microsoft.jdbc.sqlserver.SQLServerDriver;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlserverJDBC {
	private final String url = "jdbc:microsoft:sqlserver://";     
	private final String serverName= "localhost";    
	private final String portNumber = "1433";     
	private final String databaseName= "test";     
	private final String userName = "sa";     
	private final String password = "";

	private String getConnectionUrl(){          
		return url+serverName+":"+portNumber+";databaseName="+databaseName+";";    
	}
	private Connection getConnection(){  
		Connection con = null;
		try{               
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");   
			con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password); 
			if(con!=null){ 
				System.out.println("Connection Successful!");
			}
		}catch(Exception e){               
			e.printStackTrace();              
			System.out.println("Error Trace in getConnection() : " + e.getMessage());   
		}         
		return con;      
	} 
	
/*
if exists (select * from sysobjects where name='student' and type='u')
  drop table student
go
 */	
	public void createTable(){
		Connection con = getConnection();
		String sql = "create table student(id int, name varchar(10))";
		try {
			Statement statement = con.createStatement();
			statement.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		closeConnection(con);
	}

	public void insertData(){
		Connection con = getConnection();
		String sql1 = "insert into student values(1, 'test1')";
		String sql2 = "insert into student values(2, 'test2')";
		try {
			Statement statement = con.createStatement();
			statement.execute(sql1);
			statement.execute(sql2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		closeConnection(con);
	}

	/* Display the driver properties, database details     */
	public void displayDbProperties(){          
		java.sql.DatabaseMetaData dm = null;         
		java.sql.ResultSet rs = null; 
		Connection con = null;
		try{               
			con= this.getConnection();     
			if(con!=null){               
				dm = con.getMetaData();       
				System.out.println("Driver Information");   
				System.out.println("\tDriver Name: "+ dm.getDriverName());  
				System.out.println("\tDriver Version: "+ dm.getDriverVersion ());      
				System.out.println("\nDatabase Information ");               
				System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());   
				System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());   
				System.out.println("Avalilable Catalogs ");                  
				rs = dm.getCatalogs();               
				while(rs.next()){                      
					System.out.println("\tcatalog: "+ rs.getString(1));   
				}                  
				rs.close();
				rs = null;
				closeConnection(con);             
			}else{
				System.out.println("Error: No active Connection");
			}
		}catch(Exception e){             
			e.printStackTrace();         
		}         
		dm=null;     
	}    
	private void closeConnection(Connection con){    
		try{              
			if(con!=null){       
				con.close();
			}
		}catch(Exception e){         
			e.printStackTrace();         
		}     
	}


	public void testSelect() throws Exception{
		ResultSet resultSet = null;
		Connection con = getConnection();
		String queryString = "select * from student where name=?";
		PreparedStatement ps = con.prepareStatement(queryString);
		ps.setString(1, "test1");
		resultSet = ps.executeQuery();
		String id="";
		String name="";
		while(resultSet.next()){
			id = resultSet.getString("id");
			name = resultSet.getString("name");
			System.out.println(id+" "+name);
		}
		con.close();
	}
	
	public void testAbsSelect(int pageno) throws Exception{
		ResultSet resultSet = null;
		Connection con = getConnection();
		String queryString = "select * from student";
		PreparedStatement ps = con.prepareStatement(queryString, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
	//	ps.setString(1, "test1");
		resultSet = ps.executeQuery();
		String id="";
		String name="";
		resultSet.absolute(pageno);
		{
			id = resultSet.getString("id");
			name = resultSet.getString("name");
			System.out.println(id+" "+name);
		}
		con.close();
	}

/*
if exists (select * from sysobjects where name='test' and type='p')
  drop procedure test
go

create procedure test
@x int
as
select * from student where id = @x 
*/	
	public void testCallSQL() throws Exception{
		String callSQL = "{call test(?)}" ;
		Connection con = getConnection();
		CallableStatement cstmt = con.prepareCall(callSQL);

		cstmt.setString(1, "1") ;

		if(false == cstmt.execute()){
			System.out.println("execute error");
			return;			
		}

		ResultSet rs = cstmt.getResultSet();
		ResultSetMetaData rsmd = rs.getMetaData() ;
		for(int i = 1 ; i <= rsmd.getColumnCount() ; i++){
			System.out.print(rsmd.getColumnName(i) + "\t");
		}

		System.out.println("\n----------------------------------------") ;

		while(rs.next()) {
			System.out.print(rs.getString("id") + "\t") ;
			System.out.print(rs.getString("name") + "\t") ;	
			System.out.println();
		}
		rs.close() ;
		con.close();
	}
/*
if exists (select * from sysobjects where name='getCount' and type='p')
  drop procedure getCount
go

create procedure getCount
	@x varchar(10),
	@c int output
as
	select @c=count(*) from student where name = @x 
	

declare @x int;
exec getCount 'test1', @x output
print @x
*/
	public void testCallSQLWithOutput() throws Exception{
		String callSQL = "{call getCount(?, ?)}" ;
		Connection con = getConnection();
		CallableStatement cstmt = con.prepareCall(callSQL);

		cstmt.setString(1, "test1") ;
		cstmt.registerOutParameter(2, java.sql.Types.INTEGER) ;
		cstmt.execute();

		int count = cstmt.getInt(2) ;		
		System.out.println(count + " messages found.") ;		

		cstmt.close() ;
		con.close();
	}

	public static void main(String[] args) throws Exception{  
		TestJDBC myDbTest = new TestJDBC();        
		myDbTest.testSelect();    
	}
}