I. Preparations

Import the corresponding jdbc.jar package: Right-click in the current project, select Build Path, and select Configure Build Path. Under the Libraries tab, click Add External JARS on the right and select the appropriate jar package.

• JDBC connection sql sever2012: https://pan.baidu.com/disk/home?errno=0&errmsg=Auth Login Sucess&&bduss=&ssnerror=0&traceid=#/all?vmode=list&path=%2Fmy java doc Now you can write code.

2. Code Writing

1.JDBC writing mainly includes the following steps:

• Packing (completed)
• 1. Driver
• 2. Connection
• 3. Create statement instances
• 4. Execute statements
• 5. Output Result Set
• 6. Close

2. Connection

• Define Variables
  There are two variables in the connection process, one is the drivername and the other is the url.

 //sql database driver
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//Database Path
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=student1";

Where the DatabaseName parameter of the url is specified as the name of the database to which the connection is required

• Connect to the database

1. Implemented by the static method forName (String className) of the java.lang.Class class, load the driver of the database you want to connect to into the JVM (Java Virtual Machine)

     Class.forName(driverName);

2. Request and obtain a Connection object from java.sql.DriverManager that represents a connection to a database.

      con = DriverManager.getConnection(url,"test","1234");
      //DriverManager's getConnectin (String url, String username, String password) method is obtained by passing in the path, user name, and password of the specified database to which you want to connect.    

3. Create instance statements

For example:

      String sql = "select *from test";//Query test table
      statement = con.prepareStatement(sql);

• To execute a SQL statement, you must obtain a java.sql.Statement instance, which is divided into three types:
  1. Execute static SQL statements.Typically implemented through a Statement instance.
  2. Execute dynamic SQL statements.Typically implemented through a PreparedStatement instance.
  3. Execute database stored procedures.Typically implemented through a CallableStatement instance.
  Specific implementation:

  Statement stmt = con.createStatement() ;
  PreparedStatement pstmt = con.prepareStatement(sql) ;
  CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ;

4. Execute statements

       res = statement.executeQuery();

• The Statement interface provides three ways to execute SQL statements: executeQuery, executeUpdate, and execute
  1. ResultSet executeQuery(String sqlString): Executes an SQL statement that queries the database and returns a ResultSet object.
  2. int executeUpdate(String sqlString): Used to execute INSERT, UPDATE or DELETE statements and SQL DDL statements, such as CREATE TABLE, DROP TABLE, etc.
  3. execute(sqlString): A statement that returns multiple result sets, multiple update counts, or a combination of the two.
  Code for implementation:

  ResultSet rs = stmt.executeQuery("SELECT * FROM …") ;
  int rows = stmt.executeUpdate("INSERT INTO …") ;
  boolean flag = stmt.execute(String sql) ;

5. Output Result Set

     while(res.next()){
                String title = res.getString("test_name");//Get the element of the test_name column  
                String id=res.getString(1);//Get the elements of the first column;
                System.out.println(id+"Full name:"+title);
            }

Traversal result set:
Two cases:
1. Performing the update returns the number of records affected by this operation.
2. The result of executing the query is a ResultSet object.

• ResultSet Contains conformance SQL All lines of a condition in a statement, and it passes through a set of get Methods provide access to data in these rows.    
          
• Using a result set ( ResultSet)Object access method to get data:    
             while(rs.next()){    
             String name = rs.getString("name") ;    
             String pass = rs.getString(1) ; // This method is efficient    
             }    

Columns are numbered from left to right and begin with column 1.

6. Close

        if(res != null) res.close();
        if(statement != null) statement.close();
        if(con != null) con.close();

• After the operation is completed, close all JDBC objects used to release JDBC resources in the opposite order of closure and declaration:
  1. Close requestSet first
  2. Close the preparedStatement again
  3. Finally close the connection object

3. Connection Testing

[Insert picture description here] ( https://img-blog.csdnimg.cn/20190428125119435.png

This completes the JDBC connection to sql sever2012.

Full code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Jdbc_demo1 {
	 
    
    public static void main(String[] args) {
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //sql database driver
        String url = "jdbc:sqlserver://localhost:1433;DatabaseName=student1";
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            Class.forName(driverName);
            con = DriverManager.getConnection(url,"test","12345");   
            
            String sql = "select *from test";//Query test table
            statement = con.prepareStatement(sql);

            
            res = statement.executeQuery();

            while(res.next()){
                String title = res.getString("test_name");//Get the element of the test_name column  
                String id=res.getString(1);//Get the element of the first column = res.getString("test_id");
                System.out.println(id+"Full name:"+title);
            }
            
            
        	} catch (Exception e) {
        		// TODO: handle exception
        		e.printStackTrace();
        	}finally{
        		try {
        			if(res != null) res.close();
        			if(statement != null) statement.close();
        			if(con != null) con.close();
        		} catch (Exception e2) {
        			// TODO: handle exception
        			e2.printStackTrace();
        		}

        	}
    	}
}