Spring对JDBC的支持

所用到的文件截图

添加Maven依赖

以往JDBC的操作

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by liguodong on 2015/11/11.
 */
public class UserDao {

    /**
     * 保存方法
     * 需求优化的地方
     * 1、连接管理
     * 2、需要优化的地方
     * @throws SQLException
     */
    public void save() throws SQLException {
        String sql ="insert into students(id, name, sal) values(1,'liguodong',1000.00)";
        Connection conn = null;
        Statement stmt = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接对象
            conn = DriverManager.getConnection("jdbc:mysql://192.168.141.17/lgdtest",
                    "root",
                    "password");
            //执行命令对象
            stmt = conn.createStatement();
            stmt.execute(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (stmt!=null){
                stmt.close();
            }
            if(conn!=null){
                conn.close();
            }
        }

    }
}

DAO实例

测试结果

---

连接管理优化

package jdbc;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by liguodong on 2015/11/11.
 */
public class UserDaoOpt {

    //IOC容器注入
    private DataSource dataSource;
    public void setDataSource(DataSource dataSource){
        this.dataSource = dataSource;
    }

    public void save() throws SQLException {
        String sql ="insert into students(id, name, sal) values(1,'liguodong',1000.00)";
        Connection conn = null;
        Statement stmt = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接对象
            conn = dataSource.getConnection();

            //执行命令对象
            stmt = conn.createStatement();
            stmt.execute(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (stmt!=null){
                stmt.close();
            }
            if(conn!=null){
                conn.close();
            }
        }

    }
}

DAO实例配置

测试结果：

---

数据查找

package jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by liguodong on 2015/11/11.
 */
public class UserDaoOpt2 {

    //IOC容器注入
    private DataSource dataSource;
    public void setDataSource(DataSource dataSource){
        this.dataSource = dataSource;
    }

    public void save() {
        String sql ="insert into students(id, name, sal) values(2,'liguodong',1000.00)";
        //使用JDBC模板工具简化JDBC操作
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //JdbcTemplate jdbcTemplate = new JdbcTemplate();
        //jdbcTemplate.setDataSource(dataSource);
        jdbcTemplate.update(sql);//更新（插入，修改删除）
    }

    public Map<String,Object> findbyId(int id){
        String sql = "select * from students where id= ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        Map<String,Object> map = jdbcTemplate.queryForMap(sql, id);
        //System.out.println(map);
        return map;
    }

    public List<Map<String,Object>> findbyId2(int id){
        String sql = "select * from students where id= ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,id);
        //System.out.println(list);
        return list;
    }

    public List<Map<String,Object>> findbyId3(){
        String sql = "select * from students";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        //System.out.println(list);
        return list;
    }


    public List<Students> findbyId4(int id){
        String sql = "select * from students where id= ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Students> list = jdbcTemplate.query(sql,
                new RowMapper<Students>() {
                    //如何封装一行记录
                    @Override
                    public Students mapRow(ResultSet resultSet, int i) throws SQLException {
                        Students students = new Students();
                        students.setId(resultSet.getInt(/*"id"*/1));//或者students.setId(resultSet.getInt("1"));
                        students.setName(resultSet.getString(/*"name"*/2));//students.setName(resultSet.getString("2"));
                        students.setSal(resultSet.getInt(/*"sal"*/3));
                        return students;
                    }
                }
                ,id);
        //System.out.println(list);
        return list;
    }


    public List<Students> findbyAll(){
        String sql = "select * from students";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Students> list = jdbcTemplate.query(sql,
            new RowMapper<Students>() {
                //如何封装一行记录
                @Override
                public Students mapRow(ResultSet resultSet, int i) throws SQLException {
                    Students students = new Students();
                    students.setId(resultSet.getInt(/*"id"*/1));//或者students.setId(resultSet.getInt("1"));
                    students.setName(resultSet.getString(/*"name"*/2));//students.setName(resultSet.getString("2"));
                    students.setSal(resultSet.getInt(/*"sal"*/3));
                    return students;
                }
            });
        //System.out.println(list);
        return list;
    }

    public List<Students> findbyAllEnd(){
        String sql = "select * from students";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Students> list = jdbcTemplate.query(sql,new MyResult());
        //System.out.println(list);
        return list;
    }

    class MyResult implements RowMapper<Students>{
        //如何封装一行记录
        @Override
        public Students mapRow(ResultSet resultSet, int i) throws SQLException {
            Students students = new Students();
            students.setId(resultSet.getInt(/*"id"*/1));//或者students.setId(resultSet.getInt("1"));
            students.setName(resultSet.getString(/*"name"*/2));//students.setName(resultSet.getString("2"));
            students.setSal(resultSet.getInt(/*"sal"*/3));
            return students;
        }
    }

}

DAO实例配置

测试及结果

---

JdbcTemplate优化

package jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by liguodong on 2015/11/11.
 */
public class UserDaoOptEnd {

    /*//IOC容器注入
    private DataSource dataSource;
    public void setDataSource(DataSource dataSource){
        this.dataSource = dataSource;
    }*/
    //将jdbcTemplate放入IOC容器
    private  JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }

    public void save() {
        String sql ="insert into students(id, name, sal) values(100,'liguodong',1550.00)";
        jdbcTemplate.update(sql);//更新（插入，修改删除）
    }

    public Map<String,Object> findbyId(int id){
        String sql = "select * from students where id= ?";
        Map<String,Object> map = jdbcTemplate.queryForMap(sql, id);
        return map;
    }

    public List<Map<String,Object>> findbyId2(int id){
        String sql = "select * from students where id= ?";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,id);
        //System.out.println(list);
        return list;
    }

    public List<Map<String,Object>> findbyId3(){
        String sql = "select * from students";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }


    public List<Students> findbyId4(int id){
        String sql = "select * from students where id= ?";
        List<Students> list = jdbcTemplate.query(sql,
                new RowMapper<Students>() {
                    //如何封装一行记录
                    @Override
                    public Students mapRow(ResultSet resultSet, int i) throws SQLException {
                        Students students = new Students();
                        students.setId(resultSet.getInt(/*"id"*/1));//或者students.setId(resultSet.getInt("1"));
                        students.setName(resultSet.getString(/*"name"*/2));//students.setName(resultSet.getString("2"));
                        students.setSal(resultSet.getInt(/*"sal"*/3));
                        return students;
                    }
                }
                ,id);
        //System.out.println(list);
        return list;
    }


    public List<Students> findbyAll(){
        String sql = "select * from students";
        List<Students> list = jdbcTemplate.query(sql,
            new RowMapper<Students>() {
                //如何封装一行记录
                @Override
                public Students mapRow(ResultSet resultSet, int i) throws SQLException {
                    Students students = new Students();
                    students.setId(resultSet.getInt(/*"id"*/1));//或者students.setId(resultSet.getInt("1"));
                    students.setName(resultSet.getString(/*"name"*/2));//students.setName(resultSet.getString("2"));
                    students.setSal(resultSet.getInt(/*"sal"*/3));
                    return students;
                }
            });
        //System.out.println(list);
        return list;
    }

    public List<Students> findbyAllEnd(){
        String sql = "select * from students";
        List<Students> list = jdbcTemplate.query(sql,new MyResult());
        //System.out.println(list);
        return list;
    }

    class MyResult implements RowMapper<Students>{
        //如何封装一行记录
        @Override
        public Students mapRow(ResultSet resultSet, int i) throws SQLException {
            Students students = new Students();
            students.setId(resultSet.getInt(/*"id"*/1));//或者students.setId(resultSet.getInt("1"));
            students.setName(resultSet.getString(/*"name"*/2));//students.setName(resultSet.getString("2"));
            students.setSal(resultSet.getInt(/*"sal"*/3));
            return students;
        }
    }
}

Dao实例配置

测试