关于在Spring中使用注入DateSource实现查询数据库
以前查询数据库都是用DriverManager 如：
package TestConnection;
import java.sql.*;
import java.util.Scanner;
public class Testmysql {
public static void main(String[] args) {
Connection conn =null;
Statement stmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn= DriverManager.getConnection("jdbc:mysql://localhost/mydata?" +
"user=root&password=root");
stmt = conn.createStatement();
stmt.executeUpdate(sql);
rs = stmt.executeQuery("SELECT * FROM dept");
while(rs.next()){
System.out.println(rs.getString("deptno"));
}
}catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
}finally{
try{
if(rs!=null)
rs.close();
rs=null;
if(stmt!=null)
stmt.close();
stmt=null;
if(conn!=null)
conn.close();
conn=null;
}catch(Exception e){
e.printStackTrace();
}
}
}
}
现在想用DataSource实现上述功能 再try 中 这样写
{
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql//localhost/spring");
ds.setUsername("root");
ds.setPassword("root");
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
}
报No suitable driver 错（我引入了连接的jar包，并且第一个通过DriverManager的可以正常运行）我想请问这是什么问题？
另外如果想通过注入DataSource的方式
我知道在XML 中 这样写
可是在上面的try 里 如何写 才能让结果集中的数据正常输出那？应该另写一个main方法？可是我这个程序是写在main方法中的 直接去掉会报个引号错误（可是引号没有错）请问应该怎么做？
[b]问题补充：[/b]
那是否还用Statement？如何查询结果集那？另外注入方式TRY 中怎么写？
1、关于使用DataSource实现时报No suitable driver 错的处理：
try语句块中的url错误,应该修改为：
[code="java"]ds.setUrl("jdbc:mysql://localhost/spring");
[/code]
2、使用Spring配置文件,通过注入DataSource的方式实现时try语句块的实现：
[code="java"]
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
DriverManagerDataSource ds = (DriverManagerDataSource)context.getBean("dataSource");
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
[/code]
建立一个DriverManagerDataSource的单例就好了，不用每次调用都这么新建一个实例
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql//localhost/spring");
ds.setUsername("root");
ds.setPassword("root");
得到数据库连接只需要conn = ds.getConnection(); 去取连接就行了
以这种方式写关闭语句比较好，也是spring内部实现机制
[code="java"]
/**
* Close the given JDBC Connection and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
* @param con the JDBC Connection to close (may be null)
*/
public static void closeConnection(Connection con) {
if (con != null) {
try {
con.close();
}
catch (SQLException ex) {
logger.debug("Could not close JDBC Connection", ex);
}
catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.debug("Unexpected exception on closing JDBC Connection", ex);
}
}
}
/**
* Close the given JDBC Statement and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
* @param stmt the JDBC Statement to close (may be <code>null</code>)
*/
public static void closeStatement(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
}
catch (SQLException ex) {
logger.debug("Could not close JDBC Statement", ex);
}
catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.debug("Unexpected exception on closing JDBC Statement", ex);
}
}
}
/**
* Close the given JDBC ResultSet and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
* @param rs the JDBC ResultSet to close (may be <code>null</code>)
*/
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
}
catch (SQLException ex) {
logger.debug("Could not close JDBC ResultSet", ex);
}
catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.debug("Unexpected exception on closing JDBC ResultSet", ex);
}
}
}
[/code]