Java JDBC learning

JDBC (Java DataBase Connectivity) java database connection
● is a Java API for executing SQL statements, which can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language.
With JDBC,java developers only need to write a program once to access different databases

For JDBC API:

Interfaces and classes for programmers to call are integrated in Java SQL package

DriverManager class is used to manage various jDBC drivers

The Connection interface connects to a specific database

Statement interface executes sql

The PreparedStatement interface executes sql

The ResultSet interface receives query results

For the construction of Java JDBC program:

Step 1:

The driver needs to be initialized so that the communication channel with the database can be opened.

Class.forName("com.mysql.cj.jdbc.Driver"); //Reflection implementation
or
DriverManager.registerDriver(new Driver());

Step 2:

Establish a connection to the database:

This requires the use of drivermanager Getconnection () method to create a
Connection object, which represents a physically connected database

Connection con = DriverManager.getConnection(URL,USER,PASS);
URL:jdbc:mysql://ip(127.0.0.1): port (3306) / database name? characterEncoding=utf8&useSSL=false&serverTimezone=UTC
USER:Local database user name(root)
PASS:Local database password

Step 3:

Get Satement execute sql statement

Statement st = connection.createStatement();

Method in Satement: Int executeUpdate(String sql) is used to execute the number of rows returned by ddl statements and DML (add, delete, change) statements
Used to execute ddl statements and return 0
The number of rows used to execute the return operation of the dml statement
ResultSet executeQuery(String sql); Used to execute query statements and return a resultset collection

Example:

 public static void main(String[] args) {
        try {
            //Load mysql driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            //Connect to database
            Connection connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
           // System.out.println(connection);
            //Send SQL to database
            Statement st= connection.createStatement();
            st.executeUpdate("insert into course (course)values ('php')");//Insert data into the database
//Interrupt transmission
            st.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

Next, we begin to use IDEA to change the data in the database table

Start with the most basic use of the database to add data

public class JDBCDemo1 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo1 jdbcDemo1=new JDBCDemo1();
        jdbcDemo1.student("tom","male","2020-2-2");
    }
    private void student(String name,String sex,String birthday) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate("INSERT INTO t_student(NAME,sex,birthday,reg_time)" +
                    " VALUES('"+name+"','"+sex+"','"+birthday+"',now())");
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }
    }
}

The above methods are suitable for beginners and are not convenient to add multiple pieces of data to the database. The method and efficiency of adding can be improved by customizing the Student method

Get PrepareStatement execute sql statement

● use placeholders for parameter positions in sql statements, and use setXX method to set parameters in sql

PrepareStatement ps = connection.prepareStatement(sql);

Methods in PrepareStatement:
Int executeUpdate() is used to execute the number of rows returned by ddl statements and DML (add, delete, change) statements
Used to execute ddl statements and return 0
The number of rows used to execute the return operation of the dml statement
ResultSet executeQuery(); Used to execute query statements and return a resultset collection

public class Student {
    private int id;
    private String name;
    private String sex;
    private Date birthday;
    private Date reg_time;
    private int phone;
    private int height;
    private int weight;
    private  int score;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Date getBirthday(Timestamp birthday) {
        return this.birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public Date getReg_time(Timestamp reg_time) {
        return this.reg_time;
    }
    public void setReg_time(Date reg_time) {
        this.reg_time = reg_time;
    }
    public int getPhone() {
        return phone;
    }
    public void setPhone(int phone) {
        this.phone = phone;
    }
    public int getHeight(int height) {
        return this.height;
    }
    public void setHeight(int height) {
        this.height = height;
    }
    public int getWeight(int weight) {
        return this.weight;
    }
    public void setWeight(int weight) {
        this.weight = weight;
    }
    public int getScore(int score) {
        return this.score;
    }
    public void setScore(int score) {
        this.score = score;
    }
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", reg_time=" + reg_time +
                ", phone=" + phone +
                ", height=" + height +
                ", weight=" + weight +
                ", score=" + score +
                '}';
    }
}

public class JDBCDemo5 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo5 jdbcDemo5=new JDBCDemo5();
        jdbcDemo5.student("tom","male","2000-3-3","2000-3-3");
    }
    private void student(String name, String sex,Object birthday,Object reg_time) throws SQLException {
        Connection connection=null;
        PreparedStatement pt=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("insert into t_student(name,sex,birthday,reg_time)values(?,?,?,?)");//? Represents a placeholder,
            pt.setString(1,name);
            pt.setString(2,sex);
           pt.setObject(3,birthday);
           pt.setObject(4,reg_time);
            pt.executeUpdate();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            if(connection!=null){
                connection.close();
            }
            if(pt!=null){
                pt.close();
            }
        }
    }
}

The first execution cost of PreparedStatement is very high Its performance is reflected in the subsequent repeated execution Use the PreparedStatement method to execute a query against the database table The JDBC driver will send a network request to data analysis and optimize the query When executed, another network request will be generated In JDBC driver, reducing network communication is the ultimate goal If my program needs only one request during runtime, use Statement For Statement, the same query will only generate network to database communication once Such code is readable and maintainable It is much higher than the code directly using Statement. The most important thing is that it greatly improves the security Prevent sql injection

Delete operation

public class JDBCDemo2 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo2 jdbcDemo2=new JDBCDemo2();
        jdbcDemo2.student(1);
    }
    private void student(int id) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate("delete from number where Dept_id="+id);
            int row1=st.executeUpdate("delete from dept where id="+id);
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }
    }
}

Note: when deleting, if multiple tables are associated, the associated slave tables must be deleted first, otherwise the deletion cannot be performed

When performing an operation using the PreparedStatement method

public class JDBCDemo6 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo6 jdbcDemo5=new JDBCDemo6();
        jdbcDemo5.student(10);
    }
    private void student(int id) throws SQLException {
        Connection connection=null;
        PreparedStatement pt=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("delete from t_student where id=?");
          pt.setObject(1,id);
            pt.executeUpdate();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();}
        finally {
            if(connection!=null){
                connection.close();
            }
            if(pt!=null){
                pt.close();
            }
        }
    }
}

Make changes to database messages:

public class JDBCDemo4 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo4 jdbcDemo3=new JDBCDemo4();
        jdbcDemo3.student(8,"jy","female","2000-12-13");
    }
    private void student(int id,String name,String sex,String birthday) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate("update t_student set name='"+name+"',sex='"+sex+"',birthday='"+birthday+"'where id="+id);
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }
    }
}

When performing a delete operation using the PreparedStatement method:

public class JDBCDemo7 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo7 jdbcDemo5=new JDBCDemo7();
        jdbcDemo5.student("1 or 1=1");
    }

    private void student(String id) throws SQLException {
        Connection connection=null;
        PreparedStatement pt=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("delete from t_area where id=?");
          pt.setObject(1,id);
            pt.executeUpdate();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();}
        finally {
            if(connection!=null){
                connection.close();
            }

            if(pt!=null){
                pt.close();
            }
        }
    }
}

public class JDBCDemo7b {
    public static void main(String[] args) throws SQLException {
        JDBCDemo7b jdbcDemo2=new JDBCDemo7b();
        jdbcDemo2.student("1 or 1=1");
    }
    private void student(String id) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate( "delete from t_area where id = 1 or 1 = 1");
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }

    }
}

For the thread of JDBC demo7, it will detect SQL attacks. When setting the value through the set method, it will detect. If other keywords are passed in, an exception will be displayed

In JDBCDemo7b, it cannot verify the value passed in. After meeting the condition of 1 = 1, it will clear the database and empty the whole database. After execution, it will return 0

When performing a lookup operation on the database using the PreparedStatement method

public class JDBCDemo8 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo8 jdbcDemo1=new JDBCDemo8();
        jdbcDemo1.student(8);
    }
    private void student(int id) throws SQLException {
        Connection connection=null; //Connect to database
        PreparedStatement pt=null;
Student student=new Student();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("\n" +
                    "SELECT\n" +
                    "  id,\n" +
                    "  NAME,\n" +
                    "  sex,\n" +
                    "  birthday,\n" +
                    "  reg_time,\n" +
                    "  phone,\n" +
                    "  height,\n" +
                    "  weight,\n" +
                    "  score\n" +
                    "FROM\n" +
                    "  t_student\n" +
                    "WHERE id = ?\n");
            pt.setInt(1,id);
            ResultSet res=pt.executeQuery();
            while(res.next()){
                student.setId(res.getInt("id"));
                student.setName(res.getString("name"));
                student.setSex(res.getString("sex"));
                student.getBirthday(res.getTimestamp("birthday"));
                student.getReg_time(res.getTimestamp("reg_time"));
                student.getHeight(res.getInt("height"));
                student.getWeight(res.getInt("weight"));
                student.getScore(res.getInt("score"));
            }
            System.out.println(student);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            pt.close();
        }
    }
}

If we want to find multiple pieces of data at the same time, we can use the collection to find:

public class JDBCDemo9 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo9 jdbcDemo1=new JDBCDemo9();
        jdbcDemo1.student("male");
    }
    private ArrayList<Student>student(String sex) throws SQLException {
        Connection connection=null; //Connect to database
        PreparedStatement pt=null;
        ArrayList<Student>list=new ArrayList();
Student student=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("\n" +
                    "SELECT\n" +
                    "  id,\n" +
                    "  NAME,\n" +
                    "  sex,\n" +
                    "  birthday,\n" +
                    "  reg_time,\n" +
                    "  phone,\n" +
                    "  height,\n" +
                    "  weight,\n" +
                    "  score\n" +
                    "FROM\n" +
                    "  t_student\n" +
                    "WHERE sex = ?\n");
            pt.setString(1,sex);
            ResultSet res=pt.executeQuery();
            while(res.next()){
                student=new Student();
                student.setId(res.getInt("id"));
                student.setName(res.getString("name"));
                student.setSex(res.getString("sex"));
                student.getBirthday(res.getTimestamp("birthday"));
                student.getReg_time(res.getTimestamp("reg_time"));
                student.getHeight(res.getInt("height"));
                student.getWeight(res.getInt("weight"));
                student.getScore(res.getInt("score"));
                list.add(student);
            }
            System.out.println(list);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            pt.close();
        }
return list;
    }
}

Use the next() method in the ResultSet to get the next row of data
Use the getXXX(String name) method to get the value