JDBC连接数据库超详细过程

原创声明：所有内容均由所学知识书写。

---

前言

本文详细介绍如何利用druid工具包和dbutils工具包来简化dao工厂并连接数据库以及进行一系列的增删查改操作

git项目地址

---

一、环境准备

idea开发工具

mysql数据库8.0.17

准备好新建一个数据库
新建数据库代码以及表参考：

show databases;
create database if not exists demo;
use demo;
create table if not exists stuEmp(
	`id` int(255) NOT NULL AUTO_INCREMENT,
	`stu` varchar(255) not null,
	`age` int(100) not null,
	PRIMARY KEY(`id`)
	); 

这里我新建的数据库名叫做demo，新建在此库下新建了一个stuEmp表，里面的各种详细参数有id,stu(名字),age(年龄)。

二、工具包下载

工具包都可以百度搜索，找到官网下载。我这里提供我所使用的工具包下载链接：

提取码：6666

• 工具集

三、项目框架

四、具体细节

1. DaoFactory.class

package dao;

import java.io.IOException;
import java.util.Properties;

public class DaoFactory {
  private static Properties prop = new Properties();
  static {
    try {
      prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("properties/dao.properties"));
    } catch (IOException e) {
      System.err.println("在classpath下未找到dao.properties文件，请检查!");
      e.printStackTrace();
    }
  }
  public static Object getInstance(String daoName){
    Object obj = null;
    if(null != daoName && daoName.length()>0){
      try {
        obj = Class.forName(prop.getProperty(daoName)).newInstance();
      } catch (ClassNotFoundException e) {
        e.printStackTrace();
      } catch (IllegalAccessException e) {
        e.printStackTrace();
      } catch (InstantiationException e) {
        e.printStackTrace();
      }
    }else{
      System.out.println("未输入指定Dao名称");
    }
    return obj;
  }
}

2. StuDao.class

package dao;

import java.util.List;
import object.Stu;

public interface StuDao {
//提供一些常用的功能，例如数据库的增删查改
  public void addElement(Stu stu);
  public void delete(int id);//根据id删除元素
  public List<Stu> findAll();//列出所有成员
  public void update(Stu stu);//改
}

3. StuDaoImpl.class

package dao;

import static java.lang.Integer.parseInt;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import object.Stu;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import tool.ConnectionTool;

public class StuDaoImpl implements StuDao{

  @Override
  public void addElement(Stu stu) {
/*
  insert into `stuEmp`(`stu`,`age`) values ('娄黔子',22);
 */
  Connection conn = ConnectionTool.getConnectionByDataSource();
  String sql = "INSERT INTO stuEmp(`stu`,`age`) VALUES (?,?)";
  QueryRunner qr = new QueryRunner();
    try {
      conn.setAutoCommit(false);
      String ro = qr.insert(conn, sql,new ScalarHandler<>(),stu.getStu(), stu.getAge()).toString();
      int row = parseInt(ro);
      System.out.println("新增了id为"+row+"的数据");
      DbUtils.commitAndCloseQuietly(conn);
    } catch (SQLException e) {
      DbUtils.rollbackAndCloseQuietly(conn);
      e.printStackTrace();
    }
  }

  @Override
  public void delete(int id) {
/*
  delete from stuEmp where id = ?
 */
    Connection conn = ConnectionTool.getConnectionByDataSource();
    QueryRunner qr = new QueryRunner();
    String sql = "delete from stuEmp where id = ?";
    try {
      conn.setAutoCommit(false);
      int row = qr.update(conn, sql, id);
      System.out.println("删除了id为"+id+"的数据");
      DbUtils.commitAndCloseQuietly(conn);
    } catch (SQLException e) {
      DbUtils.rollbackAndCloseQuietly(conn);
      e.printStackTrace();
    }
  }

  @Override
  public List<Stu> findAll() {
    /*
    select * from stuEmp;
     */
    List<Stu> stuList = new ArrayList<>();
    QueryRunner qr = new QueryRunner();
    Connection conn = ConnectionTool.getConnectionByDataSource();
    String sql = "select * from stuEmp;";
    try {
      stuList=qr.query(conn,sql,new BeanListHandler<>(Stu.class));
      DbUtils.commitAndCloseQuietly(conn);
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return stuList;
  }

  @Override
  public void update(Stu stu) {
    /*
    update stuEmp set stu=?,age=? where id=?
     */
    QueryRunner qr = new QueryRunner();
    Connection conn = ConnectionTool.getConnectionByDataSource();
    String sql = "update stuEmp set stu=?,age=? where id=?";
    try {
      conn.setAutoCommit(false);
      int row = qr.update(conn, sql, stu.getStu(), stu.getAge(), stu.getId());
      System.out.println("更新了"+row+"行数据．");
      DbUtils.commitAndCloseQuietly(conn);
    } catch (SQLException e) {
      DbUtils.rollbackAndCloseQuietly(conn);
      e.printStackTrace();
    }

  }
}

4. Stu.class

package object;

public class Stu {
  private int id;
  private String stu;
  private int age;

  public Stu() {
  }

  public Stu(int id, String stu, int age) {
    this.id = id;
    this.stu = stu;
    this.age = age;
  }

  public int getId() {
    return id;
  }

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

  public String getStu() {
    return stu;
  }

  public void setStu(String stu) {
    this.stu = stu;
  }

  public int getAge() {
    return age;
  }

  public void setAge(int age) {
    this.age = age;
  }

  @Override
  public String toString() {
    return "Stu{" +
        "id=" + id +
        ", stu='" + stu + '\'' +
        ", age=" + age +
        '}';
  }
}

5. dao.properties

daoname=dao.StuDaoImpl

6. druid.properties

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
username=root
password=123456
#druid配置初始化大小、最小、最大
initialSize=1
minIdle=10
maxActive=20
#配置获取连接等待超时的时间
maxWait=60000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis=300000
validationQuery=SELECT  'x'
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
#配置监控统计拦截的filters
filters=stat
#其他配置
maxOpenPreparedStatements=20
removeAbandoned=true
removeAbandonedTimeout=1800
logAbandoned=true
————————————————
版权声明：本文为CSDN博主「娄黔子」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/herojeo/article/details/114573893

7. ConnectionTool.class

package tool;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectionTool {
  private static Properties prop = new Properties();
  private static DruidDataSource dds =null;
static {
  try {
    prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("properties/druid.properties"));
    dds = (DruidDataSource)DruidDataSourceFactory.createDataSource(prop);
    dds.setUsername(prop.getProperty("username"));
    dds.setPassword(prop.getProperty("password"));
    dds.setUrl(prop.getProperty("url"));
    dds.setDriverClassName(prop.getProperty("driverClass"));
  } catch (IOException e) {
    e.printStackTrace();
  } catch (Exception e) {
    e.printStackTrace();
  }
}

  public static Connection getConnectionByDataSource(){
    Connection conn = null;
    try {
        conn = dds.getConnection();
    }  catch (SQLException e) {
      e.printStackTrace();
    }
    return conn;
  }
}

五、运行结果

1. run.class

import dao.DaoFactory;
import dao.StuDaoImpl;
import object.Stu;

public class Run {

  public static void main(String[] args) {
    StuDaoImpl st = (StuDaoImpl) DaoFactory.getInstance("daoname");
//    st.addElement(new Stu(1,"王五",35));//增
//    st.delete(6);//删
    st.findAll().forEach(System.out::println);//查
//    st.update(new Stu(1,"王五",35));//改

  }

}

2. idea控制台模拟运行结果

这里只用了一行查的代码
后续可根据业务需求添加新的功能

该处使用的url网络请求的数据。

---