java jdbctemplate 简单

java jdbctemplate
1、新建java 项目
2、lib 文件夹
3、jar 包
c3p0-0.9.1.2.jar
com.springsource.net.sf.cglib-2.2.0.jar
com.springsource.org.aopalliance-1.0.0.jar
com.springsource.org.aspectj.weaver-1.6.8.RELEASE.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.7-bin.jar
spring-aop-4.0.0.RELEASE.jar
spring-aspects-4.0.0.RELEASE.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
spring-jdbc-4.0.0.RELEASE.jar
spring-orm-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar
spring-web-4.0.0.RELEASE.jar
spring-webmvc-4.0.0.RELEASE.jar

4、db.properties
user=root
password=
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/c
initialPoolSize=5
maxPoolSize=10

5、applicationContext.xml 文件 类路径下(也就是：src)

      <context:property-placeholder location="classpath:db.properties"/>

      <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
             <property name="user" value="${user}" />
             <property name="password" value="${password}" />
             <property name="driverClass" value="${driverClass}" />
             <property name="jdbcUrl" value="${jdbcUrl}" />

             <property name="initialPoolSize" value="${initialPoolSize}" />
             <property name="maxPoolSize" value="${maxPoolSize}" />
      </bean>

      <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
             <property name="dataSource" ref="dataSource"></property>
      </bean>

6、sql
CREATE TABLE dept (
id int(11) NOT NULL AUTO_INCREMENT,
dept_name varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE employee (
id int(11) NOT NULL AUTO_INCREMENT,
employee_name varchar(255) NOT NULL,
tel varchar(255) DEFAULT NULL,
dept_id int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY dept_id (dept_id),
CONSTRAINT dept_id FOREIGN KEY (dept_id) REFERENCES dept (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

7、简单java 类
8、Test类

public class SpringTemplate {
      private ApplicationContext ctx = null;
      private JdbcTemplate jdbcTemplate = null;
      {
             ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
             jdbcTemplate = ctx.getBean("jdbcTemplate", JdbcTemplate.class);
      }
      // 根据条件查询一条记录
      @Test
      public void testGetData01() {
             String sql = "select id,dept_name from dept where id=?";
             RowMapper<Dept> rowMapper = new BeanPropertyRowMapper<>(Dept.class);
             Dept dept = jdbcTemplate.queryForObject(sql, rowMapper, 2);
             System.out.println(dept);
      }
      // 修改部门名称
      @Test
      public void testUpdate03() {
             String sql = "update dept set dept_name=? where id=?";
             jdbcTemplate.update(sql, "测试部", 3);
      }
      // 删除指定部门，验证mysql外键是否生效
      @Test
      public void testUpdate02() {
             String sql = "delete from dept where id=?";
             jdbcTemplate.update(sql, 1);
      }
      // 往部门表中新增一条记录
      @Test
      public void testUpdate01() {
             String sql = "insert into dept(dept_name) values(?)";
             jdbcTemplate.update(sql, "开发部");
      }
      @Test
      public void testDataSource() throws SQLException {
             DataSource dataSource = ctx.getBean(DataSource.class);
             System.out.println(dataSource.getConnection());
      }
}