JdbcTemplate使用c3p0的增、删、改、查_jdbc怎么用c3p0实现增删改查-CSDN博客

本文链接：https://blog.csdn.net/weixin_40245787/article/details/84931398

首先我们需要导入jar包和配置环境：
在这里插入图片描述
db.properties文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///jdbctemplate?useUnicode=true&characterEncoding=utf8
username=root
password=123456

c3p0-config.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 使用默认的配置读取连接池对象 -->
    <default-config>
        <!--  1. 数据库的连接参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///jdbctemplate?useUnicode=true&amp;characterEncoding=utf8</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <!-- 2. 连接池参数 -->
        <!--初始连接数-->
        <property name="initialPoolSize">5</property>
        <!--最大连接数-->
        <property name="maxPoolSize">10</property>
        <!--等待多久以后抛出异常-->
        <property name="checkoutTimeout">2000</property>
    </default-config>

    <!-- 命名配置 -->
    <named-config name="otherc3p0">
        <!--  连接参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///c3p0test</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <!-- 连接池参数 -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">8</property>
        <property name="checkoutTimeout">1000</property>
    </named-config>
</c3p0-config>

JDBCUtils:工具类

package com.SiyualChen.day07.JDBCTest.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {
    private static  final DataSource ds = new ComboPooledDataSource();

    public static DataSource getDataSource(){
        return ds;

    }

    public  static Connection getConnection(){
        Connection conn =null;
        try {
            conn = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //创建释放资源的方法 release()  2个参数
    public static void release(Statement stmt, Connection conn){
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt= null;
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn= null;
        }
    }
    //创建释放资源的方法 release()  3个参数
    public static void release(ResultSet rs , Statement stmt, Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs= null;
        }
        release(stmt,conn);
    }
}

JDBCTemplateUtils:工具类

package com.SiyualChen.day07.JDBCTest.utils;

import org.springframework.jdbc.core.JdbcTemplate;

public class JDBCTemplateUtils {
    public static JdbcTemplate getJdbcTemplate(){
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        return jdbcTemplate;


    }
//    //测试
//    public static void main(String[] args) {
//        JdbcTemplate jdbcTemplate = getJdbcTemplate();
//        System.out.println(jdbcTemplate);
//    }

}

到目前为止环境配齐啦。测试连接没有问题。
现在我们需要准备一个数据表、和一个Bean类
数据表如下：
在这里插入图片描述
Bean类如下：

package com.SiyualChen.day07.JDBCTest;

import java.util.Date;

public class Employee {
    private int id ;
    private String name;
    private String sex;
    private Date birthday;
    private double salary;

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", salary=" + salary +
                '}';
    }

    public double getSalary() {
        return salary;
    }

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

    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 String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

准备工作完全结束我们现在就来实现
**

增、删、改

package com.SiyualChen.day07.JDBCTest;

import com.SiyualChen.day07.JDBCTest.utils.JDBCTemplateUtils;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 增、删、改
 */
public class Test01 {
    private static final JdbcTemplate jdbcTemplate = JDBCTemplateUtils.getJdbcTemplate();
    public static void main(String[] args) {
        //addData();
        //deleteData();
        //modifyData();


    }

    public static void modifyData() {
        String  sql ="update employee set name =? where id =?";
        int i = jdbcTemplate.update(sql, "小茜", 15);
        if (i>0){
            System.out.println("数据修改成功");
        }else {
            System.out.println("数据修改失败");
        }
    }

    public static void deleteData() {
        String sql="delete from employee where id =?";
        int i = jdbcTemplate.update(sql, 14);
        if (i>0){
            System.out.println("数据删除成功");
        }else {
            System.out.println("数据删除失败");
        }
    }

    public static void addData() {
        //增加
        String sql ="insert into employee values(null,?,?,?,?)";
        Object[] params={"孙悟空","男","1870-11-11",20000};
        int i = jdbcTemplate.update(sql, params);
        if (i >0){
            System.out.println("数据插入成功");
        }else{
            System.out.println("数据插入失败");
        }
    }
}

总结：增、删、改比较简单都是update方法比较容易记忆

**
下面实现查询：

查询一条数据存放在map中
查询一条记录封装成Bean
查询一条记录封装成Bean 这个简单点
查询多条记录封装成Bean并存入集合
查询一列
查询多列
查询所有的数据封装成list
查询共有多少条数据（聚合函数）
package com.SiyualChen.day07.JDBCTest;

import com.SiyualChen.day07.JDBCTest.utils.JDBCTemplateUtils;
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.List;
import java.util.Map;

public class Test02 {
private static final JdbcTemplate jdbcTemplate = JDBCTemplateUtils.getJdbcTemplate();
public static void main(String[] args) throws SQLException {
//checkForMap();//查询一条数据存放在map中
//CheckOneBean();//查询一条记录封装成Bean
//CheckOneBean01();//查询一条记录封装成Bean 这个简单点
//checkManyBeanList();//查询多条记录封装成Bean并存入集合
//checkOneCol(); //查询一列
//CheckManyCol(); //查询多列
//checkALLDataList();//查询所有的数据封装成list
//checkCount();//查询共有多少条数据（聚合函数）

}

public static void checkCount() {
    //查询共有多少条数据（聚合函数）
    String sql ="select count(id) from employee";
    Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
    System.out.println(integer);
}

public static void checkALLDataList() {
    //查询所有的数据封装成list
    String sql ="select * from employee";
    List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
    for (Map<String, Object> map : maps) {
        System.out.println(map);
    }
}

public static void CheckManyCol() {
    String sql ="select name,salary from employee";
    List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
    for (Map<String, Object> map : maps) {
        System.out.println(map);
    }
}

public static void checkOneCol() {
    //查询一列
    String sql = "select name from employee";
    List<String> list = jdbcTemplate.queryForList(sql, String.class);
    for (String s : list) {
        System.out.println(s);
    }
}

public static void checkManyBeanList() {
    //查询多条记录封装成Bean并存入集合
    String sql ="select * from employee";
    List<Employee> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Employee.class));
    for (Employee employee : list) {
        System.out.println(employee);
    }
}

public static void CheckOneBean01() {
    //查询一条记录封装成Bean 这个简单点
    String sql="select * from employee where id=?";
    Employee employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Employee.class), 13);
    System.out.println(employee);
}

public static void CheckOneBean() throws SQLException {
    //查询一条记录封装成Bean
    String sql ="select * from employee where id =?";
    Employee employee = jdbcTemplate.queryForObject(sql, new RowMapper<Employee>() {
        @Override
        public Employee mapRow(ResultSet resultSet, int i) throws SQLException {
            Employee employee1 = new Employee();
            employee1.setId(resultSet.getInt("id"));
            employee1.setName(resultSet.getString("name"));
            employee1.setSex(resultSet.getString("sex"));
            employee1.setBirthday(resultSet.getDate("daytime"));
            employee1.setSalary(resultSet.getDouble("salary"));
            return employee1;
        }
    },12);
    System.out.println(employee);
}

public static void checkForMap() {
    //查询一条数据存放在map中
    String sql ="select *from employee where id =?";
    Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql, 13);
    System.out.println(stringObjectMap);
}

}

总结：查询的方法比较多比较容易记混淆，有三大种方法

1.queryforObject查询结果生成对象，可以处理查询1条、查询多条、聚合函数查询。
2.query查询查询所有数据并封装成List。
3.queryforList 查询一列、多列、封装成List

添加JDBCTemplateUtils 模版

package com.SiyualChen.day07;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

public class JDBCTemplateUtils {
    private  static  final DataSource ds = new ComboPooledDataSource();
    public static JdbcTemplate getJdbcTemplate(){
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        return jdbcTemplate;
    }
}