JDBC 连接数据库，包含连接池

转载

单纯的老爷 2021-08-20 15:26:08 博主文章分类：JDBC

文章标签 sql java mysql 配置文件工具类 文章分类 Java 后端开发

1.不使用连接池方式(Jdbc)

1.1 工具类（JdbcUtil.java)

package com.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {

   private static String driver;
   private static String url;
   private static String username;
   private static String password;
   static {
       try {
           Properties props = new Properties();
           InputStream is = JdbcUtil.class.getResourceAsStream("/jdbc.properties");
           props.load(is);
           driver = props.getProperty("driver");
           url = props.getProperty("url");
           username = props.getProperty("username");
           password = props.getProperty("password");
           is.close();
       } catch (IOException e) {
           throw new ExceptionInInitializerError("获取数据库配置文件信息失败");
       }
       try {
           Class.forName(driver);
       } catch (ClassNotFoundException e) {
           throw new ExceptionInInitializerError("加载驱动失败");
       }
   }
   public static Connection getConnection(){
       try {
           Connection conn = DriverManager.getConnection(url,username,password);
           return conn;
       } catch (Exception e) {
           throw new ExceptionInInitializerError("连接数据库的url或用户名密码错误");
       }
   }
   public static void release(ResultSet rs,PreparedStatement ps,Connection conn){
       if(rs!=null){
           try {
               rs.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           rs = null;
       }
       if(ps!=null){
           try {
               ps.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           ps = null;
       }
       if(conn!=null){
           try {
               conn.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           conn = null;
       }
   }
   public static void main(String[] args) {
       // 以增加数据作为测试
       Connection conn = null;
       PreparedStatement ps = null;
       int result = 0;
       try {
           conn = JdbcUtil.getConnection();
           String sql = "insert into student(StudentNo,StudentName) values(1028,'袁华')";
           ps = conn.prepareStatement(sql);
           result = ps.executeUpdate();
       } catch (SQLException e) {
           e.printStackTrace();
       } finally{
           JdbcUtil.release(null, ps, conn);
       }
       if(result>0){
           System.out.println("插入成功");
       }else{
           System.out.println("插入失败");
       }

   }
}

1.2 配置文件(jdbc.properties)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool
username=root
password=123456

2.Dbcp

2.1 工具类（DbcpUtil.java)

package com.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;
/*
* DBCP数据源的使用
*/
public class DbcpUtil {
   static DataSource ds = null;
   static {
           // try   shift+alt+z
           try {
               Properties props = new Properties();
               InputStream is = DbcpUtil.class.getResourceAsStream("/dbcp.properties");
               props.load(is);
               // DBCP数据源
               ds = BasicDataSourceFactory.createDataSource(props);
           } catch (IOException e) {
               e.printStackTrace();
           } catch (Exception e) {
               e.printStackTrace();
           }

   }
   public static Connection getConnection(){
       try {
           return ds.getConnection();

       } catch (SQLException e) {
           throw new RuntimeException();
       }
   }
   public static void release(Connection conn){
       if(conn!=null){
           try {
               conn.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
   }
   public static void main(String[] args) throws SQLException {
       // 测试
       Connection conn = getConnection();
       String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1209','Jingjing',3,'jingjing@163.com')";
       PreparedStatement ps = conn.prepareStatement(sql);
       int result = ps.executeUpdate();
       if(result>0){
           System.out.println("插入成功");
       }
   }
}

1.2 配置文件(dbcp.properties)

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool
username=root
password=123456
initialSize=10
maxIdle=30
minIdle=3

3.C3p0

3.1 工具类（C3p0Util.java)

package com.jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;
/*
* C3p0数据源的使用
*/
public class C3p0Util {
   static ComboPooledDataSource ds = null;
   static {
       ds = new ComboPooledDataSource("jdbc-c3p0");
   }
   public static Connection getConnection(){
       try {
           return ds.getConnection();

       } catch (SQLException e) {
           throw new RuntimeException();
       }
   }
   public static void release(ResultSet rs,PreparedStatement ps,Connection conn){
       if(rs!=null){
           try {
               rs.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           rs = null;
       }
       if(ps!=null){
           try {
               ps.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           ps = null;
       }
       if(conn!=null){
           try {
               conn.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           conn = null;
       }
   }
   public static void main(String[] args) throws SQLException {
       // 测试
       Connection conn = getConnection();
       String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1027','fdafd',3,'fdfdaf@163.com')";
       PreparedStatement ps = conn.prepareStatement(sql);
       int result = ps.executeUpdate();
       if(result>0){
           System.out.println("插入成功");
       }
   }
}

3.2 配置文件(c3p0-config.properties)

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
   <default-config>
       <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/myschool</property>
        <property name="user">root</property>
        <property name="password">123456</property>
       <property name="initialPoolSize">10</property>
        <property name="maxPoolSize">20</property>
        <property name="minPoolSize">5</property>
   </default-config>

   <named-config name="jdbc-c3p0">
       <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/myschool</property>
        <property name="user">root</property>
        <property name="password">123456</property>
       <property name="initialPoolSize">10</property>
        <property name="maxPoolSize">20</property>
        <property name="minPoolSize">5</property>
   </named-config>
</c3p0-config>

4.Jndi(依赖于服务器)

4.1 工具类（JndiUtil.java)package com.jdbc.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
/*
* jdbc数据源连接
*/
public class JndiUtil {
   static DataSource ds= null;

   static {
       try {
           Context initCtx = new InitialContext();
           Context envCtx = (Context)initCtx.lookup("java:comp/env");
           ds = (DataSource)envCtx .lookup("mysql/jdbc");
       } catch (NamingException e) {
           e.printStackTrace();
       }
   }
   public static Connection getConnection(){
       try {
           return ds.getConnection();

       } catch (SQLException e) {
           throw new RuntimeException();
       }
   }
   public static void release(ResultSet rs,PreparedStatement ps,Connection conn){
       if(rs!=null){
           try {
               rs.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           rs = null;
       }
       if(ps!=null){
           try {
               ps.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           ps = null;
       }
       if(conn!=null){
           try {
               conn.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           conn = null;
       }
   }

   //要用到tomcat故放在jsp页面进行测试，开启tomcat，访问localhost:8080/jdbc/jndi.jsp进行测试
//   public static void main(String[] args) throws SQLException {
       //测试
//       Connection conn = JndiUtil.getConnection();
//       String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1028','jndi',3,'jndif@163.com')";
//       PreparedStatement ps = conn.prepareStatement(sql);
//       int result = ps.executeUpdate();
//       if(result>0){
//           System.out.println("插入成功");
//       }
//   }
}

4.2 配置文件（context.xml)

<?xml version="1.0" encoding="UTF-8"?>
<Context>
   <Resource name="mysql/jdbc"
           auth="Container"
            type="javax.sql.DataSource"
            username="root"
            password="123456"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/myschool"
            maxActive="8"
            maxIdle="4"/>
</Context>

4.3 jndi.jsp(当然也可用servlet)

<%@page import="java.sql.PreparedStatement"%>
<%@page import="com.jdbc.util.JndiUtil"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

    <title>这个页面为测试JNDI连接池的</title>

</head>

<body>
    <%
    // 这个页面为测试JNDI连接池的
    Connection conn = JndiUtil.getConnection();
   String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1032','哈哈',4,'hehef@163.com')";
   PreparedStatement ps = conn.prepareStatement(sql);
   int result = ps.executeUpdate();
   if(result>0){
       out.println("插入成功");
   }else {
       out.println("插入失败");
   }
   JndiUtil.release(null, ps, conn);
    %>
</body>
</html>