JDBC德鲁伊

所用jar

 

 

druid.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/qq
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

JDCBUtils

package cn.madko.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;



import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author 
 * @ClassName JDBCUtils
 * @description: TODO
 * @datetime 2023年 04月 12日 15:52
 * @version: 1.0
 */

public class JDBCUtils {

    private static DataSource ds;



    static {

        try {
            Properties props = new Properties();
            props.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds= DruidDataSourceFactory.createDataSource(props);

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

    }


    public static Connection getCon(){

        try {
          return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    //获取连接池对象
    public static DataSource getDataSource(){
        return ds;
    }





    public static void release(Connection conn, Statement stmt, ResultSet rs){
        closeconn(conn);
        closestmt(stmt);
        closers(rs);
    }

    private static void closers(ResultSet rs) {
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                rs = null;
            }
        }
    }

    private static void closestmt(Statement stmt) {
        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                stmt = null;
            }
        }
    }

    private static void closeconn(Connection conn) {
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                conn = null;
            }
        }
    }


}

JDBCTamplete

package cn.madko.util;

import cn.madko.dao.ResultSetHandler;


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

/**
 * @author 
 * @ClassName JDBCTamplete
 * @description: TODO
 * @datetime 2023年 04月 12日 18:58
 * @version: 1.0
 */

public class JDBCTamplete {
    public static int update(String sql,Object... obj){
        Connection conn = null;
        PreparedStatement pst =null;
        try {
            conn=JDBCUtils.getCon();
            pst=conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                pst.setObject(i+1, obj[i]);
            }
           return pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }finally {
            JDBCUtils.release(conn,pst,null);
        }

    }

    public static Object query(String sql, ResultSetHandler resultSetHandler, Object...obj){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;


        try {
            conn=JDBCUtils.getCon();
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length;i++) {
                pst.setObject(i+1, obj[i]);
            }
            rs = pst.executeQuery();
            return resultSetHandler.doHandler(rs);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtils.release(conn, pst, rs);
        }

    }




}

ResultSetHandler

package cn.madko.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 
 * @ClassName ResultSetHandler
 * @description: TODO
 * @datetime 2023年 04月 12日 19:06
 * @version: 1.0
 */

public interface ResultSetHandler {
    Object doHandler(ResultSet rs) throws SQLException;
}

BrandDao

package cn.madko.dao;

import cn.madko.entity.Brand;

import java.util.List;

/**
 * @author 
 * @ClassName BrandDao
 * @description: TODO
 * @datetime 2023年 04月 12日 16:33
 * @version: 1.0
 */

public interface BrandDao {
    //查询
    List<Brand> findAll();

    //查询一个
    Brand findByID(int id);

    //添加
    void addBrand(Brand brand);

    //修改
    void upBrand(Brand brand);

    //删除
    void deleteBrand(int id);

}

BrandDaoImpl

package cn.madko.dao.impl.impl;



import org.madko.dao.BrandDao;
import org.madko.dao.ResultSetHandler;
import org.madko.entity.Brand;
import org.madko.util.JDBCTemlete;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李政
 * @ClassName BrandDaoImpl
 * @description: TODO
 * @datetime 2023年 04月 12日 21:15
 * @version: 1.0
 */

public class BrandDaoImpl implements BrandDao {

    @Override
    public List<Brand> findAll() {
        String sql = "select * from brand";
       return (List<Brand>) JDBCTemlete.query(sql, new ResultSetHandler() {
            @Override
            public Object doHandle(ResultSet rs) throws SQLException {
                List<Brand> brands  = new ArrayList<Brand>();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int price = rs.getInt("price");
                    Brand brand = new  Brand(id,name,price);
                    brands.add(brand);
                }

                return brands;
            }
        });
    }

    @Override
    public Brand findByID(int id) {
        String sql ="select * from brand where id = ?";
        return (Brand) JDBCTemlete.query(sql, new ResultSetHandler() {
            @Override
            public Object doHandle(ResultSet rs) throws SQLException {
                Brand brand =null;
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int price = rs.getInt("price");
                     brand= new  Brand(id,name,price);
                }
                return brand;
            }
        }, id);

    }

    @Override
    public void addBrand(Brand brand) {
        String sql = "insert into brand(name,price) values(?,?)";
        int update = JDBCTemlete.update(sql, brand.getName(), brand.getPrice());
        if (update >0) {
            System.out.println("cc");
        }else {
            System.out.println("sb");
        }
    }

    @Override
    public void upBrand(Brand brand) {

    }

    @Override
    public void deleteBrand(int id) {

    }
}

实体类

package cn.madko.entity;

/**
 * @author 
 * @ClassName Brand
 * @description: TODO
 * @datetime 2023年 04月 12日 16:26
 * @version: 1.0
 */

public class Brand {
    private int id ;
    private String name;
    private int price;

    public Brand() {
    }

    public Brand(String name, int price) {
        this.name = name;
        this.price = price;
    }

    public Brand(int id, String name, int price) {
        this.id = id;
        this.name = name;
        this.price = price;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price='" + price + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }
}

测试类

package cn.madko.dao.impl;

import cn.madko.dao.BrandDao;
import cn.madko.entity.Brand;
import cn.madko.util.JDBCTamplete;
import org.junit.Test;
import org.junit.runner.RunWith;

import java.util.List;

import static org.junit.Assert.*;

public class BrandDaoImplTest {
BrandDao brandDao = new BrandDaoImpl();
    @Test
    public void findAll() {
        List<Brand> all = brandDao.findAll();
        for (Brand brand:all) {
            System.out.println(brand);
        }
    }

    @Test
    public void findByID() {
        System.out.println(brandDao.findByID(3));
    }

    @Test
    public void addBrand() {
        String name = "好些东西";
        int price = 1;
        Brand brand = new Brand(name,price);
        brandDao.addBrand(brand);
    }

    @Test
    public void upBrand() {

        Brand brand = new Brand(1,"好",0);
        brandDao.upBrand(brand);
    }

    @Test
    public void deleteBrand() {
        brandDao.deleteBrand(7);
    }
}