Spring连接数据库mysql操作

使用spring操作数据库是非常简单的,无论是配置还是操作,最近工作要用到就问同事如何操作了,具体的细
节还要日后慢慢学习,先贴出如何使用.

1.建立数据源,也就是一个xml配置文件 resource/applicationContext.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-2.0.xsd">

    <!-- 这里是用来配置连接数据库基础信息的,用JDBC链接 -->
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>com.mysql.jdbc.Driver</value>
        </property>
        <property name="url">
            <value>
                jdbc:mysql://10.100.100.110:3306/HotelDB
            </value>
        </property>
        <property name="username">
            <value>root</value>
        </property>
        <property name="password">
            <value>password</value>
        </property>
    </bean>
    <!-- 这里根据spring的注入原理直接把参数放到ConnectionProvider -->
    <!-- 到时候通过id就直接调用ConnectionProvider对象-->
    <bean id="com.badpeas.ConnectionProvider"
        class="hotel.information.peohotel.dbutil.ConnectionProvider">
        <property name="dataSource">
          <!-- ref元素用来将容器内其它bean的id传给<constructor-arg/> 或 <property/>元素 -->
            <!-- 同时提供错误验证功能 -->
            <ref bean="dataSource" />
        </property>
    </bean>

</beans>

==============================================

2.建立方法类 com/badpeas/dbutil/ConnectionProvider.java
==============================================
package com.badpeas.dbutil;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

/**
* 连接数据库和获取数据源的普通类
* @author badpeas
*
*/
public class ConnectionProvider {
    private DataSource dataSource = null;

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    /*
    * 这个方法用来连接数据库.
    */
    public Connection getConnection() throws SQLException{
        if (dataSource == null){
            return null;
        }
        return dataSource.getConnection();
    }
}
==============================================

3.建立工厂类去实现连接和获取数据源的工作 com/badpeas/dbutil/ConnectionFactory.java
==============================================
package com.badpeas.dbutil;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
* 利用spring统一管理连接数据库和获取数据源
* @author badpeas
*
*/
public class ConnectionFactory {
    // 访问xml,获取上下文context
    private static ApplicationContext context;
    private static final String CONFIG_FILE = "resource/applicationContext.xml";
    static {
        context = new ClassPathXmlApplicationContext(CONFIG_FILE);
    }
    // 通过在xml中指定的id获得bean对象,然后获取连接
    public static Connection getConnection() throws BeansException,
            SQLException {
        return ((ConnectionProvider) context
                .getBean("com.badpeas.ConnectionProvider")).getConnection();
    }
    // 通过在xml中指定的id获得bean对象,然后获取数据源
    public static DataSource getDataSource() {
        return ((ConnectionProvider) context
                .getBean("com.badpeas.ConnectionProvider")).getDataSource();
    }

}

==============================================

4.建立DAO类,统一管理数据库的操作方法 com/badpeas/DAO/BadpeasDataSourceDAO.java
==============================================
package com.badpeas.DAO;

import hotel.information.bean.Location;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

/**
* 操作数据库的类
* @author badpeas
*
*/
public class PeoHotelLoactionDAO {
    private JdbcTemplate jt = null;
    // 静态方法获取dataSource
    private static DataSource dataSource = null;
    static {
        dataSource = hotel.information.peohotel.dbutil.ConnectionFactory
                .getDataSource();
    }
    // 初始化的时候实例化JdbcTemplate,后面用来实现数据库序列化.
    public PeoHotelLoactionDAO() {
        this.jt = new JdbcTemplate(dataSource);
    }
    // 按bookCode查询
    public Book findBook(String bookCode) {
        String sql = "select bookId,bookName from BookDB where bookCode=?";
        RowMapper mapper = new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                Book book = new Book();
                book.setBookId(rs.getString("bookId"));
                book.setBookName(rs.getString("bookName"));
                return book;
            }
        };
        List<Book> list = jt.query(sql, new Object[] { bookCode }, mapper);
        Book bookResult = (Book)list.get(0);
        return bookResult;
    }
    //查询所有Book
    public List<Book> findBookList() {
        String sql = "select bookId,bookName from BookDB ";
        RowMapper mapper = new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                Book book = new Book();
                book.setBookId(rs.getString("bookId"));
                book.setBookName(rs.getString("bookName"));
                return book;
            }
        };
        List list = jt.query(sql, new Object[] {}, mapper);
        return list;
    }

}

==============================================

5.数据源对象 com/badpeas/bean/Book.java
==============================================
package com/badpeas/bean;

import java.io.Serializable;

/**
* 书籍bean
* @author badpeas
*
*/
public class Book implements Serializable{
    private String bookId;
    private String bookName;

    public String getBookId() {
        return bookId;
    }
    public void setBookId(String bookId) {
        this.bookId = bookId;
    }
    public String getBookName() {
        return bookName;
    }
    public void setBookName(String bookName) {
        this.bookName = bookName;
    }
}
==============================================

这里我的数据库是采用mySQL,调用的是我的BookDB表,采用的是JDBC链接,其他数据库和方式基本类同!
操作数据库的时候直接在要操作的类里面调用DAO类的方法就可以了.


[color=red][/color]