Database interviewing skills, showcasing your expertise through JDBC, from the Java Web Lightweight Development Interview tutorial

This article is the interview technique I wrote before about the database, how to show my ability from the table-building and

Interview skills, how to index the database optimization, content from the Java Web Lightweight Development Interview tutorial is a series, through the interviewer's perspective and everyone to share in the database interview experience, these are from the Java Web Lightweight Development Interview tutorial.

The previous two articles on the number of clicks are OK, but also thank the administrator to put on the home page, which inspired me, but also let me more confident to share my experience with you.

We know that in the end we're going to connect and access the database through JDBC, which means that recently the interviewer will be able to assess the candidate's abilities through JDBC skills, and this article is to show you this.

1 using try...catch...finally clauses

According to the characteristics of this clause, the FINALLY clause is bound to be executed (except with System.exit exit), regardless of whether an exception occurs or not.

According to this feature, you need to write the code that closes the JDBC object part to the finally clause.

At the same time, the following three points should be noted in Try...catch:

First, can not directly use exception to receive all the anomalies, should first use professional exception handling classes, such as SqlException to receive, and finally use exception to do the final defense.

Second, in the catch clause, do not do anything, do not directly throw an exception, you should return some actionable statements, prompting the user to encounter an exception, such as to give the contact person's phone.

Third, because monitoring and detecting anomalies is a cost, you should minimize the scope of try...catch, including only the necessary code, instead of including the entire function in the Try...catch clause.

Let us not underestimate the very easy-to-understand principle. When the author worked for a large company, he had added a project review team, all of which had to go through the team review before going online, and if the scores were not up to standard, the code needed to be rectified.

The first mentioned principles are specific scoring items, the big company is a well-known foreign enterprise, the employees are very good, but in the abnormal handling points is a common phenomenon. In other words, if you say the above in the interview, you will make the interviewer be impressed.

Below is a simple query example Resultdemo.java to observe the standard notation. There is a student table, which has two fields, all of which are the character type's number ID and name name.

1 Importjava.sql.Connection;2 ImportJava.sql.DriverManager;3 ImportJava.sql.ResultSet;4 Importjava.sql.SQLException;5 Importjava.sql.Statement;6  Public classResultdemo {7      Public Static voidMain (string[] args) {8         Try {9                //you need to ensure that the MySQL-enabled jar can be readTenClass.forName ("Com.mysql.jdbc.Driver"); One}Catch(ClassNotFoundException e) { ASystem.out.println ("Where is your MySQL JDBC Driver?")); - e.printstacktrace (); -                 return; the             } -Connection Connection =NULL; -Statement stmt =NULL; -             Try { +             //here is the connection string -Connection =Drivermanager.getconnection ( +"Jdbc:mysql://localhost:3306/class3", "root", "123456"); A                 if(Connection! =NULL) { atstmt =connection.createstatement (); -String query = "Select Id,name from Student"; -ResultSet rs=stmt.executequery (query); -                      while(Rs.next ()) -                     { - System.out.println (rs.getstring ("ID")); inSystem.out.println (rs.getstring ("Name")); -                     } to}Else { +System.out.println ("Failed to make connection!"); -                 } the}Catch(SQLException e) { *System.out.println ("Check the JDBC Driver or connection!"); $ e.printstacktrace ();Panax Notoginseng}finally { -             //Close the connection the                 Try { + stmt.close (); A connection.close (); the}Catch(SQLException e) { + e.printstacktrace (); -                 } $             } $         } -}

The business of the above code is very simple, after connecting the database, print the ID and name information of the student table sequentially. But let's take a look at the revelation that this code brings to us.

First, in a short business logic, we are in the 8th to 15th Line, 18th to 44th Line, with two pieces of try...catch, and no diagram easy to use a piece of Try...catch code to include all business methods, which follows the "Minimize detection range" principle.

Second, in the catch clauses in lines 35th and 36, instead of simply throwing out an exception, we output some information that, based on this information, the developer of the debugger can quickly get hints from and quickly find the cause.

In addition, in the catch clause, you can also output some user-oriented information, such as the user to restart the program, in short, the need to translate Java-oriented exceptions to the programmer or user can understand the message.

Third, in the 37th to 44th row of the finally clause, the connection is closed, because no matter what happens or if an exception occurs, the finally clause is bound to be executed, so you can put the code to close the connection. If you do not close the connection, the database connection object cannot be reclaimed (the Java garbage collection mechanism cannot be reclaimed).

2 pre-processing and batching

Here are the questions: Have you ever used a PreparedStatement object? What's the difference between PreparedStatement and statement?

Please make it clear that two points, 1 increase efficiency, 2 avoid SQL injection, so as to ensure the security of the system.

What do you call SQL injection? For example, the login interface shown.

We generally use the following SQL to verify the identity:

Select userName from users where userName = ' input username ' and pwd = ' input password '

In general, if the user name and password does not match, is not verified, but someone can enter in User name 1, in the User password section input: 1 ' and pwd = ' 1 ' or ' 1 ' = ' 1

The entire SQL statement will then become:

Select userName from users where userName = ' 1 ' and pwd = ' 1 ' or ' 1 ' = ' 1 '

This allows you to bypass validation.

And the object preparestatement can effectively prevent the occurrence of this phenomenon, because a "?" is a placeholder that cannot be extended. Here is an example of preprocessing and batching, as well as using the student table just mentioned, this time to bulk insert data.

Import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.sqlexception;import Java.sql.statement;public class Jdbcbatch {public static void main (string[] args) {try { Class.forName ("Com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {System.out.println ("Where is your MySQL JDBC Driver?"); E.printstacktrace (); return;}        Connection Connection = null; Preparestatement Pstmt;try {//Here is the connection string connection = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/class3 "," root "," 123456 "); if (connection! = null) {String query =" INSERT into student values (?,?) "; pstmt = connection.preparedstatement (query);//Start setting Parameters pstmt.setstring (1, "1");p stmt.setstring (2, "Peter"); Pstmt.addbatch ();//Set the second parameter pstmt.setstring (1, "2");p stmt.setstring (2, "Mike");p Stmt.addbatch ();// Execute batch pstmt.executebatch ();} else {System.out.println ("Failed to make connection!");}} catch (SQLException e) {System.out.println ("Some of Students were not inserted correctly,Please check the student table and insert manually. "); E.printstacktrace ();} Finally {try {connection.close ();} catch (SQLException e) {e.printstacktrace ()}}}}

This part of the logic is also relatively simple, using INSERT statements to BULK INSERT data. But please note the following two points:

① in Preparestatement, the placeholder number starts at 1 instead of starting at 0.

② batch operation can improve efficiency, but how much one-time operation to maximize efficiency? This is different in different databases, typically 500 to 1000 statements per batch of operations. But remember, don't put all the INSERT statements in Addbatch at once, because if the SQL statement is too large, it will burst the cache, thus making an error.

Preparestatement is a more important JDBC object. Again, when we interview, we sometimes ask this question: What's the difference between statement and preparestatement? The answer is that preparestatement can be preprocessed, and if it can be unfolded, it would be better to prevent SQL injection.

To summarize, here you can explain your ability in JDBC by describing the key points in the code.

1 illustrates the use of try...catch...finally.

2 describes the usage of PreparedStatement in detail, including preprocessing batches and SQL injection two points.

Database interviewing skills, showcasing your expertise through JDBC, from the Java Web Lightweight Development Interview tutorial