一、传统JDBC
1、传统JDBC步骤
- 加载驱动
- 获取connection连接
- 创建statement执行sql
- 返回resultSet查询结果
- 关闭资源
2、例子:添加用户和查询用户
- 引进msyql和数据源的jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.25</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.29</version>
</dependency>
- 在数据库添加user用户表
CREATE TABLE `user` (
`id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '自增id' ,
`userName` varchar(16) NULL DEFAULT '' COMMENT '用户名' ,
PRIMARY KEY (`id`)
);
- 创建实体类User,并添加toString()方法
public class User {
private Long id;
private String userName;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
'}';
}
}
- 创建userDao类,类中有saveUser()方法和getUser()方法
public class UserDao {
//保存用户
public void saveUser(String userName) throws Exception{
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
//创建statement执行sql
Statement statement = connection.createStatement();
//创建sql
String sql = "insert into user(userName) values('"+userName+"')";
//执行sql
statement.execute(sql);
//关闭资源
statement.close();
connection.close();
}
//获取用户
public User getUser(long id)throws Exception{
User user = new User();
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
//创建statement执行sql
Statement statement = connection.createStatement();
//创建sql
String sql = "select * from user where id="+id;
//执行sql
ResultSet rt = statement.executeQuery(sql);
while (rt.next()){
user.setId(rt.getLong("id"));
user.setUserName(rt.getString("userName"));
}
//关闭资源
statement.close();
connection.close();
return user;
}
}
- 编写测试类
public class Test {
@org.junit.Test
public void test() throws Exception{
UserDao userDao = new UserDao();
userDao.saveUser("chensr");
User user = userDao.getUser(1l);
System.out.println(user.toString());
}
}
运行结果:数据库中多了一条username为“chensr”的数据,并且控制台打印
User{id=1, userName='chensr'}
注:传统的JDBC每次与数据库交互的时候,都要获取数据库连接、关闭资源,使代码变得冗余。
二、Spring的JDBC模板
- spring的JDBC框架承担了资源管理和异常处理的工作,从而优化了JDBC代码,让我们只需编写从数据库读写数据必需的代码。对此Spring提供了JdbcDapSupport模板,通过getJdbcTemplate()对数据进行增删改查
1、例子:添加用户和查询用户
- 引入spring-mysql相关的jar包
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
- 创建userDao,实现JdbcDaoSupport
public class UserDao extends JdbcDaoSupport {
//保存用户
public void saveUser(String userName) throws Exception{
//创建sql
String sql = "insert into user(userName) values(?)";
//执行sql
this.getJdbcTemplate().update(sql,new Object[]{userName});
}
//获取用户
public User getUser(long id)throws Exception{
final User user = new User();
//创建sql
String sql = "select * from user where id=?";
//执行sql
this.getJdbcTemplate().query(sql, new Object[]{id}, new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
user.setId(rs.getLong("id"));
user.setUserName(rs.getString("username"));
}
});
return user;
}
}
- 配置数据源,并且为JdbcTemplate注入数据源
<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.0.xsd ">
<!-- 配置数据源,这里用的是阿里巴巴的durid数据源-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!-- 为spring jdbcTemplate注入数据源 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 定义userDao,并注入jdbcTemplate目标 -->
<bean id="userDao" class="com.chensr.test.UserDao">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
</beans>
- 创建测试类
public class Test {
@org.junit.Test
public void test() throws Exception{
ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("spring-context.xml");
UserDao userDao = (UserDao)ac.getBean("userDao");
userDao.saveUser("隔壁老王");
User user = userDao.getUser(2l);
System.out.println(user.toString());
}
}
运行结果:数据库中多了一条username为“隔壁老王”的数据,并且控制台打印
User{id=2, userName='隔壁老王'}