Programming technology of SQL database in JSP

The SQL Review 1.SQL statement is divided into two categories: DDL (Data Definition Language) and DML (Dat manipulation languge, data manipulation language). The former primarily defines the logical structure of data, including the definition of tables, views, and indexes; DML is primarily a query and update operation on a database.

2.Create Table (DDL):

Create Table tabname (Colname1coltype1[else],colname2coltype2[else],..., colnamencoltypen[else]);

For example: Cteate Table pjoiner (pno char (6) Not Null,eno char (6) nut null); char int varchar

And so on are reserved words used to define the column data type, where varchar represents a mutable character type.

3.Select <col1>,<col2>,..., <coln>from <tab1>,<tab2>,..., <tabm>[Where< conditions The subquery in the condition:

Where not Exists (Select * from TAB2 Where col1=col2)//When the query result is empty, the condition is true.

4.INSERT into <tab1> VALUES (<COL1> ...<coln>)

5.DELETE from <tab1> [where< conditions]

6.UPDATE <tab1>set <tab1>=<vlu1>...<tabn>=<vlun>[WHERE< conditions]

For example: Update exployeeset age=27where name= ' Zhao Yi '

Second, JDBC main interface:

The Java.sql.DriverManager class is used to process driver transfer and support for new database connections.

Java.sql.Connection, refers to the connection of an application to a specific database.

Java.sql.Statement, for the execution of General SQL statements (can be queries, updates, or even the process of creating a database)

Java.sql.ResultSet, the results returned by the query are saved in this object and can be used to browse and access records within the database.

1. Use ODBC database via JDBC-ODBC Bridge (no JDBC Drivers required)

Set the pubs Sysdsn,sa to username at the ODBC DSN (Data Source Name) setting, with an empty password

Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");//Load Driver

Con=drivermanager.getconnection ("Jdbc:odbc:pubs", "sa", "");//jdbc:odbc:pubs

Con.close ();//The Getwarning method that should catch ClassNotFoundException and Sqlexceptionconnection returns a SQLWarning object that should be checked before connecting.

The biggest benefits of using JDBC-ODBC are: free. However, performance is limited by ODBC, and the general ODBC driver is more expensive.

2. Use a dedicated JDBC driver.

Here is the MM JDBC driver first to put the jar file inside the classpath.

Class.forName ("Org.gjt.mm.mysql.Driver");

Con=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/dbname", "Root", "");

Con.close ();

It is not relevant to see how the database is connected to the operation of the database and the connection database.