JDBC

• Concept: Java DataBase Connectivity - Java database connection, Java language operation database
• JDBC essence: in fact, it is an officially defined set of rules for operating all relational databases, that is, interfaces. Each database manufacturer implements this interface and provides database driver jar package. We can use this set of interfaces to program, and the real executed code is to drive the implementation classes in the jar package

Simple example

//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 "," user name "," password ");

//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 = stml.executeUpdate(sql);

//7. Treatment results
System.out.println(count);

//8. Release resources
stmt.close();
conn.close();

Explain each object in detail:

DriverManager: drive management objects

Function:

• Register driver: tell the program which database driver jar to use

static void registerDriver(Driver driver): Register with the given driver
//Write code to use
Class.forName("com.mysql.jdbc.Driver");

//Source code: com mysql. jdbc. There are in the 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 step of registering the driver

• Get database connection

//Methods static Connection getConnection(String url,String user,String password);

parameter

• url: Specifies the path of the connection
  • Syntax: jdbc:mysql://ip Address (domain name): port number / database name
  • Example: jdbc:mysql://local: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

Connection: database connection object

Function:

• Get the object executing sql

Statement createStatement()
PrepareStatement prepareStatement(String sql);

• Management services
  • Start transaction: setautocommit (Boolean autocommit). When autocommit = false, start the transaction
  • Commit transaction: commit()
  • Rollback transaction: rollback()

Statement: object to execute sql

• Execute sql
  • boolean execute(String sql): arbitrary sql can be executed
  • int executeUpdate(String sql): specify DML(insert,update,delete) statements and DDL(create,alter,drop) statements
  • ResultSet executeQuery(String sql): execute DQL(select) statements

ResultSet: result set object that encapsulates query results

• boolean next(): move the cursor down one line to determine 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
  • Parameter: int: represents the number of the column, starting from 1, such as getInt(1); String: represents the name of the column, such as getDouble("balance");
• Use steps:
  • Move cursor down one line
  • Determine whether there is data
  • get data

PrepareStatement: the object that executes sql

• SQL injection problem: when splicing SQL, some special keywords of SQL participate in string splicing, which will cause security problems

//User input password:
a' or 'a' = 'a
//sql after splicing
select * from user where username = 'fdasfd' and password ='a' or 'a' = 'a'

//Obviously, the Boolean value after where is always true

• Solve the sql injection problem: use the PreparedStatement object to solve it
• Precompiled SQL: parameter usage? As placeholder
• Steps:

  1. Import jar package

  2. Register driver

  3. Get database Connection object Connection

  4. Define sql (Note: the parameters in sql use "? As placeholders, such as: select * from user where username =? and password =?)

  5. Gets the object PreparedStatement that executes the sql statement

     Connection.preparedStatement(String sql)
     

  6. Here? Assignment, method: setXXX (parameter 1, parameter 2); Parameter 1:? Position number of, starting from 1; Parameter 2:? Value of

  7. Execute sql and receive the returned results without passing sql statements

  8. Processing results

  9. Release resources

PreparedStatement will be used later to complete all operations of adding, deleting, modifying and querying

• Can prevent SQL injection
• More efficient