1. 配置环境

MySQL驱动、C3P0jar包：

c3p0-0.9.1.2.jar

mysql-connector-java-5.1.37-bin.jar

JdbcTemplate需要的jar包 ：

spring-jdbc-5.0.2.RELEASE.jar
spring-core-5.0.2.RELEASE.jar
spring-core-5.0.2.RELEASE.jar

spring-tx-5.0.2.RELEASE.jar

JdbcTemplate工具类：

[Java] 纯文本查看 复制代码

public class JdbcTemplateUtils {

    //初始化数据源
    private static DataSource ds = new ComboPooledDataSource();
// 获取JdbcTemplate对象
    public static JdbcTemplate getJdbcTemplate() {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        return  jdbcTemplate;
    }

    public static DataSource getDataSource(){
        return ds;
    }
    //得到连接的方法
    public static Connection getConn() throws SQLException {
        return ds.getConnection();
    }
}

c3p0-config.xml:

[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:///zongheanli</property>
      <property name="user">root</property>
      <property name="password">1234</property>
      <!--<property name="acquireIncrement">2</property>
      <property name="initialPoolSize">5</property>
      <property name="minPoolSize">1</property>
      <property name="maxPoolSize">5</property>-->
   </default-config>
</c3p0-config>

JdbcTemplate建表例子：

[Java] 纯文本查看 复制代码

  String sql = "create table student1(id int primary key auto_increment,name varchar(20) not null,birthday date)";
       //1. 创建JdbcTemplate对象，传入C3P0连接池,模板对象自己获得连接并且自己释放资源
       JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
       //2. 创建表，执行DDL语句
       jdbcTemplate.execute(sql);

JdbcTemplate插入例子：

[Java] 纯文本查看 复制代码

JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDruidDataSource());
jdbcTemplate.update("insert into student1 values (null, ?,?)", "孙悟空","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "孙悟天","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "孙悟饭","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "龟仙人","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "牛魔王","1999-12-10");
jdbcTemplate.update("insert into student1 values (null, ?,?)", "18号","1997-12-10");

int row = jdbcTemplate.update("update student1 set name=?, birthday=? where id=?", "苦林", "1994-12-10", 2);
System.out.println("更新了" + row + "行记录");

int row = jdbcTemplate.update("delete from student1 where id=?", 6);
System.out.println("删除" + row + "行记录");

JdbcTemplate queryForObject查询例子：

[Java] 纯文本查看 复制代码

//创建模板类的对象
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        //书写sql语句
        String sql = "select count(*) from student1";
        //执行方法，传入参数开始查询
        int count = jdbcTemplate.queryForObject(sql, int.class);
        //输出查询结果
        System.out.println(count);

JdbcTemplate queryForMap查询例子：

[Java] 纯文本查看 复制代码

 JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        Map<String, Object> map = jdbcTemplate.queryForMap("select * from student where id=?", 2);
        System.out.println(map);

JdbcTemplate queryForList查询例子：

[Java] 纯文本查看 复制代码

 JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from student1");
        for (Map<String,Object> map: mapList ) {
            System.out.println(map);
        }

JdbcTemplate query查询的无参数例子：

[Java] 纯文本查看 复制代码

JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        List<Student> students = jdbcTemplate.query("select * from student", new BeanPropertyRowMapper<>(Student.class));
        for (Student student : students) {
            System.out.println(student);
        }

JdbcTemplate query查询的数组参数例子：

[Java] 纯文本查看 复制代码

 String name="lisi";
        int password=23;
        String sql = "select * from student where name = ? and age = ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        Object[] args = {name,password};
        List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class), args);
        System.out.println(list)

        String name="lisi";
        int password=23;
        String sql = "select * from student where name = ? and age = ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        Object[] args = {name,password};
        List<Student> list = jdbcTemplate.query(sql,args, new BeanPropertyRowMapper<>(Student.class));
        System.out.println(list);

JdbcTemplate query查询的可变参数例子：

[Java] 纯文本查看 复制代码

 String name="lisi";
        int password=23;
        String sql = "select * from student where name = ? and age = ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        Object[] args = {name,password};
        List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class),name,password);
        System.out.println(list);

JdbcTemplate query查询的new PreparedStatementCreator()例子：

[Java] 纯文本查看 复制代码

 String name="lisi";
        int password=23;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        List<Student> list = jdbcTemplate.query(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                String sql = "select * from student where name = ? and age = ?";
                PreparedStatement prepar = conn.prepareStatement(sql);
                prepar.setString(1, "lisi");
                prepar.setInt(2, 23);
                return prepar;
            }
        }, new BeanPropertyRowMapper<>(Student.class));
        System.out.println(list);

JdbcTemplate query查询的new PreparedStatementSetter()例子：

[Java] 纯文本查看 复制代码

String name="lisi";
        int password=23;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
        String sql = "select * from student where name = ? and age = ?";
        List<Student> list = jdbcTemplate.query(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement prepare) throws SQLException {
                prepare.setString(1, "lisi");
                prepare.setInt(2, 23);
            }
        }, new BeanPropertyRowMapper<>(Student.class));
        System.out.println(list);
    }

奈斯