JDBC realizes the function of adding deleting modifying and checking database

JDBC, simply speaking, is to use Java to operate the database. The following briefly introduces how to realize the function of adding, deleting and modifying the database.

1. Add data

package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo2 {

    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            //1 Register driver 
            Class.forName("com.mysql.jdbc.Driver");
            //2 , definition sql
            String sql = "insert into course values(?,?,?)";
            //3 , get the Connection Object 
            //student Indicates the database you want to operate on 
            // If it is locakhost : 3306 It can also be abbreviated as "jdbc:mysql:///student"
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");
            //4 Gets the execution sql Object of 
            preparedStatement = connection.prepareStatement(sql);
            // Pass in parameters 
            preparedStatement.setInt(1,5);
            preparedStatement.setString(2,"JavaWeb");
            preparedStatement.setInt(3,88);
            //5 , execution sql
            int count = preparedStatement.executeUpdate();
            //6 , processing results 
            System.out.println(count);
            if (count > 0) {
                System.out.println(" Successful addition ");
            } else {
                System.out.println(" Add failed ");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7 Release resources 
            // Avoid null pointer exceptions 
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2. Delete data

package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo4 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1 Register driver 
            Class.forName("com.mysql.jdbc.Driver");

            //2 Gets the connection object 
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","root");

            //3 , definition sql
            String sql = "delete from course where cno = ?";

            //4 Gets the execution sql Object 
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,5);

            //5 , execution sql
            int count = preparedStatement.executeUpdate();

            //6 , processing results 
            System.out.println(count);
            if (count > 0) {
                System.out.println(" Delete succeeded ");
            } else {
                System.out.println(" Delete failed ");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7 Release resources 
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

3. Modify the data

package cn.itcast.jdbc;

import java.sql.*;

public class JdbcDemo3 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1 Register driver 
            Class.forName("com.mysql.jdbc.Driver");

            //2 Gets the connection object 
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");

            //3 , definition sql
            String sql = "update course set period = ? where cno = ?";

            //4 Gets the execution sql Object 
            preparedStatement = connection.prepareStatement(sql);
            // Setting parameters 
            preparedStatement.setInt(1,90);
            preparedStatement.setInt(2,1);


            //5 , execution sql
            int count = preparedStatement.executeUpdate();

            //6 , processing results 
            System.out.println(count);
            if (count > 0) {
                System.out.println(" Modified successfully! ");
            } else {
                System.out.println(" Modification failed! ");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7 Release resources 
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

4. Query data

package cn.itcast.jdbc;

import cn.itcast.domain.Course;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCDemo5 {

    /**
     *  Query all Course Object 
     * @return
     */
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Course> list = null;
        try {
            //1 Register driver 
            Class.forName("com.mysql.jdbc.Driver");
            //2 Get the connection 
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");

            //3 , definition sql
            String sql = "select * from course";
            //4 Gets the execution sql Object of 
            preparedStatement = connection.prepareStatement(sql);
            //5 , execution sql
            resultSet = preparedStatement.executeQuery();
            //6 Traversing the result set, encapsulating the object, loading the collection 
            Course course = null;
            list = new ArrayList<Course>();
            while (resultSet.next()) {
                // Get data 
                int cno = resultSet.getInt("cno");
                String cname = resultSet.getString("cname");
                int period = resultSet.getInt("period");
                // Create Course Object and assign a value 
                course = new Course();
                course.setCno(cno);
                course.setCname(cname);
                course.setPeriod(period);
                // Load collection 
                list.add(course);

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println(list);
    }

}

We can find that the operations of adding, deleting and modifying are basically similar statements, and the statements that execute sql are all the same, all of which are preparedStatement. executeUpdate (). However, the query operation is different, returning a result set, and the statement executing sql is preparedStatement. executeQuery ().