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配置文件
<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-2.5.xsd ">
<!--配置数据源-->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring-DB"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!--配置业务bean-->
<bean id="userService" class="com.demo.cn.spring.jdbc.UserServiceImpl">
<!--向属性dataSource注入数据源-->
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
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() );
}
}
}
运用结果为:
编写以上示例需要导入jdbc相关jar包或者maven依赖:
以maven依赖为例:
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>