jdbc与druid连接池的使用详解

package org.example.utils;

import java.sql.*;

public class JavaDateConnection {

  /**

   * 获取数据库连接

   * @return Connection

   */

  public Connection getConn() {

  //project为数据库名

   String url = "jdbc:mysql://localhost:3306/project" ;

   //用户名

   String username = "root" ;

   //密码

   String password = "Hyk59308" ;

   Connection conn = null ;

   try {

   //注册驱动

    Class.forName( "com.mysql.jdbc.Driver" );

    //classLoader,加载对应驱动

    conn = DriverManager.getConnection(url, username, password);

   } catch (ClassNotFoundException e) {

    e.printStackTrace();

   } catch (SQLException e) {

    e.printStackTrace();

   }

   return conn;

  }

String sql1= "insert into myTable values(?,?,?,?)" ; //定义sql语句

         String sql2= "select * from myTable" ;    //定义sql语句

         int result= 0 ;    //修改操作的返回值是一个整数，即受影响的行数

`` /**

              * PreparedStatement继承自Statement接口,PreparedStatement的对象已预编译过，

              * 执行速度快于Statement对象,创建其对象时，需要SQL命令字符串作为对象

              */

             PreparedStatement ps=connection.prepareStatement(sql1);  

             ps.setString( 1 , "tanker" );

             ps.setString( 2 , "m" );

             ps.setString( 3 , "1991-11-20" );

             ps.setString( 4 , "Franch" );

             result=ps.executeUpdate();

             if (result> 0 )

                 System.out.println( "插入成功" );

             else

                 System.out.println( "插入失败" );

             //Statement用于将sql语句发送到数据库

             Statement statement=connection.createStatement();

             //执行数据库操作返回的结果集,其定义的是数据库游标

             ResultSet results=statement.executeQuery(sql2);

             System.out.println( "name" + " " + "sex" + " " + "birth" + " " + "birthaddr" );

             System.out.println( "------------------------" );

             while (results.next())

             {

                 System.out.println(results.getString( "name" )+ " " +

                               results.getString( "sex" )+ " " +

                               results.getString( "birth" )+ " " +

                               results.getString( "birthaddr" ));

             }

             System.out.println( "搞定！" );

* 关闭Connection PreparedStatement

  * @param connection

  * @param preparedStatement

  */

public static void closeConnection(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){

  if (resultSet != null ) {

   try {

    resultSet.close();

   } catch (SQLException e) {

    e.printStackTrace();

   }

  }

  if (preparedStatement != null ) {

   try {

    preparedStatement.close();

   } catch (SQLException e) {

    // TODO Auto-generated catch block

    e.printStackTrace();

   }

  }

  if (connection != null ) {

   try {

    connection.close();

   } catch (SQLException e) {

    e.printStackTrace();

   }

  }

}

package util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

public class DBUtil {

  private static DataSource ds;

  static {

   //1.加载配置文件

   Properties pro = new Properties();

   try {

    pro.load(DBUtil. class .getClassLoader().getResourceAsStream( "/db.properties" ));

    //获取DataSource

    ds = DruidDataSourceFactory.createDataSource(pro);

   } catch (IOException e) {

    e.printStackTrace();

   } catch (Exception e) {

    e.printStackTrace();

   }

  }

  //获取连接

  public static Connection getConnection() throws SQLException {

   return ds.getConnection();

  }

/**

   * @param sql SQL语句

   * @param objs SQL语句占位符实参，如果没有参数则传入null

   * @return 返回增删改的结果，类型为int

   */

  public static int executeDML(String sql,Object...objs){

   // 声明jdbc变量

   Connection conn = null ;

   PreparedStatement ps = null ;

   int i = - 1 ;

   try {

    // 获取连接对象

    conn = DBUtil.getConnection();

    // 开启事务管理

    conn.setAutoCommit( false );

    // 创建SQL命令对象

    ps = conn.prepareStatement(sql);

    // 给占位符赋值

    if (objs!= null ){

     for ( int j= 0 ;j<objs.length;j++){

      ps.setObject(j+ 1 ,objs[j]);

     }

    }

    // 执行SQL

    i = ps.executeUpdate();

    conn.commit();

   } catch (Exception e) {

    try {

     conn.rollback();

    } catch (SQLException e1) {

     // TODO Auto-generated catch block

     e1.printStackTrace();

    }

    e.printStackTrace();

   } finally {

    DBUtil.closeAll( null , ps, conn);

   }

   return i;

  }

//关闭资源

  public static void closeAll(ResultSet rs,Statement stmt,Connection conn){

   try {

    if (rs!= null ){

     rs.close();

    }

   } catch (SQLException e1) {

    // TODO Auto-generated catch block

    e1.printStackTrace();

   }

   try {

    stmt.close();

   } catch (SQLException e) {

    // TODO Auto-generated catch block

    e.printStackTrace();

   }

   try {

    conn.close();

   } catch (SQLException e) {

    // TODO Auto-generated catch block

    e.printStackTrace();

   }