jdbcapplication.java

来自「有关java的源程序,为讲授java程序设计课程使用」· Java 代码 · 共 723 行 · 第 1/2 页

                System.out.println("Driver Version:"+dma.getDriverVersion());
				System.out.println("Driver Major Version:"+dma.getDriverMajorVersion());
				System.out.println("Driver Minor Version:"+dma.getDriverMinorVersion());
				System.out.println("User Name:"+dma.getUserName());
System.out.println("Database Product Name:"+dma.getDatabaseProductName());
System.out.println("Database Product Name:"+dma.getDatabaseProductVersion());

                Statement stmt=con.createStatement();
//Update DB(Execute SQL Create Table Statement)
                System.out.print("Creat Table DateBase----StateMent.executeUpdate()\n");
		        int RecordRowNum=stmt.executeUpdate("Create Table Info(ID integer, Name VARCHAR(20))");
				stmt.executeUpdate("insert into Info(ID,Name) values(3,'cheng')");
				System.out.println("The Record Row Num Is "+RecordRowNum);
                ResultSet rs=stmt.executeQuery("select * from  Info");
                while(rs.next())
 	            {
		           int ID=rs.getInt("ID");
                   System.out.print("ID: "+ID+" ");		
                   String Name=rs.getString("Name");
                   System.out.println("Name: "+Name);
			   
                }
//add One Field Column in Table
                System.out.print("Add One Column in Table ----StateMent.executeUpdate()\n");
		        stmt.executeUpdate("Alter Table Info add COLUMN Address VarChar(50)");

RecordRowNum=stmt.executeUpdate("insert into Info(ID,Name,Address) values(4,'zhang','Beijing China')");
			  	System.out.println("The Record Row Num Is "+RecordRowNum);

				stmt.executeUpdate("Update Info set Address='ShangHai China' where ID=4");
				rs=stmt.executeQuery("select * from  Info");
                while(rs.next())
 	            {
		           int ID=rs.getInt("ID");
                   System.out.print("ID: "+ID+" ");		
                   String Name=rs.getString("Name");
                   System.out.print("Name: "+Name+" ");
				   String Address=rs.getString("Address");
                   System.out.println("Address: "+Address);
                }			
//delete One Column in Table
                System.out.print("Delete One Column in Table ----StateMent.executeUpdate()\n");
		        stmt.executeUpdate("Alter Table Info Drop Column Address");
				rs=stmt.executeQuery("select * from  Info");
                while(rs.next())
 	             {
		           int ID=rs.getInt("ID");
                   System.out.print("ID: "+ID+" ");		
                   String Name=rs.getString("Name");
                   System.out.println("Name: "+Name);
				 }			

				stmt.close();   //must close
                con.close();
                }
            catch(SQLException e)
            {
                   System.out.println("SQLState:"+e.getSQLState());
                   System.out.println("Message:"+e.getMessage());
                   System.out.println("Vendor:"+e.getErrorCode());
            }
            catch(ClassNotFoundException e)
            {
                   System.out.println(e);
            }

		}

void StatementExecSQLDropTable()
		{
            try
            {
//URL Format: "jdbc:odbc:Data Source Name"
                String url="jdbc:odbc:studlist";
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/*
In SQL Server:
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
In Microsoft Access:
                Connection con=DriverManager.getConnection(url,"","");
*/
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
//Get Data Base Basica Information
				System.out.println("\nData Base Basica Information");
                DatabaseMetaData dma=con.getMetaData();
				System.out.println("URL String:"+dma.getURL());
                System.out.println("Driver Name:"+dma.getDriverName());
                System.out.println("Driver Version:"+dma.getDriverVersion());
				System.out.println("Driver Major Version:"+dma.getDriverMajorVersion());
				System.out.println("Driver Minor Version:"+dma.getDriverMinorVersion());
				System.out.println("User Name:"+dma.getUserName());
System.out.println("Database Product Name:"+dma.getDatabaseProductName());
System.out.println("Database Product Name:"+dma.getDatabaseProductVersion());

                Statement stmt=con.createStatement();
//Update DB(Execute SQL Drop Table Statement)
                System.out.print("Drop Table DateBase----StateMent.executeUpdate()\n");
		        int RecordRowNum=stmt.executeUpdate("Drop Table Info");
				stmt.close();   //must close
                con.close();
                }
            catch(SQLException e)
            {
                   System.out.println("SQLState:"+e.getSQLState());
                   System.out.println("Message:"+e.getMessage());
                   System.out.println("Vendor:"+e.getErrorCode());
            }
            catch(ClassNotFoundException e)
            {
                   System.out.println(e);
            }

		}

void PreparedStatementExecSQLSelect()
		{
            try
            {
//URL Format: "jdbc:odbc:Data Source Name"
                String url="jdbc:odbc:studlist";
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/*
In SQL Server:
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
In Microsoft Access:
                Connection con=DriverManager.getConnection(url,"","");
*/
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
//Get Data Base Basica Information
				System.out.println("\nData Base Basica Information");
                DatabaseMetaData dma=con.getMetaData();
				System.out.println("URL String:"+dma.getURL());
                System.out.println("Driver Name:"+dma.getDriverName());
                System.out.println("Driver Version:"+dma.getDriverVersion());
				System.out.println("Driver Major Version:"+dma.getDriverMajorVersion());
				System.out.println("Driver Minor Version:"+dma.getDriverMinorVersion());
				System.out.println("User Name:"+dma.getUserName());
System.out.println("Database Product Name:"+dma.getDatabaseProductName());
System.out.println("Database Product Name:"+dma.getDatabaseProductVersion());

                PreparedStatement prepstmt=con.prepareStatement("select * from  student");
//Execute SQLSelect 
				System.out.print("Execute PreparedStatement Select Statement\n");
                ResultSet rs=prepstmt.executeQuery();
                while(rs.next())
 	            {
		           int ID=rs.getInt("id");
                   System.out.print("ID: "+ID+" ");		
                   String Name=rs.getString("name");
                   System.out.print("Name: "+Name+" ");
				   int AGE=rs.getInt("age");
                   System.out.println("Age: "+AGE);		

                }		
				prepstmt.close();   //must close
                con.close();
                }
            catch(SQLException e)
            {
                   System.out.println("SQLState:"+e.getSQLState());
                   System.out.println("Message:"+e.getMessage());
                   System.out.println("Vendor:"+e.getErrorCode());
            }
            catch(ClassNotFoundException e)
            {
                   System.out.println(e);
            }

		}

void PreparedStatementExecSQLUpdateDBRecord()
		{
            try
            {
//URL Format: "jdbc:odbc:Data Source Name"
                String url="jdbc:odbc:studlist";
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/*
In SQL Server:
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
In Microsoft Access:
                Connection con=DriverManager.getConnection(url,"","");
*/
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
//Get Data Base Basica Information
				System.out.println("\nData Base Basica Information");
                DatabaseMetaData dma=con.getMetaData();
				System.out.println("URL String:"+dma.getURL());
                System.out.println("Driver Name:"+dma.getDriverName());
                System.out.println("Driver Version:"+dma.getDriverVersion());
				System.out.println("Driver Major Version:"+dma.getDriverMajorVersion());
				System.out.println("Driver Minor Version:"+dma.getDriverMinorVersion());
				System.out.println("User Name:"+dma.getUserName());
System.out.println("Database Product Name:"+dma.getDatabaseProductName());
System.out.println("Database Product Name:"+dma.getDatabaseProductVersion());

                PreparedStatement prepstmt=con.prepareStatement("select * from  student");
//Update DB(Execute PreparedStatement SQL)
                System.out.print("Update DateBase----PreparedStateMent.executeUpdate()\n");
				PreparedStatement Prepstmt=con.prepareStatement("Update student set name='yang' where id=2");
		        int RecordRowNum=Prepstmt.executeUpdate();
				System.out.println("The Record Row Num Is "+RecordRowNum);

				prepstmt=con.prepareStatement("select * from  student");
				ResultSet rs=prepstmt.executeQuery();
                while(rs.next())
 	            {
		           int ID=rs.getInt("id");
                   System.out.print("ID: "+ID+" ");		
                   String Name=rs.getString("name");
                   System.out.print("Name: "+Name+" ");
				   int AGE=rs.getInt("age");
                   System.out.println("Age: "+AGE);		
                }			

				prepstmt.close();   //must close
                con.close();
                }
            catch(SQLException e)
            {
                   System.out.println("SQLState:"+e.getSQLState());
                   System.out.println("Message:"+e.getMessage());
                   System.out.println("Vendor:"+e.getErrorCode());
            }
            catch(ClassNotFoundException e)
            {
                   System.out.println(e);
            }

		}
//利用PreparedStatement 可以实现带参数操作
void PreparedStatementExecSQLUpdateDBRecordWithParameter()
		{
            try
            {
//URL Format: "jdbc:odbc:Data Source Name"
                String url="jdbc:odbc:studlist";
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/*
In SQL Server:
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
In Microsoft Access:
                Connection con=DriverManager.getConnection(url,"","");
*/
                Connection con=DriverManager.getConnection(url,"yanguser","1234");
//Get Data Base Basica Information
				System.out.println("\nData Base Basica Information");
                DatabaseMetaData dma=con.getMetaData();
				System.out.println("URL String:"+dma.getURL());
                System.out.println("Driver Name:"+dma.getDriverName());
                System.out.println("Driver Version:"+dma.getDriverVersion());
				System.out.println("Driver Major Version:"+dma.getDriverMajorVersion());
				System.out.println("Driver Minor Version:"+dma.getDriverMinorVersion());
				System.out.println("User Name:"+dma.getUserName());
System.out.println("Database Product Name:"+dma.getDatabaseProductName());
System.out.println("Database Product Name:"+dma.getDatabaseProductVersion());

                PreparedStatement prepstmt=con.prepareStatement("select * from  student");
//Update DB with Parameter)
                System.out.print("Update DateBase----PreparedStateMent.executeUpdate() with Parameter\n");
				prepstmt=con.prepareStatement("Update student set name=? where id=?");
				prepstmt.setString(1,"LiMing");  //setup First Parameter Value='LiMing' in SQL
				for( int i=0;i<=2;i++)
				{
					prepstmt.setInt(2,i);  //setup Second Parameter Value=i in SQL
		 		    prepstmt.executeUpdate();	
				}	
				prepstmt=con.prepareStatement("select * from  student");
				ResultSet rs=prepstmt.executeQuery();
                while(rs.next())
 	             {
		           int ID=rs.getInt("id");
                   System.out.print("ID: "+ID+" ");		
                   String Name=rs.getString("name");
                   System.out.print("Name: "+Name+" ");
				   int AGE=rs.getInt("age");
                   System.out.println("Age: "+AGE);		
                 }			
				prepstmt.close();   //must close
                con.close();
                }
            catch(SQLException e)
            {
                   System.out.println("SQLState:"+e.getSQLState());
                   System.out.println("Message:"+e.getMessage());
                   System.out.println("Vendor:"+e.getErrorCode());
            }
            catch(ClassNotFoundException e)
            {
                   System.out.println(e);
            }

		}

void CallableStatementStoreProc()
		{
//URL Format: "jdbc:odbc:Data Source Name",  DSN不区分大小写
                String url="jdbc:odbc:studlist";
				String DBDriver="sun.jdbc.odbc.JdbcOdbcDriver";
				Connection con=null;
				CallableStatement callStmt;
				ResultSet rs;
		try
		{
				Class.forName(DBDriver);
		}
		catch(ClassNotFoundException e)
	    {
		        System.out.println(e);
	    }
/*
In SQL Server:
                con=DriverManager.getConnection(url,"yanguser","1234");
In Microsoft Access:
                con=DriverManager.getConnection(url,"","");
*/
		try
		{
                con=DriverManager.getConnection(url,"yanguser","1234");
//Get Data Base Basica Information
				System.out.println("\nData Base Basica Information");
                DatabaseMetaData dma=con.getMetaData();
				System.out.println("URL String:"+dma.getURL());
                System.out.println("Driver Name:"+dma.getDriverName());
                System.out.println("Driver Version:"+dma.getDriverVersion());
				System.out.println("Driver Major Version:"+dma.getDriverMajorVersion());
				System.out.println("Driver Minor Version:"+dma.getDriverMinorVersion());
				System.out.println("User Name:"+dma.getUserName());
System.out.println("Database Product Name:"+dma.getDatabaseProductName());
System.out.println("Database Product Name:"+dma.getDatabaseProductVersion());

                callStmt=con.prepareCall("{call spSelect()}");
//Format: callStmt=con.prepareCall("{call 存储过程名()}");
//Execute SQLSelect 
				System.out.print("Execute SQL Stored Procedures Statement\n");
                rs=callStmt.executeQuery();   //execute 存储过程
                while(rs.next())
 	            {
// Field Name 不区分大小写
					int ID=rs.getInt("ID");
                    System.out.print("ID: "+ID+" ");		
                    String Name=rs.getString("NAME");
                    System.out.print("Name: "+Name+" ");
				    int AGE=rs.getInt("AGE");
                    System.out.println("Age: "+AGE);		

                }
				callStmt.close();   //must close
                con.close();
      }
      catch(SQLException e)
      {
                System.out.println("SQLState:"+e.getSQLState());
                System.out.println("Message:"+e.getMessage());
                System.out.println("Vendor:"+e.getErrorCode());
      }
            
		}

}