spring中对数据库的基本增删改查操作

title: spring中对数据库的基本增删改查操作
date: 2018-3-1 14:31:41
categories: Spring
tags: Spring

---

xl_echo编辑整理，欢迎转载，转载请声明文章来源。更多案例、资料请联系QQ：1280023003

spring中对数据库的基本增删改查操作

实体类代码

package com.echo.domian;

public class User {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;

    public User(){}
    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 getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex=" + sex + "]";
    }

}

测试类代码

package com.echo.springjdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
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.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.echo.domian.User;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class JdbcTemplateTest2 {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    //修改数据库
    public void updateTest1(){
        jdbcTemplate.update("update springtest set name = 'tom' where id = 1");
    }

    @Test
    public void addTest2(){
        jdbcTemplate.update("insert into springtest values(null,'赵六',23,'男')");
    }

    @Test
    public void delTest3(){
        jdbcTemplate.update("delete from springtest where id=? ",4);
    }

    @Test
    //返回简单的数据类型
    public void selectTest4(){
        String name = jdbcTemplate.queryForObject("select name from springtest where id = ?", String.class, 2);
        System.out.println(name);
    }

    @Test
    //返回简单的数据类型
    public void selectTest5(){
        Integer a = jdbcTemplate.queryForObject("select count(*) from springtest", Integer.class);
        System.out.println(a);
    }

    @Test
    //返回一个复杂的数据类型
    public void selectTest6(){
        User user = jdbcTemplate.queryForObject("select * from springtest where id = ?", new RowMapper<User>(){

            //将结果封装进入对象的匿名内部内方法
            @Override
            public User mapRow(ResultSet rs, int arg1) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setAge(rs.getInt("age"));
                user.setName(rs.getString("name"));
                user.setSex(rs.getString("sex"));
                return user;
            }

        }, 2);

        System.out.println(user);
    }

    @Test
    //返回一个复杂的数据类型(返回一个数据集合)
    public void selectTest7(){
        List<User> user = jdbcTemplate.query("select * from springtest", new RowMapper<User>(){

            //将结果封装进入对象的匿名内部内方法
            @Override
            public User mapRow(ResultSet rs, int arg1) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setAge(rs.getInt("age"));
                user.setName(rs.getString("name"));
                user.setSex(rs.getString("sex"));
                return user;
            }

        });

        System.out.println(user);
    }

    @Test
    //使用BeanPropertyRowMapper操作数据库，返回一个复杂的数据类型(返回一个数据集合)
    public void selectTest8(){
        /*User user = jdbcTemplate.queryForObject("select * from springtest where id = ?", 
                new BeanPropertyRowMapper<User>(User.class), 2);*/

        List<User> user = jdbcTemplate.query("select * from springtest", 
                new BeanPropertyRowMapper<User>(User.class));

        System.out.println(user);
    }
}

核心配置文件

<?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">
        <!-- bean definitions here -->

    <!-- spring内置连接池 -->
    <bean id="driverManagerDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/myxlsorry"></property>
        <property name="username" value="root"></property>
        <property name="password" value="20263800mq"></property>
    </bean>



    <!-- 创建c3p0连接池 -->
    <!-- <bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/myxlsorry"></property>
        <property name="user" value="root"></property>
        <property name="password" value="20263800mq"></property>
    </bean> -->



    <!-- 引入外部属性文件的值db.properties -->
    <context:property-placeholder location="classpath:db.properties"/>
    <bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>


    <!-- 指定连接池 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="c3p0DataSource"></property>
    </bean>

</beans>