Perform database operations through SQL mark in Java

I. Expression and Function of SQL mark.

When we look at the source code of Java, we will often see the following representation in the future:

In fact, this is a typical SQL tag. In JSP, SQL tag is mainly used to conveniently perform data-related operations. For example, establishing a database connection. JSP is mainly used to dynamically obtain data in the database. Therefore, it is necessary to establish a connection with the database in JSP. At the same time, JSP is not suitable for complex operations. These operations are usually implemented in JavBeans. Therefore, for Jsp language, SQL Tag mainly performs database connection management, data query, and other operations.

As shown in the preceding SQL mark, the information of a database is simply defined. In this SQL tag, you can also use other parameters to define specific database information. For example, you can define the JNDL name or Driver manager name of the data source. Here, you can use the VAR, Driver, and URL parameters. Driver is mainly used to determine the database Driver used to connect to the database. This parameter is directly related to whether the database connection is successful. Note that different databases use different database drivers, such as Oracle databases and SQL Server databases. This requires developers to adopt different drivers based on different databases when developing applications. In particular, you need to consider how to make the system adopt the appropriate driver when an application can adopt multiple databases. In general, the database used has been determined during system deployment. During system installation, system personnel are asked to select the database used. That is, information about the database used in the basic parameters of the system. Therefore, when using SQL tags, you may also need to use IF or Case statements to determine the database driver used.

2. query data from the database with SQL mark.

The most widely used SQL tag in JSP is to dynamically obtain relevant data from the database. The SQL mark to be used is SQL: query. This flag is mainly used to implement SQL statements to access the database and return a result. That is to say, General SQL query statements, such as the Select statement JSP language, are unknown. Developers often encapsulate the SQL tag and pass it to the database. After the database runs, the SQL Mark then passes the relevant results to the application. When using this parameter, I think you need to pay attention to the following points.

First, you need to establish a database connection. SQL: the Query mark is used to implement SQL statements to access the database and return relevant results from the database. This means that when using this tag, you must first establish a database connection. Otherwise, the system reports an error.

Second, you should note that parameters should be used in the query statement as much as possible. For example, you can query related data from a product. If there are 0.1 million records in this product and no relevant parameters are included in the query statement, the database will return all records. This will affect the system response speed. From the user's point of view, he does not know all the information. Therefore, related parameters are usually included in the SQL: query tag. To meet this requirement, developers can use the Param tag to define the required parameters in SQL: query. However, the parameter must be correctly defined. In addition, the name of the database table used must be an existing field in the table. Note that different databases may have different requirements on field sensitivity. For example, in an Oracle database, the field names are case-insensitive. However, the value stored in the field is case sensitive. Therefore, when passing field names or parameter values in parameters, you must consider the case-sensitive processing method.

Third, consider whether to include the default parameters in the query statement. Sometimes users may be lazy and do not enter parameters on the query interface. If no default parameter exists, the system response speed may be slow. The database administrator may repeatedly remind developers to use default parameters to improve database query efficiency. If you want to query the product information table. At one time, only 50 records may be displayed on the Web interface. In this case, if the data is queried from the database at one time, the system performance will be reduced. It is reasonable to add a default parameter to the SQL: query tag. If the user does not enter any restrictions, the data queried by the SQL language from the database each time is 50 records. When you need more information, click the next page and execute the query statement again. The last 50 records are displayed.

Most database query optimization relies on the Where statement in the query process. In SQL: query tag, Param tag is used. To improve the performance of the application system, it is best to provide default parameters for SQL statements. How to Set specific parameters depends on the actual situation of the application system. For a sales order, the order information of the current day is displayed by default. For product information, you can set to display the last 50 product information by default.