DBCP connection and MySQL optimization

One: The principle of database connection pooling
The database connection pool holds some database connection objects (connection objects), and when you manipulate the database
Gets the connection object directly from the connection pool, and then puts it back into the database connection pool. Pass
Saving and putting back the connection object greatly improves the access efficiency of the database.

Two: DBCP Connection pool Components
Properties Pro=new properties (); Property Resource Class
InputStream Input=dbcpdemo.class.getclassloader (). getResourceAsStream ("dbcpfactory.properties"); Gets the input stream that points to the configuration file
Pro.load (input); Read the list of attributes from the input stream (key and element pairs)
DataSource Ds=basicdatasourcefactory.createdatasource (PRO);
Connection conn=ds.getconnection (); Get Connection Object
Note: The key in the property profile cannot be changed.

Three: C3P0 Connection Pool Component
The configuration file is: C3p0-config.xml
<?xml version= "1.0" encoding= "UTF-8"?>
<c3p0-config>
<default-config>
<property name= "Driverclass" >org.gjt.mm.mysql.Driver</property>
<property name= "Jdbcurl" >jdbc:mysql://localhost:3306/mydb2?characterEncoding=utf8</property>
<property name= "User" >root</property>
<property name= "Password" >123456</property>
<property name= "Initialpoolsize" >10</property>
<property name= "MaxIdleTime" >30</property>
<property name= "Maxpoolsize" >100</property>
<property name= "Minpoolsize" >10</property>
<property name= "Maxstatements" >200</property>
</default-config>
</c3p0-config>

Combopooleddatasource ds=new Combopooleddatasource (); Get Connection Pool Object
Connection conn=ds.getconnection (); Get Connection Database Connection object

Quad: triggers in MySQL (trigger)
Delimiter//

Create TRIGGER Trigger name Before|after Trigger Event (Insert|delete|update)
On trigger associated table name for each row
Begin
Logic code executed by the trigger
End

delimiter;

Trigger Case:
Delimiter//

Create trigger Stu_trigger after insert
On student for each row
Begin
Insert into log (Logname,logtime) values (New.name,now ());
End//

delimiter;

V: MySQL Optimization
Method of query optimization:

1. Optimize query SQL statements. If both the connection query and the subquery can query the results, the connection query is selected first.
Because a temporary table is created when a subquery is made, the contents of the temporary table store the subquery results, and after the query is complete, the temporary table is also revoked.

2. Create an index on the frequently used column.

3. Split the table. If you have a large number of fields in a table, you should store infrequently used fields in another table, the original table
Keep only the common query fields.

4. Add redundant fields. Add information that needs to be used frequently to tables in a redundant field, eliminating the need for connections
The time-consuming query. Eg: if you frequently want to query the employee's name and its corresponding department name,
The department name can be used as a redundant field for the Employee table EMP, eliminating the EMP table and the Dept table
The connection query.
5. Increase the intermediate table. Create an intermediate table in which query fields from multiple tables are stored in this intermediate table.

VI: Use MySQL workbench to build ER diagrams, export them into SQL files, and import
1. Create the EER model in data modeling mode.
2. Export the EER model as a SQL file.
3. Import the SQL file in SQL development mode and execute it.

DBCP connection and MySQL optimization