Java IDEA maven连接MySQL8

待我称王封你为后i 2024-04-17 05:36 48阅读 0赞

数据库：  
![在这里插入图片描述][20190822210644739.png]  
创建数据库  
代码

create database maven;
    create table user(
    sid int primary key auto_increment,
    sname varchar(20) not null
    );

dao接口：  
代码：

package cn.lys.dao;
    
    import cn.lys.domain.Person;
    
    import java.sql.SQLException;
    import java.util.List;
    
    public interface Dao {
        public abstract List<Person> findAll() throws Exception;
    }

daoimpl:

package cn.lys.dao.impl;
    import cn.lys.dao.Dao;
    import cn.lys.domain.Person;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    public class DaoImpl implements Dao {
        public List<Person> findAll() throws Exception {
            List<Person> list = new ArrayList<Person>();
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/maven?serverTimezone=GMT%2B8&useSSL=false","root","root");
                preparedStatement = connection.prepareStatement("select * from user");
                resultSet = preparedStatement.executeQuery();
                //把结果集转换为list集合
                while (resultSet.next()){
                    Person person = new Person();
                    person.setId(resultSet.getInt("sid"));
                    person.setUsername(resultSet.getString("sname"));
                    list.add(person);
                }
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            }
            return list;
        }
    }

注：  
本人的数据库是MySql 8，url中的一部分内容是用来解决时区问题的。

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>cn.lys</groupId>
        <artifactId>test_maven_sql</artifactId>
        <version>1.0-SNAPSHOT</version>
        <dependencies>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.11</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
        </dependencies>
    </project>

test文件：  
代码：

package cn.lys.test;
    
    import cn.lys.dao.Dao;
    import cn.lys.dao.impl.DaoImpl;
    import cn.lys.domain.Person;
    import org.junit.Test;
    
    import java.util.List;
    
    public class test {
        @Test
        public void findAllTest(){
            Dao dao = new DaoImpl();
            try {
                List<Person> list =  dao.findAll();
                for (Person person : list) {
                    System.out.println(person.getId());
                    System.out.println(person.getUsername());
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

[20190822210644739.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/f06151cf26c247febf48e89d2b293697.png