Spring JDBC update方法：添加，删除，更新

迷南。 2023-06-08 06:13 6阅读 0赞

Account

package com.student.jdbc;
    
    public class Account {
    	private Integer id;
    	private String username;
    	private Double balance;
    	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 Double getBalance() {
    		return balance;
    	}
    	public void setBalance(Double balance) {
    		this.balance = balance;
    	}
    	@Override
    	public String toString() {
    		return "Account [id=" + id + ", username=" + username + ", balance=" + balance + "]";
    	}
    	
    
    }

AccountDao:接口

package com.student.jdbc;
    
    public interface AccountDao {
    	public int addAccount(Account account);
    	public int updateAccount(Account account);
    	public int deleteAccount(int id);
    
    }

AccountDaoImpl

package com.student.jdbc;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    
    public class AccountDaoImpl implements AccountDao {
    	private JdbcTemplate JdbcTemplate;
    	
    	public JdbcTemplate getJdbcTemplate() {
    		return JdbcTemplate;
    	}
    
    	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    		JdbcTemplate = jdbcTemplate;
    	}
    
    	@Override
    	public int addAccount(Account account) {
    		String sql = "insert into account(username ,balance) value(?,?)";
    		Object[] obj = new Object[] {
    			account.getUsername(),
    			account.getBalance()
    		};
    		int num = this.JdbcTemplate.update(sql, obj);
    		return num;
    	}
    
    	@Override
    	public int updateAccount(Account account) {
    		String sql = "update account set username=?,balance=? where id=?";
    		Object[] params = new Object[] {
    				account.getUsername(),
    				account.getBalance(),
    				account.getId()
    				
    		};
    		int num = this.JdbcTemplate.update(sql, params);
    		return num;
    	}
    
    	@Override
    	public int deleteAccount(int id) {
    		String sql = "delete from account where id = ?";
    		int num = this.JdbcTemplate.update(sql,id);
    		return num;
    	}
    
    }

applicationContext.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"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd">
    	
    	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    		<property name="url" value="jdbc:mysql://localhost/spring"></property>
    		<property name="username" value="root"></property>
    		<property name="password" value="123456"></property>
    	</bean>
    	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    	<bean id ="accountDao" class="com.student.jdbc.AccountDaoImpl">
    		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
    	</bean>
    </beans>

JdbcTemplateTest:测试类

package com.student.jdbc;
    
    import org.junit.Test;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    public class JdbcTemplateTest {
    
    	public static void main(String[] args) {
    		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    		JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
    		jdbcTemplate.execute("create table account(" + "id int primary key auto_increment," + "username varchar(50),"+ "balance double)");
    		System.out.println("账户表创建成功！！！！！");
    
    	}
    	
    	@Test
    	public void addAccountTest() {
    		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    		AccountDao accountDao  = (AccountDao) applicationContext.getBean("accountDao");
    		Account account = new Account();
    		account.setUsername("tom");
    		account.setBalance(1000.00);
    		int num = accountDao.addAccount(account);
    		if(num>0)
    		{
    			System.out.println("成功插入了"+ num +"条数据！！！");
    		}
    		else
    		{
    			System.out.println("对不起，插入数据操作失败！！！");
    		}
    	}
    
    }

输出结果：

十月 16, 2019 7:42:13 下午 org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
    信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@64b8f8f4: startup date [Wed Oct 16 19:42:13 CST 2019]; root of context hierarchy
    十月 16, 2019 7:42:13 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
    信息: Loading XML bean definitions from class path resource [applicationContext.xml]
    十月 16, 2019 7:42:13 下午 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
    信息: Loaded JDBC driver: com.mysql.jdbc.Driver
    Wed Oct 16 19:42:14 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
    成功插入了1条数据！！！

MySQL数据库检测：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzQxNzU3_size_16_color_FFFFFF_t_70]  
确实插入了这个数据。

给测试类增加方法：

@Test
    	public void updateAccountTest() {
    		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    		AccountDao accountDao  = (AccountDao) applicationContext.getBean("accountDao");
    		Account account = new Account();
    		account.setId(1);
    		account.setUsername("tom");
    		account.setBalance(2000.00);
    		int num = accountDao.updateAccount(account);
    		if(num>0)
    		{
    			System.out.println("成功修改了"+ num +"条数据！！！");
    		}
    		else
    		{
    			System.out.println("对不起，修改数据操作失败！！！");
    		}
    	}

输出结果：

十月 16, 2019 7:56:08 下午 org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
    信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1996cd68: startup date [Wed Oct 16 19:56:08 CST 2019]; root of context hierarchy
    十月 16, 2019 7:56:08 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
    信息: Loading XML bean definitions from class path resource [applicationContext.xml]
    十月 16, 2019 7:56:09 下午 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
    信息: Loaded JDBC driver: com.mysql.jdbc.Driver
    Wed Oct 16 19:56:09 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
    成功修改了1条数据！！！

数据库刷新后的结果：  
![在这里插入图片描述][20191016200517670.png]  
说明的确数据被修改了。  
注意：运行这个代码的时候必须注释掉前面的两个测试，不然会影响测试，还会插入一条数据。

给测试类增加下面的代码：

@Test
    	public void deleteAccountTest() {
    		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    		AccountDao accountDao  = (AccountDao) applicationContext.getBean("accountDao");
    		int num = accountDao.deleteAccount(1);
    		if(num>0)
    		{
    			System.out.println("成功删除了"+ num +"条数据！！！");
    		}
    		else
    		{
    			System.out.println("对不起，删除数据操作失败！！！");
    		}
    	}

运行结果：

十月 16, 2019 8:08:47 下午 org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
    信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@64b8f8f4: startup date [Wed Oct 16 20:08:47 CST 2019]; root of context hierarchy
    十月 16, 2019 8:08:47 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
    信息: Loading XML bean definitions from class path resource [applicationContext.xml]
    十月 16, 2019 8:08:47 下午 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
    信息: Loaded JDBC driver: com.mysql.jdbc.Driver
    Wed Oct 16 20:08:48 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
    成功删除了1条数据！！！

数据库刷新后的结果：  
![在这里插入图片描述][2019101620104278.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzQxNzU3_size_16_color_FFFFFF_t_70]: /images/20230531/bab99490623645b0ac2b66fbf672b4e1.png
[20191016200517670.png]: /images/20230531/458cf827312048d384f22e61606ccbfc.png
[2019101620104278.png]: /images/20230531/7e88136dcc3c4b3ea45080afeecdd1ec.png