Shulou(Shulou.com)06/02 Report--

This article mainly explains "what are the common interview questions in JDBC". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what are the common interview questions in JDBC.

JDBC (Java Data Base Connectivity,java Database connection) is a Java API used to execute SQL statements, which can provide unified access to a variety of relational databases. It consists of a set of classes and interfaces written in Java language. JDBC provides a benchmark from which to build more advanced tools and interfaces that enable database developers to write database applications.

Initialize H2 database public class BaseTest {protected void initH2db (Connection conn) throws SQLException, ClassNotFoundException, URISyntaxException {Statement st = null; try {String schema = getClass (). GetResource ("/ db/schema.sql"). ToURI (). ToString (). Substring (6); String data = getClass (). GetResource ("/ db/data.sql"). ToURI (). ToString (). Substring (6); st = conn.createStatement () / / this sentence can not be st.execute ("drop all objects;"); / / execute initialization statement st.execute ("runscript from'" + schema + "'"); st.execute ("runscript from'" + data + ") } finally {if (Objects.nonNull (st)) {st.close ();} JDBC Operation Database flow public class JdbcDemoTest extends BaseTest {@ Test public void testJdbcDemo () {String sql = "select `id`, `name`, `age`, `address` from person where name =?"; Connection conn = null; PreparedStatement stmt = null; ResultSet resultSet = null Try {/ / register the JDBC driver Class.forName ("org.h3.Driver"); / / Open the connection conn = DriverManager.getConnection ("jdbc:h3:mem:ssb_test", "root", "root"); initH2db (conn); / / create Statement stmt = conn.prepareStatement (sql) Stmt.setString (1, "wyf"); / / execute query resultSet = stmt.executeQuery (); / / process result / / expand the result set database List peoples = new ArrayList (); while (resultSet.next ()) {Person person = new Person () / / retrieve person.setId (resultSet.getLong ("id")); person.setName (resultSet.getString ("name")); person.setAge (resultSet.getInt ("age")); person.setAddress (resultSet.getString ("address")); peoples.add (person) through fields } System.out.println (JSON.toJSONString (peoples));} catch (Exception e) {e.printStackTrace ();} finally {/ / close resource if (Objects.nonNull (resultSet)) {try {resultSet.close () } catch (SQLException e) {e.printStackTrace ();}} if (Objects.nonNull (stmt)) {try {stmt.close ();} catch (SQLException e) {e.printStackTrace () }} if (Objects.nonNull (conn)) {try {conn.close ();} catch (SQLException e) {e.printStackTrace ();}

We can see that the main process of JDBC operating the database is as follows:

Register the JDBC driver (Class.forName ("XXX");)

Open the connection ("url", "name", "password")

Create a Statement (conn.prepareStatement (sql)) based on the connection

Set parameters (stmt.setString (1, "wyf");)

Execute query (stmt.executeQuery ();)

Processing result, result set mapping (resultSet.next ())

Close Resource (finally)

The difference between Statement, PreparedStatement and CallableStatement

Statement: used to execute a fixed SQL statement with no parameters and return the results it produces, and SQL is compiled each time SQL is executed.

PreparedStatement: the object of a precompiled SQL statement that is used to execute a precompiled SQL statement with parameters. Efficient, safe and maintainable.

CallableStatement: the interface used to call stored procedures in the database. If there is an output parameter to register, it is an output parameter.

How to prevent SQL injection from PreparedStatement

Pre-compiled SQL use? The parameter is set by adding single quotation marks to prevent SQL injection. For example:

Select `id`, `name`, `age`, `address` from person where name =?

The final execution will become:

Select `id`, `name`, `age`, `address` from person where name = 'wyf or id > 0'

The execution of the or statement is effectively prevented by single quotation marks''.

Disadvantages of JDBC programming

The workload is heavy, the process is long, and there is a lot of repetitive work in operating the database.

Serious coupling between business code and technical code

Every time a new connection resource is required, the system overhead is high.

The connection resource needs to be closed manually, which is easy to forget, which brings hidden trouble.

Because of the above reasons, we rarely use JDBC directly to operate the database in the actual project development process, but generally use database connection pool (druid) + ORM framework (mybatis) to operate the database.

Advantages of using connection pooling and ORM framework

Connection resources can be reused, reducing system overhead and improving system performance.

You no longer have to worry about forgetting to release resources, which greatly simplifies the process of operating the database.

Decouple technical code from business code

Source code

Https://github.com/wyh-spring-ecosystem-student/spring-boot-student/tree/releases

Spring-boot-student-mybatis project

Thank you for your reading, the above is the content of "what are the common interview questions in JDBC". After the study of this article, I believe you have a deeper understanding of what are the common interview questions in JDBC, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.