jdbc面试_JDBC面试问答

ゝ一纸荒年。 2023-02-28 06:00 2阅读 0赞

jdbc面试

Welcome to JDBC Interview Questions and Answers. JDBC API is used to connect to relational databases and run SQL queries from Java Programs. In the last few articles, we learned about JDBC API and its important features. This article is aimed to provide some of the important JDBC Interview Questions with Answers to help you in Java interview.

欢迎来到JDBC面试问答。 JDBC API用于连接到关系数据库并从Java程序运行SQL查询。 在最后几篇文章中，我们了解了JDBC API及其重要功能。 本文旨在提供一些重要的JDBC面试问题和答案，以帮助您进行Java面试。

## JDBC面试问题 **(**JDBC Interview Questions**)** ##

1.  [What is JDBC API and when do we use it?][What is JDBC API and when do we use it]
    
    [什么是JDBC API？何时使用？][What is JDBC API and when do we use it]
2.  [What are different types of JDBC Drivers?][What are different types of JDBC Drivers]
    
    [有哪些不同类型的JDBC驱动程序？][What are different types of JDBC Drivers]
3.  [How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API?][How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API]
    
    [JDBC API如何帮助我们实现Java程序和JDBC Drivers API之间的松散耦合？][How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API]
4.  [What is JDBC Connection? Explain steps to get Database connection in a simple java program.][What is JDBC Connection_ Explain steps to get Database connection in a simple java program.]
    
    [什么是JDBC连接？][What is JDBC Connection_ Explain steps to get Database connection in a simple java program.] [说明在简单的Java程序中获取数据库连接的步骤。][What is JDBC Connection_ Explain steps to get Database connection in a simple java program.]
5.  [What is the use of JDBC DriverManager class?][What is the use of JDBC DriverManager class]
    
    [JDBC DriverManager类的用途是什么？][What is the use of JDBC DriverManager class]
6.  [How to get the Database server details in java program?][How to get the Database server details in java program]
    
    [如何在Java程序中获取数据库服务器详细信息？][How to get the Database server details in java program]
7.  [What is JDBC Statement?][What is JDBC Statement]
    
    [什么是JDBC语句？][What is JDBC Statement]
8.  [What is the difference between execute, executeQuery, executeUpdate?][What is the difference between execute_ executeQuery_ executeUpdate]
    
    [execute，executeQuery，executeUpdate有什么区别？][What is the difference between execute_ executeQuery_ executeUpdate]
9.  [What is JDBC PreparedStatement?][What is JDBC PreparedStatement]
    
    [什么是JDBC PreparedStatement？][What is JDBC PreparedStatement]
10. [How to set NULL values in JDBC PreparedStatement?][How to set NULL values in JDBC PreparedStatement]
    
    [如何在JDBC PreparedStatement中设置NULL值？][How to set NULL values in JDBC PreparedStatement]
11. [What is the use of getGeneratedKeys() method in Statement?][What is the use of getGeneratedKeys_ method in Statement]
    
    [Statement中的getGeneratedKeys（）方法有什么用？][What is the use of getGeneratedKeys_ method in Statement]
12. [What are the benefits of PreparedStatement over Statement?][What are the benefits of PreparedStatement over Statement]
    
    [与语句相比，PreparedStatement有什么好处？][What are the benefits of PreparedStatement over Statement]
13. [What is the limitation of PreparedStatement and how to overcome it?][What is the limitation of PreparedStatement and how to overcome it]
    
    [PreparedStatement的局限性是什么？如何克服它？][What is the limitation of PreparedStatement and how to overcome it]
14. [What is JDBC ResultSet?][What is JDBC ResultSet]
    
    [什么是JDBC ResultSet？][What is JDBC ResultSet]
15. [What are different types of ResultSet?][What are different types of ResultSet]
    
    [有哪些不同类型的ResultSet？][What are different types of ResultSet]
16. [What is the use of setFetchSize() and setMaxRows() methods in Statement?][What is the use of setFetchSize_ and setMaxRows_ methods in Statement]
    
    [Statement中的setFetchSize（）和setMaxRows（）方法有什么用？][What is the use of setFetchSize_ and setMaxRows_ methods in Statement]
17. [How to use JDBC API to call Stored Procedures?][How to use JDBC API to call Stored Procedures]
    
    [如何使用JDBC API调用存储过程？][How to use JDBC API to call Stored Procedures]
18. [What is JDBC Batch Processing and what are it’s benefits?][What is JDBC Batch Processing and what are it_s benefits]
    
    [什么是JDBC批处理，有什么好处？][What is JDBC Batch Processing and what are it_s benefits]
19. [What is JDBC Transaction Management and why do we need it?][What is JDBC Transaction Management and why do we need it]
    
    [什么是JDBC事务管理，为什么我们需要它？][What is JDBC Transaction Management and why do we need it]
20. [How to rollback a JDBC transaction?][How to rollback a JDBC transaction]
    
    [如何回滚JDBC事务？][How to rollback a JDBC transaction]
21. [What is JDBC Savepoint? How to use it?][What is JDBC Savepoint_ How to use it]
    
    [什么是JDBC保存点？][What is JDBC Savepoint_ How to use it] [如何使用它？][What is JDBC Savepoint_ How to use it]
22. [What is JDBC DataSource and what are it’s benefits?][What is JDBC DataSource and what are it_s benefits]
    
    [什么是JDBC DataSource，它有什么好处？][What is JDBC DataSource and what are it_s benefits]
23. [How to achieve JDBC Connection Pooling using JDBC DataSource and JNDI in Apache Tomcat Server?][How to achieve JDBC Connection Pooling using JDBC DataSource and JNDI in Apache Tomcat Server]
    
    [如何在Apache Tomcat服务器中使用JDBC数据源和JNDI实现JDBC连接池？][How to achieve JDBC Connection Pooling using JDBC DataSource and JNDI in Apache Tomcat Server]
24. [What is Apache DBCP API?][What is Apache DBCP API]
    
    [什么是Apache DBCP API？][What is Apache DBCP API]
25. [What is JDBC Connection isolation levels?][What is JDBC Connection isolation levels]
    
    [什么是JDBC连接隔离级别？][What is JDBC Connection isolation levels]
26. [What is JDBC RowSet? What are different types of RowSet?][What is JDBC RowSet_ What are different types of RowSet]
    
    [什么是JDBC RowSet？][What is JDBC RowSet_ What are different types of RowSet] [有哪些不同类型的RowSet？][What is JDBC RowSet_ What are different types of RowSet]
27. [What is the different between ResultSet and RowSet?][What is the different between ResultSet and RowSet]
    
    [ResultSet和RowSet有什么区别？][What is the different between ResultSet and RowSet]
28. [What are common JDBC Exceptions?][What are common JDBC Exceptions]
    
    [什么是常见的JDBC异常？][What are common JDBC Exceptions]
29. [What is CLOB and BLOB datatypes in JDBC?][What is CLOB and BLOB datatypes in JDBC]
    
    [JDBC中的CLOB和BLOB数据类型是什么？][What is CLOB and BLOB datatypes in JDBC]
30. [What is “dirty read” in JDBC? Which isolation level prevents dirty read?][What is _dirty read_ in JDBC_ Which isolation level prevents dirty read]
    
    [在JDBC中什么是“脏读”？][What is _dirty read_ in JDBC_ Which isolation level prevents dirty read] [哪个隔离级别可以防止脏读？][What is _dirty read_ in JDBC_ Which isolation level prevents dirty read]
31. [What is 2 phase commit?][What is 2 phase commit]
    
    [什么是两阶段提交？][What is 2 phase commit]
32. [What are the different types of locking in JDBC?][What are the different types of locking in JDBC]
    
    [JDBC中有哪些不同类型的锁定？][What are the different types of locking in JDBC]
33. [What do you understand by DDL and DML statements?][What do you understand by DDL and DML statements]
    
    [您对DDL和DML语句了解什么？][What do you understand by DDL and DML statements]
34. [What is difference between java.util.Date and java.sql.Date?][What is difference between java.util.Date and java.sql.Date]
    
    [java.util.Date和java.sql.Date有什么区别？][What is difference between java.util.Date and java.sql.Date]
35. [How to insert an image or raw data into database?][How to insert an image or raw data into database]
    
    [如何将图像或原始数据插入数据库？][How to insert an image or raw data into database]
36. [What is phantom read and which isolation level prevents it?][What is phantom read and which isolation level prevents it]
    
    [什么是幻像读取，哪个隔离级别可以阻止它？][What is phantom read and which isolation level prevents it]
37. [What is SQL Warning? How to retrieve SQL warnings in the JDBC program?][What is SQL Warning_ How to retrieve SQL warnings in the JDBC program]
    
    [什么是SQL警告？][What is SQL Warning_ How to retrieve SQL warnings in the JDBC program] [如何在JDBC程序中检索SQL警告？][What is SQL Warning_ How to retrieve SQL warnings in the JDBC program]
38. [How to invoke Oracle Stored Procedure with Database Objects as IN/OUT?][How to invoke Oracle Stored Procedure with Database Objects as IN_OUT]
    
    [如何使用数据库对象作为IN / OUT调用Oracle存储过程？][How to invoke Oracle Stored Procedure with Database Objects as IN_OUT]
39. [When do we get java.sql.SQLException: No suitable driver found?][When do we get java.sql.SQLException_ No suitable driver found]
    
    [什么时候获得java.sql.SQLException：没有找到合适的驱动程序？][When do we get java.sql.SQLException_ No suitable driver found]
40. [What are JDBC Best Practices?][What are JDBC Best Practices]
    
    [什么是JDBC最佳实践？][What are JDBC Best Practices]

## JDBC面试问答 **(**JDBC Interview Questions and Answers**)** ##

1.   
2.  ### 什么是JDBC API？何时使用？ **(**What is JDBC API and when do we use it?**)** ###
    
    **Java DataBase Connectivity** API allows us to work with relational databases. JDBC API interfaces and classes are part of `java.sql` and `javax.sql` package. We can use JDBC API to get the database connection, run SQL queries and stored procedures in the database server and process the results.
    
    JDBC API is written in a way to allow loose coupling between our Java program and actual JDBC drivers that make our life easier in switching from one database to another database servers easily.
    
    **Java DataBase Connectivity** API允许我们使用关系数据库。 JDBC API接口和类是`java.sql`和`javax.sql`包的一部分。 我们可以使用JDBC API获取数据库连接，在数据库服务器中运行SQL查询和存储过程并处理结果。
    
    JDBC API的编写方式允许Java程序与实际JDBC驱动程序之间的松散耦合，使我们从一个数据库轻松切换到另一个数据库服务器的工作变得更加轻松。
3.  ### 有哪些不同类型的JDBC驱动程序？ **(**What are different types of JDBC Drivers?**)** ###
    
    There are four types of JDBC drivers. Any java program that works with database has two parts, first part is the JDBC API and second part is the driver that does the actual work.
    
    1.  **JDBC-ODBC Bridge plus ODBC Driver (Type 1)**: It uses ODBC driver to connect to database. We should have ODBC drivers installed to connect to database, that’s why this driver is almost obsolete.
    2.  **Native API partly Java technology-enabled driver (Type 2)**: This driver converts JDBC class to the client API for the database servers. We should have database client API installed. Because of extra dependency on database client API drivers, this is also not preferred driver.
    3.  **Pure Java Driver for Database Middleware (Type 3)**: This driver sends the JDBC calls to a middleware server that can connect to different type of databases. We should have a middleware server installed to work with this driver. This adds to extra network calls and slow performance and thats why not widely used JDBC driver.
    4.  **Direct-to-Database Pure Java Driver (Type 4)**: This driver converts the JDBC calls to the network protocol understood by the database server. This solution is simple and suitable for database connectivity over the network. However for this solution, we should use database specific drivers, for example OJDBC jars by Oracle for Oracle DB and MySQL Connector/J for MySQL databases.
    
    JDBC驱动程序有四种类型。 任何与数据库一起使用的Java程序都有两部分，第一部分是JDBC API，第二部分是完成实际工作的驱动程序。
    
    1.  **JDBC-ODBC Bridge加上ODBC驱动程序（类型1）** ：它使用ODBC驱动程序连接到数据库。 我们应该已经安装了ODBC驱动程序以连接到数据库，这就是为什么该驱动程序已过时的原因。
    2.  **本机API部分支持Java技术的驱动程序（类型2）** ：该驱动程序将JDBC类转换为数据库服务器的客户端API。 我们应该安装数据库客户端API。 由于额外依赖数据库客户端API驱动程序，因此也不推荐使用该驱动程序。
    3.  **用于数据库中间件的纯Java驱动程序（类型3）** ：该驱动程序将JDBC调用发送到可以连接到不同类型数据库的中间件服务器。 我们应该安装一个中间件服务器来使用该驱动程序。 这增加了额外的网络调用并降低了性能，这就是为什么未广泛使用JDBC驱动程序的原因。
    4.  **直接数据库纯Java驱动程序（类型4）** ：该驱动程序将JDBC调用转换为数据库服务器可以理解的网络协议。 该解决方案很简单，适用于网络上的数据库连接。 但是，对于此解决方案，我们应使用数据库特定的驱动程序，例如Oracle针对Oracle DB的OJDBC jars和MySQL数据库MySQL Connector / J。
4.  ### JDBC API如何帮助我们实现Java程序和JDBC Drivers API之间的松散耦合？ **(**How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API?**)** ###
    
    JDBC API uses [Java Reflection API][] to achieve loose coupling between java programs and JDBC Drivers. If you look at a simple JDBC example, you will notice that all the programming is done in terms of JDBC API and Driver comes in picture only when it’s loaded through reflection using `Class.forName()` method.
    
    I think this is one of the best examples of using [Reflection][Java Reflection API] in core java classes to make sure that our application doesn’t work directly with Drivers API and that makes it very easy to move from one database to another. Please read more at [JDBC Example][].
    
    JDBC API使用[Java Reflection API][]来实现Java程序和JDBC驱动程序之间的松散耦合。 如果看一个简单的JDBC示例，您会注意到所有编程都是根据JDBC API完成的，并且只有当使用`Class.forName()`方法通过反射加载驱动程序时，驱动程序才会出现。
    
    我认为这是在核心Java类中使用[Reflection][Java Reflection API]的最佳示例之一，以确保我们的应用程序不能直接与Drivers API一起使用，这使得从一个数据库迁移到另一个数据库非常容易。 请在[JDBC Example中][JDBC Example]阅读更多内容。
5.  
6.  ### 什么是JDBC连接？ 说明在简单的Java程序中获取数据库连接的步骤。 **(**What is JDBC Connection? Explain steps to get Database connection in a simple java program.**)** ###
    
    JDBC Connection is like a Session created with the database server. You can also think Connection is like a [Socket connection][] from the database server.
    
    Creating a JDBC Connection is very easy and requires two steps:
    
    1.  Register and Load the Driver: Using `Class.forName()`, Driver class is registered to the DriverManager and loaded in the memory.
    2.  Use DriverManager to get the Connection object: We get connection object from `DriverManager.getConnection()` by passing Database URL String, username and password as argument.
    
        Connection con = null;
        try{
        	// load the Driver Class
        	Class.forName("com.mysql.jdbc.Driver");
        
        	// create the connection now
        	con = DriverManager.getConnection("jdbc:mysql://localhost:3306/UserDB",
        					"pankaj",
        					"pankaj123");
        	}catch (SQLException e) {
        			System.out.println("Check database is UP and configs are correct");
        			e.printStackTrace();
        	}catch (ClassNotFoundException e) {
        			System.out.println("Please include JDBC MySQL jar in classpath");
        			e.printStackTrace();
        	}
    
    JDBC连接就像使用数据库服务器创建的会话。 您还可以认为Connection就像来自数据库服务器的[Socket连接][Socket connection] 。
    
    创建JDBC连接非常简单，需要两个步骤：
    
    1.  注册并加载驱动程序：使用`Class.forName()` ，将驱动程序类注册到DriverManager并加载到内存中。
    2.  使用DriverManager来获取Connection对象：我们通过传递数据库URL字符串，用户名和密码作为参数，从`DriverManager.getConnection()`获得连接对象。
7.  ### JDBC DriverManager类的用途是什么？ **(**What is the use of JDBC DriverManager class?**)** ###
    
    JDBC `DriverManager` is the factory class through which we get the Database Connection object. When we load the JDBC Driver class, it registers itself to the DriverManager, you can look up the JDBC Driver classes source code to check this.
    
    Then when we call `DriverManager.getConnection()` method by passing the database configuration details, DriverManager uses the registered drivers to get the Connection and return it to the caller program.
    
    JDBC `DriverManager`是工厂类，通过它可以获取数据库连接对象。 当我们加载JDBC Driver类时，它将自身注册到DriverManager中，您可以查找JDBC Driver类的源代码来进行检查。
    
    然后，当我们通过传递数据库配置详细信息来调用`DriverManager.getConnection()`方法时，DriverManager使用已注册的驱动程序来获取Connection并将其返回给调用程序。
8.  ### 如何在Java程序中获取数据库服务器详细信息？ **(**How to get the Database server details in java program?**)** ###
    
    We can use `DatabaseMetaData` object to get the database server details. When the database connection is created successfully, we can get the meta data object by calling `getMetaData()` method. There are so many methods in DatabaseMetaData that we can use to get the database product name, it’s version and configuration details.
    
        DatabaseMetaData metaData = con.getMetaData();
        String dbProduct = metaData.getDatabaseProductName();
    
    我们可以使用`DatabaseMetaData`对象获取数据库服务器的详细信息。 成功创建数据库连接后，我们可以通过调用`getMetaData()`方法获取元数据对象。 DatabaseMetaData中有很多方法可用于获取数据库产品名称，版本和配置详细信息。
9.  ### 什么是JDBC语句？ **(**What is JDBC Statement?**)** ###
    
    JDBC API `Statement` is used to execute SQL queries in the database. We can create the Statement object by calling Connection `createStatement()` method. We can use Statement to execute static SQL queries by passing query through different execute methods such as execute(), executeQuery(), executeUpdate() etc.
    
    Since the query is generated in the java program, if the user input is not properly validated it can lead to SQL injection issue, more details can be found at [SQL Injection Example][].
    
    By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if we want to work with multiple ResultSet objects, then each must have been generated by different Statement objects. All execute() methods in the Statement interface implicitly close a statment’s current ResultSet object if an open one exists.
    
    JDBC API `Statement`用于在数据库中执行SQL查询。 我们可以通过调用Connection `createStatement()`方法来创建Statement对象。 我们可以通过通过不同的execute方法（例如execute（），executeQuery（），executeUpdate（）等）传递查询来使用Statement来执行静态SQL查询。
    
    由于查询是在Java程序中生成的，因此如果用户输入未正确验证，则可能导致SQL注入问题，有关更多详细信息，请参见[SQL注入示例][SQL Injection Example] 。
    
    默认情况下，每个Statement对象只能同时打开一个ResultSet对象。 因此，如果我们要使用多个ResultSet对象，则每个必须由不同的Statement对象生成。 如果存在打开的语句，Statement接口中的所有execute（）方法都会隐式关闭该语句的当前ResultSet对象。
10. ### execute，executeQuery，executeUpdate有什么区别？ **(**What is the difference between execute, executeQuery, executeUpdate?**)** ###
    
    Statement `execute(String query)` is used to execute any SQL query and it returns TRUE if the result is an ResultSet such as running Select queries. The output is FALSE when there is no ResultSet object such as running Insert or Update queries. We can use `getResultSet()` to get the ResultSet and `getUpdateCount()` method to retrieve the update count.
    
    Statement `executeQuery(String query)` is used to execute Select queries and returns the ResultSet.
    
    ResultSet returned is never null even if there are no records matching the query. When executing select queries we should use the executeQuery method so that if someone tries to execute insert/update statement it will throw java.sql.SQLException with message “executeQuery method cannot be used for update”.
    
    Statement executeUpdate(String query) is used to execute Insert/Update/Delete (DML) statements or DDL statements that returns nothing. The output is int and equals the row count for SQL Data Manipulation Language (DML) statements. For DDL statements, the output is 0.
    
    You should use execute() method only when you are not sure about the type of statement else use executeQuery or executeUpdate method.
    
    语句`execute(String query)`用于执行任何SQL查询，如果结果为ResultSet（例如运行Select查询），则返回TRUE。 如果没有诸如运行插入或更新查询之类的ResultSet对象，则输出为FALSE。 我们可以使用`getResultSet()`获取ResultSet和`getUpdateCount()`方法来获取更新计数。
    
    语句`executeQuery(String query)`用于执行Select查询并返回ResultSet。
    
    即使没有与查询匹配的记录，返回的ResultSet也永远不会为null。 在执行选择查询时，我们应该使用executeQuery方法，这样，如果有人尝试执行insert / update语句，它将抛出java.sql.SQLException，并显示消息“ executeQuery方法无法用于更新”。
    
    语句executeUpdate（String query）用于执行不返回任何内容的插入/更新/删除（DML）语句或DDL语句。 输出为int，等于SQL数据操作语言（DML）语句的行数。 对于DDL语句，输出为0。
    
    仅在不确定语句类型时，才应使用execute（）方法，否则应使用executeQuery或executeUpdate方法。
11. ### 什么是JDBC PreparedStatement？ **(**What is JDBC PreparedStatement?**)** ###
    
    JDBC `PreparedStatement` object represents a precompiled SQL statement. We can use it’s setter method to set the variables for the query.
    
    Since PreparedStatement is precompiled, it can then be used to efficiently execute this statement multiple times. PreparedStatement is better choice that Statement because it automatically escapes the special characters and avoid SQL injection attacks.
    
    JDBC `PreparedStatement`对象表示一个预编译SQL语句。 我们可以使用它的setter方法来设置查询的变量。
    
    由于PreparedStatement是预编译的，因此可以用来多次有效地执行此语句。 与Statement相比，PreparedStatement是更好的选择，因为它会自动转义特殊字符并避免SQL注入攻击。
12. ### 如何在JDBC PreparedStatement中设置NULL值？ **(**How to set NULL values in JDBC PreparedStatement?**)** ###
    
    We can use PreparedStatement setNull() method to bind the null variable to a parameter. The setNull method takes index and SQL Types as argument, for example  
    `ps.setNull(10, java.sql.Types.INTEGER);`.
    
    我们可以使用PreparedStatement setNull（）方法将null变量绑定到参数。 例如，setNull方法采用索引和SQL类型作为参数。  
    `ps.setNull(10, java.sql.Types.INTEGER);` 。
13. ### Statement中的getGeneratedKeys（）方法有什么用？ **(**What is the use of getGeneratedKeys() method in Statement?**)** ###
    
    Sometimes a table can have auto generated keys used to insert the unique column value for primary key. We can use Statement `getGeneratedKeys()` method to get the value of this auto generated key.
    
    有时表可以具有自动生成的键，用于插入主键的唯一列值。 我们可以使用Statement `getGeneratedKeys()`方法获取此自动生成的键的值。
14. ### 与语句相比，PreparedStatement有什么好处？ **(**What are the benefits of PreparedStatement over Statement?**)** ###
    
    Some of the benefits of PreparedStatement over Statement are:
    
     *  PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.
     *  PreparedStatement allows us to execute dynamic queries with parameter inputs.
     *  PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries.
     *  PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.
    
    与语句相比，PreparedStatement的一些好处是：
    
     *  PreparedStatement帮助我们防止SQL注入攻击，因为它会自动转义特殊字符。
     *  PreparedStatement允许我们使用参数输入执行动态查询。
     *  PreparedStatement比Statement快。 当我们重用PreparedStatement或将其批处理方法用于执行多个查询时，它会变得更加可见。
     *  PreparedStatement帮助我们使用setter方法编写面向对象的代码，而使用Statement，则必须使用String Concatenation创建查询。 如果要设置多个参数，则使用String串联编写Query看起来非常难看且容易出错。
15. ### PreparedStatement的局限性是什么？如何克服它？ **(**What is the limitation of PreparedStatement and how to overcome it?**)** ###
    
    One of the limitation of PreparedStatement is that we can’t use it directly with IN clause statements. Some of the alternative approaches to use PreparedStatement with IN clause are;
    
    1.  **Execute Single Queries** – very slow performance and not recommended
    2.  **Using Stored Procedure** – Database specific and hence not suitable for multiple database applications.
    3.  **Creating PreparedStatement Query dynamically** – Good approach but looses the benefit of cached PreparedStatement.
    4.  **Using NULL in PreparedStatement Query** – A good approach when you know the maximum number of variables inputs, can be extended to allow unlimited parameters by executing in parts.
    
    A more detailed analysis can be found at [JDBC PreparedStatement IN clause alternatives][].
    
    PreparedStatement的限制之一是我们不能直接将其与IN子句语句一起使用。 与IN子句一起使用PreparedStatement的一些替代方法是：
    
    1.  **执行单查询** –性能非常慢，不建议使用
    2.  **使用存储过程** –特定于数据库，因此不适合多个数据库应用程序。
    3.  **动态创建PreparedStatement查询** –很好的方法，但是却失去了缓存的PreparedStatement的好处。
    4.  **在PreparedStatement查询中使用NULL** –当您知道变量输入的最大数量时，这是一个很好的方法，可以通过部分执行来扩展为允许无限的参数。
    
    在[JDBC PreparedStatement IN子句替代中][JDBC PreparedStatement IN clause alternatives]可以找到更详细的分析。
16. ### 什么是JDBC ResultSet？ **(**What is JDBC ResultSet?**)** ###
    
    JDBC `ResultSet` is like a table of data representing a database result set, which is usually generated by executing a statement that queries the database.
    
    ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. The next() method moves the cursor to the next row. If there are no more rows, next() method returns false and it can be used in a while loop to iterate through the result set.
    
    A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable using below syntax.
    
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                           ResultSet.CONCUR_UPDATABLE);
    
    A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
    
    We can use ResultSet getter method with column name or index number starting from 1 to retrieve the column data.
    
    JDBC `ResultSet`类似于代表数据库结果集的数据表，通常是通过执行查询数据库的语句生成的。
    
    ResultSet对象维护一个游标，该游标指向其当前数据行。 最初，光标位于第一行之前。 next（）方法将光标移动到下一行。 如果没有更多行，则next（）方法将返回false，并且可以在while循环中使用它来迭代结果集。
    
    默认的ResultSet对象是不可更新的，并且具有仅向前移动的光标。 因此，您只能从第一行到最后一行遍历一次。 使用以下语法可以生成可滚动和/或可更新的ResultSet对象。
    
    当关闭，重新执行或用于生成多个结果序列中的下一个结果的Statement对象时，将自动关闭ResultSet对象。
    
    我们可以将ResultSet getter方法与列名或索引号（从1开始）一起使用，以检索列数据。
17. ### 有哪些不同类型的ResultSet？ **(**What are different types of ResultSet?**)** ###
    
    There are different types of ResultSet objects that we can get based on the user input while creating the Statement. If you will look into the Connection methods, you will see that createStatement() and prepareStatement() method are overloaded to provide ResultSet type and concurrency as input argument.
    
    There are three types of ResultSet object.
    
    1.  **ResultSet.TYPE\_FORWARD\_ONLY**: This is the default type and cursor can only move forward in the result set.
    2.  **ResultSet.TYPE\_SCROLL\_INSENSITIVE**: The cursor can move forward and backward, and the result set is not sensitive to changes made by others to the database after the result set was created.
    3.  **ResultSet.TYPE\_SCROLL\_SENSITIVE**: The cursor can move forward and backward, and the result set is sensitive to changes made by others to the database after the result set was created.
    
    Based on the concurrency there are two types of ResultSet object.
    
    1.  **ResultSet.CONCUR\_READ\_ONLY**: The result set is read only, this is the default concurrency type.
    2.  **ResultSet.CONCUR\_UPDATABLE**: We can use ResultSet update method to update the rows data.
    
    创建Statement时，可以根据用户输入获得不同类型的ResultSet对象。 如果您研究Connection方法，将看到createStatement（）和prepareStatement（）方法已重载以提供ResultSet类型和并发性作为输入参数。
    
    有三种类型的ResultSet对象。
    
    1.  **ResultSet.TYPE\_FORWARD\_ONLY** ：这是默认类型，光标只能在结果集中向前移动。
    2.  **ResultSet.TYPE\_SCROLL\_INSENSITIVE** ：光标可以前后移动，并且结果集对其他人对数据库所做的更改不敏感。
    3.  **ResultSet.TYPE\_SCROLL\_SENSITIVE** ：光标可以前后移动，结果集对其他人在创建结果集后对数据库所做的更改敏感。
    
    基于并发性，有两种类型的ResultSet对象。
    
    1.  **ResultSet.CONCUR\_READ\_ONLY** ：结果集是只读的，这是默认的并发类型。
    2.  **ResultSet.CONCUR\_UPDATABLE** ：我们可以使用ResultSet更新方法来更新行数据。
18. ### Statement中的setFetchSize（）和setMaxRows（）方法有什么用？ **(**What is the use of setFetchSize() and setMaxRows() methods in Statement?**)** ###
    
    We can use `setMaxRows(int i)` method to limit the number of rows that the database returns from the query. You can achieve the same thing using the SQL query itself. For example, in MySQL, we can use the [LIMIT][] clause to set the max rows that will be returned by the query.
    
    Understanding **fetchSize** can be tricky, for that you should know how Statement and ResultSet works. When we execute a query in the database, the result is obtained and maintained in the database cache and ResultSet is returned. ResultSet is the cursor that has the reference to the result in the database.
    
    Let’s say we have a query that returns 100 rows and we have set fetchSize to 10, so in every database trip JDBC driver will fetch only 10 rows and hence there will be 10 trips to fetch all the rows. Setting optimal fetchSize is helpful when you need a lot of processing time for each row and number of rows in the result is huge.
    
    We can set fetchSize through Statement object but it can be overridden through ResultSet object setFetchSize() method.
    
    我们可以使用`setMaxRows(int i)`方法来限制数据库从查询返回的行数。 您可以使用SQL查询本身来实现相同的目的。 例如，在MySQL中，我们可以使用[LIMIT][]子句设置查询将返回的最大行数。
    
    了解**fetchSize**可能很棘手，因为您应该知道Statement和ResultSet的工作方式。 当我们在数据库中执行查询时，将获得结果并将其保存在数据库缓存中，并返回ResultSet。 ResultSet是具有对数据库中结果的引用的游标。
    
    假设我们有一个查询，该查询返回100行，并且我们将fetchSize设置为10，因此在每个数据库行程中，JDBC驱动程序将仅获取10行，因此将进行10次行程来获取所有行。 当您需要为每一行花费大量的处理时间并且结果中的行数很大时，设置最佳的fetchSize会很有帮助。
    
    我们可以通过Statement对象设置fetchSize，但是可以通过ResultSet对象的setFetchSize（）方法覆盖它。
19. ### 如何使用JDBC API调用存储过程？ **(**How to use JDBC API to call Stored Procedures?**)** ###
    
    Stored Procedures are group of SQL queries that are compiled in the database and can be executed from JDBC API. JDBC `CallableStatement` can be used to execute stored procedures in the database. The syntax to initialize CallableStatement is;
    
        CallableStatement stmt = con.prepareCall("{call insertEmployee(?,?,?,?,?,?)}");
        stmt.setInt(1, id);
        stmt.setString(2, name);
        stmt.setString(3, role);
        stmt.setString(4, city);
        stmt.setString(5, country);
        
        //register the OUT parameter before calling the stored procedure
        stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
                     
        stmt.executeUpdate();
    
    We need to register the OUT parameters before executing the CallableStatement. More details about this can be found at [JDBC CallableStatement Example][].
    
    存储过程是一组在数据库中编译SQL查询，可以从JDBC API执行。 JDBC `CallableStatement`可以用于执行数据库中的存储过程。 初始化CallableStatement的语法是；
    
    在执行CallableStatement之前，我们需要注册OUT参数。 有关此问题的更多详细信息，请参见[JDBC CallableStatement Example][] 。
20. ### 什么是JDBC批处理，有什么好处？ **(**What is JDBC Batch Processing and what are it’s benefits?**)** ###
    
    Sometimes we need to run bulk queries of a similar kind for a database. For example, loading data from CSV files to relational database tables. As we know that we have the option to use Statement or PreparedStatement to execute queries. Apart from that JDBC API provides Batch Processing feature through which we can execute the bulk of queries in one go for a database.
    
    JDBC API supports batch processing through Statement and PreparedStatement `addBatch()` and `executeBatch()` methods.
    
    Batch Processing is faster than executing one statement at a time because the number of database calls is less. Read more at [JDBC Batch Processing Example][].
    
    有时我们需要对数据库运行类似类型的批量查询。 例如，将数据从CSV文件加载到关系数据库表。 众所周知，我们可以选择使用Statement或PreparedStatement执行查询。 除此之外，JDBC API提供了批处理功能，通过该功能，我们可以一次执行数据库的大量查询。
    
    JDBC API通过Statement和PreparedStatement `addBatch()`和`executeBatch()`方法支持批处理。
    
    批处理比一次执行一个语句要快，因为数据库调用的次数更少。 在[JDBC批处理示例中][JDBC Batch Processing Example]阅读更多内容。
21. ### 什么是JDBC事务管理，为什么我们需要它？ **(**What is JDBC Transaction Management and why do we need it?**)** ###
    
    By default when we create a database connection, it runs in auto-commit mode. It means that whenever we execute a query and it’s completed, the commit is fired automatically. So every SQL query we fire is a transaction and if we are running some DML or DDL queries, the changes are getting saved into the database after every SQL statement finishes.
    
    Sometimes we want a group of SQL queries to be part of a transaction so that we can commit them when all the queries run fine and if we get an exception, we have a choice of rollback all the queries executed as part of the transaction.
    
    JDBC API provide method `setAutoCommit(boolean flag)` through which we can disable the auto commit feature of the connection. We should disable auto commit only when it’s required because the transaction will not be committed unless we call the commit() method on connection. Database servers uses table locks to achieve transaction management and it’s resource intensive process. So we should commit the transaction as soon as we are done with it. Read more with example program at [JDBC Transaction Management Example][].
    
    默认情况下，当我们创建数据库连接时，它以自动提交模式运行。 这意味着无论何时执行查询并完成查询，都会自动触发提交。 因此，我们触发的每个SQL查询都是一个事务，如果我们正在运行一些DML或DDL查询，则在每个SQL语句完成之后，所做的更改都将保存到数据库中。
    
    有时，我们希望一组SQL查询成为事务的一部分，以便我们可以在所有查询正常运行时提交它们，如果遇到异常，我们可以选择回滚作为事务一部分执行的所有查询。
    
    JDBC API提供了方法`setAutoCommit(boolean flag)`通过它可以禁用连接的自动提交功能。 我们应该仅在需要时禁用自动提交，因为除非我们在连接上调用commit（）方法，否则不会提交事务。 数据库服务器使用表锁来实现事务管理及其资源密集型过程。 因此，我们应该在完成交易后立即提交交易。 在[JDBC事务管理示例中][JDBC Transaction Management Example]阅读有关示例程序的更多信息。
22. 
23. ### 如何回滚JDBC事务？ **(**How to rollback a JDBC transaction?**)** ###
    
    We can use Connection object `rollback()` method to rollback the transaction. It will rollback all the changes made by the transaction and release any database locks currently held by this Connection object.
    
    我们可以使用Connection对象`rollback()`方法来回滚事务。 它将回滚事务所做的所有更改，并释放此Connection对象当前持有的所有数据库锁。
24. ### 什么是JDBC保存点？ 如何使用它？ **(**What is JDBC Savepoint? How to use it?**)** ###
    
    Sometimes a transaction can be group of multiple statements and we would like to rollback to a particular point in the transaction. JDBC Savepoint helps us in creating checkpoints in a transaction and we can rollback to that particular checkpoint.
    
    Any savepoint created for a transaction is automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question. Read more at [JDBC Savepoint Example][JDBC Transaction Management Example].
    
    有时一个事务可以是多个语句组成的组，我们想回滚到事务中的特定点。 JDBC Savepoint帮助我们在事务中创建检查点，并且我们可以回滚到该特定检查点。
    
    为事务创建的任何保存点将自动释放，并在提交事务或整个事务回滚时变为无效。 将事务回滚到保存点会自动释放，并使在该保存点之后创建的任何其他保存点无效。 在[JDBC Savepoint Example中][JDBC Transaction Management Example]阅读更多内容。
25. ### 什么是JDBC DataSource，它有什么好处？ **(**What is JDBC DataSource and what are it’s benefits?**)** ###
    
    JDBC DataSource is the interface defined in `javax.sql` package and it is more powerful that DriverManager for database connections. We can use DataSource to create the database connection and Driver implementation classes does the actual work for getting connection. Apart from getting Database connection, DataSource provides some additional features such as:
    
     *  Caching of PreparedStatement for faster processing
     *  Connection timeout settings
     *  Logging features
     *  ResultSet maximum size threshold
     *  Connection Pooling in servlet container using JNDI support
    
    Read more about DataSource at [JDBC DataSource Example][].
    
    JDBC DataSource是在`javax.sql`包中定义的接口，它比DriverManager的数据库连接功能更强大。 我们可以使用DataSource创建数据库连接，而Driver实现类可以完成获取连接的实际工作。 除了获得数据库连接之外，DataSource还提供一些其他功能，例如：
    
     *  缓存PreparedStatement以进行更快的处理
     *  连接超时设置
     *  记录功能
     *  ResultSet最大大小阈值
     *  使用JNDI支持的servlet容器中的连接池
    
    在[JDBC DataSource Example中][JDBC DataSource Example]阅读有关DataSource的更多信息。
26. ### 如何在Apache Tomcat服务器中使用JDBC数据源和JNDI实现JDBC连接池？ **(**How to achieve JDBC Connection Pooling using JDBC DataSource and JNDI in Apache Tomcat Server?**)** ###
    
    For web applications deployed in a servlet container, creating JDBC connection pool is very easy and involve only few steps.
    
    1.  Creating JDBC JNDI resource in the container configuration files, usually server.xml or context.xml. For example
        
        `server.xml`
        
            <Resource name="jdbc/MyDB"
                  global="jdbc/MyDB"
                  auth="Container"
                  type="javax.sql.DataSource"
                  driverClassName="com.mysql.jdbc.Driver"
                  url="jdbc:mysql://localhost:3306/UserDB"
                  username="pankaj"
                  password="pankaj123"
                   
                  maxActive="100"
                  maxIdle="20"
                  minIdle="5"
                  maxWait="10000"/>
        
        `context.xml`
    2.  In web application, using InitialContext to look up the JNDI resource configured in the first step and then get the connection.
        
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/MyLocalDB");
        
        For a complete example, read [Tomcat DataSource JNDI Example][].
    
    对于部署在servlet容器中的Web应用程序，创建JDBC连接池非常容易，并且只需几个步骤。
    
    1.  在容器配置文件（通常是server.xml或context.xml）中创建JDBC JNDI资源。 例如
        
        `server.xml`
        
        `context.xml`
        
            <ResourceLink name="jdbc/MyLocalDB"
                            global="jdbc/MyDB"
                            auth="Container"
                            type="javax.sql.DataSource" />
    2.  在Web应用程序中，使用InitialContext查找第一步中配置的JNDI资源，然后获取连接。
        
        有关完整示例，请阅读[Tomcat DataSource JNDI Example][] 。
27. ### 什么是Apache DBCP API？ **(**What is Apache DBCP API?**)** ###
    
    If you use `DataSource` to get the Database connection, usually the code to get the connection is tightly coupled with the Driver specific DataSource implementation. Also most of the code is boiler-plate code except the choice of the DataSource implementation class.
    
    Apache DBCP helps us in getting rid of these issues by providing DataSource implementation that works as an abstraction layer between our program and different JDBC drivers. Apache DBCP library depends on Commons Pool library, so make sure they both are in the build path.
    
    For a complete example, read [Apache DBCP Example][JDBC DataSource Example].
    
    如果使用`DataSource`获得数据库连接，通常获得连接的代码与特定于驱动程序的DataSource实现紧密结合。 另外，除了选择DataSource实现类外，大多数代码都是样板代码。
    
    Apache DBCP通过提供DataSource实现来帮助我们摆脱这些问题，该实现充当程序和不同JDBC驱动程序之间的抽象层。 Apache DBCP库取决于Commons Pool库，因此请确保它们都在构建路径中。
    
    有关完整示例，请阅读[Apache DBCP示例][JDBC DataSource Example] 。
28. ### 什么是JDBC连接隔离级别？ **(**What is JDBC Connection isolation levels?**)** ###
    
    When we use JDBC Transactions for data integrity, DBMS uses locks to block access by others to the data being accessed by the transaction. DBMS uses locks to prevent Dirty Read, Non-Repeatable Reads and Phantom-Read issue.
    
    JDBC transaction isolation level is used by DBMS to use the locking mechanism, we can get the isolation level information through Connection getTransactionIsolation() method and set it with the setTransactionIsolation() method.
    
    <table> 
     <tbody> 
      <tr> 
       <th>Isolation Level</th> 
       <th>Transaction</th> 
       <th>Dirty Read</th> 
       <th>Non-Repeatable Read</th> 
       <th>Phantom Read</th> 
      </tr> 
      <tr> 
       <td>TRANSACTION_NONE</td> 
       <td>Not Supported</td> 
       <td>Not Applicable</td> 
       <td>Not Applicable</td> 
       <td>Not Applicable</td> 
      </tr> 
      <tr> 
       <td>TRANSACTION_READ_COMMITTED</td> 
       <td>Supported</td> 
       <td>Prevented</td> 
       <td>Allowed</td> 
       <td>Allowed</td> 
      </tr> 
      <tr> 
       <td>TRANSACTION_READ_UNCOMMITTED</td> 
       <td>Supported</td> 
       <td>Allowed</td> 
       <td>Allowed</td> 
       <td>Allowed</td> 
      </tr> 
      <tr> 
       <td>TRANSACTION_REPEATABLE_READ</td> 
       <td>Supported</td> 
       <td>Prevented</td> 
       <td>Prevented</td> 
       <td>Allowed</td> 
      </tr> 
      <tr> 
       <td>TRANSACTION_SERIALIZABLE</td> 
       <td>Supported</td> 
       <td>Prevented</td> 
       <td>Prevented</td> 
       <td>Prevented</td> 
      </tr> 
     </tbody> 
    </table>
    
    当我们使用JDBC Transactions进行数据完整性时，DBMS使用锁来阻止其他人访问该事务正在访问的数据。 DBMS使用锁来防止脏读，不可重复读和幻像读取问题。
    
    DBMS使用JDBC事务隔离级别来使用锁定机制，我们可以通过Connection getTransactionIsolation（）方法获取隔离级别信息，并使用setTransactionIsolation（）方法进行设置。
    
    <table> 
     <tbody> 
      <tr> 
       <th> 隔离度 </th> 
       <th> 交易 </th> 
       <th> 脏读 </th> 
       <th> 不可重复读 </th> 
       <th> 幻影阅读 </th> 
      </tr> 
      <tr> 
       <td> TRANSACTION_NONE </td> 
       <td> 不支持 </td> 
       <td> 不适用 </td> 
       <td> 不适用 </td> 
       <td> 不适用 </td> 
      </tr> 
      <tr> 
       <td> TRANSACTION_READ_COMMITTED </td> 
       <td> 支持的 </td> 
       <td> 预防的 </td> 
       <td> 允许的 </td> 
       <td> 允许的 </td> 
      </tr> 
      <tr> 
       <td> TRANSACTION_READ_UNCOMMITTED </td> 
       <td> 支持的 </td> 
       <td> 允许的 </td> 
       <td> 允许的 </td> 
       <td> 允许的 </td> 
      </tr> 
      <tr> 
       <td> TRANSACTION_REPEATABLE_READ </td> 
       <td> 支持的 </td> 
       <td> 预防的 </td> 
       <td> 预防的 </td> 
       <td> 允许的 </td> 
      </tr> 
      <tr> 
       <td> TRANSACTION_SERIALIZABLE </td> 
       <td> 支持的 </td> 
       <td> 预防的 </td> 
       <td> 预防的 </td> 
       <td> 预防的 </td> 
      </tr> 
     </tbody> 
    </table>
29. ### 什么是JDBC RowSet？ 有哪些不同类型的RowSet？ **(**What is JDBC RowSet? What are different types of RowSet?**)** ###
    
    JDBC `RowSet` holds tabular data in more flexible ways that ResultSet. All RowSet objects are derived from ResultSet, so they have all the capabilities of ResultSet with some additional features. RowSet interface is defined in `javax.sql` package.
    
    Some additional features provided by RowSet are:
    
     *  Functions as Java Beans with properties and their getter-setter methods. RowSet uses JavaBeans event model and they can send notifications to any registered component for events such as cursor movement, update/insert/delete of a row and change to RowSet contents.
     *  RowSet objects are scrollable and updatable by default, so if DBMS doesn’t support scrollable or updatable ResultSet, we can use RowSet to get these features.
    
    RowSet are broadly divided into two types:
    
    1.  **Connected RowSet Objects** – These objects are connected to database and are most similar to ResultSet object. JDBC API provides only one connected RowSet object `javax.sql.rowset.JdbcRowSet` and it’s standard implementation class is `com.sun.rowset.JdbcRowSetImpl`
    2.  **Disconnected RowSet Objects** – These RowSet objects are not required to connected to a database, so they are more lightweight and serializable. They are suitable for sending data over a network. There are four types of disconnected RowSet implementations.
        
         *  CachedRowSet – They can get the connection and execute a query and read the ResultSet data to populate the RowSet data. We can manipulate and update data while it is disconnected and reconnect to database and write the changes.
         *  WebRowSet derived from CachedRowSet – They can read and write XML document.
         *  JoinRowSet derived from WebRowSet – They can form SQL JOIN without having to connect to a data source.
         *  FilteredRowSet derived from WebRowSet – We can apply filtering criteria so that only selected data is visible.
    
    JDBC `RowSet`以比ResultSet更灵活的方式保存表格数据。 所有RowSet对象都是从ResultSet派生的，因此它们具有ResultSet的所有功能以及某些附加功能。 RowSet接口在`javax.sql`包中定义。
    
    RowSet提供的一些其他功能包括：
    
     *  用作具有属性及其getter-setter方法的Java Bean。 RowSet使用JavaBeans事件模型，它们可以将通知发送到任何已注册组件，以通知事件，例如光标移动，更新/插入/删除行以及更改RowSet内容。
     *  默认情况下，RowSet对象是可滚动和可更新的，因此，如果DBMS不支持可滚动或可更新的ResultSet，则可以使用RowSet获得这些功能。
    
    RowSet大致分为两种类型：
    
    1.  **Connected RowSet对象** –这些对象已连接到数据库，并且与ResultSet对象最相似。 JDBC API仅提供一个连接的RowSet对象`javax.sql.rowset.JdbcRowSet` ，它的标准实现类是`com.sun.rowset.JdbcRowSetImpl`
    2.  **断开连接的RowSet对象** –连接到数据库不需要这些RowSet对象，因此它们更轻便且可序列化。 它们适合通过网络发送数据。 断开连接的RowSet实现有四种类型。
        
         *  CachedRowSet –他们可以获取连接并执行查询并读取ResultSet数据以填充RowSet数据。 当数据断开连接时，我们可以操作和更新数据，然后重新连接到数据库并写入更改。
         *  WebRowSet派生自CachedRowSet –它们可以读取和写入XML文档。
         *  从WebRowSet派生的JoinRowSet –它们可以形成SQL JOIN，而不必连接到数据源。
         *  从WebRowSet派生的FilteredRowSet –我们可以应用过滤条件，以便仅可见选定的数据。
30. ### ResultSet和RowSet有什么区别？ **(**What is the different between ResultSet and RowSet?**)** ###
    
    RowSet objects are derived from ResultSet, so they have all the features of ResultSet with some additional features. One of the huge benefit of RowSet is that they can be disconnected and that makes it lightweight and easy to transfer over a network.
    
    Whether to use ResultSet or RowSet depends on your requirements but if you are planning to use ResultSet for longer duration, then a disconnected RowSet is better choice to free database resources.
    
    RowSet对象是从ResultSet派生的，因此它们具有ResultSet的所有功能以及一些其他功能。 RowSet的巨大好处之一是它们可以断开连接，从而使其轻巧且易于通过网络进行传输。
    
    是否使用ResultSet或RowSet取决于您的要求，但是如果您打算长时间使用ResultSet，那么断开连接的RowSet是释放数据库资源的更好选择。
31. ### 什么是常见的JDBC异常？ **(**What are common JDBC Exceptions?**)** ###
    
    Some of the common JDBC Exceptions are:
    
    1.  java.sql.SQLException – This is the base exception class for JDBC exceptions.
    2.  java.sql.BatchUpdateException – This exception is thrown when Batch operation fails, but it depends on the JDBC driver whether they throw this exception or the base SQLException.
    3.  java.sql.SQLWarning – For warning messages in SQL operations.
    4.  java.sql.DataTruncation – when a data values is unexpectedly truncated for reasons other than its having exceeded MaxFieldSize.
    
    一些常见的JDBC异常是：
    
    1.  java.sql.SQLException-这是JDBC异常的基本异常类。
    2.  java.sql.BatchUpdateException-批处理操作失败时将引发此异常，但是是否依赖于JDBC驱动程序将引发此异常还是引发基本SQLException。
    3.  java.sql.SQLWarning-用于SQL操作中的警告消息。
    4.  java.sql.DataTruncation-当数据值由于超出MaxFieldSize的原因而意外被截断时。
32. ### JDBC中的CLOB和BLOB数据类型是什么？ **(**What is CLOB and BLOB datatypes in JDBC?**)** ###
    
    Character Large OBjects (CLOBs) are character string made up of single-byte characters with an associated code page. This data type is appropriate for storing text-oriented information where the amount of information can grow beyond the limits of a regular VARCHAR data type (upper limit of 32K bytes).
    
    Binary Large Objects (BLOBs) are a binary string made up of bytes with no associated code page. This data type can store binary data larger than VARBINARY (32K limit). This data type is good for storing image, voice, graphical, and other types of business or application-specific data.
    
    字符大对象（CLOB）是由具有相关代码页的单字节字符组成的字符串。 此数据类型适用于存储面向文本的信息，其中信息量可能会超出常规VARCHAR数据类型的限制（上限为32K字节）。
    
    二进制大对象（BLOB）是由字节组成的二进制字符串，没有关联的代码页。 此数据类型可以存储大于VARBINARY（32K限制）的二进制数据。 此数据类型适用于存储图像，语音，图形以及其他类型的业务或应用程序特定数据。
33. ### 在JDBC中什么是“脏读”？ 哪个隔离级别可以防止脏读？ **(**What is “dirty read” in JDBC? Which isolation level prevents dirty read?**)** ###
    
    When we work with transactions, there is a chance that a row is updated and at the same time, another query can read the updated value. This results in a dirty read because the updated value is not permanent yet, the transaction that has updated the row can rollback to a previous value resulting in invalid data.
    
    Dirty Read is prevented by isolation levels TRANSACTION\_READ\_COMMITTED, TRANSACTION\_REPEATABLE\_READ, and TRANSACTION\_SERIALIZABLE.
    
    当我们处理事务时，有可能更新一行，同时另一个查询可以读取更新后的值。 由于更新的值还不是永久性的，因此会导致读取不干净，更新行的事务可能回滚到先前的值，从而导致数据无效。
    
    通过隔离级别TRANSACTION\_READ\_COMMITTED，TRANSACTION\_REPEATABLE\_READ和TRANSACTION\_SERIALIZABLE防止脏读。
34. ### 什么是两阶段提交？ **(**What is 2 phase commit?**)** ###
    
    When we work in distributed systems where multiple databases are involved, we are required to use 2 phase commit protocol. 2 phase commit protocol is an atomic commitment protocol for distributed systems. In the first phase, the transaction manager sends commit-request to all the transaction resources. If all the transaction resources are OK, the transaction manager commits the transaction changes for all the resources. If any of the transaction resources responds as Abort, then the transaction manager can rollback all the transaction changes.
    
    当我们在涉及多个数据库的分布式系统中工作时，需要使用两阶段提交协议。 2阶段提交协议是用于分布式系统的原子提交协议。 在第一阶段，事务管理器向所有事务资源发送提交请求。 如果所有事务资源均正常，则事务管理器将提交所有资源的事务更改。 如果任何事务资源以“中止”响应，则事务管理器可以回滚所有事务更改。
35. ### JDBC中有哪些不同类型的锁定？ **(**What are the different types of locking in JDBC?**)** ###
    
    On a broad level, there are two types of locking mechanism to prevent data corruption because of more than one user working with the same data.
    
    1.  Optimistic Locking – This locking is achieved with code. An extra column is introduced in the table to keep a count of updates. When you select the row, you read this column too, say ‘version’. Now when you are trying to update/delete the row, you pass this ‘version’ in the where clause. So if there are updates from other threads performed in between, the update will fail. It’s a good way to avoid data corruption but it can be error prone if someone missed updating the ‘version’ in their update statement. The update query looks something like below in this way of locking.
        
            mysql> update emp SET name = 'David', version = 5 WHERE id = 10 and version = 4;
    2.  Pessimistic Locking – Locking the record from the select to read, update and commit phase. This is usually done by database vendor software and triggered by the use of `SELECT FOR UPDATE` query. This way of locking the row can lead to slow performance and deadlock if threads are handling the lock for longer time.
    
    Apart from that some DBMS systems provide locking mechanism to lock single row, table or database.
    
    从广义上讲，有两种类型的锁定机制可以防止由于多个用户使用同一数据而导致数据损坏。
    
    1.  乐观锁–此锁通过代码实现。 表中引入了一个额外的列，以保持更新计数。 选择该行时，您也会阅读此列，说“版本”。 现在，当您尝试更新/删除行时，可以在where子句中传递此“版本”。 因此，如果在这之间执行其他线程的更新，则更新将失败。 这是避免数据损坏的好方法，但如果有人错过了更新其update语句中的“版本”，则可能容易出错。 通过这种锁定方式，更新查询如下所示。
    2.  悲观锁定–从选择读取，更新和提交阶段锁定记录。 这通常由数据库供应商软件完成，并由使用`SELECT FOR UPDATE`查询触发。 如果线程长时间处理锁，这种锁定行的方式可能会导致性能降低和死锁。
    
    除此之外，某些DBMS系统提供了锁定机制来锁定单行，表或数据库。
36. ### 您对DDL和DML语句了解什么？ **(**What do you understand by DDL and DML statements?**)** ###
    
    Data Definition Language (DDL) statements are used to define the database schema. Create, Alter, Drop, Truncate, Rename statements comes under DDL statements and usually they don’t return any result.
    
    Data Manipulation Language (DML) statements are used to manipulate data in the database schema. Select, Insert, Update, Delete, Call etc are example of DML statements.
    
    数据定义语言（DDL）语句用于定义数据库模式。 创建，更改，删除，截断，重命名语句位于DDL语句下，通常它们不返回任何结果。
    
    数据操作语言（DML）语句用于操作数据库模式中的数据。 选择，插入，更新，删除，调用等都是DML语句的示例。
37. ### java.util.Date和java.sql.Date有什么区别？ **(**What is difference between java.util.Date and java.sql.Date?**)** ###
    
    java.util.Date contains information about the date and time whereas java.sql.Date contains information only about the date, it doesn’t have time information. So if you have to keep time information in the database, it is advisable to use Timestamp or DateTime fields.
    
    java.util.Date包含有关日期和时间的信息，而java.sql.Date仅包含有关日期的信息，而没有时间信息。 因此，如果必须将时间信息保留在数据库中，建议使用Timestamp或DateTime字段。
38. ### 如何将图像或原始数据插入数据库？ **(**How to insert an image or raw data into database?**)** ###
    
    We can use BLOB to insert image or raw binary data into database.
    
    我们可以使用BLOB将图像或原始二进制数据插入数据库。
39. ### 什么是幻像读取，哪个隔离级别可以阻止它？ **(**What is phantom read and which isolation level prevents it?**)** ###
    
    A phantom read is the situation where a transaction executes a query multiple times and get different data. Suppose a transaction is executing a query to get data based on a condition and then another transaction inserts a row that matches the condition. Now when same transaction will execute the query again, a new row will be part of the result set. This new row is referred as Phantom Row and this situation is termed as Phantom Read.
    
    Phantom read can be prevented only with TRANSACTION\_SERIALIZABLE isolation level.
    
    幻像读取是事务多次执行查询并获取不同数据的情况。 假设一个事务正在执行查询以根据条件获取数据，然后另一个事务插入与条件匹配的行。 现在，当同一事务将再次执行查询时，新行将成为结果集的一部分。 此新行称为“幻影行”，这种情况称为“幻影读取”。
    
    只有使用TRANSACTION\_SERIALIZABLE隔离级别才能防止幻像读取。
40. ### 什么是SQL警告？ 如何在JDBC程序中检索SQL警告？ **(**What is SQL Warning? How to retrieve SQL warnings in the JDBC program?**)** ###
    
    SQLWarning is the subclass of SQLException and we can retrieve it by calling getWarnings() method on Connection, Statement, and ResultSet objects. SQL Warnings doesn’t stop the execution of the script but alerts the user about the warning.
    
    SQLWarning是SQLException的子类，我们可以通过在Connection，Statement和ResultSet对象上调用getWarnings（）方法来检索它。 SQL警告不会停止脚本的执行，但会向用户警告该警告。
41. ### 如何使用数据库对象作为IN / OUT调用Oracle存储过程？ **(**How to invoke Oracle Stored Procedure with Database Objects as IN/OUT?**)** ###
    
    If Oracle Stored Procedure has IN/OUT parameters as DB Objects then we need to create an Object array of the same size in the program and then use it to create Oracle STRUCT object. Then we can set this STRUCT object for the database object by calling setSTRUCT() method and work with it.
    
    如果Oracle存储过程具有作为数据库对象的IN / OUT参数，则我们需要在程序中创建相同大小的对象数组，然后使用它来创建Oracle STRUCT对象。 然后，我们可以通过调用setSTRUCT（）方法并使用它来为数据库对象设置此STRUCT对象。
42. ### 什么时候获得java.sql.SQLException：没有找到合适的驱动程序？ **(**When do we get java.sql.SQLException: No suitable driver found?**)** ###
    
    You get No suitable driver found exception when the SQL URL String is not properly formatted. You can get this exception in both simple java application using DriverManager or with JNDI resource using DataSource. The exception stack trace looks like below.
    
        org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL ''jdbc:mysql://localhost:3306/UserDB'
        	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1452)
        	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
        	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        
        
        java.sql.SQLException: No suitable driver found for 'jdbc:mysql://localhost:3306/UserDB
        	at java.sql.DriverManager.getConnection(DriverManager.java:604)
        	at java.sql.DriverManager.getConnection(DriverManager.java:221)
        	at com.journaldev.jdbc.DBConnection.getConnection(DBConnection.java:24)
        	at com.journaldev.jdbc.DBConnectionTest.main(DBConnectionTest.java:15)
        Exception in thread "main" java.lang.NullPointerException
        	at com.journaldev.jdbc.DBConnectionTest.main(DBConnectionTest.java:16)
    
    While debugging this exception, just check the URL getting printed in the logs, as in above logs the URL String is ‘jdbc:mysql://localhost:3306/UserDB whereas it should be jdbc:mysql://localhost:3306/UserDB.
    
    SQL URL字符串格式不正确时，会出现“找不到合适的驱动程序”异常。 您可以在使用DriverManager的简单Java应用程序中或通过DataSource的JNDI资源中获得此异常。 异常堆栈跟踪如下所示。
    
    在调试此异常时，只需检查日志中已打印的URL，如上述日志中，URL字符串为'jdbc：mysql：// localhost：3306 / UserDB，而应为jdbc：mysql：// localhost：3306 / UserDB 。
43. ### 什么是JDBC最佳实践？ **(**What are JDBC Best Practices?**)** ###
    
    Some of the JDBC Best Practices are:
    
     *  Database resources are heavy, so make sure you close it as soon as you are done with it. Connection, Statement, ResultSet and all other JDBC objects have close() method defined to close them.
     *  Always close the result set, statement and connection explicitly in the code, because if you are working in connection pooling environment, the connection might be returned to the pool leaving open result sets and statement objects resulting in resource leak.
     *  Close the resources in the finally block to make sure they are closed even in case of exception scenarios.
     *  Use batch processing for bulk operations of similar kind.
     *  Always use PreparedStatement over Statement to avoid SQL Injection and get pre-compilation and caching benefits of PreparedStatement.
     *  If you are retrieving bulk data into result set, setting an optimal value for fetchSize helps in getting good performance.
     *  The database server might not support all isolation levels, so check it before assuming.
     *  More strict isolation levels result in slow performance, so make sure you have optimal isolation level set for your database connections.
     *  If you are creating database connections in a web application, try to use JDBC DataSource resources using JNDI context for re-using the connections.
     *  Try to use disconnected RowSet when you need to work with ResultSet for a long time.
    
    一些JDBC最佳实践是：
    
     *  数据库资源很重，因此请确保在完成操作后立即将其关闭。 Connection，Statement，ResultSet和所有其他JDBC对象都定义了close（）方法来关闭它们。
     *  始终在代码中显式关闭结果集，语句和连接，因为如果您在连接池环境中工作，则连接可能会返回到池中，从而留下开放的结果集和语句对象，从而导致资源泄漏。
     *  关闭finally块中的资源，以确保即使在发生异常情况时也将其关闭。
     *  将批处理用于类似类型的批量操作。
     *  始终在语句上使用PreparedStatement以避免SQL注入，并获得PreparedStatement的预编译和缓存优势。
     *  如果要将大量数据检索到结果集中，则为fetchSize设置最佳值有助于获得良好的性能。
     *  数据库服务器可能不支持所有隔离级别，因此请在进行假设之前进行检查。
     *  严格的隔离级别会导致性能降低，因此请确保为数据库连接设置了最佳的隔离级别。
     *  如果要在Web应用程序中创建数据库连接，请尝试使用具有JNDI上下文的JDBC DataSource资源来重新使用连接。
     *  当需要长时间使用ResultSet时，请尝试使用断开连接的RowSet。

That’s all for JDBC interview questions and answers, I hope it will help you in JDBC interviews. Let me know if I have missed any important question and I will add it to the list.

JDBC访谈的问题和答案就这么多了，希望对您的JDBC访谈有所帮助。 让我知道是否错过任何重要的问题，并将其添加到列表中。

> 翻译自: [https://www.journaldev.com/2529/jdbc-interview-questions-and-answers][https_www.journaldev.com_2529_jdbc-interview-questions-and-answers]

jdbc面试

[What is JDBC API and when do we use it]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#jdbc-overview
[What are different types of JDBC Drivers]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#jdbc-driver-types
[How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#jdbc-reflection
[What is JDBC Connection_ Explain steps to get Database connection in a simple java program.]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#jdbc-connection
[What is the use of JDBC DriverManager class]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#jdbc-DriverManager
[How to get the Database server details in java program]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#jdbc-DatabaseMetaData
[What is JDBC Statement]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Statement
[What is the difference between execute_ executeQuery_ executeUpdate]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#execute-executeQuery-executeUpdate
[What is JDBC PreparedStatement]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#PreparedStatement
[How to set NULL values in JDBC PreparedStatement]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#set-null-values
[What is the use of getGeneratedKeys_ method in Statement]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#generated-keys
[What are the benefits of PreparedStatement over Statement]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Statement-vs-PreparedStatement
[What is the limitation of PreparedStatement and how to overcome it]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#PreparedStatement-IN-clause
[What is JDBC ResultSet]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#ResultSet
[What are different types of ResultSet]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#ResultSet-Types
[What is the use of setFetchSize_ and setMaxRows_ methods in Statement]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#fetchSize-maxRows
[How to use JDBC API to call Stored Procedures]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#CallableStatement-Stored-Procedure
[What is JDBC Batch Processing and what are it_s benefits]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Batch-Processing
[What is JDBC Transaction Management and why do we need it]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Transaction-Management
[How to rollback a JDBC transaction]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Rollback-Transaction
[What is JDBC Savepoint_ How to use it]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Savepoint
[What is JDBC DataSource and what are it_s benefits]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#DataSource-Benefits
[How to achieve JDBC Connection Pooling using JDBC DataSource and JNDI in Apache Tomcat Server]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Connection-Pool-Tomcat
[What is Apache DBCP API]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#apache-dbcp
[What is JDBC Connection isolation levels]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#connection-isolation-levels
[What is JDBC RowSet_ What are different types of RowSet]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#RowSet-Types
[What is the different between ResultSet and RowSet]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#ResultSet-vs-RowSet
[What are common JDBC Exceptions]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#SQL-Exceptions
[What is CLOB and BLOB datatypes in JDBC]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#CLOB-BLOB
[What is _dirty read_ in JDBC_ Which isolation level prevents dirty read]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#dirty-read-isolation
[What is 2 phase commit]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#2-phase-commit
[What are the different types of locking in JDBC]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#locking-types
[What do you understand by DDL and DML statements]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#DDL-DML-statements
[What is difference between java.util.Date and java.sql.Date]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#java.util.Date-vs-java.sql.Date
[How to insert an image or raw data into database]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#insert-binary-data
[What is phantom read and which isolation level prevents it]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#phantom-read-isolation
[What is SQL Warning_ How to retrieve SQL warnings in the JDBC program]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#SQL-Warnings
[How to invoke Oracle Stored Procedure with Database Objects as IN_OUT]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#Oracle-STRUCT
[When do we get java.sql.SQLException_ No suitable driver found]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#No-suitable-driver-found-exception
[What are JDBC Best Practices]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers#jdbc-best-practices
[Java Reflection API]: https://www.journaldev.com/1789/java-reflection-example-tutorial
[JDBC Example]: https://www.journaldev.com/2471/jdbc-example-mysql-oracle
[Socket connection]: https://www.journaldev.com/741/java-socket-programming-server-client
[SQL Injection Example]: https://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example
[JDBC PreparedStatement IN clause alternatives]: https://www.journaldev.com/2521/java-preparedstatement-in-clause-alternatives
[LIMIT]: https://www.journaldev.com/24379/sql-limit-mysql-limit
[JDBC CallableStatement Example]: https://www.journaldev.com/2502/callablestatement-in-java-example
[JDBC Batch Processing Example]: https://www.journaldev.com/2494/jdbc-batch-insert-update-mysql-oracle
[JDBC Transaction Management Example]: https://www.journaldev.com/2483/java-jdbc-transaction-management-savepoint
[JDBC DataSource Example]: https://www.journaldev.com/2509/java-datasource-jdbc-datasource-example
[Tomcat DataSource JNDI Example]: https://www.journaldev.com/2513/tomcat-datasource-jndi-example-java
[https_www.journaldev.com_2529_jdbc-interview-questions-and-answers]: https://www.journaldev.com/2529/jdbc-interview-questions-and-answers