spring通过xml配置JDBC连接mysql
主要文件以下几个:
1、sys.properties
2、jdbc-servlet.xml
3、IUserDAO.java
4、UserDAOImpl.java
5、User.java
6、MysqlJDBCTest.java
参考:http://www.cnblogs.com/lichenwei/p/3902294.html
1、sys.properties
配置mysql的相关属性:
#mysql
driverClassName=com.mysql.jdbc.Driver
alias=apmsys
maximumConnectionCount=300
minimumConnectionCount=10
jdbc.url=jdbc:mysql://127.0.0.1:3308/mdl?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
jdbc.username=root
jdbc.password=123456
2、jdbc-servlet.xml
读取属性文件,并获取数据库属性值
按照以往Spring的依赖注入,我们需要在接口实现类中利用构造器去获取JdbcTemplate
Spring早就帮我们想到了这点,它为我们提供了JdbcDaoSupport支持类,所有DAO继承这个类,就会自动获得JdbcTemplate(前提是注入DataSource)。
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.2.xsd">
<context:annotation-config />
<context:component-scan base-package="com.mdl.excel" />
<!-- 加载内网系统配置 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="properties" ref="configProperties" />
</bean>
<bean id="configProperties"
class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="locations">
<list>
<value>classpath:sys.properties</value>
</list>
</property>
</bean>
<!-- mybatis 配置及jdbc 配置 -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean> -->
<!-- 当使用继承JdbcDaoSupport时使用,其他情况只需要配置dataSource和jdbcTemplate就可以 -->
<bean id="userdao" class="com.mdl.excel.mysql.dao.impl.UserDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
</beans>
3、IUserDAO.java
package com.mdl.excel.mysql.dao;
import java.util.List;
import com.mdl.excel.mysql.bean.User;
public interface IUserDAO {
public void addUser(User user);
public void deleteUser(int id);
public void updateUser(User user);
public String searchUserName(int id);
public User searchUser(int id);
public List<User> findAll();
}
4、UserDAOImpl.java
接口实现类:UserDAOImpl.java
在我们的实现类中直接利用getJdbcTemplate就可以获取操作对象了。
JdbcTemplate主要提供下列方法:
1、execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
2、update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate方法用于执行批处理相关语句;
3、query方法及queryForXXX方法:用于执行查询相关语句;
4、call方法:用于执行存储过程、函数相关语句。
package com.mdl.excel.mysql.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.mdl.excel.mysql.bean.User;
import com.mdl.excel.mysql.dao.IUserDAO;
public class UserDAOImpl extends JdbcDaoSupport implements IUserDAO {
@Override
public void addUser(User user) {
String sql = "insert into user values(?,?,?)";
this.getJdbcTemplate().update(sql, user.getId(), user.getUsername(), user.getPassword());
}
@Override
public void deleteUser(int id) {
String sql = "delete from user where id=?";
this.getJdbcTemplate().update(sql, id);
}
@Override
public void updateUser(User user) {
String sql = "update user set username=?,password=? where id=?";
this.getJdbcTemplate().update(sql, user.getUsername(), user.getPassword(), user.getId());
}
@Override
public String searchUserName(int id) {// 简单查询,按照ID查询,返回字符串
String sql = "select username from user where id=?";
// 返回类型为String(String.class)
return this.getJdbcTemplate().queryForObject(sql, String.class, id);
}
@Override
public List<User> findAll() {// 复杂查询返回List集合
String sql = "select * from user";
return this.getJdbcTemplate().query(sql, new UserRowMapper());
}
@Override
public User searchUser(int id) {
String sql = "select * from user where id=?";
return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);
}
class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
return user;
}
}
}
5、User.java
package com.mdl.excel.mysql.bean;
public class User {
private String username;
private String email;
private int id;
private String password;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
6、MysqlJDBCTest.java
package com.mdl.excel.mysql.service;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.mdl.excel.mysql.bean.User;
import com.mdl.excel.mysql.dao.IUserDAO;
public class MysqlJDBCTest {
// public static void main(String[] args) {
//
// ApplicationContext ac = new
// ClassPathXmlApplicationContext("jdbc-servlet.xml");
// IUserDAO dao=(IUserDAO) ac.getBean("userdao");
// User user = dao.searchUser(1);
//
// System.out.println(user.getUsername() + "---" + user.getEmail());
//
// }
@Test // 增
public void demo1() {
User user = new User();
user.setId(3);
user.setUsername("admin");
user.setPassword("123456");
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbc-servlet.xml");
IUserDAO dao = (IUserDAO) applicationContext.getBean("userDao");
dao.addUser(user);
}
@Test // 改
public void demo2() {
User user = new User();
user.setId(1);
user.setUsername("admin");
user.setPassword("admin");
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbc-servlet.xml");
IUserDAO dao = (IUserDAO) applicationContext.getBean("userDao");
dao.updateUser(user);
}
@Test // 删
public void demo3() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbc-servlet.xml");
IUserDAO dao = (IUserDAO) applicationContext.getBean("userDao");
dao.deleteUser(3);
}
@Test // 查(简单查询,返回字符串)
public void demo4() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbc-servlet.xml");
IUserDAO dao = (IUserDAO) applicationContext.getBean("userDao");
String name = dao.searchUserName(1);
System.out.println(name);
}
@Test // 查(简单查询,返回对象)
public void demo5() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbc-servlet.xml");
IUserDAO dao = (IUserDAO) applicationContext.getBean("userDao");
User user = dao.searchUser(1);
System.out.println(user.getUsername());
}
@Test // 查(复杂查询,返回对象集合)
public void demo6() {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("jdbc-servlet.xml");
IUserDAO dao = (IUserDAO) applicationContext.getBean("userDao");
List<User> users = dao.findAll();
System.out.println(users.size());
}
}
附:
1、Spring 为每种持久化技术 提供一个支持类,在DAO 中注入 模板工具类
(1)JDBC : org.springframework.jdbc.core.support.JdbcDaoSupport
(2)Hibernate 3.0 :org.springframework.orm.hibernate3.support.HibernateDaoSupport
(3)iBatis :org.springframework.orm.ibatis.support.SqlMapClientDaoSupport
用户自己编写DAO 只需要继承 JdbcDaoSupport, 就可以注入 JdbcTemplate
2、 通过jdbcTemplate 提供 int update(String sql, Object… args) 实现增加 、修改 、删除
3、简单查询,返回原始数据类型, String类型
String sql = "select count(*) from user"; // int queryForInt(String sql)
String sql = "select name from user where id = ? "; // <T> T queryForObject(String sql, Class<T> requiredType, Object... args)
4、 复杂查询
JdbcTemplate 没有handler, 手动完成对象封装
编写实体类 RowMapper
class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
// rs 已经指向每一条数据,不需要自己调用 next,将rs指向数据 转换 User对象
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
return user;
}
}
查询单个对象 T queryForObject(String sql, RowMapper rowMapper, Object… args)
return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(),id);
查询所有对象List集合 List query(String sql, RowMapper rowMapper, Object… args)
return this.getJdbcTemplate().query(sql, new UserRowMapper());