SpringJDBC ：query（）对数据库执行查询操作

今天药忘吃喽~ 2023-06-08 06:16 21阅读 0赞

初始数据库的样子：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzQxNzU3_size_16_color_FFFFFF_t_70]  
修改Account，增加两个方法：

package com.student.jdbc;
    
    import java.util.List;
    
    public interface AccountDao {
    	public int addAccount(Account account);
    	public int updateAccount(Account account);
    	public int deleteAccount(int id);
    	public Account findAccountById(int id);
    	public List<Account> findAllAccount();
    
    }

同时AccountDaoImpl:也要增加两个方法：

package com.student.jdbc;
    
    import java.util.List;
    
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    
    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;
    	}
    
    	@Override
    	public Account findAccountById(int id) {
    		String sql = "select * from account where id = ?";
    		RowMapper<Account> rowMapper =new BeanPropertyRowMapper<Account>(Account.class);
    		return this.JdbcTemplate.queryForObject(sql, rowMapper,id);
    	}
    
    	@Override
    	public List<Account> findAllAccount() {
    		String sql = "select * from account";
    		RowMapper<Account> rowMapper =new BeanPropertyRowMapper<Account>(Account.class);
    		return this.JdbcTemplate.query(sql, rowMapper);
    	}
    
    }

配置文件不修改：

<?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>

测试文件增加一个方法：

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("对不起，插入数据操作失败！！！");
    		}
    	}
    	
    	@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("对不起，修改数据操作失败！！！");
    		}
    	}
    */
    	/*
    	@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("对不起，删除数据操作失败！！！");
    		}
    	}
    	*/
    	@Test
    	public void findAccountByIdTest() {
    		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    		AccountDao accountDao  = (AccountDao) applicationContext.getBean("accountDao");
    		Account account = accountDao.findAccountById(1);
    		System.out.println(account);
    	}
    	
    }

输出结果：

十月 16, 2019 8:28:27 下午 org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
    信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@64b8f8f4: startup date [Wed Oct 16 20:28:27 CST 2019]; root of context hierarchy
    十月 16, 2019 8:28:27 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
    信息: Loading XML bean definitions from class path resource [applicationContext.xml]
    十月 16, 2019 8:28:27 下午 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
    信息: Loaded JDBC driver: com.mysql.jdbc.Driver
    Wed Oct 16 20:28:28 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.
    Account [id=1, username=joy, balance=100.0]

在测试类增加方法：

@Test
    	public void findAllAccountTest() {
    		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
    		AccountDao accountDao  = (AccountDao) applicationContext.getBean("accountDao");
    		List<Account> account = accountDao.findAllAccount();
    		for (Account act :account)
    		{
    			System.out.println(act);
    		}
    	}

运行结果：

十月 16, 2019 8:39:46 下午 org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
    信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@64b8f8f4: startup date [Wed Oct 16 20:39:46 CST 2019]; root of context hierarchy
    十月 16, 2019 8:39:46 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
    信息: Loading XML bean definitions from class path resource [applicationContext.xml]
    十月 16, 2019 8:39:46 下午 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
    信息: Loaded JDBC driver: com.mysql.jdbc.Driver
    Wed Oct 16 20:39:47 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.
    Account [id=1, username=joy, balance=100.0]
    Account [id=2, username=tom, balance=1000.0]
    Account [id=3, username=jack, balance=2000.0]
    Account [id=4, username=rose, balance=500.0]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzQxNzU3_size_16_color_FFFFFF_t_70]: /images/20230531/49cb70a2c4d541ec9541846fe437892c.png