JDBC in JSP for SQL Server database operations

SQL Server database is a common database software. It is a Microsoft product, but also supports JDBC operations.

Operation:

<1> download the JDBC driver jar package from Microsoft's official website.

Put its driver jar package under the classpath of the application, in this is web development, so it can be placed under webroot/WEB-INF/lib.

Add the jar package to the application classpath:

Right-click sqljdbc. Jar

Perform the following operations as an example:

The configuration is complete.

Note: The driver files of different SQL Server versions are different.

The URL format of the SQL server connection is as follows:

 jdbc:sqlserver://<server_name>:<1433>;DatabaseName=<db>

Enter the database IP address at the beginning of <SERVER_NAME>. The default port number is 1433, ending with the database name.

The following is an instance connecting to a URL:

jdbc:sqlserver://localhost:<1433>;DatabaseName=student

It means to connect to the SQL Server database with the local port number 1433 and use the database "student ".

<2> Database Section

The following are database connection information:

Create a database, and then create a table under the database.

Example:

Package utils; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. SQL. statement; public class dB {Private Static connection con = NULL; Private Static statement = NULL; Private Static resultset set = NULL; private string SQL = ""; // load the private JDBC driver Private Static string drivernameofsqlserver = "com. microsoft. sqlserver. J DBC. sqlserverdriver "; // ip address (changed to your own IP address) Private Static string databaseip =" localhost "; // database username Private Static string databaseuser =" SJF "; // Database Password Private Static string databasepassword = "123456"; // database name Private Static string databasename = "pubs"; // urlprivate static string databaseurl = "JDBC: sqlserver: // "+ databaseip +": 1433; databasename = "+ databasename; // obtain the connection public connection getcon of a Database Nection () {try {// register the driver class. forname (drivernameofsqlserver); // obtain the connection con = drivermanager. getconnection (databaseurl, databaseuser, databasepassword);} catch (exception e) {system. out. println ("getconnection error"); E. printstacktrace ();} return con;} // creates a session public statement getstatement (connection con) {If (con! = NULL) {try {Statement = con. createstatement (); Return Statement;} catch (sqlexception e) {system. out. println ("getstatement error"); E. printstacktrace () ;}} return NULL ;}// query public resultset getresultsetquery (statement, string SQL) {If (statement! = NULL) {try {set = statement.exe cutequery (SQL); return set;} catch (sqlexception e) {system. out. println ("getresultsetquery error"); E. printstacktrace () ;}} return NULL;} // Add, modify, and delete the public void getresultsetupdate (statement, string SQL) {If (statement! = NULL) {try again statement.exe cuteupdate (SQL);} catch (sqlexception e) {system. out. println ("getresultsetupdate error"); E. printstacktrace () ;}}// close the connection to public static void colse (connection con) {If (con! = NULL) {try {con. close ();} catch (sqlexception e) {e. printstacktrace () ;}}// close the public static void close (statement) {If (statement! = NULL) {try {statement. close ();} catch (sqlexception e) {e. printstacktrace () ;}}// close the query set public static void close (resultset set) {If (set! = NULL) {try {set. Close () ;}catch (sqlexception e) {e. printstacktrace ();}}}}

Test:

DB db = new DB();Connection con = db.getConnection();Statement statement = db.getStatement(con);String sql = "select * from dbo.jobs";ResultSet rs = db.getResultSetQuery(statement, sql);try {if(rs.next()){System.out.println("fdfsdfsdff"+rs.getString("job_desc"));}} catch (SQLException e) {e.printStackTrace();}