Java Interview ④ Database section

Classification of 2.3.1 Database and its common database

Database is divided into: relational database and non-relational database

relational database: Mysql,oracle,sqlserver

Non-relational: Redis,mongodb

2.3.2 A brief introduction to the three paradigms of relational database

Paradigm is the norm, which is the three specifications that the relational database should follow when setting up a table.

To satisfy the second paradigm, the first paradigm must be satisfied first, and the second paradigm must be satisfied to satisfy the third paradigm.

The so-called first normal form (1NF) means that each column of a database table is an indivisible basic data item and cannot have multiple values in the same column, that is, an attribute in an entity column cannot have multiple values or cannot have duplicate attributes, and the column data is indivisible.

The second paradigm (2NF) requires that each row in a database table must be uniquely differentiated, and that for implementation it is usually necessary to add a column to the table to store the unique identities of each instance. (primary key)

Satisfying the third paradigm (3NF) must first satisfy the second paradigm, in short, the third paradigm requires that a database table does not contain non-keyword information already contained in other tables. (Foreign key)

Inverse three-paradigm, sometimes for efficiency, you can set the repetition or can deduce the field, the order (total price) and the order item (unit price)

Four basic characteristics of 2.3.3 transactions

A thing is a unit of concurrency control, a user-defined sequence of actions that are either done or not, and are an inseparable unit of work.

A transfer must be a account to deduct money success, B account plus money success, just calculate the true transfer success.

Transactions must meet four characteristics:

Atomicity: Indicates that operations within a transaction are inseparable, either succeed or fail

Consistency: Either success or failure, followed by failure to roll back the previous operation

Isolation: After a transaction starts, it cannot interfere with other transactions

Persistence (Persistence): Indicates that the transaction has started and cannot be terminated.

2.3.4 The default maximum number of connections for MySQL database?

Why do I need the maximum number of connections? The database above the specific server can only support a certain number of simultaneous connections, this time we need to set the maximum number of connections (maximum number of simultaneous service connections), the database installation will have a default maximum number of connections. The maximum number of connections is 100.

max_connections=100;

2.3.5, what about MySQL's paging? Paging for Oracle?

Why do I need to split pages? In many databases, it is not possible to display the data completely and display it in segments.

MySQL uses the keyword limit for paging, and the limit offset,size represents how many bits are taken from the index.

The paging of Oracle. Most of the cases, we can't remember, say the idea, to use three layers of nested queries

Oracle's pagination advantages can not remember, just remember some, is the use of class three nested query, if used in the work, you could go to the original project copy or Internet query.

2.3.6 A brief talk about the usage scenarios of the database triggers

triggers, which need to trigger conditions when the condition satisfies what to do after the operation.

Triggers are useful or a lot of, such as Xiaonei, Facebook, you send a log, automatically notify friends, in fact, is to increase the log to do a post-trigger, and then write to the notification table entries, because the trigger efficiency is high, and uch no trigger, efficiency and data processing ability is very low.

Every time you insert a post, you want to update the last post in the layout table, the Post Number field, and make it more efficient with triggers.

Keyword: trigger

2.3.7 A brief talk about the usage scenarios for the database's stored procedures?

Database stored procedures have the following advantages:

1) Stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can greatly improve the data execution several times.

2) in general, complex business logic requires multiple SQL statements, which are sent separately from the client to the server, when there are many operations between the client and the server, will generate a lot of network transport, if these operations in a stored procedure, the client and the server network transmission between the greatly reduced Reduced class Network Load,

3) A stored procedure can be reused once it is created, reducing the workload of the database developer.

4) High security, stored procedures can block direct access to the underlying database objects, using EXECTU permissions to call the stored procedure, without having access to the underlying database object display permissions.

To define a stored procedure:

CREATE PROCEDURE Studen (name varchar (), age int,id in);

Begin

INSERT into student values (' Lisi ', 22, 1);

Select Name,age from student;

End

Call a stored procedure

Call student ();

Select

2.3.8 How do I invoke a stored procedure with JDBC?

Load Driver

Get connections

Setting parameters

Perform

Release connection

Public static void Main (String[]args) { Connection conn = null; CallableStatement st = null; Try { It's better not to do this, because the driver's name is written dead in the program. Class.forName ("Com.mysql.jdbc.Driver"); In the actual project, datasource data is applied here, if the framework This data source does not need our code to create, we only need to datasourceds=context.lookup (); conn = Ds.getconnection (); conn = Drivermanager.getconnection ("Jdbc:mysql:///test", "root", "root"); st = Conn.preparecall ("{Call Insert_student (?,?,?)}"); St.registeroutparameter (3, Types. INTEGER); St.setstring (1, "Laowang"); St.setint (2, 25); St.execute (); Get the first few, different databases are not the same, it is recommended not to write System. out. println (st.getstring (3)); } catch(Exception e) { E.printstacktrace (); } }

2.3.9 Common SQL

Slightly

2.3.10 briefly, your understanding of JDBC

Java Databases Connection Java database connection, database relational system (Mysql,oracle) is a lot, each database management system supports the command is not the same,

Java only defines the interface, let the database vendors implement their own interface, for us only need to import the corresponding vendor development of the implementation interface, and then call the interface (Mysql+mysql driver +JDBC)

2.3.11 Write a simple JDBC program and write a JDBC program that accesses the Oracle database?

Load Driver (oracle.jdbc.driver.OracleDriver)

Get the connection (drivermanager.getconnection (URL, user, password))

Setting parameters

Statement st = Conn.createstatement ();

St.setxxx (Index,value)

Execute (EXECUTE)

Release the connection (whether the connection is small to large and must be put to finally)

The benefits of statement in 2.3.12 JDBC compared to PreparedStatement

Most of the time, PreparedStatement is used instead

1) PreparedStatement is pre-compiled, faster than statement

2) readability and maintainability of code

Although PreparedStatement to replace statement will make the code a few more lines, but such code from the readability or maintainability, is more than the direct use of statement code much higher grade.

Stmt.executeupdate ("INSERT into Tb_name (COL1,COL2,COL2,COL4) VALUES ('" +var1+ "', '" +var2+ "'," +var3+ ", '" +var4+ "')");

perstmt = Con.preparestatement ("INSERT into Tb_name (COL1,COL2,COL2,COL4) VALUES (?,?,?,?)");

Perstmt.setstring (1,VAR1);

Perstmt.setstring (2,VAR2);

Perstmt.setstring (3,VAR3);

Perstmt.setstring (4,VAR4);

Perstmt.executeupdate ();

Needless to say, for the first method. Don't tell anyone else to read your code, it will be sad if you read it yourself for some time.

3) Security

PreparedStatement can prevent SQL injection attacks, while statement cannot, for example:

String sql = "SELECT * from Tb_name where name= '" +varname+ "' and passwd= '" +varpasswd+ "'";

If we pass [' or ' 1 ' = ' 1] in as varpasswd. User name feel free to see what will become?

SELECT * from tb_name = ' random ' and passwd = ' or ' 1 ' = ' 1 ';

Because ' 1 ' = ' 1 ' is sure to be true, so you can pass any validation. What's more:

Put [';d rop table tb_name;] Incoming in as VARPASSWD:

SELECT * from tb_name = ' random ' and passwd = ';d rop table tb_name; some databases are not going to make you successful, but there are many databases that can make these statements executable.

And if you use precompiled statements. Any content you pass in will not have any matching relationship with the original statement. (As long as the database itself supports precompilation, but there may be no server-side database that does not support compilation, only a handful of desktop databases, which are direct file access) You do not have to worry about incoming data if you use precompiled statements all the time. And if you use ordinary statement, It is possible to make a judgment and worry about the drop, and so on.

The role of the 2.3.13 database connection pool

1) Limiting the number of databases will not cause the system to run slow or crash due to too many databases.

2) database connection pool does not need to be created or destroyed every time, save resources.

3) Database connection pool does not need to be created each time, response time is fast.

Java Interview ④ Database section