使用数据连接池的方式连接数据库

（1）创建资源文件context.xml，习惯放在META-INF下面，文件中的内容如下图所示。

context.xml

context.xml

---

<?xml version='1.0' encoding='utf-8'?>

<Context>

    <Resource

        name="jdbc/tofDS" 

        type="javax.sql.DataSource" 

        driverClassName="com.mysql.jdbc.Driver"

        maxIdle="2"

        maxWait="5000"

        username="root"

        password="root" 

        url="jdbc:mysql://localhost:3306/soccerleague"

        maxActive="4"/>

</Context>

---

（2）获取与数据的连接对象Connection，代码如下图所示：

JDBC.java

JDBC.java

---

package cwu.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

import org.springframework.context.annotation.Scope;

import org.springframework.stereotype.Repository;

//下面两个@是Spring注释类，可以不要，但是本例子中全部都使用了Spring注释类

@Repository

@Scope

public class JDBC {

Connection conn = null;

DataSource ds =null;

public Connection getConn() {

try {

Context context = new InitialContext();

ds = (DataSource)context.lookup("java:comp/env/jdbc/tofDS");

conn = ds.getConnection();

} catch (NamingException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

}

return conn;

// TODO Auto-generated method stub

}

}

---

（3）增加、查询举例

查询语句 插入语句

Dao层代码

---

package cwu.chang.dao;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.context.annotation.Scope;

import org.springframework.stereotype.Repository;

import cwu.chang.Po.League;

import cwu.jdbc.JDBC;

@Repository

@Scope

public class leagueDao {

PreparedStatement pstmt = null;

Statement stat = null;

@Resource

private JDBC jdbc;

List list = null;

ResultSet res = null;

public List<League> QueryLeague() {

// TODO Auto-generated method stub

list  = new ArrayList<League>();

try {

stat = (Statement) jdbc.getConn().createStatement();

res  = stat.executeQuery("select * from league");

while(res.next()){

int lyear = res.getInt(2);

String ltime = res.getString(3);

String ltitle = res.getString(4);

League league = new League();

league.setLyear(lyear);

league.setLtime(ltime);

league.setLtitle(ltitle);

list.add(league);

}

System.out.println(list);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

public void AddLeague(int lyear, String ltime, String ltitle) {

// TODO Auto-generated method stub

try {

pstmt = (PreparedStatement) jdbc.getConn().prepareStatement("insert into league values(null,?,?,?)");

pstmt.setInt(1,lyear);

pstmt.setString(2,ltime);

pstmt.setString(3,ltitle);

pstmt.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

---