用Spring和c3p0工具简单的实现增删改查

1.导入Spring和c3p0的jar包

2.配置beans.xml文件

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xmlns:aop="http://www.springframework.org/schema/aop"

    xmlns:context="http://www.springframework.org/schema/context"

    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd

        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd

        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">

        <!-- 配置链接池 -->

    <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" id="c">

        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>

        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=GBK"></property>

        <property name="user" value="root"></property>

        <property name="password" value=""></property>

        <!--依次为数据库连接池最大连接数、最小连接数、初始连接数-->

        <property name="maxPoolSize" value="15"></property>

        <property name="minPoolSize" value="5"></property>

        <property name="initialPoolSize" value="5"></property>

    </bean>

</beans>

3.编写代码

实体类Nation

package com.itnba.maya.test;

public class Nation {

    private String code;

    private String name;

    public String getCode() {

        return code;

    }

    public void setCode(String code) {

        this.code = code;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    @Override

    public String toString() {

        return "Nation [code=" + code + ", name=" + name + "]";

    }

}

main函数

修改：

package com.itnba.maya.test;

import java.sql.Connection;import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

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

public class Test {

    public static void main(String[] args) throws Exception{

        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");

        DataSource ds= (DataSource) context.getBean("c");

        JdbcTemplate j =new JdbcTemplate();//造一个工具

        j.setDataSource(ds);

        String sql="update nation set name=? where code=?";

        j.update(sql, "维吾尔族", "n001");

　　}

}

单个数据更新

package com.itnba.maya.test;

import java.sql.Connection;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

    public static void main(String[] args) throws Exception{

        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");

        DataSource ds= (DataSource) context.getBean("c");

        JdbcTemplate j =new JdbcTemplate();//造一个工具

        j.setDataSource(ds);

        String sql = "insert into nation values(?,?)";

        j.update(sql, "n006","侗族");
　　}
}

多个数据更新

package com.itnba.maya.test;

import java.sql.Connection;
import java.util.*;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

    public static void main(String[] args) throws Exception{

        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");

        DataSource ds= (DataSource) context.getBean("c");

        JdbcTemplate j =new JdbcTemplate();//造一个工具

        j.setDataSource(ds);

        String sql = "insert into nation values(?,?)";

      //批量更，用里面是object[]的集合

        List<Object[]> batchArgs=new ArrayList<Object[]>();

        batchArgs.add(new Object[]{"n007","蒙古族"});

        batchArgs.add(new Object[]{"n008","朝鲜族"});

        j.batchUpdate(sql, batchArgs);

　　}

}

查询单行：

package com.itnba.maya.test;

import java.sql.Connection;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

    public static void main(String[] args) throws Exception{

        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");

        DataSource ds= (DataSource) context.getBean("c");

        JdbcTemplate j =new JdbcTemplate();//造一个工具

        j.setDataSource(ds);

        //查询单个

        String sql="select * from nation where code=?";

        RowMapper<Nation> rowMapper= new BeanPropertyRowMapper<Nation>(Nation.class);

        Nation nation = j.queryForObject(sql, rowMapper,"n001");

        System.out.println(nation);

    }

}

查询多行：

package com.itnba.maya.test;

import java.sql.Connection;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

    public static void main(String[] args) throws Exception{

        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");

        DataSource ds= (DataSource) context.getBean("c");

        JdbcTemplate j =new JdbcTemplate();//造一个工具

        j.setDataSource(ds);

        //查询多行

        String sql="select * from nation";

        RowMapper<Nation> rowMapper= new BeanPropertyRowMapper<Nation>(Nation.class);

        List<Nation> list=j.query(sql, rowMapper);

        for(Nation a:list){

            System.out.println(a);

        }

    }

}

查询统计函数

package com.itnba.maya.test;

import java.sql.Connection;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

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

public class Test {

    public static void main(String[] args) throws Exception{

        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");

        DataSource ds= (DataSource) context.getBean("c");

        JdbcTemplate j =new JdbcTemplate();//造一个工具

        j.setDataSource(ds);

        //查询统计函数

        String sql="select count(*) from nation ";

        long l=j.queryForObject(sql, Long.class);

        System.out.println(l);

    }

}

不用?用 : 的方式查询

package com.itnba.maya.test;

import java.sql.Connection;

import java.util.*;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

    public static void main(String[] args) throws Exception{

        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");

        DataSource ds= (DataSource) context.getBean("c");

        NamedParameterJdbcTemplate j=new NamedParameterJdbcTemplate(ds);

        String sql="select * from nation where code=:a";

        Map<String, String> paramSource = new HashMap<String,String>();

        paramSource.put("a", "n001");

        RowMapper<Nation> rowMapper= new BeanPropertyRowMapper<Nation>(Nation.class);

        Nation nation = j.queryForObject(sql, paramSource, rowMapper);

        System.out.println(nation);

    }

}