JDBC（2） 2021.8.9

于 2021-08-09 21:42:20 发布
阅读量403 收藏
点赞数
文章标签： jdbc
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/asa134124/article/details/119547460
版权 
数据库连接池

1. 概念：其实就是一个容器(集合)，存放数据库连接的容器。
 当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。
2. 好处：
1. 节约资源
2. 用户访问高效
3. 实现：
1. 标准接口：DataSource   javax.sql包下的
1. 方法：
* 获取连接：getConnection()
* 归还连接：Connection.close()。如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接了。而是归还连接
2. 一般我们不去实现它，有数据库厂商来实现
1. C3P0：数据库连接池技术
2. Druid：数据库连接池实现技术，由阿里巴巴提供的

着重使用Druid

 Druid：数据库连接池实现技术，由阿里巴巴提供的

1. 步骤：

1. 导入jar包 druid-1.0.9.jar

2. 定义配置文件：

* 是properties形式的

* 可以叫任意名称，可以放在任意目录下

3. 加载配置文件。Properties

4. 获取数据库连接池对象：通过工厂来来获取  DruidDataSourceFactory

5. 获取连接：getConnection

* 代码：

3.加载配置文件

Properties pro = new Properties();

 InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");

 pro.load(is);

 4.获取连接池对象

DataSource ds = DruidDataSourceFactory.createDataSource(pro);

5.获取连接

 Connection conn = ds.getConnection();

2. 定义工具类

1. 定义一个类 JDBCUtils

2. 提供静态代码块加载配置文件，初始化连接池对象

3. 提供方法

1. 获取连接方法：通过数据库连接池获取连接

2. 释放资源

3. 获取连接池的方法

package druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DruidDemo {
    public static void main(String[] args) throws Exception {
        //1导入jar包
        //2导入配置文件
        //加载配置文件
        Properties  pro=new Properties();
        InputStream  is=DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);
        //3获取连接池

        DataSource  ds=DruidDataSourceFactory.createDataSource(pro);
        //5获取连接
        Connection  conn=ds.getConnection();
        System.out.println(conn);
    }
}

package druid;

import JDBCUtils.DruidUtils;
import com.mysql.cj.xdevapi.PreparableStatement;

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

public class DruidDemo2 {
    /*使用durid工具类完成*/

    public static void main(String[] args) {
        /*完成添加操作*/
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            //获取连接
            conn=DruidUtils.getConnection();
            //定义sql
            String  sql="insert into departments  values(?,?,?,?)";
            //获取pstme对象
            pstmt=conn.prepareStatement(sql);
            //给？赋值，防止sql注入
            pstmt.setInt(1,300);
            pstmt.setString(2,"jay");
            pstmt.setInt(3,219);
            pstmt.setInt(4,2000);
           int  count=pstmt.executeUpdate();
           System.out.println("结果"+count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
    DruidUtils.close(pstmt,conn);
        }
    }

}

 

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/myemployees
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000
 

* Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
 

* 步骤：
 

1. 导入jar包
 

2. 创建JdbcTemplate对象。依赖于数据源DataSource
 

* JdbcTemplate template = new JdbcTemplate(ds);
 

3. 调用JdbcTemplate的方法来完成CRUD的操作
 

* update():执行DML语句。增、删、改语句
 

* queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
 

* 注意：这个方法查询的结果集长度只能是1
 

* queryForList():查询结果将结果集封装为list集合
 

* 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
 

* query():查询结果，将结果封装为JavaBean对象
 

* query的参数：RowMapper
 

* 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
 

* new BeanPropertyRowMapper<类型>(类型.class)
 

* queryForObject：查询结果，将结果封装为对象
 

* 一般用于聚合函数的查询
 

 

 
 

/*封装好的Druid工具类
package JDBCUtils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.ConnectionEvent;
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  ds;
    static {

        try {
            Properties  pro=new Properties();
            pro.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds= DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /*获取连接*/
    public  static Connection getConnection() throws SQLException {
        return  ds.getConnection();

    }
    public  static void close(Statement  stmt,Connection conn) {
       /* if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }*/
       close(null,stmt,conn);
    }


    public  static void close(ResultSet  rs, Statement  stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

        /*获取连接池方法*/
        public static DataSource getDataSource () {
            return ds;
        }


    }


 

package JDBCTemplate;

        import JDBCUtils.DruidUtils;
        import org.springframework.jdbc.core.JdbcTemplate;

public class SpringJDBCTemplate {
    public static void main(String[] args) {
        JdbcTemplate  template=new JdbcTemplate(DruidUtils.getDataSource());
        String sql="update departments set manager_id=300 where department_id=?";
        int count=template.update(sql,300);
        System.out.println(count);
    }
}

 

package employees;

import java.io.InputStream;
import java.util.Date;

public class selectclass {

    private Integer employee_id;
    private String first_name;
    private  String  last_name;
    private  String  email;
    private  String  phone_number;
    private  String  job_id;
    private Double  salary;
    private Double commissiom_pct;
    private Integer manager_id;
    private Integer department_id;
    private Date  hiredate;

    @Override
    public String toString() {
        return "selectclass{" +
                "employee_id=" + employee_id +
                ", first_name='" + first_name + '\'' +
                ", last_name='" + last_name + '\'' +
                ", email='" + email + '\'' +
                ", phone_number='" + phone_number + '\'' +
                ", job_id='" + job_id + '\'' +
                ", salary=" + salary +
                ", commissiom_pct=" + commissiom_pct +
                ", manager_id=" + manager_id +
                ", department_id=" + department_id +
                ", hiredate=" + hiredate +
                '}';
    }

    public Integer getEmployee_id() {
        return employee_id;
    }

    public void setEmployee_id(Integer employee_id) {
        this.employee_id = employee_id;
    }

    public String getFirst_name() {
        return first_name;
    }

    public void setFirst_name(String first_name) {
        this.first_name = first_name;
    }

    public String getLast_name() {
        return last_name;
    }

    public void setLast_name(String last_name) {
        this.last_name = last_name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhone_number() {
        return phone_number;
    }

    public void setPhone_number(String phone_number) {
        this.phone_number = phone_number;
    }

    public String getJob_id() {
        return job_id;
    }

    public void setJob_id(String job_id) {
        this.job_id = job_id;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Double getCommissiom_pct() {
        return commissiom_pct;
    }

    public void setCommissiom_pct(Double commissiom_pct) {
        this.commissiom_pct = commissiom_pct;
    }

    public Integer getManager_id() {
        return manager_id;
    }

    public void setManager_id(Integer manager_id) {
        this.manager_id = manager_id;
    }

    public Integer getDepartment_id() {
        return department_id;
    }

    public void setDepartment_id(Integer department_id) {
        this.department_id = department_id;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

/* private  int  employee_id;
    private String first_name;
    private  String  last_name;
    private  String  email;
    private  String  phone_number;
    private  String  job_id;
    private double  salary;
    private double commissiom_pct;
    private int manager_id;
    private int department_id;
    private Date  hiredate;
    public int getEmployee_id() {
        return employee_id;
    }

    public void setEmployee_id(int employee_id) {
        this.employee_id = employee_id;
    }

    public String getFirst_name() {
        return first_name;
    }

    public void setFirst_name(String first_name) {
        this.first_name = first_name;
    }

    public String getLast_name() {
        return last_name;
    }

    public void setLast_name(String last_name) {
        this.last_name = last_name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhone_number() {
        return phone_number;
    }

    public void setPhone_number(String phone_number) {
        this.phone_number = phone_number;
    }

    public String getJob_id() {
        return job_id;
    }

    public void setJob_id(String job_id) {
        this.job_id = job_id;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public double getCommissiom_pct() {
        return commissiom_pct;
    }

    public void setCommissiom_pct(double commissiom_pct) {
        this.commissiom_pct = commissiom_pct;
    }

    public int getManager_id() {
        return manager_id;
    }

    public void setManager_id(int manager_id) {
        this.manager_id = manager_id;
    }

    public int getDepartment_id() {
        return department_id;
    }

    public void setDepartment_id(int department_id) {
        this.department_id = department_id;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }


    @Override
    public String toString() {
        return "selectclass{" +
                "employee_id=" + employee_id +
                ", first_name='" + first_name + '\'' +
                ", last_name='" + last_name + '\'' +
                ", email='" + email + '\'' +
                ", phone_number='" + phone_number + '\'' +
                ", job_id='" + job_id + '\'' +
                ", salary=" + salary +
                ", commissiom_pct=" + commissiom_pct +
                ", manager_id=" + manager_id +
                ", department_id=" + department_id +
                ", hiredate=" + hiredate +
                '}';
    }*/
}
 

 
 

package JDBCTemplate;

import JDBCUtils.DruidUtils;
import employees.selectclass;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class exercise {
    private JdbcTemplate template=new JdbcTemplate(DruidUtils.getDataSource());


    @Test
/*封装为map集合
* 这个方法的结果集长度只能为1*/
    public void  test1(){
        String sql="select * from employees where  employee_id=? ";


        Map<String, Object> stringObjectMap = template.queryForMap(sql, 100);
        System.out.println(stringObjectMap);

    }
    @Test
    public void  test2(){
        String sql="select * from locations ";


        List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> stringObjectMap : list) {
            System.out.println(stringObjectMap);

        }
    }
    @Test
    public void  test3(){
   String  sql="select * from employees";
   List<selectclass>   list=template.query(sql,new BeanPropertyRowMapper<>(selectclass.class));
        for (selectclass selectclass : list) {
            System.out.println(selectclass);

        }
     /*  List<Map<String, Object>> list = template.queryForList(sql);
        for (Map<String, Object> stringObjectMap : list) {
            System.out.println(stringObjectMap);*/






        }
    @Test
    public void  test4(){
        String sql="select count(employee_id) from employees ";
        Integer integer = template.queryForObject(sql, Integer.class);
        System.out.println(integer);


    }
    }


 


                

        

    

  



    



            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值