spring整合JDBC&servlet&dbutils&常用数据源

目录

spring整合JDBC

8. 集成servlet

spring集成dbutils

常用数据源

---

spring整合JDBC

1. 创建一个maven web工程

2. 引入依赖

<properties>
        <spring.version>4.3.0.RELEASE</spring.version>

    </properties>
    <dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>javax.servlet.jsp-api</artifactId>
            <version>2.2.1</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version> 0.9.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.3</version>
        </dependency>
        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.6.1</version>
        </dependency>

        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>

3. spring核心配置文件

<?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">
    
</beans>

4. 创建数据库和实体类

5. 创建WorkerDao接口及实现类

public interface WorkerDao {

    public void saveWorker(Worker worker);

    public void updateWorker(Worker worker);

    public void deleteWorker(Integer id);

    public Worker getWorkerById(Integer id);

    public List<Worker> getWorkers();
}

实现类：

public class WorkerDaoImpl implements WorkerDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void saveWorker(Worker worker) {

        String sql="insert into worker(name,gender,age,salary) values(?,?,?,?)";
        Object[] args=new Object[]{worker.getName(),worker.getGender(),worker.getAge(),worker.getSalary()};
        jdbcTemplate.update(sql,args);
    }

    @Override
    public void updateWorker(Worker worker) {
        String sql="update worker set  name=?,gender=?,age=?,salary=? where  id=?";
        Object[] args=new Object[]{worker.getName(),worker.getGender(),worker.getAge(),
                worker.getSalary(),worker.getId()};
        jdbcTemplate.update(sql,args);
    }

    @Override
    public void deleteWorker(Integer id) {
        String sql="delete from worker where  id=?";
        jdbcTemplate.update(sql,id);

    }

    @Override
    public Worker getWorkerById(Integer id) {
        String sql="select * from worker where  id=?";
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new RowMapper<Worker>() {
            @Override
            public Worker mapRow(ResultSet rs, int i) throws SQLException {
                Worker worker=new Worker();
                worker.setId(rs.getInt("id"));
                worker.setName(rs.getString("name"));
                worker.setGender(rs.getString("gender"));
                worker.setAge(rs.getInt("age"));
                worker.setSalary(rs.getInt("salary"));
                return worker;
            }
        });
    }

    @Override
    public List<Worker> getWorkers() {
        String sql="select * from worker";
       return  jdbcTemplate.query(sql, new RowMapper<Worker>() {
            public Worker mapRow(ResultSet rs, int i) throws SQLException {
                Worker worker=new Worker();
                worker.setId(rs.getInt("id"));
                worker.setName(rs.getString("name"));
                worker.setGender(rs.getString("gender"));
                worker.setAge(rs.getInt("age"));
                worker.setSalary(rs.getInt("salary"));
                return worker;
            }
        });
    }
}

6. 在spring核心配置文件中加入数据源 JdbcTemlate WorkerDaoImpl

<!--配置c3p0数据源-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql:///803mybatis03"></property>
        <property name="user" value="root"></property>
        <property name="password" value="root"></property>
        <!--初始化时获取的连接数，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
        <property name="initialPoolSize" value="5"/>
        <!--连接池中保留的最小连接数。-->
        <property name="minPoolSize" value="3"/>
        <!--连接池中保留的最大连接数。Default: 15 -->
        <property name="maxPoolSize" value="10"/>
        <!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
        <property name="maxIdleTime" value="60"/>
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
        <property name="acquireIncrement" value="3"/>
        <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
        <property name="idleConnectionTestPeriod" value="2"/>
    </bean>

    <!--把JdbcTemplate交给spring容管理-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
    </bean>

    <bean id="workerDao" class="cn.offcn.dao.impl.WorkerDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

7. 测试

    @Test
    public void testSaveWorker(){

        ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");
        WorkerDao workerDao=(WorkerDao) ctx.getBean("workerDao");
        Worker worker=new Worker("崔智科","女",20,80000);
        workerDao.saveWorker(worker);
    }

8. 集成servlet

在web.xml文件中监听spring核心配置文件

    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:applicationContext.xml</param-value>
    </context-param>

    <listener>
       <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>

9. 编写WorkerServlet

@WebServlet(urlPatterns = {"/workerList"})
public class WokerServlet extends HttpServlet {

    private WorkerDao workerDao;

    @Override
    public void init() throws ServletException {
        workerDao=(WorkerDao) WebApplicationContextUtils.getWebApplicationContext(getServletContext()).getBean("workerDao");
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        List<Worker> workerList=workerDao.getWorkers();
        request.setAttribute("workerList",workerList);
        request.getRequestDispatcher("/result.jsp").forward(request,response);

    }
}

10. 部署到tomcat

在pom.xml中加入插件

   <build>
        <plugins>

            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <artifactId>tomcat7-maven-plugin</artifactId>
                <configuration>
                    <path>/</path>
                    <port>8080</port>
                </configuration>
            </plugin>
        </plugins>
    </build>

spring集成dbutils

1. 引入依赖

        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>

2. 编写一个WorkerDao实现类

public class WorkerDaoImpl2 implements WorkerDao {

    private QueryRunner queryRunner;

    public void setQueryRunner(QueryRunner queryRunner) {
        this.queryRunner = queryRunner;
    }

    @Override
    public void saveWorker(Worker worker) {

    }

    @Override
    public void updateWorker(Worker worker) {

    }

    @Override
    public void deleteWorker(Integer id) {

    }

    @Override
    public Worker getWorkerById(Integer id) {
        return null;
    }

    @Override
    public List<Worker> getWorkers() {

        try {
            List<Worker> workerList=queryRunner.query("select * from worker",new BeanListHandler<Worker>(Worker.class));
            return workerList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

3. 在spring核心配置文件中配置QueryRunner和WorkerDaoImpl2

<bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
      <constructor-arg name="ds" ref="dataSource"></constructor-arg>
    </bean>

    <bean id="workerDao" class="cn.offcn.dao.impl.WorkerDaoImpl2">
       <property name="queryRunner" ref="queryRunner"></property>
</bean>

4. 重新部署运行

常用数据源

<!--C3p0数据源-->
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql:///ddd"></property>
    <property name="user" value="root"></property>
    <property name="password" value="root"></property>
</bean>
<!--Dbcp数据源-->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.1.1</version>
</dependency>
<bean name="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
     <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql:///ddd"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
</bean>
<!--Druid数据源(阿里)-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.20</version>
</dependency>
<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method=”close”>
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql:///ddd"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
</bean>
<!--spring自带数据源-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>$4.3.0.RELEASE</version>
</dependency>

<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql:///ddd"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
</bean>
<!--Hikari(光)数据源-->
<dependency>
    <groupId>hikari-cp</groupId>
    <artifactId>hikari-cp</artifactId>
    <version>2.8.0</version>
</dependency>
<bean name="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="shutdown">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="jdbcUrl" value="jdbc:mysql:///ddd"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
</bean>