jdbc 案例

 要求：使用jdbc完成CRUD操作
 拷贝依赖jar包，并BuildPath:
 mysql-connector-java-5.x.11.jar:mysql驱动包
 durid 连接池
 mysql-jdbc-版本.RELEASE.jar：支持jdbc
 spring-tx-版本.RELEASE.jar:支持事务

代码演示

(1)employee类

package com.keen.proxy.domain;

public class Employee {
    private int id;
    private String name;
    private int age;
    public void setId(int id) {
        this.id = id;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public int getId() {
        return id;
    }
    public String getName() {
        return name;
    }
    public int getAge() {
        return age;
    }

}

（2）employeeDao接口

package com.keen.proxy.dao;

import java.util.List;

import com.keen.proxy.domain.Employee;

public interface EmployeeDAO {
    //增删改查
     void save(Employee e);
    
     void update(Employee e);
     
     void delete(int id);
      
     Employee get(int id);
     
     List<Employee> listAll();
    
    
}

(3)employeDao实现类

package com.keen.proxy.dao;


import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

//import com.keen.proxy.dao.EmployeeDAO;
import com.keen.proxy.domain.Employee;
@Repository
public class EmployeeDAOimpl implements EmployeeDAO{
    private  JdbcTemplate jdbcTemplate;
    //属性：datasource
    public void setDataSource (DataSource ds)   {
        this.jdbcTemplate = new  JdbcTemplate(ds);
    }
    
    @Override
    public void save(Employee e) {
        jdbcTemplate.update("insert into employee (name ,age) value(?,?)", e.getName(),e.getAge());

        
    }

    @Override
    public void update(Employee e) {
        jdbcTemplate.update("UPDATE employee set name = ?,age = ? where id = ?", e.getName(),e.getAge(),e.getId());

        
    }

    @Override
    public void delete(int id) {
        jdbcTemplate.update("DELETE from employee where id = ?", id);

        
    }

    @Override
    public Employee get(int id) {

    List<Employee> list = jdbcTemplate.query("select id,name,age from employee", new Object[] {},new RowMapper<Employee>(){
             //把每行结果映射成一个employee对象
            @Override
            public Employee mapRow(ResultSet rs, int rowNum ) throws SQLException {
                Employee e = new Employee();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setAge(rs.getInt("age"));
                return e;
               }
            });
        return list.size()==1? list.get(0) : null;
    }

    @Override
    public List<Employee> listAll() {

    return  jdbcTemplate.query("select id,name,age from employee", new Object[] {},new RowMapper<Employee>(){
             //把每行结果映射成一个employee对象
            @Override
            public Employee mapRow(ResultSet rs, int rowNum ) throws SQLException {
                Employee e = new Employee();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setAge(rs.getInt("age"));
                return e;
               }
            });
        
    }

    

}

(4)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"
    xmlns:aop="http://www.springframework.org/schema/aop"
    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
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
<!--  从classPath的路径去加载db.properties文件 -->
<context:property-placeholder location="classpath:db.properties" system-properties-mode="NEVER" />
<!-- 配置一个durid的连接池 -->

<bean id = "dataSource" class ="com.alibaba.druid.pool.DruidDataSource"
       init-method = "init" destroy-method="close">
  <property name = "driverClassName" value ="${dirverClassName}"/>
  <property name = "url" value ="${url}"/>
  <property name = "username" value ="${username}"/>
  <property name = "password" value ="${password}"/>
  <property name = "initialSize" value ="${initialSize}"/>

</bean>
<bean id = "employeeDAO" class = "com.keen.proxy.dao.EmployeeDAOimpl">
    <property name="dataSource" ref = "dataSource"/>
</bean>

</beans>

（5） 测试类

package com.keen.proxy;

import static org.junit.jupiter.api.Assertions.*;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import com.keen.proxy.dao.EmployeeDAO;
import com.keen.proxy.domain.Employee;

@SpringJUnitConfig
public class AutoTest {
    @Autowired
    private EmployeeDAO dao;
    
    @Test
    void testSave() throws Exception {
        Employee e = new Employee();
        e.setName("乔峰");
        e.setAge(30);
        dao.save(e);
        
    }
    @Test
    void testUpdate() throws Exception {
        Employee e = new Employee();
        e.setName("张一山");
        e.setAge(22);
        e.setId(5);
        dao.update(e);
        
    }
    @Test
    void testDelete() throws Exception {
        dao.delete(6);
        
        
    }
    @Test
    void testGet() throws Exception {
        Employee e = dao.get(1);
        System.out.println(e);
        
    }
    @Test
    void testListAll() throws Exception {
       List<Employee> list = dao.listAll();
       for(Employee e : list) {
           System.out.println(e);
       }
    }
    
}

(6)resource 文件下写一个db.properties（连接数据库的参数文件）