spring-jdbc

传统的数据库包含冗余样板的代码为

package com.mysql;

import java.sql.*;

/**
 * Created by cuimiao on 15/12/10.
 */
public class TraditionalJDBC {

        public static final String DB_DRIVER = "org.gjt.mm.mysql.Driver";
        public static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8";
        public static final String DB_USER = "root";
        public static final String DB_PASSWORD = "nieyijing214";
        public static void main(String[] args) throws Exception {
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            PreparedStatement preparedStatement = null;
            String sql = "";
            String sqlPre = "";
            //加载数据库驱动
            Class.forName(DB_DRIVER);
            connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            //增
            sql = "insert into family (name,role) values('崔淼','老公'),('聂绎静','女朋友'),('嘿嘿','小三')";
            statement = connection.createStatement();
            statement.executeUpdate(sql);

            //删
            sql = "delete from family where 1 order by id DESC limit 1";
            statement = connection.createStatement();
            statement.executeUpdate(sql);

            //改
            sql = "UPDATE family SET role = '老婆' WHERE 1 ORDER BY id DESC limit 1";
            statement = connection.createStatement();
            statement.executeUpdate(sql);

            //预处理
            sqlPre = "insert into family (name,role) values(?,?)";
            preparedStatement = connection.prepareStatement(sqlPre);
            preparedStatement.setString(1, "崔小静");
            preparedStatement.setString(2, "女儿");
            preparedStatement.executeUpdate();
            preparedStatement.close();

            //查
            sql = "SELECT * FROM family";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String role = resultSet.getString("role");

                System.out.println(id + "    " + name + "    " + role);
            }

            //关闭数据库
            resultSet.close();
            statement.close();
            connection.close();
            System.out.println(connection);
            System.out.println(System.getProperty("java.class.path"));
        }

}

为了消除冗余代码,使用JdbcTemplate，JdbcTemplate中有dataSource这个property，然后进行装配，可以省去好多样板代码。

<?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:jee="http://www.springframework.org/schema/jee"
       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.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

       <!--<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">-->
              <!--<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>-->
              <!--<property name="url" value="jdbc:hsqldb:hsql://localhost:3306/test"/>-->
              <!--<property name="username" value="root"/>-->
              <!--<property name="password" value="nieyijing214"/>-->
       <!--</bean>-->

       <context:component-scan base-package="com"/>

       <bean id="jdbc" class="com.jdbc.JDBCTemplate"/>

       <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
              <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
              <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&amp;characterEncoding=utf-8"/>
              <property name="username" value="root"/>
              <property name="password" value="nieyijing214"/>
              <property name="initialSize" value="5"/>
              <property name="maxActive" value="10"/>
       </bean>
</beans>

package com.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 * Created by cuimiao on 15/12/10.
 */
@Component
public class JDBCTemplate {

    @Autowired//从这个可以看出autowire时是从全局寻找bean，包括在类上用component的，还有在xml中配置的;扫描是扫描组件。
    private DataSource dataSource;

    public void dataOperation() {
//        ApplicationContext context = new ClassPathXmlApplicationContext("spring-datasource.xml");
//        //加载数据库驱动
//        DataSource dataSource = (DataSource) context.getBean("dataSource");

            JdbcTemplate jdbcTemplate = new org.springframework.jdbc.core.JdbcTemplate(dataSource);
            String sql = "";
            String sqlPre = "";

            //增
            sql = "insert into family (name,role) values('崔淼','老公'),('聂绎静','女朋友'),('嘿嘿','小三')";
            jdbcTemplate.update(sql);

            //删
            sql = "delete from family where 1 order by id DESC limit 1";
            jdbcTemplate.update(sql);

            //改
            sql = "UPDATE family SET role = '老婆' WHERE 1 ORDER BY id DESC limit 1";
            jdbcTemplate.update(sql);

            //预处理
            sqlPre = "insert into family (name,role) values(?,?)";
            jdbcTemplate.update(sqlPre, "崔小静", "女儿");

            //查
            sql = "SELECT * FROM family";
            List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
            resultList = jdbcTemplate.queryForList(sql);

            for(Map<String,Object> map:resultList){
                long id = (Long)map.get("id");
                String name = (String)map.get("name");
                String role = (String)map.get("role");
                System.out.println(id + "    " + name + "    " + role);
            }
    }
}

测试类为

package jdbcTemplate;

import com.jdbc.JDBCTemplate;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * Created by cuimiao on 15/12/11.
 */
public class TestJdbcTemplate {

    @Test
    public void main() {
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-datasource.xml");
        JDBCTemplate jdbcTemplate = (JDBCTemplate)context.getBean("jdbc");
        jdbcTemplate.dataOperation();
    }

}

基于Dao的方式是这样的

package com.SimpleJdbcTemplate.Dao;

import com.SimpleJdbcTemplate.domian.Family;

import java.util.List;

/**
 * Created by cuimiao on 15/12/13.
 */
public interface FamilyDao {
    public void insert(Family family);
    public void update(Family family);
    public void delete();
    public List<Family> selectAll(Family family);
    public void batchInsert(List<Family> familyList);

}

package com.SimpleJdbcTemplate.domian;

/**
 * Created by cuimiao on 15/12/13.
 */
public class Family {
    private int id;
    private String name;
    private String role;

    public Family() {
    }

    public Family(int id, String name, String role) {
        this.id = id;
        this.name = name;
        this.role = role;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }
}

package com.SimpleJdbcTemplate.Impl;

import com.SimpleJdbcTemplate.Dao.FamilyDao;
import com.SimpleJdbcTemplate.domian.Family;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by cuimiao on 15/12/13.
 */
public class FamilyDaoImpl extends JdbcDaoSupport implements FamilyDao{

    public void insert(Family family){
        String sql = "insert into family (name,role) values (?,?)";
        getJdbcTemplate().update(sql,family.getName(),family.getRole());
    }
    public void delete(){
        String sql = "delete from family where 1 order by id DESC limit 1";
        getJdbcTemplate().update(sql);
    }

    public void update(Family family){
        String sql = "UPDATE family SET role = ? WHERE 1 ORDER BY id DESC limit 1";
        getJdbcTemplate().update(sql,family.getRole());
    }

    public List<Family> selectAll(Family family){
        String sql = "SELECT * FROM family";
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        return getJdbcTemplate().query(sql, BeanPropertyRowMapper.newInstance(Family.class));
    }

    public void batchInsert(List<Family> familyList){
        String sql = "insert into family(name,role)values(?,?)";
        List<Object[]> parameters = new ArrayList<Object[]>();
        for (Family f : familyList) {
            parameters.add(new Object[] { f.getName(), f.getRole() });
        }
        getJdbcTemplate().batchUpdate(sql, parameters);
    }


}

可以用JdbcDaoSupport的子类来搞impl类只是因为JdbcDaoSupport中有getJdbcTemplate这种get Tepmlate的方法，而且默认JdbcDaoSupport有datasource的属性。所以xml必须有datasource。

<?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:component-scan base-package="com"/>

       <bean id="familyDaoImpl" class="com.SimpleJdbcTemplate.Impl.FamilyDaoImpl">
              <property name="dataSource" ref="dataSource"/>
       </bean>

       <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
              <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
              <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&amp;characterEncoding=utf-8"/>
              <property name="username" value="root"/>
              <property name="password" value="nieyijing214"/>
              <property name="initialSize" value="5"/>
              <property name="maxActive" value="10"/>
       </bean>
</beans>

如果不用这种support类，就impl某个Dao类，这种情况代码为这样。

package com.SimpleJdbcTemplate.Impl;

import com.SimpleJdbcTemplate.Dao.FamilyDao;
import com.SimpleJdbcTemplate.domian.Family;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by cuimiao on 15/12/13.
 */
public class FamilyDaoImplWithoutExtend implements FamilyDao {

//    @Autowired
    private JdbcTemplate jdbcTemplate;

//    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void insert(Family family){
        String sql = "insert into family (name,role) values (?,?)";
        jdbcTemplate.update(sql, family.getName(), family.getRole());
    }
    public void delete(){
        String sql = "delete from family where 1 order by id DESC limit 1";
        jdbcTemplate.update(sql);
    }

    public void update(Family family){
        String sql = "UPDATE family SET role = ? WHERE 1 ORDER BY id DESC limit 1";
        jdbcTemplate.update(sql, family.getRole());
    }

    public List<Family> selectAll(Family family){
        String sql = "SELECT * FROM family";
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Family.class));
    }

    public void batchInsert(List<Family> familyList){
        String sql = "insert into family(name,role)values(?,?)";
        List<Object[]> parameters = new ArrayList<Object[]>();
        for (Family f : familyList) {
            parameters.add(new Object[] { f.getName(), f.getRole() });
        }
        jdbcTemplate.batchUpdate(sql, parameters);
    }

}

<?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="familyDaoImplWithoutExtend" class="com.SimpleJdbcTemplate.Impl.FamilyDaoImplWithoutExtend">
              <property name="jdbcTemplate" ref="jdbc"/>
       </bean>

       <bean id="jdbc" class="org.springframework.jdbc.core.JdbcTemplate">
              <property name="dataSource" ref="dataSource"/>
              <!--<constructor-arg name="dataSource" ref="dataSource"/>-->
       </bean>

       <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
              <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
              <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&amp;characterEncoding=utf-8"/>
              <property name="username" value="root"/>
              <property name="password" value="nieyijing214"/>
              <property name="initialSize" value="5"/>
              <property name="maxActive" value="10"/>
       </bean>

</beans>

这个看的非常清楚，FamilyDaoImplWithoutExtend包含JdbcTemplate, JdbcTemplate包含dataSource。

测试类为

package TestSimpleJdbcTemplate;

import com.SimpleJdbcTemplate.Impl.FamilyDaoImplWithoutExtend;
import com.SimpleJdbcTemplate.domian.Family;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by cuimiao on 15/12/13.
 */
public class TestSimpleJdbcWithoutExtend {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-simple-dao-without-extend.xml");
        FamilyDaoImplWithoutExtend familyDao = (FamilyDaoImplWithoutExtend) context.getBean("familyDaoImplWithoutExtend");

        List<Family> familyList = new ArrayList<Family>();
        familyList.add(new Family(0,"崔淼","老公"));
        familyList.add(new Family(0,"聂绎静","老婆"));
        familyList.add(new Family(0, "嘿嘿", "小三"));
        familyDao.batchInsert(familyList);

        familyDao.delete();

        familyDao.update(new Family(0, "", "老婆"));

        familyDao.insert(new Family(0, "女儿", "崔小静"));

        List<Family> resultList = familyDao.selectAll(new Family());

        for(Family family:resultList){
            long id = family.getId();
            String name = family.getName();
            String role = family.getRole();
            System.out.println(id + "    " + name + "    " + role);
        }
    }
}

结果为

600    崔淼    老公
601    聂绎静    老婆
602    嘿嘿    小三
609    崔淼    老公
610    聂绎静    老婆
612    崔小静    女儿
613    崔淼    老公
614    聂绎静    老婆
616    崔小静    女儿
617    崔淼    老公
618    聂绎静    老婆
620    女儿    崔小静
621    崔淼    老公
622    聂绎静    老婆
624    崔小静    女儿
625    崔淼    老公
626    聂绎静    老婆
628    崔小静    女儿