Spring jdbc开发



1，jar包
 导入Spring核心开发包到创建工程
  spring-beans-3.2.0.RELEASE.jar
  spring-context-3.2.0.RELEASE.jar
  spring-core-3.2.0.RELEASE.jar
  spring-expression-3.2.0.RELEASE.jar
 还需要下载commons-logging日志包
  commons-logging-1.1.1.jar
 导入JDBC模板开发包
  spring-jdbc-3.2.0.RELEASE.jar
  spring-tx-3.2.0.RELEASE.jar
 数据驱动包
  mysql-connector-java-5.1.10-bin.jar
 配置C3P0数据源
  com.springsource.com.mchange.v2.c3p0-0.9.1.2.jar
 配置DBCP数据源
  com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar
  com.springsource.org.apache.commons.pool-1.5.3.jar

2，使用JdbcTemplate 创建数据表

 // JDBC模板 依赖连接池获得数据库连接，所有必须先构造连接池
 DriverManagerDataSource dataSource = new DriverManagerDataSource();
 dataSource.setDriverClassName("com.mysql.jdbc.Driver");
 dataSource.setUrl("jdbc:mysql:///spring");
 dataSource.setUsername("root");
 dataSource.setPassword("123"); 
 // 创建JDBC模板
 JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); 
 // 建表SQL语句
 String sql = "create table customers(id int primary key auto_increment,name varchar(20))";
 jdbcTemplate.execute(sql);
 
3，配置dbcp数据源
 1，bean.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"
        xsi:schemaLocation="http://www.springframework.org/schema/beans 
         http://www.springframework.org/schema/beans/spring-beans.xsd">
        <!--配置数据驱动-->
     <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
   <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
   <property name="url" value="jdbc:mysql:///spring"></property>
   <property name="username" value="root"></property>
   <property name="password" value="123456"></property>
  </bean>
  <!--使用spring注入数据源-->
     <bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
      <property name="dataSource" ref="dataSource"></property>
     </bean>    
 </beans>



 2，使用spring创建对象
 ApplicationContext applicationContext=new ClassPathXmlApplicationContext("bean.xml");
 org.springframework.jdbc.core.JdbcTemplate jdbctemplat=applicationContext.getBean("jdbctemplate", org.springframework.jdbc.core.JdbcTemplate.class);
 jdbctemplat.execute("insert into heima(name) values ('张三')");



4，通过属性文件properties配置c3p0数据源

 1，jdbc.properties

  jdbc.driver=com.mysql.jdbc.Driver

  jdbc.url=jdbc\:mysql\:///spring  

  jdbc.username=root

  jdbc.password=123456

 2，jdbcC3p0.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:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="
      http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
  
     <context:property-placeholder location="classpath:jdbc.properties"/>
     <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
   <!-- ${key} 可以读取properties文件中配置 key对应value -->
   <property name="driverClass" value="${jdbc.driver}"></property>
   <property name="jdbcUrl" value="${jdbc.url}"></property>
   <property name="user" value="${jdbc.username}"></property>
   <property name="password" value="${jdbc.password}"></property>
  </bean>
     <bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
       <property name="dataSource" ref="dataSource"></property>
     </bean>
 </beans>



 3，使用spring创建对象
  ApplicationContext applicationContext=new ClassPathXmlApplicationContext("jdbcC3p0.xml");
  org.springframework.jdbc.core.JdbcTemplate jdbctemplat=applicationContext.getBean("jdbctemplate", org.springframework.jdbc.core.JdbcTemplate.class);
  jdbctemplat.execute("insert into heima(name) values ('燕子李三')");

5，使用JdbcTemplate -- 增 删 改 查
// 插入操作
  String sql = "insert into customers values(null,?)";
  jdbcTemplate.update(sql, "小丽");
// 修改操作
  String sql = "update customers set name= ? where id =?";
  jdbcTemplate.update(sql, "小明", 1);
// 删除操作
  String sql = "delete from customers where id =?";
  jdbcTemplate.update(sql, 1);
// 简单查询
 // 1，
  String sql = "select count(*) from customers";
  int count = jdbcTemplate.queryForInt(sql);  
 // 2，
  String sql = "select name from customers where id = ?";
  String name = jdbcTemplate.queryForObject(sql, String.class, 2);
  这里String.class是返回类型 ， 2是customer的id参数  
  //3，通过泛型确定返回的数据类型
  String sql="select * from heima where id=?";
  User user=jdbctemplat.queryForObject(sql, new RowMapper<User>() {   
   @Override
   public User mapRow(ResultSet rs, int rowNum) throws SQLException {
    User user=new User();
    user.setId(String.valueOf(rs.getInt(1)));
    user.setName(rs.getString("name"));
    return user;
   }
  },2);
 // 4，
  String sql="select * from heima";
  List<User> query = jdbctemplat.query(sql, new RowMapper<User>() {
   @Override
   public User mapRow(ResultSet rs, int rowNum) throws SQLException {
    User user=new User();
    user.setId(String.valueOf(rs.getInt(1)));
    user.setName(rs.getString("name"));
    return user;
   }
  });