jsp connecting database in idea

Posted by cheshil on Mon, 22 Nov 2021 19:58:32 +0100

Software version used this time:

IDEA: 2019.1.3 Professional Edition

mysql: mysql 8.0.16

jdk: 17.0.1

tomcat: tomcat 9.0

JDBC version: 8.0.16

Preliminary preparation:

1. Put the JDBC driver in the lib folder of tomcat

2. Build a database in mysql, mine is "mydata", and then execute the following two pieces of code.

CREATE TABLE `websites` (
	`name` CHAR ( 20 ) NOT NULL DEFAULT '' COMMENT 'Site name',
	`url` VARCHAR ( 255 ) NOT NULL DEFAULT '',
	`alexa` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT 'Alexa ranking',
	`country` CHAR ( 10 ) NOT NULL DEFAULT '' COMMENT 'country',
PRIMARY KEY ( `id` ) 
INSERT INTO `websites`
	( '1', 'Baidu', 'https://www.baidu.cm/', '1', 'CN' ),
	( '2', 'CSDN', 'https://www.csdn.net', '1', 'CN' ),
	( '3', 'tencent', 'http://www.qq.com', '1', 'CN' ),
	( '4', 'micro-blog', 'http://weibo.com/', '1', 'CN' ),
	( '5', 'TaoBao', 'https://www.taobao.com/', '1', 'CN' );

Official start:

1. Create a new project

  2. Select web Application under java (tick)  

  3. Enter the project name (if you directly enter the project name, click finish and a window may pop up to prompt you that you do not have this directory. Just click ok and a directory will be created for you automatically)  

4. After entering, first build two folders in Web inf, namely classes and lib;

Built look

5. Then we set it up in the project

6. Click in turn   Modules-Paths; Then click use module compile output path to set the following two to the newly created classes directory.

7. Put the JDBC driver in the lib folder (just copy it directly)

8. Directly copy the following code into index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
<!DOCTYPE html>
  <meta charset="UTF-8">
  <title>Insert title here</title></head>
  String url = "jdbc:mysql://localhost:3306/mydata?useSSL=false&serverTimezone=UTC";
  //    Loading drivers after version 8.0
  //    3306 is the port number, which is set according to its own database port--
  //    ... 3306 / "database name"? useSSL......
  //    Replace com.mysql.jdbc.Driver with com.mysql.cj.jdbc.Driver.
  //    If MySQL version 8.0 or above does not need to establish an SSL connection, it needs to be turned off.

  //    Class.forName("com.mysql.jdbc.Driver");
  //    String url = "jdbc:mysql://localhost:3306/world";
  //    Loading driving mode before version 8.0

  String un = "root";         //MySql user name
  String pa = "root";       //MySql password
  Connection con = DriverManager.getConnection(url, un, pa);
  Statement st = con.createStatement();
  String sql = "select * from websites";
  ResultSet rs = st.executeQuery(sql);
<table border="1" cellpadding="0" cellspacing="0">
  <caption>User list</caption>
    <td>user name</td>
  <%while (rs.next()) {
    <td><%=rs.getInt("id") %>
    <td><%=rs.getString("name") %>
    <td><%=rs.getString("url") %>
  <%} %></table>

9. Change to your database name and mysql user name and password respectively  

  10. Start configuring tomcat and click app config  

11. Click this "+"


12. Select Tomcat server local; Maybe you didn't find this by clicking the plus sign. The idea is put away. There are more items below. You can click to expand it.

  13. 1 is your tomcat and 2 is the port number; My 8080 port is occupied, so I change another one directly here every time; 2 there are no aritifacts. Click fix directly and it will be added by itself. It is recommended to change the Application context to your project name. Then click aplay OK;

14. Click on the triangle and wait.  

This article refers to the following articles:

JSP projects in IDEA connect to MySQL database through jdbc. The MySQL version cannot be connected. Solution to the problem. Connection differences before and after MySQL 8.0_ Bad boy_ Liu's blog - Programmer's Secret - Programmer's Secret

Topics: MySQL intellij-idea