Directory
1. The basics of database syntax
Two, JDBC foundation
Three, JDBC commonly used classes and interfaces
Fourth, the main steps of the database connection
Five, prepared statement
6. Example: Adding student records with a form
7. What is JavaBean
Eight, JavaBean specification
Nine, the creation of JavaBean
1. The structure of JavaBean
2. JavaBean creation steps
3. JavaBean properties
10. Using JavaBean in JSP
1. Description:
2. Use: There are two ways
3. Label
4. The meaning of each parameter:
5. The meaning of each parameter of Scope
6. Label
7. Label
11. DAO and VO
1. Why do we need DAO and VO?
2. Write DAO and VO
3. Using DAO and VO in JSP
1. Database Syntax Basics
Mysql database
Second, JDBC basics
JDBC is the abbreviation of Java DataBase Connectivity (Java database connection) technology.
Is a Java API that can be used to execute SQL statements.
It consists of some classes and interfaces written in Java language.
JDBC has two packages:
java.sql: core package, the classes in this package mainly complete the basic operations of the database, such as generating connections, executing SQL statements, preprocessing SQL statements, etc.;
javax.sql: extension package, which mainly provides interfaces and classes for advanced database operations.
3. JDBC commonly used classes and interfaces
Driver interface: Creates connections internally
DriverManager class: load the driver, create a connection
Connection interface: use this type of object to create Statement or PreparedStatement objects when programming, etc.
Statement interface: use this type of object to execute SQL statements during programming, and query operations can get ResultSet objects PreparedStatement interface: interface for preprocessing SQL statements ResultSet interface: interface for result sets
ResultSetMetaData interface: the metadata interface of the result set
DatabaseMetaData interface: the metadata interface of the database
4. Main steps of database connection
1. Register and load a specific driver;
2. Create a link Connection object;
3. Use the Connection object to generate a Statement object or PrepareStatement object;
4. Use the Statement object or preprocessing command object to execute SQL statements, such as query, update, insert, delete, etc.;
5. If the query statement is executed, the data must be read from the ResultSet;
6. Close ResultSet, Statement (or Statement), Connection, etc.;
Core code:
<%
//1. Load the driver
Class.forName(“com.mysql.jdbc.Driver”);
//2. Establish a connection with the database
String url = “jdbc:mysql://172.16.26.19:3306/computer_dept”;
Connection conn = DriverManager.getConnection(url, “root”, “sise”);
//3. Create command object
Statement st=conn.createStatement();
//4. Execute the SQL statement
String sql = “SELECT * FROM STUDENTS”;
ResultSet rs=st. executeQuery(sql);
//5. Get and output query results
out.println(“
| Student Number | Name | Gender | Profession |
| ” + rs.getString(“num”) + “ | ” + rs.getString(“name”) + “ | ” + rs.getString(“gender”) + “ | ” + rs.getString(“major”) + “ |
“);
//6. Release resources
rs.close();st.close();conn.close();
%>
5. Prepared statement
1. Question raised
When sending an SQL statement to the database, such as “Select * from students”, the SQL interpreter in the database will be responsible for generating the underlying internal command from the SQL statement, and then execute the command to complete the relevant data operation;
If you continuously submit SQL statements to the database, it will inevitably increase the burden on the SQL interpreter in the database and affect the execution speed;
If the application can interpret the SQL statement as an internal command at the bottom of the database in advance for the connected database, and then directly let the database execute the command, it obviously not only reduces the burden on the database, but also improves the speed of accessing the database.
2. Problem solving
For JDBC, after the connection to the database is established, the connection object can be used to call the prepareStatement(String sql) method to precompile the SQL statement, generate the underlying internal command of the database, and encapsulate the command in the PreparedStatement object. Calling the corresponding method can make the underlying internal command executed by the database;
SQL statements are supplied as parameters when creating a PreparedStatement object; since only the location of these values is known, they are denoted by the ? notation. When the SQL statement is run, the actual value will be set.
Finally, call the execute() [or executeQuery(), executeUpdate()] method of the PreparedStatement object to perform related operations.
Using PreparedStatement can also effectively prevent SQL injection
6. Example: Adding student records with a form
key code:
<%
request.setCharacterEncoding("utf-8");//Solve the problem of submitting garbled characters %>
Add student information<br>
<form method="post">
Enter student number: <input type="text" name="stuno"><br>
Enter name: <input type="text" name="stuname"><br>
Select gender: <input type="radio" name="sex" value="male" checked>male
<input type="radio" name="sex" value="female">female
<input type="submit" value="Add">
</form>
<%
String stuno = request. getParameter("stuno");
String stuname = request. getParameter("stuname");
String stusex = request. getParameter("sex");
if (stuno!=null){
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/computer_dept","root","12345678");
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select stuno from students where num=?";
ps=conn. prepareStatement(sql);
ps.setString(1,stuno);
rs = ps. executeQuery();
if(rs. next()){
out.println("Sorry, the school ID already exists!");
}
else {
sql = "insert into students(num,name,gender) values (?,?,?)";
ps = conn. prepareStatement(sql);
ps.setString(1,stuno);
ps.setString(2,stuname);
ps. setString(3, stusex);
ps. executeUpdate();
out.println("Added successfully!");
}
}
%>
Seven, what is JavaBean
(1) Presentation of the question:
In JSP, html tags are allowed to be mixed with Java language programming, which will cause some problems:
Unclear structure: business logic mixed with display
The program cannot be reused: the program can only be reused by copying the code. The above two problems can be solved after the introduction of JavaBean
(2) What is JavaBean?
JavaBean is a reusable, Java-based software component that can be intuitively manipulated in software development tools.
JavaBean is a Java class that becomes an object with certain functions or handles a certain business by encapsulating properties and methods, referred to as Bean. It can be seen that as long as the Java public class conforms to the JavaBean specification, it can be called a JavaBean.
Eight, JavaBean specification
1. Must be a Java class with public permission
2. There must be a parameterless constructor;
3. Its attributes should be limited to private or protected;
4. For the attribute named XXX, the method to read the attribute is named getXxx() (boolean type is isXxx()), and the attribute value is setXxx (parameter);
5. Implement the Serialable interface so that components can be serialized.
Advantages of using JavaBean:
Code can be reused to shorten development time;
Easy to write, easy to maintain, easy to use;
It can be used on different platforms without recompilation, bringing more scalability to JSP applications.
Nine, JavaBean creation
1. The structure of JavaBean
2. JavaBean creation steps
Create a package in the src directory with a custom name, such as: beans;
Create a public class;
Set the getter/setter method of the property.
public boolean isMale() {
return male;
}
public void setMale(boolean male) {
this.male = male;
}
public String getEducation() {
return education;
}
public void setEducation(String education) {
this.education = education;
}
public void setEmail(String email) {
this.email = email;
}
public String getEmail() {
return email;
}
}
question:
(1) How many properties does UserBean define?
(2) How to set and get the attribute value?
(3) What function does isMale() implement?
(4) Is there a default constructor?
3, JavaBean attribute
According to whether it can be read and written, it can be divided into: read-only, write-only, and read-write;
According to the complexity of the attribute, it can be divided into: simple attribute and index attribute:
Simple attributes: Non-array attributes. The attributes introduced earlier (such as: name, male, email, etc.) are all of this type, and an attribute can only take one value.
Format:
public Type getPropertyName() or isPropertyName()
public void setPropertyName(Type newValue)
Index property: Array property. An attribute can take multiple values, such as: interests, specialties, shopping, etc.
Format:
public Type getPropertyName(int index)
public void setPropertyName(int index,Type value)
public Type[] getPropertyName()
public void setPropertyName(Type[])
10. Using JavaBean in JSP
1, Description:
It should be created in a certain package, and does not support creation and use in the default package;
The class file formed after compilation is usually located in the subdirectory of the corresponding package of WEB-INF/classes.
2. Use: There are two ways
Coding method: Similar to general Java programs, create JavaBean objects in scripts and call corresponding methods. This method is easy to understand, but the code is longer;
Tag method: JSP specifies three tags to operate JavaBean, namely:
4. The meaning of each parameter:
id – object instance name
scope–Bean scope of action, the default is page, valid on a certain page, it has 4 possible values: page, request, session, application
class–Bean class name and other attributes are rarely used, so they will not be explained one by one. (MyFriend (attribute: name, mobile))
5. The meaning of each parameter of Scope
page – Covers only pages using JavaBean.
request – The valid scope is limited to the request using the JavaBean.
session–The valid scope is during the user’s entire connection process (the entire session phase is valid)
application – Valid scope covers the entire application. That is, it is valid for the entire website.
Description: page, request, session, and application have the function of containers, you can store objects in them, and you can also take out the content of the specified object: store object format: container object.setAttribute(object name, object); take out object content format: container object.getAttribute(object name).
|property=”propertyName” value=”property value”
|property=”propertyName” param=”paramName”/>
Function: Set the attribute value of the JavaBean instance, which is equivalent to: <% instance name.set method name (actual parameter list); %>
<% zhang.setMobile("1392345678"); %>
Function: Get the value of the specified property of the JavaBean instance, which is equivalent to beanName.getPropertyName() method call (getXxx() method)
<% instance name.get method name ( ); %>
Last Name First Name:
jsp:getProperty name=”zhang” property=”name”/>
phone number:
<%=zhang. getMobile()%>
Eleventh, DAO and VO
Using JDBC to access the database is an operation we often perform. Due to the many steps of the operation, it is easy to make mistakes. For this reason, JavaBean can be used for encapsulation and can be used repeatedly.
1. Why do you need DAO and VO?
JSP is the presentation layer. It is best not to connect to the database and manipulate data in JSP, but to hand over these operations to special classes to complete;
DAO (Data Access Object) class is responsible for the access and operation of the database. DAO encapsulates each queried record into a VO object, and then stores all instantiated VOs in a collection (such as: ArrayList object) and returns it to JSP;
VO (Value Object): used with DAO. In DAO, each record obtained by the query is encapsulated into VO, such as the Student object is VO.
2, write DAO and VO
VO is a JavaBean object
public class Student {
private String stuno;
private String stuname;
private String stusex;
public String getStuno() {
return stuno;
}
public void setStuno(String stuno) {
this. stuno = stuno;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public String getStusex() {
return stusex;
}
public void setStusex(String stusex) {
this. stusex = stusex;
}
}
DAO includes database connections and operations, encapsulates query results into VO, and returns them in ArrayList.
package dao;
import java.sql.*;
import java.util.ArrayList;
import bean. Student;
public class StudentDao {
private Connection conn = null;
public void initConnection() throws Exception {
// connect to MySQL
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/goodedu","scott", "tiger");
}
// return all students
public ArrayList<Student> getAllStudents() throws Exception {
ArrayList<Student> al = new ArrayList<Student>();
initConnection();
String sql = "SELECT STUNO,STUNNAME,STUSEX FROM T_STUDENT";
Statement stat = conn.createStatement();
ResultSet rs = stat. executeQuery(sql);
while (rs. next()) {
Student stu = new Student();
stu.setStuno(rs.getString("STUNO"));
stu.setStuname(rs.getString("STUNNAME"));
stu.setStusex(rs.getString("STUSEX"));
al. add(stu);
}
closeConnection();
return al;
}
public void closeConnection() throws Exception {
conn. close();
}
}
ArrayList
3, use DAO and VO in JSP
Steps for usage:
Import the DAO class and VO class in the page command;
Access the database through DAO, and put the query results in ArrayList;
Display the content of each VO object in the ArrayList.
Advantages of using DAO and VO:
By encapsulating JDBC-related codes into DAO, users do not need to understand the details of database operations, which greatly reduces program codes and facilitates division of labor during development.
How to design and name DAO and VO?
DAO is usually named XXXDao, XXX corresponds to the entity in the system, such as: StudentDao;
The name of VO corresponds to the entity in the system, such as: Student class.