1 database driver  

Driver: sound card, graphics card, database

  Our program will deal with the database through the database driver;

2 database driver

  In order to simplify the operation of developers (unified database), sun company provides a specification (java database operation) that is JDBC. The implementation of these specifications is done by specific manufacturers

  For developers, we only need to master the operation of jdbc

jar packages required for development: you can download them from maven's official website

 java.sql 

javax.sql

You also need to import the database driver package

3. The first jdbc program

1. Create a maven project

Create the database first

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan',123456,'zs@sina.com','1980-12-04'),
(2,'lisi',123456,'lisi@sina.com','1981-12-04'),
(3,'wangwu',123456,'wangwu@sina.com','1979-12-04')

  Import database driver

The code is as follows:

package com.chen;

import java.sql.*;

//My first jdbc program
public class test01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1 load drive
        Class.forName("com.mysql.jdbc.Driver");
        // 2 user information and url
        String url="jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="123456";
        // 3. The link is successful
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. Objects executing sql
        Statement statement = connection.createStatement();
        // 5. The object executing sql executes sql and returns the result if there is a result
        String sql=" SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("passwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        // 6 release the connection
        resultSet.close();
        statement.close();
        connection.close();
    }
}

Execution results:

 

Steps:

1. Load driver
2. Connect to database DriverManager
3. Gets the Statement object that executes SQL
4. Get return result set
5. Release connection

// DRIVER
Class.forName("com.mysql.jdbc.Driver");

// URL 

String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

mysql default port 3306.

// statement

Write SQL:   String sql=" SELECT * FROM users";

statement.executeQuery() -- query operation, return resultset
 statement.executeUpdate() -- modify, delete and add operations, and return the number of rows executed
 statement.execute() -- all sql statements can be executed, which is inefficient
 statement.executeBatch() -- multiple sql statements can be executed

//   resultSet: encapsulates all query results

Returns data of the specified type

• resultSet.getObject()   resultSet.getDate()resultSet.getInt() resultSet.getString()........

resultSet.beforeFirst();     -- Move to the front
 resultSet.next();                 -- Move to next
 resultSet.afterLast();         -- Move to the back

//Release resources

resultSet.close();
statement.close();
connection.close();

4 statement object

The statement object in java is used to send sql statements to the database. To complete the addition, deletion, modification and query of the database, you only need to provide this object to send sql statements to the database.

Query operation: returns query results

ResultSet resultSet = statement.executeQuery("select * from users");
System.out.println("id="+resultSet.getObject("id"));

Add: returns the number of affected rows

int i = statement.executeUpdate("insert into users(id,NAME,PASSWORD) values(1,'cxl','123456')");

 if(i>0) System.out.println("Increase success");

Delete: returns the number of affected rows

int i = statement.executeUpdate("delete from users where id = 1");
 if(i>0) System.out.println("Delete succeeded");

Modify: returns the number of affected rows

    int i  =statement.executeUpdate("update users set NAME='cxl' where id=1");
     if(i>0) System.out.println("Modified successfully");

The use of tool classes can reduce the coupling of programs, facilitate development and improve performance. The code is as follows:

Tools:

package com.jdbc.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcutil {
    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;
    static {
        // Get profile
        InputStream in = jdbcutil.class.getClassLoader().getResourceAsStream("connect.properties");
        Properties properties = new Properties();
        try {
            // Load profile
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            // Load driver
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // Get connection
    public  static Connection  getConnect() throws SQLException {
       return DriverManager.getConnection(url, username, password);
    }
    // Release connection
    public  static void res(Connection connection,Statement statement, ResultSet resultSet){
       if(resultSet!=null){
           try {
           resultSet.close();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }}
       if(statement!=null){
           try {
               statement.close();
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           }
       }
    if(connection!=null){
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    }
}

Insert:

package com.jdbc;

import com.jdbc.util.jdbcutil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class insert {
    public static void main(String[] args) {
        Connection connect=null;
        Statement statement=null;
        ResultSet resultSet=null;
        // Get connection
        try {
             connect = jdbcutil.getConnect();
            statement = connect.createStatement();
            int i = statement.executeUpdate("INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(4,'zhangsan2',123456,'zs2@sina.com','1980-12-04')");
            while(i>0){
                System.out.println("Insert successful");
                break;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcutil.res( connect,statement,resultSet);
        }
        }
}

Delete:

package com.jdbc;

import com.jdbc.util.jdbcutil;

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

public class delete {
    public static void main(String[] args) {
        Connection connect=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connect = jdbcutil.getConnect();
            statement = connect.createStatement();
            int i = statement.executeUpdate("delete from users where id = 4");
            while(i>0){
                System.out.println("Delete succeeded");
                break;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //Release connection
        finally {
            jdbcutil.res( connect,statement,resultSet);
        }
    }
}

Modification:

package com.jdbc;

import com.jdbc.util.jdbcutil;

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

public class update {
    public static void main(String[] args) {
        Connection connect =null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connect = jdbcutil.getConnect();
            statement = connect.createStatement();
            int i=statement.executeUpdate("UPDATE users SET `NAME`='chen',PASSWORD='12121',email='cxl@qq.com',birthday='1999-12-11' WHERE id = 1");
            while (i>0){
                System.out.println("Modified successfully");
                break;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            jdbcutil.res( connect,statement,resultSet);
        }
    }
}

Query:

package com.jdbc;

import com.jdbc.util.jdbcutil;

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

public class query {
    public static void main(String[] args) {
        Connection connect =null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connect = jdbcutil.getConnect();
           statement= connect.createStatement();
            resultSet = statement.executeQuery("select * from users where id = 1");
            while(resultSet.next()){
                System.out.println(resultSet.getObject("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            jdbcutil.res( connect,statement,resultSet);
        }
    }
}

Problems with sql injection:

There is a vulnerability in sql, which will be attacked and lead to data disclosure. (data will be spliced or)

For example, enter the code under the user name and password status normally, and output the result normally

package com.jdbc;


import com.jdbc.util.jdbcutil;

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

public class sql_injection {
         public static void main(String[] args) {
             login("lisi","123456");
         }
         public static void login(String username,String password){
             Connection connect =null;
             Statement statement=null;
             ResultSet resultSet=null;
             try {
                 connect = jdbcutil.getConnect();
                 statement = connect.createStatement();
                 resultSet = statement.executeQuery("select * from users where `NAME`='" + username + "' and PASSWORD='" + password + "'");
                 while (resultSet.next()){
                     System.out.println(resultSet.getObject("birthday"));
                     break;
                 }
             } catch (SQLException throwables) {
                 throwables.printStackTrace();
             }finally {
                 jdbcutil.res(connect,statement,resultSet);
             }
         }
}

Input under abnormal state: the user name and password are not entered correctly

package com.jdbc;


import com.jdbc.util.jdbcutil;

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

public class sql_injection {
         public static void main(String[] args) {
             login("'or' 1=1","'or' 1=1");
         }
         public static void login(String username,String password){
             Connection connect =null;
             Statement statement=null;
             ResultSet resultSet=null;
             try {
                 connect = jdbcutil.getConnect();
                 statement = connect.createStatement();
                 resultSet = statement.executeQuery("select * from users where `NAME`='" + username + "' and PASSWORD='" + password + "'");
                 while (resultSet.next()){
                     System.out.println(resultSet.getObject("birthday"));
                 }
             } catch (SQLException throwables) {
                 throwables.printStackTrace();
             }finally {
                 jdbcutil.res(connect,statement,resultSet);
             }
         }
}

  Result: all data in the table can be accessed illegally, which is the harm of sql injection

 

 

5 preparedstatement object

preparedstatement can prevent sql injection and is more efficient.

Use preparedstatement to solve the sql injection problem. The code is as follows:

package com.jdbc;


import com.jdbc.util.jdbcutil;
import java.sql.*;

public class sql_injection {
         public static void main(String[] args) {
           //  login("'or' 1=1","'or' 1=1");
            login("lisi","123456");
         }
         public static void login(String username,String password){
             Connection connect =null;
             PreparedStatement statement=null;
             ResultSet resultSet=null;
             try {
                 connect = jdbcutil.getConnect();
                 String sql="select * from users where `NAME`=? and PASSWORD=?";
                 statement = connect.prepareStatement(sql);
                 statement.setString(1,username);
                 statement.setString(2,password);
                 resultSet = statement.executeQuery();
                 while (resultSet.next()){
                     System.out.println(resultSet.getObject("birthday"));
                 }
             } catch (SQLException throwables) {
                 throwables.printStackTrace();
             }finally {
                 jdbcutil.res(connect,statement,resultSet);
             }
         }



/*
    public static void main(String[] args) {
        login("'or' 1=1","'or' 1=1");
    }
    public static void login(String username,String password){
        Connection connect =null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connect = jdbcutil.getConnect();
            statement = connect.createStatement();
            resultSet = statement.executeQuery("select * from users where `NAME`='" + username + "' and PASSWORD='" + password + "'");
            while (resultSet.next()){
                System.out.println(resultSet.getObject("birthday"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcutil.res(connect,statement,resultSet);
        }
    }*/
}