durid接続プール

druid接続プール
druidはjavaリンクデータベースのデータベース接続プールであり、接続が容易である.
druid.properties
propertiesファイルは必ずresourceフォルダの下に置く
resourceフォルダがなければmoduleに行って自分で作成します

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000
maxIdle=8
minIdle=3

initialsize maxActive maxWait （ ）
druidのpom構成

     <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druidartifactId>
            <version>1.0.9version>
     dependency>

DruidUtilsツールクラス

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 *
 */
public class DruidUtils {
    private static DataSource dataSource;

    static {
        //      
        Properties properties =new Properties();
        try {
            properties.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource= DruidDataSourceFactory.createDataSource(properties);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public static Connection getconnection() throws SQLException {
        return dataSource.getConnection();
    }
    public static void close(ResultSet resultSet,Statement statement, Connection connection){
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Statement statement, Connection connection){
        close(null,statement,connection);
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
}

データデモの追加

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class Druiddemo {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try {
            connection = DruidUtils.getconnection();
            String sql = "insert into user (username,address) values (?,?) ";
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1, "cwj");
            preparedStatement.setString(2,"  ");
            int count = preparedStatement.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DruidUtils.close(preparedStatement,connection);
        }
    }
}

注意クエリ操作executeQueryではsqlパラメータは不要です
データ・デモの問合せ

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Druiddemo {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet =null;
        try {
            connection = DruidUtils.getconnection();
            String sql = "SELECT *from user " ;
            preparedStatement= connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                String username = resultSet.getString("username");
                int id = resultSet.getInt("id");
                System.out.println(username+"----"+id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DruidUtils.close(resultSet,preparedStatement,connection);
        }
    }
}

データの問合せ

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Druiddemo {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet =null;
        try {
            connection = DruidUtils.getconnection();
            String sql = "SELECT *from user where id =1" ;
            preparedStatement= connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                String username = resultSet.getString("username");
                int id = resultSet.getInt("id");
                System.out.println(username+"----"+id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DruidUtils.close(resultSet,preparedStatement,connection);
        }
    }
}

public class Druiddemo {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet =null;
        try {
            connection = DruidUtils.getconnection();
            String sql = "SELECT *from user where id =1" ;
            preparedStatement= connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            System.out.println(resultSet.getString("username"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DruidUtils.close(resultSet,preparedStatement,connection);
        }
    }
}

トランザクション・オペレーション(銀行ケース)
トランザクションのロールバック
接続が完了したらconnection.setAutCommit(false);
トランザクションが完了したらconnection.commit()
catchでconnection.rollback()

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Yinhang {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;

        try {
            String str1 = "update login set account=account+500 where id =1";
            String str2 = "update login set account =account-500  where id=2 ;";
            connection = DruidUtils.getconnection();
            connection.setAutoCommit(false);
            pstmt = connection.prepareStatement(str1);
            pstmt1 = connection.prepareStatement(str2);
            pstmt.executeUpdate();
            pstmt1.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            if (connection != null) {
                connection.rollback();
            }
        } finally {
            DruidUtils.close(pstmt, connection);
            DruidUtils.close(pstmt1, null);
        }
    }
}