用Spring中的JdbcTemplate操作数据库

太过爱你忘了你带给我的痛 2021-09-19 02:30 313阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70][]

User.java:

package com.sikiedu.bean;
    
    public class User {
    	private Integer u_id;
    	private String u_username;
    	private String u_password;
    	public Integer getU_id() {
    		return u_id;
    	}
    	public void setU_id(Integer u_id) {
    		this.u_id = u_id;
    	}
    	public String getU_username() {
    		return u_username;
    	}
    	public void setU_username(String u_username) {
    		this.u_username = u_username;
    	}
    	public String getU_password() {
    		return u_password;
    	}
    	public void setU_password(String u_password) {
    		this.u_password = u_password;
    	}
    	@Override
    	public String toString() {
    		return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + "]";
    	}
    }

UserDao.java:

package com.sikiedu.dao;
    
    import java.util.List;
    
    import com.sikiedu.bean.User;
    
    public interface UserDao {
    	
    	//	增
    	void saveUser(User u);
    	//删
    	void deleteUserById(Integer id);
    	
    	//改
    	void updateUser(User u);
    	//查
    	//根据id查找用户
    	User selectUserById(Integer id);
    	//查找全部用户list
    	List<User> selectAllUser();
    	//查找用户数量
    	Integer selectUserCount();
    }

UserDaolmpl.java:

package com.sikiedu.dao;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.support.JdbcDaoSupport;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    import com.sikiedu.bean.User;
    
    public class UserDaolmpl extends JdbcDaoSupport implements UserDao {
    	private static ComboPooledDataSource dataSource;
    	
    	@Override
    	public void saveUser(User u) {
    		
    		String sql="insert into user values(null,?,?)";
    		getJdbcTemplate().update(sql,u.getU_username(),u.getU_password());
    	}
    
    	@Override
    	public void deleteUserById(Integer id) {
    		String sql="delete from user where id=?";
    		getJdbcTemplate().update(sql,id);
    	}
    
    	@Override
    	public void updateUser(User u) {
    		String sql="update user set username=?,password=? where id=?";
    		getJdbcTemplate().update(sql,u.getU_username(),u.getU_password(),u.getU_id());
    		
    	}
    
    	@Override
    	public User selectUserById(Integer id) {
    		
    		String sql="select * from user where id=?";
    		User user= getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){
    
    			@Override
    			public User mapRow(ResultSet rs, int index) throws SQLException {
    				User u=new User();
    				
    				u.setU_id(rs.getInt("id"));
    				u.setU_username(rs.getString("username"));
    				u.setU_password(rs.getString("password"));
    				return u;
    			}
    			
    		},id);
    		
    		return user;
    	}
    
    	@Override
    	public List<User> selectAllUser() {
    		String sql="select * from user";
    		List<User> list=getJdbcTemplate().query(sql, new RowMapper<User>(){
    
    			@Override
    			public User mapRow(ResultSet rs, int index) throws SQLException {
    				User u=new User();
    				
    				u.setU_id(rs.getInt("id"));
    				u.setU_username(rs.getString("username"));
    				u.setU_password(rs.getString("password"));
    				return u;
    			}
    			
    		});
    		return list;
    	}
    
    	@Override
    	public Integer selectUserCount() {
    		String sql="select count(*) from user";
    		return getJdbcTemplate().queryForObject(sql, Integer.class);
    	}
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3][]

applicationContext2.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:context="http://www.springframework.org/schema/context"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
    		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
    	
    	<context:property-placeholder location="db.properties"/>
    	
    
    	<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    		<property name="driverClass" value="${jdbc.driverClass}"></property>
    		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
    		<property name="user" value="${jdbc.user}"></property>
    		 
    		<property name="password" value="${jdbc.password}"></property>
    	</bean>
    
    	<bean name="userDao" class="com.sikiedu.dao.UserDaolmpl">
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    
    </beans>

db.properties（这是一个file文件）

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

测试代码：

JdbcTest.java:

package com.sikiedu.test;
    
    import java.util.List;
    
    import javax.annotation.Resource;
    
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.test.context.ContextConfiguration;
    import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
    
    import com.sikiedu.bean.User;
    import com.sikiedu.dao.UserDao;
    import com.sikiedu.dao.UserDaolmpl;
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration("classpath:applicationContext2.xml")
    public class JdbcTest {
    	
    	@Resource(name="userDao")
    	private UserDao ud;
    
    
    	@Test
    	public void Test1(){
    		User u=ud.selectUserById(5);
    		System.out.println(u);
    	}
    	
    	@Test
    	public void Test2(){
    		User u=new User();
    		u.setU_username("ased");
    		u.setU_password("231");
    		ud.saveUser(u);
    	}
    
    	@Test
    	public void Test3(){
    		ud.deleteUserById(12);
    	}
    
    	@Test
    	public void Test4(){
    		User u=new User();
    		u.setU_username("王五五w");
    		u.setU_password("5555w");
    		u.setU_id(5);
    		ud.updateUser(u);
    	}
    
    	@Test
    	public void Test5(){
    		 User u = ud.selectUserById(5);
    		 System.out.println(u);
    	}
    
    	@Test
    	public void Test6(){
    		 List<User> list = ud.selectAllUser();
    		 for(User u:list){
    			 System.out.println(u);
    		 }
    		 
    	}
    
    	@Test
    	public void Test7(){
    		System.out.println(ud.selectUserCount());
    	}
    	
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70]: /images/20210725/1a29030cee4344bf81152be3244cb532.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190426121438372.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019042612151129.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3]: /images/20210725/93d6ec28f0de4df1bd6a010b44c184a5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4]: /images/20210725/9529fc05b5ac4847b64073de83b3458f.png