Java: JDBC and java JDBC

Java: JDBC and java JDBC
JDBC is called Java DataBase Connectivity (java DataBase connection ). To simplify and unify database operations, SUN defines a set of Java database operation specifications, called JDBC. To put it simply, JDBC is used to execute SQL statements in Java programs. The significance of the driver is to provide unified interfaces and hide implementation details. The driver defines what the database can do (what to do). For example, the four steps mentioned above, the database manufacturer (such as Oracle) provides the implementation that conforms to these interfaces (how to do ), in writing a Java program, we only need to call the interface in the driver program to operate the database and complete these four steps. Similar to the driver of computer hardware, the JDBC driver achieves the separation of "what to do" and "how to do. Similar to using SQLPlus to access a database, you need to establish a connection with the database before operating the database. Connection is a virtual concept that does not necessarily correspond to network connections (such as small file databases). After a connection is established, you can call the SQL statement through the obtained connection object. The basic meaning of operation data is to execute SQL statements, including DML, DDL, and DCL. You can also call existing stored procedures in the database. When resources are released for JDBC programming, the connection established with the database and the statement object created through this connection may all need to call the corresponding close method to release the underlying network connection, or open a file. Load the database Driver: DriverManager can be used to load the Driver DriverManager. registerDriver (new Driver (); import com. mysql. jdbc. Driver; the package of the corresponding Driver must be imported, which is too dependent. Note: The registerDriver method is not recommended in actual development. There are two reasons: 1. Check the Driver source code. If this method is used, the Driver will be registered twice, that is, there will be two Driver objects in the memory. 2. The program depends on the mysql api and cannot be compiled without the mysql jar package. It will be very troublesome to switch the program to the underlying database in the future. Recommended method: Class. forName ("com. mysql. jdbc. driver "); // when loading a Driver, it is not the Driver class of the database, but the string of the Driver Class Name of the database. The driver class name is irregular here. You only need to view the driver documentation. Using this method will not cause the drive object to repeat in the memory, and using this method, the program only needs a string and does not need to depend on a specific driver, so that the program is more flexible. DriverManager: a service class used to manage JDBC drivers. This class is used in the program to obtain the Connection object. DriverManager. getConnection (url, user, password) is used to obtain the connection URL of the database corresponding to the URL to identify the database location. The programmer uses the URL address to tell the JDBC program which database to connect to. The URL is written as follows: jdbc: mysql: /// localhost: 3306/test? Key = value different database URL writing methods exist. If you want to know the url format of a specific database, you can refer to the database driver documentation. Common attributes: useUnicode = true & characterEncoding = Connection in the UTF-8 Jdbc program, which is used to represent the Connection of the database, Connection is the most important object in database programming, the client interacts with the database through the connection object. The common method of this object is createStatement (): Create a statement object prepareStatement (SQL) that sends SQL statements to the database ): create a PrepareSatement object prepareCall (SQL) that sends a pre-compiled SQL statement to the database: Create a callableStatement object that executes the stored procedure. --- Stored Procedure setAutoCommit (boolean autoCommit): sets whether a transaction is automatically committed. Commit (): Submit the transaction on the link. --- Related to transactions !! Rollback (): rolls back the transaction on this link. // Note that we use the JDBC interface specification. Although we load the corresponding database driver implementation package in the project, we do not need to introduce import com. mysql. jdbc. connection; because, although this does not affect the program, it relied on the driver package in the past. For JDBC programming, you can directly refer to the JDKAPI documentation.

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCDemo {    public static void main(String[] args) throws SQLException, ClassNotFoundException    {        Class.forName("com.mysql.jdbc.Driver");        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");        Statement stmt = connection.createStatement();        ResultSet rs = stmt.executeQuery("SELECT A_NAME  FROM A");         while (rs.next()) {             String name = rs.getString("A_NAME");             System.out.println("name is:"+ name);         }         rs.close();        stmt.close();        connection.close();    }}   

 

The Statement object in the Jdbc program is used to send SQL statements to the database. The common method of Statement object is executeQuery (String SQL): used to send query statements to Data. ExecuteUpdate (String SQL): used to send an insert, update, or delete statement to a database execute (String SQL): used to send any SQL statement to the database addBatch (String SQL ): put Multiple SQL statements in one batch. ExecuteBatch (): sends a batch of SQL statements to the database for execution. The ResultSet In the Jdbc program is used to represent the execution result of the SQL statement. When the Resultset encapsulates the execution result, it adopts a table-like approach. The ResultSet object maintains a cursor pointing to the table data row. Initially, the cursor calls ResultSet before the first row. the next () method can point the cursor to a specific data row, and then call the method to obtain the data of this row. Since the ResultSet is used to encapsulate the execution results, most of the methods provided by this object are used to obtain data get Methods: get any type of data getObject (int index) getObject (string columnName) obtain data of the specified type, for example, getString (int index) getString (String columnName). Question: The type of the column in the database is varchar. How can I obtain the data of this column? What about Int type? What about the bigInt type? Boolean Type? By default, the ResultSet can only be traversed (next (). For ResultSet, it can be set to scroll. You can traverse up or locate a specified physical row number. q: How to Get a rolling result set?

Statement st=con.createStatement();ResultSet rs=st.executeQuery(sql);

 

This is a default result set: it can only be executed downward and can only be iterated once.

 Statement stmt = con.createStatement(                                      ResultSet.TYPE_SCROLL_INSENSITIVE,                                      ResultSet.CONCUR_UPDATABLE);ResultSet rs = stmt.executeQuery(sql);

 

This allows you to create a rolling result set. Simply put, when creating a Statement object, createStatement () is not used, but createStatement (int, int) with parameters is used)

Statement createStatement(int resultSetType,                          int resultSetConcurrency)                          throws SQLException

ResultSetType-result set type, which is ResultSet. TYPE_FORWARD_ONLY, ResultSet. TYPE_SCROLL_INSENSITIVE or ResultSet. one of TYPE_SCROLL_SENSITIVE resultSetConcurrency-concurrency type; it is ResultSet. CONCUR_READ_ONLY or ResultSet. the first parameter value of CONCUR_UPDATABLE is ResultSet. TYPE_FORWARD_ONLY indicates the type of the ResultSet object that the cursor can only move forward. ResultSet. TYPE_SCROLL_INSENSITIVE this constant indicates the type of the ResultSet object that can be rolled but is generally not affected by the underlying data changes of the ResultSet. ResultSet. TYPE_SCROLL_SENSITIVE this constant indicates the type of the ResultSet object that can be rolled and is usually affected by changes to the underlying data of the ResultSet. The second parameter value ResultSet. CONCUR_READ_ONLY indicates the concurrent mode of the ResultSet object that cannot be updated. ResultSet. CONCUR_UPDATABLE this constant indicates the concurrent mode of the ResultSet object that can be updated. The above five values can be matched in three methods: ResultSet. TYPE_FORWARD_ONLY ResultSet. CONCUR_READ_ONLY default ResultSet. TYPE_SCROLL_INSENSITIVE ResultSet. CONCUR_READ_ONLY ResultSet. TYPE_SCROLL_SENSITIVE ResultSet. CONCUR_UPDATABLE common API next (): Move to the next row previous (): Move to the first row absolute (int row): Move to the specified row beforeFirst (): Move the first afterLast () of the resultSet (): updateRow (): Updates row data. The resultSet also provides the method Statement stmt = conn to scroll and update the result set. createStatement (ResultSet. TYPE_SCROLL_SENSITIVE, ResultSet. CONCUR_UPDATABLE); next (): Move to the next row previous (): Move to the first row absolute (int row): Move to the specified row beforeFirst (): Move the first afterLast () of the resultSet (): move to the last updateRow () of the resultSet: After the Jdbc program is run, remember to release the objects created during the running process to interact with the database, these objects are generally ResultSet, Statement, and Connection objects. Especially the Connection object, which is a very rare resource, must be released immediately after use. If the Connection cannot be closed in a timely and correct manner, it can easily lead to system downtime. The usage principle of Connection is to create a Connection as late as possible and release it as early as possible. To ensure that the resource release code can run, the resource release code must also be placed in the finally statement. PreparedStatement is a subinterface of Statement. Its instance object can be obtained by calling the Connection. preparedStatement (SQL) method. Compared with the Statement object, PreperedStatement can avoid SQL injection. Statement can frequently compile SQL statements in the database and cause database Buffer Overflow. PreparedStatement can pre-compile SQL statements to improve the execution efficiency of the database. In addition, PreperedStatement allows replacement of parameters in SQL in the form of placeholders to simplify the preparation of SQL statements.