Ayin  Java Notes shrimp  


The design paradigm of data table

There are three most common design paradigms in actual development :


The first paradigm is the most basic paradigm . If all field values in the database table are atomic values that cannot be decomposed , It shows that the database table satisfies the first paradigm ;

The second paradigm needs to ensure that every column in the database table is related to the primary key , You can't just relate to a part of the primary key ( Mainly for the union primary key ). That is to say, in a database table , Only one kind of data can be saved in a table , It is not allowed to save multiple data in the same database table ;

The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key , Not indirectly .

Native JDBC How to connect to the database ?


You can fill in the code corresponding to these steps in your mind , I'm used to it , It's a little forgotten

What is the mechanism of connection pooling in relational database ?


What is the relationship between primary key and foreign key in relational database ?


Oracle Port number ?MySQL Port number ?


test1 Table has ABC The three column , use SQL Statements for : When A Column greater than B Column time , choice A Column otherwise select B Column , When B Column greater than C Select when column B Column otherwise select C Column .

from test1

Long Type how to design the table structure ?Boolean Type how to design the table structure ?


Please introduce the principle of trigger


Are the statements in a stored procedure executed in sequence ?


How to solve SQL Injection problem


Using precompiled objects PreparedStatement

Please talk about the difference between internal connection and external connection


# Internal connection 
select * from student s,classes c where s.c_id = c.id
select * from student s INNER JOIN classes c on s.c_id = c.id

# External connection
# The left outer join
select * from student s LEFT JOIN classes c on s.c_id = c.id
# Right connection
select * from student s RIGHT JOIN classes c on s.c_id = c.id

Talk about the understanding of database transactions ? Where should the boundaries of a transaction be placed ? Why? ?


give an example :


Customer A And the customer B All the bank accounts are 10000 RMB , Customer A You need to transfer the 5000 RMB yuan is transferred to the customer B On the account of .

The process seems simple , It actually involves a series of database operations , It can be simply regarded as a two-step basic operation , From the customer A Deduct... From the amount of the account 5000 RMB , And will customers B Add... To the account 5000 RMB .

Hypothesis number 1 1 Step database operation succeeded , And if the second step fails , Will cause the entire operation to fail , And customers A The account amount will be deducted 5000 RMB .

Transaction mechanism can avoid this kind of situation , To ensure the completion of the whole operation , If something goes wrong , All previous database operations will be invalid .

Talk about the characteristics of transactions ACID


Atomicity (ATOMICITY): 

A transaction is the logical unit of work of a database , Each operation contained in the transaction is either completed , Or it's not done


Uniformity (CONSISTENCY): 

The result of transaction execution must be to change the database from one consistency state to another . So when the database contains only the results of a successful transaction commit , Say the database is in a consistent state .

If the database system breaks down , Some transactions are interrupted before they are completed , Some of the changes made to the database by these unfinished transactions have been written to the physical database , The database is in an incorrect state , Or inconsistent state .


Isolation, (ISOLATION): 

The execution of a transaction cannot be interfered by other transactions . That is, the operation and data used within a transaction are isolated from other concurrent transactions , Transactions that execute concurrently cannot interfere with each other .


persistence (DURABILITY): 

Once a transaction is committed , Its changes to the data in the database should be permanent . The next operation or failure should not have any effect on its execution result .

Talk about the isolation level of transactions


What are stored procedures ?


Stored procedures are stored in the database , Can be executed by the application through a single call , And allow users to declare variables 、 Conditional execution and other powerful programming functions .

Stored procedures are compiled on the server when they are created , So the execution is more than a single SQL Fast sentence

Which database connection pools have been used , Why use database connection pool ?