Java Interview-Database chapter (i)

1, in two ways according to the department number from high to low, salary from low to high list each employee's information.

1 Employee: 2 3 Eid,ename,salary,deptid; 4 5 Select *  from  by desc ASC;

2. What is the three-paradigm of the database?

First Normal (1NF): The field is atomic and cannot be re-divided. All relational database systems satisfy the first paradigm) the fields in the database table are single attributes and cannot be divided.

For example, the Name field, where the first and last names must be a whole, cannot distinguish which part is the last name, which part is the name, and if the first and last names are to be distinguished, the two separate fields must be designed. The second paradigm (2NF): The second paradigm (2NF) is established on the basis of the first paradigm (1NF), that is, satisfying the second paradigm (2NF) must first satisfy the first normal form (1NF). Requires that each instance or row in a database table must be divided by a unique region. It is often necessary to add a column to the table to store unique identities for each instance. This unique attribute column is called the primary key or primary key. The second normal form (2NF) requires that the attributes of an entity depend entirely on the primary key. A full dependency is a property that cannot exist that relies only on the primary key, and if it does, then this property and the primary key

This part should be separated out to form a new entity, a one-to-many relationship between the new entity and the original entity. It is often necessary to add a column to the table to store the unique identity of each instance.

In short, the second paradigm is that a non-principal attribute is dependent on the primary key. The requirements of the third paradigm are as follows: satisfying the third paradigm (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key information already contained in other tables. So the third paradigm has the following characteristics: 1, each column has only one value of 2, each row can be distinguished. 3, each table does not contain non-primary key information that other tables already contain.

3, say some of the database optimization experience?

With PreparedStatement generally higher performance than statement: A SQL to the server to execute, involving the steps: grammar check, semantic analysis, compilation, slow existence of foreign key constraints can affect the insertion and deletion performance, if the program can guarantee the integrity of the data, The foreign key is removed when designing the database. (analogy: Like exempt products, is to improve efficiency, fully believe that the product manufacturer) (for hibernate, there should be a change: Empleyee->deptment object, Now the design is a employeeàdeptid) look at the last part of the MySQL Help text sub-query section, for example, according to the principle of scanning, the following subquery is more efficient than the second associated query: 1.select e.name,e.salary where e.managerid= (select ID from the employee where Name= ' zxx '); 2.select e.name,e.salary,m.name,m.salary from Employees E, Employees m where E.managerid = M.id and m.name= ' zxx '; The table allows appropriate redundancy, such as the number of replies to a topic post and the last reply time, to separate names and passwords from the user table independently. This can be a very good one-to-one case yo! SQL statements are capitalized, especially column names and table names. In particular, the caching capabilities of SQL commands require uniform case, SQL statements, Oracle servers, syntax checking and compilation as internal instructions, caching and execution instructions.

4. What is the difference between Union and union all?

The UNION will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. In most applications, duplicate records are not generated.

5. What is the role of Class.forName?

Searches for and loads the class name in the string as specified in the parameter, and returns the class instance object that represents the bytecode if the class bytecode has already been loaded.

Otherwise, the class loader's delegate mechanism is searched and loaded, and if all the ClassLoader cannot be loaded into the class, ClassNotFoundException is thrown.

After loading the class bytecode, you can then use the class bytecode Newinstance method to create an instance object of the class. Sometimes, all of the specific class names used in our program cannot be determined at design time (i.e. development time), but only when the program is run, it is necessary to use Class.forName to dynamically load the class, which is typically configured in a configuration file, for example, the spring IOC The specific class for each dependency injection is configured in this way, and the JDBC driver class name

It is usually configured through a configuration file so that the driver class name can be replaced without modifying the source program after the product is delivered.

6, the large data volume of the paging solution.

The best approach is to use SQL statements for paging, so that each query results in a set containing only the data content of a page. In the case where the SQL statement cannot be paged, consider a large result set through cursor positioning to get the data for a page.

1 SQL statement paging, different databases under the paging scheme, the following is the main three kinds of database paging sql:2 3 SQL Server:4 5String SQL="Select Top"+PageSize+"*  fromStudentswhereId not inch"+"(Select Top"+PageSize*(pagenumber-1)+The ID fromStudentsOrder  byID) "+"Order  byid ";6 7 MySQL:8 9String SQL="Select *  fromStudentsOrder  byID Limit "+PageSize*(pagenumber-1)+","+pageSize;Ten  One Oracle: A  -String SQL="Select *  from"+(Select *, RowNum RIDs from(Select *  fromStudentsOrder  byPostimedesc)whereRid<="+PageSize*PageNumber+") asT+ -  the"whereT>"+PageSize*(pagenumber-1);

7. Use JDBC to query student transcripts and write out the main code

1Connection cn=NULL;2PreparedStatement pstmt=NULL;3ResultSet RS=NULL;4 try{5     Class.forName (driver. ClassName);6cn=drivermanager.getconnection (Url,username,password);7Pstmt=Cn.preparestatement ("SelectScore,*  fromScore,student "+8“whereScore.stuid=Student.id andStudent.name=? ");9Pstmt.setstring (1, studentname);Ten  OneResultSet RS=pstmt.executequery (); A  -  while(Rs.Next()){ -System.out.println (Rs.getint ("subject")+“ ”+rs.getfloat ("score")); the } - }catch (Exception e) {e.printstacktrace ();} -      finally - { + if(RS!= NULL) -Try{rs.Close()}catch (Exception e) {} + if(pstmt!= NULL) ATry{pstmt.Close()}catch (Exception e) {} at if(CN!= NULL) -TRY{CN.Close()}catch (Exception e) {} -}

8. What is the working mechanism of the data connection pool?

The Java EE server starts with a certain number of pooled connections and maintains not less than this number of pooled connections. When a client program needs to connect, the pool driver returns an unused pooled connection and memento it as busy.

If there is currently no idle connection, the pool driver creates a new number of connections, and the number of new connections is determined by the configuration parameters. When the pool connection call is completed, the pool driver memento the connection as idle.

This connection can be used by other calls. Implementation, the returned connection is the original connection agent, the agent connection the Close method, not the actual connection, but the connection object that it proxies back to the connection pool.

9, why to use What's the difference between ORM and JDBC?

ORM is the idea of turning object into a record in a database, or turning records in a database into OBJECDT, and we can use JDBC to realize this idea,

In fact, if our project is written in a strictly oop way, our JDBC program is already working on the ORM, whether intentionally or unintentionally. Now there are many ORM tools, which call JDBC at the bottom to implement ORM work, and we use these tools directly, eliminating the tedious details of using JDBC directly.

Improved development efficiency, and now more ORM tools are hibernate. Also heard some other ORM tools, such as TOPLINK,OJB and so on.

Java Interview-Database chapter (i)