A collection of JDBC common face questions

what is JDBC and when will it be used?

The full name of JDBC is the Java database Connection, the Java DB connection, which we can use to manipulate relational databases. JDBC interfaces and related classes are in java.sql and javax.sql packages. We can use it to connect to the database, execute SQL queries, stored procedures, and process the returned results.

The JDBC interface enables loose coupling of Java programs and JDBC drivers, making it easier to switch between different databases. What are the different types of JDBC drivers?

There are four types of JDBC drivers. The Java program that interacts with the database is divided into two parts, part of the JDBC API, and the actual drive is another part.

A JDBC-ODBC Bridge plus ODBC Driver (type 1): It uses ODBC drivers to connect to the database. You need to install ODBC to connect to the database, because of this, this approach is now basically obsolete.

B Native API partly Java technology-enabled driver (Type 2): This driver applies jdbc calls to the local interface of the database.

C Pure Java Driver for Database middleware (Type 3): This driver forwards the JDBC call to the middleware server, where it connects to different databases. Using this type of drive requires the deployment of a middleware server. This approach adds additional network calls, resulting in poor performance and therefore is rarely used.

D direct-to-database Pure Java Driver (Type 4): This driver transforms JDBC into a network protocol used by the database. This scheme is the simplest and suitable for connecting databases over a network. In this way, however, specific drivers need to be selected based on different databases, such as OJDBC, which is the driver of Oracle's Oracle database, and MySQL connector/j is a MySQL database driver. JDBC is how the Java program is implemented and the JDBC-driven loosely coupled.

The JDBC API uses the Java reflection mechanism to implement loosely coupled Java programs and JDBC drivers. Looking at a simple JDBC example, you will find that all operations are done through the JDBC interface, and the drive will only appear when loaded through the class.forname reflection mechanism.

I think this is one of the best practices for reflection mechanisms in the Java Core Library, which insulates applications and drivers, making it easier to migrate databases. Here you can see more examples of JDBC usage. What is a JDBC connection, and how to create a JDBC connection in Java.

A JDBC connection is a session established with the database server. You can imagine a connection to a socket with a database.

Creating a JDBC connection is simple and requires only two steps:

A. Register and load the driver: Using Class.forName (), the driver class is registered into the DriverManager and loaded into memory. B. Get the Connection object with DriverManager: The connection object can be obtained by invoking the Drivermanager.getconnnection () method and passing in the URL, username and password of the database connection.

1 Connection con = null;
 2 try{
 3     //load the Driver Class
 4     class.forname ("Com.mysql.jdbc.Driver");
 5     //Create the connection now
 6     con = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/dbname",
 7                     "username",
 8                     "password");
 9     }catch (SQLException e) {             System.out.println ("Check database is up and configs are correct");
One             e.printstacktrace ();     }catch (classnotfoundexception e) {             System.out.println ("Please include JDBC MySQL jar in Classpath ");             e.printstacktrace ();     }
16}

The DriverManager of JDBC is used to do something.

JDBC's DriverManager is a factory class that we use to create a database connection. When JDBC's driver class is loaded in, it registers itself into the DriverManager class, and you can look at the JDBC driver class source to learn about it.

Then we will pass the database configuration information into the Drivermanager.getconnection () method, DriverManager will use the driver registered to it to get the database connection and return to the calling program. in the Java program, how to obtain information about the database server.

Use DatabaseMetaData to obtain information about the server. After the connection to the database has been successfully established, you can obtain the metadata for the database by calling the GetMetaData () method. DatabaseMetaData There are many methods, through which they can get the name of the database, version number, configuration information and so on.

DatabaseMetaData metaData = Con.getmetadata ();
String dbproduct = Metadata.getdatabaseproductname ();

What is the statement of JDBC?

Statement is the interface used in JDBC to execute database SQL query statements. We can generate a statement object by calling the Getstatement () method of the Connection object. We can execute a static SQL query by invoking its execute (), ExecuteQuery (), Executeupdate () method.

Because the SQL statement is passed in in the program, if there is no validation of user input that can cause problems with SQL injection, if you want to know more about SQL injection, you can look at this here.

By default, a statement can only open one resultset at a time. If you want to manipulate multiple ResultSet objects, you need to create multiple statement. All execute methods of the statement interface start executing by default, and the currently open resultset is closed. What is the difference between the execute,executequery,executeupdate.

The statement execute (String query) method executes any SQL query, and returns true if the result of the query is a resultset. If the result is not resultset, such as INSERT or update query, it returns FALSE. We can get the resultset by its Getresultset method or by Getupdatecount () method to get the updated number of record bars.

The statement executequery (String query) interface is used to execute a select query and return resultset. ResultSet is not null even if the query does not return a record. We usually use ExecuteQuery to execute the query, so that if the INSERT or UPDATE statement is passed in, it throws the error message "ExecuteQuery method can is not is used for update" of Java.util.SQLException.

The statement executeupdate (String query) method is used to execute insert or update/delete (DML) statements, or to return no DDL statements. The return value is of type int, and if it is a DML statement, it is the number of updated bars and, if it is DDL, returns 0.

You should use the Execute () method only if you are unsure of the statement, or you should use the ExecuteQuery or Executeupdate method. What is the PreparedStatement of JDBC?

The PreparedStatement object represents a precompiled SQL statement. The setter method provided with it can be passed into the query's variable.

Because PreparedStatement is precompiled, the corresponding SQL statement can be executed multiple times efficiently. Since PreparedStatement automatically escape the special word, it avoids SQL injection attacks and should be used as much as possible. How to inject null values in PreparedStatement.

You can use its SetNull method to bind a null value to a specified variable. The SetNull method requires an index of incoming parameters and the type of SQL field, like this:

Ps.setnull (Java.sql.Types.INTEGER);

What is the use of the Getgeneratedkeys method in statement.

Sometimes a table generates a primary key, at which point the statement Getgeneratedkeys () method can be used to get the value of the Auto-generated primary key. What is the advantage relative to statement,preparedstatement.

Its advantage over statement is that PreparedStatement helps prevent SQL injection because it automatically escapes special characters. PreparedStatement can be used for dynamic queries. PreparedStatement execute faster. Especially if you reuse it or execute multiple statements using its spelling query interface. Using the PreparedStatement setter method is easier to write object-oriented code, and statement, we have to stitch strings to generate query statements. If there are too many arguments, string concatenation can look ugly and error prone. What is the disadvantage of PreparedStatement, how to solve this problem.

One drawback of PreparedStatement is that we can't use it directly to execute in-conditional statements, and there are some solutions to the in-condition statement: Separate queries-this performance is poor and not recommended. Using stored procedures-depending on the implementation of the database, not all databases are supported. Dynamically generating preparedstatement--This is a good idea, but you can't enjoy the benefits of PreparedStatement caching. Use null values in PreparedStatement queries-if you know the maximum number of input variables, this is a good way to extend and support infinite parameters.

A more detailed analysis of this issue can be seen in this article. What is the resultset of JDBC?

After querying the database, it returns a ResultSet, which is like a datasheet for the query result set.

The ResultSet object maintains a cursor that points to the current data row. At the beginning, this cursor is pointing to the first row. If the next () method cursor that called ResultSet is moved down one line, the next () method returns False if there is no more data. You can use it in a for loop to traverse a dataset.

The default resultset is not updatable and the cursor can only be moved down. That means you can only traverse it from the first line to the last line. However, you can also create resultset that can be rolled back or updatable, as follows.

Statement stmt = con.createstatement (resultset.type_scroll_insensitive, resultset.concur_updatable);

The ResultSet object also closes automatically when the statement object that generates the resultset is closed or rerun, or the next resultset is fetched.

You can get the column data by resultset The getter method, passing in the column name or the ordinal number starting at 1. what are the different resultset?

Depending on the input parameters when the statement is created, the different types of resultset are corresponding to each other. If you look at the connection method, you will find that the createstatement and Preparestatement methods are overloaded to support different resultset and concurrency types.

There are three kinds of resultset objects. Resultset.type_forward_only: This is the default type, and its cursor can only be moved down. Resultset.type_scroll_insensitive: The cursor can move up and down, and once it is created, the data in the database is changed again, which is transparent to it. Resultset.type_scroll_sensitive: Cursors can be moved up and down, and if the database has been modified after the build, it can be perceived.

There are two concurrent types of resultset. ResultSet.CONCUR_READ_ONLY:ResultSet is read-only, which is the default type. Resultset.concur_updatable: We can update the data using the ResultSet Update method. What is the use of the Setfetchsize and setMaxRows methods in statement.

setMaxRows can be used to limit the number of rows in the returned dataset. Of course, this function can also be implemented through SQL statements. For example, in MySQL we can use the limit condition to set the maximum number of rows to return the result.

Setfetchsize It's a little hard to understand because you have to know how statement and resultset work. When the database executes a query statement, the data queried is maintained in the database's cache. ResultSet actually refers to the cached results in the database.

Suppose we have a query that returns 100 rows of data, and we set the Fetchsize to 10, then the database driver takes only 10 data at a time, which means 10 times. This optional parameter becomes very useful when each piece of data needs to be processed for a longer period of time and returns a lot of data.

We can set the Fetchsize parameter by statement, but it is overwritten by the value that the ResultSet object sets in. How to use the JDBC interface to invoke a stored procedure.

A stored procedure is a set of SQL statements compiled by a database that can be invoked through the JDBC interface. We can execute stored procedures in the database through the JDBC CallableStatement interface. The syntax for initializing callablestatement is this:

1 CallableStatement stmt = Con.preparecall ("{Call Insertemployee (?,?,?,?,?,?)}");
2 Stmt.setint (1, id);
3 stmt.setstring (2, name);
4 stmt.setstring (3, role);
5 stmt.setstring (4, city);
6 stmt.setstring (5, country);
7//register The out parameter before calling stored procedure
8 Stmt.registeroutparameter (6, Java.sql.Types.VARC HAR);
9 stmt.executeupdate ();