A summary of database development of JSP learning _JSP programming

This paper summarizes the method of database development of JSP learning. Share to everyone for your reference. Specifically as follows:

The composition of the SQL language:

1> Data Definition Language DDL is used to define database objects such as SQL schema, datasheet, view, and Index
2> Data Manipulation Language DML data query and Data Update language
3> Data Control Language DCL set or change database user or role
4> Embedded SQL language SQL statement embedded in host language

Data type:

1> Numeric type INTEGER SMALLINT real NUMERIC DECIMAL FLOAT DOUBLE ...
2> date and Time type TIMESTAMP date times ...
3> character and String type CHARACTER CHAR VARCHAR ...

aggregate functions: AVG (), COUNT (), MAX (), MIN (), SUM () ....

Scalar functions:

Arithmetic function (absolute value, square)
String function (for string length)
Time-Date function (returns the current time of the system)
Relay Data functions

Mode:

A collection of database tables is called a pattern, consisting of the schema name and the owner of the schema

CREATE SCHEMA student AUTHORIZATION Stu;
DROP SCHEMA student CASCADE;
Cascade Delete Database objects in the schema together
Delete is not allowed when database objects exist in restrict mode

CREATE TABLE Student (
 Xuehao char (7) PRIMARY KEY,
 name CHAR (8) not NULL,
 sex char (2), age
 SMALLINT,
 Jiguan char (M),
 Dept Char (a) DEFAULT ' computer ');

Data table creation modification and deletion

ALTER TABLE Student Add address CHAR (30); Add an address column
DROP TABLE Student cascade| RESTRICT

Creation and deletion of indexes

CREATE INDEX Xuehao_index on student (Xuehao)
DROP INDEX Xuehao_index

Data Modification UPDATE student SET age=age+1 WHERE xuehao= ' 2004007 '

Data Deletion DELETE from student;

JDBC Programming

Jdbc:subprotocal:data Source Identifier
Jdbc:odbc:university the university database in the form of a JDBC ODBC bridge
Jdbc:odbc:university?user=username&password=password a connection with parameters

Format for connecting to a network database

jdbc:subprotocal://[hostname][:p Ort][dbname][?parameter1=value1][&parameter2=value2] ...
Jdbc:microsoft:sqlserver://localhost:1433;database=university?user=username&password=password

To connect to a SQL Server database using the JDBC driver:

try{
forname ("Com.microsoft.jdbc.sqlserver.SQLServerDriver"). newinstance ();
Url= "Jdbc:microsoft:sqlserver://localhost:1433;databasename=university"
conn=drivermanager.getconnection ( Url,username,password);
catch (Exception e) {
 System.out.println (e);
}

To connect using the Jdbc-odbc bridge:

try{
forname ("Sun.jdbc.odbc.JdbcOdbcDriver"). newinstance ();
Url= "Jdbc:odbc:university"
conn=drivermanager.getconnection (Url,username,password);
catch (Exception e) {
 System.out.println (e);
}

The statement object is primarily used to execute SQL statements, and you can create a statement object using the Createstatement () method of the Connection object

Statement statement=conn.createstatement ();
String sql= "SELECT * from student";
ResultSet rs=statement.executequery (SQL);

ResultSet Accept return Results
If you want to execute a insert,delete,update,create,drop statement, you should use the Executeupdate method

String sql= "Delete from student where xuehao=" + "' 0741210 '";
int i=statement.executeupdate (SQL);//return value is the number of rows affected
System.out.println (i);
Public Boolean execute () throws Exception

Used to execute an SQL statement that does not know the type in advance to dynamically process an unknown SQL statement

I hope this article will help you with the JSP program design.