Spring的数据库编程 |
您所在的位置：网站首页 › 数据库schema怎么理解 › Spring的数据库编程 |
Spring的数据库编程
|
1.JDBCTemplate 针对数据库操作，Spring框架提供了JdbcTemplate类,JdbcTemplate是一个模板类，Spring JDBC中的更高层次的抽象类均在JdbcTemplate模板类的基础上创建。JdbcTemplate类提供了操作数据库的基本方法，包括添加、删除、查询和更新。在操作数据库时，JdbcTemplate类简化了传统JDBC中的复杂步骤，这可以让开发人员将更多精力投入到业务逻辑中。 JdbcTemplate类继承自抽象类JdbcAccessor，同时实现了JdbcOperations接口。抽象类JdbcAccessor提供了一些访问数据库时使用的公共属性，具体如下: DataSource: DataSource主要功能是获取数据库连接。在具体的数据操作中，它还提供对数据库连接的缓冲池和分布式事务的支持。SQLExceptionTranslator: SQLExceptionTranslator是一个接口，它负责对SQLException异常进行转译工作。2.Spring JDBC的配置 Spring JDBC中的4个包说明
Spring对数据库的操作都封装在core、dataSource、object和support这4个包，使用Spring JDBC,就要对这些包进行配置。在Spring中，JDBC的配置是在配置文件applicationContext.xml中完成的。 3.配置数据源: 包括数据库驱动、连接数据库url、连接数据库用户名、连接数据库密码。 下面看具体操作： 第一步引入依赖： 4.0.0 org.example _20230319 1.0-SNAPSHOT 8 8 UTF-8 org.springframework spring-context 5.2.3.RELEASE org.springframework spring-beans 5.2.3.RELEASE mysql mysql-connector-java 5.1.47 org.springframework spring-jdbc 5.3.6UserDao操作数据库 package cn.hdc; import org.springframework.jdbc.core.JdbcTemplate; public class UserDaoImpl { private JdbcTemplate jdbcTemplate; public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } }applicationContext.xml配置信息： 测试类： import cn.hdc.UserDaoImpl; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class APP { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); UserDaoImpl userDao = (UserDaoImpl) context.getBean("userDao"); System.out.println(userDao.getJdbcTemplate()); } }运行结果：
注意： dataSource配置4个属性的含义
在dataSource的4个属性中，需要根据数据库类型或者系统配置设置相应的属性值。例如，如果数据库类型不同，需要更改驱动名称;如果数据库不在本地，则需要将地址中的localhost替换成相应的主机IP;默认情况下，数据库端口号可以省略，但如果修改过MySQL数据库的端口号，则需要加上修改后的端口号。此外，连接数据库的用户名和密码需要与数据库创建时设置的用户名和密码保持一致。 4.JDBCTemplate的常用方法 熟悉excute()方法，能够在程序中使用excute()方法 代码示例： 4.0.0 org.example _20230319 1.0-SNAPSHOT 8 8 UTF-8 org.springframework spring-context 5.2.3.RELEASE org.springframework spring-beans 5.2.3.RELEASE mysql mysql-connector-java 5.1.47 org.springframework spring-jdbc 5.3.6 org.springframework spring-tx 5.3.6 package cn.hdc; import org.springframework.jdbc.core.JdbcTemplate; public class UserDaoImpl { private JdbcTemplate jdbcTemplate; public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } } import cn.hdc.UserDaoImpl; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; public class APP { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate"); jdbcTemplate.execute("create table account" + "(" + " id int primary key auto_increment," + " username varchar(50)," + " balance double" + ");"); System.out.println("account表创建成功！"); // UserDaoImpl userDao = (UserDaoImpl) context.getBean("userDao"); // System.out.println(userDao.getJdbcTemplate()); } }运行结果：
成功创建了account表！ update方法代码示例： JdbcTemplate类中常用的update()方法
代码示例： pom.xml 4.0.0 org.example _20230319 1.0-SNAPSHOT 8 8 UTF-8 org.springframework spring-context 5.2.3.RELEASE org.springframework spring-beans 5.2.3.RELEASE mysql mysql-connector-java 5.1.47 org.springframework spring-jdbc 5.3.6 org.springframework spring-tx 5.3.6AccountDao接口： package cn.hdc.dao; import cn.hdc.model.Account; public interface AccountDao { public Integer addAccount(Account account); public Integer deleteAccount(Integer id); public Integer updateAccount(Account account); }AccountDaoImpl类： package cn.hdc.dao.impl; import cn.hdc.dao.AccountDao; import cn.hdc.model.Account; import org.springframework.jdbc.core.JdbcTemplate; public class AccountDaoImpl implements AccountDao { private JdbcTemplate jdbcTemplate; public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public Integer addAccount(Account account) { String sql = "insert into account(username,balance) values(?,?)"; Object[] params = new Object[]{account.getUsername(), account.getBalance()}; jdbcTemplate.update(sql, params); return jdbcTemplate.update(sql, params); } @Override public Integer deleteAccount(Integer id) { return 0; } @Override public Integer updateAccount(Account account) { return 0; } }Account类： package cn.hdc.model; public class Account { private Integer id; private String username; private Double balance; @Override public String toString() { return "Account{" + "id=" + id + ", username='" + username + '\'' + ", balance=" + 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; } }applicationContext.xml 测试类： import cn.hdc.dao.AccountDao; import cn.hdc.dao.impl.AccountDaoImpl; import cn.hdc.model.Account; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; public class APP { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate"); AccountDao accountDao = (AccountDao) context.getBean("accountDao"); Account account = new Account(); account.setUsername("tom"); account.setBalance(1000.011); Integer ret = accountDao.addAccount(account); if (ret > 0) { System.out.println("插入成功！"); } else { System.out.println("插入失败！"); } // jdbcTemplate.execute("create table account" + // "(" + // " id int primary key auto_increment," + // " username varchar(50)," + // " balance double" + // ");"); // System.out.println("account表创建成功！"); // UserDaoImpl userDao = (UserDaoImpl) context.getBean("userDao"); // System.out.println(userDao.getJdbcTemplate()); } }运行结果：
增加，删除，修改的实现代码： pom.xml 4.0.0 org.example _20230319 1.0-SNAPSHOT 8 8 UTF-8 org.springframework spring-context 5.2.3.RELEASE org.springframework spring-beans 5.2.3.RELEASE mysql mysql-connector-java 5.1.47 org.springframework spring-jdbc 5.3.6 org.springframework spring-tx 5.3.6AccountDao接口： package cn.hdc.dao; import cn.hdc.model.Account; public interface AccountDao { public Integer addAccount(Account account); public Integer deleteAccount(Integer id); public Integer updateAccount(Account account); }AccountDaoImpl类： package cn.hdc.dao.impl; import cn.hdc.dao.AccountDao; import cn.hdc.model.Account; import org.springframework.jdbc.core.JdbcTemplate; public class AccountDaoImpl implements AccountDao { private JdbcTemplate jdbcTemplate; public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public Integer addAccount(Account account) { String sql = "insert into account(username,balance) values(?,?)"; Object[] params = new Object[]{account.getUsername(), account.getBalance()}; jdbcTemplate.update(sql, params); return jdbcTemplate.update(sql, params); } @Override public Integer deleteAccount(Integer id) { String sql = "delete from account where id = ?"; return jdbcTemplate.update(sql, id); } @Override public Integer updateAccount(Account account) { String sql = "update account set username=?,balance=? where id=?"; Object[] params = new Object[]{account.getUsername(), account.getBalance(), account.getId()}; return jdbcTemplate.update(sql, params); } }Account实体类： package cn.hdc.model; public class Account { private Integer id; private String username; private Double balance; @Override public String toString() { return "Account{" + "id=" + id + ", username='" + username + '\'' + ", balance=" + 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; } }applicationContext.xml 测试类： import cn.hdc.dao.AccountDao; import cn.hdc.dao.impl.AccountDaoImpl; import cn.hdc.model.Account; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; public class APP { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate"); AccountDao accountDao = (AccountDao) context.getBean("accountDao"); Integer ret = accountDao.deleteAccount(2); if (ret > 0) { System.out.println("删除成功！"); } else { System.out.println("删除失败！"); } // Account account1 = new Account(); // account1.setId(2); // account1.setUsername("zhangsan"); // account1.setBalance(5000.02153); // Integer ret = accountDao.updateAccount(account1); // if (ret > 0) { // System.out.println("修改成功！"); // } else { // System.out.println("修改失败！"); // } // Account account = new Account(); // account.setUsername("tom"); // account.setBalance(1000.011); // Integer ret = accountDao.addAccount(account); // if (ret > 0) { // System.out.println("插入成功！"); // } else { // System.out.println("插入失败！"); // } // jdbcTemplate.execute("create table account" + // "(" + // " id int primary key auto_increment," + // " username varchar(50)," + // " balance double" + // ");"); // System.out.println("account表创建成功！"); // UserDaoImpl userDao = (UserDaoImpl) context.getBean("userDao"); // System.out.println(userDao.getJdbcTemplate()); } }通过刚才的例子，我们已经了解到JdbcTemplate类中的增，删，改操作，接下来，我们在看看JdbcTemplate类中的查操作。 JdbcTemplate类中常用的查询方法
代码示例： Account实体类： package cn.hdc.model; public class Account { private Integer id; private String username; private Double balance; @Override public String toString() { return "Account{" + "id=" + id + ", username='" + username + '\'' + ", balance=" + 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; } }AccountDao接口： package cn.hdc.dao; import cn.hdc.model.Account; import java.util.List; public interface AccountDao { public Integer addAccount(Account account); public Integer deleteAccount(Integer id); public Integer updateAccount(Account account); public Account findAccountById(Integer id); public List findAll(); }AccountDaoImpl类： package cn.hdc.dao.impl; import cn.hdc.dao.AccountDao; import cn.hdc.model.Account; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import java.util.List; public class AccountDaoImpl implements AccountDao { private JdbcTemplate jdbcTemplate; public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public Integer addAccount(Account account) { String sql = "insert into account(username,balance) values(?,?)"; Object[] params = new Object[]{account.getUsername(), account.getBalance()}; jdbcTemplate.update(sql, params); return jdbcTemplate.update(sql, params); } @Override public Integer deleteAccount(Integer id) { String sql = "delete from account where id = ?"; return jdbcTemplate.update(sql, id); } @Override public Integer updateAccount(Account account) { String sql = "update account set username=?,balance=? where id=?"; Object[] params = new Object[]{account.getUsername(), account.getBalance(), account.getId()}; return jdbcTemplate.update(sql, params); } @Override public Account findAccountById(Integer id) { String sql = "select * from account where id = ?"; RowMapper accountRowMapper = new BeanPropertyRowMapper(Account.class); Account account = jdbcTemplate.queryForObject(sql, accountRowMapper, id); return jdbcTemplate.queryForObject(sql, accountRowMapper, id); } @Override public List findAll() { String sql = "select * from account"; RowMapper accountRowMapper = new BeanPropertyRowMapper(Account.class); List list = jdbcTemplate.query(sql, accountRowMapper); return list; } }测试类： import cn.hdc.dao.AccountDao; import cn.hdc.dao.impl.AccountDaoImpl; import cn.hdc.model.Account; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; import java.util.List; public class APP { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate"); AccountDao accountDao = (AccountDao) context.getBean("accountDao"); List list = accountDao.findAll(); list.forEach(account -> { System.out.println(account); }); // Account account = accountDao.findAccountById(1); // System.out.println(account); // Integer ret = accountDao.deleteAccount(2); // if (ret > 0) { // System.out.println("删除成功！"); // } else { // System.out.println("删除失败！"); // } // Account account1 = new Account(); // account1.setId(2); // account1.setUsername("zhangsan"); // account1.setBalance(5000.02153); // Integer ret = accountDao.updateAccount(account1); // if (ret > 0) { // System.out.println("修改成功！"); // } else { // System.out.println("修改失败！"); // } // Account account = new Account(); // account.setUsername("tom"); // account.setBalance(1000.011); // Integer ret = accountDao.addAccount(account); // if (ret > 0) { // System.out.println("插入成功！"); // } else { // System.out.println("插入失败！"); // } // jdbcTemplate.execute("create table account" + // "(" + // " id int primary key auto_increment," + // " username varchar(50)," + // " balance double" + // ");"); // System.out.println("account表创建成功！"); // UserDaoImpl userDao = (UserDaoImpl) context.getBean("userDao"); // System.out.println(userDao.getJdbcTemplate()); } }applicatioContext.xml |
【本文地址】
今日新闻 |
推荐新闻 |