JDBCTemplate：spring提供的用于操作数据库的模板，类似DbUtils。使用时必须设置数据源（DataSource）；数据源如DBCP、C3P0等
一、JDBCAPI简单使用Demo
1、项目结构
2、创建MySQL数据库及表
create database springJDBCPro;
use springJDBCPro;
create table user(
id int primary key auto_increment,
username varchar(50),
password varchar(32)
);
创建数据库及表
3、Java代码demo
package springJDBCPro; import java.sql.SQLException; import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate; public class TestApp {
@Test
public void demo1() throws SQLException {
//创建数据源
BasicDataSource basicDataSource=new BasicDataSource();
//注册驱动
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
//设置URL
basicDataSource.setUrl("jdbc:mysql://localhost:3306/springJDBCPro");
//设置登录用户名
basicDataSource.setUsername("root");
//设置登录密码
basicDataSource.setPassword("hjp123");
// 创建模板
JdbcTemplate jdbcTemplate = new JdbcTemplate();
//设置数据源
jdbcTemplate.setDataSource(basicDataSource);
//录入数据
jdbcTemplate.update("insert into user(username,password) values(?,?)","Jack","Jac123");
}
}
Demo
二、JDBC结合DBCP，通过spring操作数据库
1、项目结构
2、UserDao类代码
package springJDBCDBCP;
import org.springframework.jdbc.core.JdbcTemplate;
public class UserDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void save(String username, String password) {
jdbcTemplate.update("insert into user(username,password) values(?,?)",username,password);
}
}UserDao
3、beans.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<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.xsd">
<!-- 配置数据源 -->
<bean id="dataSourceId" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/springJDBCPro"></property>
<property name="username" value="root"></property>
<property name="password" value="hjp123"></property>
</bean>
<!-- 配置模板，需要数据源 -->
<bean id="jdbcTemplateId" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSourceId"></property>
</bean>
<!-- 配置Dao，需要模板 -->
<bean id="userDaoId" class="springJDBCDBCP.UserDao">
<property name="jdbcTemplate" ref="jdbcTemplateId"></property>
</bean>
</beans>
beans
4、测试Demo
package springJDBCDBCP; import java.sql.SQLException; import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext; public class TestApp {
@Test
public void demo1() throws SQLException {
String xmlPath="springJDBCDBCP/beans.xml";
ApplicationContext applicationContext=new ClassPathXmlApplicationContext(xmlPath);
UserDao userDao=applicationContext.getBean("userDaoId",UserDao.class);
userDao.save("Tom", "Tom123");
}
}
Demo
三、JDBC结合C3P0，通过spring操作数据库（下面用的是IDEA开发工具）
1、项目结构
2、在WEB-INF文件夹下新增lib文件夹，然后添加到项目Library，在lib里面添加项目结构图中的jar包
3、新建User类
package springJDBCC3P0; /**
* Created by JiaPeng on 2015/10/13.
*/
public class User {
private Integer id;
private String userName;
private String password; 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 String getPassword() {
return password;
} public void setPassword(String password) {
this.password = password;
} @Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
'}';
}
}
User
4、新建UserDao类，并且继承自JdbcDaoSupport，该类中会判断JDBCTemplate模板对象是否为空，为空自动创建，创建完后模板会自动添加数据源对象，所以在后面配置文件中添加UserDao的bean时，只注入数据源即可，而UserDao类中也无需有JDBCTemplate模板的可写对象。
package springJDBCC3P0; import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport; /**
* Created by JiaPeng on 2015/10/13.
*/
public class UserDao extends JdbcDaoSupport {
public void save(String userName, String password) {
this.getJdbcTemplate().update("insert into user(username,password) values(?,?)", userName, password);
} public User find(int id) {
String sql = "select * from user where id=?";
return this.getJdbcTemplate().queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(User.class), id);
}
}
UserDao
5、新建配置文件beans.xml，配置如下
<?xml version="1.0" encoding="UTF-8"?>
<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.xsd">
<!--配置数据源 C3P0-->
<bean id="datasourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springJDBCPro"></property>
<property name="user" value="root"></property>
<property name="password" value="hjp123"></property>
</bean>
<!--配置Dao，Dao继承JdbcDaoSupport类，该类会判断jdbctemplate模板是否为空，如果为空该类会创建模板对象，
并且调用setDataSource方法，加载数据源-->
<bean id="userDaoId" class="springJDBCC3P0.UserDao">
<property name="dataSource" ref="datasourceId"></property>
</bean>
</beans>
beans
6、新建测试类
package springJDBCC3P0; import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext; /**
* Created by JiaPeng on 2015/10/13.
*/
public class TestApp {
@Test
public void demo1() {
String xmlPath = "springJDBCC3P0/beans.xml";
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
UserDao userDao = applicationContext.getBean("userDaoId", UserDao.class);
User user = userDao.find(1);
System.out.println(user);
}
}
TestApp
四、JDBC结合C3P0，用properties文件配置数据库信息，通过spring操作数据库（下面用的是IDEA开发工具）
1、项目结构
2、新建User类
package springJDBCC3P0Properties; /**
* Created by JiaPeng on 2015/10/13.
*/
public class User {
private Integer id;
private String userName;
private String password; 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 String getPassword() {
return password;
} public void setPassword(String password) {
this.password = password;
} @Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
'}';
}
}
User
3、新建UserDao类继承自JdbcDaoSupport
package springJDBCC3P0Properties; import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport; import java.util.List; /**
* Created by JiaPeng on 2015/10/13.
*/
public class UserDao extends JdbcDaoSupport {
public List<User> findAll() {
String sql = "select * from user";
return this.getJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(User.class));
}
}
UserDao
4、新建properties文件
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/springJDBCPro
jdbc.user=root
jdbc.password=hjp123
jdbc.properties
5、新建beans.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!--加载properties配置文件，
location用于指定配置文件位置，class:固定字符串，表示从src下获取
在spring配置文件中就可以通过${key}方式获取-->
<context:property-placeholder
location="classpath:springJDBCC3P0Properties/jdbcinfo.properties"></context:property-placeholder>
<bean id="datasourceId" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<bean id="userDaoId" class="springJDBCC3P0Properties.UserDao">
<property name="dataSource" ref="datasourceId"></property>
</bean>
</beans>
beans
6、新建测试类
package springJDBCC3P0Properties; import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext; import java.util.List; /**
* Created by JiaPeng on 2015/10/13.
*/
public class TestApp {
@Test
public void demo1() {
String xmlPath = "springJDBCC3P0Properties/beans.xml";
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
UserDao userDao = applicationContext.getBean("userDaoId", UserDao.class);
List<User> userAll = userDao.findAll();
for (int i = 0; i < userAll.size(); i++) {
System.out.println(userAll.get(i));
}
}
}
TestApp