一、开发JDBCTemplate入门
Maven项目中pom文件配置:
<properties>
<spring-version>4.3.13.RELEASE</spring-version>
</properties>
<dependencies>
<!--normal need start-->
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-expression -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${spring-version}</version>
</dependency>
<!--normal need end-->
<!--Spring JdbcTemplate need start-->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring-version}</version>
</dependency>
<!--Spring JdbcTemplate need end-->
<!--mysql driver start-->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<!--mysql driver end-->
<!--Spring test need start-->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!--Spring test need end-->
</dependencies>
自定义的MyJdbcTemplate类:(实际上并不使用这个,而是在配置文件中配置即可)
public class MyJdbcTemplate {
private static JdbcTemplate jdbcTemplate = null;
private MyJdbcTemplate() { }
/*获取DriverManagerDataSource*/
public static JdbcTemplate getInstance() throws IOException {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
//配置文件的相对路径
String filePath = "db.properties";
//获取文件的输入流
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
//获取配置文件对象
Properties prop = new Properties();
//加载文件的输入流
prop.load(is);
//获取配置文件中的信息
String url = prop.getProperty("url");
String driver = prop.getProperty("driver");
String userName = prop.getProperty("userName");
String password = prop.getProperty("password");
//设置参数
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(password);
//根据DriverManagerDataSource获取JdbcTemplate
jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate;
}
}
将db.properties数据库配置文件加载到applicationContext.xml中:
<!--配置db.properties数据库配置信息文件-->
<!--方式一:Spring默认的-->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:db.properties"/>
</bean>
<!--方式二(推荐使用)-->
<context:property-placeholder location="classpath:db.properties"/>
Spring默认连接池:
<!--Spring默认的连接池-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${userName}"/>
<property name="password" value="${password}"/>
</bean>
<!--定义JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
这里注意:
JDBC连接mysql-connector-java 5时DriverClass是com.mysql.jdbc.Driver
JDBC连接mysql-connector-java 6时DriverClass时com.mysql.cj.jdbc.Driver,且需要指定时区:
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&useJDBCCompliantTimezoneShift=true
&useLegacyDatetimeCode=false&serverTimezone=UTC"/>
测试类:
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest {
@Autowired
@Qualifier("jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Test
public void test2(){
jdbcTemplate.execute("CREATE TABLE user(id int PRIMARY KEY auto_increment,name VARCHAR(20))");
}
@Test
public void test() throws IOException {
JdbcTemplate template = MyJdbcTemplate.getInstance();
template.execute("CREATE TABLE user(id int PRIMARY KEY auto_increment,name VARCHAR(20))");
}
}
一般不使用Spring默认的连接池,而是其他的例如DBCP,C3P0,还有阿里的druid。
- DBCP:
先导入依赖包:
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>
配置文件:
<!--使用dbcp的连接池-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${userName}"/>
<property name="password" value="${password}"/>
</bean>
- C3P0:
导入依赖包:
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
配置文件:
<!--使用c3p0的连接池-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&useJDBCCompliantTimezoneShift=true
&useLegacyDatetimeCode=false&serverTimezone=UTC"/>
<property name="user" value="root"/>
<property name="password" value="root"/>
</bean>
- Druid:
导入依赖包:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
配置文件:
<!-- 阿里 druid 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${userName}"/>
<property name="password" value="${password}"/>
</bean>
使用JDBC模板:完成增删改的操作
注意在配置文件中配置好Bean,要注入jdbcTemplate
<bean id="userDao" class="com.wl.jdbc_demo1.UserDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
UserDaoImpl 记得继承JdbcDaoSupport 类:
public class UserDaoImpl extends JdbcDaoSupport implements UserDao {
/*增*/
public void add(User user) {
String sql = "insert into user values(null,?)";
this.getJdbcTemplate().update(sql,user.getName());
}
/*删*/
public void delete(User user) {
String sql = "delete from user where id =?";
this.getJdbcTemplate().update(sql,user.getId());
}
/*改*/
public void update(User user) {
String sql = "update user set name = ? where id = ?";
this.getJdbcTemplate().update(sql,user.getName(),user.getId());
}
使用JDBC模板:完成查询操作
查询更复杂一点,分为简单查询和复杂查询:
1.简单查询:
select count(*) from user ---queryForInt(String sql)
select name from user where id = ? ---queryForObject(String sql,Class clazz,Object... args)
2.复杂查询:
select * from user where id= ? ---queryForObject(String sql,RowMapper<T> rowObject,Object... args)
select * from user ---query(String sql,RowMapper<T> rowObject,Object... args)
public int findCount(){
String sql = "select count(*) from user";
Integer count = this.getJdbcTemplate().queryForObject(sql, Integer.class);
return count;
}
public String findNameById(int id){
String sql = "select name from user where id = ?";
String name = this.getJdbcTemplate().queryForObject(sql, String.class,id);
return name;
}
public User findAllById(int id){
String sql = "select * from user where id = ?";
User user = this.getJdbcTemplate().queryForObject(sql, new MyRowMapper(), id);
return user;
}
public List<User> findAll() {
String sql = "select *from user";
List<User> userList = this.getJdbcTemplate().query(sql, new MyRowMapper());
return userList;
}
class MyRowMapper implements RowMapper<User> {
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
return user;
}
测试类:
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTest2 {
@Autowired
@Qualifier("userDao")
private UserDao userDao;
@Resource(name = "user")
private User user;
//简单查询
@Test
public void findCountTest(){
int count = userDao.findCount();
System.out.println(count);
}
@Test
public void findNameByIdTest(){
String name = userDao.findNameById(2);
System.out.println(name);
}
//复杂查询
@Test
public void findAllByIdTest(){
user = userDao.findAllById(3);
System.out.println(user);
}
@Test
public void findAllTest(){
List<User> userList = userDao.findAll();
for (User user:userList
) {
System.out.println(user);
}
}
@Test
//增
public void add(){
user.setName("wangLi777");
userDao.add(user);
}
@Test
//改
public void update(){
user.setId(1);
user.setName("wangLi777");
userDao.update(user);
}
@Test
//删
public void delete(){
user.setId(1);
userDao.delete(user);
}
}