Spring 三种数据库连接方式、传统连接、JdbcTemplate、Spring Jpa data（上）

把以前所学的东西 在跟着视频过一遍 总结一下 视频地址https://www.imooc.com/learn/821

 

传统连接和JdbcTemplate大概总结一下就行，因为自己也不常用

这篇文章就只是写传统和jdbcTemplate 下一篇详细下Spring Jpa data

项目结构

 

 

传统的连接方式

 

配置文件

db.properties

jdbc.url = jdbc:mysql://localhost:3306/jdbc
jdbc.user = root
jdbc.password = root
jdbc.driverClass = com.mysql.jdbc.Driver

连接工具JDBCUtil.java

package com.binglian.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类：
 * 1） 获取Connection
 * 2） 释放资源
 */
public class JDBCUtil {


    /**
     * 获取Connection
     * @return 所获得到的JDBC的Connection
     */
    public static Connection getConnection() throws Exception {

        /**
         * 不建议大家把配置硬编码到代码中
         *
         * 最佳实践：配置性的建议写到配置文件中
         */
//        String url = "jdbc:mysql:///spring_data";
//        String user = "root";
//        String password = "root";
//        String driverClass = "com.mysql.jdbc.Driver";

        InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(inputStream);


        String url = properties.getProperty("jdbc.url");
        String user = properties.getProperty("jdbc.user");
        String password = properties.getProperty("jdbc.password");
        String driverClass = properties.getProperty("jdbc.driverClass");

        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    /**
     * 释放DB相关的资源
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void release(ResultSet resultSet,
                               Statement statement, Connection connection){

        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }



}

 

 

然后是dao层

先是接口，然后再是接口

package com.binglian.dao;

import java.util.List;

import com.binglian.domain.Student;

/**
 * StudentDAO访问接口
 */
public interface StudentDAO {

	 /**
     * 查询所有学生
     * @return 所有学生
     */
    public List<Student> query();

    /**
     * 添加一个学生
     * @param student 待添加的学生
     */
    public void save(Student student);

}

 

实现接口

Connection数据库连接

PreparedStatement 用来执行SQL的查询语句这些

ResultSet 返回集

package com.binglian.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.binglian.domain.Student;
import com.binglian.util.JDBCUtil;

/**
 * StudentDAO访问接口实现类：通过最原始的JDBC的方式操作
 * @author binglian
 *
 */
public class StudentDAOImpl implements StudentDAO{

	public List<Student> query() {

		List<Student> students=new ArrayList<Student>();
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		String sql="select id,name,age from student";
		try {
			connection=JDBCUtil.getConnection();
			preparedStatement=connection.prepareStatement(sql);
			resultSet=preparedStatement.executeQuery();
		
			Student student=null;
		
			while(resultSet.next()){
				
				int id=resultSet.getInt("id");
				String name=resultSet.getString("name");
				int age=resultSet.getInt("age");
				
				student=new Student();
				student.setId(id);
				student.setAge(age);
				student.setName(name);
				
				students.add(student);
			}
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.release(resultSet, preparedStatement, connection);
		}
		return students;
	}
	
	
    public void save(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "insert into student(name, age) values(?,?)";
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setInt(2, student.getAge());
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(resultSet,preparedStatement,connection);
        }
    }

}

 

 

JdbcTemplate

 beans-new.xml配置文件

数据源配置

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="url" value="jdbc:mysql://localhost:3306/jdbc"/>
    </bean>

用户名 和密码 以及路径 它和传统的jdbc不同

<?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.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="url" value="jdbc:mysql://localhost:3306/jdbc"/>
    </bean>

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

    <bean id="studentDAO" class="com.binglian.dao.StudentDAOSpringJdbcImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

dao层

package com.binglian.dao;

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

import com.binglian.domain.Student;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * StudentDAO访问接口实现类：通过Spring jdbc的方式操作
 */
public class StudentDAOSpringJdbcImpl implements  StudentDAO{

	private JdbcTemplate jdbcTemplate;
	
	
	public List<Student> query() {

		final List<Student> students=new ArrayList<Student>();
		
		String sql="select id,name,age from student";
		
		
		jdbcTemplate.query(sql, new RowCallbackHandler(){

			public void processRow(ResultSet rs) throws SQLException {
				
				int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");

                Student student = new Student();
                student.setId(id);
                student.setName(name);
                student.setAge(age);

                students.add(student);
				
			}});
		
		return students;
	}
	public void save(Student student) {
		String sql="insert into student(name,age) values(?,?)";
		jdbcTemplate.update(sql,new Object[]{student.getName(),student.getAge()});
	}
	
	
	public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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