JDBC-连接mysql_mysql poom-优快云博客

该博客介绍了Java项目的创建步骤。首先创建Maven，选择Maven类型并在poom中导入依赖；接着创建信息配置及日志输出文件，设置resources目录类型并创建相应文件；最后创建类并写入核心代码，包括初始化资源类、功能类、返回结果包装类和测试类。

一、创建Maven
①创建时选择Maven类型
在这里插入图片描述
②在poom中，导入依赖

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_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-jar-plugin</artifactId>
          <version>3.0.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>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>

二、创建信息配置以及日志输出文件
创建resources目录，点击右上角的Project Structure，将创建的resources目录设为Rescourses类型
在这里插入图片描述
创建两个文件分别用来存放配置信息和日志文件

配置信息：

driver =com.mysql.jdbc.Driver
url =jdbc:mysql://192.168.29.130:3306/kb06mysqltestdb?useUnicode=true&characterEncoding=utf-8&useSSL=true
username = root
password = javakb10

日志输出：

log4j.rootLogger=INFO, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=target/hadoop.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d %p [%c] - %m%n

三、创建类，写入核心代码
①初始化资源类

public class BaseConfig{
private class Config{
     String driver;
     String url;
     String username;
     String password;

    public Config(String driver,String url, String username, String password) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
    }
}

private Config config;

{
    try {
        init();
        Class.forName(config.driver);
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

public void init() throws Exception {
    String path = Thread.currentThread().getContextClassLoader().getResource("datasource.properties").getPath();
    Properties pro = new Properties();
    pro.load(new FileReader(path));
    String url = pro.getProperty("url");
    if (null==url) {
        throw new Exception("缺少url配置项异常");
    }
    String driver = pro.getProperty("driver","com.mysql.jdbc.Driver");
    
    //" USER_NAME " 处，输入对应的虚机的用户名
	//" USER_PASSWORD " 处，输入对应的虚机的密码
    String username = pro.getProperty("username", " USER_NAME ");
    String password = pro.getProperty("password", " USER_PASSWORD ");

    
    this.config = new Config(driver,url,username,password);
}

protected Connection getCon() throws SQLException {
    return DriverManager.getConnection(config.url,config.username,config.password);
}

protected void close(AutoCloseable...acs){
    for (AutoCloseable ac : acs) {
        if (ac!=null) {
            try {
                ac.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
}

②功能类

public class BaseDao extends BaseConfig{
    private PreparedStatement getPst(Connection con,String sql,Object...params) throws SQLException {
        PreparedStatement pst = con.prepareStatement(sql);
        if (params.length>0) {
            for (int i = 0; i < params.length; i++) {
               pst.setObject(i+1,params[i]);
            }
        }
        return pst;
    }

public Result exeNonQuery(String sql,Object...params){
    Connection con = null;
    PreparedStatement pst=null;
    try {
        con=getCon();
        pst=getPst(con,sql,params);
        return Result.succeed(pst.executeUpdate());
    } catch (SQLException e) {
        return Result.fail();
    }finally {
        close(pst,con);
    }
}

public Result exeQuery(String sql,Object...params){
    Connection con=null;
    PreparedStatement pst=null;
    ResultSet rst = null;
    try {
        con=getCon();
        pst=getPst(con,sql,params);
        rst=pst.executeQuery();
        List<List<String>> data = new ArrayList<>();
        if (rst!=null&&rst.next()) {
            final int CC = rst.getMetaData().getColumnCount();
            do{
                List<String> row = new ArrayList<>(CC);
                for (int i = 1; i <= CC; i++) {
                    row.add(rst.getObject(i).toString());
                }
                data.add(row);
            }while(rst.next());
        }
        return Result.succeed(data);
    } catch (SQLException e) {
        return  Result.fail();
    }finally {
        close(rst,pst,con);
    }
}
}

③返回结果包装类

public class Result<T>{

private boolean err;
private T data;

public Result(boolean err, T data) {
    this.err = err;
    this.data = data;
}

public boolean isErr() {
    return err;
}

public T getData() {
    return data;
}

public static <T>Result succeed(T data){
    return  new Result(false,data);
}
public static Result fail(){
    return new Result(true,null);
}
}

④测试类

public class Test {
    public static void main(String[] args) {
        BaseDao dao = new BaseDao();
        Result<List<List<String>>> result = dao.exeQuery("select * from student");

        if (!result.isErr()) {
            List<List<String>> data = result.getData();
            for (List<String> row : data) {
                for (String col : row) {
                    System.out.print(col+"\t");
                }
                System.out.println();
            }
        }else{
            System.out.println("查询异常");
        }
    }
}