JdbcTemplate连接数据库

JdbcTemplate连接数据库

1.导入依赖

​ 有些依赖是用不上的，但是也不会影响项目

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>JDBCTemplate-02</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>JDBCTemplate-02</name>
    <packaging>war</packaging>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
        <junit.version>5.7.1</junit.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>

        <!--自动扩充-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.12</version>
        </dependency>

            <!--spring框架 -->
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.3.12</version>
            </dependency>

            <!--spring框架 整合junit -->
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-test</artifactId>
                <version>5.3.12</version>
            </dependency>

            <!--spring框架 aop -->
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.3.9</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>5.2.7.RELEASE</version>
            </dependency>

            <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.9.5</version>
            </dependency>
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjrt</artifactId>
                <version>1.9.5</version>
            </dependency>


            <!-- 单元测试-->
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>

            <!--    DButils驱动-->
            <dependency>
                <groupId>commons-dbutils</groupId>
                <artifactId>commons-dbutils</artifactId>
                <version>1.6</version>
            </dependency>

            <!--    c3p0驱动-->
            <dependency>
                <groupId>com.mchange</groupId>
                <artifactId>c3p0</artifactId>
                <version>0.9.5.2</version>
            </dependency>

            <!-- mysql驱动-->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.11</version>
            </dependency>



    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.3.1</version>
            </plugin>
        </plugins>
    </build>
</project>

2.建立属性与数据库表列名对应的实体类 People类

public class People {

    private Integer id;
    private String name;
    private Integer age;
    private  String adress;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAdress() {
        return adress;
    }

    public void setAdress(String adress) {
        this.adress = adress;
    }

    public People(Integer id, String name, Integer age, String adress) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.adress = adress;
    }

    public People() {
    }

    @Override
    public String toString() {
        return "People{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", adress='" + adress + '\'' +
                '}';
    }
}

3.建立一个Dao接口 PeopleDao 用来写查询语句

public interface PeopleDao {
    List<People> findAllPeople();
}

4.写接口的实现类 PeopleDaoImpl 写方法的实现

@Repository  //将PeopleDaoImpl注册到Spring容器中
public class PeopleDaoImpl implements PeopleDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<People> findAllPeople() {
        List<People> peopleList = jdbcTemplate.query("select * from t_people", new RowMapper<People>() {

            @Override
            public People mapRow(ResultSet rs, int i) throws SQLException {
                People people = new People();
                people.setId(rs.getInt("id"));
                people.setName(rs.getString("sname"));
                people.setAge(rs.getInt("age"));
                people.setAdress(rs.getString("address"));
                return people;
            }
        });
        return peopleList;
    }

5.数据库的连接信息 jdbc.properties

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/tangshi?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
userName = root
passWord = 123456

6.编写Spring的主配置文件 beans.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:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:c="http://www.springframework.org/schema/c"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans.xsd
           https://www.springframework.org/schema/aop
           https://www.springframework.org/schema/aop/spring-aop.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context.xsd

">
    <!--1、注入jdbcTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

        <!--需要在Spring的主配置文件中注入一个数据源对象dataSource-->
        <constructor-arg index="0" ref="dataSource"> </constructor-arg>
    </bean>


    <!--2、注入数据源对象，获取数据源连接 关闭连接 加载驱动等等  目的：获取数据库连接-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!--数据库连接   需要 驱动类、链接地址信息、用户名、密码-->
        <property name="driverClass" value="${driver}"></property>
        <property name="jdbcUrl" value="${url}"></property>
        <property name="user" value="${userName}"></property>
        <property name="password" value="${passWord}"></property>
    </bean>

    <!--3、需要在Spring的主配置文件中加载jdbc.properties 目的：加载资源文件、获取信息-->
    <bean id="jdbc" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <array>
                <value>jdbc.properties</value>
            </array>
        </property>
    </bean>


    <!--注解扫描-->
    <context:component-scan base-package="com.hao"></context:component-scan>
    <!--开启注解-->
    <context:annotation-config/>

</beans>

**7.测试类 **

public class MyTest {
    @Test
    public void test1(){
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        PeopleDao pd = (PeopleDao) context.getBean("peopleDaoImpl");
        List<People> list = pd.findAllPeople();
        for (People people:list) {
            System.out.println(people);
        }
        }
    }

测试截图