JdbcTemplate使用c3p0的增、删、改、查

喜欢ヅ旅行 2022-04-06 16:00 112阅读 0赞

首先我们需要导入jar包和配置环境：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDI0NTc4Nw_size_16_color_FFFFFF_t_70]  
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>
            
            <property name="driverClass">com.mysql.jdbc.Driver</property>
            <property name="jdbcUrl">jdbc:mysql:///jdbctemplate?useUnicode=true&characterEncoding=utf8</property>
            <property name="user">root</property>
            <property name="password">123456</property>
    
            
            
            <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类  
数据表如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDI0NTc4Nw_size_16_color_FFFFFF_t_70 1]  
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;
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDI0NTc4Nw_size_16_color_FFFFFF_t_70]: /images/20220406/ddca35b517f74ae0991d03fdd552b3b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDI0NTc4Nw_size_16_color_FFFFFF_t_70 1]: /images/20220406/c6a131dfed494677bfd78856d72f3a01.png