Spring JDBC数据库开发
分类:
IT文章
•
2023-11-23 15:28:13
针对数据库操作，Spring框架提供了JdbcTemplate类。
1.Spring JDBC的配置
创建配置文件applicationContext.xml，添加如下代码：
1 <!--配置数据源-->
2 <bean
3 class="org.springframework.jdbc.datasource.DriverManagerDataSource">
4 <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
5 <property name="url" value="jdbc:mysql://localhost:3306/springjdbc"/>
6 <property name="username" value="root"/>
7 <property name="password" value="root"/>
8 </bean>
9 <!--配置JDBC模板-->
10 <bean >
11 <property name="dataSource" ref="dataSource"/>
12 </bean>
13 <!--配置注入类-->
14 <bean >
15 <property name="jdbcTemplate" ref="jdbcTemplate"/>
16 </bean>
2.创建Javabean和Dao接口
![]()
1 public class User {
2 private Integer id;
3 private String name;
4 private Integer age;
5
6 public Integer getId() { return id; }
7
8 public void setId(Integer id) {
9 this.id = id;
10 }
11
12 public String getName() {
13 return name;
14 }
15
16 public void setName(String password) {
17 this.name = password;
18 }
19
20 public Integer getAge() { return age; }
21
22 public void setAge(Integer age) {
23 this.age = age;
24 }
25 }
javabean
![]()
public interface UserDao {
public void execute();
public int addUser(User user);
public int updateUser(User user);
public int deleteUser(int id);
public void query();
}
UserDao
3.Spring JdbcTemplate的常用方法
2.1 execute(String sql)执行SQL语句
2.2 update()插入、更新、删除表数据操作
2.3 query()查询操作
上述的三个数据库操作方法都放在UserDaoImpl实现类中调用，具体代码如下：
1 import com.wxy.javabean.User;
2 import org.springframework.dao.DataAccessException;
3 import org.springframework.jdbc.core.JdbcTemplate;
4 import org.springframework.jdbc.core.ResultSetExtractor;
5
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.util.ArrayList;
9 import java.util.List;
10
11 public class UserDaoImpl implements UserDao {
12 //获取JdbcTemplate实例
13 private JdbcTemplate jdbcTemplate;
14 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
15 this.jdbcTemplate = jdbcTemplate;
16 }
17 //执行SQL语句的方法
18 @Override
19 public void execute() {
20 jdbcTemplate.execute("create table account( id int primary key auto_increment,"
21 + "username varchar(50)," + "balance double)");
22 }
23 //添加用户
24 @Override
25 public int addUser(User user) {
26 String sql = "insert user (id,name,age) value(?,?,?)";
27 //定义数组来存储SQL语句中的参数
28 Object[] obj = new Object[]{
29 user.getId(),
30 user.getName(),
31 user.getAge()
32 };
33 int num = this.jdbcTemplate.update(sql,obj);
34 return num;
35 }
36 //更新用户信息
37 public int updateUser(User user){
38 String sql = "update user set name=?,age=? where id=?";
39 Object[] params = new Object[]{
40 user.getName(),
41 user.getAge(),
42 user.getId()
43 };
44 int num = this.jdbcTemplate.update(sql,params);
45 return num;
46 }
47 //删除用户
48 @Override
49 public int deleteUser(int id) {
50 String sql = "delete from user where id =?";
51 int num = jdbcTemplate.update(sql,id);
52 return num;
53 }
54 //查询所有用户
55 @Override
56 public void query() {
57 String listSql = "select * from user";
58 List<User> list = jdbcTemplate.query(listSql, new ResultSetExtractor<List>() {
59 public List<User> extractData(ResultSet rs) throws SQLException,
60 DataAccessException {
61 List<User> result = new ArrayList<User>();
62 while (rs.next()) {
63 User user = new User();
64 user.setId(rs.getInt("id"));
65 user.setName(rs.getString("name"));
66 user.setAge(rs.getInt("age"));
67 result.add(user);
68 }
69 return result;
70 }
71 });
72
73 for (User user : list) {
74 System.out.print(">);
75 System.out.print("name=" + user.getName() + " ");
76 System.out.println("age=" + user.getAge() + " ");
77 }
78 }
79 }
4.编写测试类Test
1 import com.wxy.javabean.User;
2 import com.wxy.model.UserDaoImpl;
3 import org.junit.Test;
4 import org.springframework.context.ApplicationContext;
5 import org.springframework.context.support.ClassPathXmlApplicationContext;
6
7 public class JdbcTemplateTest {
8 @Test
9 public void executeTest(){
10 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
11 UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
12 userDao.execute();
13 System.out.println("创建成功！");
14 }
15 @Test
16 public void addTest(){
17 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
18 UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
19 User user = new User();
20 user.setId(3);
21 user.setName("Tom");
22 user.setAge(21);
23 int num = userDao.addUser(user);
24 if(num>0){
25 System.out.println("成功添加"+num+"个用户");
26 }else {
27 System.out.println("添加用户操作失败！");
28 }
29 }
30 @Test
31 public void updateTest(){
32 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
33 UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
34 User user = new User();
35 user.setId(2);
36 user.setName("Tim");
37 user.setAge(26);
38 int num = userDao.addUser(user);
39 if(num>0){
40 System.out.println("成功修改"+num+"个用户");
41 }else {
42 System.out.println("修改用户信息失败！");
43 }
44 }
45 @Test
46 public void deleteTest(){
47 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
48 UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
49 int num = userDao.deleteUser(2);
50 if(num>0){
51 System.out.println("成功删除"+num+"个用户");
52 }else {
53 System.out.println("删除用户操作失败！");
54 }
55 }
56 @Test
57 public void query(){
58 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
59 UserDaoImpl userDao = (UserDaoImpl) applicationContext.getBean("userDao");
60 userDao.query();
61 }
62 }
