Summary of JSP learning database development and jsp database Summary

Summary of JSP learning database development and jsp database Summary

This article summarizes the database development methods for JSP learning. Share it with you for your reference. The details are as follows:

Composition of the SQL language:

1> Data Definition Language DDL is used to define database objects such as SQL mode, data tables, views, and indexes.
2> data manipulation language DML Data Query and data update Language
3> DCL sets or changes database users or roles
4> embedded SQL statements are embedded into the host language

Data Type:

1> Number type integer smallint real numeric decimal float double...
2> DATE and TIME type timestamp date time...
3> CHARACTER and string type character char varchar...

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

Scalar functions:

Arithmetic functions (absolute value, square)
String function (evaluate the string length)
Time and date function (returns the current system time)
Relay data Functions

Mode:

A set of database tables is called a mode, which consists of the schema name and the schema owner.

Create schema student AUTHORIZATION stu;
Drop schema student CASCADE;
CASCADE deletes database objects in the mode together.
Database objects in RESTRICT mode cannot be deleted.

Create table student (xuehao CHAR (7) primary key, name CHAR (8) not null, sex CHAR (2), age SMALLINT, jiguan CHAR (20), dept CHAR (10) DEFAULT 'computer ');

Create, modify, and delete data tables

Alter table student ADD address CHAR (30); ADD address column
Drop table student CASCADE | RESTRICT

Index creation and Deletion

Create index xuehao_index ON student (xuehao)
Drop index xuehao_index

Data ModificationUPDATE student SET age = age + 1 WHERE xuehao = '000000'

Data deletionDelete from student;

JDBC Programming

Jdbc: subprotocal: data source identifier
Jdbc: odbc: The university connects to the university database through the jdbc odbc bridge.
Jdbc: odbc: university? User = username & password = password connection with Parameters

Format of the connected network database

Jdbc: subprotocal: // [hostname] [: port] [dbname] [? Parameter1 = value1] [& parameter2 = value2] ......
Jdbc: microsoft: sqlserver: // localhost: 1433; dataBase = university? User = username & password = password

Use the JDBC driver to connect to the sqlserver database:

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);}

Connect using a 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 mainly used to execute SQL statements. You can use the createStatement () method of the Connection object to create a Statement object.

Statement statement=conn.createStatement();String sql="select * from student";ResultSet rs=statement.executeQuery(sql);

ResultSet accepts returned results
Use the executeUpdate method to execute the insert, delete, update, create, and drop statements.

String SQL = "delete from student where xuehao =" + "'000000'"; int imo-statement.exe cuteUpdate (SQL); // The returned value is the number of affected rows. System. out. println (I); public boolean execute () throws Exception

Used to execute unknown SQL statements and dynamically process unknown SQL statements.

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