idea测试web连接mysql数据库

用maven创建一个web架构

 

 

 pom文件

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>test3</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>test3 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.testng</groupId>
      <artifactId>testng</artifactId>
      <version>RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.8</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>
    </dependency>


  </dependencies>

  <build>
    <finalName>test3</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

 

druid.properties

# druid.properties文件的配置
#driverClassName=com.mysql.cj.jdbc.Driver
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.44.134:3306/2022?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
username=root
password=Root2022.
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大超时时间
maxWait=3000
#用来检测连接是否有效的sql
validationQuery=SELECT 1
#保证安全性！
testWhileIdle=true

 

数据库表结构以及内容

 

 

 

 

Brand.java

 

package pojo;
/*
 * 品牌
 *
 * alt+鼠标左键整列编辑
 * 在实体类中,基本数据类型建议使用其对应的包装类型
 * */
public class Brand {

    private  Integer id;

    private  String brandName;
    private  String companyName;
    private  Integer ordered;
    private  String description;
    private  Integer status;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

 

 

 

测试类BrandTest.java

package example;
/*
 *
 * 品牌的增删改查
 *1.SQL select * from tb_brand
 * 2.参数:不需要
 * 3,结果:List<Brand>
 * */


import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.testng.annotations.Test;
import pojo.Brand;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class BrandTest {
    //1,获取Connction
    //2,加载配置文件
    @Test
    public void testSelectAll() throws Exception {
        Properties pro = new Properties();
        pro.load(new FileInputStream("D:\\test3\\src\\main\\java\\druid.properties"));
        //3,获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
        //4,获取数据库链接Connection
        Connection conn = dataSource.getConnection();
        //5,定义SQL语句
        String sql = "select * from tb_brand;";
        //6,设置pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //7设置参数

        //8,执行SQL
        ResultSet rs = pstmt.executeQuery();
        //9处理结果List<Brand> 封装Brand对象,装载List集合
        Brand brand=null;
        List<Brand> brands=new ArrayList<Brand>();
        while (rs.next()) {
            //获取数据
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("odered");
            String description = rs.getString("description");
            int status = rs.getInt("status");
            //封装Brand对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            //装载集合
            brands.add(brand);
        }
        System.out.println(brands);
        //释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }

}

 

运行结果