java server development servlet: 3. Servlet using jdbc to access database: add employee information to mysql database

Catalog

I. Preface

2, Add employee information and insert it into mysql database

1. HTML form submission Code: addEmp.html

2. Profile code: web.xml

3. Java code: AddEmpServlet.java

4. Building database and table

(1) Log in to mysql

(2) View which databases currently exist

(3) Create a new database

(4) Use a database

(5) View which tables are in the current database

(6) building tables

(7) query table

5. mysql latest driver download, web project configuration load driver

6. Effect demonstration

6. Report abnormality

I. Preface

The previous article introduced some theoretical knowledge points: Servlet using jdbc to access database, mysql simple use, redirection, etc. For details, please refer to the blog: Original java server development servlet: 3. Servlet using jdbc to access database, simple use of mysql, redirection This article mainly introduces the case of Servlet using jdbc to access database: adding employee information to mysql database

2, Add employee information and insert it into mysql database

Since it is a warehousing operation, you must first install and configure mysql database. See the blog for details: Download, install and configure MySQL under the original Mac system

1. HTML form submission Code: addEmp.html

<html>
<head>
	<!-- Simulate a message header(content-type) content Inside: "text/Webpage,Character encoding "-->
	<meta http-equiv="content-type" content="text/html;charset=utf-8"/>
</head>
<!-- body Define the overall style -->
<body style="font-size:18px; font-style:fond; color:red; ">
	<!-- action="add",Corresponding web.xml Chinese label url-pattern Li"/add" -->
	<form action="add" method="post">
		<fieldset>
			<legend>Enter employee information</legend>
				Full name:<input name="name" /><br />
				Salary:<input name="salary" /><br />
				Age:<input name="age" /><br />
				<input type="submit" value="Submission" />
		</fieldset>
	</form>
</body>
</html>

2. Profile code: web.xml

Paste out the whole configuration file, t03 will conflict with t04, so the comment is just input and display employee information, not inserted into the database.

For the case of this article, the main key reference is "t04" add employee information and insert mysql ". Others can be ignored and deleted

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
	version="3.1">
	
	<display-name>web02</display-name>
	
	<!-- t01_Chinese parameters for form submission_Solve coding problems  -->
	<servlet>
		<servlet-name>web02</servlet-name>
		<servlet-class>t01_Chinese parameters for form submission_Solve coding problems.HelloServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>web02</servlet-name>
		<url-pattern>/hello</url-pattern>
	</servlet-mapping>
	
	<!-- t02_Form submit multiple parameters_Receive response processing  -->
	<servlet>
		<servlet-name>web02_two</servlet-name>
		<servlet-class>t02_Form submit multiple parameters_Receive response processing.HelloServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>web02_two</servlet-name>
		<url-pattern>/hello_many</url-pattern>
	</servlet-mapping>
	
	<!-- t03_Enter employee data_feel get and post Submission mode  -->
<!-- 	<servlet>
		<servlet-name>addEmp</servlet-name>
		<servlet-class> t03_Enter employee data_feel get and post Submission mode.AddEmpServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>addEmp</servlet-name>
		<url-pattern>/add</url-pattern>
	</servlet-mapping> -->
	
	<!-- t04_Add employee information insert mysql  -->
	<servlet>
		<servlet-name>addEmp</servlet-name>
		<servlet-class>t04_Add employee information insert mysql.AddEmpServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>addEmp</servlet-name>
		<url-pattern>/add</url-pattern>
	</servlet-mapping>
	
</web-app>

3. Java code: AddEmpServlet.java

package t04_Add employee information insert mysql;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Insert employee information into mysql
 * @UpdateTime: 2011 February 28, 2007 17:30:00 PM
 * @ProjectName:  [Project name]
 * @ClassName: [Class name]
 * @CategoryName: [Type] such as Activity
 * @author: luminal,Email: luminal_yyh@163.com
 * @since 1.0[Version]
 * @Description: (The function of this class can be described here.)
 * 
 * Request address for inserting employee information into mysql
 * http://localhost:8080/web02/addEmp.html
 */
public class AddEmpServlet extends HttpServlet{
	public void service(HttpServletRequest request,
			HttpServletResponse response) 
	throws ServletException,IOException{
		
		request.setCharacterEncoding("utf-8");
		
		//Read request parameters
		String name = request.getParameter("name");
		String salary = request.getParameter("salary");
		String age = request.getParameter("age");
		
		//Server authentication must be available. Omit here
		response.setContentType(
				"text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		//Insert employee information into database [see JDBC knowledge point for details]
		//First, you need to build a database jsd1108db in mysql,
		//To create a table t [EMP], you can perform the following operations [mySql database and table, see the document for details]
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			//Class.forName("com.mysql.jdbc.Driver"); / / prompt for deprecation
			Class.forName("com.mysql.cj.jdbc.Driver");
			System.out.println("Success loading Mysql Driver!");
			
			conn = 
				DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/jsd1108db",
						"root","Your mysql Password");
			System.out.println("Success connect Mysql server!");  
			
			prep = 
				conn.prepareStatement("insert into " +
						"t_emp(name,salary,age) " +
						"values(?,?,?)");
			prep.setString(1, name);
			//salary written as sal and reported as NullPointerException
			//Execute the "system is busy, try again later"
			prep.setDouble(2, Double.parseDouble(salary));
			prep.setInt(3, Integer.parseInt(age));
			prep.executeUpdate();
			
out.println("Insert success");

		} catch (Exception e) {
			//Log
			e.printStackTrace();
			//Judge whether the abnormality can be recovered if
			//Be able to recover immediately; if not
			//Recovery (system exception, such as database service pause,
			//If the network connection to the database is interrupted), the user will be prompted to try again later.
			out.println("System busy, try again later");
		}finally{
			if(prep != null){
				try {
					prep.close();
				} catch (SQLException e) {
					//It must not be executed, so the log has not been printed
					}
			}
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					
					}
			}
		}
		
		
	}
}

4. Building database and table

Before you run the web project demonstration, you need to manually set up the database and tables

(1) Log in to mysql

cd /usr/local/mysql/bin

mysql -u root -p

password: q. . . 4

(2) View which databases currently exist

show databases;

(3) Create a new database

For example, create a database named jsd1108db,

The character set used in this database is utf-8

create database jsd1108db default character set utf8;

(4) Use a database

use jsd1108db;

(5) View the tables in the current database

show tables;

(6) building tables

create table t_emp(

id int primary key auto_increment,

name varchar(50),

salary double,

age int

);

(7) query table

select *from t_emp;

$ cd /usr/local/mysql/bin
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database jsd1108db default character set utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jsd1108db          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use jsd1108db;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t_emp(
    -> 
    -> id int primary key auto_increment,
    -> 
    -> name varchar(50),
    -> 
    -> salary double,
    -> 
    -> age int
    -> 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------------+
| Tables_in_jsd1108db |
+---------------------+
| t_emp               |
+---------------------+
1 row in set (0.00 sec)

mysql> select *from t_emp;
Empty set (0.00 sec)

mysql> 

 

5. mysql latest driver download, web project configuration load driver

Download address on official website: https://dev.mysql.com/downloads/connector/j/

After downloading, unzip it, as shown in the following figure:

     

web project configuration load driver

Copy the extracted jar package to the WebRoot/WEB-INF/lib / directory, and then perform the following operations:

  

6. Effect demonstration

Let's demonstrate: start tomact, deploy the web project, and enter in Google Browser: http://localhost:8080/web02/addEmp.html

     

Insert successfully, then we can open the terminal to view the data information:

Two pieces of data I submitted can be seen.

 

6. Report abnormality

There are two exceptions and solutions during sorting:

(1) "com.mysql.jdbc.Driver" prompt is deprecated

//Class.forName("com.mysql.jdbc.Driver"); / / prompt for deprecation
Class.forName("com.mysql.cj.jdbc.Driver");

(2)The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received

 

 

 

 

 

 

 

297 original articles published, 56 praised, 140000 visitors+
Private letter follow

Tags: MySQL Database Java JDBC

Posted on Sun, 15 Mar 2020 19:06:09 -0700 by dahwan