The database can be operated easily by Java using JDBC API. In java8 and later, JDBC ODBC bridge connections have been dropped. For different databases, download the driver to the corresponding official website.

Here are three common database driver names, driver class and corresponding connection string name (x represents version)

Four steps of JDBC operating database

1. Pour in the driver and load the specific driver class
2. Establish connection with database
3. Implementation of SQL
4. Process result set (for query result set)

Different database operations through JDBC API are almost the same. Take SQL server as an example. Different database differences are driving and connecting strings.

import java.sql.*;

/**
 * JDBC Four steps of operating database
 * a.Pour in the driver and load the specific driver class
 * b.Establish connection with database
 * c.Implementation of SQL
 * d.Process result set
 * @author 1
 *
 */
public class JDBCDemo{
	
	private static String URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName = student database“;
	private static String user = "sa";
	private static String pwd = "123";
	
	public static void update(String sql){ //Additions and deletions
		Statement stmt = null;
		Connection  con = null;
		try {
			//a. Pour driver, load specific driver class
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			//b. Establish connection with database
			con = DriverManager.getConnection(URL, user, pwd);
			//c. performs SQL
			stmt = con.createStatement();
			int count = stmt.executeUpdate(sql);  //Returns the number of result sets affected
			//d. Process result set
			if(count>0) {
				System.out.println("Successful operation,Affected"+count+"That's ok");
			}
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("Unable to load driver");
		}catch(SQLException e) {
			e.printStackTrace();
			System.out.println("implement SQL fail");
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(stmt!=null) stmt.close();
				if(con!=null) con.close();
			}catch(SQLException e) {
				e.printStackTrace();
				System.out.println("Shutdown failure");
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
		
	}
	
	public static void query(){ //check
		Statement stmt = null;
		Connection  con = null;
		ResultSet rs = null;
		try {
			//a. Pour driver, load specific driver class
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			//b. Establish connection with database
			con = DriverManager.getConnection(URL, user, pwd);
			//c. performs SQL
			String sql = "select * from Course";
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			//d. Process result set
			while(rs.next()) {
				System.out.print(rs.getString("Cno")+" ");
				System.out.print(rs.getString("Cname")+" ");
				System.out.print(rs.getInt("Credit")+" ");
				System.out.println(rs.getInt("Semester"));
			}
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("Unable to load driver");
		}catch(SQLException e) {
			e.printStackTrace();
			System.out.println("implement SQL fail");
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null) rs.close();
				if(stmt!=null) stmt.close();
				if(con!=null) con.close();
			}catch(SQLException e) {
				e.printStackTrace();
				System.out.println("Shutdown failure");
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
		
	}
	public static void main(String[] args) {
		String sql = "update Course set Credit = 4 where Cno='C001'";
		JDBCDemo.update(sql);
		query();
	}
}

Statement and preparedStatement

Both Statement and preparedStatement can be used for adding, deleting, modifying and querying.

Statement

The statement object can be generated through the createStatement() method of the connection object.
createStatement() method prototype Statement java.sql.Connection.createStatement() throws SQLException

Common methods:

• executeUpdate(sql)；
  Used for database update (addition, deletion and modification)
  Function prototype: int java.sql.Statement.executeUpdate(String sql) throws SQLException
  The return value is int, which is the number of records affected by the update.
• executeQuery(sql) ；
  For database query
  Function prototype: ResultSet java.sql.Statement.executeQuery(String sql) throws SQLException
  The return value is of type ResultSet. A data table used to hold the result set of a database. The ResultSet object points to the cursor of the current data row, which is initially placed before the first row (similar to an iterator).
  You can move the cursor to the next line through the next () method. If there is no next line, false will be returned. Otherwise, true will be returned.
  Get the property value through the method of getXXX ("property name")

			rs = stmt.executeQuery(sql);
			//d. Process result set
			while(rs.next()) {
				System.out.print(rs.getString("Cno")+" ");
				System.out.print(rs.getString("Cname")+" ");
				System.out.print(rs.getInt("Credit")+" ");
				System.out.println(rs.getInt("Semester"));
			}

preparedStatement

preparedStatement extends Statement
Prototype: public abstract interface java.sql.preparedstatement extensions java.sql.statement

Therefore, all the methods of preparedStatement can be used. In addition, there are many more set methods for preparedStatement:

Generated by the prepareStatement(String sql) method of the connection object.
Function prototype: PreparedStatement java.sql.Connection.prepareStatement(String sql) throws SQLException
(Note: the createStatement() of the Statement object must not have the parameter SQL. The SQL Statement is passed in at the last execution.)
The preparedStatement interface is used to represent precompiled SQL statement objects. The SQL statements here usually contain placeholders?. The SQL statement is precompiled and stored in the preparedStatement object, which can improve the efficiency of executing the SQL statement.

//try and catch are omitted here, and the template is roughly the same as above
			//a. Pour driver, load specific driver class
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			//b. Establish connection with database
			con = DriverManager.getConnection(URL, user, pwd);
			//c. performs SQL
			String sql = "select Sno,Sname from Student where Sname like ?";  
			pstmt = con.prepareStatement(sql); //precompile
			pstmt.setString(1,"Zhang%");  //Inquire about the student surnamed Li
			rs = pstmt.executeQuery();
			//d. Process result set
			while(rs.next()) {
				System.out.print(rs.getString("Sno")+" ");
				System.out.print(rs.getString("Sname")+" ");
				System.out.println();
			}

The set method is used to set the value of the placeholder. The first parameter is the order in which the placeholder appears in the SQL statement (starting from 1), and the second parameter is the value.

Advantages of preparedStatement over Statement

1) Easier to write (avoid string concatenation - use placeholders)
2) Improve performance with precompiling
3) More secure (effectively prevents SQL injection)
For example: for a login operation (using statement)
User input user name: XXX 'or 1=1 –
Password: XXX
In this way, the query statement is

select count(*)from login where uname='name' and upwd ='pwd'

Turned into

select count(*)from login where uname='XXX' or 1=1 --' and upwd ='XXX'

– commented after
or 1=1, so it can login successfully.
But when using preparedStatement, user input will be treated as a whole, and dangerous input will be escaped, so it is safer.

CallableStatement

CallableStatement is used to call a saving procedure or saving function
The CallableStatement object is generated through the prepareCall (String sql) method of the connection object.

Note: sql here has format requirements
For saving process: sql={call saving process name (parameter list)}
For storage function: sql = {? =call store function name (parameter list)}
How many placeholders are used for several parameters? replace

Set parameter value through setXXX (index, value)
Set the output parameter type through registerOutParameter (index, SQLType)
After calling execute()
Receive the output parameters through getXXX().

For example, the saving process p3 is used to count the number of boys in a department. The input parameter is the Department and the output parameter is the number of boys in the Department

ALTER PROCEDURE p3 @dept VARCHAR(10),@number INT OUTPUT
AS
SELECT @number = COUNT(Sno) FROM Student WHERE Sdept = @dept AND Ssex = 'male'

//a. Pour in the driver and load the specific driver class
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			//b. Establish connection with database
			con = DriverManager.getConnection(URL, user, pwd);
			//Get CallableStatement object
			cstmt = con.prepareCall("{call p3(?,?)}");
			
			//Assign values to parameters
			cstmt.setString(1, "Department of Computer Science");
			
			//Set the data type of the output result
			cstmt.registerOutParameter(2, Types.INTEGER);
			//implement
			cstmt.execute();
			//Get output
			int result = cstmt.getInt(2);
			System.out.println(result);