Using JDBC for Oracle user authentication

Two days to write the sketch, the previous Java EE Environment Basic use framework. User registration and user authentication are now simulated using JDBC with Oracle stored procedures.

First, add the necessary jar package

Requires JDBC to connect Oracle's packages and Shiro-core dependencies, adding shiro-core primarily to facilitate the use of SHA-256 hashing algorithms.

Ii. writing a JDBC connection

Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;ImportOrg.slf4j.Logger;Importorg.slf4j.LoggerFactory; Public classOracle {Private Static FinalLogger Logger = Loggerfactory.getlogger (Oracle.class);  Public StaticConnection getconnection () {Connection conn=NULL; Try{class.forname ("Oracle.jdbc.driver.OracleDriver"); Logger.debug ("Trying to connect to the database"); String URL= "Jdbc:oracle:thin:@192.168.0.20:1541:test"; String username= "Apps"; String Password= "Apps"; Conn=drivermanager.getconnection (URL, username, password); } Catch(ClassNotFoundException cnfe) {logger.error (Cnfe.getmessage ()); } Catch(SQLException sqle) {logger.error (Sqle.getmessage ()); }        returnConn; }     Public Static voidCloseConnection (Connection conn) {Try {            if(Conn! =NULL) {conn.close (); Conn=NULL; }        } Catch(SQLException sqle) {logger.error (Sqle.getmessage ()); }    }}

Third, the establishment of the table

 --  Create table  create  table   lh_user_t (id  integer  not  null  , username  varchar2  (255 ), --  user name  password varchar2  (255 ), -- Span style= "COLOR: #008080" > password  roleid integer  --  foreign key link ) 

A full user add and certification authorization should contain at least three tables: User_table, role_table, and permission_table, which are not discussed in this article.

Iv. adding users

The Userdao class is responsible for database communication, and password hashing is implemented by the UserService class.

Importjava.sql.CallableStatement;Importjava.sql.Connection;Importjava.sql.PreparedStatement;Importjava.sql.SQLException;Importjava.sql.Types;ImportOrg.slf4j.Logger;Importorg.slf4j.LoggerFactory; Public classUserdao {Private Static FinalLogger Logger = Loggerfactory.getlogger (Userdao.class); //Add User     Public voidSaveuser (intUserID, string Username, string password,intRoleid) {String SQL= "INSERT into lh_user_t values (?,?,?,?)"; Connection Conn=oracle.getconnection (); PreparedStatement PS=NULL; Try{PS=conn.preparestatement (SQL); Ps.setint (1, UserID); Ps.setstring (2, username); Ps.setstring (3, password); Ps.setint (4, Roleid);        Ps.executeupdate (); } Catch(SQLException sqle) {logger.error (Sqle.getmessage ()); } finally{oracle.closeconnection (conn); if(PS! =NULL) {                Try{ps.close (); } Catch(SQLException e) {logger.error (E.getmessage ()); } PS=NULL; }        }    }    //Authenticating Users (added later)}

UserService class

ImportOrg.apache.shiro.crypto.hash.Sha256Hash; Public classUserService {PrivateUserdao Userdao; Private Static intUserID = 1;  PublicUserService () {Userdao=NewUserdao (); }     Public voidSaveuser (string Username, string password,intRoleid) {String Npassword=Newsha256hash (password). Tohex ();    Userdao.saveuser (Userservice.userid, username, Npassword, Roleid); }    //...}

V. User authentication (Oracle stored procedure)

Create or Replace procedureValidate_user (in_usernameinch varchar2, In_passwordinch varchar2, Out_result outvarchar2) asTmp_uid lh_user_t.id%type;begin  Select Count(*)     intoTmp_uid fromlh_user_t TwhereT.username=In_username andT.password=In_password; Out_result:= 'S'; exception whenNo_data_found ThenOut_result:= 'E';End;

Vi. user authentication (JDBC calls stored procedures)

Importjava.sql.CallableStatement;Importjava.sql.Connection;Importjava.sql.PreparedStatement;Importjava.sql.SQLException;Importjava.sql.Types;ImportOrg.slf4j.Logger;Importorg.slf4j.LoggerFactory; Public classUserdao {Private Static FinalLogger Logger = Loggerfactory.getlogger (Userdao.class); //Add User// {...} //Verifying users     Publicstring ValidateUser (string username, string password) {String SQL= "Call Validate_user (?,?,?)"; String result=NULL; Connection Conn=oracle.getconnection (); CallableStatement CS=NULL; Try{CS=conn.preparecall (SQL); Cs.setstring (1, username); Cs.setstring (2, password); Cs.registeroutparameter (3, Types.varchar);            Cs.execute (); Result= Cs.getstring (3); } Catch(SQLException sqle) {logger.error (Sqle.getmessage ()); } finally{oracle.closeconnection (conn); if(cs! =NULL) {                Try{cs.close (); } Catch(SQLException e) {logger.error (E.getmessage ()); } CS=NULL; }        }        returnresult; }}

The following also adds a hash algorithm to the UserService class

 Public classUserService {PrivateUserdao Userdao; Private Static intUserID = 1;  PublicUserService () {Userdao=NewUserdao (); }    // {...}     Publicstring ValidateUser (string username, string password) {string Npassword=Newsha256hash (password). Tohex (); returnUserdao.validateuser (username, npassword); }}

Vii. Summary

The application layer determines whether the user is authenticated successfully based on the string returned by the service class, ' E ' stands for failure, and ' S ' represents success. Using any validation framework requires that the user's password be read from the database and compared in the Java framework, and the individual prefers to hand over the work to the database to save resources.

Using JDBC for Oracle user authentication