JDBC (Java connecting to a database)

maven

<! -- JDBC add database dependency -->
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
Copy the code

The test code

import java.sql.*; /** * @projectName: JDBC * @packagename: PACKAGE_NAME * @author: zy7y * @date: 2020/8/26 5:38 PM * @description: jdbc */ public class TestJdbc01 { public static void main(String[] args) throws ClassNotFoundException, SQLException { // 1. Class. ForName (" com.mysql.jdbc.driver "); / / 2. New connection String url = "JDBC: mysql: / / 192.168.0.222:3306 / school? useUnicode=true&characterEncoding=utf8&useSSL=true"; String username = "root"; String password = "root"; / / connect to get the object database Connection Connection = DriverManager. GetConnection (url, username, password); Executed SQL / / / / 3. The object of the SQL Statement Statement = connection. The createStatement (); String sql = "SELECT * FROM student"; ResultSet ResultSet = Statement.executeQuery (SQL); while (resultSet.next()){ System.out.println("id=" + resultSet.getObject("studentno")); } // 4. Release the connection resultset.close (); // Close the result set object statement.close(); // Close the execution of the SQL object connection.close(); // Close database object}}Copy the code

Connection Database object

        // Set to turn off auto commit
        connection.setAutoCommit(false);
        // Transaction commit
        connection.commit();
        // Transaction rollback
        connection.rollback();
Copy the code

Statement The object on which the SQL is executed

 // Execute SQL to obtain resultSet
        ResultSet resultSet = statement.executeQuery(sql);
        // Execute any SQL statements
        statement.execute(sql);
        // Update, insert, delete use, return a number of affected rows
        statement.executeUpdate(sql); 
Copy the code

The ResultSet result set

            // Get any type of column data
            resultSet.getObject("id");
            // Get column data of type String
            resultSet.getString("name");
            resultSet.getInt("age");
            resultSet.getFloat("money");
            resultSet.getDate("date");
Copy the code

Release the connection

        resultSet.close(); // Close the result set object
        statement.close(); // Close execution of SQL objects
        connection.close(); // Close the database object
Copy the code

SQL injection

www.liaoxuefeng.com/wiki/125259…

SQL has vulnerabilities. Using OR to concatenate SQL and using Statement string can easily cause SQL injection problems. This is because SQL parameters are often passed in from method parameters. If the user’s input is a carefully constructed string, it can spell an unexpected SQL that is correct, but it queries on conditions that are not intended by the program. For example: name =” Bob ‘OR pass=”, pass=” OR pass='” :

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''
Copy the code

PreparedStatement Objects for SQL execution (secure and efficient)

import java.sql.*;

/ * * *@ProjectName: jdbc
 * @PackageName: PACKAGE_NAME
 * @Author: zy7y
 * @Date: 2020/8/26 5:38 PM *@Description: jdbc
 */
public class TestJdbc01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. Load the driver
        Class.forName("com.mysql.jdbc.Driver");

        // 2. Create a connection
        String url = "JDBC: mysql: / / 192.168.0.222:3306 / school? useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";

        // Establish a connection to get the object that operates on the database
        Connection connection = DriverManager.getConnection(url,username,password);

        // 3. Execute SQL PreparedStatement using? Make a placeholder instead of a parameter
        String sql = "SELECT * FROM student where studentname = ?";

        // Execute SQL object,
        PreparedStatement preparedStatement = connection.prepareStatement(sql); // Precompile SQL, write SQL first, do not execute

        // Attach values to arguments (placeholder positions, inserted values)
        preparedStatement.setObject(1."Zhang wei");


        // Execute SQL to obtain resultSet
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("studentno"));

        }
        // release the connection
        resultSet.close(); // Close the result set object
        preparedStatement.close(); // Close execution of SQL objects
        connection.close(); // Close the database object}}Copy the code