JDBC programming steps

1, Get link method

1.1 mode 1: use Dirver interface

Driver driver=new com.mysql.jdbc.Driver();
//jdbc:mysql protocol
//localhost:ip address
//3306: default MySQL port number
//test: Database
        String url="jdbc:mysql://localhost:3306/test";
//The properties interface is used to read configuration files in the form of key value pairs;
        Properties properties=new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = driver.connect(url,properties);
        System.out.println(connect);

1.2 mode 2: the iteration of mode 1 and the absence of third-party APIs in the following programs make the program more portable

  //Get the implementation class object by reflection
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        //Call the Driver's null argument constructor with newInstance
        Driver o = (Driver) aClass.newInstance();
        String url="jdbc:mysql://localhost:3306/test";
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        Connection connect = o.connect(url, properties);
        System.out.println(connect);

1.3 method 3: replace the Driver with the Driver manager

//Get Dirver's object
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver o = (Driver) aClass.newInstance();
        //Register driver
        DriverManager.registerDriver(o);
        //Get link
        String url="jdbc:mysql://localhost:3306/test";
        String password="123456";
        String user="root";
        Connection connection = DriverManager.getConnection(url, user,password);
        System.out.println(connection);

1.4 method 4: optimize method 3 and omit the registration driver

//Load Dirver's object
    Class.forName("com.mysql.jdbc.Driver");
        //Register driver
        //Reason for omitting: automatic loading of Driver is implemented in the Driver class of mysql
       // DriverManager.registerDriver(o);
        //Get link
        String url="jdbc:mysql://localhost:3306/test";
        String password="123456";
        String user="root";
        Connection connection = DriverManager.getConnection(url, user,password);
        System.out.println(connection);

1.5 method 5: declare the four basic information required for database connection in the configuration file, and obtain the connection by reading the configuration file
//Benefits: 1. It separates data and code and realizes decoupling. 2

//Get the information of the configuration file through the class loader

##This is JDBC Properties file: url = JDBC: mysql://localhost:3306/test password=123456 user=root driverClass=com. mysql. jdbc. Driver

//Get the information of the configuration file through the class loader
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
//Load it into the load method of Properties to read it
Properties properties = new Properties();
properties.load(is);
//properties is to obtain values through key value pairs
String url = properties.getProperty("url");
String password = properties.getProperty("password");
String user = properties.getProperty("user");
String driverClass = properties.getProperty("driverClass");
//Load driver
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);

2, Implement CRUD operation using PreparedStatement

2.1. Disadvantages of using Statement to operate data table

• There is string splicing operation, which is cumbersome
• There is a problem of SQL injection (SQL injection is a method of injecting illegal SQL statement segments into the user input data by using some systems that do not fully check the user input data, so as to use the system's SQL system to complete malicious behavior)

SQL injection problem: for JAVA, to prevent SQL injection, just use Preparedstatement (extended from Statement) instead of state.
2.2. preparedStatement implements CRUD operation
General version

public void Update_test(String sql,Object ... test) throws Exception {//test means? Number of
        Connection lianjie = internet.lianjie();
        PreparedStatement preparedStatement = lianjie.prepareStatement(sql);
        for (int i = 0; i < test.length; i++) {
            preparedStatement.setObject(i+1,test[i]);
        }
        preparedStatement.execute();
        lianjie.close();
        preparedStatement.close();
    }

 public void cs() throws Exception {
        String sql="delete from customers where id=?";
        Update_test(sql,24);

    }

2.3. The preparedStatement implements query operations
Note: we use a class to return the result set of the query in select in java

ORM idea:

• A data table corresponds to a java class
• A record in the table corresponds to an object of the java class
• A field in the table corresponds to an attribute of the java class
  code:

public user_table get_Select(String sql,Object ...text) throws SQLException, ClassNotFoundException, NoSuchFieldException, IllegalAccessException {
        Connection mysql = Add_Mysql.get_Mysql();
        PreparedStatement preparedStatement = mysql.prepareStatement(sql);
        for (int i = 0; i < text.length; i++) {
        //Fill placeholder
            preparedStatement.setObject(i + 1, text[i]);
        }
        ResultSet resultSet = preparedStatement.executeQuery();
        //Get the metadata of the result set (String name="TOM", then String and name are the metadata of "TOM")
        ResultSetMetaData metaData = resultSet.getMetaData();
        //Get the number of columns in the result through metadata
        int columnCount = metaData.getColumnCount();
        //Take out the result set and assign it to the class
        if (resultSet.next()) {
            user_table user = new user_table();
            for (int i = 0; i < columnCount; i++) {
                //Get select each field value through resultSet
                Object value = resultSet.getObject(i + 1);
                //Get the column name of each column through metadata getColumnName (not recommended)
                //Or alias getcolumnlabel
                String catalogName = metaData.getColumnName(i+1);
                //To user_ A field of table is assigned a value
                Field declaredField = Class.forName("jdbc.user_table").getDeclaredField(catalogName);
                declaredField.setAccessible(true);
                //Assign data to that object
                declaredField.set(user, value);

            }
            return user;
        }
        return null;
    }

public static void main(String[] args) throws ClassNotFoundException, SQLException, IllegalAccessException, NoSuchFieldException {
        Select_text select_text = new Select_text();
        String sql="select user,password,balance from user_table where user=?";
        user_table select = select_text.get_Select(sql,"BB");
        System.out.println(select);
    }

Query considerations
When the field name and attribute name for the table are different
1. You must declare that sql is the alias of the field named by the class attribute
2. When using resultsetmeta, you need to use getColumnLabel() instead of getcolumnname() to get the alias of the column
3. If there is no alias for the field in sql, getColumnLabel gets the column name

Query codes for different tables:

public <T> T Select (  Class<T> clazz,String sql,Object ...text) throws Exception {
        Connection connection = And_Mysql();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < text.length; i++) {
            preparedStatement.setObject(i+1,text[i]);
        }
        ResultSet resultSet = preparedStatement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while(resultSet.next()){
        //Get a different watch by reflection or
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object object = resultSet.getObject(i + 1);
                String columnLabel = metaData.getColumnLabel(i + 1);
                Field declaredField = Class.forName("jdbc.User").getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(t,object);
            }
            return t;
        }
        return  null;
    }

Query multiple fields

   */
    public <T> List Select (Class<T> clazz, String sql, Object ...text) throws Exception {
        Connection connection = And_Mysql();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < text.length; i++) {
            preparedStatement.setObject(i+1,text[i]);
        }
        ResultSet resultSet = preparedStatement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        //Add the queried data to the list with and return it to the list at last
        ArrayList<Object> list = new ArrayList<>();
        while(resultSet.next()){
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object object = resultSet.getObject(i + 1);
                String columnLabel = metaData.getColumnLabel(i + 1);
                Field declaredField = Class.forName("jdbc.User").getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(t,object);
            }
            list.add(t);
        }
        return list;
    }