Java uses JDBC technology to manipulate SQL Server database execution stored procedures

Java uses JDBC technology to manipulate SQL Server database execution stored procedures:

1. New SQL Server database: java_conn_test

2. New table: Tb_user

3. Create a new three stored procedure, respectively:

1> new user stored procedure with parameters:

CREATE PROCEDURE [dbo].[P_insert_user]@name nvarchar( -),@UserPwd nvarchar( -) asBEGIN    INSERT  intoTb_userVALUES(NEWID(),@name,@UserPwd)END

2> query user information stored procedure without parameters:

CREATE PROCEDURE [dbo]. [p_select_user]  as BEGIN    SELECT *  from Tb_user END

3> stored procedure with parameters with output parameters:

CREATE PROCEDURE [dbo].[P_select_usercount]@name nvarchar( -),@result intOutput asBEGIN    SELECT @result= COUNT(0) fromTb_userWHERE @name=UserNameEND

4> prepare for new Java project, import Sqljdbc.jar

 Packagecom. PROJECT_DATABASE01;Importjava.sql.Connection;ImportJava.sql.DriverManager; Public classSelectQuery {PrivateConnection Conn; /** Create a method to return connection*/     PublicConnection getconnection () {Try{class.forname ("Com.microsoft.sqlserver.jdbc.SQLServerDriver"); System.out.println ("Database driver loading succeeded"); Conn=drivermanager.getconnection ("Jdbc:sqlserver://localhost:1433;databasename=java_conn_test", "sa", "123456"); if(conn==NULL) {System.out.println ("Database connection Failed"); System.out.println ("-----------------------"); }Else{System.out.println ("Database connection succeeded"); System.out.println ("-----------------------"); }        } Catch(Exception e) {//Todo:handle ExceptionE.printstacktrace (); }        returnConn; }}

5> Executing stored procedures:

 Packagecom. PROJECT_DATABASE01;Importjava.sql.CallableStatement;Importjava.sql.Connection;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;Importjava.sql.Statement;Importjava.sql.Types; Public classStartmain {Private StaticConnection Conn;  Public Static voidMain (string[] args) {//TODO auto-generated Method StubConn=Newselectquery (). getconnection ();                Getproduseinsert ();                GetProduseSelect02 ();            Getproduseselect (); }    /** Execute Select no parameter stored procedure, query data*/         Public Static voidGetproduseselect () {if(conn==NULL) {System.out.println ("Linked database Failed"); }Else {            Try{CallableStatement cs=conn.preparecall ("{Call P_select_user ()}"); ResultSet RS=Cs.executequery ();  while(Rs.next ()) {String name=rs.getstring ("UserName"); String pwd=rs.getstring ("Userpwd"); String UserId=rs.getstring ("UserId"); SYSTEM.OUT.PRINTLN (Name+ "\ t" +pwd+ "\ T" +UserId); } System.out.println ("Query succeeded"); System.out.println ("-----------------------"); } Catch(Exception e) {//Todo:handle ExceptionSYSTEM.OUT.PRINTLN ("Query Failed"); System.out.println ("-----------------------"); }        }    }        /** Execute insert parameter stored procedure, query data*/     Public Static voidGetproduseinsert () {if(conn==NULL) {System.out.println ("Database connection Failed"); }Else {            Try{callablestatement IC=conn.preparecall ("{Call P_insert_user (?,?)}"); Ic.setstring (1, "Heyangyi"); Ic.setstring (2, "123");                Ic.execute (); System.out.println ("Add Success"); }             Catch(Exception ex) {//Todo:handle ExceptionSystem.out.println ("Add Failed"); }        }    }    /** Execution of stored procedures with output parameters*/     Public Static voidGetProduseSelect02 () {if(conn==NULL) {System.out.println ("Database link Failed"); }Else {            Try{CallableStatement SP=conn.preparecall ("{Call P_select_usercount (?,?)}"); Sp.setstring (1, "Heyangyi"); Sp.registeroutparameter (2, Types.integer);                Sp.execute (); System.out.println ("Query succeeded:" +sp.getint (2)); } Catch(Exception e) {//Todo:handle ExceptionSYSTEM.OUT.PRINTLN ("Query Failed"); }        }    }}

Java uses JDBC technology to manipulate SQL Server database execution stored procedures