spring jdbc 数据库连接初学

spring jdbc 数据库连接在实际的开发中虽然很少用,因为其全映射会做太多的无用功,具体的细节我也不清楚,反正就是很少用,不过作为学习,学到呢这里,记录以下还是很有必要的.

测试准备:
开发环境:eclipse maven spring mysql8.0
所需jar包:

• <1>. spring 4个核心jar包: spring-core-5.0.8.RELEASE.jar,
  spring-beans-5.0.8.RELEASE.jar ,
  spring-expression-5.0.8.RELEASE.jar ,
  spring-context-5.0.8.RELEASE.jar ,
• <2>. 依赖包:commons-logging-1.2.jar
• <3>.测试包:junit-4.12.jar
• <4>. 数据库连接相关的包:spring-jdbc-5.0.8.RELEASE.jar, mysql-connector-java-8.0.12.jar

因为是在maven中完成,所以以上的包都通过在pom.xml中配置完成
配置代码如下:

<dependencies>
        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.0.8.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.8.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.0.8.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-expression -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>5.0.8.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.8.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.12</version>
        </dependency>

    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.2</version>
                <configuration>
                    <path>/</path>
                    <port>80</port>
                    <server>tomcat7</server>
                </configuration>
            </plugin>
        </plugins>
    </build>

在配置号好pom.xml 工程文件之后,我们就开始测试了
在测试包中:

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

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.bd.domain.Teacher;

@RunWith(SpringJUnit4ClassRunner.class)
public class SpringJdbcTest {
    @Test
    public void test() {
        //创建一个连接池
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        //配置相关属性
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/notetest?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("Root1234");

        //生成jdbcTemplate模板对象
        JdbcTemplate jdbcTemplate=new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
        List<Teacher> list=new ArrayList<>();

        jdbcTemplate.query("select * from teacher", new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Teacher t=new Teacher();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setSex(rs.getString("sex"));
                t.setPhone(rs.getString("phone"));
                list.add(t);

            }
        });
        System.out.println(list);
    }

}

以上是我们直接创建连接池设置相关属性来完成的,其实,在学习了spring之后,我们知道在spring中每一个对象都是一个bean
因此我们可以在spring 的配置文件application-config.xml来配置数据库连接的公共部分,就相当于封装了一个工具类
首先我们我们向创建一个mysql.properties 文件,用来保存连接相关的属性

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/notetest?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT&useSSL=false
user=root
password=******

然后在application-config.xml中配置bean

 <!--加载外界文件  -->
    <context:property-placeholder location="classpath:mysql.properties"/>

    <bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${user}"/>
        <property name="password" value="${password}"/>
    </bean>
    <!-- 配置jdbctemplate  模板 -->
    <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--注入数据源   这个name是固定 订餐的set方法确定-->
        <property name="dataSource" ref="dataSource"/>
    </bean>

我们重新改进以下测试类

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

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.bd.domain.Teacher;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:spring/application-config.xml")
public class SpringJdbcTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    public void test() {
        /*DriverManagerDataSource dataSource=new DriverManagerDataSource(); 
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/notetest?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("Root1234");

        JdbcTemplate jdbcTemplate=new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);*/



        List<Teacher> list=new ArrayList<>();

        jdbcTemplate.query("select * from teacher", new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Teacher t=new Teacher();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setSex(rs.getString("sex"));
                t.setPhone(rs.getString("phone"));
                list.add(t);

            }
        });
        System.out.println(list);
    }

}

代码页面更加简洁,创建对象都交给了spring,在实际的开发过程中应该更倾向于后者吧