Detailed steps and code for JDBC to connect to a SQL Server database

Detailed steps and code for JDBC to connect to a SQL Server database

The steps for JDBC to connect to a SQL Server database are as follows:

[Java]View Plaincopyprint?

2. 1. Load the JDBC driver:
4. Before connecting to the database, first load the driver of the database you want to connect to the JVM (Java Virtual machine),
6. This is achieved through the static method forname (String className) of the Java.lang.Class class.
8. After a successful load, an instance of the driver class is registered in the DriverManager class.
10. 2. Provide the URL of the JDBC connection
12. • The connection URL defines the protocol, sub-protocol, and data source identity when the database is connected.
14. • Written form: protocol: Sub-Protocol: Data source identification
16. Protocol: Always start with JDBC in JDBC
18. Sub-Protocol: A bridge-connected driver or database management system name.
20. Data source identification: The tag locates the address of the database source and the connection port.
22. 3. Create a connection to the database
24. • To connect to a database, you need to request and obtain a connection object from Java.sql.DriverManager.
26. The object represents a connection to a database.
28. • Use DriverManager's getconnectin (string URL, string username,
30. String password) method to pass in the path to the specified database to be connected, the user name of the database, and
32. Password to get it.
34. 4. Create a statement
36. • To execute an SQL statement, you must obtain an java.sql.Statement instance that is divided into the following 3 statement instances
38. Type of:
40. 1. Execute static SQL statements.     Typically implemented through statement instances.
42. 2. Execute dynamic SQL statements.     Typically implemented through PreparedStatement instances.
44. 3. Execute the database stored procedure.     Typically implemented through CallableStatement instances.
46. The specific implementation method:
48. Statement stmt = Con.createstatement ();
50. PreparedStatement pstmt = con.preparestatement (sql);
52. CallableStatement cstmt = Con.preparecall ("{Call Demosp (?,?)}");
54. 5. Execute SQL statements
56. The statement interface provides three ways to execute SQL statements: ExecuteQuery, executeupdate
58. and execute
60. 1, ResultSet executeQuery (): Execute SQL statement to query database
62. A result set (ResultSet) object is returned.
64. 2,int executeupdate (): Used to perform INSERT, update, or
66. Delete statements and SQL DDL statements, such as CREATE table and drop table
68. 3. Execute (): Used to perform a return of multiple result sets, multiple update counts, or a combination of the two
70. Statement.
72. Specific implementation code:
74. ResultSet rs = Pstmt.executequery ();
76. int rows = Pstmt.executeupdate ();
78. Boolean flag = Pstmt.execute ();
80. 6. Processing results
82. Two cases:
84. 1. The number of records affected by this operation is returned by performing the update.
86. 2. The result of executing the query returned is a ResultSet object.
88. resultset contains all rows that conform to the conditions in the SQL statement, and it provides a set of get methods for these
90. Access to the data in the row.
92. • Get data using the access method of the result set (ResultSet) object:
94. While (Rs.next ()) {
96. String name = rs.getstring ("name");
98. String pass = rs.getstring (1);
100. }
102. 7. Close the JDBC Object
104. All JDBC objects used are closed after the operation is complete to release the JDBC resource, turn off order harmony
106. The opposite of the Ming Order:
108. 1. Close record set
110. 2. Closing the statement
112. 3. Close the Connection object

JDBC Connect SQL Server database code:

[Java]View Plaincopyprint?

2. Package COM.ACCP.JDBC;
6. Import java.sql.Connection;
8. Import Java.sql.DriverManager;
10. Import java.sql.PreparedStatement;
12. Import Java.sql.ResultSet;
14. Import java.sql.SQLException;
18. Import Org.apache.log4j.Logger;
22. Public class Basedao {
24. //Use log4j to log logs
26. private static Logger Logger = Logger.getlogger (Basedao.   Class);
28. //Connection driver
30. private static final String DRIVER = "Com.microsoft.sqlserver.jdbc.SQLServerDriver";
32. //connection path
34. private static final String URL = "Jdbc:sqlserver://localhost:1433;databasename=myschool";
36. //user name
38. private static final String USERNAME = "sa";
40. //Password
42. private static final String PASSWORD = "sa";
46. //Static code block
48. Static {
50. try {
52. //Load driver
54. Class.forName (DRIVER);
56. } catch (ClassNotFoundException e) {
58. E.printstacktrace ();
60. }
62. }
66. /* 
68. * Get database connection
70. */
72. Public Connection getconnection () {
74. Connection conn = null;
76. Logger.debug ("Start Connection Database");
78. try{
80. Conn=drivermanager.getconnection (URL, USERNAME, PASSWORD);
82. }catch (SQLException e) {
84. E.printstacktrace ();
86. Logger.error ("Database connection failed!  ");
88. }
90. Logger.debug ("database connection succeeded");
92. return conn;
94. }
98. /* 
100. * Close database connection, note the order of closing
102. */
104. public void Close (ResultSet rs, PreparedStatement PS, Connection conn) {
106. if (rs!=null) {
108. try{
110. Rs.close ();
112. rs=null;
114. }catch (SQLException e) {
116. E.printstacktrace ();
118. Logger.error ("shutdown resultset failed");
120. }
122. }
124. if (ps!=null) {
126. try{
128. Ps.close ();
130. ps=null;
132. }catch (SQLException e) {
134. E.printstacktrace ();
136. Logger.error ("shutdown PreparedStatement failed");
138. }
140. }
142. if (conn!=null) {
144. try{
146. Conn.close ();
148. conn=null;
150. }catch (SQLException e) {
152. E.printstacktrace ();
154. Logger.error ("Shutdown Connection failed");
156. }
158. }
160. }
162. }

Detailed steps and code for JDBC to connect to a SQL Server database