新手详细讲解：Spring-jdbc简单的增删改查，详细文件配置

向右看齐 2023-10-10 09:57 27阅读 0赞

### 新手详细讲解：Spring-jdbc简单的增删改查 ###

简单看一下这次项目的目录：创建一个maven的quickstart项目  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExNDI0MjYxMzAz_size_16_color_FFFFFF_t_70]

<table> 
 <thead> 
  <tr> 
   <th>文件</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>db.properties</strong></td> 
   <td>设置数据库连接属性</td> 
  </tr> 
  <tr> 
   <td><strong>pom.xml</strong></td> 
   <td>项目所以依赖的架包</td> 
  </tr> 
  <tr> 
   <td><strong>SpringJDBC.xml</strong></td> 
   <td>设置数据库连接属性</td> 
  </tr> 
  <tr> 
   <td><strong>User.java</strong></td> 
   <td>数据库中表对应属性</td> 
  </tr> 
  <tr> 
   <td><strong>UserRowMapper.java</strong></td> 
   <td>结果集封装成一个对象</td> 
  </tr> 
  <tr> 
   <td><strong>UserDAO.java</strong></td> 
   <td>简单的数据业务操作</td> 
  </tr> 
  <tr> 
   <td><strong>TestCase.java</strong></td> 
   <td>测试类</td> 
  </tr> 
 </tbody> 
</table>

#### 1.db.properties ####

设置数据库连接属性  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExNDI0MjYxMzAz_size_16_color_FFFFFF_t_70 1]

#### 2.pom.xml ####

导入项目需要的架包，保证联网的情况下，再保存，确保不会因为网络原因导致架包下载失败。（如果网络不稳定，比较麻烦，后期maven会报各种错）

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
    
      <groupId>com.haihsi</groupId>
      <artifactId>SpringJDBC</artifactId>
      <version>0.0.1-SNAPSHOT</version>
      <packaging>jar</packaging>
    
      <name>SpringJDBC</name>
      <url>http://maven.apache.org</url>
    
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
      </properties>
    
      <dependencies>
      	
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.2</version>
        </dependency>
        
         <dependency>
         	<groupId>org.springframework</groupId>
         	<artifactId>spring-webmvc</artifactId>
         	<version>4.3.3.RELEASE</version>
         </dependency>
         
         
          <dependency>
         	<groupId>mysql</groupId>
         	<artifactId>mysql-connector-java</artifactId>
         	<version>5.1.40</version>
          </dependency>
         
          
          <dependency>
         	<groupId>commons-dbcp</groupId>
         	<artifactId>commons-dbcp</artifactId>
         	<version>1.4</version>
          </dependency>
          
           
          <dependency>
         	<groupId>org.springframework</groupId>
         	<artifactId>spring-jdbc</artifactId>
         	<version>4.3.3.RELEASE</version>
          </dependency>
      </dependencies>
    </project>

#### 3.SpringJDBC.xml ####

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd">
    	
    	<context:component-scan base-package="com.haishi"/>
    	
    	<util:properties id="db" location="classpath:db.properties"/>
    	
    	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    		<property name="driverClassName" value="#{db.driver}"/>
    		<property name="url" value="#{db.url}"/>
    		<property name="username" value="#{db.user}"/>
    		<property name="password" value="#{db.pwd}"/>
    	</bean>
    	
    	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		<property name="dataSource" ref="dataSource"/>
    	</bean>
    </beans>

#### 4.User.java ####

实体类，对应数据表中的属性，谁知get，set方法

package com.haishi.entity;
    import java.sql.Timestamp;
    
    public class User{ 
    	private Integer id;
    	private String userName;
    	private String pwd;
    	private String phone;
    	private String email;
    	private Timestamp time;
    	
    	public Integer getId() { 
    		return id;
    	}
    	public void setId(Integer id) { 
    		this.id = id;
    	}
    	public String getUserName() { 
    		return userName;
    	}
    	public void setUserName(String userName) { 
    		this.userName = userName;
    	}
    	public String getPwd() { 
    		return pwd;
    	}
    	public void setPwd(String pwd) { 
    		this.pwd = pwd;
    	}
    	public String getPhone() { 
    		return phone;
    	}
    	public void setPhone(String phone) { 
    		this.phone = phone;
    	}
    	public String getEmail() { 
    		return email;
    	}
    	public void setEmail(String email) { 
    		this.email = email;
    	}
    	public Timestamp getTime() { 
    		return time;
    	}
    	public void setTime(Timestamp time) { 
    		this.time = time;
    	}
    	public String toString(){ 
    		return "User [id=" + id + ", userName=" + userName + ", pwd=" + pwd + ", phone=" + phone + ", email=" + email
    				+ ", time=" + time + "]";
    	}
    }

#### 5.UserRowMapper .java ####

package com.haishi.entity;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import org.springframework.jdbc.core.RowMapper;
    
    public class UserRowMapper 
    implements RowMapper<User>{ 
    	
    	/** * 该方法用于将jdbcTemplate查询的 * 结果集封装成一个user对象 * * ResultSet rs：查询的结果集 * int index：每一行记录的索引 * 即第一行记录index的值是0 */
    	@Override
    	public User mapRow
    	(ResultSet rs,int index) 
    			throws SQLException{ 
    		
    		User u=new User();
    		u.setId(rs.getInt("id"));
    		u.setUserName(rs.getString("user_name"));
    		u.setPwd(rs.getString("pwd"));
    		u.setEmail(rs.getString("email"));
    		u.setPhone(rs.getString("phone"));
    		u.setTime(rs.getTimestamp("time"));
    		
    		return u;
    	}
    }

#### 6.UserDAO.java ####

具体的数据操纵

package com.haishi.DAO;
    import java.util.List;
    import javax.annotation.Resource;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    import com.haishi.entity.User;
    import com.haishi.entity.UserRowMapper;
    
    @Repository
    public class UserDAO{ 
    	
    	@Resource
    	private JdbcTemplate temp;
    	
    	public List<User> findALL(){ 
    		String sql="select * from user_t";
    		
    		UserRowMapper rowMapper=
    				new UserRowMapper(); 
    		
    		List<User> list
    		=temp.query(sql,rowMapper);
    		return list;
    	}
    	
    	public User findById(int id){ 
    		String sql="select * from user_t where id=?";
    		UserRowMapper rowMapper=
    				new UserRowMapper();
    		
    		Object[] params=new Object[]{ id};
    		
    		User u
    		=temp.queryForObject(sql,params,rowMapper);
    		
    		return u;
    	}
    	
    	public void save(User u){ 
    		String sql="insert into user_t "
    				+ "values(null,?,?,?,?,now())";
    		
    		Object[] params=new Object[]{ 
    				u.getUserName(),u.getPwd(),
    				u.getPhone(),u.getEmail()};
    		
    		temp.update(sql,params);
    	}
    	
    	public void update(User u){ 
    		String sql="update user_t set pwd=? "
    				+ "where user_name=?";
    		
    		Object[] params=
    				new Object[]
    			{ u.getPwd(),u.getUserName()};
    		
    		
    		temp.update(sql,params);
    	}
    	
    	
    	public void delete(int id){ 
    		String sql="delete from user_t where id=?";
    		
    		Object[] params=new Object[]{ id};
    		
    		temp.update(sql,params);
    	}
    }

#### 7.TestCase.java ####

测试不同的方法解开对应的@test注解就可以了

package Test;
    import java.util.List;
    import org.junit.Before;
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import com.haishi.DAO.UserDAO;
    import com.haishi.entity.User;
    
    public class TestCase{ 
    	private static final String XML="SpringJDBC.xml";
    	
    	private ApplicationContext ac;
    	
    	@Before
    	public void init(){ 
    		ac=new ClassPathXmlApplicationContext(XML);
    	}
    	
    // @Test
    	public void test1(){ 
    		UserDAO dao
    		=ac.getBean("userDAO",UserDAO.class);
    		List<User> list=dao.findALL();
    		System.out.println(list);
    	}
    	
    	@Test
    	public void test2(){ 
    		UserDAO dao
    		=ac.getBean("userDAO",UserDAO.class);
    		User u=dao.findById(1002);
    		System.out.println(u);
    	}
    	
    	
    // @Test
    	public void test3(){ 
    		UserDAO dao
    		=ac.getBean("userDAO",UserDAO.class);
    		
    		User u=new User();
    		u.setUserName("kitty");
    		u.setPhone("12112112111");
    		u.setPwd("123456");
    		u.setEmail("123@qq.com");
    		
    		dao.save(u);
    	}
    	
    	
    // @Test
    	public void test4(){ 
    		UserDAO dao
    		=ac.getBean("userDAO",UserDAO.class);
    		
    		User u=new User();
    		u.setUserName("kitty");
    		u.setPhone("12112112111");
    		u.setPwd("654321");
    		u.setEmail("123@qq.com");
    		
    		dao.update(u);
    	}
    	
    	
    	
    	@Test
    	public void test5(){ 
    		UserDAO dao
    		=ac.getBean("userDAO",UserDAO.class);
    		
    		dao.delete(1002);
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExNDI0MjYxMzAz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190924192934672.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExNDI0MjYxMzAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExNDI0MjYxMzAz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190924195142910.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ExNDI0MjYxMzAz,size_16,color_FFFFFF,t_70