关于在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 中怎么写?

3个回答

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]

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问