Spring连接数据库程序实现（JDBC）

清疚 2023-07-10 08:41 14阅读 0赞

Spring中的JDBC连接与直接使用JDBC连接是有区别的，spring对连接做了很大封装，消除冗余代码。

1.创建数据库表结构：

CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int DEFAULT NULL,
      `sex` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.创建对应的数据库表的pojo

public class User {
        private int id;
        private String name;
        private int age;
        private String sex;
    
    
        public User() {
        }
    
        public User(int id, String name, int age, String sex) {
            this.id = id;
            this.name = name;
            this.age = age;
            this.sex = sex;
        }
    
        //慈此处省略 get/set方法
    }

3. 创建表与实体间的映射

public class UserRowMapper implements RowMapper {
    
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            User user = new User(resultSet.getInt("id"), resultSet.getString("name"),
                    resultSet.getInt("age"), resultSet.getString("sex"));
            return user;
        }
    }

4. 创建数据操作接口

public interface UserService {
    
        public void save(User user);
    
        public List<User> getUsers();
    }

5.创建数据操作接口实现类

public class UserServiceImpl implements UserService {
    
        private JdbcTemplate jdbcTemplate;
    
        //设置数据源
        public void setDataSource(DataSource dateSource) {
            this.jdbcTemplate = new JdbcTemplate(dateSource);
        }
    
        public void save(User user) {
            jdbcTemplate.update("insert into user(name, age, sex) values(?, ?, ?)",
                    new Object[]{user.getName(), user.getAge(), user.getSex()},
                    new int[]{Types.VARCHAR, Types.INTEGER, Types.VARCHAR});
    
        }
    
        @SuppressWarnings("unchecked")
        public List<User> getUsers() {
            List list = jdbcTemplate.query("select * from user", new UserRowMapper());
            return list;
        }
    }

6. 创建spring配置文件

7.测试

public class SpringJDBCTest {
    
        public static void main(String[] args) {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
            UserService userService = (UserService) context.getBean("userService");
            User user = new User();
            user.setName("张三");
            user.setAge(18);
            user.setSex("男");
            //保存一条记录
            userService.save(user);
    
            List<User> person = userService.getUsers();
            System.out.println("获取所有的user");
            for (User user1 : person) {
                System.out.println(user1.getId() + " " + user1.getName() + " " + user1.getAge() + " " + user1.getSex() );
    
            }
    
        }
    }

运用结果为：

![20200301174803126.png][]

编写以上示例需要导入jdbc相关jar包或者maven依赖：

以maven依赖为例：

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    
          
          <dependency>
              <groupId>org.apache.commons</groupId>
              <artifactId>commons-dbcp2</artifactId>
              <version>2.1.1</version>
          </dependency>

[20200301174803126.png]: https://img-blog.csdnimg.cn/20200301174803126.png