1. JDBC principle

1. JDBC API: provides various operation providers, connection statement Preparedstatement resultset;
2. JDBC Driver Manager: manage different data drivers;
3. All kinds of database drivers: database drivers provided by corresponding database manufacturers, which link to directly operate the database;

2. Java operation database (JDBC API)

1. Establish connection with database;
2. Send SQL statement;
3. Return processing results;

DriverManager    //Manage jdbc drivers
Connection    //Connect
Statement   //Additions and deletions
PreparedStatement    //Additions and deletions
CallableStatement    //Calling stored procedures / functions in the database
ResultSet    //Result set returned

Specific steps for JDBC to access database:
a. Import specific driver and load specific driver class;
b. Establish connection with database;
c. Send sql, execute;
d. Processing result set (query);

Database database driver
MySQL                   ojdbc-x.jar
Oracle					mysql-connector-java-x.jar
SqlServer				sqljdbc-x.jar

3. Java operation MySQL

package jdbc;

import java.sql.*;
//import com.mysql.jdbc.Connection;

public class JDBC {
	private final String URL = "jdbc:mysql://localhost:3306/zhouzhongzhou?serverTimezone=GMT%2B8&useSSL=false";
	private final String USER = "root";
	private final String PWD = "123456";

	private Connection connection;
	private Statement stmt;
	private int count;
	private ResultSet rs;
	public void Util() {
		// a. Add database driver and import specific driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// b. Establish connection with database
		try {
			connection = DriverManager.getConnection(URL, USER, PWD);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// c. Send sql, add, delete, modify and query
		try {
			stmt = connection.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// Implementation of sql
		String sql = "insert into stu values('S_9999','NiuJiu',21,'females')";
		// d. Combing results
		try {
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//
		if (count > 0) {
			System.out.println("Operation succeeded!");
		}
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public void Query() {
		//Add specific data drivers
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			connection = DriverManager.getConnection(URL,USER,PWD);
			stmt = connection.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql = "SELECT sid,sname,age,gender from stu";
		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while(rs.next()) {
				String id = rs.getString("sid");
				String name = rs.getString("sname");
				int age = rs.getInt("age");
				String gender = rs.getString("gender");
				System.out.println(id +"--"+ name +"--"+ age +"--" + gender);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String [] args) {
		JDBC jdbc = new JDBC();
		//jdbc.Util();
		jdbc.Query();
		
	}
}

DriverManager; / / manage jdbc drivers
 Connection; / / connect to the database (generated through DriverManager)

Statement(PreparedStatement); / / add, delete, modify and query (generated through Connection)
CallableStatement; / / call the stored procedure in the database and store the function (generated through Connection)

Result; / / returns the result set (generated by Statement, PreparedStatement, CallableStatement)

Operation database object generated by Connection:
Connection generates Statement object: createStatement();
Connection generates PreparedStatement object: preparestitem();
Connection generates a CallableStatement object: prepareCall();

1. Statement operation database:
Add, delete and modify: executeUpdate();
Query: executeQuery();

ResultSet: save the result set, select* from xxxx;
next(); move the cursor down to determine whether there is next data; true, false
 previous(); put back true/false
 Getxxxx (field name | location); get specific field value

2. PreparedStatement operation database:
public interface PreparedStatement extends Statement;
Add, delete and modify: executeUpdate();
Query: executeQuery();
Copy operation: setXxx();

Differences between PreparedStatement and Statement in use:
1,Statement: sql,executeUpdate(sql);
2. PreparedStatement:sql(?,?,?,?); when creating a PreparedStatement object, replace the placeholders with the sql precompiled PreparedStatement (sql); executeupdate(); setxxx()!

PreparedStatement compares the advantages of Statement in use:
1. Precompile operation can be performed
stmt:
stmt = connection.createStatement();
String sql = "insert into stu values('String','String',int,'String')";
stmt.executeUpdate(sql);

pstmt: 
String sql = "insert into stu values(?,?,?,?)";
pstmt = connection.preparedStatement(sql); / / precompile SQL
pstmt.setString(1,name);
pstmt.setInt();
....
2. Improve performance (example: 100 times of repeated increase, only need to compile once)
stmt:
stmt = connection.createStatement();
String sql = "insert into stu values('String','String',int,'String')";
stmt.executeUpdate(sql); / / execute 100 times

pstmt: 
String sql = "insert into stu values(?,?,?,?)";
pstmt = connection.preparedStatement(sql); / / precompile SQL
pstmt.setString(1,name);
pstmt.setInt();
...
pstmt.executeUpdate(); / / execute 100 times
 3. Security (effectively prevent the risk of sql injection)
pstmt: effectively prevent sql injection;
SQL injection: mix the content input by the client with the SQL statement of the developer;

package jdbc;

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

public class PareparedStatementDemo {

	private final String URL = "jdbc:mysql://localhost:3306/zhouzhongzhou?serverTimezone=GMT%2B8&useSSL=false";
	private final String USER = "root";
	private final String PWD = "123456";

	private Connection connection;
	//private Statement stmt;
	private PreparedStatement pstmt;
	private int count;
	private ResultSet rs;
	public void Update() {
		// a. Add database driver and import specific driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// b. Establish connection with database
		try {
			connection = DriverManager.getConnection(URL, USER, PWD);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// c. Send sql, add, delete, modify and query
		/*
		try {
			stmt = connection.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// Implementation of sql
		String sql = "insert into stu values('S_9999','NiuJiu',21,'females')";
		// d,Carding results
		try {
			count = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		*/
		
		String sql = "insert into stu values(?,?,?,?)";
		
		try {
			pstmt = connection.prepareStatement(sql);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			pstmt.setString(1, "s_0000");
			pstmt.setString(2, "zhangsan");
			pstmt.setInt(3, 45);
			pstmt.setString(4, "male");
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		try {
			count = pstmt.executeUpdate();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		//
		if (count > 0) {
			System.out.println("Operation succeeded!");
		}
		if(pstmt!=null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public void Query() {
		//Add specific data drivers
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			connection = DriverManager.getConnection(URL,USER,PWD);
			String sql = "SELECT sid,sname,age,gender from stu";
			pstmt = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//String sql = "SELECT sid,sname,age,gender from stu";
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while(rs.next()) {
				String id = rs.getString("sid");
				String name = rs.getString("sname");
				int age = rs.getInt("age");
				String gender = rs.getString("gender");
				System.out.println(id +"--"+ name +"--"+ age +"--" + gender);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(pstmt!=null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String [] args) {
		PareparedStatementDemo psd = new PareparedStatementDemo();
		//psd.Update();
		psd.Query();
	}
}