Getting started with JDBC

Take notes according to the pictures and texts of the course for your review when necessary

1.JDBC concept

• Java DataBase Connectivity: Java database connection, Java language operation database
• JDBC essence: actually, it is a set of rules defined by the official (sun company) to operate all relational databases, that is, interfaces. Each database manufacturer implements this set of interfaces and provides database driven jar packages. This set of interfaces (JDBC) can be used for programming, and the real executed code is to drive the implementation classes in the jar packages.

2. Basic JDBC steps

2.1 steps

	1. Import driver jar package mysql-connector-java-xxxx-bin.jar
		1.copy mysql-connector-java-xxxx-bin.jar To project libs Directory
		2.Right click-->Add As Library
	2. Register driver
	3. Get database connection object Connection
	4. definition sql
	5. Get execution sql Statement object Statement
	6. implement sql，Accept returned results
	7. Processing results
	8. Release resources

2.2 code implementation

//1. Import the driver jar package
//2. Register driver
Class.forName("com.mysql.jdbc.Driver");
//3. Get database connection object
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4. Define sql statements
String sql = "update account set balance = 500 where id = 1";
//5. Get the object Statement executing sql
Statement stmt = conn.createStatement();
//6. Execute sql
int count = stmt.executeUpdate(sql);
//7. Treatment results
System.out.println(count);
//8. Release resources
stmt.close();
conn.close();

3. Detailed explanation of JDBC objects

3.1 DriverManager: drive management object

• Register driver: tell the program which database driver jar to use
  static void registerDriver(Driver driver): register with the given driver DriverManager.
  Write code using: class forName("com.mysql.jdbc.Driver");
  By looking at the source code, I found that: on COM mysql. jdbc. Static code block in driver class

 static {
		try {
			java.sql.DriverManager.registerDriver(new Driver());
		} catch (SQLException E) {
			throw new RuntimeException("Can't register driver!");
	 }
}

Note: the driver jar package after mysql5 can omit the steps of registering the driver.

• Get database connection:
  • Method: static Connection getConnection(String url, String user, String password)
  • Parameters:
    • url: Specifies the path of the connection
      • Syntax: jdbc:mysql://ip Address (domain name): port number / database name
      • Example: jdbc:mysql://localhost:3306/db3
      • Details: if the local mysql server is connected and the default port of mysql service is 3306, the url can be abbreviated as jdbc:mysql: / / / database name
    • User: user name
    • Password: password

3.2 Connection: database connection object

Function:

• Get the object executing sql

  • Statement createStatement()
  • PreparedStatement prepareStatement(String sql)

• Management services:

  • Start transaction: setAutoCommit(boolean autoCommit): call this method to set the parameter to false, that is, start the transaction

• Commit transaction: commit()

• Rollback transaction: rollback()

3.3 Statement: object to execute sql

1. Execute sql

• boolean execute(String sql): arbitrary sql can be executed

• int executeUpdate(String sql): execute DML (insert, update, delete) statements and DDL(create, alter, drop) statements
  *Return value: the number of affected rows. You can judge whether the DML statement is executed successfully by the number of affected rows. If the return value > 0, the execution is successful; otherwise, it fails.

• ResultSet executeQuery(String sql): execute DQL (select) statements

1. give an example
   • Add a record to the account table
   • account table modification record
   • Delete a record from the account table

Statement stmt = null;
		        Connection conn = null;
		        try {
		            //1. Register driver
		            Class.forName("com.mysql.jdbc.Driver");
		            //2. Define sql
		            String sql = "insert into account values(null,'Wang Wu',3000)";
		            //3. Get the Connection object
		            conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
		            //4. Get the object Statement executing sql
		            stmt = conn.createStatement();
		            //5. Execute sql
		            int count = stmt.executeUpdate(sql);//Number of rows affected
		            //6. Treatment results
		            System.out.println(count);
		            if(count > 0){
		                System.out.println("Successfully added!");
		            }else{
		                System.out.println("Failed to add!");
		            }
		
		        } catch (ClassNotFoundException e) {
		            e.printStackTrace();
		        } catch (SQLException e) {
		            e.printStackTrace();
		        }finally {
		            //stmt.close();
		            //7. Release resources
		            //Avoid null pointer exceptions
		            if(stmt != null){
		                try {
		                    stmt.close();
		                } catch (SQLException e) {
		                    e.printStackTrace();
		                }
		            }
		
		            if(conn != null){
		                try {
		                    conn.close();
		                } catch (SQLException e) {
		                    e.printStackTrace();
		                }
		            }
		        }

3.4 ResultSet: result set object that encapsulates query results

• boolean next: move the cursor down one line to judge whether the current line is at the end of the last line (whether there is data). If yes, it returns false. If not, it returns true
• Getxxx (parameter): get data
  • Xxx: represents the data type, such as int getint(), string getstring()
    Parameters:
    1. int: represents the number of the column, starting from 1, such as getString(1)
    2. String: represents the name of the column. For example: getDouble("balance")
    Use steps:
    1. Move the cursor down one line
    2. Judge whether there is data
    3. Obtain data

				 //Loop to determine whether the cursor is at the end of the last line
	            while(rs.next()){
	                //get data
	                //6.2 data acquisition
	                int id = rs.getInt(1);
	                String name = rs.getString("name");
	                double balance = rs.getDouble(3);
	
	                System.out.println(id + "---" + name + "---" + balance);
	            }

• Define a method to query the data of emp table, encapsulate it as an object, then load the collection and return.
  1. Define Emp class
  2. Define the method public List findAll() {}
  3. Implementation method select * from emp;

3.5 PreparedStatement: the object that executes sql

1. SQL injection problem: when splicing SQL, some special keywords of SQL participate in string splicing. It will cause security problems
   1. Enter the user's password: a 'or' a '='a
   2. sql: select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'

2. Solve the sql injection problem: use the PreparedStatement object to solve it

3. Precompiled SQL: parameter usage? As placeholder

4. Steps:
   1. Import the driver jar package mysql-connector-java-x.x.xxx-bin jar
   2. Register driver
   3. Get the database Connection object Connection
   4. Define sql
   *Note: the sql parameters are used in the? As a placeholder. For example: select * from user where username =? and password = ?;
   5. Get the Preparedstatement connection. Object that executes the SQL statement prepareStatement(String sql)
   6. Here you are? Assignment:
   Method: setXXX (parameter 1, parameter 2)
   Parameter 1:? The position number of starts from 1
   Parameter 2:? Value of
   7. Execute sql and accept the returned results without passing sql statements
   8. Treatment results
   9. Release resources

5. Note: PreparedStatement will be used later to complete all operations of adding, deleting, modifying and querying
   1. Prevent SQL injection
   2. Higher efficiency