The first JDBC [Introduction to JDBC, using JDBC to connect to the database, simple tools]

tags: JDBC

1. What is JDBC

The full name of JDBC: Java Data Base Connectivity, it is a Java API that can execute SQL statements

2. Why do we use JDBC

• There are many databases on the market. Originally, we needed to learn different APIs according to different databases. In order to simplify this operation, Sun company defined the JDBC API [interface]
• Sun only provides the JDBC API [interface], and the database manufacturer is responsible for the implementation.
• For us, operating databases are all on the JDBC API [interface] , using different databases, as long as you use the database driver provided by the database manufacturer
• This greatly simplifies our learning costs

3. Simple operation of JDBC

step:

1. Import MySQL or Oracle driver package
2. Load the database driver
3. Get connected to the database
4. Get objects that can execute SQL statements
5. Execute SQL statement
6. Close the connection

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {

            /*
            *  
            *
            * 1 mysql api mysql 
            * 2 
            *
            *  
            * */

            //1.
            //DriverManager.registerDriver(new com.mysql.jdbc.Driver());

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

            //-Connetcion
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/zhongfucheng", "root", "root");

            //sql statement 
            statement = connection.createStatement();

            //sql , 
            resultSet = statement.executeQuery("SELECT * FROM users");

            //
            while (resultSet.next()) {

                System.out.println(resultSet.getString(1));

                System.out.println(resultSet.getString(2));
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {

            /*
            *  
            *
            *  
            * */

            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }


 

Above we have simply used JDBC to query the data in the database. Next, let's take a look at the objects used in the above code.

4.Connection object

All interactions between the client and the database are done through Connection.

commonly used ways:

//sql statement 

createcreateStatement()

//sql PrepareSatement 

prepareStatement(sql) 

//callableStatement 

prepareCall(sql)

//

setAutoCommit(boolean autoCommit)

//

commit()

//

rollback()


 

5.Statement object

The Statement object is used to send SQL statements to the database, and all additions, deletions, and changes to the database can be completed by sending SQL statements through this object.

Common methods of Statement object:

//

executeQuery(String sql)

//

executeUpdate(String sql)

//sql 

execute(String sql)

//sql 

addBatch(String sql)

//sql 

executeBatch()

 

6.ResultSet object

The ResultSet object represents the execution result of the Sql statement . When the Statement object executes executeQuery(), it will return a ResultSet object

The ResultSet object maintains a cursor of a data row [simply understood as a pointer]. Calling the ResultSet.next() method allows the cursor to point to a specific data row to obtain the data of the row

Common methods:

//

getObject(String columnName)

//API 

getString(String columnName)

//

next()

Previous()

absolute(int row)

beforeFirst()

afterLast()


 

7. Write a simple tool class

Through the above understanding, we have been able to use JDBC to add, delete, modify, and check the database data. We found that regardless of the addition, deletion, modification, and query, we need to connect to the database and close the resources, so we extract the operations of connecting to the database and releasing resources to a tool class

    /*
    *  driver url username password 
    *  
    *
    * */

    private static String  driver = null;
    private static String  url = null;
    private static String  username = null;
    private static String password = null;

    static {
        try {

            //
            InputStream inputStream = UtilsDemo.class.getClassLoader().getResourceAsStream("db.properties");

            Properties properties = new Properties();
            properties.load(inputStream);

            //
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //
            Class.forName(driver);


        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

 

If you think this article helped you, you can give the author a little encouragement