SpringTemplate 实现 MySQL 增删改查（控制台）

Spring JdbcTemplate的常用方法

方法描述execute()execute(String sq) 方法可用于执行sql语句update()用于执行插入、更新和删除操作query()用于执行数据查询操作

update()

方法描述int update(String sql)该方法是最简单的update方法重载形式，它直接执行传入的SQL语句，并返回受影响的行数。int update(PreparedStatementCreatorpsc)该方法执行从PreparedStatementCreator 返回的语句，然后返回受影响的行数。int update(String sql, PreparedStatementSetter pss))该方法通过PreparedStatementSetter 设置SQL语句中的参数，并返回受影响的行数。int update(String sql,Object… args)该方法使用Object… .设置SQL语句中的参数，要求参数不能为NULL,并返回受影响的行数。

query()

方法描述List query(String sql, RowMapper rowMapper)执行String类型参数提供的SQL语句,并通过RowMapper返回一个List类型的结果。List query (String sql,PreparedStatementSetter pss,RowMapper rowMapper)根据String类型参数提供的SQL语句创建PreparedStatement对象，通过RowMapper将结果返回到List中。List query (String sql,Object[] args,RowMapper rowMapper)使用Object[]的值来设置SQL语句中的参数值，采用RowMapper回调方法可以直接返回List类型的数据。queryForObject(String sql,RowMapper rowMapper,Object… args)将args 参数绑定到SQL语句中，并通过RowMapper返回一个Object类型的单行记录。queryForList ( String sql,Object[] args,class< T> elementType)该方法可以返回多行数据的结果，但必须是返回列表，elementType 参数返回的是List元素类型。

项目目录

pom.xml

<?xml version="1.0" encoding="UTF-8"?> <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>ssmchapter04</groupId> <artifactId>ssmchapter04</artifactId> <version>1.0-SNAPSHOT</version> <packaging>war</packaging> <name>ssmchapter04 Maven Webapp</name> <!-- FIXME change it to the project's website --> <url>http://www.example.com</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.7</maven.compiler.source> <maven.compiler.target>1.7</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>4.3.6.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-context --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.3.6.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-core --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>4.3.6.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-expression --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-expression</artifactId> <version>4.3.6.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging --> <dependency> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> <version>1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.3.6.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter-api</artifactId> <version>5.3.2</version> <scope>compile</scope> </dependency> </dependencies> <build> <finalName>ssmchapter04</finalName> <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) --> <plugins> <plugin> <artifactId>maven-clean-plugin</artifactId> <version>3.1.0</version> </plugin> <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging --> <plugin> <artifactId>maven-resources-plugin</artifactId> <version>3.0.2</version> </plugin> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.8.0</version> </plugin> <plugin> <artifactId>maven-surefire-plugin</artifactId> <version>2.22.1</version> </plugin> <plugin> <artifactId>maven-war-plugin</artifactId> <version>3.2.2</version> </plugin> <plugin> <artifactId>maven-install-plugin</artifactId> <version>2.5.2</version> </plugin> <plugin> <artifactId>maven-deploy-plugin</artifactId> <version>2.8.2</version> </plugin> </plugins> </pluginManagement> </build> </project>

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-4.3.xsd"> <!-- 1配置数据源 --> <bean id="dataSource" class= "org.springframework.jdbc.datasource.DriverManagerDataSource"> <!--数据库驱动 --> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <!--连接数据库的url --> <property name="url" value="jdbc:mysql://localhost:3306/spring?serverTimezone=GMT" /> <!--<property name="url" value="jdbc:mysql://localhost:3306/spring" />--> <!--//jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT&useSSL=false--> <!--连接数据库的用户名 --> <property name="username" value="root" /> <!--连接数据库的密码 --> <property name="password" value="admin" /> </bean> <!-- 2配置JDBC模板 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!-- 默认必须使用数据源 --> <property name="dataSource" ref="dataSource" /> </bean> <!--定义id为accountDao的Bean--> <bean id="accountDao" class="com.ssm.jdbc.AccountDaoImpl"> <!-- 将jdbcTemplate注入到accountDao实例中 --> <property name="jdbcTemplate" ref="jdbcTemplate" /> </bean> </beans>

Account.java

private Integer id; // 账户id private String username; // 用户名 private Double balance; // 账户余额

AccountDao.java

public int addAccount(Account account); // 更新 public int updateAccount(Account account); // 删除 public int deleteAccount(int id); // 通过id查询 public Account findAccountById(int id); // 查询所有账户 public List<Account> findAllAccount();

AccountDaoImpl.java

public class AccountDaoImpl implements AccountDao { // 声明JdbcTemplate属性及其setter方法 private JdbcTemplate jdbcTemplate; public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } // 添加账户 public int addAccount(Account account) { // 定义SQL String sql = "insert into account(username,balance) value(?,?)"; // 定义数组来存放SQL语句中的参数 Object[] obj = new Object[]{ account.getUsername(), account.getBalance() }; // 执行添加操作，返回的是受SQL语句影响的记录条数 int num = this.jdbcTemplate.update(sql, obj); return num; } // 更新账户 public int updateAccount(Account account) { // 定义SQL String sql = "update account set username=?,balance=? where id = ?"; // 定义数组来存放SQL语句中的参数 Object[] params = new Object[]{ account.getUsername(), account.getBalance(), account.getId() }; // 执行添加操作，返回的是受SQL语句影响的记录条数 int num = this.jdbcTemplate.update(sql, params); return num; } // 删除账户 public int deleteAccount(int id) { // 定义SQL String sql = "delete from account where id = ? "; // 执行添加操作，返回的是受SQL语句影响的记录条数 int num = this.jdbcTemplate.update(sql, id); return num; } // 通过id查询账户数据信息 public Account findAccountById(int id) { //定义SQL语句 String sql = "select * from account where id = ?"; // 创建一个新的BeanPropertyRowMapper对象 RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class); // 将id绑定到SQL语句中，并通过RowMapper返回一个Object类型的单行记录 return this.jdbcTemplate.queryForObject(sql, rowMapper, id); } // 查询所有账户信息 public List<Account> findAllAccount() { // 定义SQL语句 String sql = "select * from account"; // 创建一个新的BeanPropertyRowMapper对象 RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class); // 执行静态的SQL查询，并通过RowMapper返回结果 return this.jdbcTemplate.query(sql, rowMapper); } }

JdbcTemplateTest.java

public class JdbcTemplateTest { /** * 使用execute()方法建表 */ // public static void main(String[] args) { // // 加载配置文件 // ApplicationContext applicationContext = // new ClassPathXmlApplicationContext("applicationContext.xml"); // // 获取JdbcTemplate实例 // JdbcTemplate jdTemplate = // (JdbcTemplate) applicationContext.getBean("jdbcTemplate"); // // 使用execute()方法执行SQL语句，创建用户账户管理表account // jdTemplate.execute("create table account(" + // "id int primary key auto_increment," + // "username varchar(50)," + // "balance double)"); // System.out.println("账户表account创建成功！"); // } @Test public void mainTest() { // 加载配置文件 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); // 获取JdbcTemplate实例 JdbcTemplate jdTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate"); // 使用execute()方法执行SQL语句，创建用户账户管理表account jdTemplate.execute("create table account(" + "id int primary key auto_increment," + "username varchar(50)," + "balance double)"); System.out.println("账户表account创建成功！"); } @Test public void addAccountTest() { // 加载配置文件 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); // 获取AccountDao实例 AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao"); // 创建Account对象，并向Account对象中添加数据 Account account = new Account(); account.setId(19); account.setUsername("tom"); account.setBalance(1000.00); // 执行addAccount()方法，并获取返回结果 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 = (AccountDao) applicationContext.getBean("accountDao"); // 创建Account对象，并向Account对象中添加数据 Account account = new Account(); account.setId(1); account.setUsername("tom"); account.setBalance(2000.00); // 执行updateAccount()方法，并获取返回结果 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 = (AccountDao) applicationContext.getBean("accountDao"); // 执行deleteAccount()方法，并获取返回结果 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 = (AccountDao) applicationContext.getBean("accountDao"); // 执行findAccountById()方法 Account account = accountDao.findAccountById(2); System.out.println(account); } @Test public void findAllAccountTest() { // 加载配置文件 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); // 获取AccountDao实例 AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao"); // 执行findAllAccount()方法,获取Account对象的集合 List<Account> account = accountDao.findAllAccount(); // 循环输出集合中的对象 for (Account act : account) { System.out.println(act); } } }

整理自 “我要自学网” SSM 教程

---